NPOI导出Excel并下载到客户端

大家好，又见面了，我是你们的朋友全栈君。

报表数据导出Excel，很常见的需求，然而每次都能忘了，今天再次遇到了，总结一下。

一般来说都需要有个标题

／／需要输出的字段
string[] fieldArr = { "Title", "Sequence", "WorkSheetDescription", "Position", "StartTime", "EndTime", "Transactor", "Status", "WorkClass", "Result", "StartDoTime", "EndDoTime" };

／／对应的中文描述            System.Collections.Generic.Dictionary<string, string> fieldDic = new System.Collections.Generic.Dictionary<string, string> { { "Title", "工单编号" }, { "Sequence", "顺序" }, { "WorkSheetDescription", "工单描述" }, { "Position", "位置" }, { "StartTime", "计划开始时间" }, { "EndTime", "计划结束时间" }, { "Transactor", "处理人" }, { "Status", "工单状态" }, { "WorkClass", "班次" }, { "Result", "执行结果" }, { "StartDoTime", "实际开始时间" }, { "EndDoTime", "实际结束时间" } };

设置表单表格样式

ICellStyle titleStyle= wb.CreateCellStyle();
            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "楷体";
            font1.Color = HSSFColor.White.Index;//字体颜色
            font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗
            titleStyle.SetFont(font1);//字体设置具体的字体
            //背景色
            titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            titleStyle.FillPattern = FillPattern.SolidForeground;
            titleStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式

设置列宽

int[] columnWidth = { 10, 10, 25, 10, 15, 15, 20, 10, 20, 20, 15, 15 };
            for (int i = 0; i < columnWidth.Length; i++)
            { //设置列宽度，256*字符数，因为单位是1/256个字符 sheet.SetColumnWidth(i, 256 * columnWidth[i]); }

把生成的Excel发送到客户端

MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("WS" + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.Flush();

            Response.End();
            wb = null;
            ms.Close();
            ms.Dispose();

因为我的项目是SharePoint的，所以数据源是SPListItemCollection传入，其他的DataTable或者其他的相应改动一下就行。

代码如下

public void NPOIExcel(SPListItemCollection items)
{
HSSFWorkbook wb = new HSSFWorkbook();
//标题样式样式
ICellStyle titleStyle= wb.CreateCellStyle();
IFont font1 = wb.CreateFont();//字体
font1.FontName = "楷体";
font1.Color = HSSFColor.White.Index;//字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗
titleStyle.SetFont(font1);//设置字体
//设置背景色
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
//创建一个表单
ISheet sheet = wb.CreateSheet("Sheet0");
//设置列宽
int[] columnWidth = { 10, 10, 25, 10, 15, 15, 20, 10, 20, 20, 15, 15 };
for (int i = 0; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, 256 * columnWidth[i]);
}
IRow row;
ICell cell;
string[] fieldArr = { "Title", "Sequence", "WorkSheetDescription", "Position", "StartTime", "EndTime", "Transactor", "Status", "WorkClass", "Result", "StartDoTime", "EndDoTime" };
System.Collections.Generic.Dictionary<string, string> fieldDic = new System.Collections.Generic.Dictionary<string, string> { { "Title", "工单编号" }, { "Sequence", "顺序" }, { "WorkSheetDescription", "工单描述" }, { "Position", "位置" }, { "StartTime", "计划开始时间" }, { "EndTime", "计划结束时间" }, { "Transactor", "处理人" }, { "Status", "工单状态" }, { "WorkClass", "班次" }, { "Result", "执行结果" }, { "StartDoTime", "实际开始时间" }, { "EndDoTime", "实际结束时间" } };
／／写入标题行
row = sheet.CreateRow(0);
for (int i = 0; i < fieldArr.Length; i++)
{
cell = row.CreateCell(i);//创建第j列
cell.CellStyle = titleStyle;
SetCellValue(cell, fieldDic[fieldArr[i]]);
}
／／写入数据，从第2行开始
for (int i = 1; i <= items.Count; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = 0; j < fieldArr.Length; j++)
{
cell = row.CreateCell(j);
//根据数据类型设置不同类型的cell
var field = fieldArr[j];
object obj = null;
／／如果报错，跳过该字段
try
{
obj = items[i - 1][field];
}
catch
{
continue;
}
if (obj != null)
{
SetCellValue(cell, obj);
}
}
}
//发送到客户端
MemoryStream ms = new MemoryStream();
wb.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("WS" + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
wb = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 根据数据类型设置不同类型的cell
/// </summary>
/// <param name="cell"></param>
/// <param name="obj"></param>
public static void SetCellValue(ICell cell, object obj)
{
try
{
if (obj.GetType() == typeof(int))
{
cell.SetCellValue((int)obj);
}
else if (obj.GetType() == typeof(double))
{
cell.SetCellValue((double)obj);
}
else if (obj.GetType() == typeof(IRichTextString))
{
cell.SetCellValue((IRichTextString)obj);
}
else if (obj.GetType() == typeof(string))
{
cell.SetCellValue(obj.ToString());
}
else if (obj.GetType() == typeof(DateTime))
{
cell.SetCellValue(Convert.ToDateTime(obj).ToString("yyyy/MM/dd"));
}
else if (obj.GetType() == typeof(bool))
{
cell.SetCellValue((bool)obj);
}
else
{
cell.SetCellValue(obj.ToString());
}
}
catch (Exception ex)
{
}
}