[C#] 将DataSet内容导入到Excel (矩阵区域导出)

   

    /// <summary>
    /// 将DataSet内容导入到Excel
    /// </summary>
    /// <param name="dsExport">DataSet</param>
    public static void FACTDataSetToExcel(System.Data.DataSet dsExport,string _fileName)
    {
        //保存文件名
        string FILE_NAME = ""+_fileName+".xls";
        //数据DataTable
        System.Data.DataTable dt = dsExport.Tables[0];
        string _cellStr = string.Empty;
        //列名
        string strColumnName = string.Empty;
        ApplicationClass xlApp = new ApplicationClass();   
        Workbook wbs = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Worksheet worksheet = (Worksheet)wbs.Worksheets[1];
        #region 导出Excel保存至服务器目录
        if(dt.Rows.Count >0)
        {
            // 设置列名
            for(int head = 0;head<dt.Columns.Count;head++)
            {               
                worksheet.Cells[1,head+1] = dt.Columns[head].ColumnName.ToString();;
                Range rg = (Range)worksheet.Cells[1,head+1];
            }
            try
            {
                object[,] obj = new object[dt.Rows.Count,dt.Columns.Count];
                for(int k=0;k<dt.Rows.Count;k++)
                {
                    for(int l=0;l<dt.Columns.Count;l++)
                    {
                        if (dt.Columns[l].ColumnName.Equals("NET PRICE") || dt.Columns[l].ColumnName.Equals("TRACKING NUMBER")|| dt.Columns[l].ColumnName.Equals("SALES ORDER NO.")|| dt.Columns[l].ColumnName.Equals("DELIVERY DATE")|| dt.Columns[l].ColumnName.Equals("EAN/UPC NUMBER"))
                        {
                            obj[k,l] = "\'"+ dt.Rows[k][l].ToString();
                        }
                        else
                        {
                            obj[k,l] = dt.Rows[k][l].ToString();
                        }
                    }
                }
                Range rangeObj = worksheet.get_Range("A2",Missing.Value);
                rangeObj = rangeObj.get_Resize(dt.Rows.Count,dt.Columns.Count);
                rangeObj.set_Value(Missing.Value,obj);
                wbs.Saved = true;
                wbs.SaveCopyAs(HttpContext.Current.Server.MapPath(".")+"\\FactReports\\"+FILE_NAME);
            }
            #region 异常
            catch
            {
                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();

                    xlApp.Quit();

                    int generation = System.GC.GetGeneration(xlApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                    xlApp = null;
                    System.GC.Collect(generation);
                }

                GC.Collect();//强行销毁

                KillProcess();
            }
                #endregion
            finally
            {
                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();

                    xlApp.Quit();

                    int generation = System.GC.GetGeneration(xlApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                    xlApp = null;
                    System.GC.Collect(generation);
                }

                GC.Collect();//强行销毁

                KillProcess();
            }               
        }
        #endregion
        else
        {
            if (xlApp != null)
            {
                xlApp.Workbooks.Close();

                xlApp.Quit();

                int generation = System.GC.GetGeneration(xlApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;
                System.GC.Collect(generation);
            }

            GC.Collect();//强行销毁

            KillProcess();
        }
    }

    /// <summary>
    /// 强行杀Excel进程
    /// </summary>
    private static void KillProcess()
    {
        System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        System.DateTime startTime = new DateTime();
        int m, killId = 0;
        for (m = 0; m < excelProc.Length; m++)
        {
            if (startTime < excelProc[m].StartTime)
            {
                startTime = excelProc[m].StartTime;
                killId = m;
            }
        }
        if (excelProc[killId].HasExited == false)
        {
            excelProc[killId].Kill();
        }
    }