unity读写本地excel_2024.4.22

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using OfficeOpenXml;
using System.IO;
using Excel;
using System.Data;
using System;
/// <summary>
/// https://blog.csdn.net/Xz616/article/details/128893023
/// Unity3D操作数据之Excel表操作(创建、读取、写入、修改)
/// 北斗课题 VR 设备统计功能开发——每次程序打开关闭记录对应时间到本地电子表格
/// 列：打开年，月，日，时间，关闭年，月，日，时间
/// </summary>
public class ExcelOperation : MonoBehaviour
{
    // [SerializeField]
    string strExcelSavePath = "";
    [SerializeField]
    string strFilename = "北斗VR设备统计.xlsx";
    // Start is called before the first frame update
    void Start()
    {
        //strExcelSavePath = Application.streamingAssetsPath;

    }
    /// <summary>
    /// 增
    /// Application.dataPath   :           E:/UnityProject_V2020.3.47f3c1/BDProject/Assets
    /// Application.streamingAssetsPath    :/UnityProject_V2020.3.47f3c1/BDProject/Assets/StreamingAssets
    /// </summary>
    public void CreatExcel()
    {
        //文件地址
        FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
        //如果文件存在删除重建
        if (newFile.Exists)
        {
            newFile.Delete();
            newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
        }

        //数据操作//如果只想创建文件不写入表头，数据操作步骤可以省略
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //初次创建增加数据操作（重点在于这条操作语句不同）
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");

            //添加对应列名
            worksheet.Cells[1, 1].Value = "列名1";
            worksheet.Cells[1, 2].Value = "列名2";
            worksheet.Cells[1, 3].Value = "列名3";

            //保存
            package.Save();
            print("创建成功");
        }
    }
    /// <summary>
    /// 存
    /// </summary>
    public void WriteExcel()
    {
        //文件地址
        FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
        //数据操作
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
            ExcelWorksheet worksheet = package.Workbook.Worksheets["test"];

            //添加第二行数据
            worksheet.Cells[2, 1].Value = "名称1";
            worksheet.Cells[2, 2].Value = "价格1";
            worksheet.Cells[2, 3].Value = "销量1";

            //添加第三行数据
            worksheet.Cells[3, 1].Value = "名称2";
            worksheet.Cells[3, 2].Value = "价格2";
            worksheet.Cells[4, 3].Value = "销量2";

            //保存
            package.Save();
            print("写入数据成功");
        }
    }
    /// <summary>
    /// 已知已有行数，添加一行新数据，供外部调用
    /// dt为开始时间
    /// </summary>
    public void AddStartTimeToExcel()
    {
        if (!isSaveExcel)
        { //文件地址
            FileInfo newFile = new FileInfo(strExcelSavePath + "/" + strFilename);
            //数据操作
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                //增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
                ExcelWorksheet worksheet = package.Workbook.Worksheets["设备统计"];

                //添加第curRows(原行数当现索引用)行数据  Cells[x,y]//x，y都是从1开始数的
                curRows++;
                worksheet.Cells[curRows, 1].Value = (curRows - 1).ToString();
                worksheet.Cells[curRows, 2].Value = DateTime.Now.Year;
                worksheet.Cells[curRows, 3].Value = DateTime.Now.Month;
                worksheet.Cells[curRows, 4].Value = DateTime.Now.Day;
                worksheet.Cells[curRows, 5].Value = DateTime.Now.ToString("HH:mm:ss");

                //保存
                package.Save();
                isSaveExcel = true;
                print("写入开始时间到excel");
            }
        }
    }
    /// <summary>
    /// 给系统一登录新增的那一行数据的最后一列新增内容——存储结束时间
    /// </summary>
    public void AddEndTimeToExcel()
    {
        if (isSaveExcel)
        { //文件地址
            FileInfo newFile = new FileInfo(strExcelSavePath + "/" + strFilename);
            //数据操作
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                //增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
                ExcelWorksheet worksheet = package.Workbook.Worksheets["设备统计"];

                //添加第curRows(原行数当现索引用)行数据  Cells[x,y]//x，y都是从1开始数的
                // curRows = curRows++;
                // worksheet.Cells[curRows, 1].Value = (curRows - 1).ToString();
                worksheet.Cells[curRows, 6].Value = DateTime.Now.Year;
                worksheet.Cells[curRows, 7].Value = DateTime.Now.Month;
                worksheet.Cells[curRows, 8].Value = DateTime.Now.Day;
                worksheet.Cells[curRows, 9].Value = DateTime.Now.ToString("HH:mm:ss");

                //保存
                package.Save();
                isSaveExcel = false;
                print("写入结束时间到excel");
            }
        }
    }
    /*/// <summary>
    /// 删改 from csdn
    /// </summary>
    public void ChangeExcel()
    {
        //文件地址
        FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");

        //数据操作
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets["test"];

            //追加
            worksheet.Cells[4, 1].Value = "名称3";
            worksheet.Cells[4, 2].Value = "价格3";
            worksheet.Cells[4, 3].Value = "销量3";

            //删除某一列（参数：列的序号）
            worksheet.DeleteColumn(1);
            //删除某一行（参数：行的序号）
            worksheet.DeleteRow(1);

            //修改（和添加一样）
            worksheet.Cells[4, 1].Value = "修改名称";
            worksheet.Cells[4, 2].Value = "修改价格";
            worksheet.Cells[4, 3].Value = "修改销量";


            //保存
            package.Save();

            print("删改数据成功");
        }
    }*/
    /// <summary>
    /// 第一次存开始时间的时候不变此值，存结束时间的时候变
    /// </summary>
    bool isSaveExcel = false;
    /// <summary>
    /// 系统刚打开查询表格看看表格中总行数，也就是即将添加的行的索引
    /// </summary>
    int curRows = 0;
    /// <summary>
    /// 系统刚打开查询表格看看表格中总行数，也就是即将添加的行的索引
    /// </summary>
    int curColumns = 0;
    /* /// <summary>
     /// 查 from csdn
     /// </summary>
     public void ReadExecel()
     {
         //加载文件
         FileStream fileStream = File.Open(strExcelSavePath + "/test.xlsx", FileMode.Open, FileAccess.Read);
         IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
         DataSet result = excelDataReader.AsDataSet();

         // 获取表格有多少列
         int columns = result.Tables[0].Columns.Count;
         // 获取表格有多少行 
         int rows = curRows = result.Tables[0].Rows.Count;
         Debug.Log("共有：" + columns + "列 " + rows + "行");

         //第一行为表头，不读取。没有表头从0开始(获取数据)
         for (int i = 0; i < rows; i++)
         {
             for (int j = 0; j < columns; j++)
             {
                 // 获取表格中指定行指定列的数据 
                 string value = result.Tables[0].Rows[i][j].ToString();
                 Debug.Log(value);
             }
         }
         fileStream.Close();

         print("查询数据成功");
     }*/
    /// <summary>
    /// 读取本地excel,为了填充变量 curRows，获取当前行数
    /// </summary>
    /// <param name="fileName">含文件后缀：北斗VR设备统计.xlsx</param>
    public void ReadExecel()
    {
        //加载文件
        FileStream fileStream = File.Open(strExcelSavePath + "/" + strFilename, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
        DataSet result = excelDataReader.AsDataSet();

        // 获取表格有多少列
        int columns = curColumns = result.Tables[0].Columns.Count;
        // 获取表格有多少行 
        int rows = curRows = result.Tables[0].Rows.Count;
        Debug.Log("共有：" + columns + "列 " + rows + "行");

        /* //第一行为表头，不读取。没有表头从0开始(获取数据)
         for (int i = 0; i < rows; i++)
         {
             for (int j = 0; j < columns; j++)
             {
                 // 获取表格中指定行指定列的数据 
                 string value = result.Tables[0].Rows[i][j].ToString();
                 Debug.Log(value);
             }
         }*/
        fileStream.Close();
        //print("查询数据成功，行数：" + curRows);
    }
    // Update is called once per frame
    void Update()
    {
        /*if (Input.GetKeyUp(KeyCode.U))
        {
            print(Application.streamingAssetsPath);

            CreatExcel();
        }
        if (Input.GetKeyUp(KeyCode.I))
        {
            WriteExcel();
        }*/
        /*if (Input.GetKeyUp(KeyCode.O))
        {
            ReadExecel();
            AddStartTimeToExcel();
        }
        if (Input.GetKeyUp(KeyCode.P))
        {
            AddEndTimeToExcel();
        }*/
    }
    /// <summary>
    /// DontDestroyOnLoad(GameObject);
    //让此物体在场景销毁的时候不进行销毁，但是有几点需要注意
    //再次返回到此物体所在场景的时候，这个脚本所在的物体也会进行初始化，执行Awake(),Start()方法，所以要设计成单例的：
    //注意看注释
    /// </summary>

    private static ExcelOperation _instance;   // 单例
    public ExcelOperation GameManagerInstance
    {
        get { return _instance; }
    }
    void Awake()
    {
        if (_instance != null)
        {
            //这里一定要是销毁this.gameObject
            Destroy(this.gameObject);
            return;
        }
        //这句话只执行一次，第二次上面return了
        _instance = this;
        strExcelSavePath = Application.streamingAssetsPath;
        ReadExecel();
        AddStartTimeToExcel();
        DontDestroyOnLoad(gameObject);
    }

    private void OnApplicationQuit()
    {
        AddEndTimeToExcel();
    }
}