C# MVC使用NPOI导出Excel

C# MVC使用NPOI导出Excel

导出Excel方法

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="searchkey"></param>
        /// <returns></returns>
        public FileResult exprotexcel(string dt_begin, string dt_end, string title, string searchkey)
        {
            #region 获取数据
            string sError = "";
            List<DepartmentDetails> data = PublicBll.GetDepartmentDetailsAll(dt_begin, dt_end, ref sError);
            }
            #endregion
            //设置表头
            Dictionary<string, string> collection = new Dictionary<string, string>();
            collection.Add("dt_OperDate", "时间");
            collection.Add("companyname", "餐厅名称");
            collection.Add("vch_dishname", "菜单");
            collection.Add("num_cost", "订单总价");
            collection.Add("num_discount", "补贴金额");
            collection.Add("num_ys", "实收金额");
            collection.Add("member_name", "用餐人姓名");
            collection.Add("vch_id", "身份证号码");
            collection.Add("area", "区域");
            collection.Add("age", "年龄");
            collection.Add("diningStyle", "就餐方式");
            collection.Add("vch_empname", "点餐员");
            collection.Add("vch_tel", "点餐员手机号");
            collection.Add("location", "点餐位置");

            var byteInfo = ExportExcel<DepartmentDetails>(collection, data, searchkey);
            return File(byteInfo, "application/vnd.ms-excel", string.Format("{0}-{1}.xls", title, DateTime.Now.ToString("yyyyMMddHHmm")));
        }

公共部分

		/// <summary>
        /// NPOI导出EXCEl公共部分
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        /// <param name="arrHead">表头</param>
        /// <param name="arrColWidth">列宽</param>
        /// <param name="headHeight">表头高度</param>
        /// <param name="colHeight">列高度</param>
        /// <param name="dataSource">数据</param>
        /// <param name="title">表头标题</param>
        /// <param name="footer">底部内容</param>
        public static byte[] ExportExcel<T>(Dictionary<string, string> columnsHeader, List<T> dataSource, string title = null, string footer = null)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = 18;

            IRow row;
            ICell cell;

            #region excel标题头
            int rowIndex = 0;
            if (!string.IsNullOrEmpty(title))
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.Boldweight = 700;
                cellStyle.SetFont(font);
                var region = new CellRangeAddress(0, 0, 0, columnsHeader.Keys.Count > 0 ? columnsHeader.Keys.Count - 1 : 0);
                sheet.AddMergedRegion(region);
                //合并单元格后样式
                ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

                row = sheet.CreateRow(rowIndex);
                row.HeightInPoints = 20;
                cell = row.CreateCell(0);
                cell.SetCellValue(title);
                cell.CellStyle = cellStyle;
                rowIndex++;
            }
            #endregion

            #region 列头
            row = sheet.CreateRow(rowIndex);
            row.HeightInPoints = 15;
            int cellIndex = 0;
            foreach (var value in columnsHeader.Values)
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle = workbook.CreateCellStyle();
                cellStyle.BorderBottom = BorderStyle.THIN;
                cellStyle.BorderLeft = BorderStyle.THIN;
                cellStyle.BorderRight = BorderStyle.THIN;
                cellStyle.BorderTop = BorderStyle.THIN;
                //背景色
                cellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                //水平垂直居中
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                cellStyle.SetFont(font);

                cell = row.CreateCell(cellIndex);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(value);
                cellIndex++;
            }
            rowIndex++;
            #endregion

            #region 主题内容

            //单元格样式 注：不要放循环里面，NPOI中调用workbook.CreateCellStyle()方法超过4000次会报错
            ICellStyle contentStyle = workbook.CreateCellStyle();
            contentStyle.BorderBottom = BorderStyle.THIN;
            contentStyle.BorderLeft = BorderStyle.THIN;
            contentStyle.BorderRight = BorderStyle.THIN;
            contentStyle.BorderTop = BorderStyle.THIN;
            contentStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont contentFont = workbook.CreateFont();
            contentFont.FontHeightInPoints = 10;
            contentStyle.SetFont(contentFont);

            //日期格式样式
            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.BorderBottom = BorderStyle.THIN;
            dateStyle.BorderLeft = BorderStyle.THIN;
            dateStyle.BorderRight = BorderStyle.THIN;
            dateStyle.BorderTop = BorderStyle.THIN;
            dateStyle.VerticalAlignment = VerticalAlignment.CENTER;
            dateStyle.SetFont(contentFont);
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
            foreach (T item in dataSource)
            {
                row = sheet.CreateRow(rowIndex);
                row.HeightInPoints = 15;
                rowIndex++;
                Type type = item.GetType();
                PropertyInfo[] properties = type.GetProperties();
                if (properties.Length > 0)
                {
                    cellIndex = 0;
                    foreach (var key in columnsHeader.Keys)
                    {
                        cell = row.CreateCell(cellIndex);
                        cell.CellStyle = contentStyle;

                        if (properties.Select(x => x.Name.ToLower()).Contains(key.ToLower()))
                        {
                            var property = properties.Where(x => x.Name.ToLower() == key.ToLower()).FirstOrDefault();
                            string drValue = property.GetValue(item) == null ? "" : property.GetValue(item).ToString();
                            //当类型类似DateTime?时
                            var fullType = property.PropertyType.Name == "Nullable`1" ? property.PropertyType.GetGenericArguments()[0].FullName : property.PropertyType.FullName;
                            switch (fullType)
                            {
                                case "System.String": //字符串类型
                                    cell.SetCellValue(drValue);
                                    break;
                                case "System.DateTime": //日期类型
                                    if (string.IsNullOrEmpty(drValue) || drValue == "0001/1/1 0:00:00")
                                    {
                                        cell.SetCellValue("");
                                    }
                                    else
                                    {
                                        DateTime dateV;
                                        DateTime.TryParse(drValue, out dateV);
                                        cell.SetCellValue(dateV);

                                        cell.CellStyle = dateStyle; //格式化显示
                                    }
                                    break;
                                case "System.Boolean": //布尔型
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    cell.SetCellValue(boolV);
                                    break;
                                case "System.Int16": //整型
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    cell.SetCellValue(intV);
                                    break;
                                case "System.Decimal": //浮点型
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    cell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull": //空值处理
                                    cell.SetCellValue("");
                                    break;
                                default:
                                    cell.SetCellValue("");
                                    break;
                            }
                        }
                        cellIndex++;
                    }
                }

            }
            #endregion

            #region 结尾行
            if (!string.IsNullOrEmpty(footer))
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                cellStyle.SetFont(font);
                var region = new CellRangeAddress(rowIndex, rowIndex, 0, columnsHeader.Keys.Count > 0 ? columnsHeader.Keys.Count - 1 : 0);
                sheet.AddMergedRegion(region);
                //合并单元格后样式
                ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

                row = sheet.CreateRow(rowIndex);
                row.HeightInPoints = 18;
                cell = row.CreateCell(0);
                cell.SetCellValue(footer);
                cell.CellStyle = cellStyle;
            }
            #endregion

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
                return ms.ToArray();
                //或者直接导出不用返回值　　var response = System.Web.HttpContext.Current.Response;
                //response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                //response.ContentType = "application/vnd.ms-excel";
                //response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
                //response.BinaryWrite(ms.ToArray());
                //response.Buffer = true;
                //response.Flush();
                //response.End();
            }
        }