1、使用Epplus读取
下载地址为https://epplus.codeplex.com/,下载文件后引用Epplus.dll文件。
这个类库读取Excel方便快捷,但是它只能读取.xlsx类型的文件,不支持.xls,具体代码如下:
static void Main(string[] args)
{
//获取excel文件
var file = new FileInfo(@"D:\a.xlsx");
DataTable dt = null;
using (var package = new ExcelPackage(file))
{
//获取workbook
ExcelWorkbook workbook = package.Workbook;
if (workbook != null)
{
if (workbook.Worksheets.Count > 0)
{
//获取workbook的第一个worksheet
ExcelWorksheet worksheet = workbook.Worksheets.First();
//将worksheet转成datatable
dt = WorksheetToTable(worksheet);
}
}
}
if (dt != null)
{
Console.WriteLine(dt.Rows.Count);
Console.WriteLine(dt.Columns.Count);
}
Console.ReadLine();
}
/// <summary>
/// 将worksheet转成datatable
/// </summary>
/// <param name="worksheet">待处理的worksheet</param>
/// <returns>返回处理后的datatable</returns>
private static DataTable WorksheetToTable(ExcelWorksheet worksheet)
{
//获取worksheet的行数
int rows = worksheet.Dimension.End.Row;
//获取worksheet的列数
int cols = worksheet.Dimension.End.Column;
DataTable dt = new DataTable(worksheet.Name);
DataRow dr = null;
for (int i = 1; i <= rows; i++)
{
if (i > 1)
{
dr = dt.Rows.Add();
}
for (int j = 1; j <= cols; j++)
{
//默认将第一行设置为datatable的标题
if (i == 1)
{
dt.Columns.Add(worksheet.Cells[i, j].Value.ToString());
}
//剩下的写入datatable
else
{
dr[j - 1] = worksheet.Cells[i, j].Value.ToString();
}
}
}
return dt;
}
2、使用NPOI读取
下载地址为https://github.com/tonyqus/npoi,下载文件后引用。
这个可以读取.xlsx文件,也能读取.xls文件,具体代码如下:
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
namespace NpoiReadExcel
{
class ExceHelper
{
public static DataTable ExcelToTable(string fileName)
{
DataTable dt = new DataTable();
//将文件转换成stream
using (FileStream fileStream = new FileStream(fileName, FileMode.OpenOrCreate))
{
//根据不同版本创建不同的workbook
IWorkbook workbook;
if (fileName.Contains(".xlsx"))
{
//针对07及以上(.xlsx文件)
workbook = new XSSFWorkbook(fileStream);
}
else
{
//针对03(.xls文件)
workbook = new HSSFWorkbook(fileStream);
}
//获取第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
//获取sheet的第一行
IRow firstRow = sheet.GetRow(0);
//获取sheet的列数
int cellCount = firstRow.LastCellNum;
//第一行作为标题加到datatable中
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
//sheet的行数(这里获取的行数是去除首行的行数)
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
//获取sheet的行数据
IRow row = sheet.GetRow(i);
//因为没有数据的行默认是null,去除空数据
if (row == null)
{
continue;
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
//没有数据的单元格默认是null,去除
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
return dt;
}
}
}