Generating Excel Reports using ASP.NET and C#


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;


Similar Articles