1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data.OleDb;
5using System.Data;
6using Excel;
7using System.Reflection;
8
9namespace OtherTools
10{
11    public class OfficeUse
12    {
13        public OfficeUse()
14        { }
15        /**//// <summary> 
 16        /// 读取Excel文档返回DataSet["table1"] 
 17        /// </summary> 
 18        /// <param name="Path">文件名称</param> 
 19        /// <returns>返回一个数据集</returns> 
 20        public DataSet ReadExcelToDS(string Path)
21        {
22            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
23            OleDbConnection conn = new OleDbConnection(strConn);
24            conn.Open();
25            string strExcel = "";
26            OleDbDataAdapter myCommand = null;
27            DataSet ds = null;
28            strExcel = "select * from [sheet1$]";
29            myCommand = new OleDbDataAdapter(strExcel, strConn);
30            ds = new DataSet();
31            myCommand.Fill(ds, "table1");
32            return ds;
33        }
34        /**//// <summary>
 35        /// 根据数据表创建Excel
 36        /// </summary>
 37        /// <param name="dt">要创建的数据表DataTable</param>
 38        public void CreateExcelWorkbook(System.Data.DataTable dt)
39        {
40
41
42            //RemoveFiles(strCurrentDir); // utility method to clean up old files 
 43
44            Excel.Application oXL;
45            Excel._Workbook oWB;
46            Excel._Worksheet oSheet;
47            Excel.Range oRng;
48            try
49            {
50                GC.Collect();
51                oXL = new Excel.Application();
52                oXL.Visible = true;
53                //Get a new workbook.
 54                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
55                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
56                // Create Header and sheet 
 57                for (int j = 0; j < dt.Columns.Count; j++)
58                {
59                    oSheet.Cells[1, j + 1] = dt.Columns[j].Caption.ToString();
60                }
61                int ri = 1;
62                int di = 0;
63                foreach (DataRow dr in dt.Rows)
64                {
65                    ri++;
66                    di = 0;
67                    foreach (DataColumn dc in dt.Columns)
68                    {
69                        di++;
70                        oSheet.Cells[ri, di] = dr[dc.ColumnName].ToString();
71                    }
72                }
73                // build the sheet contents
 74
75                //Format A1:Z1 as bold, vertical alignment = center. 
 76                oSheet.get_Range("A1", "Z1").Font.Bold = true;
77                oSheet.get_Range("A1", "Z1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
78                //AutoFit columns A:Z.
 79                oRng = oSheet.get_Range("A1", "Z1");
80                oRng.EntireColumn.AutoFit();
81                //oXL.Visible = false;
 82                //oXL.UserControl = false;
 83                //string strFile = "report" + System.DateTime.Now.Ticks.ToString() + ".xls";
 84                //oWB.SaveAs(strCurrentDir + strFile, Excel.XlFileFormat.xlWorkbookNormal,
 85                //null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
 86                /**///// Need all following code to clean up and extingush all references!!!
 87                //oWB.Close(null, null, null);
 88                //oXL.Workbooks.Close();
 89                //oXL.Quit();
 90                //System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
 91                //System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
 92                //System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
 93                //System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
 94                //oSheet = null;
 95                //oWB = null;
 96                //oXL = null;
 97                //GC.Collect(); // force final cleanup!
 98            }
99
100            catch (Exception theException)
101            {
102
103                String errorMessage;
104
105                errorMessage = "Error: ";
106
107                errorMessage = String.Concat(errorMessage, theException.Message);
108
109                errorMessage = String.Concat(errorMessage, " Line: ");
110
111                errorMessage = String.Concat(errorMessage, theException.Source);
112                System.Windows.Forms.MessageBox.Show("导出未能完成：" + errorMessage);
113                KillProcess("Excel");
114            }
115            finally
116            {
117
118            }
119
120        }