private static void ReadFromExcel()
{
DataTable dt = new DataTable("table");
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 query = @"SELECT * FROM Sample";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
}
foreach (DataRow row in dt.Rows)
{
foreach (var item in row.ItemArray)
{
Console.WriteLine(item);
}
}
}
protected void AddBug(string BugData)
{
System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
System.Data.OleDb.OleDbConnection objCon;
System.Data.OleDb.OleDbCommand objComm;
string strInsert = "";
objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
objBuilder.Add("Jet OLEDB:Database Password", "");
objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);
strInsert = " Insert Into ReportedBugs ";
strInsert += " (Tester, AppName, Build, DateReported, Description) ";
strInsert += " Values( " + BugData + " ) ";
objComm = new System.Data.OleDb.OleDbCommand();
objComm.Connection = objCon;
objComm.CommandType = System.Data.CommandType.Text;
objComm.CommandText = strInsert;
try
{
objCon.Open();
objComm.ExecuteNonQuery();
objCon.Close();
Response.Redirect("Default.aspx");
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
private void btPotvrdi_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(tbPutanja.Text) || (op.CheckPathExists == false || op.CheckFileExists == false))
{
MessageBox.Show("Proverite da li ste uneli sve podatke kako treba!", "Greška!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string newConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbPutanja.Text;
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
builder.Add("Data source", tbPutanja.Text);
Program.ChangeConnectionString(builder.DataSource, builder.Provider);
Properties.Settings.Default.Save();
try
{
OleDbConnection dbConn = new OleDbConnection(builder.ConnectionString);
dbConn.Open();
dbConn.Close();
}
catch
{
MessageBox.Show("Lokacija baze ili naziv fajla nije ispravan! Moraćete ponovo da je definišete.", "Informacija", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
LoadingScreen s = new LoadingScreen();
Form1 f = new Form1(s);
this.Hide();
f.Show();
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", Properties.Settings.Default.provider);
builder.Add("Data Source", Properties.Settings.Default.path);
string connString = builder.ConnectionString;
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
conn.Close();
LoadingScreen splashy = new LoadingScreen();
splashy.Show();
Application.Run(new Form1(splashy));
/*Form1 f = new Form1();
f.Show();*/
}
catch
{
Application.Run(new ConnectionDefinition());
/*ConnectionDefinition c = new ConnectionDefinition();
c.Show();*/
}
//Application.Run(/*new ConnectionDefinition()*/);
}
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;
}
public NoviPacijent()
{
InitializeComponent();
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", Properties.Settings.Default.provider);
builder.Add("Data Source", Properties.Settings.Default.path);
connString = builder.ConnectionString;
}
private static void InputDataFromExl(string directoryPath, string excelFileName)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = directoryPath + ReportsDirectory + excelFileName;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
Console.WriteLine(dt.Rows[0].ItemArray[0]);
int rowsCount = dt.Rows.Count - 1;
for (int i = 2; i < rowsCount; i++)
{
foreach (var item in dt.Rows[i].ItemArray)
{
Console.WriteLine(item);
}
}
}
static void Main(string[] args)
{
var fileName = string.Format("{0}\\..\\..\\..\\file.xlsx", Directory.GetCurrentDirectory());
DataSet sheet1 = 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 (OleDbConnection connection = new OleDbConnection(conString.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
}
connection.Close();
}
var table = sheet1.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.Write("{0, 15} ", row[column]);
}
Console.WriteLine();
}
}
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);
}
}
}
private static void Main()
{
Console.WriteLine("Name:");
string name = Console.ReadLine();
Console.WriteLine("Salary for {0}:", name);
string salary = Console.ReadLine();
var connectionBuilder = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.ACE.OLEDB.12.0;"
};
connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
connectionBuilder.DataSource = File;
var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);
var cmdAppendRow = new OleDbCommand("INSERT INTO [Sheet1$](FullName, Salary) VALUES('" + name + "','" + salary + "')", connectToExcel);
try
{
connectToExcel.Open();
using (connectToExcel)
{
int rowsAffected = cmdAppendRow.ExecuteNonQuery();
Console.WriteLine("{0} rows affected", rowsAffected);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private static void Main()
{
var connectionBuilder = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.ACE.OLEDB.12.0;"
};
connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
connectionBuilder.DataSource = File;
var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);
var cmdToExcel = new OleDbCommand("SELECT * FROM [Sheet1$]", connectToExcel);
connectToExcel.Open();
using (connectToExcel)
{
var reader = cmdToExcel.ExecuteReader();
while (reader != null && reader.Read())
{
var name = (string)reader["FullName"];
var salary = (double)reader["Salary"];
Console.WriteLine("{0}: {1}", name, salary);
}
}
}
public static IEnumerable<string[]> GetReportsData(string zipPath, string extractPath)
{
if (Directory.Exists(extractPath))
{
Directory.Delete(extractPath, true);
}
using (ZipFile archive = ZipFile.Read(zipPath))
{
archive.ExtractAll(extractPath, ExtractExistingFileAction.OverwriteSilently);
}
IEnumerable<string> excelFiles = DirSearch(extractPath);
var connectionString = new OleDbConnectionStringBuilder();
connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
connectionString.Add("Extended Properties", "Excel 12.0");
var sales = new List<String[]>();
foreach (var file in excelFiles)
{
connectionString.DataSource = file;
// Get the date from the name of the file.
string pattern = "-Sales-Report-";
string salesDate = file.Substring(file.LastIndexOf(pattern) + pattern.Length);
string supermarket = Path.GetFileNameWithoutExtension(file.Substring(0, file.LastIndexOf(pattern)));
salesDate = salesDate.Substring(0, salesDate.IndexOf(".xls"));
sales.AddRange(ReadOneExcelFile(connectionString, supermarket, salesDate));
}
Directory.Delete(extractPath, true);
return sales;
}
private void ConvertDBFToCSV(string dbfPath)
{
if (File.Exists(dbfPath))
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder()
{
DataSource = Path.GetDirectoryName(dbfPath),
Provider = "Microsoft.Jet.OLEDB.4.0"
};
builder.Add("Extended Properties", "dBase III");
using (OleDbConnection connection = new OleDbConnection() { ConnectionString = "Provider=vfpoledb;Data Source=" + dbfPath + ";Collating Sequence=machine;" })
{
using (OleDbCommand cmd = new OleDbCommand() { Connection = connection })
{
cmd.CommandText = "SELECT MR_BKNO,CONS_ACC,COL_DATE,COL_DATE,AMT_PAID,MR_RPNO FROM [" + Path.GetFileName(dbfPath) + "] where AMT_PAID<>0.00 and CONS_ACC<>" + (char)34 + " " + (char)34;
connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
string csvData = string.Empty;
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
csvData +=
"NULL" + "}" + //SBM ID
"NULL" + "}" + //Collector Name
//"NULL" + "}" + //Collector ID
row[0].ToString().Trim() + "}" +
row[1].ToString().Trim() + "}" +
row[2].ToString().Trim().Replace(" 00.00.00", "") + "}" +
row[3].ToString().Trim().Replace(" 00.00.00", "") + "}" +
row[4].ToString().Trim() + "}" +
"NULL" + "}" + //Receipet No.
"NULL" + "}" + //Cheque No.
"01/01/2001" + "}" + //Cheque Date.
"NULL" + "}" + //BankNameCode
"NULL" + "}" + //M<anual Book No
row[5].ToString().Trim() +
"}Cash}0"
+ Environment.NewLine;
}
}
connection.Close();
string csvFilePath = Server.MapPath("Uploads") + "//CSV//" + Path.GetFileNameWithoutExtension(dbfPath) + ".csv";
File.WriteAllText(csvFilePath, csvData);
UploadCSV(csvFilePath);
}
}
}
}
/// <summary>
/// Excel导入Access
/// </summary>
public void Excel2Access(string[] filenames)
{
try
{
for (int j = 0; j < filenames.Length; j++)
{
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = filenames[j];
connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");
using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
DataSet ds = new DataSet();
string sql = string.Format("select * from [{0}$]", sheetName);
OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
cn.Open();
using (OleDbDataReader dr = cmdLiming.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
DataTable dt = ds.Tables[sheetName];
if (dt.Rows.Count > 0)
{
for (int i = 4; i < dt.Rows.Count; i++)
{
//写入数据库数据
if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
{
string MySql = "insert into TB_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) values('" + dt.Rows[i][1].ToString() + "','" +
dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" +
dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
CarBLL carBLL = new CarBLL();
DataTable carList = carBLL.FindAll_infos(getblacklistsql);
dgvCarList.DataSource = carList;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Excel导入Access
/// </summary>
public void Excel2Access(string[] filenames)
{
try
{
for (int j = 0; j < filenames.Length; j++)
{
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = filenames[j];
connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");
using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
DataSet ds = new DataSet();
string sql = string.Format("select * from [{0}$]", sheetName);
OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
cn.Open();
using (OleDbDataReader dr = cmdLiming.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
DataTable dt = ds.Tables[sheetName];
if (dt.Rows.Count > 0)
{
for (int i = 4; i < dt.Rows.Count; i++)
{
//写入数据库数据
if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
{
string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList ";
DriverListBLL driverListBll = new DriverListBLL();
DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);
dgvBlackList.DataSource = dtDriverList;
dgvBlackList.Rows[0].Selected = false;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//----------------------------------
public ExcelFile(string _filePath)
{
//FilePath = _filePath;
csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = _filePath;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml; HDR=YES");
}
static void Main(string[] args)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = @"..\..\Excel.xlsx";
csbuilder.Add("Extended Properties", "Excel 12.0");
ReadDataFromExcel(csbuilder);
InsertDataToExcel(csbuilder, new Data(16, "New Name", 999));
}
public ExcelReader(string path, bool hasHeaders, bool hasMixedData)
{
this.path = path;
OleDbConnectionStringBuilder strBuilder = new OleDbConnectionStringBuilder();
strBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
strBuilder.DataSource = path;
strBuilder.Add("Extended Properties", "Excel 8.0;"+
"HDR=" + (hasHeaders ? "Yes" : "No") + ';' +
"Imex=" + (hasMixedData ? "2" : "0") + ';' +
"");
strConnection = strBuilder.ToString();
}
static void Main(string[] args)
{
var fileName = string.Format(@"{0}\..\..\score.xlsx", Directory.GetCurrentDirectory());
OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder();
conString.Provider = "Microsoft.ACE.OLEDB.12.0";
conString.DataSource = fileName;
conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
string name = "Homer Simpson", score = "-3";
AppendRow(conString, name, score);
PrintSpreadsheet(conString);
}
private static void AddDataFromExcelToSQLDB(string tempDir)
{
DataSet sheet1 = new DataSet();
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=No");
DirectoryInfo dirs = new DirectoryInfo(tempDir);
foreach (var dir in dirs.GetDirectories())
{
string dateString = dir.ToString();
DateTime date = DateTime.ParseExact(dateString, "dd-MMM-yyyy", CultureInfo.InvariantCulture);
foreach (var file in dir.GetFiles())
{
string[] data = file.ToString().Split(new string[] { "-Sales" }, StringSplitOptions.RemoveEmptyEntries);
csbuilder.DataSource = (tempDir + "//" + dateString + "//" + file.ToString());
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow item in dt.Rows)
{
double num;
if (double.TryParse(item.ItemArray[0].ToString(), out num))
{
string sqlQuery = "INSERT INTO Sales";
Dictionary<string, object> parametters = new Dictionary<string, object>();
parametters.Add("ProductId", item.ItemArray[0]);
parametters.Add("Qantity", item.ItemArray[1]);
parametters.Add("UnitPrice", item.ItemArray[2]);
parametters.Add("Sum", Math.Round(Convert.ToDouble(item.ItemArray[3]), 2));
parametters.Add("MarketId", getMarketId(data[0]));
parametters.Add("Data", date.Date);
SqlProvider.ExecuteSqlQueryInsert(sqlQuery, parametters, null);
}
}
}
connection.Close();
}
}
}
}
public Form1(LoadingScreen splashy)
{
_splashy = splashy;
InitializeComponent();
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", Properties.Settings.Default.provider);
builder.Add("Data Source", Properties.Settings.Default.path);
connString = builder.ConnectionString;
try
{
dbConn = new OleDbConnection(connString);
dbConn.Open();
loadDataGrid(query);
dbConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Informacija", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
gridPacijenti.Columns[0].ReadOnly = true;
gridPacijenti.Columns[0].DefaultCellStyle.ForeColor = Color.Gray;
}
static void Main(string[] args)
{
var fileName = string.Format("{0}\\..\\..\\..\\file.xlsx", Directory.GetCurrentDirectory());
DataSet sheet1 = 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");
string queryText = @"INSERT INTO [Sheet1$] (Name, Score) VALUES (@Name, @Score)";
string name = "Homer", score = "3";
using (OleDbConnection connection = new OleDbConnection(conString.ConnectionString))
{
connection.Open();
using (OleDbCommand oleDbCmd = connection.CreateCommand())
{
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.CommandText = queryText;
oleDbCmd.Parameters.AddWithValue("@Name", name);
oleDbCmd.Parameters.AddWithValue("@Score", score);
oleDbCmd.ExecuteNonQuery();
}
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
}
connection.Close();
}
var table = sheet1.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.Write("{0, 15} ", row[column]);
}
Console.WriteLine();
}
}
private static void InsertDataFromXls(string filePath, string dateString)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = filePath;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
string[] dateParts = dateString.Split('-');
int day = int.Parse(dateParts[0]);
int month = GetMonthAsInt(dateParts[1]);
int year = int.Parse(dateParts[2]);
DateTime reportDate = new DateTime(year, month, day);
int rowsCount = dt.Rows.Count - 1;
for (int i = 2; i < rowsCount; i++)
{
Report report = new Report
{
ProductId = Convert.ToInt32(dt.Rows[i].ItemArray[0]),
Quantity = Convert.ToInt32(dt.Rows[i].ItemArray[1]),
UnitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]),
Sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]),
Date = reportDate
};
string locationName = dt.Rows[0].ItemArray[0].ToString();
InsertInSqlServer(report, locationName);
}
}
/// <summary>
/// 初始化ExcelData
/// </summary>
/// <param name="strExcelFilePath">文件完整路径</param>
/// <param name="strSheetName">工作簿名称</param>
private void ExcelToDataTable(string strExcelFilePath, string strSheetName = "Sheet1")
{
OleDbConnectionStringBuilder con = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.ACE.OLEDB.12.0",
DataSource = strExcelFilePath
};
con.Add("Extended Properties", "Excel 12.0;HDR=YES");
string strCon = con.ConnectionString;
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strCon))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strCon);
adapter.Fill(ds, strSheetName);
conn.Close();
}
_ExcelTable = ds.Tables[strSheetName];
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet sheet1 = new DataSet();
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = Server.MapPath(@"demo.xlsx");
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
grdResult.DataSource = sheet1;
grdResult.DataBind();
}
connection.Close();
}
}
private static void AddRow(string name, int score)
{
DataTable dt = new DataTable("table");
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))
{
string insertToTable = @"INSERT INTO [Sheet1$] (Name, Score) VALUES (@name, @score)";
using (OleDbCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandType = CommandType.Text;
command.CommandText = insertToTable;
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@score", score);
command.ExecuteNonQuery();
}
}
}
/* 07. Implement appending new rows to the Excel file.*/
/// <summary>
/// Mains this instance.
/// </summary>
public static void Main()
{
// Setup connection. Try OleDbConnectionStringBuilder
OleDbConnectionStringBuilder excelConnectionString = new OleDbConnectionStringBuilder();
excelConnectionString.Provider = "Microsoft.Jet.OLEDB.4.0"; ;
excelConnectionString.DataSource = "..\\..\\test.xls";
excelConnectionString.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=2");
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString.ConnectionString);
excelConnection.Open();
// Insert new row
using (excelConnection)
{
OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$](Name, Score) VALUES(@Name, @Score)", excelConnection);
command.Parameters.AddWithValue("@Name", "Pesho Zlia Ide v Excel");
command.Parameters.AddWithValue("@Score", "12");
command.ExecuteNonQuery();
Console.WriteLine("Done! Check the file!");
}
}
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = Server.MapPath(@"demo.xlsx");
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
string queryText = @"INSERT INTO [Sheet1$] (Name, Score) VALUES (@Name, @Score)";
using (OleDbConnection oConn = new OleDbConnection(csbuilder.ConnectionString))
{
using (OleDbCommand oRS = oConn.CreateCommand())
{
oConn.Open();
oRS.CommandType = CommandType.Text;
oRS.CommandText = queryText;
oRS.Parameters.AddWithValue("@Name", "saykor");
oRS.Parameters.AddWithValue("@Score", "55");
oRS.ExecuteNonQuery();
}
}
}
/*
* Obtaining the name of the excel sheets and placing them on a list.
* This method was obtained from: http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order
*/
public List<string> ListSheetInExcel(string filePath)
{
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
String strExtendedProperties = String.Empty;
sbConnection.DataSource = filePath;
List<string> listSheet = new List<string>();
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);
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;
}
public DataTable Run(string query)
{
var csb = new OleDbConnectionStringBuilder();
csb.Provider = @"Microsoft.Jet.OLEDB.4.0";
csb.DataSource = this.Directory;
csb.Add("Extended Properties", "dBASE IV");
DataTable result = new DataTable();
using (var db = new OleDbConnection(csb.ConnectionString))
using (var cmd = db.CreateCommand())
{
cmd.Connection = db;
cmd.CommandText = query;
db.Open();
if (query.StartsWith("select", StringComparison.InvariantCultureIgnoreCase))
{
using (var reader = cmd.ExecuteReader())
{
result.Load(reader);
}
}
else
{
var recordsAffected = cmd.ExecuteNonQuery();
result.Columns.Add("RecordsAffected");
result.LoadDataRow(new object[] { recordsAffected }, false);
}
db.Close();
}
return result;
}
private static string GetConnectionString(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);
return sbConnection.ConnectionString;
}
private DataTable ReadData()
{
string ConnectionString = "";
if (this.Context.FileType == ImportFileType.excelx || this.Context.FileType == ImportFileType.excel)
{
System.Data.OleDb.OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder();
builder.DataSource = this.Context.DataLocation + this.Context.DataFilePath;
builder.Provider = "Microsoft.ACE.OLEDB.12.0";
if (this.Context.FileType == ImportFileType.excel)
{
builder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
}
else
{
builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1");
}
ConnectionString = builder.ConnectionString;
var con = new System.Data.OleDb.OleDbConnection(ConnectionString);
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable dt = new DataTable();
con.Open();
var dtSheets = new System.Data.DataTable();
dtSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtSheets.Rows.Count == 0)
{
throw new Exception("No Sheet exist in the excel file");
}
else if (dtSheets.Rows[0].IsNull(2))
{
throw new Exception("No Data Avaiable");
}
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyCommand = new System.Data.OleDb.OleDbDataAdapter("Select * from " + "[" + dtSheets.Rows[0][2] + "]", con);
// MyCommand.TableMappings.Add("Table", "TestTable");
MyCommand.Fill(ds);
dt = ds.Tables[0];
List <DataColumn> dc = new List <DataColumn>();
foreach (DataColumn c in dt.Columns)
{
if (c.DataType == typeof(string))
{
dc.Add(c);
}
}
con.Close();
con.Dispose();
foreach (DataColumn c in dc)
{
foreach (DataRow dr in dt.Rows)
{
if (dr[c.ColumnName] != null)
{
dr[c.ColumnName] = dr[c.ColumnName].ToString().Trim();
}
}
}
return(dt);
}
else if (this.Context.FileType == ImportFileType.csv)
{
ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.Context.DataLocation + this.Context.DataFilePath + ";Extensions=asc,csv,tab,txt;";
}
return(new DataTable());
}
protected void Page_Load(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
System.Data.OleDb.OleDbConnection objCon;
System.Data.OleDb.OleDbCommand objComm;
string strSelect = "";
//Create a connection to the database
objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
objBuilder.Add("Jet OLEDB:Database Password", "");
objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);
//Build the SQL Select command
//Note the Spaces are important!
strSelect = " Select ";
strSelect += " Tester, AppName, Build, DateReported, Description ";
strSelect += " From ReportedBugs";
Trace.Write(strSelect);
//Build a Command object to send your command
objComm = new System.Data.OleDb.OleDbCommand();
objComm.Connection = objCon;
objComm.CommandType = System.Data.CommandType.Text;
objComm.CommandText = strSelect;
//Response.Write(objComm.CommandText.ToString)
//Create a DataReader variable to referance the results
//we get back from the database.
System.Data.OleDb.OleDbDataReader objDR;
//Open the connection and run the command
try
{
objCon.Open();
objDR = objComm.ExecuteReader();
//Print out the results
Response.Write("<table border='1'>");
Response.Write("<tr>");
Response.Write("<th>Tester</th>");
Response.Write("<th>App Name</th>");
Response.Write("<th>Build</th>");
Response.Write("<th>DateReported</th>");
Response.Write("<th>Description</th>");
Response.Write("</tr>");
while (objDR.Read() == true)
{
Response.Write("<tr>");
Response.Write("<td>" + objDR["Tester"] + "</td>");
Response.Write("<td>" + objDR["AppName"] + "</td>");
Response.Write("<td>" + objDR["Build"] + "</td>");
Response.Write("<td>" + objDR["DateReported"] + "</td>");
Response.Write("<td>" + objDR["Description"] + "</td>");
Response.Write("</tr>");
}
Response.Write("</table>");
objDR.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
if (objCon.State == System.Data.ConnectionState.Open)
{
objCon.Close();
}
}
}