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

Dilcarina Duarte on February 6, 2025

Hello Community!

I need help with my code to create an excel file output with more than one table.

Take a look on my piece of code:

// ************************************************************************************************

var num = Context.getSelectedLanguage();
var oFile= Context.createOutputObject();

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();

oFile.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);
oFile.DefineF("HEAD2", getString("TEXT_1"), 10, Constants.C_BLACK, c_nBlue, Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);
oFile.DefineF("FORM", getString("TEXT_1"), 10, Constants.C_BLACK, c_nYellow, Constants.FMT_CENTER | Constants.FMT_VCENTER, 0, 0, 0, 0, 0, 1);
oFile.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];
oFile.BeginTable(100, Constants.C_BLACK, Constants.C_WHITE, Constants.FMT_LEFT, 0); // In Excel output format, this creates a new worksheet.
oFile.TableRow();
oFile.TableCellF("Funcion", 30, "HEAD");
oFile.TableCellF("Funcion", 30, "HEAD");
}
oFile.WriteReport();
}

// ************************************************************************************************

This creates an excel file with only one table "Table 1" (img_Actual_Output)

My question is, how can I have more tables (sheets)? (img_Desired_Output) - If you guys can tell me the functions will be great!

Also, the main function to add a model image to my excel... I tried aris help but without success xD

Thanks in advance!!

Regards,

Dilcarina

Attachments

img_Actual_Output.png
img_Desired_Output.png

Tags: #arisreport; #arisreportscripting; #arisscript; #excel

Notify Moderator

7 Replies

Michael Wieczorek on February 10, 2025

Hello,
you can create multiple tables via

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

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

BR, Michael

Like

Sign in
or register to reply.

Notify Moderator
Dilcarina Duarte on February 11, 2025

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

Like

Sign in
or register to reply.

Notify Moderator
KS

Kartik Sarangi on February 12, 2025

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

Like

Sign in
or register to reply.

Notify Moderator
Martin Schröder on February 12, 2025

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

Like

Sign in
or register to reply.

Notify Moderator
Dilcarina Duarte on February 13, 2025

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

Like

Sign in
or register to reply.

Notify Moderator
Martin Schröder on February 20, 2025

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

Like

Sign in
or register to reply.

Notify Moderator
Dilcarina Duarte on February 25, 2025

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

Like

Sign in
or register to reply.

Notify Moderator

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

7 Replies

ARIS Gebruikers Groep Nederland (AGNL) Theme session: Process Excellence

ARIS User Group DACH meeting