Generating Excel reports by using .Net
-
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
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
-
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
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!
-
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!
-
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
Hi, ok, is ODBC/Jet driver functionality depends on the excel version (2003 or 2007)? Thanks!
-
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
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 ------>
-
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
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
-
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 ------>
Thanks!
jayasshc
-
Hi, ok, is ODBC/Jet driver functionality depends on the excel version (2003 or 2007)? Thanks!
-
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
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
-
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
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