NPOIはexcelテンプレートでデータを書き込みエクスポート
4788 ワード
private void ToExcel(string id)
{
//
string TempletFileName = Server.MapPath("template.xls");
//
string ReportFileName = Server.MapPath("out.xls");
FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet ws = hssfworkbook.GetSheet("Sheet1");
// WorkSheet
System.Data.DataTable dt = new System.Data.DataTable();
dt = DbHelperMySQLnew.Query("select * from t_jb_info where id='" + id + "'").Tables[0];
#region
if (dt.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(dt.Rows[0]["blrq"].ToString()))
{
DateTime blrq = DateTime.Parse(dt.Rows[0]["blrq"].ToString());
ws.GetRow(7).GetCell(4).SetCellValue("56565");
ws.GetRow(7).GetCell(6).SetCellValue(blrq.Month.ToString());
ws.GetRow(7).GetCell(8).SetCellValue(blrq.Day.ToString());
ws.GetRow(1).GetCell(2).SetCellValue(blrq.Year.ToString());
ws.GetRow(1).GetCell(4).SetCellValue(blrq.Month.ToString());
ws.GetRow(1).GetCell(6).SetCellValue(blrq.Day.ToString());
//strnian = strblnian = blrq.Year.ToString();
//stryue = strblyue = blrq.Month.ToString();
//strri = strblri = blrq.Day.ToString();
}
ws.GetRow(1).GetCell(10).SetCellValue(id);
// strbh = id;
ws.GetRow(2).GetCell(1).SetCellValue(dt.Rows[0]["fyr"].ToString());
ws.GetRow(2).GetCell(4).SetCellValue(dt.Rows[0]["dizhi"].ToString());
ws.GetRow(2).GetCell(10).SetCellValue(dt.Rows[0]["lxdh"].ToString());
ws.GetRow(3).GetCell(1).SetCellValue(dt.Rows[0]["bfyr"].ToString());
ws.GetRow(5).GetCell(0).SetCellValue(dt.Rows[0]["wtnr"].ToString());
if (!string.IsNullOrEmpty(dt.Rows[0]["chuliyijian"].ToString()))
{
ws.GetRow(6).GetCell(1).SetCellValue(dt.Rows[0]["chuliyijian"].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[0]["ldps"].ToString()))
{
ws.GetRow(8).GetCell(1).SetCellValue(dt.Rows[0]["ldps"].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[0]["psrq"].ToString()))
{
DateTime blrq = DateTime.Parse(dt.Rows[0]["psrq"].ToString());
ws.GetRow(10).GetCell(4).SetCellValue(blrq.Year.ToString());
ws.GetRow(10).GetCell(6).SetCellValue(blrq.Month.ToString());
ws.GetRow(10).GetCell(8).SetCellValue(blrq.Day.ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[0]["jgrq"].ToString()))
{
DateTime blrq = DateTime.Parse(dt.Rows[0]["jgrq"].ToString());
ws.GetRow(12).GetCell(4).SetCellValue(blrq.Year.ToString());
ws.GetRow(12).GetCell(6).SetCellValue(blrq.Month.ToString());
ws.GetRow(12).GetCell(8).SetCellValue(blrq.Day.ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[0]["jieguozt"].ToString()))
{
ws.GetRow(11).GetCell(1).SetCellValue(dt.Rows[0]["jieguozt"].ToString());
}
}
#endregion
ws.ForceFormulaRecalculation = true;
using (FileStream filess = File.OpenWrite(ReportFileName))
{
hssfworkbook.Write(filess);
}
//filess.Close();
System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// , " / "
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("55.xls"));
// , ,
Response.AddHeader("Content-Length", filet.Length.ToString());
// ,
Response.ContentType = "application/ms-excel";
//
Response.WriteFile(filet.FullName);
//
Response.End();
}
dll http://npoi.codeplex.com/releases