HOW TO: Excel Maker

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

HOW TO: Excel Maker

Postby bcusick » Tue May 16, 2006 7:55 pm

Recently I wanted a way to export all the fields on a form (in a foundset) and export them to Excel. Thanks to Servoy, it was EASY!

There are two methods: one called "getFormFields" and the other called "ExcelMaker" which calls the "getFormFields" function.

The "getFormFields" method takes the NAME of a form as an argument, then pokes into the repository to find out what fields are on a form (NOT "named" fields - but ALL fields whether they have a name or not). The method will also put them in order if they have a tab sequence, and if not, will return them in creation order:

Code: Select all
//pass in the form name
var formName = arguments[0]

//get solution ID
//Get a dataset based on query
var maxReturnedRows = 1
var query = "select solution_id from servoy_solutions where solution_name = ?"
var args = new Array();
args[0] = application.getSolutionName()
var dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var solutionID = dataset.getValue(1,1)
var releaseNum = application.getSolutionRelease()

//get form ID
maxReturnedRows = 1000
query = 'select a.element_id, a.property_value ' +
   'from servoy_element_properties a ' +
   'where a.content_id = 37 ' +
   'and a.revision = (select revision from servoy_releases where solution_id = ? and release_number = ? ' +
   'and element_id = a.element_id) and a.property_value = ? ' +
   'order by a.property_value'

args = new Array();
args[0] = solutionID
args[1] = releaseNum
args[2] = formName

dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var formID = dataset.getValue(1,1)

//get all the dataproviders for the current form (with or without a name)
query = 'select b.property_value, c.property_value, b.element_id, d.property_value ' +
   'from servoy_elements a, servoy_element_properties b, servoy_element_properties c, servoy_element_properties d ' +
   'where a.object_type_id = 4 ' +
   '   and a.element_id = b.element_id ' +
   '   and a.element_id = c.element_id ' +
   '   and a.element_id *= d.element_id ' +
   '   and b.content_id = 15 ' +
   '   and c.content_id = 24 ' +
   '   and d.content_id = 28 ' +
   '   and a.solution_id = ? ' +
   '   and a.parent_element_id = ? ' +
   '   and b.property_value NOT LIKE ? ' +
   'order by d.property_value, b.element_id'

args = new Array();
args[0] = solutionID
args[1] = formID
args[2] = 'globals.%'

dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var colNames = new Array()

if(dataset.getMaxRowIndex() > 0)
{
   colNames = dataset.getColumnAsArray(1)
}


return colNames



Next we have the main "ExcelMaker" method (I made it a global method in a module so that I can use it from ANY solution). It takes no arguments and can be executed from any form:

Code: Select all
//get the form name
var formName = currentcontroller.getName()

//var tabData = forms[formName].controller.copyAllRecords()

//get the table information
var tableName = forms[formName].controller.getTableName()
var max = forms[formName].foundset.getSize();

//get the names of the fields on the form
var colList = globals.getFormFields(formName);
var maxCols = colList.length

if(colList.length > 0)
{

   //output the column names first
   var output = colList.join('\t')
   
   //loop through foundset of form and get all the columns and data
   for ( var i = 1 ; i <= max ; i++ )
   {
      forms[formName].foundset.setSelectedIndex(i)
      
      for ( var c = 0 ; c < maxCols ; c++ )
      {
         if(c == 0)
         {
            //first column
            output += '\n"' + forms[formName].foundset[colList[c]] + '"'
         }
         else
         {
            //subsequent columns
            output += '\t"' + forms[formName].foundset[colList[c]] + '"'
         }
      }
   }
   
   //prompt for the fileName
   var fileName = plugins.file.showFileSaveDialog('myExport.xls')
   
   //see if they chose an export name
   if(fileName)
   {
      var success = plugins.file.writeTXTFile(fileName, output)
      if(success)
      {
         plugins.dialogs.showInfoDialog( '', 'Export successful.', 'OK')
      }
      else
      {
         plugins.dialogs.showErrorDialog( 'Error', 'An error occured when trying to export your data.', 'OK')
      }
   }
   else
   {
      plugins.dialogs.showInfoDialog( '', 'Export canceled by user.', 'OK')
   }
}


I hope this helps in your development efforts!
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Postby Harjo » Tue May 16, 2006 10:43 pm

WOW! :shock: this is great stuff!

Thanks Bob
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 Westy » Wed May 17, 2006 2:43 pm

This is something that we will use a lot!
Thank you very much Bob.

Dean
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Postby Westy » Wed May 17, 2006 3:48 pm

Dates appear in 'Wed Mar 15 00:00:00 EST 2006' format. How can they be shown in MM/DD/YYYY format?
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Postby patrick » Wed May 17, 2006 4:14 pm

There are quite a few issues regarding formatting. An integer like 15 will be printed as 15.0, dates as you say, numbers have no proper formatting, blobs will fail etc. And what happens if a field contains a carriage return? We do all kinds of stuff in our export methods (that basically work the same) to figure out the right format, escape special chars that will "destroy" Excel etc. You could consider using something like

Code: Select all
aTable = databaseManager.getTable(servername, tablename)
allColumns = aTable.getColumnNames();
for ( var i = 0 ; i < allColumns.length ; i++ )
{
   aTable.getColumn(allColumns[i])
   aType = aColumn.getType()
}


to figure out the datatype of a dataprovider. In our export methods we try to gather as much information as possible about the table(s) involved and throw that in a multidimensional array. So Bobs "colList" becomes a more complex Array...

Hope this helps.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Postby Westy » Thu Feb 22, 2007 4:52 pm

Does ExcelMaker work in Servoy 3.x?
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Postby bcusick » Tue Mar 20, 2007 7:12 pm

Ummmm... nope! I'll get a fix going.

That's what happens when you code directly against the repository!

TIP: Don't code directly against the repository! :D
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Postby bcusick » Tue Mar 20, 2007 9:46 pm

CODE FIXED for 3.x:

Just update the "getFormFields" method to the code below - and you're all set for 3.x:

Code: Select all
//pass in the form name
var formName = arguments[0]

//get solution ID
//Get a dataset based on query
var maxReturnedRows = 1
//var query = "select solution_id from servoy_solutions where solution_name = ?"
var query = 'select name, root_element_id, active_release, latest_release from servoy_root_elements ' +
   'where name = ? and object_type_id = 43 order by name'
var args = new Array();
args[0] = application.getSolutionName()
var dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var solutionID = dataset.getValue(1,2)
var releaseNum = application.getSolutionRelease()

maxReturnedRows = 1000

query = 'select a.element_id, a.property_value ' +
   'from servoy_element_properties a ' +
   'where a.content_id = 37 ' +
   'and a.revision = (select revision from servoy_releases where root_element_id = ? ' +
   ' and release_number = ? ' +
   " and element_id = a.element_id) and a.property_value = ? "+
   'order by a.property_value'

args = new Array();
args[0] = solutionID
args[1] = releaseNum
args[2] = formName

dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var formID = dataset.getValue(1,1)

query = 'select b.property_value from servoy_elements a, servoy_element_properties b ' +
   'where a.element_id = b.element_id ' +
   'AND a.parent_element_id = ? ' +  //form id
   'AND b.revision = ? ' + //revision
   'AND b.content_id = ? ' + //fields (15)
   'AND b.property_value not like ?' // 'globals.%' - exclude global fields

args = new Array();
args[0] = formID
args[1] = releaseNum
args[2] = 15
args[3] = 'globals.%'

dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);

var colNames = new Array()

if(dataset.getMaxRowIndex() > 0)
{
   colNames = dataset.getColumnAsArray(1)
}

return colNames
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Postby antonio » Wed Apr 11, 2007 6:53 pm

Hi Bob, is there a full listing somewhere of what each content_id, object_type_id etc corresponds to?
Tony
Servoy 8 - 2022.03 LTS
antonio
 
Posts: 638
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Postby patrick » Wed Apr 11, 2007 7:42 pm

No. But it's all in the tables of the repository. It just takes some adventuring to figure that out...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Postby IT2Be » Wed Apr 11, 2007 9:02 pm

The Analyzer will show you all fields on a form.

If it is not (yet) what you want or need simply add it to our bugtracker or throw me an email and we will add it asap.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: HOW TO: Excel Maker

Postby chico » Mon Apr 27, 2009 5:03 pm

Has anyone used this in Servoy 4.1? It seems to be failing for me.

It seems to fail when trying to pull the formID in the getFormFields method.

-Chico
--------------------------------------------
Servoy Version: 6.0.3
DB: MySQL 5.1
Win XP/Vista/7 - Java 5u20 / 6u22
OS X - 10.6.5 - Java 5/6 update 3
chico
 
Posts: 271
Joined: Tue Nov 20, 2007 6:34 am


Return to How To

Who is online

Users browsing this forum: No registered users and 5 guests

cron