通过excel模板文件根据数据库数据修改其中的单元格数据

/// <summary> 2 /// 根据文件模板生成excel 3 /// </summary> 5 /// <param name="dttew">利润表_月报</param> 11 /// <param name="opetes">文件名</param> 12 /// <param name="queryKey">资产负债表{税款所属期起-税款所属期止}</param> 13 public void LeadExcelone(DataTable dttew, string opetes, string[] queryKey) 14 { 15 string filePath = Server.MapPath("/FilePath/" + "报表申报官方模板.xls");//项目内置文件模板地址 16 using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 17 { 18 MemoryStream ms = new MemoryStream(); 19 var hssfworkbook = new HSSFWorkbook(file); 95 var sheet2 = hssfworkbook.GetSheetAt(1);//获取文件第二个sheet 96 row3 = 4; 97 if (dttew != null && dttew.Rows.Count > 0) 98 { 99 for (int i = 0; i < dttew.Rows.Count; i++) 100 { 101 sheet2.GetRow(row3).GetCell(2).SetCellValue(dttew.Rows[i]["F_BalanceCP"].ToString() == "" ? "0.00" : dttew.Rows[i]["F_BalanceCP"].ToString()); 102 sheet2.GetRow(row3).GetCell(3).SetCellValue(dttew.Rows[i]["F_BalanceCY"].ToString() == "" ? "0.00" : dttew.Rows[i]["F_BalanceCY"].ToString()); 103 row3++; 104 } 105 } 223 hssfworkbook.Write(ms); 224 System.Web.HttpContext.Current.Response.Clear(); 225 System.Web.HttpContext.Current.Response.ClearHeaders(); 226 System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); 227 System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.sheet.binary.macroEnabled.12"; 228 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename="+ opetes + ".xls"); 229 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); 230 //清除缓存 231 System.Web.HttpContext.Current.Response.Flush(); 232 System.Web.HttpContext.Current.Response.End(); 233 //关闭缓冲区 234 ms.Close(); 235 } 236 }

 

通过excel模板文件根据数据库数据修改其中的单元格数据

标签：memory   datatable   count   数据   clear   type   exce   binary   xls