excel文本写入 npoi_NPOI 通过excel模板写入数据并导出

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");

//娣诲????淇???orkSheet?????版??

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();

}