Unity读写.xlsx表格文件

Unity读写.xlsx表格文件

用到的dll文件，下载地址

1、打开系统弹窗文件夹-写入文件

 private void SaveExcel()
    {
        OpenFileName ofn = new OpenFileName();
        ofn.structSize = Marshal.SizeOf(ofn);
        ofn.filter = "Excel Files(*.xlsx)\0*.xlsx\0";  //指定打开格式
        ofn.file = new string(new char[256]);
        ofn.maxFile = ofn.file.Length;
        ofn.fileTitle = new string(new char[64]);
        ofn.maxFileTitle = ofn.fileTitle.Length;
        ofn.initialDir = UnityEngine.Application.dataPath;//默认路径
        ofn.title = "打开Excel";
        ofn.defExt = "xlsx";
        //注意 一下项目不一定要全选 但是0x00000008项不要缺少
        ofn.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000200 | 0x00000008;
        //打开windows框
        if (DllTest.GetSaveFileName(ofn))
        {
            //TODO

            //把文件路径格式替换一下
            ofn.file = ofn.file.Replace("\\", "/");
           //Debug.Log(ofn.file); 
            FileInfo newFile = new FileInfo(ofn.file);
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(ofn.file);
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                // 添加一个sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("信息");
                //添加一点测试数据
                List<ImportClass> importClasses = new List<ImportClass>();
                importClasses.Add(new ImportClass("小刚", "789", "789789000"));
                importClasses.Add(new ImportClass("小亮", "147", "147147"));

                worksheet.Cells[1, 1, 1, 6].Merge = true;//合并单元格(1行1列到1行6列)
                worksheet.Cells["A1"].Value = "学生信息"; //显示
                worksheet.Cells["A1"].Style.Font.Size = 16; //字体大小
                worksheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //对其方式
                worksheet.Cells["A1"].Style.Border.BorderAround(ExcelBorderStyle.Thin); //表格边框
                package.Save();
            }
        }
    }

2、打开系统弹窗文件夹-读取文件

 private void ReadExcel()
    {
        OpenFileName ofn = new OpenFileName();
        ofn.structSize = Marshal.SizeOf(ofn);
        ofn.filter = "Excel Files(*.xlsx)\0*.xlsx\0";  //指定打开格式
        ofn.file = new string(new char[256]);
        ofn.maxFile = ofn.file.Length;
        ofn.fileTitle = new string(new char[64]);
        ofn.maxFileTitle = ofn.fileTitle.Length;
        ofn.initialDir = UnityEngine.Application.dataPath;//默认路径
        ofn.title = "打开Excel";
        ofn.defExt = "xlsx";
        ofn.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000200 | 0x00000008;

        //打开windows框
        if (DllTest.GetOpenFileName(ofn))
        {
            ofn.file = ofn.file.Replace("\\", "/");
            FileInfo info = new FileInfo(ofn.file);FileShare.Read);
            FileStream stream = info.Open(FileMode.Open, FileAccess.Read);

            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            DataSet result = excelReader.AsDataSet();
            int columns = result.Tables[0].Columns.Count;//获取列数    240
            int rows = result.Tables[0].Rows.Count;//获取行数          1001
                                                   // Debug.Log("行数:" + rows + "   列数:" + columns);
            for (int i = 0; i < columns; i++)
            {
                for (int j = 1; j < rows; j++)
                {
                   Debug.Log(result.Tables[0].Rows[j][i].ToString());
                }
            }
        }
    }

3、使用协程后台自动写入文件

    string _path;
 IEnumerator SaveEexcel_Times()
    {
        _path = "D:/GIS/shili/test.xlsx";//文件路径
        if (!File.Exists(_path))
        {
            Debug.LogError("文件不存在: " + _path);
            yield return null;
        }
        FileInfo newFile = new FileInfo(_path);
        if (newFile.Exists)
        {
            newFile.Delete();  // ensures we create a new workbook
            newFile = new FileInfo(_path);
        }
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            // 添加一个sheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("信息");
          
            //添加一点测试数据
            List<ImportClass> importClasses = new List<ImportClass>();
            importClasses.Add(new ImportClass("小刚", "789", "789789000"));
            importClasses.Add(new ImportClass("小亮", "147", "147147"));

            worksheet.Cells[1, 1, 1, 6].Merge = true;//合并单元格(1行1列到1行6列)
            worksheet.Cells["A1"].Value = "学生信息"; //显示
            worksheet.Cells["A1"].Style.Font.Size = 16; //字体大小
            worksheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //对其方式
            worksheet.Cells["A1"].Style.Border.BorderAround(ExcelBorderStyle.Thin); //表格边框

            Debug.Log("保存数据成功！");
            package.Save();
        }
    }

4、使用协程后台读取文件

 string _path;
 IEnumerator ReadEexcel_cunhuo()
    {
        _path = "D:/GIS/shili/result.xlsx";
        if (!File.Exists(_path))
        {
            Debug.LogError("文件不存在: " + _path);
            yield return null;
        }
        using (FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read, FileShare.Read))
        {
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); ;//读取 2007及以后的版本
            DataSet result = excelReader.AsDataSet();
            int columns = result.Tables[0].Columns.Count;//获取列数    240
            int rows = result.Tables[0].Rows.Count;//获取行数          1001
                                                   // Debug.Log("行数:" + rows + "   列数:" + columns);
            for (int i = 0; i < columns; i++)
            {
                for (int j = 1; j < rows; j++)
                {
                   Debug.Log(result.Tables[0].Rows[j][i].ToString());
                }
            }
            yield return null;
        }
    }