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)
            {
            }
        }