Reader Level:
Articles

Generating Excel Reports using ASP.NET and C#

By Sushmita Kumari on December 29, 2005
This article and code snippet shows how to generate Excel reports from ASP.NET using Excel objects in .NET Framework.
  • 0
  • 0
  • 91328

When we talk about reporting, Crystal Reports and Excel are very common reporting formats to generate and view reports. Many developers may think, Excel reporting means writing macros and do some Office/Excel VBA programming. Those days are long gone.

.NET Framework class library provides Office Interoperability class that can be used as other .NET classes to generate Excel reports. All Excel and Office Interoperability related functionality is defined in Excel and Microsoft.Office.Core namespaces.

The following code shows how to use Microsoft Excel objects from Microsoft Visual Studio using C# .NET.

using Excel ;
using
Microsoft.Office.Core;
#region
Variable Decleration
private
Excel.Application oExcelApp;
private
Excel.Workbooks oBooks;
private
Excel._Workbook oBook;
private
Excel._Worksheet oSheet;
private
Excel.Range oexcelRange ;
private
Excel.Characters ocharacter ;
private object
oMissing;
oMissing = system.Reflection.Missing.Value;
//To specify the optional values for the method available inside the Excel Namespace.
#endregion

The following code shows how to use Excel and other objects. This code creates a new Excel.Application, gets WorkBooks, and open it. After that, it creates Worksheets.

oExcelApp=new Excel.Application();
oExcelApp.Visible =
false
;
oBooks = m_oExcelApp.Workbooks;
oMissing = System.Reflection.Missing.Value;
oExcelApp.Visible =
false
;
oBook=oBooks.Open("File Name",oMissing,oMissing,oMissing,oMissing,oMissing,oMissing, oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
oExcelApp.Visible =
false
;
oExcelApp.UserControl =
false
;
oSheet = (Excel._Worksheet)oBook.Worksheets["sheet Name"];
oSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
oSheet.Unprotect(oMissing);

The following code shows how to use Excel.Range object:

oexcelRange =((Excel.Range)m_oSheet.Cells[4,1]);

The following code shows how to use Excel.Characters. In this way we can do the manipulation of the partial text of a particular cell.

ocharacter = oexcelRange.get_Characters ("1", "5");
//1 and 5 is the start and end index of the selected text of the cell which is the value of the excelRangeobject.
ocharacter.Font.ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
ocharacter.Font.Bold=
true;

Sushmita Kumari

Sushmita kumari holds  under graduate in Computer Science degree and MBA in Healthcare Management.She is Brain Bench and Microsoft certified professional. Presently she is working  as a software engineer. ... Read more

Personal Blog: http://www.addiox.com
COMMENT USING

Trending up