Hi,
I have never written data in excel , I have always read it.
I do use the oledb provider. I'm sure that to write should be the same
thing.
In any case here s the code I use
string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string srcQuery = "Select * from [" + GetExcelSheetNames(
sourceFile)[0] + "]"; //read from the first sheet
OleDbConnection srcConn = new OleDbConnection( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);
readerExcel = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);
while( readerExcel.Read() ){....}
static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Jiro Hidaka" <me*****@newsgroups.nospam> wrote in message
news:C2**********************************@microsof t.com...
Hello,
I would like to know of a fast way to export data source data into an
Excel
sheet.
I found a way from C# Corner(Query Tool to Excel using C# and .NET) which
is
a neat little way of exporting dataset data to an excel using the Excel
COM
object.
This works fine but the problem is its pretty darn slow when exporting
large
amounts of data(>5000).
I would like a faster alternative through .NET.
One other way I can think of is to create a VBA macro within excel that
executes an T-SQL stored procedure and this macro is called by a .NET
program. I have not tried this option yet because I would like to get your
input first.
Thanx in advance!
--
Jiro Hidaka
********
Programmer For
Medisca Pharmaceutique
