Hi Folks - looking for a simple method to export a foundset to an xls file.
Looks like that option is available on the File menu but I cant find how that can be applied in code.
On a similar note I see the controller.printXML - I cant for the life of me see any but obscure reasons why printing to a document of XML would be useful - I would have thought a methods to send the XML to a file would have been more useful - I’m sure there’ll be gazillions of uses for it but cant see them immediately myself!
Kahuna:
Hi Folks - looking for a simple method to export a foundset to an xls file.
It2be has a plugin that does exactly that.
Kahuna:
On a similar note I see the controller.printXML - I cant for the life of me see any but obscure reasons why printing to a document of XML would be useful - I would have thought a methods to send the XML to a file would have been more useful - I’m sure there’ll be gazillions of uses for it but cant see them immediately myself!
Print to xml does allow you to send to a file. It’s called print because it also takes your screen layout into the xml file, which actually can be very useful.
If you can use the example Robert suggested, this might also be a nice topic:
It’s an older thread by Bob Cusick, but it works fine. We extended it even more so we have the actual translated columnnames en columnsort order taken into account…
It’s an older thread by Bob Cusick, but it works fine. We extended it even more so we have the actual translated columnnames en columnsort order taken into account…
Thanks Marc - I did see that old post from Bob.
Unfortunately we dont run a servoy_repository in our dev machines so that technique would not work in that environment (though it may on a server?)
I was hoping that since that post from Bob Servoy may have incorporated a more simple method to write to an XLS (or odf tyope) file, it’s very standard and all our clients use it as an easy way to move data.
I’ll check-out Roberts technique and see if we can make that fit.
Kahuna:
Unfortunately we dont run a servoy_repository in our dev machines so that technique would not work in that environment (though it may on a server?)
We don’t either… but you don’t need the ‘repository’ part anymore.
I think at the time Bob was thinking of this, there was no other option to do this.
In 4.x you could easily extract this (&more) information using the solutionModel.
Kahuna:
Any air-code /samples / snippets you’d be prepared to share Marc?
As always
I’ll leave it in “Bob’s” order, so this is the code that’s getting all the details of the form.
function getExportFormFields()
{
//pass in the form name
var $form = arguments[0];
var $offset = 0;
if($form == undefined || !forms[$form]) return;
var $smForm = solutionModel.getForm($form);
if($smForm.extendsForm) $smForm = $smForm.extendsForm;
var $smHdrT = $smForm.getTitleHeaderPart();
var $smHdr = $smForm.getHeaderPart();
var $smBody = $smForm.getBodyPart();
var $smFields = $smForm.getFields();
var $smElements = new Array();
var $colTitles = new Array();
var $colNames = new Array();
var $vlNames = new Array();
var $colFormats = new Array();
var $vlname;
var $colFormat;
var $vPos;
var $eVisible;
var $bodyHeight = $smBody.height; // Caution: bodyheight is measured from top of form to bottom of body!!!
if($smHdrT != null) $offset = $offset + $smHdrT.height;
if($smHdr != null) $offset = $offset + $smHdr.height;
for (var i = 0; i < $smFields.length; i++)
{
$eVisible = forms[$form].elements[$smFields[i].name].visible;
$vPos = $smFields[i].y;
if ($eVisible && (($vPos > $offset && $vPos < $bodyHeight) || !(/_tbl/.test($form))))
{
$smElements[$smElements.length] = [$smFields[i]];
}
}
for (var i = 0; i < $smElements.length; i++)
{
$smElements[i][1] = forms[$form].elements[$smElements[i][0].name].getLocationX();
}
if(/_tbl/.test($form) && $smElements.length > 0)
{
$smElements.sort(function(a,b){return a[1] - b[1]});
}
for (var i = 0; i < $smElements.length; i++)
{
$col = $smElements[i][0].name;
$vlName = null;
$colFormat = null;
if($smElements[i][0].valuelist != null) $vlName = $smElements[i][0].valuelist.name;
if('format' in $smElements[i][0] && $smElements[i][0].format != null) $colFormat = $smElements[i][0].format;
$colNames[$colNames.length] = $smElements[i][0].dataProviderID;
$colTitles[$colTitles.length] = $smElements[i][0].text;
$vlNames[$vlNames.length] = $vlName;
$colFormats[$colFormats.length] = $colFormat;
}
return [$colNames, $vlNames, $colTitles, $colFormats];
}
And this is the code that should be called in the first place and uses the code above…
function exportExcel()
{
// get the form name
var $form = application.getMethodTriggerFormName();
var $table = forms[$form].controller.getTableName();
// get the names of the fields on the form
var $colProps = globals.getExportFormFields($form);
var $colList = $colProps[0];
var $colValueList = $colProps[1];
var $colTitles = $colProps[2];
var $colFormat = $colProps[3];
var $colVlPos = new Array();
var $colFPos = new Array();
var $dataset = databaseManager.convertToDataSet(forms[$form].foundset, $colList);
var $maxCols = $dataset.getMaxColumnIndex();
var $maxRow = $dataset.getMaxRowIndex();
for (var c = 1; c <= $maxCols; c++)
{
if($colValueList[c-1]) $colVlPos[$colVlPos.length] = c;
if($colFormat[c-1]) $colFPos[$colFPos.length] = c;
}
var $colNames = new Array();
var $colName;
for (var i = 0; i < $maxCols; i++)
{
$colName = i18n.getI18NMessage($colTitles[i])
if($colName == null || $colName == '' || /^!/.test($colName)) $colName = $colList[i];
$colNames[i] = $colName;
//application.output(i + ' ' + $colName)
}
if($maxCols > 0)
{
//output the column names first
var $output = '"' + $colNames.join('"\t"') + '"';
var $rowArray;
//loop through foundset of form and get all the columns and data
for ( var i = 1 ; i <= $maxRow ; i++ )
{
for (var c = 0; c < $colVlPos.length; c++)
{
var $orgVal = $dataset.getValue(i,$colVlPos[c]);
var $retVal = application.getValueListDisplayValue($colValueList[$colVlPos[c]-1], $orgVal);
$dataset.setValue(i, $colVlPos[c], $retVal);
}
for (var c = 0; c < $colFPos.length; c++)
{
var $orgVal = $dataset.getValue(i,$colFPos[c]);
var $format = $colFormat[$colFPos[c]-1];
if(/number/.test($format)) var $retVal = utils.numberFormat($orgVal, i18n.getI18NMessage($format))
else if(/date/.test($format) || /time/.test($format)) var $retVal = utils.dateFormat($orgVal, i18n.getI18NMessage($format))
else var $retVal = $orgVal
$dataset.setValue(i, $colFPos[c], $retVal);
}
$rowArray = $dataset.getRowAsArray(i);
$output += '\n"' + $rowArray.join('"\t"') + '"';
}
//prompt for the fileName
var $fileName = plugins.file.showFileSaveDialog('Export_' + $table + '.xls')
var $title = i18n.getI18NMessage('lbl.info');
var $button = i18n.getI18NMessage('lbl.ok');
var $msg;
//see if they chose an export name
if($fileName)
{
var $success = plugins.file.writeTXTFile($fileName, $output)
if($success)
{
$msg = i18n.getI18NMessage('servoy.plugin.export.success', [$maxRow]);
plugins.dialogs.showInfoDialog($title, $msg, $button);
}
else
{
$title = i18n.getI18NMessage('lbl.arg_error', [i18n.getI18NMessage('lbl.export')]);
$msg = i18n.getI18NMessage('dlg.error_exporting');
plugins.dialogs.showErrorDialog($title, $msg, $button);
}
}
else
{
$msg = i18n.getI18NMessage('dlg.action_canceled_by_user');
plugins.dialogs.showInfoDialog($title, $msg, $button);
}
}
}
Please do mind: as we have a multi-language system, we’re doing about everything (including nr/date Format) with ‘i18n’. You might have to rewrite these parts…
var _ds = databaseManager.convertToDataSet(foundset),
_sTxt = _ds.getAsText('\t','\r','"',false);
//
// now save _sTxt as an .xls file
//
It's not a full xls format but Excel opens it without the need to import.
Hope this helps.
Got that working nicely thanks Robert. I’ll use that for some of the quick and simple output some of our users need - primarily where they are going to use the data in other process. I’ll work on it (with Marc’s sample code) to get it running with our data dictionary for field name translation. Should make some nice output!