获取及安装EXCEL支持库
新建项目,通过管理NuGet程序包获取ExcelDataReader库文件
选中浏览,输入EXCEL,可用看到ExcelDataReader,ExcelDataReader.DataSet两个库文件
选中安装后
点击二OK完成安装
ExcelDataReader.DataSet库的安装同上
读取EXCEL工作簿中的各个工作表
/// <summary>
/// 读取Excel
/// </summary>
private void excelRead()
{
string ExcelFile = System.AppDomain.CurrentDomain.BaseDirectory + "Data.xlsx";
//1:打开文件,得到文件stream
var streamData = File.Open(ExcelFile, FileMode.Open, FileAccess.Read);
//var streamDataWrite = File .WriteAllText (ExcelFile, FileMode.Open, FileAccess.Read)
//2:得到文件reader(需要NuGet包ExcelDataReader)
var readerData = ExcelReaderFactory.CreateOpenXmlReader(streamData);
//3:通过reader得到数据(需要NuGet包ExcelDataReader.DataSet )
var result = readerData.AsDataSet();
//4:得到ExcelFile文件的表Sheet
var sheet = result.Tables[0];
var columns = sheet.Columns.Count;//ExcelFile的表Sheet的列数
var rows = sheet.Rows.Count;//Sheet中的行数
string[][] arrayValue = new string[rows][];
for (int i = 0; i < arrayValue.Length ; i++)
{
arrayValue[i]= new string [columns];
}
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
string strValue = result.Tables[0].Rows[i][j].ToString();
arrayValue[i][j]= strValue;
}
}
FileStream stream = File.Open(ExcelFile, 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(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result1 = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//excelReader.IsFirstRowAsColumnNames = true;
//DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
//while (excelReader.Read())
//{
//excelReader.GetInt32(0);
//}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}