Excel 2010 Output, with new 2010 features

One Star

Excel 2010 Output, with new 2010 features

Hi,
it seems that when using tExcelOutput and appending to an existing file, with existing sheets, with preserving the formatting, an error occurs if the file was created with Excel 2010 and includes new 2010 features such as conditional formatting with negative data bars, or the new sparklines feature.
This is a big issue for me since I want to generate these fancy spreadsheets automatically with an ETL process - the spreadsheet is a weekly business report and makes heavy use of these new Excel 2010 features - there is no way to make it 2007-compatible.
Any idea of how I could solve this problem?
The error looks like this:
Exception in thread "main" java.lang.Error: java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTDxfs$1
at xxx_etl.wbpr_generate_0_1.wbpr_generate.tMysqlInput_1Process(wbpr_generate.java:664)
at xxx_etl.wbpr_generate_0_1.wbpr_generate.runJobInTOS(wbpr_generate.java:838)
at xxx_etl.wbpr_generate_0_1.wbpr_generate.main(wbpr_generate.java:709)
Caused by: java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTDxfs$1
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxfs.<clinit>(Unknown Source)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxfs$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.model.StylesTable.writeTo(StylesTable.java:362)
at org.apache.poi.xssf.model.StylesTable.commit(StylesTable.java:377)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:177)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:181)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:214)
M.
One Star

Re: Excel 2010 Output, with new 2010 features

Just found this:
https://issues.apache.org/bugzilla/show_bug.cgi?id=49370
This is the same bug; it suggests that a different OOXML Schema should be used, which includes a wider specification or something.
Can someone tell me what I have to do to change the schema? Do I need to recompile Talend, or not? In any case, a detailed explanation would be helpful, since I have no clue.
One Star

Re: Excel 2010 Output, with new 2010 features

I was referring to tFileOutputExcel of course, not tExcelOutput.
One Star

Re: Excel 2010 Output, with new 2010 features

Anyone?
One Star

Re: Excel 2010 Output, with new 2010 features

Update:
Indeed I was able to successfully use tFileOutputExcel now, in spite of all the new 2010 features used in my spreadsheet. I did my
- downloading "ooxml-schemas-1.0.jar" from http://mirrors.ibiblio.org/pub/mirrors/maven2//org/apache/poi/ooxml-schemas/1.0/
- copying it to <talend>/lib/java
- renaming "poi-ooxml-schemas-3.6-20091214.jar" in that folder to "poi-ooxml-schemas-3.6-20091214_backup.jar"
- renaming "ooxml-schemas-1.0.jar" to "poi-ooxml-schemas-3.6-20091214.jar"
So now the error is gone, HOWEVER when the Excel file is generated, the existing conditional formatting is not applied.
That is, even though the formatting rule is still there, but it is not applied until I open the spreadsheet, edit some cell value and press enter (once I do that, the formatting is reapplied only for that particular cell)
I'll try to look into the Excel XML schema itself to find out why this is.
One Star

Re: Excel 2010 Output, with new 2010 features

For those interested: I've given up using the tFileOutputExcel component, since it simply isn't able to deal with all the special formatting features.
Instead, I'm now trying to generate the XML sheets inside the XLSX archive myself, by using tAdvancedFileOutputXML.
This is very difficult, however, since although there is "advanced" in the component name, it's not advanced at all. For example, for an XML attribute, I have to choose either to give it a fixed string name, or a dynamic name from one of the stream fields. But what if I want to define an attribute as, say, a concatenation of multiple strings and numbers, conditional on some arbitrary field or variable taking on some specific value or range of values? What if I want to add a certain attribute to an element only if some Java expression is true? That's not possible.
So I have to generate the XML that way, then process it using multiple regular expressions, stripping out some things and adding others, because of the missing features of the XML component, then "paste" it into some prebuilt XML template which my Excel sheet is based on, then archive it using tArchive, rename to .XLSX, and it works.
It's incredibly complicated though, and it would probably be easier to just write my own script to do all these things, instead of using Talend.
Any comments by Talend on how they are going to solve the problem, or any easier way for me to do it? Would appreciate it.
One Star

Re: Excel 2010 Output, with new 2010 features

Thanks for your tip MS! Saved me some times and was enough for what I had to do Smiley Happy
Additionnaly, I had to update the jar in another location as it's regenerated on startup.
So file should be changed there instead:
plugins\org.talend.designer.components.localprovider_4.1.1.r50363\components\tFileInputExcel\
behd

Re: Excel 2010 Output, with new 2010 features

This post refer to Talend 4.1.1 and POI 3.6. Someone know if patch is required with version 5.0.2 there use POI 3.7 or if the issus is fixed? I quickly check the POI's history of change between 3.6 and 3.7 but I didn't see anythink about it.
Thx.