使用ExcelDataReader库https://github.com/ExcelDataReader/ExcelDataReader
可用vs nuget 获取 ExcelDataReader和ExcelDataReader.DataSet库;
void ReadExcel(Stream _s)
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(_s);
var _ds = excelReader.AsDataSet(new ExcelDataSetConfiguration() {
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
},
UseColumnDataType = false
});
//var _ds = excelReader.AsDataSet();
DataRowCollection _rows = _ds.Tables[0].Rows;
for (int i = 1; i < _rows.Count; i++)
{
//string _str = "工作项-" + _rows[i][0] + " 工作内容-" + _rows[i][1] + " 责任人-" + _rows[i][2];
Debug.Log(_str);
//_text.text += _str;
}
Debug.Log("ExcelReader--Read()");
string sheetName = excelReader.Name;
int sheetfield = excelReader.FieldCount;
int sheetCount = excelReader.ResultsCount;
Debug.Log("SheetCount:" + sheetCount + " SheetField:" + sheetfield + " of SheetName:" + sheetName);
//while (excelReader.Read())
//{
// Debug.Log(excelReader.GetString(0));
// Debug.Log(excelReader.GetString(1));
// Debug.Log(excelReader.GetString(2));
//
//}
excelReader.Close();
}
---------------------------------------------------------------------------------------------
如果报错:InvalidCastException: Cannot cast from source type to destination type.
System.Data.Common.DoubleDataContainer.DoCopyValue (System.Data.Common.DataContainer from, Int32 from_index, Int32 to_index)
System.Data.Common.DataContainer.CopyValue (System.Data.Common.DataContainer from, Int32 from_index, Int32 to_index)
System.Data.Common.RecordCache.CopyRecord (System.Data.DataTable fromTable, Int32 fromRecordIndex, Int32 toRecordIndex)
调用AsDataSet时,出现InvalidCastException: Cannot cast from source type to destination type.错误,是由于Excel的cell中没有内容导致,可以用如下方法绕过这个错误:
DataSet ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
},
UseColumnDataType = false
});