学步园

 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        }
121        /**//// <summary>
122        /// 杀死运行中的进程
123        /// </summary>
124        /// <param name="processName">进程名</param>
125        public void KillProcess(string processName)
126        {
127            System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName(processName);
128
129            foreach (System.Diagnostics.Process procCur in procs)
130            {
131                procCur.Kill();
132                procCur.Close();
133            }
134        }
135
136
137
138
139
140        /**//// <summary> 
141        /// 写入Excel文档 
142        /// </summary> 
143        /// <param name="Path">文件名称</param> 
144        //public bool SaveFP2toExcel(string Path)
145        //{
146        //    try
147        //    {
148        //        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
149        //        OleDbConnection conn = new OleDbConnection(strConn);
150        //        conn.Open();
151        //        System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
152        //        cmd.Connection = conn;
153        //        for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
154        //        {
155        //            if (fp2.Sheets[0].Cells[i, 0].Text != "")
156        //            {
157        //                cmd.CommandText  = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0]. Cells[i, 0].Text + "','" +
158        //                fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
159        //                "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
160        //                cmd.ExecuteNonQuery();
161        //            }
162        //        }
163        //        conn.Close();
164        //        return true;
165        //    }
166        //    catch (System.Data.OleDb.OleDbException ex)
167        //    {
168        //        System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
169        //    }