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;