HOW TO: make your own scriptable export.

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

HOW TO: make your own scriptable export.

Postby Harjo » Sun Aug 08, 2004 8:45 pm

Hi,
in one of my solutions I needed to export data to certain files, with a specific field-order, and field-seperator.
Because the export-plugin won't allow us to do scriptable export (yet), I made a method of my own.

make a method, called: createExportFile
Code: Select all
var fieldnames = arguments[0]    // boolean, export with ColumnNames at top
var seperator = arguments[1]    // string, choose seperator, for example:  "\t" for TAB "," or ";"
var quotes = arguments[2]       // boolean, ColumnNames with quotes
var columnnames = arguments[3]   // string, ColumnNames seperated by: ";"
var exportfile = ""          //  create a text variable

// set the variable insertquotes to " or nothing
if (quotes == true) var insertquotes = '"'
else var insertquotes = "";

// split the columnames into an Array
var columnArray = columnnames.split(";")
// determine how many columns are used
var columnCount = columnArray.length

// if fieldnames is true than place the ColumnNames at top
if(fieldnames == true)
{   
   for(var x=0;x<= columnCount-1;x++)
   {
      if(x != columnCount-1) exportfile += columnArray[x] + seperator
      else exportfile += columnArray[x]
   }
// make an 'Enter'   
exportfile += "\n"
}

// loop thru the foundset
for(var i=1;i<= foundset.getSize();i++)
{
   var record = foundset.getRecord(i);
   for(var x=0;x<= columnCount-1;x++)
   {
      if(record[columnArray[x]])
      {
         if(x != columnCount-1) exportfile += insertquotes + record[columnArray[x]] + insertquotes + seperator
         else exportfile += insertquotes + record[columnArray[x]] + insertquotes
      }
      else
      {
         if(x != columnCount-1) exportfile += insertquotes + seperator
         else exportfile += insertquotes
      }
   }
   exportfile += "\n"
}

//open dialog, with a suggested filename
var save = application.showFileSaveDialog('file_export.mer')
if(save != null)
{
   var success = application.writeTXTFile(save,exportfile);
   if (success)
   {
      plugins.dialogs.showInfoDialog('Info', 'The File is saved!','OK');
   }
   else
   {
      plugins.dialogs.showErrorDialog('Warning', 'The File is NOT saved because of an error!','OK');
   }
}



Here is an example how you can call the method:
Code: Select all
//first argument: boolean, export with ColumnNames at top
//second argument: string, choose seperator, for example:  "\t" for TAB "," or ";"
//third argument: boolean, ColumnNames with quotes.
//fourth argument: string, ColumnNames seperated by: ";"
createExportFile(true,"\t",true,"customerid;companyname");


You even can use related fields, for example:
Code: Select all
createExportFile(true,"\t",true,"customerid;companyname;company_to_adresses.adres");


The method is pretty FAST. In my example it exported 2600 records in about 4 seconds.

BTW: because I am using new functions in Servoy 2.1, above only works in this version!

ENJOY!
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Postby Stef » Fri Sep 03, 2004 11:37 am

This is great stuff, Harjo!

Saves me hours of work!

Thanks,
Stef
Stef
 
Posts: 208
Joined: Wed May 28, 2003 2:19 pm
Location: Kasterlee, Belgium

Postby faheemhameed » Tue Nov 02, 2004 8:17 am

Great stuff!!

It will be great if we can be able to pass the foundset object.
Also if we can have option to change the \n characters in the field to someother character, that will be very useful.

Thanks
Hameed
Pilot simple software
Hong Kong
User avatar
faheemhameed
 
Posts: 763
Joined: Wed Sep 10, 2003 7:23 am

Postby Harjo » Tue Nov 02, 2004 8:51 am

faheemhameed wrote:Great stuff!!

It will be great if we can be able to pass the foundset object.
Also if we can have option to change the \n characters in the field to someother character, that will be very useful.

Thanks

1.) What do you mean by that? the method does only the current foundset now!
2.) Yes, change: exportfile += "\n" into something else. But why would you do that?
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Postby faheemhameed » Tue Nov 02, 2004 9:23 am

1.) What do you mean by that? the method does only the current foundset now!


I want to call this method from within a global method. So I would pass the foundset of table I want to export as follows:

Code: Select all
createExportFile(forms.studentList.foundset, true,"\t",true,"customerid;companyname;company_to_adresses.adres");


2.) Yes, change: exportfile += "\n" into something else. But why would you do that?


I do not wanted to replace the row seperator (\n). I wanted to replace a \n character in a column's value to someother character because it breaks the current record and makes a new record. So If export 10 records and we have a \n character in two of those records, then I get 12 records in the export instead of 10.

Say for example if you open up the exported file in the excel you would get wrong number of records.

Thanks
Hameed
Pilot simple software
Hong Kong
User avatar
faheemhameed
 
Posts: 763
Joined: Wed Sep 10, 2003 7:23 am

Postby Harjo » Tue Nov 02, 2004 9:58 am

At your service! 8)

Both wishes are implemented:

make a globalmethod: createExportFile

Code: Select all
var form = arguments[0]
var fieldnames = arguments[1]    // boolean, export with ColumnNames at top
var seperator = arguments[2]    // string, choose seperator, for example:  "\t" for TAB "," or ";"
var quotes = arguments[3]       // boolean, ColumnNames with quotes
var columnnames = arguments[4]   // string, ColumnNames seperated by: ";"
var exportfile = ""          //  create a text variable

// set the variable insertquotes to " or nothing
if (quotes == true) var insertquotes = '"'
else var insertquotes = "";

// split the columnames into an Array
var columnArray = columnnames.split(";")
// determine how many columns are used
var columnCount = columnArray.length

// if fieldnames is true than place the ColumnNames at top
if(fieldnames == true)
{   
   for(var x=0;x<= columnCount-1;x++)
   {
      if(x != columnCount-1) exportfile += columnArray[x] + seperator
      else exportfile += columnArray[x]
   }
// make an 'Enter'   
exportfile += "\n"
}

// loop thru the foundset
for(var i=1;i<= forms[form].foundset.getSize();i++)
{
   var record = forms[form].foundset.getRecord(i);
   for(var x=0;x<= columnCount-1;x++)
   {
      if(record[columnArray[x]])
      {
        utils.stringReplace(record[columnArray[x]],'\n', '')
      if(x != columnCount-1) exportfile += insertquotes + record[columnArray[x]] + insertquotes + seperator
         else exportfile += insertquotes + record[columnArray[x]] + insertquotes
      }
      else
      {
         if(x != columnCount-1) exportfile += insertquotes + seperator
         else exportfile += insertquotes
      }
   }
   exportfile += "\n"
}

//open dialog, with a suggested filename
var save = application.showFileSaveDialog('file_export.mer')
if(save != null)
{
   var success = application.writeTXTFile(save,exportfile);
   if (success)
   {
      plugins.dialogs.showInfoDialog('Info', 'The File is saved!','OK');
   }
   else
   {
      plugins.dialogs.showErrorDialog('Warning', 'The File is NOT saved because of an error!','OK');
   }
}


make a global method: startExportFile:
Code: Select all
//first argument: give the form name
//second argument: boolean, export with ColumnNames at top
//third argument: string, choose seperator, for example:  "\t" for TAB "," or ";"
//fourth argument: boolean, ColumnNames with quotes.
//fifth argument: string, ColumnNames seperated by: ";"

createExportFile('companies_list',true,"\t",true,"customerid;companyname");


ENJOY!
Last edited by Harjo on Tue Nov 02, 2004 10:13 am, edited 1 time in total.
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Postby faheemhameed » Tue Nov 02, 2004 10:09 am

Great work!! Nice and quick.

I am using this script for my export now.


Thanks a lot.
Hameed
Pilot simple software
Hong Kong
User avatar
faheemhameed
 
Posts: 763
Joined: Wed Sep 10, 2003 7:23 am

Re: HOW TO: make your own scriptable export.

Postby amcgilly » Tue Mar 10, 2009 11:43 pm

Harjo,

I've been using this all over the place and it's been working great. Thanks again for posting it.

I am trying to refine it a bit so that I can make it format dates correctly as it exports them. I need to identify the type of each dataprovider specified in the columnArray, bearing in mind that the dataproviders might come from relations, and not necessarily from the base table of the form. Through much fiddling with JSTable and JSColumn objects I could probably get there, but I was hoping to find a getDataproviderType() function. That would have made it very easy. But I don't see such a function.

Any suggestions? Am I missing something?
Adrian McGilly
Servoy Developer

Image
amcgilly
 
Posts: 375
Joined: Fri Dec 09, 2005 12:03 am
Location: San Francisco, CA

Re: HOW TO: make your own scriptable export.

Postby ngervasi » Wed Mar 11, 2009 12:18 am

I like to have more control over the format of the fields so here's what I'm using (the code is pretty old but works fine in servoy 4.x):

Code: Select all
function sintpro_inout_exportHtmlFile2()
{
   // Export in HTML format
   // Es.: sintpro_inout_exportHtmlFile('articoli_tabella','id;marca;modello;prezzo','ID;Brand;Model;Price','num;txt;txt;num2','export.html');
   // Format keywords: num, num2, val4, val2, data, dataora, boolean
   
   var formName = arguments[0]         // string, form name
   var columnnames = arguments[1]      // string, ColumnNames seperated by: ";"
   var fieldnames = arguments[2]      // string, FielnaNames separated by ";"
   var formats = arguments[3]         // string, Format keywords seperated by: ";"
   var nomeFile = arguments[4]         // File name
   
   var exportfile = '<html><head><style type="text/css"> .text {font-family: Verdana; font-size: 10pt;}</style></head><body class="text"><table>'
   
   // Split the columnames into an Array
   var columnArray = columnnames.split(';')
   // Split the fieldnames into an Array
   var fieldnamesArray = fieldnames.split(';')
   // Split the Formats into an Array
   var formatsArray = formats.split(';')
   // Determine how many columns are used
   var columnCount = columnArray.length
   
   // If fieldnames is not null than place the Field Names at top
   if(fieldnames)
   {
      // Apro la riga
      exportfile += '<tr>'   
      for(var x=0;x<= columnCount-1;x++)
      {
         exportfile += '<td><b>' + fieldnamesArray[x] + '</b></td>'
      }
      // Chiudo la riga   
      exportfile += '</tr>'
   }
   
   // Loop thru the foundset
   for(var i=1;i<= forms[formName].foundset.getSize();i++)
   {
      exportfile += '<tr>'
      
      var record = forms[formName].foundset.getRecord(i);
      for(var x=0;x<= columnCount-1;x++)
      { 
         exportfile += '<td>' +
         globals.sintpro_inout_formattaValore(record[columnArray[x]],formatsArray[x]);
         + '</td>'
      }    
      exportfile += '</tr>'
   }
   
   exportfile += '</table></body></html>'
   
   // Open dialog, with a suggested filename
   var save = plugins.file.showFileSaveDialog(nomeFile)
   
   if(save != null)
   {
      var success = plugins.file.writeTXTFile(save,exportfile)
      if (success)
      {
         plugins.dialogs.showInfoDialog('Info', 'File '+ save + ' esportato con successo.','OK');
      }
      else
      {
         plugins.dialogs.showErrorDialog('Attenzione!', 'Errore! Il file non è stato esportato','OK');
      }
   }
}


And this is the method to format the fields:

Code: Select all
function sintpro_inout_formattaValore()
{
   // format the output based on the supplied format keyword
   // num, num2, val4, val2, data, dataora, boolean
   
   var input = arguments[0]   // value to format
   var formato = arguments[1]   // format keyword
   // Default to no format
   var output = input
   
   switch(formato)
   {
      case 'num':
      output = utils.numberFormat(input, '0.#');
      break;
      
      case 'num2':
      output = utils.numberFormat(input, '0.00');
      break;
      
      case 'val4':
      output = utils.numberFormat(input, '¤0.0000');
      break;
      
      case 'val2':
      output = utils.numberFormat(input, '¤0.00');
      break;
      
      case 'data':
      output = utils.dateFormat(input,'dd-MM-yyyy');
      break;
      
      case 'dataora':
      output = utils.dateFormat(input,'dd-MM-yyyy HH:mm:ss');
      break;
      
      case 'boolean':
         if (input > 0)
         {
            output = 'Y';
         }
         else
         {
            output = 'N';
         }
      break;
   }
   
   return output;
}
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy


Return to How To

Who is online

Users browsing this forum: No registered users and 6 guests

cron