Excel Reporting

 

We will be discussing the following features and will see some examples on how these features can be implemented in .Net.

 

• Modifying the structure of the report
• Carrying out analysis or calculations on the data available
• Formatting the data.

Advantages of Excel reporting

 

Microsoft Excel is very friendly with almost all segments of the corporate, even the top management/decision makers are very comfortable with excel, they can play around excel.  Apart from these we have the following advantages,

 

• Ease of use
• Ease of modification
• Ease of formatting
• Ease of Analysis
• Easy to manage

 

Excel reporting .NET

 

The solution, which I am providing now, is not a pure .NET solution, it is a combination of SQL stored procedure, Excel Macros and Java Script. .Net is just used to tie all of these together, generate the report and present it to the End user.

 

Before getting into the solution, first we should understand how we can write into an excel document.

 

Writing into an Excel document using SQL

 

3.2.1.1 Regions in Excel

 

A data region is an area on a report containing data from a data source. Types of data regions are charts, lists, tables, and matrices.  (Refer http://msdn2.microsoft.com/en-us/library/ms155976.aspx)

 

We can name a set of columns in Excel, which will be acting as a table inside the Excel sheet.  We can Inserts and updates to these Named Regions.

 

3.2.1.2Creating a Named region

 

·        Open a excel sheet

·        Enter the table header

 

 

·        Select the Column header as shown above.

·        Select Insert è Name èDefine

·    A window opens as shown above

·        Name it as “employee” and click “Add”

·        Click OK

·        Save the document and close it. (Name the document as Emp.xls and store it in c:\

 

 

Writing data into the named region using the stored procedure 

From SQL we can export the data to any database or data stores using DTS packages and Export wizard. Apart from this we can also export data from SQL to any database or datasource using a Query “Insert into OpenRowSet”.

 

We should all realize that Excel can also store data like access or any other database and Data can be retrieved or written into the excel document.

 

We are going to use this statement and export the data to excel docoument.

 

Create the following SP in Northwind database,

 

---

Create procedure dbo.ExportEmployeeData(@filePath as varchar(500))

as

begin

            DECLARE @Expr1 VARCHAR(200),@Expr2 VARCHAR(200),@Expr3 VARCHAR(200) 

            Declare @Sql1 AS VARCHAR(800), @Sql as varchar(800)

            SET @Expr1 = ”’Microsoft.Jet.OLEDB.4.0”’

            SET @Expr2 =  ”’Excel 8.0;Database=’ + @filePath + ‘;HDR=YES”’ 

            SET @Expr3 = ”’SELECT * FROM [Employee]”’

           

            set @Sql1 = @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3

           

            set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees

 

            Exec(@Sql)

end

---

 

In this SQL we are taking the excel name with path as the parameter and we are using Microsoft jet OLEDB drives to write the data into the SQL. Where [Employee] in the statement

 

SET @Expr3 = ”’SELECT * FROM [Employee]”’

 

Is the region we created in the Excel Sheet.

 

The statement “set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees” actually fetched the records from employees table and inserts it into the Excel sheet.

 

Now, try executing this stored procedure using the following statement,

 

ExportEmployeeData ‘c:\employee.xls’

 Notes:

1. There should not any data contained in the cells where we will be trying to write the data.

 

For example, In our case the region is from B4 to E4. and if we are going to insert 10 rows then there should not any data in the cells from B5 to E14.

 

If any data exists it will throw an error.

2. The excel file should be in the SQL Server system or else the folder should be shared with write permission and file share path should be given.
3. Also note that, SP is not the only way to write into an excel file, the same logic can be implemented in our java or .net code.

 

Is it not simple to write the data into an excel sheet. Of course it is.

 

Now, that we know how the data can be written into an excel sheet, the next step is formatting the data (Presentation). This can be achieved using Excel formulas and Macros.