匯出Excel~~~不使用tool產生標準excel格式的xls

前置：
需要準備一個excel的範本，標題同匯出結果的標題(類似ExcelSpeedGen的做法)
需把唯讀勾掉
--------------------------------------------------------------------------------

說明：
在程式中利用範本複製一份到temp資料夾
然後一筆一筆insert data進去複製的excel中
再將temp資料夾的excel匯出 最後刪除
--------------------------------------------------------------------------------

範例：

using System.Data.OleDb

System.Data.OleDb.OleDbCommand cmd;
System.IO.FileInfo fileinfo ; //從程式中抓取樣版檔案
if(sCarCategory == "1")
{
fileinfo = new System.IO.FileInfo(Server.MapPath("ExportCarISC_G.xls"));
}
else
{
fileinfo = new System.IO.FileInfo(Server.MapPath("ExportCarISC_M.xls"));
}
//複製一份到temp資料夾
fileinfo.CopyTo(Global.strFileSavePath + sFileName);
string Conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Global.strFileSavePath + sFileName + ";" + "Extended Properties='Excel 8.0;HDR=Yes;MAXSCANROWS=16;Persist Security Info=False'";
System.Data.OleDb.OleDbConnection ExcelConn = new System.Data.OleDb.OleDbConnection(Conn);
DataExport csDE = new DataExport(CarCertifDB, CarCertifCmd);
DataSet ds = csDE.C_GetData(UserID, IDNo);
string dcN="", dcV="", sql="";
#region Title
dcN += @"[車輛種類],[審驗單位],[合格證明編號],[委託客戶統編],[委託客戶名稱], . . [HC(ppm)],[轉速(rpm)],[蒸發HC(g/test)] ";
#endregion

#region detail
for(int i=0 ;i<….Count;i++)
{
dcV="";
dcV += "'"+ds.Tables[0].Rows[i]["CarCategory"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CertiUnit"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CertificateNo"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["Comp_IDNum"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CName"].ToString()+"'," ... +"'"+ds.Tables[0].Rows[i]["LazyHC"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["LazySpeed"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["HCEVAP_R"].ToString()+"'";
sql = "Insert into [Sheet1$] (" + dcN + ") values (" + dcV + ")";
cmd = new System.Data.OleDb.OleDbCommand(sql, ExcelConn);
if(ExcelConn.State == ConnectionState.Closed)
ExcelConn.Open();
cmd.ExecuteNonQuery();
}
#endregion
ExcelConn.Close();
//從temp資料夾抓取檔案
fileinfo = new System.IO.FileInfo(Global.strFileSavePath + sFileName);
if(fileinfo.Exists)
{
Response.ClearHeaders();
Response.Clear();
string attachment = "attachment; filename=" + sFileName;
Response.AddHeader("Accept-Language", "zh-tw");
Response.AddHeader("Content-Disposition", attachment);
Response.AddHeader("Content-Length", fileinfo.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(fileinfo.FullName);
Response.Flush();
//刪除temp中該檔案
fileinfo.Delete();
Response.End();
}