关于导出excel 把一个dataset的多个datatable导入到一个excel的多个sheet中

几经查询资料，我完成了这个看似结果还不错的任务，在解决任务的过程也也遇到了很多的问题，但最后胜利的成果还是要和大家分享一下，

大家看看有没有什么问题，多提提意见，有更好的可以给我看看，我需要。

代码如下

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;
using HCK.BLL;
using HCK.Model;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Collections.Generic;

 

 

中间省略

 

/// <summary)
    /// 导出excel
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="tableNames"></param>
    /// <param name="FileName"></param>
    public void doExport(DataSet ds, string[] tableNames, string FileName)
    {       
        string path = "";       
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        try
        {
            excel.Visible = false;
            //设置禁止弹出保存和覆盖的询问提示框
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;

            //增加一个工作簿
            Workbook book = excel.Workbooks.Add(true);
            //添加工作表
            Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)
                book.Worksheets.Add(Missing.Value, Missing.Value, 9, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

            for (int i = 0; i <ds.Tables.Count; i++)
            {
                System.Data.DataTable table = ds.Tables[i];
                //获取一个工作表
                Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
                int rowIndex = 1;
                int colIndex = 0;

                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    sheet.Cells[1, colIndex] = col.ColumnName;
                }
                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        //sheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        //在这里要在数字前加前单引号
                        String typeName = row[col.ColumnName].GetType().ToString();
                        sheet.Cells[rowIndex, colIndex] = typeCheckAdd(row[col.ColumnName].ToString(), typeName);
                    }
                }
                sheet.Name = tableNames[i];
            }

            path = Server.MapPath("../") + @"excel/" + FileName + ".xls";
            Page.ClientScript.RegisterStartupScript(this.GetType(), "abc", "alert('" + path + "')", true);
            //book.Save();
            book.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //excel.Save(strExcelFileName);

        }
        catch (Exception err)
        {          
            string mess = err.Message.ToString();
            Page.ClientScript.RegisterStartupScript(this.GetType(), "abc", "alert('" + mess + "')", true);
        }
        finally
        {
            excel.Quit();
            //excel = null;
            IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
            int k = 0;
            GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
            p.Kill();
            GC.Collect();
        }
       
        System.IO.FileInfo file = new System.IO.FileInfo(path);
        Response.Clear();
        Response.AddHeader("Content-Disposition", "attachment;   filename=" + HttpUtility.UrlEncode(file.Name));
        Response.AddHeader("Content-Length", file.Length.ToString());
        Response.ContentType = "application/ms-excel";
        Response.WriteFile(file.FullName);
        Response.End();
        //System.IO.FileInfo deletefile = new FileInfo(path);//删除生成的文件
        //if (deletefile.Exists)
        //{
        //    deletefile.Delete();
        //}
    }
    [DllImport("User32.dll", CharSet = CharSet.Auto)]
    public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
    //若是大数需加前导引号变成字符串
    public String typeCheckAdd(String cellContent, String strType)
    {
        String cellContentAdd;
        switch (strType)
        {
            case "System.String":
                cellContentAdd = "'" + cellContent;
                break;
            default:
                cellContentAdd = cellContent;
                break;

        }
        return cellContentAdd;
    }

 

 

 

思路大概就是先把生成的excel生成到项目中，然后下载到客户端，本想着把项目中的excel删除，没有成功，想着反正也没有很多，一直都是一个，也就没有管它了。 对与那个关于excel.exe进程也查了一些资料，成功了，不知道是否理想。大家多多提提意见。