Export to csv file

Classic Servoy
1

I need to export to a csv file from the web client. How do I proceed ?

2

Loop through your records and build the csv file.
Write the file to the server root directory
Return a html link to the csv file

If needed, you could add something like

<mime-mapping>
        <extension>csv</extension>
        <mime-type>text/csv</mime-type>
</mime-mapping>

to the mimetype section of the server\conf\web.xml file.

Dean Westover
Choices Software, Inc.

3

Thanks for the tip.

Is there some sample code ? I went through the forums. This is the only suitable code I could find:

/create a csv from the dataset
var csv = dataset.getAsText(‘,’,‘\n’,‘"’,true);

//create the temp csv and store it for downloading
var fileName = ‘report.csv’;
var tempFile = ‘./server/webapps/ROOT/’ + fileName;
var success = plugins.file.writeTXTFile(tempFile,csv);

I need to export only the foundset displayed columns. Not all the foundset columns are displayed.
Not sure how to convert the dispalyed list foundset to dataset.
Also need to use this as a global method so that I can pass in the form name. I also need to do lookups in the database to find related data.

4

sjk:
Is there some sample code

//add "csv" sub-folder to root folder
//maximum rows to export (test to see what maximum works best for your solution)
var MaxReturnedRows = 300;
var TableName = 'forms';
var ColumnNames = '"' + 'name","description","creation_date","comments' + '"';
//optional filter for query
var Filter = ' ' + 'WHERE key_contact = globals.key_contact';
var Query = "SELECT name,description,creation_date,comments FROM forms WHERE file_id = '"+globals.file_id+"'";// + Filter; 
var Dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), Query, null, MaxReturnedRows); 
//create csv file
var File = Dataset.getAsText(',','\n','"',false);
File = ColumnNames + "\n" + File;
//modify dates - remove time, change order to month/day/year, and change dashes to slashes
File = File.replace(/(\d\d\d\d)-(\d\d)-(\d\d)( \d\d\:\d\d\:\d\d\.\d*))/g,'$2/$3/$1');
//in the modify dates line $2/$3/$1 is what changes the order of month, day, and year
//the numbers 2, 3 and 1 indicate the results of the corresponding matching
//parentheses in the regular expression pattern (earlier in the same line).
//following line removes 'null' - expensive because Replace is applied (again) to entire table
File = utils.stringReplace(File,'null','');
var installdir = java.lang.System.getProperty("user.dir");
installdir = utils.stringReplace(installdir,'\\','\/');
var uuid = application.getNewUUID()
globals.file_name ='spreadsheet-'+uuid+'.csv';
var filepath=installdir+'/server/webapps/ROOT/csv/'+globals.file_name;
var Success = plugins.file.writeTXTFile(filepath,File);
if (application.getApplicationType()==5)
{
	elements.export_to_csv.visible = false;//hide export button after clicked
	elements.link.visible = true;//show file download link using field named "link" with displayType of HTML_AREA
	//the dataprovider for the field named link is a media calc field named "link"
	//the link media calc field = var URL = '/csv/' + globals.file_name;return '<html><body><a href="' + URL + '">Right click here</a>, then select "Save Target As...". Type a file name that ends with ".csv" extension.</body></html>';
	//or you could use
	//application.showURL('/csv/' + globals.file_name,'_self')//this might require mimetype setting described separately above
}

sjk:
Also need to use this as a global method so that I can pass in the form name

Add ability to receive parameter(s) to top of method. Something like…

var fname = arguments[0]
forms[fname].controller.show()

Adjust to your own situation. Others may have suggestions for improvements.

Dean Westover
Choices Software, Inc.