需要加入库文件 Excel.dll 和ICSharpCode.SharpZipLib库文件,官方链接 http://exceldatareader.codeplex.com/
然后引入 excle命名空间(using Excel;)
1.将excle数据读取并存入bytes文件
FileStream stream = File.Open(文件路径, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
ExcelDataSet set = new ExcelDataSet();//protobuf中定义的类
do
{
ExcelDataTable table = new ExcelDataTable();
// sheet name
while (excelReader.Read())//读取一行
{
ExcelDataRow row = new ExcelDataRow();
for (int i = 0; i < excelReader.FieldCount; i++)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);//读取数据是否为null ,不为空取出数据
row.Cells.Add(value);
}
table.Rows.Add(row);
}
if (table.Rows.Count > 0)
set.Tables.Add(table);
} while (excelReader.NextResult());
//protobuf中的转化二进制方法
var bytes = set.ToByteArray();
//写入文件
File.WriteAllBytes(targetPath, bytes);
2.读取excle生成的bytes文件
public class ReadExcel
{
/// <summary>
/// 只读Excel方法
/// </summary>
/// <param name="ExcelPath"></param>
/// <returns></returns>
public static DataSet GameReadExcel(string ExcelPath)
{
TextAsset asset = ResourceManager.Instance.Load<TextAsset>(ExcelPath);//加载bytes文件
var dataset = ExcelDataSet.Parser.ParseFrom(asset.bytes);//读取的文件转化为proto类
//dataset.Tables[0].Rows[]
DataSet set = ExcelUtils.ConvertDataSet(dataset);//将proto类转化为DataSet类型
return set;
}
}
public class ExcelUtils : ScriptableObject
{
//将protobuf中的数据转化成DataSet
public static DataSet ConvertDataSet(ExcelDataSet dataSet )
{
DataSet set = new DataSet();
for (int i = 0; i < dataSet.Tables.Count; i++)
{
var tableData = dataSet.Tables[i];
DataTable table = new DataTable();
int columnCount = dataSet.Tables[i].Rows[0].Cells.Count;
int rowCount = dataSet.Tables[i].Rows.Count;
for (int k = 0; k < columnCount; k++)
{
table.Columns.Add(new DataColumn());
}
for (int m = 0; m < rowCount; m++)
{
object[] rowData = new object[columnCount];
for (int n = 0; n < columnCount; n++)
{
rowData[n] = tableData.Rows[m].Cells[n];
}
table.Rows.Add(rowData);
}
set.Tables.Add(table);
}
return set;
}
}
3.使用excle数据
public DataSet skillData;
skillData = ReadExcel.GameReadExcel(skillPath);
int rows = skillData.Tables[0].Rows.Count;//第一张表的行数
int itemcount = skillData.Tables[0].Rows[0].ItemArray.Length;//第一张表第一行的列数
string string_item skillTable.Rows[i][j].ToString();