使用npoi.dll导出数据到excel
.net数据导出excel数据有多种方法，最常用的就是使用office组件，但随之而来的问题也很棘手，又要调权限又要确定是否安装office很是麻烦，最近一个项目中也有数据导出功能，随使用excel模板完美完成功能，调试完成发布服务器，又是一通调试，最终可以导出。但是项目中不只一处要数据导出，有四个同事来做这就带来很多麻烦，大家每人都创建了很多模板（当然很多功能属同一模块的都放在一个模板中创建了若干sheet），以后维护很麻烦。于是网上搜索到了npoi,研究了一会尝试使用npoi导出数据。
首先添加npoidll引用（ NPOI.dll和Ionic.Zip.dll 注: npoi版本是NPOI_1.2.5_binary）
#region NPOI导出
public MemoryStream DataMemory(DataTable dt, string headerText)
{
NOPIHelper nopi = new NOPIHelper();
MemoryStream ms = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(headerText);
#region 文件右键属性
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Golden3C";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "hbj"; //填加xls文件作者信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 导出excel的名称
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 30;//行高
row.CreateCell(0).SetCellValue(headerText);
sheet.GetRow(0).GetCell(0).CellStyle = nopi.SetCellStyle(workbook, 20);//设置excel单元格样式
nopi.MergedRegion(sheet, 0, 0, 0, 20);
#endregion
#region 排列表头
{
string[] arrRow1 = new string[] {
"序号",
"数据类型",
"年/半年/季度/月",
"区县",
"企业名称",
"企业类型(现有、新建)",
"设计生产能力(万吨/年)",
"治污设施主体处理工艺",
"新建减排措施投运时间(XXXX年XX月)",
"产品类型（浆、机制纸及纸板、纸制品）",
"产品产量（吨）",
"废水排放量（吨）",
"排放去向",
"",
"平均出水COD浓度（mg/L）",
"",
"平均出水氨氮浓度（mg/L）",
"",
"是否有治污设施中控系统",
"是否安装在线监测仪器、数据与地方环保部门联网并通过有效性审核",
"备注"
};
IRow row1 = sheet.CreateRow(1);//第二行
row1.HeightInPoints = 20;
for (int i = 0; i < arrRow1.Length; i++)
{
row1.CreateCell(i).SetCellValue(arrRow1[i]);
row1.GetCell(i).CellStyle = nopi.SetCellStyle(workbook, 10);
}
string[] arrRow2 = new string[] {
"","","","","","","","","","","","",
"是否纳管",
"纳管后排入集中污水处理设施名称",
"减排措施实施前平均出水COD浓度",
"减排措施实施后平均出水COD浓度",
"减排措施实施前平均出水氨氮浓度",
"减排措施实施后平均出水氨氮浓度","","",""
};
IRow row2 = sheet.CreateRow(2);//第三行
for (int j = 0; j < arrRow2.Length; j++)
{
row2.CreateCell(j).SetCellValue(arrRow2[j]);
row2.GetCell(j).CellStyle = nopi.SetCellStyle(workbook, 10);
}
//开始合并单元格--跨行合并
nopi.MergedRegion(sheet, 1, 2, 0, 0);
nopi.MergedRegion(sheet, 1, 2, 1, 1);
nopi.MergedRegion(sheet, 1, 2, 2, 2);
nopi.MergedRegion(sheet, 1, 2, 3, 3);
nopi.MergedRegion(sheet, 1, 2, 4, 4);
nopi.MergedRegion(sheet, 1, 2, 5, 5);
nopi.MergedRegion(sheet, 1, 2, 6, 6);
nopi.MergedRegion(sheet, 1, 2, 7, 7);
nopi.MergedRegion(sheet, 1, 2, 8, 8);
nopi.MergedRegion(sheet, 1, 2, 9, 9);
nopi.MergedRegion(sheet, 1, 2, 10, 10);
nopi.MergedRegion(sheet, 1, 2, 11, 11);
nopi.MergedRegion(sheet, 1, 2, 18, 18);
nopi.MergedRegion(sheet, 1, 2, 19, 19);
nopi.MergedRegion(sheet, 1, 2, 20, 20);
//跨列合并
nopi.MergedRegion(sheet, 1, 1, 12, 13);
nopi.MergedRegion(sheet, 1, 1, 14, 15);
nopi.MergedRegion(sheet, 1, 1, 16, 17);
}
#endregion 排列表头
#region 处理列值
{
string[] arrColumn = new string[] {
"TA032_Type",
"TA032_dateTime",
"EC001_SSQXMC",
"EC101_WRYMC",
"TA032_WSCLX",
"TA032_SJSCNL",
"TA032_ZWSSGY",
"TA032_RunDate",
"TA032_ProductType",
"TA032_ProductNum",
"TA032_FSPFL",
"TA032_SFNG",
"TA032_NGHCSMC",
"TA032_CODJPSSQND",
"TA032_CODJPSSHND",
"TA032_NH3JPSSQND",
"TA032_NH3JPSSHND",
"TA032_SFYZK",
"TA032_SFYZX",
"TA032_remark"
};
for (int j = 0; j < dt.Rows.Count; j++)//循环行
{
IRow rowColumn = sheet.CreateRow(j + 3);
rowColumn.CreateCell(0).SetCellValue(j + 1);//序号
rowColumn.GetCell(0).CellStyle = nopi.SetCellStyle(workbook, 10);
for (int i = 1; i < arrColumn.Length - 1; i++)
{
//处理前两列(除去序号列)
if (i == 1)
{
string time = dt.Rows[j]["TA032_dateTime"].ToString();
//存储的数据类型：0=年数据；1=月数据；2=季数据,3=半年数据
if (dt.Rows[j]["TA032_Type"].ToString() == "0")
{
rowColumn.CreateCell(1).SetCellValue("年数据");
rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年");
}
else if (dt.Rows[j]["TA032_Type"].ToString() == "1")
{
rowColumn.CreateCell(1).SetCellValue("月数据");
rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + time.Substring(4, 2) + "月");
}
else if (dt.Rows[j]["TA032_Type"].ToString() == "2")
{
string season = "";
rowColumn.CreateCell(1).SetCellValue("季度数据");
if (time.Substring(4, 2) == "21")
season = "一季度";
else if (time.Substring(4, 2) == "22")
season = "二季度";
else if (time.Substring(4, 2) == "23")
season = "三季度";
else
season = "四季度";
rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + season);
}
else if (dt.Rows[j]["TA032_Type"].ToString() == "3")
{
string halfYear = "下半年";
rowColumn.CreateCell(1).SetCellValue("半年数据");
if (time.Substring(4, 3) == "306")
halfYear = "上半年";
rowColumn.CreateCell(2).SetCellValue(time.Substring(0, 4) + "年" + halfYear);
}
rowColumn.GetCell(1).CellStyle = nopi.SetCellStyle(workbook, 10);
rowColumn.GetCell(2).CellStyle = nopi.SetCellStyle(workbook, 10);
}
//从第三列往后 0 1 2 3 4
switch (dt.Columns[arrColumn[i + 1]].DataType.ToString())
{
case "System.String"://字符串类型
rowColumn.CreateCell(i + 2).SetCellValue(dt.Rows[j][arrColumn[i + 1]].ToString());
break;
case "System.DateTime"://日期类型
DateTime dtime = DateTime.Parse(dt.Rows[j][arrColumn[i + 1]].ToString());
rowColumn.CreateCell(i + 2).SetCellValue(dtime.ToString("yyyy年MM月"));
break;
case "System.Boolean"://布尔型
bool boolValue = false;
bool.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out boolValue);
rowColumn.CreateCell(i + 2).SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = 0;
int.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out intValue);
rowColumn.CreateCell(i + 2).SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubValue = 0;
double.TryParse(dt.Rows[j][arrColumn[i + 1]].ToString(), out doubValue);
rowColumn.CreateCell(i + 2).SetCellValue(doubValue);
break;
case "System.DBNull"://空值处理
rowColumn.CreateCell(i + 2).SetCellValue("");
break;
default:
rowColumn.CreateCell(i + 2).SetCellValue("");
break;
}
rowColumn.GetCell(i + 2).CellStyle = nopi.SetCellStyle(workbook, 10);
}
}
}
#endregion
//自动设置列宽
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
#endregion NPOI导出
//***************************************************************************************************************************
//注释：排列表头中每个数组代表excel中的每一行，数组中的""作用是合并单元格，否则合并单元格后表头会很乱。
处理列值 即根据数据源dataset 和要导出的excel表头排列column，这要就不用循环去定位excel列对应dataset里的那个列
//***************************************************************************************************************************
#region 通用方法（单行or复杂表头）
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public void MergedRegion(HSSFSheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
sheet.SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, HSSFColor.BLACK.index);//需要设置边框颜色，否则左侧没有 added by sean 2014-07-28
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="fontSize">字体大小</param>
/// <returns>样式</returns>
public ICellStyle SetCellStyle(HSSFWorkbook workbook, short fontSize)
{
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自动换行
//设置字体格式
IFont font = workbook.CreateFont();
font.FontName = "宋体";//字体
font.FontHeightInPoints = fontSize;//字号
//font1.Color = HSSFColor.RED.index;//颜色
font.Boldweight = 700;//粗体
//font.IsItalic = true;//斜体
//font.Underline = (byte)FontUnderlineType.DOUBLE;//添加双下划线
style.SetFont(font);
//单元格边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
return style;
}
/// <summary>
/// excel文件右键属性
/// </summary>
/// <param name="workbook"></param>
public void SetFileAttribute(HSSFWorkbook workbook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Golden3C";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "hbj"; //填加xls文件作者信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
/// <summary>
/// 自动设置Excel列宽
/// </summary>
/// <param name="sheet">Excel表</param>
public void AutoSizeColumns(HSSFSheet sheet)
{
if (sheet.PhysicalNumberOfRows > 0)
{
IRow headerRow = sheet.GetRow(sheet.PhysicalNumberOfRows - 1);//获取最后一行，因为列是根据最后一行来排
for (int j = 0; j < headerRow.Cells.Count; j++)
{
sheet.AutoSizeColumn(j);
}
}
}
#endregion
