maxColumnIndex
Number  maxColumnIndex

Set the maximum column index to be read (default is unlimited = -1).

Returns
Number  index int

Supported Clients
SmartClient, WebClient, NGClient

maxRowIndex
Number  maxRowIndex

Set the maximum row index to be read (default is unlimited = -1)

Returns
Number  index int

Supported Clients
SmartClient, WebClient, NGClient

addSheet
Number  addSheet( IDWorkbook )

Add a sheet to the end of the workbook (returns index).

Parameters
IDWorkbook  workBook  the workbook

Returns
Number  index 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 workbook
Object  sheet  index:int or name:String

Returns
Number  index 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  set IDFormattedDataSet

Supported Clients
SmartClient, WebClient, NGClient

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 workbook
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, Object, Boolean )

Read the file and return a 2D Array for a set sheet.

Parameters
String  filePath  the file path
Object  sheet  index:int or name:String
Boolean  showHeader  the show header

Returns
Array<Array<Object>>  Object[][]

Supported Clients
SmartClient, WebClient, NGClient

Sample

// 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 for a set sheet. The showHeader parameter defaults to true.

Parameters
String  filePath  the file path
Object  sheet  index:int or name:String

Returns
Array<Array<Object>>  values Object[][]

Supported Clients
SmartClient, WebClient, NGClient

Sample

// 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 )

Read the file and return a 2D Array. The showHeader parameter defaults to true.

Parameters
String  filePath  the file path

Returns
Array<Array<Object>>  values Object[][]

Supported Clients
SmartClient, WebClient, NGClient

Sample

// 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 a cell value per WorkBook, sheet name or index, column name or index and row number (optional calculate formula parameter).

Parameters
IDWorkbook  workBook  the workbook
Object  sheet  index:int or name:String
Object  column  index:int or name:String
Number  row  the row
Boolean  calculateFormula  the formula

Returns
Object  Object

Supported Clients
SmartClient, WebClient, NGClient

getCellValue
Object  getCellValue( IDWorkbook, Object, Object, Number )

Get a cell value per WorkBook, sheet name or index, column name or index and row number (optional calculate formula parameter).

Parameters
IDWorkbook  workBook  the workbook
Object  sheet  index:int or name:String
Object  column  index:int or name:String
Number  row  the row

Returns
Object  Object

Supported Clients
SmartClient, WebClient, NGClient

getDataSet
JSDataSet  getDataSet( String, Object, Boolean )

Read the file and return a JSDataSet for a set sheet.

Parameters
String  filePath
Object  sheet  index:int or name:String
Boolean  showheader

Returns
JSDataSet  JSDataSet

Supported Clients
SmartClient, WebClient, NGClient

Sample

// Read the file and return a JSDataSet. The showHeader parameter defaults to false.
// 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 )

Read the file and return a JSDataSet for a set sheet. The showHeader parameter defaults to false.

Parameters
String  filePath
Object  sheet  index:int or name:String

Returns
JSDataSet  JSDataSet

Supported Clients
SmartClient, WebClient, NGClient

Sample

// Read the file and return a JSDataSet. The showHeader parameter defaults to false.
// 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 path

Returns
JSDataSet  set JSDataSet

Supported Clients
SmartClient, WebClient, NGClient

Sample

// Read the file and return a JSDataSet. The showHeader parameter defaults to false.
// 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");
}
}

getSheetMarginsInCentimiters
Array<Number>  getSheetMarginsInCentimiters( IDWorkbook, Object )

Get the margins of the sheet in cm

Parameters
IDWorkbook  workBook  the workbook
Object  sheet  index:int or name:String

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 workbook
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>  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 workbook
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 path

Returns
IDWorkbook  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 workbook
Object  sheet  index:int or name:String

Returns
Boolean  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 workbook
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 workbook
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 workbook
Object  sheet  index:int or name:String
Number  top  the top
Number  left  the left
Number  bottom  the bottom
Number  right  the right

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 workbook
Object  sheet  index:int or name:String
Number  top  the top
Number  left  the left
Number  bottom  the bottom
Number  right  the right

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 workbook
Object  sheet  index:int or name:String
String  sheetName  the new sheet name

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  sheetName  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

Returns
FormattedColor  color FormattedColor

Supported Clients
SmartClient, WebClient, NGClient

update
String  update( String, String, Array<Object>, Object, Object, Object, Number )

Update an existing Excel file

Parameters
String  filePath:String  the file path
String  outputFilePath:String  the output file path
Array<Object>  header:Object[]  the header
Object  set:Object  the JSDataSet or IDFormattedDataSet
Object  sheet:Object  index:int or name:String
Object  column:Object  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!
}
}
}

write
String  write( String, Array<Object>, Object, Boolean )

Write an Excel file

Parameters
String  filePath:String  the file name
Array<Object>  header:Object[]  the header
Object  set:Object  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 file

Parameters
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, String )

Write a workbook to a file

Parameters
IDWorkbook  workBook  the workbook
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!
}
}
}

writeWorkBook
Boolean  writeWorkBook( IDWorkbook )

Write an workbook to a file

Parameters
IDWorkbook  workBook  the workbook

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!
}
}
}