Opening or saving the SQL report output in Excel.

Classic Servoy
1

Hi all,

I have the following method that runs sql report.

Is there a way to opening or saving the sql report output in Excel?

maxRows = 20;
var TB1 = globals.SQL_Table1
var TB2 = globals.SQL_Table2
var subj = globals.Num1

query2 = "select " + TB1 +".*,  " + TB2 +".* from " + TB1 +",  " + TB2 +" where " + TB1 +".subj_num = " + TB2 +".subj_num and  " + TB1 +".subj_num in ( " + subj + ") order by  " + TB1 +".subj_num   ";

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query2, null, maxRows);

globals.SQL_HTML = '<html>'+dataset.getAsHTML() + '</html>'

Thanks in advance,

Abrahim

2

Use getAsText:

Function getAsText
Description
Returns the dataset result of SQL query as formatted text.
Syntax
dataset.getAsText(String column_separator, String
row_separator, String value_delimiter, boolean addColumnNames)
Parameters
String column_separator - any specified column separator; examples: tab
‘\t’; comma ‘,’; semicolon ‘;’; space ’ ’ .
String row_separator - the specified row separator; examples: new line ‘\n’.
String value_delimiter - the specified value delimiter; example: double quote
‘"’.
boolean addColumnNames - true to add column names as a first row; or false.

Example //assuming the variable dataset contains a dataset
//you can create csv or tab delimited results
var csv = dataset.getAsText(‘,’,‘\n’,‘"’,true);
var tab = dataset.getAsText(‘\t’,‘\n’,‘"’,true);

3

Paul,

Here is what I did:
I added the following two lines to the end of method and called it:

var tab = dataset.getAsText('\t','\n','"',true);
 
globals.SQL_HTML = tab

The report output in globals.SQL_HTML field was tab delimiter.
Can I link this to The “ExcelMaker” method or is there any other way to view or export it into Excel?

Thanks,

Abrahim

4

If you seperate columns with a semikolon, write everything to a text file ending “.csv” you should be able to open it directly with Excel without further dialogs or assistants.

5

You can also use the data plugin to take away all the pain of converting data types, hassle with quotes and problems with ‘in-cell’ tabs, returns etc.

6

Using the native servoy plugins, you need something like this:

var tab = dataset.getAsText('\t','\n','"',true);
var fileName = 'export.xls';
//Show a save dialog
var file = plugins.file.showFileSaveDialog(fileName);
//Write textual file
var success = plugins.file.writeTXTFile(file,tab);
7

You guys are awesome!

Thanks for all the help.

Abrahim