在网上看到很多 的解析Excel 的文章,其中最经典的一篇莫过于雨凇Momo的Unity3D研究院之MAC&Windows跨平台解析Excel(六十五)
但是在使用的过程中还是碰到了不少的 问题 ,在这里总结一下,希望能对看到此处的朋友一个帮助。
Excel的读取,
需要加入库文件 Excel.dll 和ICSharpCode.SharpZipLib库文
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
using Excel;
using System.Data;
Excel文件读取和转换List格式
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public class ExcelAccess
{
public static string ExcelName = "Book.xlsx";
public static string[] SheetNames = { "sheet1", "sheet2", "sheet3", "sheet4" };
public static List<Menu> SelectMenuTable(int tableId)
{
DataRowCollection collect = ExcelAccess.ReadExcel(SheetNames[tableId - 1]);
List<Menu> menuArray = new List<Menu>();
for (int i = 1; i < collect.Count; i++)
{
if (collect[i][1].ToString() == "") continue;
Menu menu = new Menu();
menu.m_Id = collect[i][0].ToString();
menu.m_level = collect[i][1].ToString();
menu.m_parentId = collect[i][2].ToString();
menu.m_name = collect[i][3].ToString();
menuArray.Add(menu);
}
return menuArray;
}
/// <summary>
/// 读取 Excel 需要添加 Excel; System.Data;
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
static DataRowCollection ReadExcel(string sheet)
{
FileStream stream = File.Open(FilePath(ExcelName), FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//int columns = result.Tables[0].Columns.Count;
//int rows = result.Tables[0].Rows.Count;
return result.Tables[sheet].Rows;
}
}
这里逻辑很简单,如果有不懂得可以上Excel的文档里去看,但是这个Excel的库有一个限制,就是只能读不能写,并且只能在编辑器下用,如果打包出来加载时会报空指针异常,原因就不清楚了,所以建议大家,让策划把Excel写好后,在编辑器下读取后用Unity 的ScriptableObject 存起来,然后保存成Asset文件,可以在运行时更方便的读取。
下面给出我的实现方式,大家可以根据自己实体类来写这个StrignHolder;
StringHolder类
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
using UnityEngine;
using System.Collections.Generic;
public class BookElementHolder : ScriptableObject
{
public List<Menu> menus1;
public List<Menu> menus2;
public List<Menu> menus3;
public List<Good> goods;
}
制作Asset编辑器(可以把Asset达成AssetBundle的包,然后用WWW读取)
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
[MenuItem("Assetbundles/Create Assetbundles")]
public static void ExcuteBuild()
{
BookElementHolder holder = ScriptableObject.CreateInstance<BookElementHolder>();
holder.menus1 = ExcelAccess.SelectMenuTable(1);
holder.menus2 = ExcelAccess.SelectMenuTable(2);
holder.menus3 = ExcelAccess.SelectMenuTable(3);
holder.goods = ExcelAccess.SelectGoodTable();
AssetDatabase.CreateAsset(holder, HolderPath);
AssetImporter import = AssetImporter.GetAtPath(HolderPath);
import.assetBundleName = "booknames";
BuildPipeline.BuildAssetBundles("Assets/Abs");
Debug.Log("BuildAsset Success!");
}
Asset读取方式
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public string assetName = "bonenames";
public void readAsset()
{
Object asset = Resources.Load<Object>(assetName);
BookElementHolder ceh = (BookElementHolder)asset;
foreach (Good gd in ceh.goods)
{
Debug.Log(gd.m_name);
}
}
好了,Excel的读取就到这里,接下来讲一下Excel 的写入,怎么生成一个Excel文件,并把数组或字典中的数据写入Excel中呢?
此时需要一个Excel.dll的姐妹,EPPlus.dll 官方链接 https://epplus.codeplex.com/releases/view/118053
使用方法在官方的文档中都有,这里只贴出我的实现方式。
需要添加的命名空间
[csharp] view plain copy
using OfficeOpenXml;
写入方法
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public static void WriteExcel(string outputDir)
{
//string outputDir = EditorUtility.SaveFilePanel("Save Excel", "", "New Resource", "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();
}
}
把上面的数据换成你自己的数组和字典遍历就OK 了。好了,今天的课程就到这里,欢迎大神指教啊
但是在使用的过程中还是碰到了不少的 问题 ,在这里总结一下,希望能对看到此处的朋友一个帮助。
Excel的读取,
需要加入库文件 Excel.dll 和ICSharpCode.SharpZipLib库文
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
using Excel;
using System.Data;
Excel文件读取和转换List格式
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public class ExcelAccess
{
public static string ExcelName = "Book.xlsx";
public static string[] SheetNames = { "sheet1", "sheet2", "sheet3", "sheet4" };
public static List<Menu> SelectMenuTable(int tableId)
{
DataRowCollection collect = ExcelAccess.ReadExcel(SheetNames[tableId - 1]);
List<Menu> menuArray = new List<Menu>();
for (int i = 1; i < collect.Count; i++)
{
if (collect[i][1].ToString() == "") continue;
Menu menu = new Menu();
menu.m_Id = collect[i][0].ToString();
menu.m_level = collect[i][1].ToString();
menu.m_parentId = collect[i][2].ToString();
menu.m_name = collect[i][3].ToString();
menuArray.Add(menu);
}
return menuArray;
}
/// <summary>
/// 读取 Excel 需要添加 Excel; System.Data;
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
static DataRowCollection ReadExcel(string sheet)
{
FileStream stream = File.Open(FilePath(ExcelName), FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//int columns = result.Tables[0].Columns.Count;
//int rows = result.Tables[0].Rows.Count;
return result.Tables[sheet].Rows;
}
}
这里逻辑很简单,如果有不懂得可以上Excel的文档里去看,但是这个Excel的库有一个限制,就是只能读不能写,并且只能在编辑器下用,如果打包出来加载时会报空指针异常,原因就不清楚了,所以建议大家,让策划把Excel写好后,在编辑器下读取后用Unity 的ScriptableObject 存起来,然后保存成Asset文件,可以在运行时更方便的读取。
下面给出我的实现方式,大家可以根据自己实体类来写这个StrignHolder;
StringHolder类
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
using UnityEngine;
using System.Collections.Generic;
public class BookElementHolder : ScriptableObject
{
public List<Menu> menus1;
public List<Menu> menus2;
public List<Menu> menus3;
public List<Good> goods;
}
制作Asset编辑器(可以把Asset达成AssetBundle的包,然后用WWW读取)
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
[MenuItem("Assetbundles/Create Assetbundles")]
public static void ExcuteBuild()
{
BookElementHolder holder = ScriptableObject.CreateInstance<BookElementHolder>();
holder.menus1 = ExcelAccess.SelectMenuTable(1);
holder.menus2 = ExcelAccess.SelectMenuTable(2);
holder.menus3 = ExcelAccess.SelectMenuTable(3);
holder.goods = ExcelAccess.SelectGoodTable();
AssetDatabase.CreateAsset(holder, HolderPath);
AssetImporter import = AssetImporter.GetAtPath(HolderPath);
import.assetBundleName = "booknames";
BuildPipeline.BuildAssetBundles("Assets/Abs");
Debug.Log("BuildAsset Success!");
}
Asset读取方式
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public string assetName = "bonenames";
public void readAsset()
{
Object asset = Resources.Load<Object>(assetName);
BookElementHolder ceh = (BookElementHolder)asset;
foreach (Good gd in ceh.goods)
{
Debug.Log(gd.m_name);
}
}
好了,Excel的读取就到这里,接下来讲一下Excel 的写入,怎么生成一个Excel文件,并把数组或字典中的数据写入Excel中呢?
此时需要一个Excel.dll的姐妹,EPPlus.dll 官方链接 https://epplus.codeplex.com/releases/view/118053
使用方法在官方的文档中都有,这里只贴出我的实现方式。
需要添加的命名空间
[csharp] view plain copy
using OfficeOpenXml;
写入方法
[csharp] view plain copy 在CODE上查看代码片派生到我的代码片
public static void WriteExcel(string outputDir)
{
//string outputDir = EditorUtility.SaveFilePanel("Save Excel", "", "New Resource", "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();
}
}
把上面的数据换成你自己的数组和字典遍历就OK 了。好了,今天的课程就到这里,欢迎大神指教啊