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;
}
}
}
