C#將數據導出到Execl匯總(3)

上面的方面，均將要導出的execl數據，直接給浏覽器輸出文件流，下面的方法是首先將其存到服務器的某個文件夾中，然後把文件發送到客戶端。這樣可以持久的把導出的文件存起來，以便實現其它功能。

5、將execl文件導出到服務器上，再下載。

二、winForm中導出Execl的方法：

1、方法1：

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.APPSettings["conn"]);
SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
DataSet ds=new DataSet();
da.Fill(ds,"table1");
DataTable dt=ds.Tables["table1"];
string name=System.Configuration.ConfigurationSettings.APPSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路徑，文件格式為當前日期+4位隨機數
FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("自動編號,姓名,年齡");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
Response.ContentType = "application/ms-Excel";// 指定返回的是一個不能被客戶端讀取的流，必須被下載
Response.WriteFile(name); // 把文件流發送到客戶端
Response.End();

public void Out2Excel(string sTableName,string url)
{
Excel.Application oExcel=new Excel.Application();
Workbooks oBooks;
Workbook oBook;
Sheets oSheets;
Worksheet oSheet;
Range oCells;
string sFile="",sTemplate="";
//
System.Data.DataTable dt=TableOut(sTableName).Tables[0];
sFile=url+"myExcel.xls";
sTemplate=url+"MyTemplate.xls";
//
oExcel.Visible=false;
oExcel.DisplayAlerts=false;
//定義一個新的工作簿
oBooks=oExcel.Workbooks;
oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);
oBook=oBooks.get_Item(1);
oSheets=oBook.Worksheets;
oSheet=(Worksheet)oSheets.get_Item(1);
//命名該sheet
oSheet.Name="Sheet1";
oCells=oSheet.Cells;
//調用dumpdata過程，將數據導入到Excel中去
DumpData(dt,oCells);
//保存
oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
oBook.Close(false, Type.Missing,Type.Missing);
//退出Excel，並且釋放調用的COM資源
oExcel.Quit();
GC.Collect();
KillProcess("Excel");
}
private void KillProcess(string processName)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process();
//得到所有打開的進程
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if(!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch(Exception Exc)
{
throw new Exception("",Exc);
}
}