Generating Excel reports by using .Net

Charith Jayasundara

I am currently working with a .Net web application. I have to create several complicated excel reports using that web application. I have two doubts First Question: Can I use all the excel functions with .Net ? Do you have any good samples ? Second Question: This is not a windows application, this is a web server. Can I generate excel report by using .Net web application? Thanks!:)

jayasshc

pmarfleet

csecharithjayasshc wrote:

First Question: Can I use all the excel functions with .Net ? Do you have any good samples ?

Do you need to include functions in your Excel spreadsheet? Or do you just need to output a set of literal values to it? If you just need to output data to your spreadsheet you should look at using the ODBC/Jet driver. Google will provide you with plenty of samples. If you need to create functions for particular cells, then you will need to use the Excel automation library. Again, Google should be your starting point.

csecharithjayasshc wrote:

Second Question: This is not a windows application, this is a web server. Can I generate excel report by using .Net web application?

Yes you can, but of the two options I mentioned above I would only recommend using the ODBC/Jet solution from a web application. Excel has a single-threaded architecture, so the automation library is not suited for use within a server application. Regards Paul

Charith Jayasundara

Dear Paul, Thank you very much for the reply. I just noticed another more important fact. I'll receive all the required data through a stored procedure. So I don't want to use excel functions, I can calculate everything in C# and post the calculated value on the cell. So "Excel automation library" would be suitable for that, isn't it? Thanks Again!

pmarfleet

Yes, but you're still better off using the ODBC/Jet driver rather than Excel automation for the reasons I mentioned in my previous post. This is what I recently used when creating a data export facility for an ASP.NET web app. Paul

Charith Jayasundara

Hi, ok, is ODBC/Jet driver functionality depends on the excel version (2003 or 2007)? Thanks!

Urs Enzler

This article shows you another approach: http://www.bbv.ch/pdf_files/system_event/2005_XMLto%20Excel.pdf XSLT is used to create Excel files. The advantage is that you do not need Excel itself to create the files. The disadvantage is that you have a Excel-xml file in the end. If you need to have an xls then you still have to use Excel to transform the xml to a xls file. But this is mus easier then program all data into Excel with interop.

-^-^-^-^-^- no risk no funk ................... please vote ------>

eoe

Hi, if you are looking for a solution for a large project, you should evaluate the libraries offered by aspose http://www.aspose.com[^]. I started a project using COM/INTEROP to create an interface to the office world. Then it was necessary to support the generation of word documents on a server without having any MS office application installed. We decided to use aspose. Finally we were able to improve our applications noticeably. Erik

Charith Jayasundara

Thanks!

jayasshc

pmarfleet

No. You don't need Excel installed on the server, only the driver. Paul

Charith Jayasundara

Hi Paul, Could you please provide me a good C# example for odbc/jet driver thing? I tried on the internet but still I couldn't find a proper one. I could find several nice article for "Excel Automation Library". Thanks for your kind help! Good Luck!!!

jayasshc

pmarfleet

Try something like this:

//create random file so as not to conflict
string dataFile = "";

//make sure the temp file is not read only (copied from template) as this stops the OLE connection from updating
File.SetAttributes(dataFile, FileAttributes.Normal);

//inject data
System.Data.OleDb.OleDbConnection tempExcelConnection = new System.Data.OleDb.OleDbConnection();
tempExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
tempExcelConnection.ConnectionString += "Data Source=" + dataFile + ";";
tempExcelConnection.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes;"";";
System.Data.OleDb.OleDbCommand tempExcelCommand = new System.Data.OleDb.OleDbCommand();
tempExcelCommand.Connection = tempExcelConnection;

System.Data.SqlClient.SqlDataReader dr = null;

tempExcelCommand.Connection.Open();

// TODO: Get data for SqlDataReader

if (dr.HasRows)
{
while(dr.Read())
{
//NB. Ths query expects a named range called "mynamedrange" containing 5 columns
//the data will be inserted after the named range so should only include the column headings
tempExcelCommand.CommandText = String.Format("INSERT INTO [mynamedrange] VALUES ({0}, {1}, {2}, {3}, {4})", dr[0], dr[1], dr[2], dr[3], dr[4]);
tempExcelCommand.ExecuteNonQuery();
}
}

dr.Close();
tempExcelCommand.Connection.Close();

Regards Paul