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;
}
View Code
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;
}
}
}