嘗試過很多Excel導入導出方法,都不太理想,無意中逛到oschina時,發(fā)現(xiàn)了NPOI,無需Office COM組件且不依賴Office,頓時驚為天人,懷著無比激動的心情寫下此文。
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)站。
它的以下一些特性讓我相當喜歡:
本文使用的是它當前的最新版本1.2.4,此版本的程序集縮減至2個:NPOI.dll、Ionic.Zip.dll,直接引用到項目中即可。
對于我們開發(fā)者使用的對象主要位于NPOI.HSSF.UserModel空間下,主要有HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell,對應的接口為位于NPOI.SS.UserModel空間下的IWorkbook、ISheet、IRow、ICell,分別對應Excel文件、工作表、行、列。
簡單演示一下創(chuàng)建一個Workbook對象,添加一個工作表,在工作表中添加一行一列:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; public class NPOIWrite {
void CreateSheet()
{
IWorkbook workbook = new HSSFWorkbook(); //創(chuàng)建Workbook對象
ISheet sheet = workbook.CreateSheet( "Sheet1" ); //創(chuàng)建工作表
IRow row = sheet.CreateRow(0); //在工作表中添加一行
ICell cell = row.CreateCell(0); //在行中添加一列
cell.SetCellValue( "test" ); //設置列的內容
} } |
相應的讀取代碼:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; public class NPOIRead {
void GetSheet(Stream stream)
{
IWorkbook workbook = new HSSFWorkbook(stream); //從流內容創(chuàng)建Workbook對象
ISheet sheet = workbook.GetSheetAt(0); //獲取第一個工作表
IRow row = sheet.GetRow(0); //獲取工作表第一行
ICell cell = row.GetCell(0); //獲取行的第一列
string value = cell.ToString(); //獲取列的值
} } |
從DataTable讀取內容來創(chuàng)建Workbook對象:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | public static MemoryStream RenderToExcel(DataTable table) {
MemoryStream ms = new MemoryStream();
using (table)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return ms; } |
如果看不慣DataTable,那么DataReader也行:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | public static MemoryStream RenderToExcel(IDataReader reader) {
MemoryStream ms = new MemoryStream();
using (reader)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
int cellCount = reader.FieldCount;
// handling header.
for ( int i = 0; i < cellCount; i++)
{
headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
}
// handling value.
int rowIndex = 1;
while (reader.Read())
{
IRow dataRow = sheet.CreateRow(rowIndex);
for ( int i = 0; i < cellCount; i++)
{
dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return ms; } |
以上代碼把創(chuàng)建的Workbook對象保存到流中,可以通過以下方法輸出到瀏覽器,或是保存到硬盤中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | static void SaveToFile(MemoryStream ms, string fileName) {
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte [] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null ;
} } static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName) {
if (context.Request.Browser.Browser == "IE" )
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader( "Content-Disposition" , "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray()); } |
需要注意的是,sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1,這里可能存在BUG:當沒有數(shù)據(jù)或只有一行數(shù)據(jù)時sheet.LastRowNum為0,PhysicalNumberOfRows 表現(xiàn)正常。
這里讀取流中的Excel來創(chuàng)建Workbook對象,并轉換成DataTable:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | static DataTable RenderFromExcel(Stream excelFileStream) {
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(0)) //取第一個表
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0); //第一行為標題行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for ( int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for ( int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null )
{
for ( int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null )
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
}
return table;
}
}
} } |
或者是直接生成SQL語句來插入到數(shù)據(jù)庫:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction) {
int rowAffected = 0;
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(0)) //取第一個工作表
{
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(0); //第一行為標題行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
for ( int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null )
{
builder.Append(insertSql);
builder.Append( " values (" );
for ( int j = row.FirstCellNum; j < cellCount; j++)
{
builder.AppendFormat( "''{0}''," , GetCellValue(row.GetCell(j)).Replace( "''" , "''''" ));
}
builder.Length = builder.Length - 1;
builder.Append( ");" );
}
if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
{
//每50條記錄一次批量插入到數(shù)據(jù)庫
rowAffected += dbAction(builder.ToString());
builder.Length = 0;
}
}
}
}
}
return rowAffected; } |
這里的Excel可能沒有數(shù)據(jù),所以可以加一個方法來檢測: