Login | Register
My pages Projects Community openCollabNet

Chapter 3. Report Definition File

XML Overview

We tried to keep the XML DTD as simple as possible. Each XML file must have a "WORKBOOK" tag and inside that tag we must define a "SHEET" tag or multiple "SHEET" tags. Inside a SHEET we must have at least one "ROW" or "RESULTSET" tag. A "ROW" represents a row in the Excel WorkSheet. A "RESULTSET" tag represents multiple rows and is generated from a SQL query sent to a database. To make use of a "RESULTSET" tag you must have already configured a database connection using the "DB" tag. The "DB" tag is optional but if you want to fetch data from a datasource you need it. Inside the "DB" tag you must specify a "DB_DRIVER", "DB_PASSWORD", "DB_USER", "DB_PATH", and "DB_URL". "DB_PATH" is the path to your jdbc driver and the rest is required by your JDBC driver. Inside the "RESULTSET" or "ROW" tags you need to define at least one "COLUMN" element. A "COLUMN" can be thought of as a spreadsheet cell if defined within a "ROW" element. If a "COLUMN" element is defined in a "RESULTSET" you can think of it as a column in the spreadsheet. Within a "RESULTSET" element you should have a column defined for every column you expect the SQL query to return. Inside the "COLUMN" element we must specify a "VALUE" element which represents the data in the cell. Also inside a "RESULTSET" element we must write a "SQL" element that represents the SQL query we will sent to the database. There are also some optional tags we will discuss later. Below is a snippet of XML that might help clear things up.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TOEXCEL SYSTEM "TOEXCEL.dtd">
<TOEXCEL>
  <WORKBOOK Name="?location" Exists="true" ParamFile="params/Supplier.xml">
    <SHEET Name="?sheet1" Exists="true" Crop="true">
	 <HEADER id="Header">
        <HEADER_LEFT Type="Parameter">Author</HEADER_LEFT>
        <HEADER_CENTER Type="Parameter">Company</HEADER_CENTER>
        <HEADER_RIGHT Type="Text">=Date: =Time:</HEADER_RIGHT>
      </HEADER>
      <FOOTER id="Footer">
        <FOOTER_CENTER Type="Text">=File:</FOOTER_CENTER>
        <FOOTER_LEFT Type="Text">=Sheet:</FOOTER_LEFT>
        <FOOTER_RIGHT Type="Text">=Page: of =NumOfPages:</FOOTER_RIGHT>
      </FOOTER>
      <DB id="DB">
        <DB_DRIVER Type="Parameter">DB_DRIVER</DB_DRIVER>
        <DB_URL Type="Parameter">DB_URL</DB_URL>
        <DB_USER Type="Parameter">DB_USER</DB_USER>
        <DB_PASSWORD Type="Parameter">DB_PASSWORD</DB_PASSWORD>
        <DB_PATH Type="Parameter">DB_PATH</DB_PATH>
      </DB>
      <ROW Exists="false" id="0" Height="-1">
        <COLUMN Width="15" Align="0" DataFormat="General" id="0">
          <VALUE Type="Text">Supplier #</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="1">
          <VALUE Type="Text">Supplier</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="2">
          <VALUE Type="Parameter">reportType</VALUE>
        </COLUMN>
      </ROW>
      <RESULTSET Exists="false" id="-1" Height="-1">
        <COLUMN Width="15" Align="0" DataFormat="General" id="0" BgColor="13">
          <VALUE Type="Database">1</VALUE>
        </COLUMN>
        <COLUMN Width="15" Align="0" DataFormat="General" id="1" BgColor="49">
          <VALUE Type="Database">2</VALUE>
        </COLUMN>
        <COLUMN Width="15" Align="0" DataFormat="($#,##0.00_);[Red]($#,##0.00)" 
        	id="2" BgColor="9">
          <VALUE Type="Database">3</VALUE>
        </COLUMN>
        <SQL IsPreparedStmt="true" id="rs1">select SUPP_NO, SUPP_NAME,? from SUPPDETAILS
        		 order by ? DESC</SQL>
      </RESULTSET>
    </SHEET>
  </WORKBOOK>
</TOEXCEL>

The above sample report definition file relies on a paramater file listed below. What we are trying to do is keep the report definition file static. That is once it is defined you leave it alone and modify the associated parameter xml file.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TOEXCEL SYSTEM "TOEXCELPARAMS.dtd">
<TOEXCELPARAMS>
<PARAMETERS id="DB">
	<PARAM id="DB_PATH">c:\pathtodriver.jar</PARAM>
		<PARAM id="DB_DRIVER">sun.jdbc.odbc.JdbcOdbcDriver</PARAM>
		<PARAM id="DB_URL">jdbc:odbc:BVDATALOCAL</PARAM>
		<PARAM id="DB_PASSWORD">vv</PARAM>
		<PARAM id="DB_USER">paul</PARAM>
		</PARAMETERS>
	<PARAMETERS id="?location">
		<PARAM id="Path">C:\Documents and Settings\Paul\My Documents\test.xls</PARAM>
	</PARAMETERS>
	<PARAMETERS id="?sheet1">
		<PARAM id="Name">
			Suppliers
		</PARAM>
	</PARAMETERS>
	
	<PARAMETERS id="rs1">
		<PARAM id="0">SUPP_YTD_PURCH</PARAM>
		<PARAM id="1">SUPP_YTD_PURCH</PARAM>
	</PARAMETERS>
	
	<PARAMETERS id="Suppliers">
		<PARAM id="reportType">Year To Date Purchases</PARAM>		
	</PARAMETERS>	
	<PARAMETERS id="Header">
		<PARAM id="Author">Prepared by: Paul Pound</PARAM>
		<PARAM id="Company">your company name here</PARAM>		
	</PARAMETERS>	
</TOEXCELPARAMS>