c#生成Excel，可建立sheet,设定sheet名称，调整列宽度

Excel生成类  ExcelRW.cs

 

using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Microsoft.Office.Interop.Excel; using System.Collections; using System.Diagnostics; /// <summary> /// Summary description for ExcelRW /// </summary> public static class ExcelRW { public static void ExcelSave(Hashtable ht) { Application excel = new Application();//引用Excel对象 Workbooks oBooks; Workbook oBook; Sheets oSheets; Worksheet oSheet; Range oCells; string sFile = ht["excelName"] as string; //excel.Save("ddd.xls"); ArrayList alSheet = ht["sheetNameList"] as ArrayList; Workbook wb = excel.Workbooks.Add(true);//引用Excel工作簿 excel.Visible = false;//使Excel可视 oBooks = excel.Workbooks; oBook = oBooks.get_Item(1); oSheets = oBook.Worksheets; oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); foreach (Hashtable htOneSheel in alSheet) { oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); ArrayList ColNameList = htOneSheel["ColName"] as ArrayList; //命名该sheet oSheet.Name = htOneSheel["sheetName"] as string; System.Data.DataTable dtable = htOneSheel["dataRows"] as System.Data.DataTable; Range column = ((Range)oSheet.Cells[1, 1]).EntireColumn; column.ColumnWidth = 10; int iRow = 1; foreach (DataRow dr in dtable.Rows) { int icel = 1; for (int iCol = 0; iCol < ColNameList.Count; iCol++) { oSheet.Cells[iRow, icel++] = Convert.ToString(dr[ColNameList[iCol].ToString()]); } iRow++; } oSheets.Add(Type.Missing, oSheets[oSheets.Count], 1, Type.Missing); //设定第一个sheet未活动sheet //((Worksheet)oSheets.get_Item(1)).Activate(); oSheet = (Worksheet)oSheets.get_Item(1); } string strFilePathAndname = HttpContext.Current.Server.MapPath(".") + "/Excel/" + sFile; if (System.IO.File.Exists(strFilePathAndname)) { System.IO.File.Delete(strFilePathAndname); } //excel.Workbooks.get_Item(1).Worksheets.Select(excel.Workbooks.get_Item(1).Worksheets[1]); 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); oBook.Close(false, Type.Missing, Type.Missing); //退出Excel，并且释放调用的COM资源 excel.Quit(); GC.Collect(); //excel.Save("ddd.xls"); KillProcess("Excel"); } public static void Out2Excel(string sTableName, string url) { Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Workbooks oBooks; Workbook oBook; Sheets oSheets; Worksheet oSheet; Range oCells; string sFile = "", sTemplate = ""; // System.Data.DataTable dt = null;// 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"; //(Worksheet)oSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); oCells = oSheet.Cells; //调用dumpdata过程，将数据导入到Excel中去 //DumpData(dt, oCells); //保存 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); oBook.Close(false, Type.Missing, Type.Missing); //退出Excel，并且释放调用的COM资源 oExcel.Quit(); GC.Collect(); KillProcess("Excel"); } private static 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); } } }

 

 

测试使用代码


testExcelDown.aspx

 

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

 

 

 

testExcelDown.aspx.cs

 

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