1 // Copyright (C) 2013 fanyong All rights reserved.    
  2 // Created Date:    2013-04-10  3 // Modified Date    2013-04-11  4 // File:             ExcelHelper.cs  5 // Author:           fanyong@gmail.com  6 // Version:         V1.0  7 // Description:   Excel helper  8   9 using System; 10 using System.Collections.Generic; 11 using System.Text; 12 using System.Data; 13 using System.Data.OleDb; 14 using System.IO; 15 using Microsoft.Office.Interop.Excel; 16  17 namespace BookFilter 18 { 19     /// <summary> 20     /// Excel Helper v0.1 21     /// </summary> 22     public class ExcelHelper 23     { 24         public ExcelHelper() 25         { 
 26             // 27         } 28  29         public DataSet Excel2DataSet(string path) 30         { 31             DataSet ds = new DataSet(); 32             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";"; 33             try 34             { 35                 using (OleDbConnection conn = new OleDbConnection(strConn)) 36                 { 37                     conn.Open(); 38                     //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等   39                     DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); 40  41                     //包含excel中表名的字符串数组 42                     string[] strTableNames = new string[dtSheetName.Rows.Count]; 43                     for (int k = 0; k < dtSheetName.Rows.Count; k++) 44                     { 45                         strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); 46                     } 47  48                     OleDbDataAdapter myCommand = null; 49                     DataTable dt = new DataTable(); 50  51                     //从指定的表明查询数据,可先把所有表明列出来供用户选择 52                     string strExcel = "select * from [" + strTableNames[0] + "]"; 53                     myCommand = new OleDbDataAdapter(strExcel, strConn); 54                     myCommand.Fill(ds, "table1"); 55                     conn.Close(); 56                 } 57             } 58             catch (Exception ex) 59             { 60                 throw ex; 61                 //RedirectErrorPage("导出excel数据到dataset出错，请联系管理员" + ex); 62             } 63             return ds; 64         } 65  66         /// <summary>   67         /// 通过工作表名 获取数据  
 68         /// </summary>   69         /// <param name="name"></param>   70         /// <returns></returns>   71         public System.Data.DataTable GetContentBySheetName(OleDbConnection conn, string name) 72         { 73             System.Data.DataTable dt = new System.Data.DataTable(); 74             OleDbDataAdapter myCommand = null; 75             string strExcel = "select * from [" + name + "]"; 76  77             using (myCommand = new OleDbDataAdapter(strExcel, conn)) 78             { 79                 myCommand.Fill(dt); 80                 return dt; 81             } 82  83         } 84  85         /// <summary> 86         /// 加载excel文件到dataset中 87         /// </summary> 88         /// <param name="path">Excel文件路径</param> 89         /// <returns>DataSet</returns> 90         public DataSet Excel2DataSetWithSheet(string path) 91         { 92             DataSet dataSet = new DataSet(); 93             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";"; 94             try 95             { 96                 System.Data.DataTable dt = new System.Data.DataTable(); 97  98                 using (OleDbConnection conn = new OleDbConnection(strConn)) 99                 {100                     if (conn.State == ConnectionState.Closed)101                     {102                         conn.Open();103                     }104                     // 可以过滤隐藏sheet105                     List<string> tableNames = GetSheetNameFromExcel(conn);106 107                     string[] strArr = new string[tableNames.Count];108 109                     tableNames.CopyTo(strArr);110 111                     List<string> sheets = new List<string>();112                     sheets.AddRange(strArr);113 114 115                     // 以sheetName新建文件夹116                     for (int i = 0; i < sheets.Count; i++)117                     {118                         //处理sheetName的#号 和 去掉末尾的$ like this:www#16k#net#cn$119                         string curSheetName = sheets[i];120                         curSheetName = curSheetName.Replace('#', '.');121                         curSheetName = curSheetName.TrimEnd('$');122 123                         //拼接创建的目录124                         int last = path.LastIndexOf('\\');125                         string dir = path.Substring(0, last);126                         string dirPath = dir + "\\" + curSheetName;127 128                         if (!Directory.Exists(dirPath))129                         {130                             Directory.CreateDirectory(dirPath);131                         }                        
132                     }                    
133 134 135                     if (null != tableNames && tableNames.Count > 0)136                     {137                         foreach (string strTable in tableNames)138                         {139                             //获取sheet页的内容140                             dt = GetContentBySheetName(conn, strTable);141                             dt.TableName = strTable;142                             dataSet.Tables.Add(dt);143                         }144                     }145                 }146             }147             catch (Exception ex)148             {149                 // Excel转化成DataSet异常150                 throw ex;151             }152             return dataSet;153         }154 155         /// <summary>  156         /// 获取Excel 中的工作表  
157         /// </summary>  158         /// <returns></returns>  159         public List<string> GetSheetNameFromExcel(OleDbConnection conn)160         {161             System.Data.DataTable dtSheetName = null;162             try163             {164                 dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });165                 List<string> strTableNames = new List<string>();166                 for (int i = 0; i < dtSheetName.Rows.Count; i++)167                 {168                     string s = dtSheetName.Rows[i]["TABLE_NAME"].ToString();169 170                     //过滤一下没用的表，Excel 默认生成的隐藏文件  171                     if (!s.Contains("_FilterDatabase") && s.LastIndexOf('_') + 1 != s.Length)172                     {173                         strTableNames.Add(s);174                     }175                 }176                 return strTableNames;177             }178             catch (Exception ex)179             {180                 throw ex;181             }182         }183 184         /// <summary>185         /// 将List转化成DataSet186         /// </summary>187         /// <typeparam name="T">泛型</typeparam>188         /// <param name="list">list</param>189         /// <returns>dataset</returns>190         private DataSet ListToDataSet<T>(List<T> list)191         {192             //list is nothing or has nothing, return nothing (or add exception handling)193             if (list == null || list.Count == 0) { return null; }194 195             //get the type of the first obj in the list196             Type obj = list[0].GetType();197 198             //now grab all properties199             System.Reflection.PropertyInfo[] properties = obj.GetProperties();200 201             //make sure the obj has properties, return nothing (or add exception handling)202             if (properties.Length == 0) { return null; }203 204             //it does so create the dataset and table205             DataSet dataSet = new DataSet();206             DataTable dataTable = new DataTable();207 208             //now build the columns from the properties209             System.Data.DataColumn[] columns = new DataColumn[properties.Length];210             for (int i = 0; i < properties.Length; i++)211             {212                 columns[i] = new DataColumn(properties[i].Name, properties[i].PropertyType);213             }214 215             //add columns to table216             dataTable.Columns.AddRange(columns);217 218             //now add the list values to the table219             foreach (var item in list)220             {221                 //create a new row from table222                 var dataRow = dataTable.NewRow();223 224                 //now we have to iterate thru each property of the item and retrieve it's value for the corresponding row's cell225                 var itemProperties = item.GetType().GetProperties();226 227                 for (int i = 0; i < itemProperties.Length; i++)228                 {229                     dataRow[i] = itemProperties[i].GetValue(item, null);230                 }231 232                 //now add the populated row to the table233                 dataTable.Rows.Add(dataRow);234             }235 236             //add table to dataset237             dataSet.Tables.Add(dataTable);238 239             //return dataset240             return dataSet;241         }242 243         /// <summary>244         /// 下载execl文件，适用于web项目245         /// </summary>246         private void DownloadExcel()247         {248             //Response.Clear();249             //Response.ClearHeaders();250             //Response.Buffer = true;251             //Response.AddHeader("Accept-Language", "zh-cn");252             //// UrlEncode防止文件名出现乱码253             //string fileName = HttpUtility.UrlEncode(this.txtPayMonth.Text + this.ddlDataSourceExport.SelectedItem + "result.csv");254             //Response.AddHeader("content-disposition", "attachment; filename=" + fileName);255             //Response.ContentType = "application/octet-stream";256             ////ExportToExcel(ds);  // 把DataSet导出成Excel.csv格式              257             //authorPay.ExportToExcel(ds);258             //Response.Flush();259         }260 261         /// <summary>262         /// 把DataSet导出到Excel中并且可以分sheet263         /// </summary>264         /// <param name="dataSet">要导出的数据来源</param>265         /// <param name="fileName">导出的Excel名称</param>266         public void DataSetToLocalExcel(DataSet dataSet, string fileName)267         {268             string outputPath = string.Empty;269 270             bool deleteOldFile = true;271             if (deleteOldFile)272             {273                 if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }274             }            
275 276             // Create the Excel Application object277             Application excelApp = new Application();            
278 279             // Create a new Excel Workbook280             Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);281 282             int sheetIndex = 0;283 284             // 遍历每张 DataTable285             foreach (System.Data.DataTable dt in dataSet.Tables)286             {287                 // Create a new Sheet288                 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(289                     excelWorkbook.Sheets.get_Item(++sheetIndex),290                     Type.Missing, 1, XlSheetType.xlWorksheet);291                 excelSheet.Name = dt.TableName;292 293                 //初始化Sheet中的变量294                 int rowIndex = 1;295                 int colIndex = 1;296                 297                 //列出标题298                 foreach (DataColumn col in dt.Columns)299                 {300                     //LogHelper.Info(" 调用Excel组件，col.ColumnName：" + col.ColumnName );301                     excelApp.Cells[1, colIndex] = col.ColumnName;302                     excelSheet.get_Range(excelApp.Cells[1, colIndex], excelApp.Cells[1, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 303                     colIndex++;304                 }305 306                 // Mark the first row as BOLD307                 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;                
308 309                 //列出行310                 for (int i = 0; i < dt.Rows.Count; i++)311                 {312                     DataRow row = dt.Rows[i];313 314                     //新起一行，当前单元格移至行首315                     rowIndex++;316                     colIndex = 1;317 318                     foreach (DataColumn col in dt.Columns)319                     {320                         if (col.DataType == System.Type.GetType("System.String"))321                         {322                             excelApp.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();323                         }324                         else325                         {326                             excelApp.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();327                         }328                         colIndex++;329                     }330 331                     // 设置边框332                     // 计算最后一列的字母标识333                     string finalColLetter = string.Empty;334                     string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";335                     int colCharsetLen = colCharset.Length;336 337                     if (dt.Columns.Count > colCharsetLen)338                     {339                         finalColLetter = colCharset.Substring(340                             (dt.Columns.Count - 1) / colCharsetLen - 1, 1);341                     }342 343                     finalColLetter += colCharset.Substring(344                             (dt.Columns.Count - 1) % colCharsetLen, 1);345 346                     // 列出范围：标识成 A1:F9 这样的347                     string excelRange = string.Format("A1:{0}{1}",348                         finalColLetter, (dt.Rows.Count + 1));349 350                     //使用最佳宽度,设置样式351                     Range allDataWithTitleRange = excelSheet.get_Range(excelRange, Type.Missing);352                     allDataWithTitleRange.Select();353                     allDataWithTitleRange.Columns.AutoFit();354                     allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框355                 }356             }357             358             excelApp.Application.DisplayAlerts = false;359             // 保存Excel并且关闭excelWorkbook对象360             excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,361                 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,362                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);363             364             excelWorkbook.Close(true, Type.Missing, Type.Missing);365             excelWorkbook = null;366 367             // Release the Application object368             excelApp.Quit();369             excelApp = null;370 371             // 回收未引用对象372             GC.Collect();373             GC.WaitForPendingFinalizers();374         }375 376     }377 }