使用NPOI導(dǎo)入導(dǎo)出標(biāo)準Excel

轉(zhuǎn)自http://www.cnblogs.com/lwme/archive/2011/11/18/npoi_excel_import_export.html

?

試過很多Excel導(dǎo)入導(dǎo)出方法，都不太理想，無意中逛到oschina時，發(fā)現(xiàn)了NPOI，無需Office COM組件且不依賴Office，頓時驚為天人，懷著無比激動的心情寫下此文。

曾使用過的方法

直接導(dǎo)出html，修改后綴名為.xls，這個方法有點像騙人的把戲，而且不能再導(dǎo)入

使用Jet OLEDB引擎來進行導(dǎo)入導(dǎo)出，完全使用sql語句來進行操作，缺點能控制的東西非常有限，比如格式就難以控制

使用Office COM組件進行導(dǎo)入導(dǎo)出，對環(huán)境依賴性太強(如“檢索 COM 類工廠…”錯誤)；且需要通過打開Excel.exe進程進行操作；雖然可以通過關(guān)閉工作表以及Marshal.ReleaseComObject方法來釋放資源，但依然避免不了性能差。

關(guān)于NPOI

NPOI是POI項目的.NET版本，是由@Tony Qu(http://tonyqus.cnblogs.com/)等大俠基于POI開發(fā)的，可以從http://npoi.codeplex.com/下載到它的最新版本。它不使用Office COM組件(Microsoft.Office.Interop.XXX.dll)，不需要安裝Microsoft Office，支持對Office 97-2003的文件格式，功能比較強大。更詳細的說明請看作者的博客或官方網(wǎng)站。

它的以下一些特性讓我相當(dāng)喜歡：

支持對標(biāo)準的Excel讀寫

支持對流(Stream)的讀寫 (而Jet OLEDB和Office COM都只能針對文件)

支持大部分Office COM組件的常用功能

性能優(yōu)異 (相對于前面的方法)

使用簡單，易上手

使用NPOI

本文使用的是它當(dāng)前的最新版本1.2.4，此版本的程序集縮減至2個：NPOI.dll、Ionic.Zip.dll，直接引用到項目中即可。

對于我們開發(fā)者使用的對象主要位于NPOI.HSSF.UserModel空間下，主要有HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell，對應(yīng)的接口為位于NPOI.SS.UserModel空間下的IWorkbook、ISheet、IRow、ICell，分別對應(yīng)Excel文件、工作表、行、列。

簡單演示一下創(chuàng)建一個Workbook對象，添加一個工作表，在工作表中添加一行一列：

view sourceprint?

01	using NPOI.HSSF.UserModel;

02	using NPOI.SS.UserModel;

03

04	public class NPOIWrite

05

{

06	??? void CreateSheet()

07

??? {

08	??????? IWorkbook workbook = new HSSFWorkbook();//創(chuàng)建Workbook對象

09	??????? ISheet sheet = workbook.CreateSheet("Sheet1");//創(chuàng)建工作表

10	??????? IRow row = sheet.CreateRow(0);//在工作表中添加一行

11	??????? ICell cell = row.CreateCell(0);//在行中添加一列

12	??????? cell.SetCellValue("test");//設(shè)置列的內(nèi)容

13

??? }

14

}

相應(yīng)的讀取代碼：

view sourceprint?

01	using System.IO;

02	using NPOI.HSSF.UserModel;

03	using NPOI.SS.UserModel;

04

05	public class NPOIRead

06

{

07	??? void GetSheet(Stream stream)

08

??? {

09	??????? IWorkbook workbook = new HSSFWorkbook(stream);//從流內(nèi)容創(chuàng)建Workbook對象

10	??????? ISheet sheet = workbook.GetSheetAt(0);//獲取第一個工作表

11	??????? IRow row = sheet.GetRow(0);//獲取工作表第一行

12	??????? ICell cell = row.GetCell(0);//獲取行的第一列

13	??????? string value = cell.ToString();//獲取列的值

14

??? }

15

}

使用NPOI導(dǎo)出

從DataTable讀取內(nèi)容來創(chuàng)建Workbook對象：

view sourceprint?

01	public static MemoryStream RenderToExcel(DataTable table)

02

{

03	??? MemoryStream ms = new MemoryStream();

04

05	??? using (table)

06

??? {

07	??????? using (IWorkbook workbook = new HSSFWorkbook())

08

??????? {

09	??????????? using (ISheet sheet = workbook.CreateSheet())

10	??????????? {

11	??????????????? IRow headerRow = sheet.CreateRow(0);

12

13	??????????????? // handling header.

14	??????????????? foreach (DataColumn column in table.Columns)

15	??????????????????? headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

16

17	??????????????? // handling value.

18	??????????????? int rowIndex = 1;

19

20	??????????????? foreach (DataRow row in table.Rows)

21	??????????????? {

22	??????????????????? IRow dataRow = sheet.CreateRow(rowIndex);

23

24	??????????????????? foreach (DataColumn column in table.Columns)

25	??????????????????? {

26	??????????????????????? dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

27	??????????????????? }

28

29	??????????????????? rowIndex++;

30	??????????????? }

31

32	??????????????? workbook.Write(ms);

33	??????????????? ms.Flush();

34	??????????????? ms.Position = 0;

35	??????????? }

36

??????? }

37

??? }

38	??? return ms;

39

}

如果看不慣DataTable，那么DataReader也行：

view sourceprint?

01	public static MemoryStream RenderToExcel(IDataReader reader)

02

{

03	??? MemoryStream ms = new MemoryStream();

04

05	??? using (reader)

06

??? {

07	??????? using (IWorkbook workbook = new HSSFWorkbook())

08

??????? {

09	??????????? using (ISheet sheet = workbook.CreateSheet())

10	??????????? {

11	??????????????? IRow headerRow = sheet.CreateRow(0);

12	??????????????? int cellCount = reader.FieldCount;

13

14	??????????????? // handling header.

15	??????????????? for (int i = 0; i < cellCount; i++)

16	??????????????? {

17	??????????????????? headerRow.CreateCell(i).SetCellValue(reader.GetName(i));

18	??????????????? }

19

20	??????????????? // handling value.

21	??????????????? int rowIndex = 1;

22	??????????????? while (reader.Read())

23	??????????????? {

24	??????????????????? IRow dataRow = sheet.CreateRow(rowIndex);

25

26	??????????????????? for (int i = 0; i < cellCount; i++)

27	??????????????????? {

28	??????????????????????? dataRow.CreateCell(i).SetCellValue(reader[i].ToString());

29	??????????????????? }

30

31	??????????????????? rowIndex++;

32	??????????????? }

33

34	??????????????? workbook.Write(ms);

35	??????????????? ms.Flush();

36	??????????????? ms.Position = 0;

37	??????????? }

38

??????? }

39

??? }

40	??? return ms;

41

}

以上代碼把創(chuàng)建的Workbook對象保存到流中，可以通過以下方法輸出到瀏覽器，或是保存到硬盤中：

view sourceprint?

01	static void SaveToFile(MemoryStream ms, string fileName)

02

{

03	??? using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))

04

??? {

05	??????? byte[] data = ms.ToArray();

06

07	??????? fs.Write(data, 0, data.Length);

08	??????? fs.Flush();

09

10	??????? data = null;

11

??? }

12

}

13

14	static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)

15

{

16	??? if (context.Request.Browser.Browser == "IE")

17	??????? fileName = HttpUtility.UrlEncode(fileName);

18	??? context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);

19	??? context.Response.BinaryWrite(ms.ToArray());

20

}

使用NPOI導(dǎo)入

需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，這里可能存在BUG：當(dāng)沒有數(shù)據(jù)或只有一行數(shù)據(jù)時sheet.LastRowNum為0，PhysicalNumberOfRows 表現(xiàn)正常。

這里讀取流中的Excel來創(chuàng)建Workbook對象，并轉(zhuǎn)換成DataTable：

view sourceprint?

01	static DataTable RenderFromExcel(Stream excelFileStream)

02

{

03	??? using (excelFileStream)

04

??? {

05	??????? using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))

06

??????? {

07	??????????? using (ISheet sheet = workbook.GetSheetAt(0))//取第一個表

08	??????????? {

09	??????????????? DataTable table = new DataTable();

10

11	??????????????? IRow headerRow = sheet.GetRow(0);//第一行為標(biāo)題行

12	??????????????? int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells

13	??????????????? int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

14

15	??????????????? //handling header.

16	??????????????? for (int i = headerRow.FirstCellNum; i < cellCount; i++)

17	??????????????? {

18	??????????????????? DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

19	??????????????????? table.Columns.Add(column);

20	??????????????? }

21

22	??????????????? for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)

23	??????????????? {

24	??????????????????? IRow row = sheet.GetRow(i);

25	??????????????????? DataRow dataRow = table.NewRow();

26

27	??????????????????? if (row != null)

28	??????????????????? {

29	??????????????????????? for (int j = row.FirstCellNum; j < cellCount; j++)

30	??????????????????????? {

31	??????????????????????????? if (row.GetCell(j) != null)

32	??????????????????????????????? dataRow[j] = GetCellValue(row.GetCell(j));

33	??????????????????????? }

34	??????????????????? }

35

36	??????????????????? table.Rows.Add(dataRow);

37	??????????????? }

38	??????????????? return table;

39

40	??????????? }

41

??????? }

42

??? }

43

}

或者是直接生成SQL語句來插入到數(shù)據(jù)庫：

view sourceprint?

01	public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction)

02

{

03	??? int rowAffected = 0;

04	??? using (excelFileStream)

05

??? {

06	??????? using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))

07

??????? {

08	??????????? using (ISheet sheet = workbook.GetSheetAt(0))//取第一個工作表

09	??????????? {

10	??????????????? StringBuilder builder = new StringBuilder();

11

12	??????????????? IRow headerRow = sheet.GetRow(0);//第一行為標(biāo)題行

13	??????????????? int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells

14	??????????????? int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

15

16	??????????????? for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)

17	??????????????? {

18	??????????????????? IRow row = sheet.GetRow(i);

19	??????????????????? if (row != null)

20	??????????????????? {

21	??????????????????????? builder.Append(insertSql);

22	??????????????????????? builder.Append(" values (");

23	??????????????????????? for (int j = row.FirstCellNum; j < cellCount; j++)

24	??????????????????????? {

25	??????????????????????????? builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));

26	??????????????????????? }

27	??????????????????????? builder.Length = builder.Length - 1;

28	??????????????????????? builder.Append(");");

29	??????????????????? }

30

31	??????????????????? if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)

32	??????????????????? {

33	??????????????????????? //每50條記錄一次批量插入到數(shù)據(jù)庫

34	??????????????????????? rowAffected += dbAction(builder.ToString());

35	??????????????????????? builder.Length = 0;

36	??????????????????? }

37	??????????????? }

38	??????????? }

39

??????? }

40

??? }

41	??? return rowAffected;

42

}

這里的Excel可能沒有數(shù)據(jù)，所以可以加一個方法來檢測：

view sourceprint?

01	public static bool HasData(Stream excelFileStream)

02

{

03	??? using (excelFileStream)

04

??? {

05	??????? using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))

06

??????? {

07	??????????? if (workbook.NumberOfSheets > 0)

08	??????????? {

09	??????????????? using (ISheet sheet = workbook.GetSheetAt(0))

10	??????????????? {

11	??????????????????? return sheet.PhysicalNumberOfRows > 0;

12	??????????????? }

13	??????????? }

14

??????? }

15

??? }

16	??? return false;

17

}

結(jié)尾

好吧，不說啥了，放代碼：點擊下載

作者：囧月
出處：http://lwme.cnblogs.com/
本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責(zé)任的權(quán)利。

posted on 2011-11-20 11:14 NET未來之路 閱讀(...) 評論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/lonelyxmas/archive/2011/11/20/2255853.html

總結(jié)

以上是生活随笔為你收集整理的使用NPOI导入导出标准Excel的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。