[笔记]C#下载自订格式的Excel

首先须先安装NuGet套件---->NPOI

using需要的项目

using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.SS.Util;using System.IO;

程式码如下

public ActionResult TEST_EXCEL(){  try  {    //建立Excel    HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //建立活页簿    ISheet sheet = hssfworkbook.CreateSheet("sheet"); //建立sheet    //设定标题样式    ICellStyle headerStyle = hssfworkbook.CreateCellStyle();    IFont headerfont = hssfworkbook.CreateFont();    headerStyle.Alignment = HorizontalAlignment.CENTER; //水平置中    headerStyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中    headerfont.FontName = "微软正黑体";    headerfont.FontHeightInPoints = 12;    headerfont.Boldweight = (short)FontBoldWeight.BOLD;//粗体    headerStyle.SetFont(headerfont);        //主标题    sheet.CreateRow(1);//先CreateRow建立,才可GetRow取得该栏位    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6)); // 合併第2行(1,1) A~F列储存格(0,6)    sheet.GetRow(1).CreateCell(0).SetCellValue("我是主标题");    sheet.GetRow(1).GetCell(0).CellStyle = headerStyle; //套用样式    //副标题    sheet.CreateRow(2);    sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 6)); // 合併第3行(2,2) A~F列储存格(0,6)    sheet.GetRow(2).CreateCell(0).SetCellValue("我是副标题");    sheet.GetRow(2).GetCell(0).CellStyle = headerStyle;     //设定栏位样式    ICellStyle headerStyle_02 = hssfworkbook.CreateCellStyle();    IFont headerfont_02 = hssfworkbook.CreateFont();    headerStyle_02.Alignment = HorizontalAlignment.CENTER; //水平置中    headerStyle_02.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中    headerStyle_02.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;//设定框限线    headerStyle_02.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;    headerStyle_02.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;    headerStyle_02.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;    headerfont_02.FontName = "微软正黑体";    headerfont_02.FontHeightInPoints = 12;    headerfont_02.Boldweight = (short)FontBoldWeight.BOLD;//粗体    headerStyle_02.SetFont(headerfont_02);    sheet.CreateRow(3).CreateCell(0).SetCellValue("序号");    sheet.GetRow(3).GetCell(0).CellStyle = headerStyle_02;    sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, 5));    sheet.GetRow(3).CreateCell(1).SetCellValue("项目");    sheet.GetRow(3).GetCell(1).CellStyle = headerStyle_02;    //合併栏位设定border需连同被合併的座标一起设    sheet.GetRow(3).CreateCell(2).CellStyle = headerStyle_02;    sheet.GetRow(3).CreateCell(3).CellStyle = headerStyle_02;    sheet.GetRow(3).CreateCell(4).CellStyle = headerStyle_02;    sheet.GetRow(3).CreateCell(5).CellStyle = headerStyle_02;    sheet.GetRow(3).CreateCell(6).SetCellValue("总计");    sheet.GetRow(3).GetCell(6).CellStyle = headerStyle_02;    //设定资料样式(序号、总计)    ICellStyle dataStyle = hssfworkbook.CreateCellStyle();    IFont datafont = hssfworkbook.CreateFont();    dataStyle.Alignment = HorizontalAlignment.CENTER; //水平置中    dataStyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中    dataStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;    dataStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;    dataStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;    dataStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;    datafont.FontName = "微软正黑体";    datafont.FontHeightInPoints = 12;    dataStyle.SetFont(datafont);    //塞资料    //下方为写死资料,可利用迴圈塞资料    //序号    sheet.CreateRow(4).CreateCell(0).SetCellValue("1");    sheet.GetRow(4).GetCell(0).CellStyle = dataStyle;    //案件项目    sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 5));    sheet.GetRow(4).CreateCell(1).SetCellValue("测试资料");    sheet.GetRow(4).GetCell(1).CellStyle = dataStyle;    //合併栏位设定border需连同被合併的座标一起设    sheet.GetRow(4).CreateCell(2).CellStyle = dataStyle;    sheet.GetRow(4).CreateCell(3).CellStyle = dataStyle;    sheet.GetRow(4).CreateCell(4).CellStyle = dataStyle;    sheet.GetRow(4).CreateCell(5).CellStyle = dataStyle;    //总计    sheet.GetRow(4).CreateCell(6).SetCellValue("100");    sheet.GetRow(4).GetCell(6).CellStyle = dataStyle;    var excelDatas = new MemoryStream();    hssfworkbook.Write(excelDatas);    Response.Clear();    Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode("XXX统计表.xls"));    Response.ContentType = "application/octet-stream";    Response.Charset = "gb2312";    Response.ContentEncoding = Encoding.UTF8;    Response.BinaryWrite(excelDatas.GetBuffer());    Response.Flush();    Response.End();    return new EmptyResult();  }  catch (Exception ex)  {     return null;  }}

结果如下