Sunday, January 2, 2011

A easy way to create simple excel reports with formatting and colors

Do you have a requirement to create small reports in Excel format, but not enough time and budget to buy and integrate components to create full fledged Excel binaries? Here is an easy way to create well formatted good looking Excel report using a simple server side component.

Instead of creating real Excel binary data; this idea utilizes the html parsing capabilities of MS Excel. A well formatted html document could very well be parsed and opened in MS Excel. Let us see the below example. See the HTML code below

<html>
<head></head>
<body>
      <table border=1>
            <tr>
                  <td>BookId</td>
                  <td>Name</td>
                  <td>Author</td>
                  <td>Price</td>
            </tr>
            <tr>
                  <td>ASR </td>
                  <td>A simple report </td>
                  <td>Best Author</td>
                  <td>$10.00</td>
            </tr>

      </table>
</body>
</html>

Try copying the code in a text file and save it as test.xls. When you double click and open it, you can see that, excel opens the document without any problem.



Now try adding formatting and colors to the td tags.

<html>
<head></head>
<body>
      <table border=1>
            <tr>
                  <td bgcolor="#998800"><b>BookId</b></td>
                  <td bgcolor="#998800"><b>Name</b></td>
                  <td bgcolor="#998800"><b>Author</b></td>
                  <td bgcolor="#998800"><b>Price</b></td>
            </tr>
            <tr>
                  <td>ASR </td>
                  <td>A simple report </td>
                  <td>Best Author</td>
                  <td>$10.00</td>
            </tr>

      </table>
</body>
</html>


You can see that Excel handles that pretty well too.

To simulate the merge feature of Excel you can use colspan or rowspan appropriately in the td tag.

<html>
<head></head>
<body>
      <table border=1>
            <tr>
                  <td bgcolor="#998800"><b>BookId</b></td>
                  <td bgcolor="#998800"><b>Name</b></td>
                  <td bgcolor="#998800"><b>Author</b></td>
                  <td bgcolor="#998800"><b>Price</b></td>
            </tr>
            <tr>
                  <td>ASR </td>
                  <td>A simple report </td>
                  <td>Best Author</td>
                  <td>$10.00</td>
            </tr>
            <tr>
                  <td>AASR </td>
                  <td>Another simple report </td>
                  <td>Another Best Author</td>
                  <td>$10.00</td>
            </tr>
            <tr>
                  <td colspan="3" align="right" bgcolor="#990010" style="color:#ffffff"><b>Total</b></td>
                  <td>$20.00</td>
            </tr>

      </table>
</body>
</html>


As you can see, this is a very easy and cost-effective way to create simple Excel reports that looks good.

If you want to integrate this feature into a web application, just write the plain HTML back into the response stream with one of the following acceptable mime types.

application/vnd.ms-excel (official)
application/msexcel
application/x-msexcel
application/x-ms-excel
application/vnd.ms-excel
application/x-excel
application/x-dos_ms_excel
application/xls application/x-xls

As long as the client PC has MS Excel installed, the document will open using Excel application in the client browser.

0 comments:

Post a Comment

Blog Archive