SQL 2 Excel Pro allows you to choose download format of a Workbook. The default is .xls but you can also choose between .csv (comma delimited text) and .htm.

The HTML format can be used for various things such as embedding into other pages in iFrames etc. Another interesting way to use the HTML format is together with an Excel Web Query. It allows you to create rich formatted spreadsheets which pulls the values dynamically from your SQL 2 Excel Pro Worksheets. You can create a spreadsheet with any formatting that you like, Charts, Pivot Tables, conditional formatting etc, and then set up a Web Query to grab the HTML output from a SQL 2 Excel Pro Worksheet.

Notes!

• Excel will grab the data from the webserver as a Public user. This technique only works if your Workbook/Worksheet is published for Public users in SQL 2 Excel Pro.
  
  
• Requires SQL 2 Excel Pro version 1.9.0 or higher
  

You should be able to use this in Excel 2003 as well, but the menus and Excel UI looks different.

Step-by-step Instructions

1. Create a Workbook which contains one or more Worksheets in SQL 2 Excel Pro.  See the SQL 2 Excel Tutorial for the basic steps on how to do this.
   
   
2. Publish the Workbook and make sure the access is set to Public on the Workbook, Worksheet as well as the SQL 2 Excel Pro Section and Category.
   
   
3. Get the download URL for the Workbook.
   
   If you don't use SEF
   
   
   • Go to the frontend and right-click on the Download button and select Properties
     
     
   • Hightlight the URL in the dialog by clicking and dragging your selection to the very end of the URL
     
     Internet Explorer:
     
     
     Firefox:
     
     
     
   • Press CTRL + C to copy the selected URL to clipboard
     
   • Dismiss the dialog
     
     

   If you do use SEF:

   • The best bet is to compose the URL manually. The download URL looks like like this:
     

     http://yourserver.yourdomain.com/index.php?option=com_sql2excel&controller=download&task=dl&id=[workbook_id]

     ...where [workbook_id] is the Workbook ID that you wish to grab data from. Try the URL in your web browser until you get it right.

    

4. Open Notepad and paste in the URL or type it in.
   
   Sample URL:
   
   

   http://localhost/dev1511/index.php?option=com_sql2excel&controller=download&task=dl&id=1

   
   The download URL will invoke a download of the Excel file - unless you have set the download format to HTM. You can override the download format, by adding a format selection parameter to the URL.
   
   

   Format Value	Format
   2	Excel .xls  (97-2003)
   3	CSV
   4	HTML

   
   
   In this case we want to use HTML, so we add &format=4 to the end of the URL, like this:
   

    http://localhost/dev1511/index.php?option=com_sql2excel&controller=download&task=dl&id=1&format=4

   
   
5. Try this URL in a browser and make sure you get a web page with your Worksheet data.
   
   The page will by default only show the first Worksheet in the Workbook.  You can access a specific Worksheet in the Workbook by adding another parameter to the URL
   
   sheet=[Worksheet_id]
   
   For example, the Workbook contains two Worksheets:
   
   

   Worksheet ID	Sheet Name
   5	Popular Articles
   6	Latest Articles

   
   
   You want to access the second Worksheet, Latest Articles, then you add sheet=6 to the URL, like this:
   

   http://localhost/dev1511/index.php?option=com_sql2excel&controller=download&task=dl&id=1&format=4&sheet=6

   
   
6. Start Excel
   
   
7. Open an existing spreadsheet where you wish to import the data, or create a new one from scratch.  I'm starting with a brand new spreadsheet in this tutorial.
   
   
8. Go to the cell where you wish to import the data from SQL 2 Excel Pro. The data columns will be populated to the right of the selected cell, and the rows will be populated below. E.g. you are selecting the upper left corner for the data from SQL 2 Excel Pro. I select A5 :
   
   
   
   
9. Select  the Data Ribbon and then click on "From Web" button
   
   
   
   
10. In the dialog that pops up, copy and paste in the download URL that you got in step 5)  as the Address using CTRL+C and CTRL + V
    
    
11. Click on Go
    
    
    
12. The HTML version of the Worksheet should be shown in the window below, something like this:
    
    
    
    
    
13. Select the data table by clicking on the small yellow arrow. The arrow turns into a green check mark once you have selected it.
    
    
    
    
14. Click on Import
    
    
15. A dialog pops up asking where you want to put the data. We have already selected A5, so just click OK.
    
    
    
    
16. The spreadsheet is populated with the data from the Worksheet URL
    
    
    
    
17. You could add data from more than one Worksheet at this point. I'm going to leave it at this single one for this tutorial..
    
    
18. Now you can add formatting, charts, auto-filter, font- & number formats, conditonal formatting etc etc..
    
    
    
    
19. Save the Spreadsheet
    
    
    
20. To update the data in the spreadsheet, just click on the Refresh All button on the Data Ribbon. The formatting stays intact, you only need to do that once.
    
    
    

References

• Different Ways of Using Web Queries in Microsoft Office Excel 2003

• Pull data into Microsoft Excel with Web queries