DataTable或者DataSet匯出Excel

因为工作上 有需要要求数据汇出Excel，所以参考了网上一些内容自己整理的代码如下，主要是实现DataTable/DataSet汇出Excel，同时保证不会影响客户原来自己的Excel操作。主要使用垃圾回收已经Office的Dll文件。

Code
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Windows.Forms;
  6using System.Reflection;
  7using System.Threading;
  8using Microsoft.Office.Interop.Excel;
  9
 10namespace Gmrbrian.Components
 11{
 12    匯出文件類#region 匯出文件類
 13
 14    public static class DataExport
 15    {
 16        private static Microsoft.Office.Interop.Excel.ApplicationClass ObjExcel = null;
 17        private static Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = null;
 18        private static Workbooks ObjWorkBooks = null;
 19        private static Sheets ObjSheets = null;
 20        private static object ObjMissing = System.Reflection.Missing.Value;
 21
 22        /**//// <summary>
 23        /// 單表匯出Excel
 24        /// </summary>
 25        /// <param name="AColumnTitle">需要自定義table的ColumnTitle</param>
 26        /// <param name="ATable">輸出表</param>
 27        /// <param name="ASaveFileName">保存文件名包含路徑</param>
 28        public static void ExportToExcel(List<string> AColumnTitle, System.Data.DataTable ATable, string ASaveFileName)
 29        {
 30            CreateExcelObject();
 31            if (AColumnTitle == null)
 32            {
 33                ExportToExcel(ATable, (Worksheet)ObjWorkBook.ActiveSheet);
 34            }
 35            else
 36            {
 37                FillExcelColumnTitle(AColumnTitle, (Worksheet)ObjWorkBook.ActiveSheet);
 38                FillExcelData(ATable, (Worksheet)ObjWorkBook.ActiveSheet);
 39            }
 40            SaveExcelFile(ASaveFileName);
 41        }
 42
 43        /**//// <summary>
 44        /// 整個Dataset匯出Excel
 45        /// </summary>
 46        /// <param name="ADataSet"></param>
 47        /// <param name="ASaveFileName"></param>
 48        public static void ExportToExcel(System.Data.DataSet ADataSet, string ASaveFileName)
 49        {
 50            CreateExcelObject();
 51
 52            if (ADataSet == null || ADataSet.Tables.Count <= 0)
 53                return;
 54            CreateExcelSheets(ADataSet.Tables.Count);
 55            for (int i = 0; i < ADataSet.Tables.Count; i++)
 56            {
 57                ExportToExcel(ADataSet.Tables[i], ((Worksheet)ObjSheets.get_Item(i + 1)));
 58            }
 59
 60            SaveExcelFile(ASaveFileName);
 61        }
 62
 63        /**//// <summary>
 64        /// 默認直接填充表內容
 65        /// </summary>
 66        /// <param name="ATable"></param>
 67        /// <param name="AWorkSheet"></param>
 68        private static void ExportToExcel(System.Data.DataTable ATable, Worksheet AWorkSheet)
 69        {
 70            AWorkSheet.Name = ATable.TableName;
 71            for (int i = 0; i < ATable.Columns.Count; i++)
 72            {
 73                AWorkSheet.Cells[1, i + 1] = ATable.Columns[i].Caption;
 74            }
 75            for (int i = 0; i < ATable.Rows.Count; i++)
 76            {
 77                for (int j = 0; j < ATable.Columns.Count; j++)
 78                {
 79                    AWorkSheet.Cells[2 + i, j + 1] = ATable.Rows[i][j].ToString();
 80                }
 81            }
 82        }
 83
 84        private static void FillExcelData(System.Data.DataTable ATable, Worksheet AWorkSheet)
 85        {
 86            if (ATable.TableName.Length > 0)
 87                AWorkSheet.Name = ATable.TableName;
 88            for (int i = 0; i < ATable.Rows.Count; i++)
 89            {
 90                for (int j = 0; j < ATable.Columns.Count; j++)
 91                {
 92                    AWorkSheet.Cells[2 + i, j + 1] = ATable.Rows[i][j].ToString();
 93                }
 94            }
 95        }
 96
 97        private static void FillExcelColumnTitle(List<string> AListStr,Worksheet AWorkSheet)
 98        {
 99            for (int i = 0; i < AListStr.Count; i++)
100            {
101                AWorkSheet.Cells[1, i + 1] = AListStr[i];
102            }
103        }
104
105        public static int CreateExcelSheets(int ACount)
106        {
107            CreateExcelObject();
108            if (ACount >= 3)
109            {
110                for (int i = 3; i < ACount; i++)
111                {
112                    ObjSheets.Add(ObjMissing, ObjSheets.get_Item(i), ObjMissing, ObjMissing);
113                }
114            }
115            return ObjSheets.Count;
116        }
117
118        private static void CreateExcelObject()
119        {
120            if (ObjSheets == null)
121            {
122                ObjExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
123                ObjWorkBooks = ObjExcel.Workbooks;
124                ObjWorkBook = ObjWorkBooks.Add(ObjMissing);
125                ObjSheets = ObjWorkBook.Worksheets;
126            }
127        }
128    
129        private static void SaveExcelFile(string AFileName)
130        {
131            SaveFileDialog sfd = new SaveFileDialog();
132            sfd.Filter = "*.xls|*.*";
133            sfd.FileName = AFileName;
134            if (sfd.ShowDialog() == DialogResult.OK)
135            {
136                ObjWorkBook.Close(true, AFileName, ObjMissing);
137            }
138            ObjExcel.Quit();
139            ClearExcelObject();
140            
141        }
142
143        private static void ClearExcelObject()
144        {
145            System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjSheets);//xlsheet为表
146            System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjWorkBook);//xlwb为工作簿
147            System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjWorkBooks);//xlwb为工作簿
148            System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);//xlapp为excel应用程序
149            ObjExcel = null;
150            ObjWorkBook = null;
151            ObjWorkBooks = null;
152            ObjSheets = null;
153            GC.Collect();    //垃圾回收會自動刪除該進程
154        }
155
156        public static void Test()
157        {
158            CreateExcelObject();
159            CreateExcelSheets(10);
160            SaveExcelFile("D:\\Test.xls");
161            //KillExcelProcess();
162        }
163
164        以后可能會用到Function#region 以后可能會用到Function
165        /**//// <summary>
166        /// 太暴力會把用戶錯殺所有的Excel進程
167        /// </summary>
168        private static void KillExcelProcess()
169        {
170            foreach (System.Diagnostics.Process xlProcess in System.Diagnostics.Process.GetProcesses())
171            {
172                if (xlProcess.ProcessName.ToUpper().Equals("EXCEL"))
173                {
174                    //结束 excel 进程 
175                    xlProcess.Kill();
176                }
177            }
178
179        }
180
181        /**//// <summary>
182        /// 只是使用反射原理調用Excel沒有實用價值
183        /// </summary>
184        private static void InvokeExcelObject()
185        {
186            if (ObjSheets == null)
187            {
188                try
189                {
190                    Type tExcel = Type.GetTypeFromProgID("Excel.Application");
191                    ObjExcel = (ApplicationClass)Activator.CreateInstance(tExcel);
192                    ObjWorkBooks = (Workbooks)ObjExcel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, ObjExcel, null);
193                    ObjWorkBook = (Workbook)ObjWorkBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, ObjWorkBooks, null);
194                    ObjSheets = (Sheets)ObjWorkBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, ObjWorkBook, null);
195                    tExcel.InvokeMember("Visible", BindingFlags.SetProperty, null, ObjExcel, new object[] { true });
196                }
197                catch (Exception E)
198                {
199
200                }
201                finally
202                {
203                    Thread.Sleep(500);
204                }
205            }
206        }
207        #endregion 
208
209    }
210    #endregion
211}
212