[Solved] ARIS Script (Report) - Export an excel file with many sheets

Hello,
you can create multiple tables via 

oFile.BeginTable(...)
....
oFile.EndTable(...)

oFile.BeginTable(...)
...
oFile.EndTable(...)
...

BR, Michael

Hello Michael,

I had already tried to do this, because supposedly this function 'BeginTable' - In Excel output format, creates a new worksheet. 
But I got an error "Start Table: You cannot start another table within a table in Excel". 
I then tried to use the 'EndTable' function, but it also gave an error

Regards,
Dilcarina
 

Hello Dilcarina,

Please try BeginSection() and EndSection(). 
p_output.BeginSection(false, Constants.SECTION_DEFAULT);
//table
p_output.EndSection();

If you need only excel output, then you should explore Excel API. 
Please refer https://ariscommunity.com/users/eva-klein/2010-08-24-output-picture-model-microsoft-excel for reference.

Regards,
Kartik Sarangi

Hello Dilcarina,

well, it's about how you define "table" in Excel. If you mean tables as named ranges of cells within a sheet, Begin/EndTable() does not help.

I am afraid the Aris Script API only knows how to create worksheets, so Michael's answer is right.

The error message "Start Table: You cannot start another table within a table in Excel" points to missing EndTable() statements, they cannot be nested, but must close a BeginTable() first.

Your code example only has a BeginTable() before the oFile.WriteReport();

Regards, Martin

Hello everyone,

I completely understand your arguments. I did the same before opening the post here. It makes perfect sense to me to close the table I started.
But the situation is that when I use the "EndTable" function I get the error: "Can't find method com...excel.AExcelGenerator.EndTable(...)" I also tried the 'BeginSection' and 'EndSection' functions but it doesn't do what I want... 

Regards,
Dilcarina

Hello Dilcarina,

without your code it's difficult to say but a common pitfall could be the different sequence and number of arguments for 
EndTable(String sFootText, int p_iWidthPercent, String p_Font, int p_iFontSize, int p_FontColor, int p_BkColor, int p_iShading, int p_Format, int p_iIndent)

Cf. script help /abs/help/en/script/ba/#/home/r_O_StandardOutputObject_endtable/en/1 on your server

sFootText sets the worksheet name, you would need to specify at least an empty string ""

Regards, Martin

Hello Martin!

Sorry for the delay.. You're right and I've already managed to solve it, I already have multiple sheets!!!
Thank you so much Martin!! And to everyone who commented!

There's the test code that gives me the desired output:

var c_nBlue = new java.awt.Color(0.5, 0.6, 0.9).getRGB();
var c_nGreen = new java.awt.Color(0.5, 0.9, 0.7).getRGB(); 
var c_nRed = new java.awt.Color(1, 0.3, 0.4).getRGB();
var c_nYellow = new java.awt.Color(1, 0.7, 0).getRGB();
var c_nDefaultWidth = 30; 
var c_nMaxCol = 254;

var num = Context.getSelectedLanguage();
var gs_Empty = Packages.java.lang.String( "" );
var g_oOutfile = Context.createOutputObject();

g_oOutfile.DefineF("HEAD", getString("TEXT_1"), 10, Constants.C_BLACK, c_nGreen, Constants.FMT_BOLD | Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);
g_oOutfile.DefineF("HEAD2", getString("TEXT_1"), 10, Constants.C_BLACK, c_nBlue, Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);
g_oOutfile.DefineF("FORM", getString("TEXT_1"), 10, Constants.C_BLACK, c_nYellow, Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);
g_oOutfile.DefineF("CELL", getString("TEXT_1"), 10, Constants.C_BLACK, Constants.C_TRANSPARENT, Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);

main();

function main(){
   
   var aSelModels = ArisData.getSelectedModels();
   if(aSelModels.length != 0){
       oModel = aSelModels[0];
       oModel.BuildGraph(true);
       
       var workbook = Context.createExcelWorkbook(Context.getSelectedFile());
       oModel.BuildGraph(true);
       
       var pic = oModel.Graphic(false,false,num);
       
       var gcmw = g_oOutfile.getCurrentMaxWidth();
       var gph = g_oOutfile.GetPageHeight() - 5;
       var gtm = g_oOutfile.GetTopMargin();
       var gbm = g_oOutfile.GetBottomMargin();
       
       g_oOutfile.BeginTable(100, Constants.C_BLACK, Constants.C_WHITE, Constants.FMT_LEFT, 0); // In Excel output format, this creates a new worksheet.
       g_oOutfile.TableRow();
       g_oOutfile.TableCellF("", 30, "CELL");
       g_oOutfile.OutGraphic(pic, -1, g_oOutfile.getCurrentMaxWidth(),gph- g_oOutfile.GetTopMargin() -g_oOutfile.GetBottomMargin() -10);
       g_oOutfile.EndTable("first",100, "Arial",10,Constants.C_BLACK,Constants.C_BLACK,0,Constants.FMT_LEFT | Constants.FMT_ITALIC,0);
       
       g_oOutfile.BeginTable(100, Constants.C_BLACK, Constants.C_WHITE, Constants.FMT_LEFT, 0); // In Excel output format, this creates a new worksheet.
       g_oOutfile.TableRow();
       g_oOutfile.TableCellF("0", 30, "HEAD");
       g_oOutfile.TableCellF("1", 30, "CELL");
       g_oOutfile.TableCellF("2", 30, "HEAD");
       g_oOutfile.TableCellF("3", 30, "CELL");
       g_oOutfile.TableCellF("4", 30,"HEAD");
       g_oOutfile.TableRow();
       g_oOutfile.EndTable("second",100, "Arial",10,Constants.C_BLACK,Constants.C_BLACK,0,Constants.FMT_LEFT | Constants.FMT_ITALIC,0);
       workbook.write();
       
   }
   g_oOutfile.WriteReport();
}
 

Regards,
Dilcarina