近期项目中用到Excel导出功能，之前都是用普通的office组件导出的方法，今天尝试用下NPOI，故作此文以备日后查阅。
1.NPOI官网http://npoi.codeplex.com/，下载最新工具包。
2.选择.net版本（例如dotnet4），引用其中的dll。
3.后台代码
using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.IO;
using Test_DBUtility;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel; public partial class Handlers_ExportExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string act = Request["action"].ToString();
DataTable dt = new DataTable();
string sql = string.Empty;
int[] array = new int[] { };
switch (act)
{
case "export":
sql = @"select M_MEDIUM 产品图片,PN,CN_NAME 产品名称,EN_NAME 英文名称,DESC_CN 中文描述,DESC_EN 英文描述,REMARKS 备注描述,MANUFACTURER_PN 厂家编码,PROVIDER_FULL_NAME 供应商,SP_RESULT 审批结果," +
"RANK 产品定级,BASE_STOCK 基础库存,COST 实际成本,CURRENT_PRICE 现价,SPECIAL_PRICE 特价 from viewsdb_en.dbo.View_ProductExcelExport";
dt = SqlHelper.Query(sql).Tables[];
array = new int[] { , , , , , , , , , , , , , , }; //定义列宽
NpoiExcel(dt, "操作表", array);
break;
}
}
} /// <summary>
/// NPOI导出EXCEL
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="title">导出文件的名称</param>
/// <param name="array">列宽数组</param>
public void NpoiExcel(DataTable dt, string title, int[] array)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow();
headerrow.Height = * ;
ICellStyle style = book.CreateCellStyle();
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center; /*标题*/
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
/*设置列宽*/
if (array.Length > )
{
for (int c = ; c < array.Length; c++)
{
sheet.SetColumnWidth(c, array[c] * );
}
}
} /*内容*/
for (int j = ; j <= dt.Rows.Count; j++)
{
NPOI.SS.UserModel.IRow contentrow = sheet.CreateRow(j);
contentrow.Height = * ;
for (int k = ; k < dt.Columns.Count; k++)
{
ICell cell = contentrow.CreateCell(k);
cell.CellStyle = style;
if (k == )
{
string picurl = dt.Rows[j - ][k].ToString();
AddCellPicture(sheet, book, picurl, j, k);
}
else
{
cell.CellStyle = style;
cell.SetCellValue(dt.Rows[j - ][k].ToString());
}
}
} MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
} /// <summary>
/// 向sheet插入图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="workbook"></param>
/// <param name="fileurl"></param>
/// <param name="row"></param>
/// <param name="col"></param>
private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
//由于File类只能读取本地资源，所以在配置文件中配置了物理路径的前半部分
string DiscPath = ConfigurationManager.AppSettings["PictureDiscPath"];
string FileName = DiscPath.Replace("\\", "/") + fileurl.Replace("http://www.bolioptics.com/", "");
if (File.Exists(FileName) == true)
{
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(, , , , col, row, col + , row + );
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}