Export wizard to Excel anybody?

Classic Servoy
1

I would like to give my users the ability to use an export wizard. This is what it should do:
Based on the current foundset give the user a list of a subset of columns that are allowed the be exported, but not in the technical way but with an alias name (maybe with i18n).
Even the relations should use alias names, the users simply do not understand the "dataprovider…
Maybe it would be easier if all the allowed columns would be placed on a layout.
The export format should be Excel .xls.
I already tried the exportmaker that bob once did, nice thing but the users simply want it different.

I already looked at the data-plugin, but I think that would be half of the story.
Oliver

2

I already looked at the data-plugin, but I think that would be half of the story.

You are (sort of) correct because it is intentional.
The more we would ‘invent’ the more we would have an issue with other people wanting something else.

3

bender42:
I already tried the exportmaker that bob once did, nice thing but the users simply want it different.

What if you would use this code, with some adjustments you can simply make this work for every screen without having to bother about any exportdefinition at all… I could post you the code we use…

Besides this, give your users the ability to define customized tableviews using the solutionmodel.
Once they created a tableview they can export it again… and better: this tableview may persist in a database, so next time they don’t have to define anything at all!

Maybe this different view may help you…

4

Thanks Marc,

a code snippet would be interessting.

Customized tableviews with solutionmodel is something specific for servoy 4.+? I just don´t know what that is.

What else should be possible is to use an abstraction between user input and database columns and relationnames.
I thought of a table that kind of translate or describes the structure and use i18n for that as well.

Oliver Naegele
OScomputing AG

5

bender42:
a code snippet would be interessting.

Here you go:

function exportExcel()
{
	// get the form name
	var $form	= globals.nav_form;
	var $table	= forms[$form].controller.getTableName();
	var $maxRow	= forms[$form].foundset.getSize();

	//	get the names of the fields on the form
	var $colProps = globals.getExportFormFields($form);
	
	var $colList		= $colProps[0];
	var $colValueList	= $colProps[1];
	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 <= $maxRow ; i++ )
		{
			forms[$form].foundset.setSelectedIndex(i)

			for ( var c = 0 ; c < $maxCols ; c++ )
			{
				if(c == 0)
				{
					//first column
					$output += '\n"' + forms[$form].foundset[$colList[c]] + '"'
				}
				else
				{
					//subsequent columns
					if(!$colValueList[c])
					{	
						$output += '\t"' + forms[$form].foundset[$colList[c]] + '"'
					}
					else
					{
						$output += '\t"' + application.getValueListDisplayValue($colValueList[c], forms[$form].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')
		}
	}
}

function getExportFormFields()
{
    //pass in the form name
    var $form = arguments[0];
    var $elements = forms[$form].elements.allnames;
    var $colNames = new Array();
    var $vlNames = new Array();
        
    if(/_tbl/.test($form) && $elements.length > 0)
    {
    	$elements.sort(function(a,b){return forms[$form].elements[a].getLocationX() - forms[$form].elements[b].getLocationX()});
    }
    
    for (var i = 0; i < $elements.length; i++)
    {
    	if (/^fld_/.test($elements[i]))
    	{
    		$colNames[$colNames.length] = forms[$form].elements[$elements[i]].getDataProviderID();
    		$vlNames[$vlNames.length]	= forms[$form].elements[$elements[i]].getValueListName();
    	}
    }
                                 
    return [$colNames, $vlNames];
}

The solutionmodel allows you to create almost everything dynamicly within Servoy. This means also forms.
You could start of converting a real form to the database, then let the user add/delete columns via your abstraction and then create the form every time the user needs it.