Excel导入导出数据库02

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.IO;
  6 using System.Data.OleDb;
  7 using System.Data;
  8 using Microsoft.Office.Interop.Excel;
  9 using System.Reflection;
 10 using System.Runtime.InteropServices;
 11 
 12 namespace WinOrderAd
 13 {
 14     public class Excel
 15     {
 16         public string FilePath
 17         {
 18             get;
 19             set;
 20         }
 21         public Dictionary<string, string> FiledNames
 22         {
 23             get;
 24             set;
 25         }
 26         public Excel()
 27         {
 28         }
 29 
 30         public DataSet ImportExcel()//若只需要知道数据就用此方法
 31         {
 32 
 33             try
 34             {
 35                 string strConn;
 36                 if (Path.GetExtension(FilePath) == ".xlsx")
 37                     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
 38                 else
 39                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
 40                 OleDbConnection OleConn = new OleDbConnection(strConn);
 41                 OleConn.Open();
 42                 System.Data.DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
 43                 DataSet OleDsExcle = new DataSet();
 44                 for (int i = 0; i < table.Rows.Count; i++)
 45                 {
 46                     string tableName = table.Rows[i]["Table_Name"].ToString();
 47                     tableName = tableName.Replace("'", "");
 48                     if (tableName.EndsWith("$"))
 49                     {
 50                         string sql = "SELECT * FROM [" + tableName + "]";
 51                         OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
 52                         OleDaExcel.Fill(OleDsExcle, tableName);
 53                         OleConn.Close();
 54                     }
 55                 }
 56                 return OleDsExcle;
 57             }
 58             catch (Exception err)
 59             {
 60                 throw err;
 61             }
 62         }
 63 
 64 
 65         /// <summary>
 66         /// 用Excel Com组件方式读取Excel内容到DataSet(兼容性较高)
 67         /// </summary>
 68         /// <param name="path"></param>
 69         /// <returns></returns>
 70         public DataSet ToDataTableEx()
 71         {
 72             Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
 73 
 74             excel.Visible = false;
 75             excel.ScreenUpdating = false;
 76             excel.DisplayAlerts = false;
 77 
 78             excel.Workbooks.Add(FilePath);
 79             Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
 80             Range rangecell = null;
 81             DataSet ds = new DataSet();
 82             try
 83             {
 84                 //遍历Worksheets中的每张表
 85                 for (int i = 1; i <= excel.Worksheets.Count; i++)
 86                 {
 87                     //获得指定表
 88                     worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[i];
 89 
 90                     System.Data.DataTable dt = new System.Data.DataTable();
 95 
 96                     //取表明赋值到dt TableName
 97                     dt.TableName = worksheet.Name;
 98 
 99                     worksheet.Columns.EntireColumn.AutoFit();
100 
101                     int row = worksheet.UsedRange.Rows.Count;
102                     int col = worksheet.UsedRange.Columns.Count;
103 
104                     for (int c = 1; c <= col; c++)
105                     {
106                         dt.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text));
107                     }
108                     //添加一样式列
109                     dt.Columns.Add(new DataColumn("Style"));
110 
111                     for (int r = 2; r <= row; r++)
112                     {
113                         DataRow newRow = dt.NewRow();
114                         for (int c = 1; c <= col; c++)
115                         {
116                             rangecell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c];
117                             newRow[c - 1] = rangecell.Text;
118                             if (c == 3)
119                             {
120                                 //取信息的字体颜色与背景颜色
121                                 newRow[col] = rangecell.Font.Color + "|" + rangecell.Interior.Color;
122                             }
123                         }
124                         dt.Rows.Add(newRow);
125                     }
126                     ds.Tables.Add(dt);
127                 }
128             }
129             catch (Exception ex)
130             {
131                 throw (ex);
132             }
133             finally
134             {
135                 if (worksheet != null)
136                 {
137                     System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
138                     worksheet = null;
139                     System.Runtime.InteropServices.Marshal.ReleaseComObject(rangecell);
140                     rangecell = null;
141                 }
142                 excel.Workbooks.Close();
143                 excel.Quit();
144                 int generation = System.GC.GetGeneration(excel);
145                 if (excel != null)
146                 {
147                     System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
148                     excel = null;
149                 }
150                 System.GC.Collect(generation);
151             }
152             return ds;
153         }
154
341 
342     }
343 }

  1  public void ExportExcel(string[] listTableName, string[] listColName, List<List<Ad>> resource, string exporFilePath)
  2         {
  3             List<Ad> list = null;
  4             List<Ad> listresource = null;
  5             Microsoft.Office.Interop.Excel.Application app =
  6                new Microsoft.Office.Interop.Excel.ApplicationClass();
  7 
  8             app.Visible = false;
  9             app.ScreenUpdating = false;
 10             app.DisplayAlerts = false;
 11             Workbook wBook = app.Workbooks.Add(true);
 12 
 13             InsertLinkWorksheet(app, wBook);
 14 
 15             Worksheet wSheet = null;
 16             Range rangeResource = null;
 17             Range rangeContent = null;
 18             Range rangeTitle = null;
 19             //for (int k = 0; k < listList.Count; k++)
 20             for (int k = listList.Count - 1; k >= 0; k--)
 21             {

 23                 listresource = resource[k];//已排好的数据
 24                 wSheet = wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
 25                 wSheet.Name = listTableName[k];
 26 
 27                 try
 28                 {
 29                     int resourcenum = listresource.Count;
 30                     if (resourcenum > 0)
 31                     {
 32                         for (int i = 0; i < resourcenum; i++)
 33                         {
 34                             wSheet.Cells[i + 2, 1] = (i + 1);
 35                             wSheet.Cells[i + 2, 2] = listresource[i].ID;
 36                             wSheet.Cells[i + 2, 3] = listresource[i].Info;
 37                             wSheet.Cells[i + 2, 4] = listresource[i].Format;
 38                             wSheet.Cells[i + 2, 5] = listresource[i].Times;
 39                             wSheet.Cells[i + 2, 6] = listresource[i].Attach;
 40                             wSheet.Cells[i + 2, 7] = listresource[i].Frequency;
 41                             wSheet.Cells[i + 2, 8] = listresource[i].Same;
 42                             wSheet.Cells[i + 2, 9] = listresource[i].Dif;
 43                             wSheet.Cells[i + 2, 10] = listresource[i].Balanced;
 44 
 45                             if (listresource[i].RowStyle.Split('|')[0] != "")
 46                             {
 47                                 rangeResource = wSheet.get_Range(wSheet.Cells[i + 2, 1], wSheet.Cells[i + 2, 10]);
 48                                 rangeResource.Font.Color = listresource[i].RowStyle.Split('|')[0];
 49                                 rangeResource.Interior.Color = listresource[i].RowStyle.Split('|')[1];
 50                             }
 51                         }
 52                     }
 53  78                     int m = 0;
 79                     int col = listColName.Count();
 80                     for (m = 0; m < col; m++)
 81                     {
 82                         string headname = listColName[m];//单元格头部
 83                         wSheet.Cells[1, 1 + m] = headname;
 84                     }
 85                     //内容
 86                     rangeContent = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[list.Count + resourcenum + 2 + 1, col]);
 87                     rangeContent.Borders.Color = System.Drawing.Color.Black.ToArgb();
 88                     //rangeContent.Interior.Color = 10092543;    //设置区域背景色
 89                     rangeContent.VerticalAlignment = -4108;//竖向居中
 90                     rangeContent.HorizontalAlignment = -4108;//横向居中
 91                     rangeContent.RowHeight = 18;
 92                     rangeContent.EntireColumn.AutoFit();//自动调整列宽
 93                     //标题
 94                     rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, col]);
 95                     rangeTitle.Borders.Color = System.Drawing.Color.Black.ToArgb();
 96                     rangeTitle.Interior.Color = 65280;    //设置区域背景色
 97                     rangeTitle.VerticalAlignment = -4108;
 98                     rangeTitle.HorizontalAlignment = -4108;
 99                     rangeTitle.RowHeight = 18;
100                     rangeTitle.EntireColumn.AutoFit();
101                     //冻结首行
102                     //rangeTitle.Select();
103                     app.ActiveWindow.SplitColumn = 0;
104                     app.ActiveWindow.SplitRow = 1;
105                     app.ActiveWindow.FreezePanes = true;
106                     //rangeTitle.Font.Bold = true;    //设置字体粗体。
107                 }
108                 catch (Exception err)
109                 {
110                     throw err;
111                 }
112                 finally
113                 {
114 
115                 }
116             }
117             //设置禁止弹出保存和覆盖的询问提示框   
118             app.DisplayAlerts = false;
119             app.AlertBeforeOverwriting = false;
120             ((Worksheet)wBook.Worksheets["Sheet1"]).Delete();
121             try
122             {
123                 wBook.Saved = true;
124                 //保存工作簿   
125                 System.Reflection.Missing miss = System.Reflection.Missing.Value;
126                 wBook.SaveAs(exporFilePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
127             }
128             catch (Exception ex)
129             {
130                 throw ex;
131             }
132 
133             if (rangeResource != null)
134             {
135                 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeResource);
136                 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeContent);
137                 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeTitle);
138                 rangeResource = null;
139                 rangeContent = null;
140                 rangeTitle = null;
141             }
142 
143             if (wSheet != null)
144             {
145                 System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
146                 wSheet = null;
147             }
148 
149             if (wBook != null)
150             {
151                 System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
152                 wBook = null;
153             }
154             app.Workbooks.Close();
155             app.Quit();
156             int generation = System.GC.GetGeneration(app);
157             if (app != null)
158             {
159                 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
160                 app = null;
161             }
162             GC.Collect(generation);
163         }