.Net操作excel

  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             try
108             {
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             try
133             {
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             else
215             {
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                 else
250                 {
251                     insertSql.Append("‘‘");
252                 }
253                 if (j != columnCount - 1)
254                     insertSql.Append(",");
255             }
256             insertSql.Append(")");
257             return insertSql.ToString();
258         }

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