Export of all FMEDA results to Exel

Hi,

Well, others may object but I think there is no solution out of the box to export multiple FMEDA sheets into a single Excel (standard report).

Of course it possible to write a separate script that extracts DC data and writes them to a single Excel but that has to be done from scratch and cannot utilize any standard Excel report which you get when you export the table, I'm sorry.

Jan

medini_1023909254

Hi,

and also in addition the settings on the DC Cover Page (Metrics at Level, Include/Exclude Ports, Individual Metrics, Include/Exclude Transient Faults) have an significant impact on the FMEDA -taking care of all them probably makes the script even more difficult. Moreover, just deriving Excel without looking at these settings for each sheet may produce a wrong result.

Eckhardt

medini_1059007410

Hendrik's reply via e-mail (lacking direct internet access at the moment):

You are right, this is not a standard report available in medini. But why not?

Some years ago we got a script that was able to export all results from all DC-sheets in medini. It simply produced one line in Excel per DC-sheet. And that was simple and sufficient.

Now that variants can be handled inside the DC-sheet (and I started to use this feature), the script does not work anymore, since there are several lines of results in one DC-sheet and the script was not foreseen for that. (In the past I was using one DC-sheet per variant. And I am still using one DC-sheet per safety goal.)

Isn’t it possible to extend the existing script to also export as many lines as there are in one DC-sheet?

Best regards,

H. Ardner

Well, that sounds feasible. I am not aware of that script though so I cant tell for sure.

Jan

medini_1068027459

Hi,

i'd be willing to try and write a script that does at least a csv-Export, but i'm struggling to find where to access the data with the "Diagnose -> What is this?"-feature of the debug mode. I can find some data, but not data for all variants and safety goals that appear under "Metrics". Is there even a way to extract that data, or is access limited here?

Regards,

Lukas

I think this might be the proper topic.

I'm using the excel.js library to create a custom FMEDA report generator and for the most part it works well. The library is using the plugin de.ikv.medini.docgen.openxml and I have managed to add support for styles, merged cells, and image import into my script. See the attachment. Remember that is uses undocumented function calls and it might not work in the future if it is updated... :-)

For the styles to work it needs the style to be used at least once in the Excel file, otherwise it will result in a corrupted file.

So far so good, but I have found no way to put equations into Excel files. I guess it is a feature request from my side to ask for the <f> tag to be added to docgen. :-)

Regards,

Mattias

Hi Mattias,

Speaking from personal experience and couple of trials I made I would recommend to can use the apache.poi plugin (available in standard medini app since 24R2. , It offers a lot of more features to a Office documents ( not only excel)

There are many examples on the web, so take a looksie and play around with XSSFWorkbook.

create/load the sheet
cerate cell and set its formula
evaluate formula
write the Workbook object into the file

So in general feature request not necessary as you can directly use the classes form apache.poi plugin ( using bin to expose some of the classes)

Marian

Hendrik,

There is one solution that will however require BIRT designer plugin.

there is also a script around how to triggere reporting form the script.

The only complication is that you want ot have everything inside of a single workbook (goal/variant => separate sheet ) while respecting the current table editor layout.

Standard BIRT report templates are fixed with columns => disconnected from the current table editor layout state.

Some extra scripting and manipulation of the BIRT template need to be done to match the layout ..

In summary no out-of the box effortless solution available.

In any case if you really want the matching layout with the DC table editor it will require some coding to ( JS or BIRT)

Marian

Guys, a the request for POI based Excel handling is increasing it might be a good idea to start with a new excel.js (maybe poi.js?) library, what do you think?

Who can and want to contribute to an initial start?

Happy Scripting

Jan

PS: For the sake of completeness: the library that we are currently using in excel.js is the gradfather of the code that is now in POI; it basically was donated by the original author to the Apache project and became the code behind "XSSFWorkbook". We still stick to the much simpler library as POI adds another level of complexity that we dont need in most cases. Though POI is used by many internal features we have like BIRT or M2Doc. (updated)

Thank you for the advice. I will dig into the POI plugin with my trusted Java decompiler and see if I can port the code that I already have to it.: -)

Mattias

Managed to port my code and so far POI seems to be easier and more straight forward. :-) Much less likely to end up with a corrupt file too, it seems.

Mattias

Hi Mattias,

I had the same observation with POI.

PS; there are official POI pages including JavaDoc and several useful how to articles on POI.

Marian

Jan,

I have a question I noticed that there are actually 2x poi plugins available under /plugins

Id: org.apache.poi, Version: 4.1.2, Location: reference:file:plugins/org.apache.poi_4.1.2.jar
Id: org.apache.poi, Version: 5.2.3.202303241438, Location:reference:file:plugins/org.apache.poi_5.2.3.202303241438.jar

So which plugin version is used by JS bind function ? bind("org.apache.poi","org.apache.poi.xssf.usermodel.XSSFWorkbook",false)

I think it should not be a Problem to use 4 or 5, I was just curios how the "bind" wil the behave (class from plugin version 5 or 4)?

Marian

Here is a quick-and-dirty version of a basic poi.js library. Feel free to improve it and make it "official". :-)

Regards,

Mattias

Many thanks Mattias,

I have updated it bit

Wrapped classes + Methods inside of the "POI" object
fixed missing workbook object references in some methods
extended to create/open Word document
extended with EXCEL evaluateFormula(sheet,cell)

Marian

Great start guys, thanks for posting so quickly. I will review what you have proposed and add it to the official library post once a few tests are done.

@Marian: there are 2 POI versions because BIRT and M2Doc require different versiosn unfortunately. As our "bind" function doe snot accept/get any further details on the required version, I assume/think it would pick the latest available that would be 5.

In the first generation of our libs we basically just defined fucntions top level (as Mattias did). Example: ui.js. Later we scoped the fucntions by an object (as Marian did), example factory.js.

Even later we introduced the assighnment of a loaded content so one can use "var MyPOI = load("poi.js"). So the library basically returns the object but doe snot name it. Examples: annotation.js or reports.js.

I personally like the latets generation the most but wanted to clarify with you guys. Shall we make poi.js 1, second or 3. generation like?

Jan

Guys, no comment or oppinion? If not, I will go forward and use optino 3.

Will post a consolidated first version soon.

Jan

Jan
3rd gen is the preferred one.

Marian

Thanks Marian. Btw. one question: have you managed to do it "the 3rd way" in conjunction with JavaImporter() and bind? I mean, you proposed:

// Populate classes, through the JS Rhino JavaImporter's constructor
// Other classes can be added to the constructor or later using the JavaImporter's importClass(java.lang.Class)
var POI = JavaImporter(
	bind("org.apache.poi", "org.apache.poi.xssf.usermodel.XSSFWorkbook", false),
 ...
	bind("org.apache.poi", "org.apache.poi.xwpf.usermodel.XWPFDocument", false)
);

how does that translate if I have just an object I define:

({
 openWordFile: function(...) {}
})

Any help apreciated

Jan

JAn,

the JavaImporter does not handle the JS Objects ( Scriptable).

In fact the JavaImporter is a scriptable so one can use same approach as defintiion of of attributes for any JS Object.

Attaching some demo.

PS: I am not sure where you were aiming with the JavaImporter. Wheter the libfile expecting to return a JavaImporter (gen3.5) or the usage load extenral lib file (3rd gen) into a JavaImporter object. Anyways both things are demoed in my example.

Happy to have discussion to clarify.

Marian

Thanks Marian,

will check. I meanwhile solved it differently but the code looks ugly, so will check your proposals.

Jan

Hi guys, sorry, it was a bit silent the last days. Just a short update: I stumbled over a nice JavaScript construct which allows me to do very smooth usage of classes loaded via bind. And that the "uses" keyword (see with - JavaScript | MDN). Unfortunately it is marked deprecated. Anyway, just wante dto let you know what I tried:

var imports = JavaImporter(
	bind("org.apache.poi", "org.apache.poi.xssf.usermodel.XSSFWorkbook", false),
 .. more bindings
);

__POI.openExcelFile = function (path) {
	with (imports) {
		// "this" refers to the POI object ( JavaImporter classes + methods)
		var doc = null;
		try {
			var excelFile = new java.io.File(path);
			doc = new XSSFWorkbook(excelFile);
		} catch (e) { 
			// TODO handle error
			console.error(e);
		}
		return doc;
	}
};

as you can see, with the "with" keyword a can decouple the bound classes from the object/API definition, no need to have the classes as part of the scriptable. Anyway, not sure I shall continue this path.

Jan

Hi Jan,

I had same idea. here is my two cents:

1) "with" is apparently not allowed in strict mode, that is the reason why i throw away the "with" idea . Hovewer when testing it IRL there seem to be 2 strict modes:

pragma induced $STRICT_MODE$
script induced 'use strict';

The pragma induced strict mode does not complain about "with" at all while the script induced raises a script execution error

// $STRICT_MODE$ $WARNING_AS_ERROR$
//"use strict";//uncommenting will raise several errors: redeclarations, with
//also if warn as error pragma it will complain that code has no side effect 
var f = {
	a: "internal variable"
};
with (f) {
 console.log(a);
}
try {
	eval("'use strict';with(f){console.log(a);}");
} catch (e) {
	console.error(e);
}
var h=2 ;
//var h=1;//uncommented should cause redeclaration of h

2.) the importers, private functions outside of the library Scribtable

This is actually a good approach however one has to remember that when used via plain load those wil no longer be internal but a root script scope (top level) variables and will override anything in the user script.( importers variable, or any function in the user script)

No worries there is a trick to use the load to scope
in this way the importers and anything declared inside the library file will be visible only to the library object.

Marian