把数据导出到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;
}
附截图