One of my friend asked me how he can parse excel sheet, add/read data to excel sheet. The first thing that came to my mind is interop, but then he told me that he would take averages of some columns and group them. Himm, this thing reminds me sql commands :) Of course suddenly my mind changes from interop farmland to ado.net farmland. i start writing the code; however i got some stupid errors, like :
- if you dont close connection object to your excel file, your writes are in the buffer, and not written to the excel file
- when you create a new worksheet, DONT use $ at the end of the worksheet name
- when you read from a worksheet USE $ at the end of the worksheet name, even if u created without $
Anyways here is a very brief code i used in this small app
private string CreateASheet()
{
if (FileName == String.Empty)
return String.Empty;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + FileName + @";Extended Properties=
""Excel 8.0;HDR=YES;""";
string SheetName = DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() +
DateTime.Now.Hour.ToString() +
DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string CreateCommand = "Create Table " +SheetName+
" (Ticker varchar(255),Average_Price varchar(50), Average_Bid varchar(50));";
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
conn.Open();
OleDbCommand cmd = new OleDbCommand(CreateCommand, conn);
cmd.ExecuteNonQuery();
}
finally
{
conn.Dispose();
}
return SheetName;
}
if (FileName == String.Empty)
return;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source="+FileName+@";Extended Properties=
""Excel 8.0;HDR=YES;""";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand(@"select ticker,avg(prc) as average_price,
avg(bid)as Average_Bid from [051923748$] group by ticker order by ticker", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
...
...