1. In Joomla Administrator, select Component > SQL 2 Excel. This takes you to the SQL 2 Excel control panel."
   
   
   
   You can install sample data by clicking on the "Add Sample Data" button. But this tutorial will guide you through the whole process from scratch.
   
   
2. SQL 2 Excel supports Sections and Categories. It is the same concept as Joomla uses to classify articles. First define a Section. Click on "Sections" in the SQL 2 Excel control panel. This will show you the list of defined sections.
   
   
   
   
3. Click on New in the toolbar. This brings up a form where you can specify the name of your Section. We want to create a section called "Demo" so we fill it in like this:
   
   
   
   Note: We can control access to the section with two method. One is by by setting the Access level to Public/Registered/Special. The other is by setting it to Published/Unpublished. When it is Unpublished the section will NOT show up in the SQL 2 Excel module in the frontent. Here we set it to Public access and Published.
   
   Note 2 : Description is only used for your own notes. Not displayed anywhere at the moment.
   
   Click on Save to save your Section.
   
   
   
   
4. Now you should see the Demo section listed in the list of sections, something like this:
   
   
   
   
5. Next, we need a Category. Click on Categories and then on New. It will bring up a similar form where you enter the Category information. Here I define a Category called "My Category" for this tutorial. Setting it to Public access and making it Published directly. You need to select a section for your category. I select our "Demo" section as the parent of this category.
   
   
   
   
6. Click on Save The category and you should now see your new category in the list of Categories:
   
   
   
   
7. Next step is to create Worksheets that we wish to be included in our Excel Workbook. Click on Worksheets and then click on New. It brings up a form where you can define the content of a Worksheet. There are several settings in this form, but it's fairly simple to setup.
   
   General Settings
   
   
   
   The Title is only used in the backend. You can use some descriptive name so that you can identify the worksheet.
   The Sheet Name is the name of the Worksheet in the Excel workbook.
   Include no records : If you set this to Yes then the worksheet will be included even when the query returns zero results. YIf you set it to No then the Worksheet is not even included in teh Workbook when no records are found.
   Message no records : Optional message to show on the Worksheet when no records are found.
   
   
   
   
   
   Header
   
   
   

   Query
   
   
   
   This is the key part of the Worksheet setting. It controls what data you output.
   
   Column Heading Bg Col : The background color of the column headings.
   Column Heading Text Col : The text color of the column headings.
   Column Heading Text Size : The text size of the column headings.
   Col widths : Column widths. Comma delimited list. In this case we define the width of the first three columns of the worksheet. The rest of the columns will be left at default.
   SQL Query : The SQL query that will be executed for this worksheet. Any valid SQL query can be entered here. Use myPHPadmin, or soem other means to debug and test your queries to make sure they are error free.
   
   Footer
   
   
   
   Similar to the header. This text shows up at the bottom of the Worksheet, below the data output. Please note the use of a line break to create a space between the data and the footer text.
   
   Click on Save to store your new Worksheet. It should show up in the list of Worksheets like this:
   
   

8. Finally, we define a workbook. Click on Workbooks and then on New. This brings up a form where you define the workbook properties:
   
   
   
   We specify a Seaction and a Category for the workbook. This allows us to later display specific Workbooks in the SQL 2 Excel Module. You can copy the module and publish it on different pages. Each module can be configured to show Workbooks only from certain Sections and/or categories.
   
   At the bottom of this form you can select which Worksheets to include in this Workbook.  Select one or more Workbooks by using the green arrows. You can move the selected Worksheets up or down using the up/down arrows to the right. This controls the sheet order in the Workbook. The Worksheet listed on top will be the first Worksheet. In this case we only include one Worksheet so the order is not an issue.
   
   We select to publish this Workbook with Public access.
   
   Click on Save.
   
   
   
   
   
   
9. The Workbook should now show up in the list of defined Workbooks, like this:
   
   
   
   
   
10. We have now defined our first Workbook. It only has one Worksheet. If you want to add more Worksheets, just define the extra worksheets and then edit the Workbook and include as many Worksheets you need. So how do we now publish these Workbooks? This is where the SQL 2 Excel Module comes in. Make sure you have published the module in Module Manager and that you are displaying it on some page(s).
    
    
11. Go to a page on your site where the SQL 2 Excel Module has been published on. It should now show up and list the Workbook that we defined:
    
    
    
    The Articles Workbook shows up. Clicking on the link executes the SQL query that we have defined for the Worksheet and delivers an Excel file directly to the browser. This is what the report looks like in OpenOffice Calc:
    
    
    
    

This concludes the first simple "Hello World" tutorial for SQL 2 Excel.

Still unclear about something?

• Submit a comment/question below
  
  
• Use our Discussion Forum