引言
項(xiàng)目中常用到將數(shù)據(jù)導(dǎo)入excel，將excel中的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的功能，曾經(jīng)也查找過相關(guān)的內(nèi)容，將曾經(jīng)用過的方案總結(jié)一下。
方案一
npoi
npoi 是 poi 項(xiàng)目的 .net 版本。poi是一個(gè)開源的java讀寫excel、word等微軟ole2組件文檔的項(xiàng)目。
使用 npoi 你就可以在沒有安裝 office 或者相應(yīng)環(huán)境的機(jī)器上對 word/excel 文檔進(jìn)行讀寫。npoi是構(gòu)建在poi 3.x版本之上的，它可以在沒有安裝office的情況下對word/excel文檔進(jìn)行讀寫操作。
優(yōu)勢
(一)傳統(tǒng)操作excel遇到的問題：
1、如果是.net，需要在服務(wù)器端裝office，且及時(shí)更新它，以防漏洞，還需要設(shè)定權(quán)限允許.net訪問com+，如果在導(dǎo)出過程中出問題可能導(dǎo)致服務(wù)器宕機(jī)。
2、excel會(huì)把只包含數(shù)字的列進(jìn)行類型轉(zhuǎn)換，本來是文本型的，excel會(huì)將其轉(zhuǎn)成數(shù)值型的，比如編號(hào)000123會(huì)變成123。
3、導(dǎo)出時(shí)，如果字段內(nèi)容以“-”或“=”開頭，excel會(huì)把它當(dāng)成公式進(jìn)行，會(huì)報(bào)錯(cuò)。
4、excel會(huì)根據(jù)excel文件前8行分析數(shù)據(jù)類型，如果正好你前8行某一列只是數(shù)字，那它會(huì)認(rèn)為該列為數(shù)值型，自動(dòng)將該列轉(zhuǎn)變成類似1.42702e+17格式，日期列變成包含日期和數(shù)字的。
(二)使用npoi的優(yōu)勢
1、您可以完全免費(fèi)使用該框架
2、包含了大部分excel的特性(單元格樣式、數(shù)據(jù)格式、公式等等)
3、專業(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、來自全世界大量成功且真實(shí)的測試cases
9、大量的實(shí)例代碼
11、你不需要在服務(wù)器上安裝微軟的office，可以避免版權(quán)問題。
12、使用起來比office pia的api更加方便，更人性化。
13、你不用去花大力氣維護(hù)npoi，npoi team會(huì)不斷更新、改善npoi，絕對省成本。
npoi之所以強(qiáng)大，并不是因?yàn)樗С謱?dǎo)出excel，而是因?yàn)樗С謱?dǎo)入excel，并能“理解”ole2文檔結(jié)構(gòu)，這也是其他一些excel讀寫庫比較弱的方面。通常，讀入并理解結(jié)構(gòu)遠(yuǎn)比導(dǎo)出來得復(fù)雜，因?yàn)閷?dǎo)入你必須假設(shè)一切情況都是可能的，而生成你只要保證滿足你自己需求就可以了，如果把導(dǎo)入需求和生成需求比做兩個(gè)集合，那么生成需求通常都是導(dǎo)入需求的子集，這一規(guī)律不僅體現(xiàn)在excel讀寫庫中，也體現(xiàn)在pdf讀寫庫中，目前市面上大部分的pdf庫僅支持生成，不支持導(dǎo)入。
構(gòu)成
npoi 1.2.x主要由poifs、ddf、hpsf、hssf、ss、util六部分組成。
npoi.poifs
ole2/activex文檔屬性讀寫庫
npoi.ddf
microsoft office drawing讀寫庫
npoi.hpsf
ole2/activex文檔讀寫庫
npoi.hssf
microsoft excel biff(excel 97-2003)格式讀寫庫
npoi.ss
excel公用接口及excel公式計(jì)算引擎
npoi.util
基礎(chǔ)類庫，提供了很多實(shí)用功能，可用于其他讀寫文件格式項(xiàng)目的開發(fā)
npoi組成部分
npoi 1.x的最新版為npoi 1.2.5，其中包括了以下功能：
1、讀寫ole2文檔
2、讀寫docummentsummaryinformation和summaryinformation
3、基于littleendian的字節(jié)讀寫
4、讀寫excel biff格式
5、識(shí)別并讀寫excel biff中的常見record，如rowrecord, stylerecord, extendedformatrecord
6、支持設(shè)置單元格的高、寬、樣式等
7、支持調(diào)用部分excel內(nèi)建函數(shù)，比如說sum, countif以及計(jì)算符號(hào)
8、支持在生成的xls內(nèi)嵌入打印設(shè)置，比如說橫向/縱向打印、縮放、使用的紙張等。
npoi 2.0主要由ss, hpsf, ddf, hssf, xwpf, xssf, openxml4net, openxmlformats組成，具體列表如下：
assembly名稱 模塊/命名空間 說明
npoi.dll
npoi.poifs
ole2/activex文檔屬性讀寫庫
npoi.dll
npoi.ddf
微軟office drawing讀寫庫
npoi.dll
npoi.hpsf
ole2/activex文檔讀寫庫
npoi.dll
npoi.hssf
微軟excel biff(excel 97-2003, doc)格式讀寫庫
npoi.dll
npoi.ss
excel公用接口及excel公式計(jì)算引擎
npoi.dll
npoi.util
基礎(chǔ)類庫，提供了很多實(shí)用功能，可用于其他讀寫文件格式項(xiàng)目的開發(fā)
npoi.ooxml.dll npoi.xssf excel 2007(xlsx)格式讀寫庫
npoi.ooxml.dll npoi.xwpf word 2007(docx)格式讀寫庫
npoi.openxml4net.dll npoi.openxml4net openxml底層zip包讀寫庫
npoi.openxmlformats.dll npoi.openxmlformats 微軟office openxml對象關(guān)系庫
(以上內(nèi)容來自百度百科)從上表可知npoi組件已支持excel2007，記得之前用的時(shí)候只支持excel2003。很久沒研究過這玩意兒了。
案例
官網(wǎng)地址：http://npoi.codeplex.com/，可以從官網(wǎng)下載npoi2.x版本的。
首先引入
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;
輔助類
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沒有列頭
///
///
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();
}
}
///
/// 獲取單元格類型
///
///
///
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;
}
}
}
}