C#将数据导出为Excel文件

1，此类用于将DataTable中的数据导出到Excel文件中，并由指定的Excel摩板规定固定的输出格式，用来得到规定的样式文件。
2，导出文件可以是Excel文件或者XML文件。
3，作为摩板的Excel文件要求有宏“MainMacro”，来处理DataTable中的数据。

 1using System;
 2using System.Data;
 3
 4namespace ExportFile
 5{
 6
 7    public class DataExport
 8    {
 9    
10        public static bool ToExcel(DataTable datatable,string[] header,string templatefilename,string filename,string filepath) {
11            return GenerateFile(datatable,header,templatefilename,filename,filepath,"Excel",true);
12        }
13
14        public static bool ToExcel(DataTable datatable,string templatefilename,string filename,string filepath) {
15            return GenerateFile(datatable,null,templatefilename,filename,filepath,"Excel",false);
16        }
17
18        public static bool ToXML(DataTable datatable,string[] header,string templatefilename,string filename,string filepath) {
19            return GenerateFile(datatable,header,templatefilename,filename,filepath,"XML",true);
20        }
21
22        private static bool GenerateFile(DataTable datatable,string[] Header,string templatefilename,string filename,
23                                        string filepath,string filetype,bool hasheader){
24            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
25            oExcel.Visible = false;
26            oExcel.DisplayAlerts = false;
27            Excel.Workbooks oBooks = oExcel.Workbooks;
28            Excel._Workbook oBook = null;
29            object oMissing = System.Reflection.Missing.Value;
30            
31            try{
32                oBook = oBooks.Open(templatefilename,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,
33                    oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
34
35                string[,] Content = ConvertDataToArray(datatable);
36
37                oExcel.Run("MainMacro",Content, Header,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,
38                    oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,
39                    oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); 
40
41                string strSaveFile = filepath + filename;
42
43                if(filetype == "Excel"){
44                    oBook.SaveAs(strSaveFile,oMissing,oMissing,oMissing,oMissing,oMissing,Excel.XlSaveAsAccessMode.xlShared,oMissing,oMissing,oMissing,
45                        oMissing,oMissing);
46                }
47                else if(filetype == "XML"){
48                    oBook.SaveAs(strSaveFile,Excel.XlFileFormat.xlXMLSpreadsheet,oMissing,oMissing,oMissing,oMissing,Excel.XlSaveAsAccessMode.xlNoChange,
49                        Excel.XlSaveConflictResolution.xlOtherSessionChanges,oMissing,oMissing,oMissing,oMissing);
50                }
51            }
52            catch(Exception){
53                return false;
54            }
55            finally{
56                if(oBook != null){
57                    oBook.Close(false,oMissing,oMissing);
58                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
59                    oBook = null;
60                }
61                if(oBooks != null){
62                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
63                    oBooks = null;
64                }
65                if(oExcel != null){
66                    oExcel.Quit();
67                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
68                    oExcel = null;
69                }
70                GC.Collect();
71            }
72            return true;
73        }
74
75        private static string[,] ConvertDataToArray(DataTable datatable){
76
77            int rowCount = datatable.Rows.Count;
78            int columnCount = datatable.Columns.Count;
79            string[,] arrData = new string[rowCount,columnCount];
80
81            for(int i = 0 ;i< rowCount ;i++) {
82                for(int j = 0;j < columnCount ; j++ ) {
83                    arrData[i,j] = Convert.ToString(datatable.Rows[i][j]);
84                }
85            } 
86            return arrData;
87        }
88
89    }
90}