导出Dataset到Excel，并保持格式。

说明：本文有参考网上的代码，部份内容为本人修改。

开发环境：C# 2005 (WinForm)

因项目需要，要实现将DataSet导出到Excel，同时要求：

1. 并且操持格式不变。
2. WorkSheet以DataTable的Name命名。
3. 速度快。
4. 不用保存文件，导出后直接打开这个Excle文件即可。

     （因为不保存的话，不能杀死这个新产生的进程，所以是先保存，然后再打开这个文件。）

在网上找了一些代码，试了以后发现有此下问题：

1.  如果直接打开，则不能杀死产生的Excel进程。
2. 会将其他正常的Excel进程杀死

经过一个下午的反复测试，终于可以了，代码如下：

using Microsoft.Office.Interop.Excel;



        /// <summary>

        /// 导致DataSet到Excel

        /// </summary>

        /// <param name="dataSet">DataSet</param>

        public static void ExportToExcel(DataSet dataSet)

        {

           

            try

            {

                // Create the Excel Application object

                //ApplicationClass excelApp = new ApplicationClass();

                string outputPath = System.Windows.Forms.Application.StartupPath + @"/Excel/123.xls";   

                Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

                excelApp.DisplayAlerts = false;//保存时不提示  



                //System.Diagnostics.Process xlPrc;

                System.Diagnostics.Process[] xlPrcArray;

                xlPrcArray = System.Diagnostics.Process.GetProcessesByName("EXCEL");



                //确定新建Excel文件的进程

                System.Diagnostics.Process newExcelPrc = xlPrcArray[0];

                for (int i = 0; i <= xlPrcArray.Length - 1; i++)

                {

                    if (xlPrcArray[i].TotalProcessorTime.TotalSeconds < newExcelPrc.TotalProcessorTime.TotalSeconds)

                    {

                        newExcelPrc = xlPrcArray[i];                       



                    }

                }



                // Create a new Excel Workbook

                Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);



                int sheetIndex = 0;



                // Copy each DataTable

                foreach (System.Data.DataTable dt in dataSet.Tables)

                {



                    // Copy the DataTable to an object array

                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];



                    // Copy the column names to the first row of the object array

                    for (int col = 0; col < dt.Columns.Count; col++)

                    {

                        rawData[0, col] = dt.Columns[col].ColumnName;

                    }



                    // Copy the values to the object array

                    for (int col = 0; col < dt.Columns.Count; col++)

                    {

                        //字符型前面要加上：'

                        if (dt.Columns[col].DataType == System.Type.GetType("System.String"))

                        {

                            for (int row = 0; row < dt.Rows.Count; row++)

                            {

                                rawData[row + 1, col] = "'" + dt.Rows[row].ItemArray[col].ToString();

                            }

                        }

                        else

                        {

                            for (int row = 0; row < dt.Rows.Count; row++)

                            {

                                rawData[row + 1, col] = dt.Rows[row].ItemArray[col].ToString();

                            }

                        }





                    }



                    // Calculate the final column letter

                    string finalColLetter = string.Empty;

                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

                    int colCharsetLen = colCharset.Length;



                    if (dt.Columns.Count > colCharsetLen)

                    {

                        finalColLetter = colCharset.Substring(

                            (dt.Columns.Count - 1) / colCharsetLen - 1, 1);

                    }



                    finalColLetter += colCharset.Substring(

                            (dt.Columns.Count - 1) % colCharsetLen, 1);



                    // Create a new Sheet

                    Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(

                        excelWorkbook.Sheets.get_Item(++sheetIndex),

                        Type.Missing, 1, XlSheetType.xlWorksheet);



                    excelSheet.Name = dt.TableName;



                    // Fast data export to Excel

                    string excelRange = string.Format("A1:{0}{1}",

                        finalColLetter, dt.Rows.Count + 1);



                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;



                    // Mark the first row as BOLD

                    ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;

                }



                // Save and Close the Workbook

                excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excelWorkbook.Close(true, Type.Missing, Type.Missing);

                excelWorkbook = null;



                 Release the Application object



                excelApp.Quit();

                excelApp = null;

                

                // Collect the unreferenced objects

                GC.Collect();

                GC.WaitForPendingFinalizers();

     

                newExcelPrc.Kill();  

                                

                //打开刚才导出的文件

                Process.Start(outputPath);  

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }





        }