OLEBはEXCELにCURD操作を行います。
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; } /// <summary> /// /// </summary> /// <param name="phyfilepath"></param> /// <returns></returns> private static string GetReadConnStr(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=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) { OleDbConnection conn = null; string sheetName = "Sheet1$"; DataTable dataTable = null; try { using (conn = new OleDbConnection(GetReadConnStr(phyfilepath))) { conn.Open(); 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); //return null; } finally { conn.Close(); } return dataTable; } public static DataTable GetExcelSheetTable(string phyfilepath,string SheetName) { OleDbConnection conn = null; string sheetName = SheetName; DataTable dataTable = null; try { using (conn = new OleDbConnection(GetReadConnStr(phyfilepath))) { conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 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 void ExcelColDataUpdate(string phyfilepath, string sheetName,string setvalue,string where) { OleDbConnection conn = null; try { using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath))) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE ["+sheetName+"$] "+setvalue+" "+where; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new BaseDBException(ex.Message); } finally { conn.Close(); } } public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnNames, string values) { OleDbConnection conn = null; try { using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath))) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into [" + sheetName + "$] (" + columnNames + ") values(" + values + ")"; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new BaseDBException(ex.Message); } finally { conn.Close(); } } public static void ExcelVoucherDataInsert(string filePath, string sheetName, DataTable dt) { StringBuilder sb = new StringBuilder(); if (dt == null || dt.Rows.Count == 0) return; try { using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath))) { conn.Open(); 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(); } } } catch (Exception ex) { throw new BaseDBException(ex.Message); } } public static void ExcelVoucherDataInsert(string filePath, string sheetName, string itemClass , string number , string name) { try { using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath))) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into [" + sheetName + "$] values('" + itemClass + "','" + number + "','" + name + "')"; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } catch(Exception ex) { throw new BaseDBException(ex.Message); } } public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnName, string[] values) { OleDbConnection conn = null; try { using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath))) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; foreach (string str in values) { cmd.CommandText = "insert into [" + sheetName + "$] (" + columnName + ") values(' " + str + "')"; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } } catch (Exception ex) { throw new BaseDBException(ex.Message); } finally { conn.Close(); } } } }
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); try {// olecommand.ExecuteNonQuery(); } catch (Exception eee) { Response.Write(eee.ToString()); conn.Close(); } finally { conn.Close();// } 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'"; DoOleSql(sql,"test.xls"); 5、 excel string sql = "insert into [sheet1$](FieldName1,FieldName2,…) values('a',’b’,…)"; DoOleSql(sql,"test.xls"); 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 “ ” 。