废话不多说,对 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 表的读写操作就实现了。