你的位置:首页 > ASP.net教程

[ASP.net教程]asp.net mvc NPOI 生成Excel文件


 private string PushToDown(string addtime)    {      DataTable dt = _bCreateCode.PushtoExcel(addtime);      //1、实例化workbook工作簿对象      HSSFWorkbook hssfworkbook = new HSSFWorkbook();      //2、创建文档摘要信息      DocumentSummaryInformation dsf = PropertySetFactory.CreateDocumentSummaryInformation();      dsf.Company = "公司名称";//公司      dsf.Category = "类别";//类别      //CustomProperties 自定义属性      SummaryInformation si = PropertySetFactory.CreateSummaryInformation();      si.Author = "作者";//作者      si.Subject = "序列号";//主题      si.Title = "序列号列表";//标题      //si.RevNumber = "1.0";//版本号      //3、将写好的文档摘要 赋值workbook对象      hssfworkbook.DocumentSummaryInformation = dsf;      hssfworkbook.SummaryInformation = si;      //4、创建Sheet      HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");      //HSSFSheet Sheet2 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet2");      //HSSFSheet Sheet3 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet3");      //5、创建页眉页脚      sheet1.CreateRow(0).CreateCell(1).SetCellValue(123);      sheet1.Header.Center = "统计数据";      sheet1.Header.Left = "logo.png";      sheet1.Header.Right = "address";      sheet1.Footer.Center = "page";      //6、标题      string yeartime = DateTime.Today.Year + "-" + DateTime.Today.Month + "-" + DateTime.Today.Day + "-" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second;      HSSFCell fcell = (HSSFCell)sheet1.CreateRow(0).CreateCell(0);//第一行      fcell.SetCellValue(addtime + "序列号列表");//文本      //合并单元格      sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13));//2.0使用 2.0以下为Region      //标题样式      HSSFCellStyle fCellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      HSSFFont ffont = (HSSFFont)hssfworkbook.CreateFont();      ffont.FontHeight = 20 * 20;      ffont.FontName = "宋体";      ffont.Color = HSSFColor.Black.Index;      fCellStyle.SetFont(ffont);      fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐      fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐      fcell.CellStyle = fCellStyle;      //7、设置单元格格式 创建单元格      /*模拟设定7列*/      HSSFDataFormat dataformat = (HSSFDataFormat)hssfworkbook.CreateDataFormat();//数据格式      HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();//数据字体      font.Color = HSSFColor.Black.Index; //颜色      font.IsItalic = false;//斜体      font.IsStrikeout = false;//加粗      font.FontName = "宋体";//字体      //必不可少 可以变更在循环输出数据时指定类型 需要调用sqlDbType 较复杂      //Id int类型      HSSFCell cell1 = (HSSFCell)sheet1.CreateRow(1).CreateCell(0); //创建单元格      HSSFCellStyle cellStyle1 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();//单元格样式      cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      // CellRangeAddressList ranglist1 = new CellRangeAddressList(0, 65535, 0, 0);//集合限定类型      // DVConstraint constraint1 = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.BETWEEN, "0", "100");//约束      cellStyle1.SetFont(font);      cell1.CellStyle = cellStyle1;      cell1.SetCellValue("");      //Name      HSSFCell cell2 = (HSSFCell)sheet1.CreateRow(1).CreateCell(1);      HSSFCellStyle cellStyle2 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle2.SetFont(font);      cell2.CellStyle = cellStyle2;      cell2.SetCellValue("");      //phone      HSSFCell cell3 = (HSSFCell)sheet1.CreateRow(1).CreateCell(2);      HSSFCellStyle cellStyle3 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle3.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle3.SetFont(font);      cell3.CellStyle = cellStyle3;      cell3.SetCellValue("");      //address      HSSFCell cell4 = (HSSFCell)sheet1.CreateRow(1).CreateCell(3);      HSSFCellStyle cellStyle4 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle4.SetFont(font);      cell4.CellStyle = cellStyle4;      cell4.SetCellValue("");      //Status      HSSFCell cell5 = (HSSFCell)sheet1.CreateRow(1).CreateCell(4);      HSSFCellStyle cellStyle5 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle5.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle5.SetFont(font);      cell5.CellStyle = cellStyle5;      cell5.SetCellValue("");      //balance      HSSFCell cell6 = (HSSFCell)sheet1.CreateRow(1).CreateCell(5);      HSSFCellStyle cellStyle6 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cell6.SetCellValue("");      cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle6.SetFont(font);      cell6.CellStyle = cellStyle6;      //CreateDate      HSSFCell cell7 = (HSSFCell)sheet1.CreateRow(1).CreateCell(6);      HSSFCellStyle cellStyle7 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle7.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle7.SetFont(font);      cell7.CellStyle = cellStyle7;      cell7.SetCellValue("");      HSSFCell cell8 = (HSSFCell)sheet1.CreateRow(1).CreateCell(7);      HSSFCellStyle cellStyle8 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle8.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle8.SetFont(font);      cell8.CellStyle = cellStyle8;      cell8.SetCellValue("");      HSSFCell cell9 = (HSSFCell)sheet1.CreateRow(1).CreateCell(8);      HSSFCellStyle cellStyle9 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle9.SetFont(font);      cell9.CellStyle = cellStyle9;      cell9.SetCellValue("");      HSSFCell cell10 = (HSSFCell)sheet1.CreateRow(1).CreateCell(9);      HSSFCellStyle cellStyle10 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle10.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle10.SetFont(font);      cell10.CellStyle = cellStyle10;      cell10.SetCellValue("");      HSSFCell cell11 = (HSSFCell)sheet1.CreateRow(1).CreateCell(10);      HSSFCellStyle cellStyle11 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle11.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle11.SetFont(font);      cell11.CellStyle = cellStyle11;      cell11.SetCellValue("");      HSSFCell cell12 = (HSSFCell)sheet1.CreateRow(1).CreateCell(11);      HSSFCellStyle cellStyle12 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle12.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle12.SetFont(font);      cell12.CellStyle = cellStyle12;      cell12.SetCellValue("");      HSSFCell cell13 = (HSSFCell)sheet1.CreateRow(1).CreateCell(12);      HSSFCellStyle cellStyle13 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();      cellStyle13.DataFormat = HSSFDataFormat.GetBuiltinFormat("");      cellStyle13.SetFont(font);      cell13.CellStyle = cellStyle13;      cell13.SetCellValue("");      //8、创建单元格 加入数据      HSSFRow r = (HSSFRow)sheet1.CreateRow(1);//第二行 标题      for (int i = 0; i < dt.Columns.Count; i++)      {        r.CreateCell(i).SetCellValue(dt.Columns[i].ToString());      }      if (dt.Rows.Count > 0)      {        for (int i = 0; i < dt.Rows.Count; i++)        {          HSSFRow row = (HSSFRow)sheet1.CreateRow(i + 2);//写入行          for (int j = 0; j < dt.Columns.Count; j++)//写入列          {            row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());          }        }      }      FileStream fs = new FileStream(Server.MapPath("~/PushtoExcel/" + yeartime + ".xls"), FileMode.Create);      hssfworkbook.Write(fs);      fs.Close();      return yeartime + ".xls";    }