private void ReadExcel(string i_ExcelPath, string i_SheetName) { DataTable dataTable = new DataTable(); OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0;", DataSource = i_ExcelPath }; if (i_ExcelPath.CompareTo(".xls") == 0) { builder.Add("Extended Properties", "Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"); } else { builder.Add("Extended Properties", "Extended Properties='Excel 12.0;HDR=NO';"); } using (OleDbConnection connection = new OleDbConnection(builder.ConnectionString)) { connection.Open(); using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + i_SheetName + "]", connection)) { adapter.Fill(dataTable); } connection.Close(); } }
public static string GenerateConnectionString(ExcelFile excel) { if (excel == null) { throw new ExcelFileException($"Ссылна на объект {nameof(excel)} равна NULL "); } string ext = excel.Extension.ToLower(); OleDbConnectionStringBuilder strcon = new OleDbConnectionStringBuilder(); switch (ext) { case ".xlsx": { strcon.Add("Provider", $"{OleDbProvider}"); strcon.Add("Extended Properties", "Excel 12.0;HDR=YES;IMEX=1"); break; } case ".xls": { strcon.Add("Provider", $"{OleDbProvider}"); strcon.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=1"); break; } default: throw new Exception("Неверный формат файла."); } strcon.Add("Data Source", excel.FullPath); ConnectionStringGenerated?.Invoke(null, strcon.ToString()); return(strcon.ToString()); }
bool CargarHojasCombo() { try { OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder(); cb.DataSource = ruta; if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLS") { cb.Provider = "Microsoft.Jet.OLEDB.4.0"; cb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=0;"); } else if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLSX") { cb.Provider = "Microsoft.ACE.OLEDB.12.0"; cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=0;"); } dt.Clear(); bool flag = false; // String strconn2 = ""; // strconn2 = "Provider=Microsoft.ACE.OLEDB.8.0;Data Source=" + ruta + ";Extended Properties=Excel 8.0;"; // OleDbConnection mconn2 = new OleDbConnection(strconn2); OleDbConnection mconn2 = new OleDbConnection(cb.ConnectionString); //abre una conexion de tipo oledb mconn2.Open(); dt = mconn2.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Lo agrega a mi datatable if (dt != null) { String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. cmbHoja.Items.Clear(); foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); cmbHoja.Items.Add(excelSheets[i].Substring(0, excelSheets[i].Length - 1)); //$ i++; } cmbHoja.SelectedIndex = 0; //cierra una conexion de tipo oledb flag = true; } else { flag = false; } mconn2.Close(); return(flag); } catch (Exception ex) { Log_Error_bus.Log_Error(ex.ToString()); MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); dt = new DataTable(); return(false); } }
public static string GetConnectionString(string fileName) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); string[] newExtensions = new string[] { ".xlsx", ".xlsb", ".xlsm" }; string[] oldExtensions = new string[] { ".xls" }; string extension = Path.GetExtension(fileName); if (newExtensions.Contains(extension)) { builder.Provider = "Microsoft.ACE.OLEDB.12.0"; builder.Add("Extended Properties", "Excel 12.0 Xml; HDR=No; READONLY=true; IMEX=1"); } else if (oldExtensions.Contains(extension)) { builder.Provider = "Microsoft.Jet.OLEDB.4.0"; builder.Add("Extended Properties", "Excel 8.0; HDR=No; READONLY=true; IMEX=1"); } else { throw new ArgumentException(Resources.UnknownExcelExtension, "fileName"); } builder.DataSource = fileName; return(builder.ConnectionString); }
static void Main() { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); builder.ConnectionString = @"Data Source=c:\Sample.mdb"; // Call the Add method to explicitly add key/value // pairs to the internal collection. builder.Add("Provider", "Microsoft.Jet.Oledb.4.0"); builder.Add("Jet OLEDB:Database Password", "MyPassword!"); builder.Add("Jet OLEDB:System Database", @"C:\Workgroup.mdb"); // set up row-level locking. builder.Add("Jet OLEDB:Database Locking Mode", 1); // Dump the contents of the "filled-in" // OleDbConnectionStringBuilder // to the console window. DumpBuilderContents(builder); // Clear current values and reset known keys to their // default values. builder.Clear(); // Dump the contents of the newly emptied // OleDbConnectionStringBuilder // to the console window. DumpBuilderContents(builder); Console.WriteLine("Press Enter to continue."); Console.ReadLine(); }
protected override string GetConnectionString() { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(FConnectionString); builder.DataSource = tbDatabase.Text; if (!String.IsNullOrEmpty(tbUserName.Text)) { builder.Add(MsAccessDataConnection.strUserID, tbUserName.Text); } else { builder.Remove(MsAccessDataConnection.strUserID); } if (!String.IsNullOrEmpty(tbPassword.Text)) { builder.Add(MsAccessDataConnection.strPassword, tbPassword.Text); } else { builder.Remove(MsAccessDataConnection.strPassword); } return(builder.ToString()); }
public GpsHistory() { //todo 后期需统一将数据库操作封装到一层中 gpsHistoryDBConnectBuilder = new OleDbConnectionStringBuilder(); gpsHistoryDBConnectBuilder.Add("Provider", "MSDAORA"); gpsHistoryDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "gpsDB")); gpsHistoryDBConnectBuilder.Add("Persist Security Info", true); gpsHistoryDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "gpsDBUser")); gpsHistoryDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "gpsDBPasswd")); //gpsHistoryDBConnectBuilder.Add("Provider", "MSDAORA"); //gpsHistoryDBConnectBuilder.Add("Data Source", "10.178.1.218/gspgis"); //gpsHistoryDBConnectBuilder.Add("Persist Security Info", true); //gpsHistoryDBConnectBuilder.Add("User ID", "zcdz"); //gpsHistoryDBConnectBuilder.Add("Password", "zcdz"); gpsDeviceDBConnectBuilder = new OleDbConnectionStringBuilder(); gpsDeviceDBConnectBuilder.Add("Provider", "MSDAORA"); gpsDeviceDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDB")); gpsDeviceDBConnectBuilder.Add("Persist Security Info", true); gpsDeviceDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDBUser")); gpsDeviceDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "gpsDeviceDBPasswd")); //gpsDeviceDBConnectBuilder.Add("Provider", "MSDAORA"); //gpsDeviceDBConnectBuilder.Add("Data Source", "10.178.1.108/pgis"); //gpsDeviceDBConnectBuilder.Add("Persist Security Info", true); //gpsDeviceDBConnectBuilder.Add("User ID", "zcdz"); //gpsDeviceDBConnectBuilder.Add("Password", "zcdz"); }
//Returns the connection object public OleDbConnection OpenExcelFile(string ExcelFilePath, string SheetName, ExcelFileType FileType, bool HasHeaderRow, bool ForceMixedDataAsText, int TypeGuessRows, string CSVDelimiter) { string ConnectionStringOptions = ""; DataTable sheet1 = new DataTable(); OleDbDataAdapter adapter; DataSet ds = new DataSet(); adapter = new OleDbDataAdapter(); string ParentDirectory = ExcelFilePath.Substring(0, ExcelFilePath.LastIndexOf('\\')); string FileName = ExcelFilePath.Split('\\').Last(); if (HasHeaderRow) { ConnectionStringOptions += "HDR=YES;"; } else { ConnectionStringOptions += "HDR=NO;"; } if (ForceMixedDataAsText) { ConnectionStringOptions += "IMEX=1;"; } ConnectionStringOptions += "TypeGuessRows=" + TypeGuessRows + ";"; ConnectionStringOptions = ConnectionStringOptions.Substring(0, ConnectionStringOptions.Length - 1); //remove trailing ; character OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder(); if (FileType == ExcelFileType.xlsx) { csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0"; csbuilder.DataSource = ExcelFilePath; csbuilder.Add("Extended Properties", "Excel 12.0 Xml;" + ConnectionStringOptions); } else if (FileType == ExcelFileType.xls) { csbuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; csbuilder.DataSource = ExcelFilePath; csbuilder.Add("Extended Properties", "Excel 8.0;" + ConnectionStringOptions); } else if (FileType == ExcelFileType.csv) { csbuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; csbuilder.DataSource = ParentDirectory; csbuilder.Add("Extended Properties", "text;FMT=Delimited(" + CSVDelimiter + ");" + ConnectionStringOptions); } else { throw new System.ArgumentException("Invalid file type specified. File must end with .xls, .xlsx or .csv"); } OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString); con.Open(); return(con); }
// kai paspaudziamas mygtukas "Pradėti" private async void MygtukasPradeti_Click(object sender, EventArgs e) { toRun = true; int atsakuSkaicius = int.Parse(atsakuSarasas.SelectedItem.ToString()); mygtukasPradeti.Enabled = false; atsakuSarasas.Enabled = false; mygtukasStabdyti.Enabled = true; CloseButton.EnableDisable(this, false); Task[] ts = new Task[atsakuSkaicius]; while (toRun) { ConcurrentBag <DatabaseTable> generatedSequences = new ConcurrentBag <DatabaseTable>(); await Task.Run(() => { for (int i = 0; i < atsakuSkaicius; i++) { ts[i] = Task.Factory.StartNew(() => generatedSequences.Add(obj.GenerateSequence())); } Task.WaitAll(ts); } ); try { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); string path = Directory.GetParent(System.Reflection.Assembly.GetExecutingAssembly().Location).FullName; string fileName = Path.Combine(path, "Database.mdb"); builder.ConnectionString = @"Data Source=" + fileName; builder.Add("Provider", "Microsoft.Jet.Oledb.4.0"); builder.Add("Jet OLEDB:Database Password", "12345"); foreach (DatabaseTable element in generatedSequences) { string[] data = { element.threadId.ToString(), element.data }; ListViewItem listViewItem = new ListViewItem(data); atsakymuLentele.Items.Insert(0, listViewItem); if (atsakymuLentele.Items.Count > 20) { atsakymuLentele.Items.RemoveAt(atsakymuLentele.Items.Count - 1); } InsertData(builder.ConnectionString, element.threadId, element.generatedTime, element.data); } } catch (Exception ex) { LogWriter logger = new LogWriter(ex.ToString()); logger.LogWrite(ex.ToString()); } } }
/// <summary> /// 圈选获取监所详细信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public JSDetail GetJSDetailByPoly(string id) { OleDbConnectionStringBuilder zzjgDBConnectBuilder = new OleDbConnectionStringBuilder(); zzjgDBConnectBuilder.Add("Provider", "MSDAORA"); zzjgDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "zzjgDB")); zzjgDBConnectBuilder.Add("Persist Security Info", true); zzjgDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBUser")); zzjgDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBPasswd")); JSDetail info = new JSDetail(); try { using (OleDbConnection conn = new OleDbConnection(zzjgDBConnectBuilder.ConnectionString)) { //缺少照片数据 String sql = String.Format("select JSMC,LD,DH,DZ,BZRS from B_ZTK_SP_JSJBXX where JSBH='{0}'", id); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (!reader.IsDBNull(0)) { info.JS_MC = reader[0].ToString(); } if (!reader.IsDBNull(1)) { info.DWLD_XM = reader[1].ToString(); } if (!reader.IsDBNull(2)) { info.DWLD_LXDH = reader[2].ToString(); } if (!reader.IsDBNull(3)) { info.GAJGXZ = reader[3].ToString(); } if (!reader.IsDBNull(4)) { info.RS = reader[4].ToString(); } } } } catch (Exception ex) { throw ex; } return(info); //string json = ServiceUtil.GetRemoteXmlStream(ConfigHelper.GetValueByKey("webservice.config", "圈选监所详细信息") + id, null); //return JsonConvert.DeserializeObject<List<JSDetail>>(json)[0]; }
static void Main(string[] args) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); builder.ConnectionString = @"Data Source=C:\Sample.mdb"; // Call the Add method to explicitly add key/value // pairs to the internal collection. builder.Add("Provider", "Microsoft.Jet.Oledb.4.0"); builder.Add("Jet OLEDB:Database Password", "MyPassword!"); builder.Add("Jet OLEDB:System Database", @"C:\Workgroup.mdb"); // Set up row-level locking. builder.Add("Jet OLEDB:Database Locking Mode", 1); Console.WriteLine(builder.ConnectionString); Console.WriteLine(); // Clear current values and reset known keys to their // default values. builder.Clear(); // Pass the OleDbConnectionStringBuilder an existing // connection string, and you can retrieve and // modify any of the elements. builder.ConnectionString = "Provider=DB2OLEDB;Network Transport Library=TCPIP;" + "Network Address=192.168.0.12;Initial Catalog=DbAdventures;" + "Package Collection=SamplePackage;Default Schema=SampleSchema;"; Console.WriteLine("Network Address = " + builder["Network Address"].ToString()); Console.WriteLine(); // Modify existing items. builder["Package Collection"] = "NewPackage"; builder["Default Schema"] = "NewSchema"; // Call the Remove method to remove items from // the collection of key/value pairs. builder.Remove("User ID"); // Note that calling Remove on a nonexistent item does not // throw an exception. builder.Remove("BadItem"); Console.WriteLine(builder.ConnectionString); Console.WriteLine(); // Setting the indexer adds the value, if // necessary. builder["User ID"] = "SampleUser"; builder["Password"] = "******"; Console.WriteLine(builder.ConnectionString); Console.WriteLine("Press Enter to finish."); Console.ReadLine(); }
public TempleManager() { this.zzjgDBConnectBuilder = new OleDbConnectionStringBuilder(); zzjgDBConnectBuilder.Add("Provider", "MSDAORA"); zzjgDBConnectBuilder.Add("Data Source", ConfigHelper.GetValueByKey("webservice.config", "zzjgDB")); zzjgDBConnectBuilder.Add("Persist Security Info", true); zzjgDBConnectBuilder.Add("User ID", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBUser")); zzjgDBConnectBuilder.Add("Password", ConfigHelper.GetValueByKey("webservice.config", "zzjgDBPasswd")); }
/// <inheritdoc/> protected override string GetConnectionStringWithLoginInfo(string userName, string password) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(ConnectionString); builder.Remove("User ID"); builder.Add("User ID", userName); builder.Remove("Password"); builder.Add("Password", password); return(builder.ToString()); }
public static string GetOleDbConnectionString(string dbfDir) { var builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.Jet.OLEDB.4.0", DataSource = dbfDir }; builder.Add("Extended Properties", "dBASE IV"); builder.Add("User ID", "Admin"); builder.Add("Password", string.Empty); return(builder.ConnectionString); }
bool CargarArchivoExcelADataTable() { try { ds.Clear(); String strconn = ""; bool flag = false; OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder(); cb.DataSource = ruta; if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLS") { cb.Provider = "Microsoft.Jet.OLEDB.4.0"; cb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=0;"); } else if (Path.GetExtension(cb.DataSource).ToUpper() == ".XLSX") { cb.Provider = "Microsoft.ACE.OLEDB.12.0"; cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=0;"); } // strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ruta + ";Extended Properties=Excel 12.0;"; // OleDbConnection mconn = new OleDbConnection(strconn); OleDbConnection mconn = new OleDbConnection(cb.ConnectionString); //El nombre de la hoja del archivo de marcaciones tiene que llamarse Marcaciones OleDbDataAdapter ad = new OleDbDataAdapter("Select * from [" + plantilla + "$]", mconn); //abre una conexion de tipo oledb mconn.Open(); //Lo agrega a mi datatable ad.Fill(ds); if (ds != null) { flag = true; } else { flag = false; } //cierra una conexion de tipo oledb mconn.Close(); return(flag); } catch (Exception ex) { Log_Error_bus.Log_Error(ex.ToString()); MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); ds = new DataTable(); return(false); } }
/// <summary> /// Databases the connection string. /// </summary> /// <param name="databasePath">The database path.</param> /// <param name="usePassword">if set to <c>true</c> [use password].</param> /// <returns>System.String.</returns> internal static string DatabaseConnectionString(string databasePath, bool usePassword = false) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); builder.ConnectionString = $"Data Source={databasePath}"; builder.Add("Provider", "Microsoft.JET.Oledb.4.0"); if (usePassword) { builder.Add("Jet OLEDB:Database Password", Database.DbPassword); } builder.Add("Mode", 12); return(builder.ToString()); }
protected override OleDbConnection GetNativeConnection(string connectionString) { OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(ConnectionString); oleDBCnnStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; oleDBCnnStrBuilder.Add("Mode", "ReadWrite"); if (false == connectionString.Contains("Extended Properties")) { oleDBCnnStrBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes"); } return(new OleDbConnection(oleDBCnnStrBuilder.ToString())); }
protected override OleDbConnection GetNativeConnection(string connectionString) { OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString); oleDBCnnStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; if (connectionString.ToUpperInvariant().Contains("UNICODE")) { oleDBCnnStrBuilder.Add("Extended Properties", "text;CharacterSet=UNICODE;HDR=Yes;FMT=Delimited"); } else { oleDBCnnStrBuilder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited"); } return(new OleDbConnection(oleDBCnnStrBuilder.ToString())); }
public ChildDBConnect(int spec_id, String tableName, String punktName) { tabName = tableName; RegistryKey regKey = Registry.CurrentUser; regKey = regKey.OpenSubKey("Software\\UFSIN\\ivrJournal"); OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder(); bldr.DataSource = regKey.GetValue("dbPath", "").ToString(); // Указываем путь bldr.Provider = DataProvider; // Указываем провайдера bldr.Add("Jet OLEDB:Database Password", "ivr32a"); OleDbCon = new OleDbConnection(bldr.ConnectionString); sprSet = new DataSet(); if (tableName == "spec_psycho") { InitDataTableSpecPsyho(spec_id, tableName, punktName); } else { InitDataTableIVR(spec_id, tableName, punktName); } }
private void Form1_Load(object sender, EventArgs e) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder() { Provider = "Microsoft.Jet.OLEDB.4.0", DataSource = System.IO.Directory.GetCurrentDirectory() + "\\database.accdb", }; builder.Add("Jet OLEDB:Engine Type", 5); var catalog = new ADOX.Catalog(); var table = new ADOX.Table(); table.Name = "DataTableSample1"; table.Columns.Append("Column"); table.Columns.Append("Column1"); table.Columns.Append("Column2"); if (!System.IO.File.Exists(builder.DataSource)) { catalog.Create(builder.ConnectionString); } try { catalog.Tables.Append(table); } catch (Exception ex) { MessageBox.Show("Exists"); } var connection = catalog.ActiveConnection as ADODB.Connection; if (connection != null) { connection.Close(); } }
private static void InsertIntoExcel() { OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder(); conString.Provider = "Microsoft.ACE.OLEDB.12.0"; conString.DataSource = FullFilePath; conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); using (var dbCon = new OleDbConnection(conString.ConnectionString)) { dbCon.Open(); var command = new OleDbCommand("INSERT INTO [Sheet1$] VALUES (@vendor, @incomes, @expences, @taxes, @financialResults)"); command.Parameters.AddWithValue("@vendor", "Vendor"); command.Parameters.AddWithValue("@incomes", "Incomes"); command.Parameters.AddWithValue("@expences", "Expences"); command.Parameters.AddWithValue("@taxes", "Taxes"); command.Parameters.AddWithValue("@finanvialResults", "Financial Results"); //string command = "SELECT * FROM [Sales$]"; //using (var adapter = new OleDbDataAdapter(command, dbCon)) //{ // adapter.Fill(sheet); //} } }
static void Main(string[] args) { if (System.IO.File.Exists(FileName)) { var Builder = new OleDbConnectionStringBuilder() { DataSource = Path.GetDirectoryName(FileName), Provider = "Microsoft.Jet.OLEDB.4.0" }; Builder.Add("Extended Properties", "dBase III"); using (var cn = new System.Data.OleDb.OleDbConnection() { ConnectionString = Builder.ConnectionString }) { using (var cmd = new OleDbCommand() { Connection = cn }) { cmd.CommandText = "SELECT * FROM " + Path.GetFileName(FileName); cn.Open(); var dt = new DataTable("Data"); dt.Load(cmd.ExecuteReader()); dt.WriteXml("Data.xml"); } } } }
private List <String> GetExcelSheetNames(string filePath) { OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder(); String strExtendedProperties = String.Empty; sbConnection.DataSource = filePath; sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0"; strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1"; sbConnection.Add("Extended Properties", strExtendedProperties); List <string> listSheet = new List <string>(); using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString())) { conn.Open(); DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow drSheet in dtSheet.Rows) { if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign) { listSheet.Add(drSheet["TABLE_NAME"].ToString()); } } } return(listSheet); }
private void button3_Click(object sender, EventArgs e) { if (dataGridView1.RowCount > 0) { try { using (SaveFileDialog saveFileDialog1 = new SaveFileDialog()) { saveFileDialog1.AddExtension = true; saveFileDialog1.DefaultExt = "xls"; saveFileDialog1.Filter = "Excel文件(*.xls)|*.xls"; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = DateTime.Now.ToString("药品结余 yyyy-MM-dd"); if (saveFileDialog1.ShowDialog() == DialogResult.OK) { OleDbConnectionStringBuilder osb = new OleDbConnectionStringBuilder(); osb.Provider = "Microsoft.Jet.OLEDB.4.0"; osb.DataSource = saveFileDialog1.FileName; osb.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=2"); StringBuilder sb = new StringBuilder(); string TableName = DateTime.Now.ToString("hh_mm_ss"); sb.Append(string.Format("CREATE TABLE [{0}](", TableName)); using (DataTable DatDB = dataGridView1.DataSource as DataTable) { foreach (DataColumn dc in DatDB.Columns) { sb.Append(string.Format("[{0}]{1},", dc.ColumnName, OleDbType.VarChar)); } using (OleDbConnection cn = new OleDbConnection(osb.ConnectionString)) { using (OleDbCommand cmd = new OleDbCommand(sb.ToString().TrimEnd(',') + ")", cn)) { cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); } using (OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}]", TableName), cn)) { using (OleDbCommandBuilder cb = new OleDbCommandBuilder(oda)) { cb.DataAdapter.Update(DatDB); } } } } MessageBox.Show("保存成功"); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("没有结余数据,勿需保存!!!"); } }
static void Main(string[] args) { DataTable dt = new DataTable("newtable"); OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder(); csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0"; csbuilder.DataSource = @"..\..\Table.xlsx"; csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString)) { connection.Open(); string selectSql = @"SELECT * FROM [Sheet2$]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } connection.Close(); } foreach (DataRow row in dt.Rows) { foreach (var item in row.ItemArray) { Console.WriteLine(item); } } }
public List <string> ListSheetInExcel(string filePath) { OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder(); String strExtendedProperties = String.Empty; sbConnection.DataSource = filePath; if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file { sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0"; strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed } else if (Path.GetExtension(filePath).Equals(".xlsx")) //for 2007 Excel file { sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0"; strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1"; } sbConnection.Add("Extended Properties", strExtendedProperties); List <string> listSheet = new List <string>(); using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString())) { conn.Open(); DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow drSheet in dtSheet.Rows) { if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign) { listSheet.Add(drSheet["TABLE_NAME"].ToString()); } } } return(listSheet); }
/// <summary> /// Creates a <see cref="OleDbConnection"/> based on the specified Microsoft Excel workbook. /// </summary> /// <param name="fileName">Full path and file name of the Microsoft Excel workbook to use as the data source.</param> /// <returns>Returns a <see cref="OleDbConnection"/>.</returns> private OleDbConnection GetWorkbookConnection(string fileName) { // http://stackoverflow.com/a/9274455/2386774 if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException("fileName", "The filename must not be empty or null."); } OleDbConnectionStringBuilder connectionString = new OleDbConnectionStringBuilder(); connectionString.DataSource = fileName; string extendedProperties = String.Empty; if (Path.GetExtension(fileName).Equals(".xls")) // for 97-03 Excel file { connectionString.Provider = "Microsoft.Jet.OLEDB.4.0"; extendedProperties = "Excel 8.0"; } else if (Path.GetExtension(fileName).Equals(".xlsx")) // for 2007 Excel file { // Install 2007 Office System Driver: Data Connectivity Components // https://www.microsoft.com/en-us/download/details.aspx?id=23734 connectionString.Provider = "Microsoft.ACE.OLEDB.12.0"; extendedProperties = "Excel 12.0"; } else { throw new ArgumentException("The file must be of type .xls or .xlsx."); } connectionString.Add("Extended Properties", extendedProperties + ";HDR=Yes;IMEX=1"); // HDR=ColumnHeader,IMEX=InterMixed return(new OleDbConnection(connectionString.ToString())); }
static void Main() { string fileName = "../../scoreboard.xlsx"; DataSet sheet = new DataSet(); OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder(); conString.Provider = "Microsoft.ACE.OLEDB.12.0"; conString.DataSource = fileName; conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); using (var dbCon = new OleDbConnection(conString.ConnectionString)) { dbCon.Open(); string command = "SELECT * FROM [Sheet1$]"; using (var adapter = new OleDbDataAdapter(command, dbCon)) { adapter.Fill(sheet); } string name = "Joro the rabbit"; double score = 434.34; AddToExcel(name, score, dbCon); } DataTable table = sheet.Tables[0]; PrintTable(table); }
private static string ExcelConnectionString(string filePath) { try { OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder(); String strExtendedProperties = String.Empty; sbConnection.DataSource = filePath; if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file { sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0"; strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed } else if (Path.GetExtension(filePath).Equals(".xlsx")) //for 2007 Excel file { sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0"; strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1"; } sbConnection.Add("Extended Properties", strExtendedProperties); return(sbConnection.ToString()); } catch (Exception) { throw; } }
private void Form1_Load(object sender, EventArgs e) { DataTable dt = new DataTable(); OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = System.IO.Path.Combine(Application.StartupPath, "Database1.accdb") }; // // Our highly secure password :-) // Builder.Add("Jet OLEDB:Database Password", "password"); using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString }) { using (OleDbCommand cmd = new OleDbCommand { Connection = cn, CommandText = "SELECT TOP 6 CompanyName FROM Customers;" }) { cn.Open(); dt.Load(cmd.ExecuteReader()); ListBox1.DisplayMember = "CompanyName"; ListBox1.DataSource = dt; } } }