save tables to excel sheets class and down load class

            做了一个对页面的四张表单保存到Excel中的四个Sheet中，并提供下载 ， 本来想在客户端完成操作， 实在没想出来好的解决办法，如果谁有请告诉我， 我实现了先把形成的Excel生成并保存到服务器端，当然服务器端要进行一些配置，允许客户端对服务器端保存Excel。服务器用的是win2003，打开管理中的组件服务，找到DCOM配置 --> Microsoft Excel 应用程序 -- > 属性 ，对访问权限进行自定义配置，添加EveryOne用户，给予全部权限。

            下面给出相应的页面部分代码，和download类和SaveExcel类。

SaveExcel command and close view command;#region SaveExcel command and close view command;
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        删除前8天的Excel，保存最近两天的#region 删除前8天的Excel，保存最近两天的
        for (int i = 2; i < 10; i++)
        {
            string strDirec = Server.MapPath("~/ShareReport/BusyFreeSectionAnalyse/") + DateTime.Now.Date.AddDays(-i).ToLongDateString();
            if (System.IO.Directory.Exists(strDirec))
                Directory.Delete(strDirec, true);
        }
        #endregion

        DataTable[] dt = new DataTable[4];
        dt[0] = MakeTables(m_strXmlBusy, "GetBusyCellTopN");
        dt[1] = MakeTables(m_strXmlBusy, "GetIdleCellTopN");
        dt[2] = MakeTables(m_strXmlBusy, "GetTrafficTopNCell");
        dt[3] = MakeTables(m_strXmlBusy, "GetTrafficBottomNCell");

        if (dt[0].Rows.Count <= 0 || dt[1].Rows.Count <= 0 || dt[2].Rows.Count <= 0 || dt[3].Rows.Count <= 0)
        {
            Server.Transfer("Error.aspx?ErrorCode=" + "TableEmpty");
        }
        else
        {
            string strDirectory = Server.MapPath("~/ShareReport/BusyFreeSectionAnalyse/") + DateTime.Now.ToLongDateString();
            string strFileName = DateTime.Now.Ticks.ToString() + ".xls";
            SavaExcelsToServer saveExc = new SavaExcelsToServer(strDirectory, strFileName, dt);

            if (saveExc.SaveExcel())
            {
                bool fFlag = DownLoad.ResponseFile(Page.Request, Page.Response, strFileName, strDirectory + "\\" + strFileName, 1024000);
                if (!fFlag)
                    Response.Write("下载文件出错,可能已经被移走或删除！");
                Response.Flush();
                Response.End();
            }
        }
    }
    #endregion

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 System.IO;
using System.Threading;

/**//// <summary>
/// DownLoad 的摘要说明
/// </summary>
public class DownLoad
{
    private DownLoad()
    {}

    /**//// <summary>
    /// 下载函数
    /// </summary>
    /// <param name="H_Request">能够读取客户端在WEB请求发送期间发送的HTTP值</param>
    /// <param name="H_Response">封装asp.net操作的HTTP响应信息</param>
    /// <param name="FileName">文件名：aa.txt</param>
    /// <param name="FullPath">绝对路径：Server.MapPath("Department"+Str_FullPath)//Str_FullPath=\aa.txt</param>
    /// <param name="Speed">当前线程被阻塞的毫秒数</param>
    /// <returns></returns>
    public static bool ResponseFile(HttpRequest H_Request, HttpResponse H_Response, string FileName, string FullPath, long Speed)
    {
        try
        {
            System.IO.FileStream File = new FileStream(FullPath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
            System.IO.BinaryReader BR = new BinaryReader(File);
            try
            {
                H_Response.AddHeader("Accept-Ranges", "bytes");
                H_Response.Buffer = false;
                long Long_FileLength = File.Length;
                long Long_StartBytes = 0;

                int Int_Pack = 10240;
                int Int_Sleep = (int)Math.Floor((float)(1000 * Int_Pack / Speed)) + 1;
                if (H_Request.Headers["Range"] != null)
                {
                    H_Response.StatusCode = 200;
                    string[] Str_Range = H_Request.Headers["Range"].Split(new char[] { '=', '-' });
                    Long_StartBytes = Convert.ToInt64(Str_Range[1]); ;
                }
                H_Response.AddHeader("Content-Length", (Long_FileLength - Long_StartBytes).ToString());
                if (Long_StartBytes != 0)
                {
                    H_Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", Long_StartBytes, Long_FileLength - 1, Long_FileLength));
                }
                H_Response.AddHeader("Connection", "Keep-Alive");
                H_Response.ContentType = "application/octet-stream";
                H_Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8));

                BR.BaseStream.Seek(Long_StartBytes, SeekOrigin.Begin);
                int maxCount = (int)Math.Floor((float)((Long_FileLength - Long_StartBytes) / Int_Pack)) + 1;
                for (int i = 0; i < maxCount; i++)
                {
                    if (H_Response.IsClientConnected)
                    {
                        H_Response.BinaryWrite(BR.ReadBytes(Int_Pack));
                        Thread.Sleep(Int_Sleep);
                    }
                    else
                    {
                        i = maxCount;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                BR.Close();
                File.Close();
            }
            return true;
        }
        catch
        {
            return false;
        }
    }
}

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 System.IO;
using Excel = Microsoft.Office.Interop.Excel;

/**//// <summary>
/// SavaExcelsToServer 是把多个表保存在多个sheet中
/// </summary>
public class SavaExcelsToServer
{
    private string m_strDirectory;//Excel文件夹
    private string m_strFileName;//Excel文件名
    private object[] m_ColumnHead;//表列头对象
    private DataTable[] m_dataTable;//导入Excel的表

    /**//// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="strDirectory">存Excel的文件夹路径</param>
    /// <param name="strFileName">存Excel文件名,Excel文件一定要放到strDirectory文件夹内</param>
    /// <param name="strColName">列头名数组</param>
    public SavaExcelsToServer(string strDirectory, string strFileName, DataTable[] dataTable)
    {
        m_strDirectory = strDirectory;
        m_strFileName = strFileName;
        m_dataTable = dataTable;
        
        if (!System.IO.Directory.Exists(m_strDirectory))
        {
            Directory.CreateDirectory(m_strDirectory);
        }
        if (File.Exists(Path.Combine(m_strDirectory, m_strFileName)))
        {
            File.Delete(m_strDirectory + "\\" + m_strFileName);
        }
    }

    /**//// <summary>
    /// 保存Excel
    /// </summary>
    public bool SaveExcel()
    {
        int nCount = 0;     //所有表列的记数 
        int nRowCount = 0;  //行数量
        int m_ColCount = 0; //列数量
        int nSeries = 0;    //数组维数
        string[] strColName;//表头名数组

        object objValue = System.Type.Missing;
        object A1;//Excel的列头对象
        object H1;//Excel的列头对象
        object A2;//Excel的行对象
        object H2;//Excel的行对象
        Excel.Application EApp;

        nSeries = m_dataTable.Length;
        EApp = new Excel.ApplicationClass();

        if (EApp == null)
        {
            Console.WriteLine("不能打开Excel。");
            return false;
        }
        else
        {
            Excel.Workbooks xlWookBooks = EApp.Workbooks;
            Excel.Workbook xlWorkBook = xlWookBooks.Add(true);//或者根据绝对路径打开工作簿文件a.xls     
            Excel.Sheets xlSheets = xlWorkBook.Worksheets;
          
            for (int i = 0; i < nSeries; i++)
            {
                nRowCount = m_dataTable[i].Rows.Count;
                m_ColCount = m_dataTable[i].Columns.Count;
                m_ColumnHead = new object[m_ColCount];
                strColName = new string[m_ColCount];
                for (int j = 0; j < m_ColCount; j++)
                {
                    try { strColName[j] = m_dataTable[i].Columns[j].Caption; }
                    catch { strColName[j] = string.Empty; }
                }
               
                Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[i + 1], Type.Missing, Type.Missing);
                xlWorkSheet.Name = m_dataTable[i].TableName;

                //对象变量初始化
                A1 = xlWorkSheet.Cells[1, 1];
                H1 = xlWorkSheet.Cells[1, m_ColCount];
                A2 = xlWorkSheet.Cells[2, 1];
                H2 = xlWorkSheet.Cells[2, m_ColCount];

                Excel.Range Range = xlWorkSheet.get_Range(A1, H1);
                Range.Columns.HorizontalAlignment = 3;//value 水平居中

                //设置Excle的表头

                for (int k = 0; k < m_ColCount; k++)
                {
                    try { m_ColumnHead[k] = (object)strColName[k]; }
                    catch { m_ColumnHead[k] = (object)string.Empty; }
                }
                Range.Value2 = m_ColumnHead;
                Range.Interior.ColorIndex = 0;

                //向Excel中写入数据
                Range = xlWorkSheet.get_Range(A2, H2);
                object[,] ColumnData = new object[nRowCount, m_ColCount];

                for (int n = 0; n < nRowCount; n++)
                {
                    for (int j = 0; j < m_ColCount; j++)
                    {
                        try { ColumnData[n, j] = m_dataTable[i].Rows[n][j]; }
                        catch { ColumnData[n, j] = string.Empty; }
                    }
                }
                Range = Range.get_Resize(nRowCount, m_ColCount);
                Range.Value2 = ColumnData;
                //设置Excel的属性
                Range.Interior.ColorIndex = 0; //设置其Excel的背景颜色
                Range.Columns.HorizontalAlignment = 3; //value 水平居中
                Range.Cells.EntireColumn.AutoFit(); //Excel的表格宽度
            }
            Excel.Worksheet xlWorkSheet14 = (Excel.Worksheet)xlSheets[1];
            xlWorkSheet14.Delete();
            //保存Excel文件
            EApp.Visible = false;
            EApp.UserControl = false;
            EApp.DisplayAlerts = false;
            xlWorkBook.Saved = true;
            //EApp.ActiveWorkbook.SaveCopyAs(a);
            xlWorkBook.SaveCopyAs(m_strDirectory + "\\" + m_strFileName);
            
            //释放资源
            xlWorkBook.Close(false, Type.Missing, Type.Missing);
            EApp.Quit();
            return true;
        }
    }
}