需要加入库文件 Excel.dll 和ICSharpCode.SharpZipLib库文件,官方链接 http://exceldatareader.codeplex.com/
链接:https://pan.baidu.com/s/135RaegkW_FLObGDe2Wn5nA
提取码:bi8t
读取Excel
using UnityEngine;
using System.IO;
using OfficeOpenXml;
public class ExcelReader : MonoBehaviour
{
private static string _excellName = "游戏评测数据";
void Start()
{
string path = Directory.GetCurrentDirectory() + "/" + _excellName + ".xlsx";
LoadExcel(path);
}
public void LoadExcel(string path)
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (ExcelPackage excel = new ExcelPackage(fs))
{
ExcelWorksheets workSheets = excel.Workbook.Worksheets;
for (int i = 1; i <= workSheets.Count; i++)
{
ExcelWorksheet worksheet = workSheets[i];
int colCount = worksheet.Dimension.End.Column;
Debug.LogFormat("sheet {0}", worksheet.Name);
for (int row = 1, count = worksheet.Dimension.End.Row; row <= count; row++)
{
for (int col = 1; col <= colCount; col++)
{
var text = worksheet.Cells[row, col].Text ?? "";
Debug.LogFormat("下标:{0},{1} 内容:{2}", row, col, text);
}
}
}
}
}
}
}
写入Excel
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using OfficeOpenXml;
using Excel;
using System.Data;
public class ExcelWrite : MonoBehaviour
{
private static string _excellName = "游戏评测数据";
private static string _excellPath = "";
void Update()
{
if (Input.GetKeyDown(KeyCode.Space))
{
AddToExcel("test", string.Format("连击数,时间,踩中数,总分|{0},{1},{2},{3}", "6fff0", "50x", "523555523", "555555"));
}
}
#region 添加进表格
public static void AddToExcel(string sheetName, string Info)
{
//自定义excel的路径
//string path = Application.streamingAssetsPath + "/" + _excellName + ".xlsx";
if (_excellPath == "")
{
_excellPath = Directory.GetCurrentDirectory() + "/" + _excellName + ".xlsx";
}
FileInfo newFile = new FileInfo(_excellPath);
ExcelWorksheet worksheet = null;
string[] messages = Info.Split('|');//索引字段名| 参数
if (!newFile.Exists)
{
//通过ExcelPackage打开文件
using (ExcelPackage package = new ExcelPackage(newFile))
{
worksheet = package.Workbook.Worksheets.Add(sheetName);
string[] fieldNameS = messages[0].Split(',');
for (int i = 0; i < fieldNameS.Length; i++)
{
worksheet.Cells[1, i + 1].Value = fieldNameS[i];
}
//保存excel
package.Save();
//Debug.LogWarning("The _excelName is wrong!");
}
}
//通过ExcelPackage打开文件
using (ExcelPackage package = new ExcelPackage(newFile))
{
// StreamingAssets目录下的 党员信息.xlsx文件的路径:Application.streamingAssetsPath + "/党员信息.xlsx"
FileStream fileStream = File.Open(_excellPath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
// 表格数据全部读取到result里
DataSet result = excelDataReader.AsDataSet();
int rowId = 1;
if (result.Tables.Contains(sheetName))
{
worksheet = package.Workbook.Worksheets[sheetName];
// 获取表格有多少行
rowId = result.Tables[sheetName].Rows.Count + 1;
}
else
{
worksheet = package.Workbook.Worksheets.Add(sheetName);
string[] fieldNameS = messages[0].Split(',');
for (int i = 0; i < fieldNameS.Length; i++)
{
worksheet.Cells[1, i + 1].Value = fieldNameS[i];
}
rowId = 2;
}
//游戏参数
string[] paramArray = messages[1].Split(',');
for (int i = 0; i < paramArray.Length; i++)
{
worksheet.Cells[NunberToChar(i) + rowId].Value = paramArray[i];
}
//保存excel
package.Save();
//print("添加新数据完成");
}
}
#endregion
//根据字母表索引字母 如0为A,1为B
private static string NunberToChar(int number)
{
if (number >= 0 && number <= 35)
{
int num = number + 65;
System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
byte[] btNumber = new byte[] { (byte)num };
return asciiEncoding.GetString(btNumber);
}
Debug.LogWarning("数字不在转换范围内");
return "数字不在转换范围内";
}
}