Unity Excel的读取写入

废话不多说，对 Excel 表的操作少不了要引入第三方库，首先我们需要引入 Excel.dll 和 ICSharpCode.SharpZipLib.dll，这两个类库在网上都能找到；然后我们还需要引入 System.Data.dll，这个类库在 Unity3D 的安装路径下的 Editor\Data\Mono\lib\mono\unity 文件夹下能找到。

using UnityEngine;
using System.Collections;
using System.IO;
using OfficeOpenXml;
using Excel;
using System.Data;
using UnityEngine.UI;

public class Excels : MonoBehaviour
{
    string Ename = "test", sheet = "Sheet1";
    public Text fd;
    public Button write, read;
    void Start()
    {
        string gg = Ename + ".xlsx";
        read.onClick.AddListener(delegate () { ReadExcel2(gg, sheet); });
        write.onClick.AddListener(delegate () { WriteExcel(gg, sheet); });
    }

    /// <summary>
    /// 读取 Excel 
    /// </summary>
    /// <param name="excelName"></param>
    /// <param name="sheetName"></param>
    public void ReadExcel(string excelName, string sheetName)
    {
        FileStream stream = File.Open(Application.streamingAssetsPath + "/" + excelName, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelReadder = ExcelReaderFactory.CreateOpenXmlReader(stream);


        DataSet result = excelReadder.AsDataSet();

        int columns = result.Tables[0].Columns.Count;
        int rows = result.Tables[0].Rows.Count;

        for (int i = 0; i < rows; i++)
        {
            for (int j = 0; j < columns; j++)
            {
                string gg = result.Tables[0].Rows[i][j].ToString();
                if (j % 3 == 0 && j != 0)
                {
                    fd.text += gg + "\n";
                }
                else
                {
                    fd.text += gg + "\t";
                }
            }
        }
    }


    void ReadExcel2(string excelName, string sheetName)
    {
        fd.text = "读取";
        FileStream stream = File.Open(Application.streamingAssetsPath + "/" + excelName, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        do
        {
            // sheet name
            Debug.Log(excelReader.Name);
            while (excelReader.Read())
            {
                for (int i = 0; i < excelReader.FieldCount; i++)
                {
                    string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
                    //fd.text = "读取";
                    fd.text += value;
                }
            }
        } while (excelReader.NextResult());
    }
}

如果这样写，发布后运行，也许会报错，这时我们就又需要引入第三方库了，去 Unity3D 安装路径下的Editor\Data\Mono\lib\mono\unity，找到所有 I18N 开头的类库导入Unity中，就不会报错了，如下图：

Excel 表的读取功能解决了，那我们如何生成一张 Excel 表，并写入数据呢？这时我们需要导入一个叫 EPPlus.dll 的类库，网上也有，大家可以自己下载。

代码如下：

 /// <summary>
    /// 写入 Excel ; 需要添加 OfficeOpenXml.dll;
    /// </summary>
    /// <param name="excelName">excel文件名</param>
    /// <param name="sheetName">sheet名称</param>
     void WriteExcel()
    {
        fd.text = "写入";
        string outputDir = Application.streamingAssetsPath + "/Test.xlsx";
        FileInfo newFile = new FileInfo(outputDir);
        if (newFile.Exists)
        {
            newFile.Delete();  // ensures we create a new workbook
            newFile = new FileInfo(outputDir);
        }
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            // add a new worksheet to the empty workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
            //Add the headers
            worksheet.Cells[1, 1].Value = "ID";
            worksheet.Cells[1, 2].Value = "Product";
            worksheet.Cells[1, 3].Value = "Quantity";
            worksheet.Cells[1, 4].Value = "Price";
            worksheet.Cells[1, 5].Value = "Value";

            //Add some items...
            worksheet.Cells["A2"].Value = 12001;
            worksheet.Cells["B2"].Value = "Nails";
            worksheet.Cells["C2"].Value = 37;
            worksheet.Cells["D2"].Value = 3.99;

            worksheet.Cells["A3"].Value = 12002;
            worksheet.Cells["B3"].Value = "Hammer";
            worksheet.Cells["C3"].Value = 5;
            worksheet.Cells["D3"].Value = 12.10;

            worksheet.Cells["A4"].Value = 12003;
            worksheet.Cells["B4"].Value = "Saw";
            worksheet.Cells["C4"].Value = 12;
            worksheet.Cells["D4"].Value = 15.37;

            //save our new workbook and we are done!
            package.Save();
        }
   }

好了，Excel 表的读写操作就实现了。