1、引言
1.1解决哪些问题
现在很多公司用的导出基本上采用的通过gridView导出excel,此种导出存在以下几种问题
1、数据量大的时候有时导出有时会让浏览器卡死，因为导出的excel不是真正的excel，是html格式的，只是用excel打开，查看方式只需用记事本或其他文本编辑器打开就行了。
2、 由于导出的是html，用excel打开，会出现以下两个问题：1、所以导出的数据都会弹出一个提示框，“您尝试打开的文件的格式与文件扩展名指定格式不一致，打开文件前请验证文件没有损坏且来源可信”的对话框2、当修改里面的内容时只能保存一个副本操作及其不方便。3、而且html格式的文档导入也会存在很大的问题。
1.2未解决的问题
1、NPOI导出2007格式和2010格式的很慢，几万数据很可能会卡死。
2、 NPOI
2. 1 NPOI简介
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对 Word/Excel文档进行读写操作
2.2 NPOI优势
（一）传统操作Excel遇到的问题：
1、如果是.NET，需要在服务器端装Office，且及时更新它，以防漏洞，还需要设定权限允许.NET访问COM+，如果在导出过程中出问题可能导致服务器宕机。
2、Excel会把只包含数字的列进行类型转换，本来是文本型的，Excel会将其转成数值型的，比如编号000123会变成123。
3、导出时，如果字段内容以“-”或“=”开头，Excel会把它当成公式进行，会报错。
4、Excel会根据Excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702E+17格式，日期列变成包含日期和数字的。
5、第二种是直接导出html，修改后缀名为.xls，这个方法有点像骗人的把戏，而且不能再导入
6、第三种是使用Jet OLEDB引擎来进行导入导出，完全使用sql语句来进行操作，缺点能控制的东西非常有限，比如格式就难以控制
7、第四中CSV格式的文件其实导出的是文本格式的文件，此种格式的文件优点导出方便而且速度快。但是使用者用excel对数据进行分析后需要对数据进行重新存一份。
（二）使用NPOI的优势
1、你不需要在服务器上安装微软的Office，可以避免版权问题。
2、使用起来比Office PIA的API更加方便，更人性化。
3、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。
NPOI之所以强大，并不是因为它支持导出Excel，而是因为它支持导入Excel，并能“理解”OLE2文档结构，这也是其他一些Excel读写库比较弱的方面。通常，读入并理解结构 远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在Excel读写库中，也体现在pdf读写库中，目前市面上大部分的pdf库仅支持生成，不支持导入。
4、支持excel07格式和简单的10格式的导入导出
5、支持图片的导入导出
6、导入导出速度很快
2.3 NPOI组件构成
NPOI 2.01,当是.net2.0框架时提供以下使用的dll和配置文件。
1、 ICSharpCode.SharpZipLib.dll
2、 NPOI.dll
3、 NPOI.OOXML.dll
4、 NPOI.OpenXml4Net.dll
5、 NPOI.OpenXml4Net.dll.config
6、 NPOI.OpenXmlFormats.dll
7、 NPOI.XML
如果是.net 3.5框架时提供以下使用的dll和配置文件
1、 ICSharpCode.SharpZipLib.dll
2、 NPOI.dll
3、 NPOI.OOXML.dll
4、 NPOI.OpenXml4Net.dll
5、 NPOI.OpenXml4Net.dll.config
6、 NPOI.OpenXmlFormats.dll
7、 NPOI.XML
如果是.net 4.0框架时提供以下使用的dll和配置文件
1、 ICSharpCode.SharpZipLib.dll
2、 NPOI.dll
3、 NPOI.OOXML.dll
4、 NPOI.OpenXml4Net.dll
5、 NPOI.OpenXmlFormats.dll
6、 NPOI.XML
2.4 如何使用NPOI
2.4.1 使用准备
将新建项目时所使用的.net框架版本和NPOI相对应，然后对所有dll添加引用
2.4.2 NPOI组件dll详细情况
1、NPOI.DLL中包含的模块
NPOI.Util 基础辅助库
NPOI.POIFS OLE2格式读写库，主要负责处理DocumentInformation
NPOI.DDF Microsoft Drawing格式读写库
NPOI.SS Excel 2003和Excel 2007操作库共用的接口库以及公用模块实现，如公式计算库
NPOI.HPSF OLE2的Summary Information和Document Summary Information属性读写库
NPOI.HSSF Excel BIFF格式读写库，这是用户用的最多的命名空间
2、NPOI.OOXML中包含的模块
NPOI.XSSF Excel 2007操作库，大部分对象都实现了NPOI.SS的接口
NPOI.XWPF Word 2007操作库
3、NPOI.OpenXml4Net.DLL中包含的模块
只有一个，即NPOI.OpenXml4Net，它是从POI的子项目OpenXml4j移植过来的，其功能类似于NPOI.POIFS，只是它操作的是OOXML格式。这个模块和微软提供的 System.Packaging功能是一致的，只是System.Packaging支持.Net 3.0以上，这个支持.NET 2.0。
4、NPOI.OpenXmlFormats.DLL中包含的模块
也只有一个，即OpenXmlFormats，它定义了所有OOXML对象和C#对象的映射关系，并协助进行序列化和反序列化，从而使文件读写层和逻辑层分离。
2.4.3 使用操作
1、取数据操作，由于导出的数据量很大，不能一次将数据取到内存中，需要分配的将数据取到内存中
Demo中分批取数据方法
1 BATOStatisticsRPT bllATO = new BATOStatisticsRPT(); 2 // 由于导出数据量很大 分页服务取得的数据很大所以分批取数据放到DataTable中 3 MyPager pager = new MyPager(); 4 5 pager.PageSize = 5000;//每次取5000条数据 6 7 pager.TotalSize = bllATO.GetRecord(); 8 9 int pageCount = pager.GetPageCount(); 10 11 DataTable dt = new DataTable(); 12 13 for (int i = 0; i < pageCount; i++) 14 15 { 16 17 DataTable dtTemp = new DataTable(); 18 19 dtTemp = bllATO.GetDataTable("", "", pager.PageSize * i, pager.PageSize * (i + 1)); 20 21 dt.Merge(dtTemp); 22 23 } 24 25 return dt;
其中有一个简单分页类代码为：
/// <summary>
/// MyPager类
/// </summary>
public class MyPager
{
/// <summary>
/// 总条数
/// </summary>
public int TotalSize { get; set; }
/// <summary>
/// 每页数据量
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 当前页面
/// </summary>
public int CurrentPageIndex { get; set; }
/// <summary>
/// 得到总页数
/// </summary>
/// <returns>总页数</returns>
public int GetPageCount()
{
int totalPageCount;
return totalPageCount = (int)Math.Ceiling((double)TotalSize / PageSize);
}
}
3、导出excel2003格式
NPOIHelper.TableToExcelForXLSAny(this.GetDataTable(), "excel2003导出");
NPOIHelPer代码帮助类代码为
/// <summary>
/// NPOIHelper类
/// </summary>
public class NPOIHelper
{
#region excel2003超过65535行数据用多个sheet处理
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls) 最大支持65535行
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelName">导出文件名称</param>
public static void TableToExcelForXLSAny(DataTable dt, string excelName)
{
Stopwatch stopWacth = new Stopwatch();
stopWacth.Start();// 开始计时器
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
int rowCount = dt.Rows.Count;
int sheetMaxRow = 65535;
//大于65535行数据
if (rowCount > sheetMaxRow)
{
for (int k = 1; k <= rowCount / sheetMaxRow; k++)
{
ISheet sheet = hssfworkbook.CreateSheet(excelName + "_" + k);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.SetColumnWidth(i, 30 * 180);// 设置所在列的宽度
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
int m = 0;
//数据
for (int i = (k - 1) * sheetMaxRow; i < k * sheetMaxRow; i++)
{
IRow row1 = sheet.CreateRow(m + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
m++;
}
}
if (rowCount % sheetMaxRow != 0)
{
ISheet sheet = hssfworkbook.CreateSheet(excelName + "_" + (rowCount / sheetMaxRow + 1));
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.SetColumnWidth(i, 30 * 180);// 设置所在列的宽度
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
int m = 0;
//数据
for (int i = (rowCount / sheetMaxRow) * sheetMaxRow; i < rowCount; i++)
{
IRow row1 = sheet.CreateRow(m + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
m++;
}
}
}
// 小于65536行
else
{
ISheet sheet = hssfworkbook.CreateSheet(excelName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.SetColumnWidth(i, 30 * 180);// 设置所在列的宽度
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.Clear();
curContext.Response.ContentType = "application/x-excel";
string filename = HttpUtility.UrlEncode(excelName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
hssfworkbook.Write(curContext.Response.OutputStream);
File.AppendAllText(curContext.Server.MapPath("/TestMinutes/TestResult.txt"), "\nNPOI得到dataTable后导出数据速度:" + stopWacth.ElapsedMilliseconds.ToString()+"毫秒", System.Text.Encoding.UTF8);
stopWacth.Stop();
curContext.Response.End();
}
#endregion
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelName">excel名称</param>
public static void TableToExcelForXLS(DataTable dt, string excelName)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.Clear();
curContext.Response.ContentType = "application/x-excel";
string filename = HttpUtility.UrlEncode(excelName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
hssfworkbook.Write(curContext.Response.OutputStream);
curContext.Response.End();
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>单元格类型</returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region Excel2007和简单的Excel2010
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
// 表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
// continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
// 数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelName">文件名称</param>
public static void TableToExcelForXLSX(DataTable dt, string excelName)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.Clear();
curContext.Response.ContentType = "application/x-excel";
string filename = HttpUtility.UrlEncode(excelName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
xssfworkbook.Write(curContext.Response.OutputStream);
curContext.Response.End();
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>单元格类型</returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
TableToExcelForXLSAny方法中第一个参数为DataTable，第二个参数为报表名称 ,调用此方法时，由于excel2003每个sheet最多支持65535行数据，如果导出的数据量大于65535行时，会分为多个sheet。
4、导出excel2007格式
NPOIHelper.TableToExcelForXLSX(this.GetDataTable(), "excel2007或者excel2010导出");
TableToExcelForXLSX方法中第一个参数为DataTable，第二个参数为报表名称
5、将excel2003导入到DataTable中
DataTable dt = NPOIHelper.ExcelToTableForXLS(Server.MapPath("/TestFile/excel2003导入.xls"));
ExcelToTableForXLS中的参数为导入文件的物理路径
6、将excel2007导入到DataTable中
DataTable dt = NPOIHelper.ExcelToTableForXLSX(Server.MapPath("/TestFile/excel2007或者excel2010导入.xlsx"));
ExcelToTableForXLSX中的参数为导入文件的物理路径
2.5 其他导出帮助类代码
CSV导出帮助类代码
1 /// <summary> 2 /// CSVHelper类 3 /// </summary> 4 public class CSVHelper 5 { 6 /// <summary> 7 /// 导出CSV格式 8 /// </summary> 9 /// <param name="dt">数据源</param> 10 /// <param name="FileName">保存的文件名称</param> 11 public static void DataTableToCSV(DataTable dt, string FileName) 12 { 13 Stopwatch stopWacth = new Stopwatch(); 14 stopWacth.Start();// 开始计时器 15 StringWriter sw = new StringWriter(); 16 StringBuilder sb = new StringBuilder(); 17 18 //得到列名 19 for (int i = 0; i < dt.Columns.Count; i++) 20 { 21 sb.Append(dt.Columns[i].ColumnName); 22 if (i != dt.Columns.Count - 1) 23 { 24 sb.Append(","); 25 } 26 } 27 sw.WriteLine(sb.ToString()); 28 29 //得到每列的数据 30 foreach (DataRow dr in dt.Rows) 31 { 32 for (int i = 0; i < dt.Columns.Count; i++) 33 { 34 sw.Write(dr[i]); 35 if (i != dt.Columns.Count - 1) 36 { 37 sw.Write(","); 38 } 39 } 40 41 sw.WriteLine(""); 42 } 43 44 sw.Close(); 45 System.Web.HttpContext curContext = System.Web.HttpContext.Current; 46 curContext.Response.Clear(); 47 curContext.Response.AddHeader("Content-Disposition", "attachment; filename=" + curContext.Server.UrlEncode(FileName) + ".csv"); 48 curContext.Response.ContentType = "application/ms-excel"; 49 curContext.Response.ContentEncoding = System.Text.Encoding.Default; // 采用默认编码输出 50 curContext.Response.Write(sw); 51 File.AppendAllText(curContext.Server.MapPath("/TestMinutes/TestResult.txt"), "\nCSV得到dataTable后导出数据速度:"+stopWacth.ElapsedMilliseconds.ToString()+"毫秒", System.Text.Encoding.UTF8); 52 stopWacth.Stop(); 53 curContext.Response.End(); 54 } 55 }
2.6 附加说明
CSV和NPOI的比较
CSV文件简单说明：
每条记录占一行
以逗号为分隔符
逗号前后的空格会被忽略
字段中包含有逗号，该字段必须用双引号括起来
字段中包含有换行符，该字段必须用双引号括起来
字段前后包含有空格，该字段必须用双引号括起来
字段中的双引号用两个双引号表示
字段中如果有双引号，该字段必须用双引号括起来
字段中如果有太长的数字，该字段必须单引号括起来
日期格式不用做特殊处理
第一条记录，可以是字段名
CSV优点：
CSV格式的文件其实导出的是文本格式的文件，此种格式的文件优点导出方便而且比NPOI速度快。
CSV缺点：
使用者用excel对数据进行分析后需要对数据进行重新存一份。
最后把用datatable直接导出html附上（实质上是骗人的把戏，是文本格式的数据）
/// <summary>
/// 通过GRIDVIEW导出EXCEL
/// </summary>
/// <param name="dtData">数据源</param>
/// <param name="excelName">导出的excel名</param>
public void ExportExcelForGridView(System.Data.DataTable dtData, string excelName)
{
System.Web.UI.WebControls.GridView GridGy = null;
string FileName = HttpUtility.UrlEncode(excelName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");// "CgsReport" + DateTime.Now.Date.ToString("yyyyMMddHHmm") + ".xls";
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
if (dtData != null)
{
GridGy = new System.Web.UI.WebControls.GridView();
GridGy.AllowPaging = false;
GridGy.Visible = true;
GridGy.DataSource = dtData;
GridGy.RowDataBound += new GridViewRowEventHandler(GridGy_RowDataBound);
GridGy.DataBind();
}
curContext.Response.ClearContent();
curContext.Response.AddHeader("content-disposition", "attachment;filename=" + "" + FileName);
curContext.Response.Charset = "GB2312";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
curContext.Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridGy.RenderControl(htmlWrite);
string top = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title>Datos</title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=GB2312\" />\n<style>\n</style>\n</head>\n<body>\n";
string end = "</body></html>";
curContext.Response.Write(top + stringWrite.ToString() + end);
// curContext.Response.End();
dtData.Dispose();
}
