1 using System;  2 using System.Data;  3 using System.Data.OleDb;  4 using System.IO;  5 using System.Text;  6   7 namespace Test  8 {  9     public class DataAccess_Excel 10     { 11         private string GetConnectionString(string path) 12         { 13             string extension = Path.GetExtension(path).ToUpper(); 14             if (".XLS".Equals(extension)) 15                 return GetXLSConnStr(path); 16             else if (".XLSX".Equals(extension)) 17                 return GetXLSXConnStr(path); 18             else 19                 return null; 20         } 21  22         /// <summary> 23         /// "HDR=Yes:"用于指示将Excel表格中的第一行作为标题，此时在查询语句中可以将标题作为数据表的字段名使用" 24         /// "HDR= No:"则表示将Excel表格中的所有行都作为数据内容而不包含标题" 25         /// </summary> 26         /// <param name="path"></param> 27         /// <returns></returns> 28         private string GetXLSConnStr(string path) 29         { 30             StringBuilder sb = new StringBuilder(); 31             sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="); 32             sb.Append(path); 33             sb.Append(";Extended Properties=‘Excel 8.0;HDR=YES;‘"); 34             return sb.ToString(); 35         } 36  37         private string GetXLSXConnStr(string path) 38         { 39             StringBuilder sb = new StringBuilder(); 40             sb.Append("Provider=Microsoft.ACE.OLEDB.12.0;"); 41             sb.Append("Data Source=" + path + ";"); 42             sb.Append("Properties=‘Excel 12.0;HDR=YES‘"); 43             return sb.ToString(); 44         } 45  46         /// <summary> 47         /// 用来获取数据连接 48         /// </summary> 49         /// <param name="path">excel文件的路径</param> 50         /// <returns>返回一个OleDbConnection对象</returns> 51         private OleDbConnection GetConnection(string path) 52         { 53             string conString = GetConnectionString(path); 54             OleDbConnection oleConnection = new OleDbConnection(conString); 55             try 56             { 57                 oleConnection.Open(); 58             } 59             catch (Exception ex) 60             { 61                 throw ex; 62             } 63             return oleConnection; 64         } 65  66         /// <summary> 67         /// 从EXCEL中获取第一个SHEET的名称 68         /// 注意Excel wooksheet的名字必须以"$"结尾并且包含在方括号中。列名如有需要也应当包含在方括号中（如列名中包含有空格其它特殊字符等）。 69         /// </summary> 70         /// <param name="path"></param> 71         /// <returns></returns> 72         private string GetSheetName(string path) 73         { 74             try 75             { 76                 string tableName = string.Empty; 77                 DataTable dt = null; 78                 using (OleDbConnection oleConnection = GetConnection(path)) 79                 { 80                     dt = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 81                 } 82                 if (dt.Rows.Count > 0) 83                 { 84                     tableName = dt.Rows[0][2].ToString().Trim(); 85                     if (!tableName.Contains("$")) 86                     { 87                         tableName += "$"; 88                     } 89                     tableName = "[" + tableName + "]"; 90                 } 91                 return tableName; 92             } 93             catch (Exception ex) 94             { 95                 throw ex; 96             } 97         } 98  99         /// <summary>100         /// 修改数据101         /// </summary>102         /// <param name="path">excel文件路径</param>103         /// <returns>sql语句</returns>104         public int ExecuteNonQuery(string path, string sql)105         {106             int i = -1;107             try108             {109                 using (OleDbConnection oleConnection = GetConnection(path))110                 {111                     OleDbCommand oleCommand = oleConnection.CreateCommand();112                     oleCommand.CommandText = sql;113                     oleCommand.CommandType = CommandType.Text;114                     i = oleCommand.ExecuteNonQuery();115                 }116             }117             catch (Exception ex)118             {119                 throw ex;120             }121             return i;122         }123 124         /// <summary>125         /// 查询数据126         /// </summary>127         /// <param name="path">excel文件路径</param>128         /// <returns>sql语句</returns>129         public DataTable ExecuteQuery(string path, string sql)130         {131             DataTable dt = null;132             try133             {134                 dt = new DataTable();135                 dt.TableName = GetSheetName(path);136                 using (OleDbConnection oleConnection = GetConnection(path))137                 {138                     OleDbCommand oleCommand = oleConnection.CreateCommand();139                     oleCommand.CommandText = sql;140                     oleCommand.CommandType = CommandType.Text;141                     OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);142                     oleAdapter.Fill(dt);143                 }144             }145             catch (Exception ex)146             {147                 throw ex;148             }149             return dt;150         }151 152         /// <summary>153         /// 获取excel文件中的数据，默认从sheet1中获取数据，如果sheet1中没有数据，则从nicelabel中获取数据154         /// </summary>155         /// <param name="path">excel文件路径</param>156         /// <returns>返回一个DataTable</returns>157         public DataTable GetDataFromExcelFile(string path)158         {159             string sheetName = GetSheetName(path);160             string sql = string.Format(@"Select * From {0}", sheetName);161             return ExecuteQuery(path, sql);162         }163 164         /// <summary>165         /// 创建表并插入数据166         /// </summary>167         /// <param name="dgv">DataGridView对象</param>168         /// <param name="path">excel 文件路径</param>169         /// <param name="row">表示哪一行</param>170         public void CreateTableAndInsertValue(DataTable dt, string path, int? row = null)171         {172             CreateTable(dt, path);173             InsertData(dt, path, row);174         }175 176         /// <summary>177         /// 创建表单178         /// </summary>179         /// <param name="dgv"></param>180         /// <param name="path"></param>181         public void CreateTable(DataTable dt, string path)182         {183             StringBuilder createSql = new StringBuilder();184             createSql.Append(string.Format("Create Table {0}",dt.TableName));185             createSql.Append(" ( ");186             for (int i = 0; i < dt.Columns.Count; i++)187             {188                 createSql.Append("[");189                 createSql.Append(dt.Columns[i].ColumnName);190                 createSql.Append("]");191                 createSql.Append(" memo");192                 if (i != dt.Columns.Count - 1)193                     createSql.Append(",");194             }195             createSql.Append(" )");196             ExecuteNonQuery(path, createSql.ToString());197         }198 199         /// <summary>200         /// 插入表数据201         /// </summary>202         /// <param name="dgv">表单</param>203         /// <param name="path">文件路径</param>204         /// <param name="row">如果ROW为NULL,则插入所有数据，否则只插入特定行的数据</param>205         public void InsertData(DataTable dt, string path, int? row = null)206         {207             int rowFrom = 0;208             int rowEnd = 0;209             if (row == null)210             {211                 rowFrom = 0;212                 rowEnd = dt.Rows.Count;213             }214             else215             {216                 rowFrom = row.Value;217                 rowEnd = rowFrom + 1;218             }219             for (int rowIndex = rowFrom; rowIndex < rowEnd; rowIndex++)220             {221                 string insertSql = GetInsertSql(dt.Rows[rowIndex]);222                 ExecuteNonQuery(path, insertSql);223             }224         }225 226         /// <summary>227         /// 插入数据228         /// </summary>229         public int InsertData(DataRow dr, string path)230         {231             string insertSql = GetInsertSql(dr);232             return ExecuteNonQuery(path, insertSql);233         }234 235         private string GetInsertSql(DataRow dr) 236         {237             StringBuilder insertSql = new StringBuilder();238             insertSql.Append(string.Format("Insert Into {0} Values ",dr.Table.TableName));239             insertSql.Append(" ( ");240             int columnCount = dr.Table.Columns.Count;241             for (int j = 0; j < columnCount; j++)242             {243                 if (dr.Field<string>(j) != null)244                 {245                     insertSql.Append("‘");246                     insertSql.Append(dr.Field<string>(j).Replace("‘", "‘‘"));247                     insertSql.Append("‘");248                 }249                 else250                 {251                     insertSql.Append("‘‘");252                 }253                 if (j != columnCount - 1)254                     insertSql.Append(",");255             }256             insertSql.Append(")");257             return insertSql.ToString();258         }

            //使用OLEDB无法直接删除excel中的数据，需要先删除原数据文件，然后重新CreateTable259     }260 }