Excel 2007 – Page 3 – Justin Cooney

.Net, ADO.NET, ASP.NET, Excel, Excel 2003, Excel 2007, Excel 2010, MS Office, Programming, VB.NET, WebForms

ASP.NET – Generating an Excel Report on the Fly Using the Interop.Excel Namespace

2012-09-022013-11-19 Justin Cooney

As a developer, a commonly requested feature is the ability to export data from the Web into MS Excel.

Choosing the best way to export your data from an ASP.NET Web page to Excel for your users is never an easy task. There are several ways to generate your reports, with each way having its pros and cons. For starters you’ll have to take into consideration if you are developing a Web based application that needs to enforce strong security, or a more permissive Web-based application that will allow you to write temporary files to your server’s hard drive.

In an earlier article I covered one of the more common ways: how to take an existing report that has already been generated as an HTML table and stream it as Excel to the client browser. This is a great technique, but leaves it up to Excel to interpret the HTML that you are sending its way. Furthermore, Microsoft seems to be deprecating the markup that was previously so amazingly convenient when formatting HTML streamed to Excel. The article also goes into depth about what markup commands are still useful and what markup has been deprecated. It’s definitely worth reading this article if you are considering streaming HTML as an Excel document using the command: Response.ContentType = “application/ms-excel”

Before I go into further details on using the Interop.Excel namespace to generate Excel files, I’d like to point out that I am providing a fully functional example at the end of this article. If you are primarily interested in the code, then you can skip to the bottom of this article and you can copy and paste the code sample into a Visual Studio project where it should run without any problems.

Continue reading “ASP.NET – Generating an Excel Report on the Fly Using the Interop.Excel Namespace” →

Excel, Excel 2007, MS Office, Outlook, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Tips, Transact-SQL, tSQL

SQL OpenRowSet Excel Query Returns Null Values

2012-02-20 Justin Cooney

In an earlier article I reviewed how to query an Excel spreadsheet from SQL Server Management Studio (SSMS). I’ve found that this functionality is very useful, especially when working with data maintained by business users outside of a database system.

For an overview of how to connect SSMS with an Excel WorkSheet, please see my detailed article covering the subject. I continue to use the method of saving my SpreadSheet as a .xls format file which I then connect to using the OpenRowSet command with the Microsoft Jet OLEDB 4.0 driver. Once the Excel worksheet is query-able by SSMS it’s quite simple to work with this data just like you would work with a regular table.

However, one important caveat is that if your worksheet contains numeric as well as alphabetic data, this will confuse the driver and depending on the order of the data in your Spreadsheet, null results will be returned instead of the numeric data.

This behavior can be surprising when one first notices it, but happily it can be easily corrected so that you can properly query mixed data type columns.

Continue reading “SQL OpenRowSet Excel Query Returns Null Values” →

ASP.NET, C#, Excel, Excel 2003, Excel 2007, Excel 2010, HTML, Programming, Software, VB.NET, Web Development

Generating Excel documents through HTML, XML, and CSS – Part 2

2011-09-082012-09-02 Justin Cooney

Overview:

The latest versions of Excel (2007, 2010) differ from Excel 2003 in their support of streaming a HTML document containing Excel-specific markup. Although core markup features still work, some of the previously supported functionality has now been removed.

This directly affects the ability in code to stream a table to Excel and then use MSO tags to format the layout.

In the more recent versions of Excel (2007, 2010) Microsoft has reworked their support for streaming Excel information on the Web and has added refined control over the ability to create an Excel document with a series of worksheets. But sadly with each new version of Excel they are removing much of their support for streaming HTML information to the browser that worked so well for Web developers in the past.

Instead, when using the newer Microsoft syntax, a temporary file must be saved to the server before it can be sent to the browser. Further, using the new Microsoft method one cannot simply send an HTML table, but must re-query the database, and then re-generate the entire document.

In this article I cover the tried-and-true method of streaming Excel directly to a client’s browser and explain what Excel markup still works, and what doesn’t. I will cover the newer method of doing so in a future article.

If you are interested in further background on this topic or useful links please see Part 1 of this article at: Generating Excel documents through HTML, XML, and CSS – Part 1

How to Stream the Excel Document Directly:

I recently had the requirement to output an ASP.NET GridView report in Excel, but additionally with the following Excel formatting requirements:

Print in Landscape layout
Add a detailed header to each printed page
Add a detailed footer to each printed page
Show Excel Rows and Columns on each printed page
Freeze the header table row when paging through Excel
Show the frozen header table row on each printed page

Using the Excel 2003 document markup tags I am able to make each of these requirements visible in Excel regardless of the version. However some of the requirements make the printing process fail when trying to print with Excel 2007 or Excel 2010 (Excel 2003 of course works just fine).

The requirements that cause the printing process to fail with Excel 2007 or Excel 2010 are:

Show Excel Rows and Columns on each printed page
Freeze the header table row when paging through Excel
Show the frozen header table row on each printed page

So without further ado, here is the code that still works in all versions of Excel Continue reading →