Generating Microsoft Excel Reports in .NET

Mark Bourisaw
12y
161.6k
0
2

Article

Like most developers sooner or later you will be required to produce reports for the project that you are working on. Most of the time this will require a third party tool such as Crystal Reports but sometime the company you work for will be unwilling to fork over the money to purchase a reporting tool. So what do you do then? Microsoft Excel is installed on most computers these days since it is part of Microsoft office, and you can create detailed reports with Excel, but it will take some doing. I will attempt to show you how to create a simple report in Excel and some of the problems you may encounter along the way, this will work with Excel 97-2000. There is a complete project for this and can be download to your computer to work with.

After opening Visual Studio .NET you will need to create a project and name it. Do not name it Excel as this will be the name space given to the assembly and this will cause problems with the Excel object (been there done that). The next step will be to add a reference to the Excel COM object.

After adding the reference to the Excel object you can begin developing your report. This project has two reports that it will create, one is a template-based report and the second is a non-template based report. So what do I mean by a template based report? Basically I have found from many hours of beating my head against the wall that the best way to make Excel reports is to create an Excel template and put as much formatting in the template as possible. This will include the header, row and column formatting. As you look at the code you will see the difference in what it takes to format a spreadsheet on the fly in code. The non template based report is simply opening Excel and adding a workbook with a spreadsheet and putting all of the formatting in the spreadsheet via code. As you will see the template report is much simpler to use and code for.

In the project I have added a class called clsExcelReport and it is in this class that most of the reporting code has been developed. There are two main functions; the first is CreateExcelTemplateReport and CreateExcelReport. The CreateExcelTemplateReport is the report created from the template and the CreateExcelReport is the non-template report. Also I have created a dataset and loaded it with data for the reports but you will normally use a database connection to SQL, Oracle or some other database to pull your data for the reports.

For the template-based report you basically open the report and just drop in the data as this report has all of the formatting already in it. The steps for the report are laid out below (see code for more details):

Declare the application, workbook and spreadsheet variables.

Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;
Open the workbook

theWorkbook = oXL.Workbooks.Open(TemplatePath, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", true, false, 0, true);
Get the data from the dataset (or database) and put it on the template

DataRow[] rpt = ACTable.Select("Ticker <> ''","Ticker ASC");
foreach(DataRow dr in rpt)
{
worksheet.Cells[RowCounter,1] = dr["Ticker"].ToString();
worksheet.Cells[RowCounter,2] =
dr["PurchaseDate"].ToString();
worksheet.Cells[RowCounter,3] = dr["Shares"].ToString();
worksheet.Cells[RowCounter,4] =
dr["PurchasePrice"].ToString();
worksheet.Cells[RowCounter,5] = dr["Total"].ToString();
RowCounter++;
}
Save the spreadsheet as a report

oXL.ActiveWorkbook.SaveAs(SavePath + @"\TemplateReport", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

The Excel (non template) based report you will have to open a workbook format the spreadsheet, add the header, put the data on the report and save it. The steps for the report are laid out below (see code for more detail):

Declare the application, workbook and spreadsheet variables.

Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;
Add a blank workbook with blank spreadsheet to the Excel application.

theWorkbook = oXL.Workbooks.Add(Type.Missing);
Add the formatting necessary and add the column headers.

Range rg = worksheet.get_Range("A1","E1");
rg.Select();
rg.Font.Bold = true;
rg.Font.Name = "Arial";
rg.Font.Size = 10;
rg.WrapText = true;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 40;
rg.Borders.Weight = 3;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;

rg = worksheet.get_Range("A1",Type.Missing);
rg.Cells.ColumnWidth = 7;
rg.Value = "Ticker";

rg = worksheet.get_Range("B1",Type.Missing);
rg.Cells.ColumnWidth = 11;
rg.Value = "Purchase Date";

rg = worksheet.get_Range("C1",Type.Missing);
rg.Cells.ColumnWidth = 6.5;
rg.Value = "Shares";

rg = worksheet.get_Range("D1",Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value = "Purchase Price";

rg = worksheet.get_Range("E1",Type.Missing);
rg.Cells.ColumnWidth = 11;
rg.Value = "Total";

Format the cells for the data and put the data in the cells.

DataRow[] rpt = ACTable.Select("Ticker <> ''","Ticker ASC");
foreach(DataRow dr in rpt)
{
rg = (Excel.Range)worksheet.Rows[RowCounter, Type.Missing];
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Cells.RowHeight = 12;
rg.Font.Name = "Aerial";
rg.Font.Size = 10;
rg = (Excel.Range)worksheet.Cells[RowCounter,2];
rg.NumberFormat = "MM/DD/YYYY";
rg = (Excel.Range)worksheet.Cells[RowCounter,4];
rg.NumberFormat = "$0.00";
worksheet.Cells[RowCounter,1] = dr["Ticker"].ToString();
worksheet.Cells[RowCounter,2] =
dr["PurchaseDate"].ToString();
worksheet.Cells[RowCounter,3] = dr["Shares"].ToString();
worksheet.Cells[RowCounter,4] =
dr["PurchasePrice"].ToString();
TotalValue = Convert.ToDouble(dr["Total"].ToString());
worksheet.Cells[RowCounter,5] = TotalValue.ToString("C");
RowCounter++;
Save the spreadsheet as a report.

oXL.ActiveWorkbook.SaveAs(SavePath + @"\NonTemplateReport",
Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}