C#將DataTable資料匯出到EXCEL的兩種方法

標籤：type   sys   csharp   erro   iss   exclusive   column   asp   sheet   

1、在非伺服器控制項的頁面匯出資料，需要藉助一張temp空頁面post回背景資料。

前台：window.location.href = "../Temp.aspx";

後台：　try{

　　　　dtSource = Session["MyDataTable"] //假設資料在Session中　　　　

　　　　if (dtSource == null || dtSource.Rows.Count == 0)
            {
                return;
            }
            DataGrid dg = new DataGrid();
            dg.DataSource = dtSource;
            dg.DataBind();

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "UTF-8";//GB2312
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";//text/csv
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");

            System.IO.StringWriter oSW = new System.IO.StringWriter();
            HtmlTextWriter oHW = new HtmlTextWriter(oSW);
            dg.RenderControl(oHW);
            HttpContext.Current.Response.Write(oSW.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();

　　　　}catch(e){

　　　　log.Error(e);
            Response.Redirect("原頁面.aspx");

　　　　}

　2、通過引用　Microsoft.Office.Interop.Excel.dll 和 Microsoft.CSharp.dll，不推薦，伺服器需要安裝Office才行　

/// <summary>
        /// 該方法需要引用Microsoft.Office.Interop.Excel.dll 和 Microsoft.CSharp.dll
        /// 將資料由DataTable匯出到Excel
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        /// <param name="filePath"></param>
        private string exportDataTableToExcel(DataTable dataTable, string fileName, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application excel;

            Microsoft.Office.Interop.Excel._Workbook workBook;

            Microsoft.Office.Interop.Excel._Worksheet workSheet;

            object misValue = System.Reflection.Missing.Value;

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

            workBook = excel.Workbooks.Add(misValue);

            workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;

            int rowIndex = 1;

            int colIndex = 0;

            //取得標題  
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;

                excel.Cells[1, colIndex] = col.ColumnName;
            }

            //取得表格中的資料  
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;

                colIndex = 0;

                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;

                    excel.Cells[rowIndex, colIndex] =

                         row[col.ColumnName].ToString().Trim();

                    //設定表格內容置中對齊  
                    //workSheet.get_Range(excel.Cells[rowIndex, colIndex],

                    //  excel.Cells[rowIndex, colIndex]).HorizontalAlignment =

                    //  Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                }
            }

            excel.Visible = true;

            string saveFile = filePath + fileName + ".xls";

            if (File.Exists(saveFile))
            {
                File.Delete(saveFile);//嘿嘿，這樣不好，偷偷把原來的刪掉了，暫時這樣寫，項目中不可以
            }

            workBook.SaveAs(saveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,

                misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,

                misValue, misValue, misValue, misValue, misValue);

            dataTable = null;

            workBook.Close(true, misValue, misValue);

            excel.Quit();

            PublicMethod.Kill(excel);//調用kill當前excel進程  

            releaseObject(workSheet);

            releaseObject(workBook);

            releaseObject(excel);

            if (!File.Exists(saveFile))
            {
                return null;
            }
            return saveFile;
        }
        /// <summary>
        /// 釋放COM組件對象
        /// </summary>
        /// <param name="obj"></param>
        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }  
        /// <summary>
        /// 關閉進程的內部類
        /// </summary>
        public class PublicMethod
        {
            [DllImport("User32.dll", CharSet = CharSet.Auto)]

            public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

            public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
            {
                //如果外層沒有try catch方法這個地方需要拋異常。
                IntPtr t = new IntPtr(excel.Hwnd);

                int k = 0;

                GetWindowThreadProcessId(t, out k);

                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

                p.Kill();
            }
        }  　

C#將DataTable資料匯出到EXCEL的兩種方法