c#產生Excel，可建立sheet,設定sheet名稱，調整列寬度

Excel產生類  ExcelRW.cs

using System;<br />using System.Data;<br />using System.Configuration;<br />using System.Web;<br />using System.Web.Security;<br />using System.Web.UI;<br />using System.Web.UI.WebControls;<br />using System.Web.UI.WebControls.WebParts;<br />using System.Web.UI.HtmlControls;<br />using Microsoft.Office.Interop.Excel;<br />using System.Collections;<br />using System.Diagnostics;<br />/// <summary><br />/// Summary description for ExcelRW<br />/// </summary><br />public static class ExcelRW<br />{</p><p> public static void ExcelSave(Hashtable ht)<br /> {<br /> Application excel = new Application();//引用Excel對象<br /> Workbooks oBooks;<br /> Workbook oBook;<br /> Sheets oSheets;<br /> Worksheet oSheet;<br /> Range oCells;<br /> string sFile = ht["excelName"] as string;<br /> //excel.Save("ddd.xls");<br /> ArrayList alSheet = ht["sheetNameList"] as ArrayList;<br /> Workbook wb = excel.Workbooks.Add(true);//引用Excel活頁簿<br /> excel.Visible = false;//使Excel可視<br /> oBooks = excel.Workbooks;<br /> oBook = oBooks.get_Item(1);<br /> oSheets = oBook.Worksheets;<br /> oSheet = (Worksheet)oSheets.get_Item(oSheets.Count);<br /> foreach (Hashtable htOneSheel in alSheet)<br /> {</p><p> oSheet = (Worksheet)oSheets.get_Item(oSheets.Count);<br /> ArrayList ColNameList = htOneSheel["ColName"] as ArrayList;<br /> //命名該sheet<br /> oSheet.Name = htOneSheel["sheetName"] as string;<br /> System.Data.DataTable dtable = htOneSheel["dataRows"] as System.Data.DataTable;<br /> Range column = ((Range)oSheet.Cells[1, 1]).EntireColumn;<br /> column.ColumnWidth = 10;<br /> int iRow = 1;<br /> foreach (DataRow dr in dtable.Rows)<br /> {<br /> int icel = 1;<br /> for (int iCol = 0; iCol < ColNameList.Count; iCol++)<br /> {<br /> oSheet.Cells[iRow, icel++] = Convert.ToString(dr[ColNameList[iCol].ToString()]);</p><p> }<br /> iRow++;<br /> }<br /> oSheets.Add(Type.Missing, oSheets[oSheets.Count], 1, Type.Missing);<br /> //設定第一個sheet未活動sheet<br /> //((Worksheet)oSheets.get_Item(1)).Activate();<br /> oSheet = (Worksheet)oSheets.get_Item(1);<br /> }<br /> string strFilePathAndname = HttpContext.Current.Server.MapPath(".") + "/Excel/" + sFile;<br /> if (System.IO.File.Exists(strFilePathAndname))<br /> {<br /> System.IO.File.Delete(strFilePathAndname);<br /> }<br /> //excel.Workbooks.get_Item(1).Worksheets.Select(excel.Workbooks.get_Item(1).Worksheets[1]);<br /> oSheet.SaveAs(strFilePathAndname, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);<br /> oBook.Close(false, Type.Missing, Type.Missing);<br /> //退出Excel，並且釋放調用的COM資源<br /> excel.Quit();<br /> GC.Collect();<br /> //excel.Save("ddd.xls");<br /> KillProcess("Excel");<br /> }</p><p> public static void Out2Excel(string sTableName, string url)<br /> {<br /> Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();<br /> Workbooks oBooks;<br /> Workbook oBook;<br /> Sheets oSheets;<br /> Worksheet oSheet;<br /> Range oCells;<br /> string sFile = "", sTemplate = "";<br /> //<br /> System.Data.DataTable dt = null;// TableOut(sTableName).Tables[0];<br /> sFile = url + "myExcel.xls";<br /> sTemplate = url + "MyTemplate.xls";<br /> //<br /> oExcel.Visible = false;<br /> oExcel.DisplayAlerts = false;<br /> //定義一個新的活頁簿<br /> oBooks = oExcel.Workbooks;<br /> 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);<br /> oBook = oBooks.get_Item(1);<br /> oSheets = oBook.Worksheets;<br /> oSheet = (Worksheet)oSheets.get_Item(1);<br /> //命名該sheet<br /> oSheet.Name = "Sheet1";<br /> //(Worksheet)oSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);<br /> oCells = oSheet.Cells;<br /> //調用dumpdata過程，將資料匯入到Excel中去<br /> //DumpData(dt, oCells);<br /> //儲存<br /> oSheet.SaveAs(sFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);<br /> oBook.Close(false, Type.Missing, Type.Missing);<br /> //退出Excel，並且釋放調用的COM資源<br /> oExcel.Quit();<br /> GC.Collect();<br /> KillProcess("Excel");<br /> }<br /> private static void KillProcess(string processName)<br /> {<br /> System.Diagnostics.Process myproc = new System.Diagnostics.Process();<br /> //得到所有開啟的進程<br /> try<br /> {<br /> foreach (Process thisproc in Process.GetProcessesByName(processName))<br /> {<br /> if (!thisproc.CloseMainWindow())<br /> {<br /> thisproc.Kill();<br /> }<br /> }<br /> }<br /> catch (Exception Exc)<br /> {<br /> throw new Exception("", Exc);<br /> }<br /> }<br />}<br />

測試使用代碼

testExcelDown.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="testExcelDown.aspx.cs" Inherits="testExcelDown" %><br /><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br /><html xmlns="http://www.w3.org/1999/xhtml" ><br /><head runat="server"><br /> <title>Untitled Page</title><br /></head><br /><body><br /> <form id="form1" runat="server"><br /> <div><br /> <asp:Button ID="bnt_test" runat="server" OnClick="bnt_test_Click" Text="測試下載" /></div><br /> </form><br /></body><br /></html><br />

testExcelDown.aspx.cs

using System;<br />using System.Data;<br />using System.Configuration;<br />using System.Collections;<br />using System.Web;<br />using System.Web.Security;<br />using System.Web.UI;<br />using System.Web.UI.WebControls;<br />using System.Web.UI.WebControls.WebParts;<br />using System.Web.UI.HtmlControls;<br />public partial class testExcelDown : System.Web.UI.Page<br />{<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> }<br /> protected void bnt_test_Click(object sender, EventArgs e)<br /> {<br /> DataSearch();<br /> }</p><p> /// <summary><br /> /// 產生excel資料準備<br /> /// </summary><br /> private void DataSearch()<br /> {<br /> ArrayList arColName = new ArrayList();<br /> ArrayList alData = new ArrayList();</p><p> arColName.Add("title");<br /> arColName.Add("url");<br /> DataTable dtSheet1 = new DataTable();<br /> dtSheet1.Columns.Add(new DataColumn("title"));<br /> dtSheet1.Columns.Add(new DataColumn("url"));<br /> //sheet1資料準備</p><p> DataRow dr = dtSheet1.NewRow();<br /> dr["title"] = "百度" ;<br /> dr["url"] = "http://www.baidu.com";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "400電話";<br /> dr["url"] = "http://www.my400800.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "goole";<br /> dr["url"] = "http://www.google.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "sina";<br /> dr["url"] = "http://www.sina.com.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "tel4006";<br /> dr["url"] = "http://www.tel4006.com";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "soso";<br /> dr["url"] = "http://www.soso.com";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "gougou";<br /> dr["url"] = "http://www.gougou.com";<br /> dtSheet1.Rows.Add(dr);<br /> Hashtable htOneType = new Hashtable();<br /> htOneType["dataRows"] = dtSheet1;<br /> htOneType["ColName"] = arColName;<br /> htOneType["sheetName"] = "網站實用大全1";<br /> alData.Add(htOneType);<br /> //sheet2資料準備<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "百度MP3";<br /> dr["url"] = "http://MP3.baidu.com";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "400電話搜尋";<br /> dr["url"] = "http://blog.my400800.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "goole地圖";<br /> dr["url"] = "http://map.google.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "sina Blog";<br /> dr["url"] = "http://blog.sina.com.cn";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "tel4006 搜尋";<br /> dr["url"] = "http://www.tel4006.com/400";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "soso";<br /> dr["url"] = "http://www.soso.com";<br /> dtSheet1.Rows.Add(dr);<br /> dr = dtSheet1.NewRow();<br /> dr["title"] = "gougou";<br /> dr["url"] = "http://www.gougou.com";<br /> dtSheet1.Rows.Add(dr);<br /> htOneType = new Hashtable();<br /> htOneType["dataRows"] = dtSheet1;<br /> htOneType["ColName"] = arColName;<br /> htOneType["sheetName"] = "網站實用大全2";<br /> alData.Add(htOneType);</p><p> Hashtable htAllData = new Hashtable();<br /> htAllData["sheetNameList"] = alData;</p><p> htAllData["excelName"] = "網站實用大全.xls";<br /> ExcelRW.ExcelSave(htAllData);<br /> Response.Redirect("Excel/" + htAllData["excelName"] as string);<br /> }<br />}<br />