Excel导入导出数据库02

excel导入时还要保存字体、其背景颜色等信息时读取方法就要改变:

  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 }

listTableName为多个sheet的名称

listColName为每页的列名

resource为sheet数据队列

exporFilePath为要保存的路径

posted @ 2013-05-28 10:09  欢醉  阅读(163)  评论(0编辑  收藏