Servoy Properties Summary | |
---|---|
Number | maxColumnIndex |
Number | maxRowIndex |
Servoy Properties Details |
---|
maxColumnIndex
Number maxColumnIndex
Get/Set the maximum column index to be read (default is unlimited = -1).Returns
Number index int
Supported Clients
SmartClient, WebClient, NGClient
maxRowIndex
Number maxRowIndex
Get/Set the maximum row index to be read (default is unlimited = -1)Returns
Number index int
Supported Clients
SmartClient, WebClient, NGClient
Servoy Methods Details |
---|
addSheet
Number addSheet( IDWorkbook )
Add a sheet to the end of the workbook (returns index).Parameters
IDWorkbook workBook the work book
Returns
Number sheet int
Supported Clients
SmartClient, WebClient, NGClient
cloneSheet
Number cloneSheet( IDWorkbook, Object )
Clone a sheet at index (or with name) to the end of the workbook, returns index.Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Returns
Number sheet int
Supported Clients
SmartClient, WebClient, NGClient
convertDataSet
IDFormattedDataSet convertDataSet( JSDataSet )
Convert a regular JSDataSet to a Formatted DataSet.Parameters
JSDataSet dataSet the data set
Returns
IDFormattedDataSet dataset IDFormattedDataSet
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Convert a regular JSDataSet to a Formatted DataSet.
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, o.company_name, o.description FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// set the name of the file
}
var file = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the IDExcel object
var object = plugins.it2be_data.excel();
// convert the BEDataSet to a IDFormattedDataSet
var set = object.convertDataSet(dataset);
// add formatting properties to the IDFormattedDataSet
set.setCellBorder(1, 1, set.BORDER_MEDIUM, plugins.it2be_data.excelColor().GREEN);
set.setRowHeight(1, 30);
set.setColumnWidth(1, 50);
set.setColumnWidth(2, 50);
set.setCellAlignment(1, 1, set.ALIGN_RIGHT);
set.setCellAlignment(1, 2, set.ALIGN_CENTER);
set.setCellBackground(1, 3, plugins.it2be_data.excelColor().OLIVE);
set.setCellFont(1, 2, "Times,1,14", plugins.it2be_data.excelColor().BLUE);
set.setCellBorder(2, 3, set.BORDER_MEDIUM, plugins.it2be_data.excelColor().SILVER);
set.setCellBackground(2, 2, plugins.it2be_data.excelColor().BLUE);
set.setCellFont(2, 1, "Times,1,14", plugins.it2be_data.excelColor().YELLOW);
set.setCellFont(2, 2, set.createFont(set.FONT_ITALIC));
// A formula should be added equally formatted as in IDExcel
// but with a comma as value separator
// don't forget to start with the '=' character'
set.setCellFormula(18, 1, "=CONCATENATE(A7,\" \",B7)");
set.setValue(2, 2, new Date());
set.setValue(2, 3, 2.24);
set.setCellBackground(2, 4, plugins.it2be_data.excelColor().GREEN);
set.setCellBackground(3, 4, plugins.it2be_data.excelColor().PURPLE);
set.setCellBackground(4, 4, plugins.it2be_data.excelColor().LIME);
// write the file
filePath = object.write(filePath.getAbsolutePath(), set, true);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
freezePanes
void freezePanes( IDWorkbook, Object, Object, Number, Number, Number )
Freeze panes where freezedColumns represents visible columns in left pane and freezedRows represents visible rows in top pane.Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Object column index:int or name:String
Number row the row
Number freezedColumns the freezed columns
Number freezedRows the freezed rows
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
getArray
Array<Array<Object>> getArray( String )
Read the file and return a 2D Array. The showHeader parameter defaults to true.Parameters
String filePath the file name
Returns
Array<Array<Object>> array Object[][]
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getArray
Array<Array<Object>> getArray( String, Object, Boolean )
Read the file and return a 2D Array. The showHeader parameter defaults to true.Parameters
String filePath the file name
Object sheet index:int or name:String
Boolean showHeader the show header
Returns
Array<Array<Object>> array Object[][]
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getArray
Array<Array<Object>> getArray( String, Object )
Read the file and return a 2D Array. The showHeader parameter defaults to true.Parameters
String filePath the file name
Object value index:int or name:String or showHeader:boolean
Returns
Array<Array<Object>> array Object[][]
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getCellValue
Object getCellValue( IDWorkbook, Object, Object, Number, Boolean )
Get cell value per WorkBook, sheet name or index, column name or index and row number (optional calculate formula parameter).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Object column index:int or name:String
Number row the row
Boolean formula the formula
Returns
Object value Object
Supported Clients
SmartClient, WebClient, NGClient
getCellValue
Object getCellValue( IDWorkbook, Object, Object, Number )
Get cell value per WorkBook, sheet name or index, column name or index and row number (optional calculate formula parameter).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Object column index:int or name:String
Number row the row
Returns
Object value Object
Supported Clients
SmartClient, WebClient, NGClient
getDataSet
JSDataSet getDataSet( String, Object )
Read the file and return a JSDataSet. The showHeader parameter defaults to false.Parameters
String filePath the file name
Object value index:int or name:String or showHeader:boolean
Returns
JSDataSet set JSDataSet
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getDataSet
JSDataSet getDataSet( String )
Read the file and return a JSDataSet. The showHeader parameter defaults to false.Parameters
String filePath the file name
Returns
JSDataSet set JSDataSet
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getDataSet
JSDataSet getDataSet( String, Object, Boolean )
Read the file and return a JSDataSet. The showHeader parameter defaults to false.Parameters
String filePath the file name
Object sheet index:int or name:String
Boolean showHeader the show header
Returns
JSDataSet set JSDataSet
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read the file and return a 2D Array. The showHeader parameter defaults to true.
// set the name of the file
var file = plugins.it2be_data.showFileOpenDialog();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
var set = null;
var array = null;
var excel = plugins.it2be_data.excel();
// we can retrieve an array of sheet names and select one by name
var sheets = excel.getSheetNames(filePath);
// with the showHeader parameter set to true the first row will will show in the result
// create a dataset from the file
// the showHeader parameter is set to false by default
// REMARK: a header will always be constructed from the first row
set = excel.getDataSet(filePath, 0);
// create a two dimensional array from the file
// the showHeader parameter is set to true by default
array = excel.getArray(filePath, sheets[4], false);
// when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());
application.output("set.getMaxColumnIndex() = " + set.getMaxColumnIndex());
// because the set is a 'standard' JSDataSet all the functions and properties
// we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();
// limit the number of columns we show to 5 when columncount > 5
// 5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;
// loop through the columns
for (var i = 1; i <= columncount; i++) {
// retreive the names
message += set.getColumnName(i) + "::";
}
message += "\n";
// JSDataSet.sort(number, boolean) is also valid
set.sort(1, false);
var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();
// loop through the rows of the JSDataSet
// REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1; i <= rowcount; i++) {
// loop through the columns
for (var ii = 1; ii <= columncount; ii++) {
// and retreive the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("JSDataSet", message);
}
} else {
// output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
if (array) {
var message = "";
application.output("array.length = " + array.length);
// because the showHeader parameter is set to false with a (two dimensional) array
// the first row will NOT show in the array
// limit the column and row count for display purposes
var columncount = (array[0].length > 5) ? 5 : array[0].length;
var rowcount = (array.length > 5) ? 5 : array.length;
// loop through the 3D array
// REMARK: an array starts at index 0
for (var i = 0; i < rowcount; i++) {
for (var ii = 0; ii < columncount; ii++) {
message += array[i][ii] + "::";
}
message += "\n";
}
// show the result
if (message) {
plugins.it2be_data.showInfoDialog("Array", message);
}
} else {
// output to the debugger to show there is NO result
application.output("Array = null");
}
}
getSheetMarginsInCentimeters
Array<Number> getSheetMarginsInCentimeters( IDWorkbook, Object )
Get the sheet margins in centimetersParameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Returns
Array<Number> margins double[]
Supported Clients
SmartClient, WebClient, NGClient
getSheetMarginsInCentimiters
Array<Number> getSheetMarginsInCentimiters( IDWorkbook, Object )
Get the sheet margins in centimetersParameters
IDWorkbook workBook
Object sheet
Returns
Array<Number> margins double[]
Supported Clients
SmartClient, WebClient, NGClient
getSheetMarginsInInches
Array<Number> getSheetMarginsInInches( IDWorkbook, Object )
Get an array with the top, left, bottom, right print margins in inches (only for the selected worksheet).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Returns
Array<Number> double[]
Supported Clients
SmartClient, WebClient, NGClient
getSheetNames
Array<Object> getSheetNames( Object )
Retrieve an array of sheet names.Parameters
Object value filePath:String or workbook:IDWorkbook
Returns
Array<Object> names Object[]
Supported Clients
SmartClient, WebClient, NGClient
moveSheet
Boolean moveSheet( IDWorkbook, Number, Number )
Move a sheet at index to index, returns boolean.Parameters
IDWorkbook workBook the work book
Number oldIndex the old index
Number newIndex the new index
Returns
Boolean boolean
Supported Clients
SmartClient, WebClient, NGClient
readWorkBook
IDWorkbook readWorkBook( String )
Read an IDExcel file and return a WorkBook for use with getWorkBookCellData(.) and setWorkBookCellData(...).Parameters
String filePath the file name
Returns
IDWorkbook workbook IDWorkbook
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Read an IDExcel file and return a WorkBook for use with getWorkBookCellData(.) and setWorkBookCellData(...).
// get the name of the input file
var file = plugins.it2be_data.showFileOpenDialog(1);
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// retrieve a WorkBook
var excel = plugins.it2be_data.excel();
var workbook = excel.readWorkBook(filePath);
// write cell data using the sheet name, colum name and row number
// the below code will write a date value to cell 'D10'
// using the sheet index, colum index and row index
// REMARK: indexes always start at zero(0)
excel.setWorkBookCellData(workbook, 0, "D", 10, new Date());
// the below code will write a string value to cell 'K3'
excel.setWorkBookCellData(workbook, 0, 3, 10, "value 1");
// retrieve a value from the WorkBook cell at position 'A5'
// a non-existing cell will return a null value
var value = excel.getWorkBookCellData(workbook, 0, "A", 5);
application.output(value);
// and write the file back to the same file
excel.writeWorkBook(workbook);
// it is also possible to write the file back to a new file
// excel.writeWorkBook(workbook, "/newFileName.xls");
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
removeSheet
Boolean removeSheet( IDWorkbook, Object )
Remove a sheet at index (or with name), returns boolean.Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Returns
Boolean result boolean
Supported Clients
SmartClient, WebClient, NGClient
setCellValue
void setCellValue( IDWorkbook, Object, Object, Number, Object )
Set cell value per WorkBook, sheet name or index, column name or index and row number (optional override data type parameter).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Object column index:int or name:String
Number row the row
Object value the value
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setCellValue
void setCellValue( IDWorkbook, Object, Object, Number, Object, Object )
Set cell value per WorkBook, sheet name or index, column name or index and row number (optional override data type parameter).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Object column index:int or name:String
Number row the row
Object value the value
Object numberFormat the number format
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setSheetMarginsInCentimeters
void setSheetMarginsInCentimeters( IDWorkbook, Object, Number, Number, Number, Number )
Set the top, left, bottom, right print margins in centimeters (only for the selected worksheet, -1 is leave untouched).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Number top the top margin
Number left the left margin
Number bottom the bottom margin
Number right the right margin
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setSheetMarginsInInches
void setSheetMarginsInInches( IDWorkbook, Object, Number, Number, Number, Number )
Set the top, left, bottom, right print margins in inches (only for the selected worksheet, -1 is leave untouched).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
Number top the top margin
Number left the left margin
Number bottom the bottom margin
Number right the right margin
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setSheetName
void setSheetName( String )
Set the generic name of the sheet to write (do this before acually writing the sheet) or set the sheetName for a workbook sheet (by index or name).Parameters
String name the sheet name
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setSheetName
void setSheetName( IDWorkbook, Object, String )
Set the generic name of the sheet to write (do this before acually writing the sheet) or set the sheetName for a workbook sheet (by index or name).Parameters
IDWorkbook workBook the work book
Object sheet index:int or name:String
String name the sheet name
Returns
void
Supported Clients
SmartClient, WebClient, NGClient
setUserColor
FormattedColor setUserColor( Number, String )
Change the color at index 16-55 to the color you want (the LAST change is used).Parameters
Number index the index
String color the color string
Returns
FormattedColor color FormattedColor
Supported Clients
SmartClient, WebClient, NGClient
update
String update( String, Object )
Update an existing Excel fileParameters
String filePath the file path
Object set Object[] or JSDataSet or IDFormattedDataSet
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
update
String update( String, Array<Object>, Array<Array<Object>>, Object, Object, Number )
Update an existing Excel fileParameters
String filePath the file path
Array<Object> header:Object[] the header
Array<Array<Object>> set:Object[][] or JSDataSet or IDFormattedDataSet
Object sheet index:int or name:String
Object column index:int or name:String
Number row:int the row
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
update
String update( String, Array<Array<Object>>, Object )
Update an existing Excel fileParameters
String filePath the file path
Array<Array<Object>> set:Object[][] or JSDataSet or IDFormattedDataSet
Object sheet index:int or name:String
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
update
String update( String, String, Array<Object>, Array<Array<Object>>, Object, Object, Number )
Update an existing Excel fileParameters
String filePath the file path
String outputFilePath:String the output file path
Array<Object> header:Object[] the header
Array<Array<Object>> set:Object[][] or JSDataSet or IDFormattedDataSet
Object sheet index:int or name:String
Object column index:int or name:String
Number row:int the row
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
update
String update( String, Array<Array<Object>>, Object, Object, Number )
Update an existing Excel fileParameters
String filePath the file path
Array<Array<Object>> set:Object[][] or JSDataSet or IDFormattedDataSet
Object sheet index:int or name:String
Object column index:int or name:String
Number row:int the row
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
update
String update( String, Object, Number, Object )
Update an existing Excel fileParameters
String filePath the file path
Object column index:int or name:String
Number row:int the row
Object value3
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Update an existing Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, c.fax_direct, o.company_name FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// get the name of the input file
}
var file = plugins.it2be_data.showFileOpenDialog(1);
// it is possible to set a different output file name
// if not, the file will receive a sequence number
// var outputfile = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
var header = ["firstname", "lastname", "fax", "company"];
// we can use either a 2D array or a dataset for input
// in this example the dataset will be used
// set the input file and the dataset. default values are sheet index, column and row are 0 (zero)
outputfile = plugins.it2be_data.excel().update(filePath, dataset);
// set the input file, the dataset and the sheet index default values for column and row are 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0);
// set the input file, the dataset, column and row. default for the sheet index is 0 (zero)
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 2, 3);
// set the input file, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, dataset, 0, 2, 3);
// set the input file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, header, dataset, 0, 2, 3);
// set the input file, output file, header, the dataset, sheet index, column and row
// outputfile = plugins.it2be_data.excel().update(filePath, outputfile, header, dataset, 0, 2, 3);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", outputfile);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
write
String write( String, Array<Object>, JSDataSet, Boolean )
Write an Excel fileParameters
String filePath:String the file name
Array<Object> header:Object[] the header
JSDataSet set:JSDataSet or IDFormattedDataSet
Boolean overwrite:boolean the overwrite
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Write an Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, o.company_name, o.description FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// set the name of the file
}
var file = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
// it is also possible to create a 3 dimensional array (see example dbf)
// in that case the rest of the data is ignored when not creating a dbf file
var header = ["firstname", "lastname", "company", "description"];
// we can use either a 2D array or a dataset for input
// you would typically use a 2D array from data you can not retreive from the database
// in the below example we will create a 2D array from a dataset
// just to show you how to do it
var selection = new Array();
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
var row = dataset.getRowAsArray(i);
selection[i - 1] = row;
}
// write the file
// filePath = plugins.it2be_data.excel().writeArray(filePath, header, selection);
// we won't use the 'selection' array since a dataset can be used without creating an array
filePath = plugins.it2be_data.excel().write(filePath.getAbsolutePath(), dataset);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
writeArray
String writeArray( String, Array<Object>, Array<Array<Object>>, Boolean )
Write an an Array to a fileParameters
String filePath:String the file name
Array<Object> header:Object[] the header
Array<Array<Object>> array:Object[][] the array
Boolean overwrite:boolean the overwrite
Returns
String fileName String
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Write an Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, o.company_name, o.description FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// set the name of the file
}
var file = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
// it is also possible to create a 3 dimensional array (see example dbf)
// in that case the rest of the data is ignored when not creating a dbf file
var header = ["firstname", "lastname", "company", "description"];
// we can use either a 2D array or a dataset for input
// you would typically use a 2D array from data you can not retreive from the database
// in the below example we will create a 2D array from a dataset
// just to show you how to do it
var selection = new Array();
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
var row = dataset.getRowAsArray(i);
selection[i - 1] = row;
}
// write the file
// filePath = plugins.it2be_data.excel().writeArray(filePath, header, selection);
// we won't use the 'selection' array since a dataset can be used without creating an array
filePath = plugins.it2be_data.excel().write(filePath.getAbsolutePath(), dataset);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
writeWorkBook
Boolean writeWorkBook( IDWorkbook )
Write an workbook to a fileParameters
IDWorkbook workBook the work book
Returns
Boolean result boolean
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Write an Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, o.company_name, o.description FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// set the name of the file
}
var file = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
// it is also possible to create a 3 dimensional array (see example dbf)
// in that case the rest of the data is ignored when not creating a dbf file
var header = ["firstname", "lastname", "company", "description"];
// we can use either a 2D array or a dataset for input
// you would typically use a 2D array from data you can not retreive from the database
// in the below example we will create a 2D array from a dataset
// just to show you how to do it
var selection = new Array();
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
var row = dataset.getRowAsArray(i);
selection[i - 1] = row;
}
// write the file
// filePath = plugins.it2be_data.excel().writeArray(filePath, header, selection);
// we won't use the 'selection' array since a dataset can be used without creating an array
filePath = plugins.it2be_data.excel().write(filePath.getAbsolutePath(), dataset);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}
writeWorkBook
Boolean writeWorkBook( IDWorkbook, String )
Write a workbook to a fileParameters
IDWorkbook workBook the work book
String absolutePath the file path
Returns
Boolean result boolean
Supported Clients
SmartClient, WebClient, NGClient
Sample
// Write an Excel file
// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
var query = "SELECT c.firstname, c.lastname, o.company_name, o.description FROM it2be_contacts AS c, it2be_companies AS o WHERE c.it2be_companiesid=o.it2be_companiesid";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// stop executing this method when the dataset is empty
// and show an error dialog
if (!dataset.getMaxRowIndex()) {
plugins.it2be_data.showErrorDialog("Error.", "There are no records!", "OK");
Return;
// set the name of the file
}
var file = plugins.it2be_data.showFileSaveDialog();
// get the name of the operating system to know how to execute the,
// to the filetype attached,application
var osname = application.getOSName();
// check that we really selected a file
// if not ignore the rest of the code
if (file) {
var filePath = file.getAbsoluteFile();
// create the header as a 2 dimentional array
// it is also possible to create a 3 dimensional array (see example dbf)
// in that case the rest of the data is ignored when not creating a dbf file
var header = ["firstname", "lastname", "company", "description"];
// we can use either a 2D array or a dataset for input
// you would typically use a 2D array from data you can not retreive from the database
// in the below example we will create a 2D array from a dataset
// just to show you how to do it
var selection = new Array();
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
var row = dataset.getRowAsArray(i);
selection[i - 1] = row;
}
// write the file
// filePath = plugins.it2be_data.excel().writeArray(filePath, header, selection);
// we won't use the 'selection' array since a dataset can be used without creating an array
filePath = plugins.it2be_data.excel().write(filePath.getAbsolutePath(), dataset);
// open the data in the (to the filetype attached) application
// checking the os needs to be done via evaluation of a string
// you can also use the tools plugin that will give you an integer
if (osname.indexOf("Mac") > -1) {
// mac os x
application.executeProgram("open", filePath);
} else {
if (osname.indexOf("Windows") > -1) {
// windows
application.executeProgram("rundll32", "url.dll,FileProtocolHandler", filePath);
} else {
// linux etc. would love to know how to do this!
}
}
}