MVC web NPOI 组件导出EXCLE设置其背景色方法

来源：互联网发布：青牛软件电话编辑：程序博客网时间：2024/05/18 18:54

把数据导出到EXCLE表格方法:

public EmptyResult SaveExcelList()
        {
            NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            //HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            // 新增页。
            ISheet sheet = workbook.CreateSheet(string.Format("检验时间维护"));

            List<ViewColorDesign> datalist = GetNewColorList2();
            //设置表头。
            IRow headerRow = sheet.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("大类");
            headerRow.GetCell(0).CellStyle = GetCellsStyle(workbook, "gray");
            headerRow.CreateCell(1).SetCellValue("中类");
            headerRow.GetCell(1).CellStyle = GetCellsStyle(workbook, "gray");
            headerRow.CreateCell(2).SetCellValue("小类");
            headerRow.GetCell(2).CellStyle = GetCellsStyle(workbook, "gray");
            headerRow.CreateCell(3).SetCellValue("2H");
            headerRow.GetCell(3).CellStyle = GetCellsStyle(workbook, "gray");
            headerRow.CreateCell(4).SetCellValue("4H");
            headerRow.GetCell(4).CellStyle = GetCellsStyle(workbook, "gray");
            headerRow.CreateCell(5).SetCellValue("4H+");
            headerRow.GetCell(5).CellStyle = GetCellsStyle(workbook, "gray");

            int z = 0;
            foreach (var row in datalist)
            {
                z++;
                IRow dataRow = sheet.CreateRow(z);
                dataRow.CreateCell(0).SetCellValue(row.bigclass);
                dataRow.CreateCell(1).SetCellValue(row.secclass);
                dataRow.CreateCell(2).SetCellValue(row.thirdclass);
                dataRow.CreateCell(3).SetCellValue(row.h2color);
                dataRow.GetCell(3).CellStyle = GetCellsStyle(workbook,row.h2color);
                dataRow.CreateCell(4).SetCellValue(row.h4color);
                dataRow.GetCell(4).CellStyle = GetCellsStyle(workbook, row.h4color);
                dataRow.CreateCell(5).SetCellValue(row.h5color);
                dataRow.GetCell(5).CellStyle = GetCellsStyle(workbook, row.h5color);
            }
            workbook.Write(ms);
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("检验时间维护" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx", System.Text.Encoding.UTF8));
            Response.BinaryWrite(ms.ToArray());
            return new EmptyResult();
        }

设置背景色方法：

private static ICellStyle GetCellsStyle(NPOI.XSSF.UserModel.XSSFWorkbook workbook,string color)
        {
            ICellStyle CellsStyle = workbook.CreateCellStyle();
            CellsStyle.Alignment = HorizontalAlignment.Center;
            //边框
            CellsStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            CellsStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            CellsStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            CellsStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            CellsStyle.FillPattern = FillPattern.SolidForeground;
            switch(color)
            {
                case "white":
                    CellsStyle.FillForegroundColor = IndexedColors.White.Index;
                    break;
                case "red":
                    CellsStyle.FillForegroundColor = IndexedColors.Red.Index;
                    break;
                case "yellow":
                    CellsStyle.FillForegroundColor = IndexedColors.Yellow.Index;
                    break;
                case "lime":
                    CellsStyle.FillForegroundColor = IndexedColors.BrightGreen.Index;
                    break;
                case "gray":
                    CellsStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
                    break;
                default:
                    CellsStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
                    break;
            }
            //CellsStyle.FillBackgroundColor = IndexedColors.BrightGreen.Index;

            return CellsStyle;
        }

附截图

0 1