CRUD資料通路類基類
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace myexcel{
public class DbExcel
{
/// <summary>
/// 擷取讀取連接配接字元串
/// </summary>
/// <param name="phyfilepath"></param>
/// <returns></returns>
private static string GetOptionConnstr(string phyfilepath)
{
string endstr = phyfilepath.Substring(phyfilepath.IndexOf('.') + 1);
if (endstr.ToLower() == "xlsx")
{
return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=no;\";Data Source=" + phyfilepath;
}
return "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=no;\";Data Source=" + phyfilepath;
}
/// 擷取操作連接配接字元串
private static string GetReadConnStr(string phyfilepath)
return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1\";Data Source="+phyfilepath;
return "Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source="+phyfilepath;
public static DataTable GetExcelDataTable(string phyfilepath)
OleDbConnection conn = null;
string sheetName = "Sheet1";
DataTable dataTable = null;
try
using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))
{
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in sheetNames.Rows)
{
sheetName = dr[2].ToString().Trim();
break;
}
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
DataSet ds = new DataSet();
oada.Fill(ds, "InitData");
dataTable = ds.Tables["InitData"];
}
catch (Exception ex)
throw new BaseDBException(ex.Message);
finally
conn.Close();
return dataTable;
public static DataTable GetExcelSheetTable(string phyfilepath)
string sheetName = "Sheet1$";
//return null;
public static DataTable GetExcelSheetTable(string phyfilepath,string SheetName)
string sheetName = SheetName;
public static void ExcelColDataUpdate(string phyfilepath, string sheetName,string setvalue,string where)
using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE ["+sheetName+"$] "+setvalue+" "+where;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnNames, string values)
cmd.CommandText = "insert into [" + sheetName + "$] (" + columnNames + ") values(" + values + ")";
public static void ExcelVoucherDataInsert(string filePath, string sheetName, DataTable dt)
StringBuilder sb = new StringBuilder();
if (dt == null || dt.Rows.Count == 0) return;
using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath)))
foreach (DataRow row in dt.Rows)
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into [" + sheetName + "$] values('" + row["FName"].ToString() + "','" + row["FNo"].ToString() + "','" + row["FIName"].ToString() + "')";
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
public static void ExcelVoucherDataInsert(string filePath, string sheetName, string itemClass , string number , string name)
cmd.CommandText = "insert into [" + sheetName + "$] values('" + itemClass + "','" + number + "','" + name + "')";
catch(Exception ex)
public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnName, string[] values)
foreach (string str in values)
cmd.CommandText = "insert into [" + sheetName + "$] (" + columnName + ") values(' " + str + "')";
}
}
編寫連接配接與操作excel檔案的通用函數
代碼
protected void DoOleSql(string sql, string database)
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("\\") + database + "; Extended Properties='Excel 8.0;HDR=no;IMEX=0'";
try
{//打開連接配接
conn.Open();
}
catch (Exception e)
{
Response.Write(e.ToString());
OleDbCommand olecommand = new OleDbCommand(sql, conn);
{//執行語句
olecommand.ExecuteNonQuery();
catch (Exception eee)
Response.Write(eee.ToString());
conn.Close();
finally
conn.Close();//關閉資料庫
注:1)使用 Excel 工作簿時,預設情況下,區域中的第一行是标題行(或字段名稱)。如果第一個區域不包含标題,您可以在連接配接字元串的擴充屬性中指定 HDR=NO。如果您在連接配接字元串中指定 HDR=NO,Jet OLE DB 提供程式将自動為您命名字段(F1 表示第一個字段,F2 表示第二個字段,依此類推);2)IMEX=1将所有讀入資料看作字元,其他值(0、2)請查閱相關幫助文檔;3)如果出現“找不到可安裝的isam”錯誤,一般是連接配接字元串錯誤
3、從excel檔案讀取資料
string sql = "select * from [sheet1$]";
DoOleSql(sql,"test.xls");
4、更新excel檔案中的資料
string sql = "update [sheet1$] set FieldName1='333' where FieldName2='b3'";
5、向excel檔案插入資料
string sql = "insert into [sheet1$](FieldName1,FieldName2,…) values('a',’b’,…)";
6、删除excel檔案中的資料:不提倡使用這種方法
7、對于非标準結構的excel表格,可以指定excel中sheet的範圍
1)讀取資料:string sql = "select * from [sheet1$A3:F20]";
2)更新資料:string sql = "update [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";
3)插入資料:string sql = "insert into [sheet1$A9:F15](FieldName1,FieldName2,…) values('a',’b’,…)";
4)删除資料:不提倡
注:1)代碼根據需要可以自行修改;2)如果出現“操作必須使用一個可更新的查詢”錯誤,可能sql語句中對excel檔案中的“字段”引用有錯誤,或對 excel檔案不具有“修改”權限;3)如果出現“不能擴充標明範圍”錯誤,可能是對excel檔案引用的“範圍”有錯誤。
本文轉自linzheng 51CTO部落格,原文連結:http://blog.51cto.com/linzheng/1080854