it2be - data plugin excel add sheet

Questions and answers on developing, deploying and using plugins and JavaBeans

it2be - data plugin excel add sheet

Postby matthias_suck » Sat Apr 28, 2018 10:46 am

Hello everybody,

I have the it2be data plugin and would like to attach a new sheet to an Excel workbook but no matter what I do I can not get it.

My code:
// create the Excel Object
if (tx == 0){
vExcel = plugins.it2be_data.createExcel( );
vWorkbook = vExcel.readWorkBook( vFile.getAbsolutePath( ) );
vExcel.setSheetName(a10);
tx = 1;
} else {
vExcel.addSheet(vWorkbook);
vExcel.setSheetName(a10)
}

During the first run, the Excel is generated (this is ok) and on the second pass a new sheet should be attached.

Maybe someone can help me, I'm at a loss.

Matthias Suck
matthias_suck
 
Posts: 10
Joined: Wed Mar 09, 2011 11:17 am

Re: it2be - data plugin excel add sheet

Postby Peter de Groot » Mon Apr 30, 2018 9:00 am

Matthias,

excel_before.png
excel_before.png (4.71 KiB) Viewed 3218 times


when I use the line below the new sheet is created
Code: Select all
      $excel.setSheetName($workbook,$excel.addSheet($workbook), "Test new sheet")


excel_after.png
excel_after.png (4.85 KiB) Viewed 3218 times


Regards,

Peter
User avatar
Peter de Groot
 
Posts: 215
Joined: Thu Jan 10, 2008 8:38 pm
Location: Not sure...

Re: it2be - data plugin excel add sheet

Postby matthias_suck » Mon Apr 30, 2018 10:12 am

Peter,

Thanks for your help.
Is not working.

Probably I have genrell somewhere a thought error.

Maybe you can help me with this.
I am a beginner with it2be - data, the instructions are unfortunately a bit poor.

Thanks in advance.
Matthias Suck

this is my complete Code:

/** @type {plugins.file.JSFile} */
var vFile;
/** @type {String} */
var vQuery, vOsName, vOutputPath;
/** @type {JSDataSet} */
var vDataSet;
/** @type {plugins.it2be_data.IDExcel} */
var vExcel;
/** @type {plugins.it2be_data.IDFormattedDataSet} */
var vSet;
/** @type {plugins.it2be_data.IDWorkbook} */
var vWorkbook;

var y = 0;
var t = 0;
var x = 0;
var z = 0;
var l = 0;
var a1 = [1];
var a10 = "";
var a13 = "";
var a12 = "";
var b11 = 0;
var b12 = 0;
var b13 = 0;
var b14 = 0;
var xid = 0;
var xtext2 = "";
var xdatum = new Date();

vFile = plugins.file.showFileSaveDialog( );
// Get the name of the operating system to know how to execute the,
// to the filetype attached,application
vOsName = application.getOSName( );

var x2=0;
if ( vFile.getAbsolutePath().length > 2 ) {
/** @type {JSFoundset<db:/fms_data/paswort>} */
var foundsetP3 = databaseManager.getFoundSet('fms_data', 'paswort');
foundsetP3.loadAllRecords();
foundsetP3.find()
foundsetP3.svnr = forms.Pers_ADM.b300;
foundsetP3.search()

var nRecordCount3 = 0;
nRecordCount3 = databaseManager.getFoundSetCount(foundsetP3);
foundsetP3.getRecord(nRecordCount3); // loads the max records

tx = 0;
for ( t = 1 ; t <= nRecordCount3 ; t ++ ) {
foundsetP3.setSelectedIndex(t);
if (foundsetP3.svnr == forms.Pers_ADM.b300 && (foundsetP3.pilot_st == 1 || foundsetP3.pilot_i ==1) && foundsetP3.fstatus ==0){
xid = foundsetP3.id_vnr
a12 = utils.stringTrim(foundsetP3.benutzer)
b11 = foundsetP3.pilot_st
b12 = foundsetP3.ext_p
b13 = foundsetP3.free_p
b14 = foundsetP3.pilot_i
xdatum = utils.dateFormat(foundsetP3.awadat,'dd.MM.yyyy')

xtext2 = foundsetP3.b1
if (utils.stringTrim(foundsetP3.inter_nr).length > 2){
xtext2 = foundsetP3.inter_nr
}
if (utils.stringTrim(foundsetP3.auswnr).length > 2){
xtext2 = foundsetP3.auswnr
}
if (utils.stringTrim(foundsetP3.stempel).length > 2){
xtext2 = foundsetP3.stempel
}
if (utils.stringTrim(foundsetP3.camo).length > 2){
xtext2 = foundsetP3.camo
}
if (utils.stringTrim(foundsetP3.b1).length > 2){
xtext2 = foundsetP3.b1
}
if (utils.stringTrim(foundsetP3.b2).length > 2){
xtext2 = foundsetP3.b2
}
xtext2=utils.stringTrim(xtext2)
a10 = utils.stringReplace(a12, ' ', '_')

// Get a dataset based on a query on the example (crm) database
// and it2be_companies/contacts tables
vQuery = "SELECT bez, genehm, mit_ub, ub FROM pilot_berech WHERE id_nr=" + xid + " AND flug_h =0 AND fstatus=0 ORDER BY pos ASC, bez ASC";
vDataSet = databaseManager.getDataSetByQuery( databaseManager.getDataSourceServerName( controller.getDataSource( ) ), vQuery, null, 1000 );

zx = 0;
// Stop executing this method when the dataset is empty
// and show an error dialog
if ( !vDataSet.getMaxRowIndex( ) ) {
plugins.dialogs.showErrorDialog( "Error...", "There are no records!", "OK" );
zx = 1;
}

// create the Excel Object
if (tx == 0){
vExcel = plugins.it2be_data.createExcel( );
vWorkbook = vExcel.readWorkBook( vFile.getAbsolutePath( ) );
vExcel.setSheetName(a10);
tx = 1;
} else {
vWorkbook = vExcel.readWorkBook( vFile.getAbsolutePath( ) );
vExcel.setSheetName(vWorkbook,vExcel.addSheet(vWorkbook), a10)
tx = tx+1;
x2 = x2+1;
application.output(a10)
}
// convert the DataSet to a FormattedDataSet
vSet = plugins.it2be_data.createExcel( ).convertDataSet( vDataSet );
// Add formatting properties to the FormattedDataSet
// IMPORTANT: when you have headers values are positioned row +1 (header)
vSet.setRowHeight( 1, 30 );
vSet.setRowHeight( 2, 25 );
vSet.setColumnWidth( 1, 50 );
vSet.setColumnWidth( 2, 40 );

vSet.clear(1,1)
vSet.clear(2,1)
vSet.clear(1,3)
vSet.clear(2,3)
vSet.clear(3,3)
a1 = [1]
a1[0] = " "
vSet.addRow(1,a1)
vSet.addRow(1,a1)
vSet.addRow(1,a1)

a13 = 'Pilotenberechtigungen' + ' - ' + a12 + ' , ' + xtext2;
vSet.setCellValue( 1, 2, a13 );

vSet.setCellBackground( 4, 1, plugins.it2be_data.COLOR.DARK_CYAN );
vSet.setCellBorder( 4, 1, plugins.it2be_data.BORDER.CELL_BORDER_MEDIUM, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( 4, 2, plugins.it2be_data.BORDER.CELL_BORDER_MEDIUM, plugins.it2be_data.COLOR.BLACK );
vSet.setCellAlignment( 4, 2, plugins.it2be_data.TEXTALIGNMENT.CENTER );
vSet.setCellBackground( 4, 2, plugins.it2be_data.COLOR.DARK_CYAN );
vSet.setCellFont( 4, 1, "Times,1,14", plugins.it2be_data.COLOR.ANTIQUE_WHITE );
vSet.setCellFont( 4, 2, vSet.createFont( plugins.it2be_data.FONT.ITALIC ) );
vSet.setCellFont( 4, 2, "Times,1,14", plugins.it2be_data.COLOR.ANTIQUE_WHITE );

x = 2;
y = 0;
for ( i = 1 ; i <= nRecordCount ; i ++ ) {
foundsetP2.setSelectedIndex(i);
if (foundsetP2.id_nr == forms.Pers_ADM.b200 && foundsetP2.klfdnr >= 1 && foundsetP2.genehm ==1 && foundsetP2.flug_h !=1 && foundsetP2.fstatus !=1){
vSet.setCellValue( 4, x, foundsetP2.bez );
vSet.setColumnWidth( x, 40 );
vSet.setCellBackground( 4, x, plugins.it2be_data.COLOR.DARK_CYAN );
vSet.setCellBorder( 4, x, plugins.it2be_data.BORDER.CELL_BORDER_MEDIUM, plugins.it2be_data.COLOR.BLACK );
vSet.setCellAlignment( 4, x, plugins.it2be_data.TEXTALIGNMENT.CENTER );
vSet.setCellFont( 4, x, vSet.createFont( plugins.it2be_data.FONT.ITALIC ) );
vSet.setCellFont( 4, x, "Times,1,14", plugins.it2be_data.COLOR.ANTIQUE_WHITE );
x = x+1;
y = y+1;
}
}

x = 5;
for ( i = 1 ; i < vDataSet.getMaxRowIndex(); i ++ ) {
if (vSet.getValue(x, 2) == "1"){
vSet.setCellValue( x, 2, "X" );
} else {
vSet.setCellValue( x, 2, "-" );
}
if (vSet.getValue(x,3) == "1"){
vSet.setCellValue( x, 2, "m.Ü." );
}
if (vSet.getValue(x,4) == "1"){
vSet.setCellFont( x, 1, vSet.createFont( plugins.it2be_data.FONT.BOLD ) );
}
vSet.setCellAlignment( x, 2, plugins.it2be_data.TEXTALIGNMENT.CENTER );
vSet.setCellBorder( x, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( x, 2, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setRowHeight( x, 15 );
vSet.clear(x,3)
x = x+1;
}
if (y >= 2){
z = 3;
for ( t = 1 ; t < y ; t ++ ) {
l = 5;
for ( i = 1 ; i < vDataSet.getMaxRowIndex(); i ++ ) {
if (vSet.getValue(l, 2) == "1"){
vSet.setCellValue( l, z, "X" );
} else {
vSet.setCellValue( l, z, "-" );
}

vSet.setCellAlignment( l, z, plugins.it2be_data.TEXTALIGNMENT.CENTER );
vSet.setCellBorder( l, z, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
l = l+1;
}
z = z+1;
}
}
if (y < 3){
l = 5;
for ( i = 1 ; i < vDataSet.getMaxRowIndex(); i ++ ) {
vSet.setCellValue( l, 4, "" );
l = l+1;
}
}
if (y < 4){
l = 5;
for ( i = 1 ; i < vDataSet.getMaxRowIndex(); i ++ ) {
vSet.setCellValue( l, 5, "" );
l = l+1;
}
}

vSet.freezePanes(5,1,vDataSet.getMaxRowIndex(),vDataSet.getMaxColumnIndex());

if (b11 ==1){
vSet.setCellValue( 2, 1, "Hauptamtlicher Pilot");
if (b14 ==1){
vSet.setCellValue( 2, 1, "Hauptamtlicher Pilot & Flight Instructor");
}
}
if (b12 ==1){
vSet.setCellValue( 2, 1, "Nebenamtlicher Pilot");
if (b14 ==1){
vSet.setCellValue( 2, 1, "Nebenamtlicher Pilot & Flight Instructor");
}
}
if (b13 ==1){
vSet.setCellValue( 2, 1, "Betriebsfremder Pilot");
if (b14 ==1){
vSet.setCellValue( 2, 1, "Betriebsfremder Pilot & Flight Instructor");
}
}
if (b14 ==1 && b11 == 0 && b12 ==0 && b13 ==0){
vSet.setCellValue( 2, 1, "Flight Instructor");
}
vSet.setCellValue( 2, 2, " Helikoptertypen / issue date: " + xdatum );

i=x+3
vSet.setRowHeight( i, 25 );
vSet.setCellBorder( i, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( i, 2, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellValue( i, 1, " Eingesehen am:" );
vSet.setCellValue( i, 2, " Status" );

i=x+4
vSet.setRowHeight( i, 25 );
vSet.setCellBorder( i, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( i, 2, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellValue( i, 1, " DPFO:" );

i=x+6
vSet.setCellBorder( i, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellValue( i, 1, " Pilot:" );

i=x+4
vSet.setCellBorder( i, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( i, 2, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellValue( i, 2, " X = zugelassen" );

i=x+5
vSet.setRowHeight( i, 25 );
vSet.setCellValue( i, 2, " m.Ü. = mit überwachung" );

i=x+6
vSet.setRowHeight( i, 25 );
vSet.setCellValue( i, 2, " - = nicht zugelassen" );

i=x+7
vSet.setCellBorder( i, 1, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );
vSet.setCellBorder( i, 2, plugins.it2be_data.BORDER.CELL_BORDER_THIN, plugins.it2be_data.COLOR.BLACK );

// A formula should be added like you would in MS Excel
// but with a comma as value separator
// don't forget to start with the '=' character'
vSet.setCellFormula( 18, 1, "=CONCATENATE(A7,\" \",B7)" );

// This is formatting the date value that we added in the beginning ( vSet.setValue(2, 2, new Date()) )
// vSet.setCellFormat(3, 2, vSet.FORMAT_DATETIME);

// Set formatting of a column
// vSet.setColumnFormat(2, vSet.FORMAT_DATETIME);

// write the file
if (tx == 1){
vOutputPath = vExcel.write( vFile.getAbsolutePath( ), null, vSet, 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 ( vOsName.indexOf( "Mac" ) > -1 ) {
// mac os x
application.executeProgram( "open", [vOutputPath] );
}
else if ( vOsName.indexOf( "Windows" ) > -1 ) {
// windows
application.executeProgram( "rundll32", ["url.dll,FileProtocolHandler", vOutputPath] );
}
else {
application.executeProgram( "xdg-open", ["filename"] );
}
} else{
vExcel.setSheetName(vWorkbook,vExcel.addSheet(vWorkbook), "Test new sheet")
filePath = vFile.getAbsoluteFile();
// plugins.it2be_data.IDExcel(vExcel.update(filePath, vSet));
// vExcel.update(vOutputPath, vSet, x2, 2, 3);
vExcel.writeWorkBook( vWorkbook );

if ( vOsName.indexOf( "Mac" ) > -1 ) {
// mac os x
application.executeProgram( "open", [vFile.getAbsolutePath( )] );
}
else if ( vOsName.indexOf( "Windows" ) > -1 ) {
// windows
// application.executeProgram( "rundll32", ["url.dll,FileProtocolHandler", vFile.getAbsolutePath( )] );
vExcel.writeWorkBook( vWorkbook );
}
else {
application.executeProgram( "xdg-open", ["filename"] );
}
}
}
}
}
matthias_suck
 
Posts: 10
Joined: Wed Mar 09, 2011 11:17 am

Re: it2be - data plugin excel add sheet

Postby Peter de Groot » Mon Apr 30, 2018 11:22 am

I think you need to select the sheet to be renamed, try this on a new created .xls or .xlsx file

Code: Select all
   var vFile = plugins.file.showFileOpenDialog();
   var vExcel   = plugins.it2be_data.createExcel();
   var vWorkbook = vExcel.readWorkBook( vFile);
   var vSheets = vExcel.getSheetNames(vWorkbook);
   
   var vSheet;
   
   /**LOOP EXISTING SHEETS*/
   for(var i = 0; i < vSheets.length ; i++){
      vSheet = vSheets[i];
      vExcel.setSheetName(vWorkbook, vSheet, 'a10-'+i);
      /**ADD YOUR DATA
       *
       *
       * */
   }
   
        vExcel.setSheetName(vWorkbook,vExcel.addSheet(vWorkbook), "new sheet")

   vExcel.writeWorkBook(vWorkbook,plugins.file.showFileSaveDialog().getAbsolutePath());


Regards,
Peter
User avatar
Peter de Groot
 
Posts: 215
Joined: Thu Jan 10, 2008 8:38 pm
Location: Not sure...

Re: it2be - data plugin excel add sheet

Postby matthias_suck » Wed May 02, 2018 5:09 pm

Many thanks.

is working fine.
My problem at the moment is that I do not put the datasets on the individual sheets.
The sheets are created by name, but I can not get it that the dataset comes on it.

Can you please help me with this?

Matthias Suck

My Code:
/** @type {String} */
var vQuery, vOutputPath, a10, a12;
/** @type {JSDataSet} */
var vDataSet;
/** @type {plugins.it2be_data.IDFormattedDataSet} */
var vSet;

var vFile = plugins.file.showFileOpenDialog();
var vExcel = plugins.it2be_data.createExcel();
var vWorkbook = vExcel.readWorkBook( vFile);

/** @type {JSFoundset<db:/fms_data/paswort>} */
var foundsetP1 = databaseManager.getFoundSet('fms_data', 'paswort');
foundsetP1.loadAllRecords();

foundsetP1.find()
foundsetP1.svnr = forms.Pers_ADM.b300;
foundsetP1.search()

var nRecordCount3 = 0;
nRecordCount3 = databaseManager.getFoundSetCount(foundsetP1);
foundsetP1.getRecord(nRecordCount3); // loads the max records

/**LOOP EXISTING SHEETS*/
var ti = 0;
for (var t = 1 ; t <= nRecordCount3 ; t ++ ) {
foundsetP1.setSelectedIndex(t);
if (foundsetP1.svnr == forms.Pers_ADM.b300 && (foundsetP1.pilot_st == 1 || foundsetP1.pilot_i ==1) && foundsetP1.fstatus ==0){
var xid = foundsetP1.id_vnr
a12 = utils.stringTrim(foundsetP1.benutzer)
a10 = utils.stringReplace(a12, ' ', '_')

vExcel.setSheetName(vWorkbook,vExcel.addSheet(vWorkbook), a10+t)

vQuery = "SELECT bez, genehm, mit_ub, ub FROM pilot_berech WHERE id_nr=" + xid + " AND flug_h =0 AND fstatus=0 ORDER BY pos ASC, bez ASC";
vDataSet = databaseManager.getDataSetByQuery( databaseManager.getDataSourceServerName( controller.getDataSource( ) ), vQuery, null, 1000 );

// convert the DataSet to a FormattedDataSet
vExcel.setSheetName(a10+t)
vSet = plugins.it2be_data.createExcel( ).convertDataSet( vDataSet );

// vExcel.setCellValue( vWorkbook, ti, 1, 10, "value 1" );

vOutputPath = vExcel.write( vFile.getAbsolutePath( ), null, vSet, true );
// vOutputPath = vExcel.update(vWorkbook, vDataSet, ti, 2, 3);
ti=ti+1
}
}

vExcel.setSheetName(vWorkbook,vExcel.addSheet(vWorkbook), "new sheet")

vExcel.writeWorkBook(vWorkbook,plugins.file.showFileSaveDialog().getAbsolutePath());
application.executeProgram( "rundll32", ["url.dll,FileProtocolHandler", vOutputPath] );
matthias_suck
 
Posts: 10
Joined: Wed Mar 09, 2011 11:17 am


Return to Plugins and Beans

Who is online

Users browsing this forum: No registered users and 10 guests