Export To Excel

[Guest]

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!

 

[sloan]

If you have XP Excel (guaranteed), you can export data to Excel.. in the xml
format.

This way you don't have to use or reference the excel object library.

You cannot do graphs, imbedded objects, but you can do raw data.

CHeck this blog:
http://spaces.msn.com/sholliday/ 9/22/2005
(that blog is for reading excel/xml data... its a little tricker to export
it to excel)

 

[Alvin Bruney]

Another approach is to dump the data into a datagrid and do a response.write
taking care to change content type to vnd-excel.

--

________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Professional VSTO.NET - Wrox/Wiley 2006

 

[Guest]

Hi thanx so much for your reply,

I am not using ASP.NET but in the future I will use this approach.

--
Jiro Hidaka
********
Programmer For
Medisca Pharmaceutique

Another approach is to dump the data into a datagrid and do a response.write
taking care to change content type to vnd-excel.

--

________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Professional VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------

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

 

[Ignacio Machin \( .NET/ C# MVP \)]

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 = 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();
}
}
}