引言
項(xiàng)目中常用到將數(shù)據(jù)導(dǎo)入Excel，將Excel中的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)的功能，曾經(jīng)也查找過(guò)相關(guān)的內(nèi)容，將曾經(jīng)用過(guò)的方案總結(jié)一下。
方案一
NPOI
NPOI 是 POI 項(xiàng)目的 .NET 版本。POI是一個(gè)開(kāi)源的Java讀寫(xiě)Excel、WORD等微軟OLE2組件文檔的項(xiàng)目。
使用 NPOI 你就可以在沒(méi)有安裝 Office 或者相應(yīng)環(huán)境的機(jī)器上對(duì) WORD/EXCEL 文檔進(jìn)行讀寫(xiě)。NPOI是構(gòu)建在POI 3.x版本之上的，它可以在沒(méi)有安裝Office的情況下對(duì)Word/Excel文檔進(jìn)行讀寫(xiě)操作。
優(yōu)勢(shì)
(一)傳統(tǒng)操作Excel遇到的問(wèn)題：
1、如果是.NET，需要在服務(wù)器端裝Office，且及時(shí)更新它，以防漏洞，還需要設(shè)定權(quán)限允許.NET訪(fǎng)問(wèn)COM+，如果在導(dǎo)出過(guò)程中出問(wèn)題可能導(dǎo)致服務(wù)器宕機(jī)。
2、Excel會(huì)把只包含數(shù)字的列進(jìn)行類(lèi)型轉(zhuǎn)換，本來(lái)是文本型的，Excel會(huì)將其轉(zhuǎn)成數(shù)值型的，比如編號(hào)000123會(huì)變成123。
3、導(dǎo)出時(shí)，如果字段內(nèi)容以“-”或“=”開(kāi)頭，Excel會(huì)把它當(dāng)成公式進(jìn)行，會(huì)報(bào)錯(cuò)。
4、Excel會(huì)根據(jù)Excel文件前8行分析數(shù)據(jù)類(lèi)型，如果正好你前8行某一列只是數(shù)字，那它會(huì)認(rèn)為該列為數(shù)值型，自動(dòng)將該列轉(zhuǎn)變成類(lèi)似1.42702E+17格式，日期列變成包含日期和數(shù)字的。
(二)使用NPOI的優(yōu)勢(shì)
1、您可以完全免費(fèi)使用該框架
2、包含了大部分EXCEL的特性(單元格樣式、數(shù)據(jù)格式、公式等等)
3、專(zhuān)業(yè)的技術(shù)支持服務(wù)(24*7全天候) (非免費(fèi))
4、支持處理的文件格式包括xls, xlsx, docx.
5、采用面向接口的設(shè)計(jì)架構(gòu)( 可以查看 NPOI.SS 的命名空間)
6、同時(shí)支持文件的導(dǎo)入和導(dǎo)出
7、基于.net 2.0 也支持xlsx 和 docx格式(當(dāng)然也支持.net 4.0)
8、來(lái)自全世界大量成功且真實(shí)的測(cè)試Cases
9、大量的實(shí)例代碼
11、你不需要在服務(wù)器上安裝微軟的Office，可以避免版權(quán)問(wèn)題。
12、使用起來(lái)比Office PIA的API更加方便，更人性化。
13、你不用去花大力氣維護(hù)NPOI，NPOI Team會(huì)不斷更新、改善NPOI，絕對(duì)省成本。
NPOI之所以強(qiáng)大，并不是因?yàn)樗С謱?dǎo)出Excel，而是因?yàn)樗С謱?dǎo)入Excel，并能“理解”O(jiān)LE2文檔結(jié)構(gòu)，這也是其他一些Excel讀寫(xiě)庫(kù)比較弱的方面。通常，讀入并理解結(jié)構(gòu)遠(yuǎn)比導(dǎo)出來(lái)得復(fù)雜，因?yàn)閷?dǎo)入你必須假設(shè)一切情況都是可能的，而生成你只要保證滿(mǎn)足你自己需求就可以了，如果把導(dǎo)入需求和生成需求比做兩個(gè)集合，那么生成需求通常都是導(dǎo)入需求的子集，這一規(guī)律不僅體現(xiàn)在Excel讀寫(xiě)庫(kù)中，也體現(xiàn)在pdf讀寫(xiě)庫(kù)中，目前市面上大部分的pdf庫(kù)僅支持生成，不支持導(dǎo)入。
構(gòu)成
NPOI 1.2.x主要由POIFS、DDF、HPSF、HSSF、SS、Util六部分組成。
NPOI.POIFS
OLE2/ActiveX文檔屬性讀寫(xiě)庫(kù)
NPOI.DDF
Microsoft Office Drawing讀寫(xiě)庫(kù)
NPOI.HPSF
OLE2/ActiveX文檔讀寫(xiě)庫(kù)
NPOI.HSSF
Microsoft Excel BIFF(Excel 97-2003)格式讀寫(xiě)庫(kù)
NPOI.SS
Excel公用接口及Excel公式計(jì)算引擎
NPOI.Util
基礎(chǔ)類(lèi)庫(kù)，提供了很多實(shí)用功能，可用于其他讀寫(xiě)文件格式項(xiàng)目的開(kāi)發(fā)
NPOI組成部分
NPOI 1.x的最新版為NPOI 1.2.5，其中包括了以下功能：
1、讀寫(xiě)OLE2文檔
2、讀寫(xiě)DocummentSummaryInformation和SummaryInformation
3、基于LittleEndian的字節(jié)讀寫(xiě)
4、讀寫(xiě)Excel BIFF格式
5、識(shí)別并讀寫(xiě)Excel BIFF中的常見(jiàn)Record，如RowRecord, StyleRecord, ExtendedFormatRecord
6、支持設(shè)置單元格的高、寬、樣式等
7、支持調(diào)用部分Excel內(nèi)建函數(shù)，比如說(shuō)sum, countif以及計(jì)算符號(hào)
8、支持在生成的XLS內(nèi)嵌入打印設(shè)置，比如說(shuō)橫向/縱向打印、縮放、使用的紙張等。
NPOI 2.0主要由SS, HPSF, DDF, HSSF, XWPF, XSSF, OpenXml4Net, OpenXmlFormats組成，具體列表如下：
Assembly名稱(chēng) 模塊/命名空間 說(shuō)明
NPOI.DLL
NPOI.POIFS
OLE2/ActiveX文檔屬性讀寫(xiě)庫(kù)
NPOI.DLL
NPOI.DDF
微軟Office Drawing讀寫(xiě)庫(kù)
NPOI.DLL
NPOI.HPSF
OLE2/ActiveX文檔讀寫(xiě)庫(kù)
NPOI.DLL
NPOI.HSSF
微軟Excel BIFF(Excel 97-2003, doc)格式讀寫(xiě)庫(kù)
NPOI.DLL
NPOI.SS
Excel公用接口及Excel公式計(jì)算引擎
NPOI.DLL
NPOI.Util
基礎(chǔ)類(lèi)庫(kù)，提供了很多實(shí)用功能，可用于其他讀寫(xiě)文件格式項(xiàng)目的開(kāi)發(fā)
NPOI.OOXML.DLL NPOI.XSSF Excel 2007(xlsx)格式讀寫(xiě)庫(kù)
NPOI.OOXML.DLL NPOI.XWPF Word 2007(docx)格式讀寫(xiě)庫(kù)
NPOI.OpenXml4Net.DLL NPOI.OpenXml4Net OpenXml底層zip包讀寫(xiě)庫(kù)
NPOI.OpenXmlFormats.DLL NPOI.OpenXmlFormats 微軟Office OpenXml對(duì)象關(guān)系庫(kù)
(以上內(nèi)容來(lái)自百度百科)從上表可知NPOI組件已支持excel2007，記得之前用的時(shí)候只支持excel2003。很久沒(méi)研究過(guò)這玩意兒了。
首先引入
ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll
然后引入命名空間：
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
輔助類(lèi)
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.Formula.Eval;
namespace Wolfy.Common
{
///
/// 使用NPOI組件
/// 需引入ICSharpCode.SharpZipLib.dll/NPOI.dll/NPOI.OOXML.dll/NPOI.OpenXml4Net.dll/NPOI.OpenXmlFormats.dll
/// office2007
///
public class NPOIExcelHelper
{
///
/// 將Excel文件中的數(shù)據(jù)讀出到DataTable中
///
///
///
public static DataTable Excel2DataTable(string file, string sheetName, string tableName)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//office2003 HSSFWorkbook
workbook = new XSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheet(sheetName);
dt = Export2DataTable(sheet, 0, true);
return dt;
}
///
/// 將指定sheet中的數(shù)據(jù)導(dǎo)入到datatable中
///
/// 指定需要導(dǎo)出的sheet
/// 列頭所在的行號(hào)，-1沒(méi)有列頭
///
///
private static DataTable Export2DataTable(ISheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable dt = new DataTable();
XSSFRow headerRow = null;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as XSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
dt.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex) as XSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
ICell cell = headerRow.GetCell(i);
if (cell == null)
{
break;//到最后 跳出循環(huán)
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
dt.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = HeaderRowIndex + 1; i <= sheet.LastRowNum; i++)
{
XSSFRow row = null;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as XSSFRow;
}
else
{
row = sheet.GetRow(i) as XSSFRow;
}
DataRow dtRow = dt.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.Boolean:
dtRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dtRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.Boolean:
dtRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dtRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Numeric:
dtRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dtRow[j] = strFORMULA.ToString();
}
else
{
dtRow[j] = null;
}
break;
default:
dtRow[j] = "";
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dtRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dtRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dtRow[j] = Convert.ToString(str);
}
else
{
dtRow[j] = null;
}
break;
default:
dtRow[j] = "";
break;
}
}
}
dt.Rows.Add(dtRow);
}
}
catch (Exception)
{
return null;
}
return dt;
}
///
/// 將DataTable中的數(shù)據(jù)導(dǎo)入Excel文件中
///
///
///
public static void DataTable2Excel(DataTable dt, string file, string sheetName)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow header = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//數(shù)據(jù)
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
byte[] buffer = stream.ToArray();
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buffer, 0, buffer.Length);
fs.Flush();
}
}
///
/// 獲取單元格類(lèi)型
///
///
///
private static object GetValueType(XSSFCell cell)
{
if (cell == null)
{
return null;
}
switch (cell.CellType)
{
case CellType.Blank:
return null;
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Error:
return cell.ErrorCellValue;
case CellType.Numeric:
return cell.NumericCellValue;
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
default:
return "=" + cell.StringCellValue;
}
}
}
}