Generate Excel Sheet by GTD Report Server

Question: How to generate excel output from database?

Answer: There are many choices:

For Windows platform

• Call MS Excel runtime to generate excel file.
• Output report in delimited format and open with Excel program.
• Oracle Report plus Rep2excel
• Other third-party reporting solution (Crystal Report etc.).

For Linux/Unix platform

• Output report in delimited format and open with Excel program.
• Call third-party API (Apache POI, JXL etc) to build excel output
• Other third-party reporting solution (Oracle Reports etc.).

Now let's introduce a third-party reporting server named GTD Excel Report Server.

Overview of Steps

1. Install Java runtime.
2. Download & install GTD Excel Report Server
3. Start GTD Server and logon as administrator
4. Create DB connections
5. Build report
6. Run report to get excel output.

Installing Java Runtime Environment (JRE)

GTD Excel Report Server is 100% pure java. You should have Java Runtime Environment (JRE) installed on your server before you install GTD Server.
If you have installed the JRE or JDK 1.5 or above, please ignore this sesction.

Download Free JRE from website of Sun Microsystems. For example: http://www.java.com/en/download/manual.jsp

Once you download the JRE, please install it on the server.

Testing Java Runtime Environment

java -version

You should get similar output

java version "1.5.0_14"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_14-b03)
Java HotSpot(TM) Client VM (build 1.5.0_14-b03, mixed mode, sharing)

Installing GTD Excel Report Server

1. Visit http://gtd.lv2000.com/download.php and download the full edition of GTD Server. It is a zip file. Extract the archive to the desired install directory (GTD_HOME):

• For Windows: c:\Program Files\gtdserver\
• For Linux: /usr/local/gtdserver

2. And then create directory for the configuration files of the server. Make sure that the directory is writable for user that starts GTD report server.

• For Windows: c:\gtdserver\config\
• For Linux: /usr/local/gtdserver-data/config

3. Now le's create directory for the log files. Make sure the directory is writable for user that starts GTD report server.

• For Windows: c:\gtdserver\log\
• For Linux: /usr/local/gtdserver-data/log

4. You will see a file named config.properities.sample in GTD_HOME (installation directory). Please edit the file and then rename the file to config.properities.

Sample file for Linux:

port=8002
configDir=/usr/local/gtdserver-data/config
logFile=/usr/local/gtdserver-data/log/gtdreport.log

Sample file for Windows: ( Note: If you use backslashes, you must double them )

port=8002
configDir=c:/gtdserver/config
logFile=c:\\gtdserver\\log\\gtdreport.log

Start GTD Server by command line.

Open console window or terminal, and then change work directory to GTD_HOME, run shartup.bat (For Windows) or shartup.sh (For Linux). For exampe:

c:\Program Files\gtdserver>startup.bat

linux:/usr/local/gtdserver # chmod a+x *.sh
linux:/usr/local/gtdserver # ./startup.sh

You will see the terminal outpu like this

C:\Program Files\gtdserver>startup.bat

C:\Program Files\gtdserver>echo off

C:\Program Files\gtdserver>java -classpath ;./tomcat-embed/lib/catalina-optional.jar;./tomcat-embed/lib/catalina.jar;./t
omcat-embed/lib/commons-el.jar;./tomcat-embed/lib/commons-logging.jar;./tomcat-embed/lib/commons-modeler-2.0.jar;./tomca
t-embed/lib/jasper-compiler-jdt.jar;./tomcat-embed/lib/jasper-compiler.jar;./tomcat-embed/lib/jasper-runtime.jar;./tomca
t-embed/lib/jsp-api.jar;./tomcat-embed/lib/naming-factory.jar;./tomcat-embed/lib/naming-resources.jar;./tomcat-embed/lib
/servlet-api.jar;./tomcat-embed/lib/servlets-default.jar;./tomcat-embed/lib/tomcat-coyote.jar;./tomcat-embed/lib/tomcat-
http.jar;./tomcat-embed/lib/tomcat-util.jar;./ExcelReportSvr/WebRoot/WEB-INF/lib/log4j-1.2.15.jar;;./lib/antlr.jar;./lib
/classes12.jar;./lib/commons-beanutils.jar;./lib/commons-collections.jar;./lib/commons-digester.jar;./lib/commons-fileup
load.jar;./lib/commons-lang.jar;./lib/commons-logging.jar;./lib/commons-validator.jar;./lib/jakarta-oro.jar;./lib/jdom.j
ar;./lib/log4j-1.2.15.jar;./lib/mysql-connector-java-5.1.6-bin.jar;./lib/poi-3.0.1-alpha1-20070816.jar;./lib/sqljdbc.jar
;./lib/struts.jar;./lib/struts-legacy.jar;./lib/activation.jar;./lib/mail.jar;./xlsrep.jar -Duser.language=en -Duser.reg
ion=US com.gtdreport.excelreport.TomcatServer start
***********************
*
* Loading C:\Program Files\gtdserver/config.properities
*
***********************

Info: port: 8002
Info: configDir: c:\gtdserver\config
Info: logFile: d:\gtdserver\log\gtdreport.log
***********************
*
* Server Starting
*
***********************

log4j:WARN No appenders could be found for logger (org.apache.catalina.startup.Embedded).
log4j:WARN Please initialize the log4j system properly.
StartupConfigServlet.init called
ServletContext/logFile=d:\gtdserver\log\gtdreport.log
2009-01-24 14:59:15 [com.gtdreport.excelreport.struts.StartupConfigServlet]-[INFO] Servlet StartupConfigServlet Init
2009-01-24 14:59:15 [com.gtdreport.excelreport.struts.StartupConfigServlet]-[INFO] releaseDate=20080908
2009-01-24 14:59:15 [com.gtdreport.excelreport.struts.StartupConfigServlet]-[INFO] ServletContext/configDir=c:\gtdserver\config
2009-01-24 14:59:18 [com.gtdreport.excelreport.struts.StartupConfigServlet]-[INFO] ServletContext/version=2.01
2009-01-24 14:59:18 [org.apache.coyote.http11.Http11BaseProtocol]-[INFO] Initializing Coyote HTTP/1.1 on http-8002
2009-01-24 14:59:18 [org.apache.coyote.http11.Http11BaseProtocol]-[INFO] Starting Coyote HTTP/1.1 on http-8002
***********************
*
* Server Started
*
***********************

Visit GTD server via browser

Please open the browser (IE/Firefox etc), and then visit GTD server. The URL looks like:

http://localhost:8002/

Port is specified in GTD_HOME/config.properities

You will see Login form, please login the server, using the initial username and password.

UserName: admin
Password: 1234

Create DB connections

1. Login GTD Server as admin, navigate to Connection Pool Manager

2. Click "Configure a new JDBC Connection Pool... " in the bottom-left of Connection Pool Manager.

3. The Configure a JDBC Connection Pool page appears. Fill in the information given to you by your DBA or administrator, and then click Submit button.

Example:

FAQ. I can not find my database from Database Type select box.

A: Please navigate to JDBC Driver Manager, look for your database.

• If your database exists in JDBC Driver Manager, please enable it. For more information, please visit JDBC Driver Manager section.
• If you can not find your database in JDBC Driver Manager, you should have to create connection pool through Advanced mode.

Build a simple report

Logon GTD Server as admin. Click the link New Report link from Dev tab. Now you can use the Report Wizard to create a simple report.

To learn more on this topic, please click GTD Guide / Building Reports

Run Reports

You can logon GTD Server through web browser such as IE to run report.

1. Logon GTD server, navigate to Run Report page.

• If you are administrator or developer, you can navigate to Report Manager, and then click Run to run specified report.
• If you common user, please navigate to My Reports, and then click Run to run specified report.

2. On the Run Report page, enter/change the value of parameter if availiable, select data source, click Submit button.

3. The GTD server will serve back excel output.

To learn more on this topic, please click GTD Guide / Run Reports

.