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

    }
}