Hi,
I am trying to typecast the object to an excel format and add some hyperlinks. I am trying to get the output object after WriteReport(). Below are the code snippet.
var outObj= Context.createOutputObject();
//Creating a table in below method.
file.BeginTable(.......
file.DefineF("He.......
file.DefineF("He.......
file.DefineF("Co.......
file.DefineF("Re.......
file.TableRow();.......
//file.TableCell.......
file.TableCell(p.......
file.TableRow();
//file.TableCell.......
file.TableCell(".......
file.TableRow();
....
outObj.WriteReport();
//Then trying manipulate the data
var excelSheet = Context.getFile("sample.xls", Constants.LOCATION_OUTPUT);
Here I am getting null value for excelSheet .
Could you please guide me if I am missing anything.
You didn't really specify what you need the file for after you used the WriteReport method (manipulate?). If it's just about adding hyperlinks:
There is a far easier way to add hyperlinks. Any output you write (e.g. with OutputF) after creating a table cell will be written into that table cell until you either create a new row, add a new cell, or end the table.
So just use the TableCell (or TableCellF) method with an empty string as p_sText argument (the argument that specifies what ARIS should write into the cell after its creation), and directly after that use the OutputLink (or OutputLinkF) method to fill the table cell with content.
Example:
var o_Output = Context.createOutputObject(); o_Output.DefineF("Standard", "Arial", 11, Constants.C_BLACK, Constants.C_TRANSPARENT, Constants.FMT_LEFT, 0, 0, 0, 0, 0, 0); main(); o_Output.WriteReport(); function main(){ o_Output.BeginTable(100, Constants.C_BLACK, Constants.C_TRANSPARENT, Constants.FMT_LEFT, 0); o_Output.TableRow(); o_Output.TableCellF("", 50, "Standard"); o_Output.OutputLinkF("A link", "https://www.google.com", "Standard"); o_Output.TableCellF("", 50, "Standard"); o_Output.OutputLinkF("Another link", "https://www.softwareag.com", "Standard"); o_Output.TableRow(); o_Output.TableCellF("", 100, "Standard"); o_Output.OutputLinkF("A third link", "https://www.ariscommunity.com", "Standard"); o_Output.EndTable("Hello", 100, "Arial", 11, Constants.C_BLACK, Constants.C_TRANSPARENT, 0, Constants.FMT_LEFT, 0) }
Could you please help me to solve another issue (I would say part 2 of this issue) .
I just want to create a hyperlink the cell and on click it should open a tab in that excel document.
i.e in your solution
o_Output.OutputLinkF("A link", "https://www.google.com", "Standard");
instead of "https://www.google.com" it should open "MyTab" tab in the same excel document.
I created a tab with name "MyTab" in the excel sheet and tried "'" + "MyTab"+ "'!A1" in place of "https://www.google.com" but it didn't work.
o_Output.OutputLinkF("MyTab", "'" + "MyTab"+ "'!A1", "Standard");
It throws a exception and says "URI not absolute".
Could you please help..
Hmm, uh, this is not good. I'm getting the same error (using ARIS 10.0.3).
Furthermore I'm not only getting this error in the Script editor, where of course it could exist because we made a mistake, but also in the What-You-See-Is-What-You-Get editor. I should note though, that the WYSIWYG Editor creates a bookmark with StandardOutputObject.addLocalBookmark ( String id ) and then references the set id in the OutputLink/OutputLinkF method as a link destination. This is worrying because of course the WYSIWYG editor translates the stuff we design with the GUI into Javascript code in the way SoftwareAG said it will work - and it doesn't work.
So apparently it's a bug.
If you really need this functionality right now, you'll have to scrap the entire idea of using the default ARIS StandardOutputObject methods and instead create a Workbook. Originally only intended to be used for xls files, it seemingly also works with xlsx files.
var FILENAME = "result.xlsx"; var newWorkbook = Context.createExcelWorkbook(FILENAME); var sheetOne = newWorkbook.createSheet("MainSheet"); var rowOneOne = sheetOne.createRow(0); var cellOneOneOne = rowOneOne.createCell(0); cellOneOneOne.setCellValue("Some content"); var sheetTwo = newWorkbook.createSheet("SecondSheet"); var rowTwoOne = sheetTwo.createRow(0); var cellTwoOneOne = rowTwoOne.createCell(0); cellTwoOneOne.setHyperlink("A link", "MainSheet!A1"); newWorkbook.write();
Of course, as with all script code, all of this becomes easier and more useful if you iterate through arrays and create rows and cells for different data, and not just hard code two sheets like me, just trying to show you how to link stuff. Consult the ARIS script help to find out which methods are available for the different objects (sheet, row, cell,...). You can find them all from the start page of the script help ("Contents") > "ARIS Script" > "Methods for reports and semantic checks" > "Objects" > "Output object" > "Workbook".
Really sorry for any work you've already put into the original StandardOutputObject idea, maybe you can take some of the program logic and take that with you when you work on the Worksheet idea? Anyways, hope this helps.
Got the solution. Here is the complete sequence of code,
///Create the table.
file.BeginTable(.......
file.DefineF("He.......
file.DefineF("He.......
file.TableRow();.......
//file.TableCell.......
file.TableCell(p.......
file.TableRow();
//file.TableCell.......
file.TableCell(".......
file.TableRow();
....
....
//finish the table
file.WriteReport();
//getting the output file here
var document= Context.getFile(Context.getSelectedFile(), Constants.LOCATION_OUTPUT);
//converting the document to excel
var myWorkbook = Context.createExcelWorkbook(Context.getSelectedFile(), document);
var sheets = myWorkbook.getSheets();
var mainSheet = sheets[0];
var validRows = mainSheet.getRows();
var selectedCell = validRows[i].getCellAt(1);
var cellValue = selectedCell.getCellValue();
//got the tab name which we should link
var tabName = myWorkbook.getSheetName(2);
//set the hyperlink..
selectedCell.setHyperlink(cellValue,"'" + tabName + "'"+"!A1");
//write the excel to output
myWorkbook.write();