This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new ac9ba58c82 ODS support in Excel Writer transform. fixes #7191 (#7198)
ac9ba58c82 is described below
commit ac9ba58c82817f5d6d3b16d391235cfa05b126e8
Author: Bart Maertens <[email protected]>
AuthorDate: Tue Jun 2 12:07:54 2026 +0000
ODS support in Excel Writer transform. fixes #7191 (#7198)
* ODS support in Excel Writer transform. fixes #7191
* reverted translation updates. #7191
---
.../pages/pipeline/transforms/excelwriter.adoc | 69 ++-
.../transforms/0101-check-ods-file-exists.hpl | 175 ++++++
.../transforms/0101-create-ods-file.hpl | 153 +++++
.../main-0101-excel-writer-output-ods.hwf | 187 ++++++
.../excelwriter/ExcelWriterOutputFormat.java | 39 ++
.../excelwriter/ExcelWriterTransform.java | 40 +-
.../excelwriter/ExcelWriterTransformDialog.java | 29 +-
.../excelwriter/ExcelWriterWorkbookDefinition.java | 45 ++
.../transforms/excelwriter/ods/OdsExcelWriter.java | 628 +++++++++++++++++++++
.../excelwriter/ods/OdsFormatConverter.java | 39 ++
.../excelwriter/ods/OdsFormulaConverter.java | 96 ++++
.../excelwriter/ods/OdsFormulaHelper.java | 75 +++
.../transforms/excelwriter/ods/OdsStyleHelper.java | 155 +++++
.../transforms/excelwriter/ods/OdsTableHelper.java | 179 ++++++
.../excelwriter/ods/OdsWorkbookHandle.java | 45 ++
.../excelwriter/messages/messages_en_US.properties | 15 +-
.../ods/OdsExcelWriterIntegrationTest.java | 217 +++++++
.../excelwriter/ods/OdsExcelWriterTest.java | 480 ++++++++++++++++
.../excelwriter/ods/OdsFormatConverterTest.java | 30 +
.../excelwriter/ods/OdsFormulaConverterTest.java | 45 ++
20 files changed, 2705 insertions(+), 36 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/excelwriter.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/excelwriter.adoc
index b1ee906e60..dc8d709a3e 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/excelwriter.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/excelwriter.adoc
@@ -16,7 +16,7 @@ under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
-:description: The Microsoft Excel Writer transform writes incoming rows from
Hop out to an MS Excel file. It supports both the .xls and .xlsx file formats.
+:description: The Excel Writer transform writes incoming rows to Microsoft
Excel (.xls, .xlsx) or OpenDocument Spreadsheet (.ods) files.
= image:transforms/icons/excelwriter.svg[Excel writer transform Icon,
role="image-doc-icon"] Excel writer
@@ -25,9 +25,17 @@ under the License.
|
== Description
-The Microsoft Excel Writer transform writes incoming rows from Hop out to an
MS Excel file. It supports both the .xls and .xlsx file formats.
+The Excel Writer transform writes incoming rows from Hop to spreadsheet files.
+It supports three output formats:
-The .xls files use a binary format which is better suited for simple content,
while the .xlsx files use the Open XML format which works well with templates
since it can better preserve charts and miscellaneous objects.
+* `.xls` — legacy Excel binary format (Apache POI)
+* `.xlsx` — modern Excel Open XML format (Apache POI)
+* `.ods` — OpenDocument Spreadsheet format (LibreOffice Calc, Apache
OpenOffice; written via ODFDOM)
+
+The `.xls` and `.xlsx` backends share the same POI code path.
+The `.ods` backend is a separate implementation that mirrors the same
transform options where the ODF format allows it.
+
+The `.xls` files use a binary format which is better suited for simple
content, while the `.xlsx` files use the Open XML format which works well with
templates since it can better preserve charts and miscellaneous objects.
|
== Supported Engines
@@ -40,6 +48,16 @@ The .xls files use a binary format which is better suited
for simple content, wh
!===
|===
+== Output formats
+
+[options="header"]
+|===
+|Format|Extension|Backend|Notes
+|Excel 97–2003|`.xls`|POI|Sheet password protection supported
+|Excel 2007+|`.xlsx`|POI|Streaming mode for large files; no sheet password
protection
+|OpenDocument Spreadsheet|`.ods`|ODFDOM|LibreOffice Calc compatible; see
<<ods-limitations>> below
+|===
+
== Options
=== File & sheet tab
@@ -49,11 +67,11 @@ The .xls files use a binary format which is better suited
for simple content, wh
[options="header"]
|===
|Option|Description
-|Stream XLSX data|Check this option when writing large XLSX files.
+|Extension|Choose `xls`, `xlsx`, or `ods`. This determines the output file
format.
+|Stream XLSX data|Check this option when writing large XLSX files (not
available for `.xls` or `.ods`).
It uses internally a streaming API and is able to write large files without
any memory restrictions (of course not exceeding Excel's limit of 1,048,575
rows and 16,384 columns).
|Create parent folder|Enable to create the parent folder
-|If output file exists|Check this option when writing large XLSX files.
-It uses internally a streaming API and is able to write large files without
any memory restrictions (of course not exceeding Excel's limit of 1,048,575
rows and 16,384 columns).
+|If output file exists|Choose to reuse an existing file or create a new one.
|Add filename(s) to result|Check to have the filename added to the result
filenames
|Wait for first row before creating file|Checking this option makes the
transform create the file only after it has seen a row.
If this is disabled the output file is always created, regardless of whether
rows are actually written to the file.
@@ -65,17 +83,15 @@ If this is disabled the output file is always created,
regardless of whether row
|===
|Option|Description
|Sheet Name|The sheet name the transform will write rows to.
-|Make this the active sheet|If checked the Excel file will by default open on
the above sheet when opened in MS Excel.
+|Make this the active sheet|If checked the spreadsheet file will open on this
sheet by default (in Excel, LibreOffice Calc, etc.).
|If sheet exists in output file|The output file already has this sheet (for
example when using a template, or writing to existing files), you can choose to
write to the existing sheet, or replace it.
-|Protect Sheet|The XLS file format allows to protect an entire sheet from
changes.
-If checked you need to provide a password.
-Excel will indicate that the sheet was protected by the user you provide here.
+|Protect Sheet|Lock the sheet with an optional password. Supported for `.xls`
and `.ods` output. The *protected by user* field applies to `.xls` only. Not
supported for `.xlsx`.
|===
*Template section*
When creating new files (when existing files are replaced, or completely fresh
files are created) you may choose to create a copy of an existing template file
instead.
-Please make sure that the template file is of the same type as the output file
(both must be xls or xlsx respectively).
+The template and output file must use the same extension (`.xls`, `.xlsx`, or
`.ods`).
When creating new sheets, the transform may copy a sheet from the current
document (the template or an otherwise existing file the transform is writing
to).
A new sheet is created if the target sheet is not present, or the existing one
shall be replaced as per configuration above.
@@ -92,7 +108,7 @@ A new sheet is created if the target sheet is not present,
or the existing one s
|Write Header|If checked the first line written will contain the field names
|Write Footer|If checked the last line written will contains the field names
|Auto Size Columns|If checked the transform tries to automatically size the
columns to fit their content.
-Since this is not a feature the xls(x) file formats support directly, results
may vary.
+For `.xls`/`.xlsx` this is approximated by POI; for `.ods` the OpenDocument
*optimal column width* flag is set.
|Force formula recalculation a|If checked, the transform tries to make sure
all formula fields in the output file are updated.
* The xls file format supports a "dirty" flag that the transform sets.
@@ -100,6 +116,7 @@ The formulas are recalculated as soon as the file is opened
in MS Excel.
* For the xlsx file format, the transform must try to recalculate the formula
fields itself.
Since the underlying POI library does not support the full set of Excel
formulas yet, this may give errors.
The transform will throw errors if it cannot recalculate the formulas.
+* For `.ods`, formula results are cleared before save so Calc recalculates on
open. Hop does not evaluate ODF formulas at write time.
|Leave styles of existing cells unchanged|If checked, the transform will not
try to set the style of existing cells it is writing to.
This is useful when writing to pre-styled template sheets.
|===
@@ -112,9 +129,8 @@ This is useful when writing to pre-styled template sheets.
|Start writing at end of sheet|The transform will try to find the last line of
the sheet, and start writing from there.
|Offset by ... rows|Any non-0 number will cause the transform to move this
amount of rows down (positive numbers) or up (negative numbers) before writing
rows.
Negative numbers may be useful if you need to append to a sheet, but still
preserve a pre-styled footer.
-|Begin by writing ... empty lines|The transform will try to find the last line
of the sheet, and start writing from there.
-|Omit Header|Any non-0 number will cause the transform to move this amount of
rows down (positive numbers) or up (negative numbers) before writing rows.
-Negative numbers may be useful if you need to append to a sheet, but still
preserve a pre-styled footer.
+|Begin by writing ... empty lines|When *shift existing cells down* is
selected, empty rows are inserted at the write position instead of simply
skipping ahead.
+|Omit Header|Skip the header row when appending to an existing sheet.
|===
*Fields section*
@@ -135,12 +151,29 @@ The `ignore manual fields` ignores any fields manually
defined in the transform'
|Format|The Excel format to use in the sheet.
Please consult the Excel manual for valid formats.
There are some online references as well.
+For `.ods`, common Excel format tokens are converted to OpenDocument
equivalents where possible.
|Style from cell|A cell (i.e. A1, B3 etc.) to copy the styling from for this
column (usually some pre-styled cell in a template)
|Field Title|If set, this is used for the Header/Footer instead of the Hop
field name
|Header/Footer style from cell|A cell to copy the styling from for
headers/footers (usually some pre-styled cell in a template)
-|Field Contains Formula|Set to Yes, if the field contains an Excel formula (no
leading '=')
+|Field Contains Formula|Set to Yes, if the field contains an Excel formula (no
leading '=').
+For `.ods`, Excel-style formulas are converted to OpenFormula syntax on a
best-effort basis.
|Hyperlink|A field, that contains the target to link to.
The supported targets are Link to other cells, http, ftp, email, and local
documents
-|Cell Comment / Cell Author|The xlsx format allows to put comments on cells.
-If you'd like to generate comments, you may specify fields holding the comment
and author for a given column.
+|Cell Comment / Cell Author|Comments are written for `.xlsx` and `.ods`
(OpenDocument annotations).
+Excel may not display ODS annotations; LibreOffice Calc does.
|===
+
+[[ods-limitations]]
+== ODS output notes and limitations
+
+The `.ods` backend supports the same transform dialog options as
`.xls`/`.xlsx` wherever the OpenDocument format allows.
+Known differences:
+
+* *Formulas* — Excel syntax is converted to OpenFormula (`of:=...`). Complex
Excel-only functions may not translate. Formula results are not calculated by
Hop; LibreOffice Calc recalculates when the file is opened.
+* *Comments* — Stored as ODF annotations. Visible in LibreOffice Calc;
Microsoft Excel may ignore them in `.ods` files.
+* *Hyperlinks* — Stored as ODF `text:a` elements.
+* *Format masks* — Excel format strings are mapped to ODF number formats on a
best-effort basis.
+* *Style copy* — Copies the referenced cell's style name, not a full POI cell
style object.
+* *Sheet protection* — Uses ODF `table:protected` with SHA-1 password hash
(LibreOffice Calc compatible). The *protected by user* field is not used for
`.ods`.
+* *Streaming* — The *Stream XLSX data* option applies to `.xlsx` only.
+* *Sheet names* — The 31-character Excel sheet name limit is not enforced for
`.ods`.
diff --git a/integration-tests/transforms/0101-check-ods-file-exists.hpl
b/integration-tests/transforms/0101-check-ods-file-exists.hpl
new file mode 100644
index 0000000000..93c7a8e0d8
--- /dev/null
+++ b/integration-tests/transforms/0101-check-ods-file-exists.hpl
@@ -0,0 +1,175 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0101-check-ods-file-exists</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/05/29 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/05/29 12:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>File exists!</from>
+ <to>Cleanup temporary file</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Look for test ODS file</from>
+ <to>Detect empty stream</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Detect empty stream</from>
+ <to>Abort because expected file not found!</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Look for test ODS file</from>
+ <to>File exists!</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Abort because expected file not found!</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <message>Expected ODS file not found!</message>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>336</xloc>
+ <yloc>368</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Cleanup temporary file</name>
+ <type>ProcessFiles</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <addresultfilenames>N</addresultfilenames>
+ <createparentfolder>N</createparentfolder>
+ <operation_type>delete</operation_type>
+ <overwritetargetfile>N</overwritetargetfile>
+ <simulate>N</simulate>
+ <sourcefilenamefield>filename</sourcefilenamefield>
+ <attributes/>
+ <GUI>
+ <xloc>720</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Detect empty stream</name>
+ <type>DetectEmptyStream</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>336</xloc>
+ <yloc>256</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>File exists!</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>560</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Look for test ODS file</name>
+ <type>GetFileNames</type>
+ <description/>
+ <distribute>N</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <doNotFailIfNoFile>N</doNotFailIfNoFile>
+ <dynamic_include_subfolders>N</dynamic_include_subfolders>
+ <file>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ <name>${PROJECT_HOME}/files/excel/temp-ods-output.ods</name>
+ </file>
+ <filefield>N</filefield>
+ <filter>
+ <filterfiletype>all_files</filterfiletype>
+ </filter>
+ <isaddresult>Y</isaddresult>
+ <limit>0</limit>
+ <raiseAnExceptionIfNoFile>N</raiseAnExceptionIfNoFile>
+ <rownum>N</rownum>
+ <attributes/>
+ <GUI>
+ <xloc>336</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/0101-create-ods-file.hpl
b/integration-tests/transforms/0101-create-ods-file.hpl
new file mode 100644
index 0000000000..55ec0ba603
--- /dev/null
+++ b/integration-tests/transforms/0101-create-ods-file.hpl
@@ -0,0 +1,153 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0101-create-ods-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/05/29 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/05/29 12:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>Write test ODS file</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Data grid</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <data>
+ <line>
+ <item>A</item>
+ </line>
+ </data>
+ <fields>
+ <field>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <name>f1</name>
+ <type>String</type>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>256</xloc>
+ <yloc>192</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Write test ODS file</name>
+ <type>TypeExitExcelWriterTransform</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <appendEmpty>0</appendEmpty>
+ <appendLines>N</appendLines>
+ <appendOffset>0</appendOffset>
+ <appendOmitHeader>N</appendOmitHeader>
+ <fields>
+ <field>
+ <commentAuthorField/>
+ <commentField/>
+ <format/>
+ <formula>N</formula>
+ <hyperlinkField/>
+ <name>f1</name>
+ <styleCell/>
+ <title>f1</title>
+ <titleStyleCell/>
+ <type>String</type>
+ </field>
+ </fields>
+ <file>
+ <SpecifyFormat>N</SpecifyFormat>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <autosizecolums>N</autosizecolums>
+ <createParentFolder>Y</createParentFolder>
+ <date_time_format/>
+ <do_not_open_newfile_init>Y</do_not_open_newfile_init>
+ <extension>ods</extension>
+ <filename_field/>
+ <filename_in_field>N</filename_in_field>
+ <if_file_exists>new</if_file_exists>
+ <if_sheet_exists>new</if_sheet_exists>
+ <name>${PROJECT_HOME}/files/excel/temp-ods-output</name>
+ <password/>
+ <protect_sheet>N</protect_sheet>
+ <protected_by/>
+ <sheetname>Sheet1</sheetname>
+ <split>N</split>
+ <splitevery>0</splitevery>
+ <stream_data>N</stream_data>
+ </file>
+ <footer>N</footer>
+ <forceFormulaRecalculation>N</forceFormulaRecalculation>
+ <header>Y</header>
+ <leaveExistingStylesUnchanged>N</leaveExistingStylesUnchanged>
+ <makeSheetActive>Y</makeSheetActive>
+ <rowWritingMethod>overwrite</rowWritingMethod>
+ <startingCell>A1</startingCell>
+ <template>
+ <enabled>N</enabled>
+ <filename>template.xls</filename>
+ <hidden>N</hidden>
+ <sheet_enabled>N</sheet_enabled>
+ <sheetname/>
+ </template>
+ <attributes/>
+ <GUI>
+ <xloc>448</xloc>
+ <yloc>192</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/main-0101-excel-writer-output-ods.hwf
b/integration-tests/transforms/main-0101-excel-writer-output-ods.hwf
new file mode 100644
index 0000000000..8d91574fd4
--- /dev/null
+++ b/integration-tests/transforms/main-0101-excel-writer-output-ods.hwf
@@ -0,0 +1,187 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0101-excel-writer-output-ods</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Verify Excel Writer transform can write a basic ODS file
(OpenDocument Spreadsheet).</description>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/05/29 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/05/29 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>208</xloc>
+ <yloc>96</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0101-create-ods-file.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <filename>${PROJECT_HOME}/0101-create-ods-file.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>368</xloc>
+ <yloc>96</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0101-check-ods-file-exists.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <filename>${PROJECT_HOME}/0101-check-ods-file-exists.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>592</xloc>
+ <yloc>96</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Dummy</name>
+ <description/>
+ <type>DUMMY</type>
+ <attributes/>
+ <parallel>N</parallel>
+ <xloc>464</xloc>
+ <yloc>208</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Success</name>
+ <description/>
+ <type>SUCCESS</type>
+ <attributes/>
+ <parallel>N</parallel>
+ <xloc>752</xloc>
+ <yloc>96</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Error</name>
+ <description/>
+ <type>ABORT</type>
+ <attributes/>
+ <always_log_rows>N</always_log_rows>
+ <message>Something wrong happened!</message>
+ <parallel>N</parallel>
+ <xloc>464</xloc>
+ <yloc>320</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>0101-create-ods-file.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>0101-create-ods-file.hpl</from>
+ <to>0101-check-ods-file-exists.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0101-create-ods-file.hpl</from>
+ <to>Dummy</to>
+ <enabled>Y</enabled>
+ <evaluation>N</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0101-check-ods-file-exists.hpl</from>
+ <to>Dummy</to>
+ <enabled>Y</enabled>
+ <evaluation>N</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>Dummy</from>
+ <to>Error</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>0101-check-ods-file-exists.hpl</from>
+ <to>Success</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterOutputFormat.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterOutputFormat.java
new file mode 100644
index 0000000000..469a471931
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterOutputFormat.java
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter;
+
+public final class ExcelWriterOutputFormat {
+
+ public static final String EXT_XLS = "xls";
+ public static final String EXT_XLSX = "xlsx";
+ public static final String EXT_ODS = "ods";
+
+ private ExcelWriterOutputFormat() {}
+
+ public static boolean isOds(String extension) {
+ return EXT_ODS.equalsIgnoreCase(extension);
+ }
+
+ public static boolean isXlsx(String extension) {
+ return EXT_XLSX.equalsIgnoreCase(extension);
+ }
+
+ public static boolean isXls(String extension) {
+ return EXT_XLS.equalsIgnoreCase(extension);
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
index 8645130859..5d2f9b4660 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
@@ -43,6 +43,7 @@ import org.apache.hop.pipeline.Pipeline;
import org.apache.hop.pipeline.PipelineMeta;
import org.apache.hop.pipeline.transform.BaseTransform;
import org.apache.hop.pipeline.transform.TransformMeta;
+import org.apache.hop.pipeline.transforms.excelwriter.ods.OdsExcelWriter;
import org.apache.hop.staticschema.metadata.SchemaDefinition;
import org.apache.hop.staticschema.metadata.SchemaFieldDefinition;
import org.apache.hop.staticschema.util.SchemaDefinitionUtil;
@@ -84,6 +85,8 @@ public class ExcelWriterTransform
public static final String CONST_COULDN_T_BE_FOUND_IN_THE_INPUT_STREAM =
"] couldn't be found in the input stream!";
+ private OdsExcelWriter odsExcelWriter;
+
public ExcelWriterTransform(
TransformMeta transformMeta,
ExcelWriterTransformMeta meta,
@@ -282,7 +285,7 @@ public class ExcelWriterTransform
data.usedFiles.clear();
}
- private void createParentFolder(FileObject filename) throws Exception {
+ public void createParentFolder(FileObject filename) throws Exception {
// Check for parent folder
FileObject parentfolder = null;
try {
@@ -332,6 +335,10 @@ public class ExcelWriterTransform
}
private void closeOutputFile(ExcelWriterWorkbookDefinition file) throws
HopException {
+ if (file.isOds()) {
+ getOdsExcelWriter().closeOutputFile(file);
+ return;
+ }
OutputStream out = null;
CountingOutputStream countingOut = null;
try {
@@ -436,6 +443,10 @@ public class ExcelWriterTransform
public void writeNextLine(ExcelWriterWorkbookDefinition workbookDefinition,
Object[] r)
throws HopException {
+ if (workbookDefinition.isOds()) {
+ getOdsExcelWriter().writeNextLine(workbookDefinition, r);
+ return;
+ }
try {
openLine(workbookDefinition.getSheet(), workbookDefinition.getPosY());
Row xlsRow =
workbookDefinition.getSheet().getRow(workbookDefinition.getPosY());
@@ -795,10 +806,14 @@ public class ExcelWriterTransform
}
public void prepareNextOutputFile(Object[] row) throws HopException {
+ if (ExcelWriterOutputFormat.isOds(meta.getFile().getExtension())) {
+ getOdsExcelWriter().prepareNextOutputFile(row);
+ return;
+ }
try {
// Validation
//
- // sheet name shouldn't exceed 31 character
+ // sheet name shouldn't exceed 31 character (Excel limit)
if (data.realSheetname != null && data.realSheetname.length() > 31) {
throw new HopException(
BaseMessages.getString(
@@ -1217,7 +1232,7 @@ public class ExcelWriterTransform
* @param fileName
* @return
*/
- private int getNextSplitNr(String fileName) {
+ public int getNextSplitNr(String fileName) {
int splitNr = 0;
boolean fileFound = false;
// Check if file exists and fetch max splitNr
@@ -1236,4 +1251,23 @@ public class ExcelWriterTransform
}
return splitNr;
}
+
+ OdsExcelWriter getOdsExcelWriter() {
+ if (odsExcelWriter == null) {
+ odsExcelWriter = new OdsExcelWriter(this);
+ }
+ return odsExcelWriter;
+ }
+
+ public void recordBytesWritten(long written, FileObject file) {
+ dataVolumeOut = (dataVolumeOut != null ? dataVolumeOut : 0L) + written;
+ if (!data.isBeamContext() && written > 0 && file != null) {
+ try {
+ LineageFileIoEmitter.emitTransformFileIo(
+ this, FileIoOperation.WRITE, null, file, written, true, null);
+ } catch (Exception ignored) {
+ // optional lineage
+ }
+ }
+ }
}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformDialog.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformDialog.java
index 1421d492c7..ac57e325a0 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformDialog.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformDialog.java
@@ -177,6 +177,7 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
private static final String LABEL_FORMATXLSX =
"ExcelWriterDialog.FormatXLSX.Label";
private static final String LABEL_FORMATXLS =
"ExcelWriterDialog.FormatXLS.Label";
+ private static final String LABEL_FORMATODS =
"ExcelWriterDialog.FormatODS.Label";
public ExcelWriterTransformDialog(
Shell parent,
@@ -273,9 +274,11 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
String xlsLabel = BaseMessages.getString(PKG, LABEL_FORMATXLS);
String xlsxLabel = BaseMessages.getString(PKG, LABEL_FORMATXLSX);
- wExtension.setItems(new String[] {xlsLabel, xlsxLabel});
+ String odsLabel = BaseMessages.getString(PKG, LABEL_FORMATODS);
+ wExtension.setItems(new String[] {xlsLabel, xlsxLabel, odsLabel});
wExtension.setData(xlsLabel, "xls");
wExtension.setData(xlsxLabel, "xlsx");
+ wExtension.setData(odsLabel, "ods");
PropsUi.setLook(wExtension);
wExtension.addModifyListener(lsMod);
@@ -1555,10 +1558,11 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
shell,
wFilename,
variables,
- new String[] {"*.xlsx", "*.xls", "*.*"},
+ new String[] {"*.xlsx", "*.xls", "*.ods", "*.*"},
new String[] {
BaseMessages.getString(PKG, LABEL_FORMATXLSX),
BaseMessages.getString(PKG, LABEL_FORMATXLS),
+ BaseMessages.getString(PKG, LABEL_FORMATODS),
BaseMessages.getString(PKG, "System.FileType.AllFiles")
},
true));
@@ -1569,10 +1573,11 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
shell,
wTemplateFilename,
variables,
- new String[] {"*.xlsx", "*.xls", "*.*"},
+ new String[] {"*.xlsx", "*.xls", "*.ods", "*.*"},
new String[] {
BaseMessages.getString(PKG, LABEL_FORMATXLSX),
BaseMessages.getString(PKG, LABEL_FORMATXLS),
+ BaseMessages.getString(PKG, LABEL_FORMATODS),
BaseMessages.getString(PKG, "System.FileType.AllFiles")
},
true));
@@ -1739,9 +1744,10 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
}
wDoNotOpenNewFileInit.setSelection(file.isDoNotOpenNewFileInit());
if (file.getExtension() != null) {
-
if (file.getExtension().equals("xlsx")) {
wExtension.select(1);
+ } else if (file.getExtension().equals("ods")) {
+ wExtension.select(2);
} else {
wExtension.select(0);
}
@@ -2012,15 +2018,22 @@ public class ExcelWriterTransformDialog extends
BaseTransformDialog {
}
private void enableExtension() {
- wProtectSheet.setEnabled(wExtension.getSelectionIndex() == 0);
- if (wExtension.getSelectionIndex() == 0) {
+ int extensionIndex = wExtension.getSelectionIndex();
+ boolean xlsFormat = extensionIndex == 0;
+ boolean odsFormat = extensionIndex == 2;
+ wProtectSheet.setEnabled(xlsFormat || odsFormat);
+ if (xlsFormat || odsFormat) {
wPassword.setEnabled(wProtectSheet.getSelection());
- wProtectedBy.setEnabled(wProtectSheet.getSelection());
+ wProtectedBy.setEnabled(wProtectSheet.getSelection() && xlsFormat);
wStreamData.setEnabled(false);
- } else {
+ } else if (extensionIndex == 1) {
wPassword.setEnabled(false);
wProtectedBy.setEnabled(false);
wStreamData.setEnabled(true);
+ } else {
+ wPassword.setEnabled(false);
+ wProtectedBy.setEnabled(false);
+ wStreamData.setEnabled(false);
}
}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterWorkbookDefinition.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterWorkbookDefinition.java
index 9ec2e11779..ea21c65128 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterWorkbookDefinition.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterWorkbookDefinition.java
@@ -18,6 +18,7 @@
package org.apache.hop.pipeline.transforms.excelwriter;
import org.apache.commons.vfs2.FileObject;
+import org.apache.hop.pipeline.transforms.excelwriter.ods.OdsWorkbookHandle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
@@ -28,12 +29,15 @@ public class ExcelWriterWorkbookDefinition {
private String fileName;
private Workbook workbook;
private Sheet sheet;
+ private OdsWorkbookHandle odsWorkbookHandle;
private int posX;
private int posY;
private int datalines;
private int splitNr;
private CellStyle[] cellStyleCache;
private CellStyle[] cellLinkStyleCache;
+ private String[] odsStyleNameCache;
+ private String[] odsLinkStyleNameCache;
public ExcelWriterWorkbookDefinition(
String fileName, FileObject file, Workbook workbook, Sheet sheet, int
posX, int posY) {
@@ -47,6 +51,21 @@ public class ExcelWriterWorkbookDefinition {
this.splitNr = 0;
}
+ public ExcelWriterWorkbookDefinition(
+ String fileName, FileObject file, OdsWorkbookHandle odsWorkbookHandle,
int posX, int posY) {
+ this.fileName = fileName;
+ this.file = file;
+ this.odsWorkbookHandle = odsWorkbookHandle;
+ this.posX = posX;
+ this.posY = posY;
+ this.datalines = 0;
+ this.splitNr = 0;
+ }
+
+ public boolean isOds() {
+ return odsWorkbookHandle != null;
+ }
+
public FileObject getFile() {
return file;
}
@@ -71,6 +90,14 @@ public class ExcelWriterWorkbookDefinition {
this.sheet = sheet;
}
+ public OdsWorkbookHandle getOdsWorkbookHandle() {
+ return odsWorkbookHandle;
+ }
+
+ public void setOdsWorkbookHandle(OdsWorkbookHandle odsWorkbookHandle) {
+ this.odsWorkbookHandle = odsWorkbookHandle;
+ }
+
public int getPosX() {
return posX;
}
@@ -122,6 +149,24 @@ public class ExcelWriterWorkbookDefinition {
public void clearStyleCache(int nrFields) {
cellStyleCache = new CellStyle[nrFields];
cellLinkStyleCache = new CellStyle[nrFields];
+ odsStyleNameCache = new String[nrFields];
+ odsLinkStyleNameCache = new String[nrFields];
+ }
+
+ public void cacheOdsStyle(int fieldNr, String styleName) {
+ odsStyleNameCache[fieldNr] = styleName;
+ }
+
+ public void cacheOdsLinkStyle(int fieldNr, String styleName) {
+ odsLinkStyleNameCache[fieldNr] = styleName;
+ }
+
+ public String getCachedOdsStyle(int fieldNr) {
+ return odsStyleNameCache[fieldNr];
+ }
+
+ public String getCachedOdsLinkStyle(int fieldNr) {
+ return odsLinkStyleNameCache[fieldNr];
}
public void cacheStyle(int fieldNr, CellStyle style) {
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriter.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriter.java
new file mode 100644
index 0000000000..9c406c122c
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriter.java
@@ -0,0 +1,628 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import java.io.BufferedOutputStream;
+import java.io.InputStream;
+import java.io.OutputStream;
+import java.util.Calendar;
+import org.apache.commons.vfs2.FileObject;
+import org.apache.hop.core.ResultFile;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopFileException;
+import org.apache.hop.core.io.CountingOutputStream;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.util.Utils;
+import org.apache.hop.core.vfs.HopVfs;
+import org.apache.hop.i18n.BaseMessages;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterOutputField;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransform;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformData;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformMeta;
+import
org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterWorkbookDefinition;
+import org.apache.poi.ss.util.CellReference;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableCell;
+import org.odftoolkit.odfdom.dom.element.table.TableTableElement;
+import org.w3c.dom.Node;
+
+/**
+ * ODS output backend for {@link ExcelWriterTransform}. Supports basic cell
values, styling,
+ * hyperlinks, comments, formulas, headers, footers, append, templates,
starting cell, sheet clone,
+ * row push-down, auto-size columns, and active sheet selection.
+ */
+public class OdsExcelWriter {
+
+ private static final Class<?> PKG = ExcelWriterTransformMeta.class;
+
+ private final ExcelWriterTransform transform;
+ private final ExcelWriterTransformMeta meta;
+ private final ExcelWriterTransformData data;
+
+ public OdsExcelWriter(ExcelWriterTransform transform) {
+ this.transform = transform;
+ this.meta = transform.getMeta();
+ this.data = transform.getData();
+ }
+
+ public void prepareNextOutputFile(Object[] row) throws HopException {
+ try {
+ if (data.isBeamContext() && meta.getFile().isFileNameInField()) {
+ throw new HopException(
+ BaseMessages.getString(
+ PKG,
"ExcelWriterTransform.Exception.FilenameFromFieldNotSupportedInBeam"));
+ }
+
+ int numOfFields = !Utils.isEmpty(meta.getOutputFields()) ?
meta.getOutputFields().size() : 0;
+ if (numOfFields == 0) {
+ numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0;
+ }
+
+ int splitNr = 0;
+ if (!meta.getFile().isFileNameInField()) {
+ splitNr = transform.getNextSplitNr(meta.getFile().getFileName());
+ }
+
+ FileObject file = getFileLocation(row);
+
+ if (!file.getParent().exists() && meta.getFile().isCreateParentFolder())
{
+ transform.createParentFolder(file);
+ }
+
+ if (transform.isDebug()) {
+ transform.logDebug(
+ BaseMessages.getString(
+ PKG, "ExcelWriterTransform.Log.OpeningFile",
file.getName().toString()));
+ }
+
+ if (file.exists() && data.createNewFile && !file.delete()) {
+ if (transform.isBasic()) {
+ transform.logBasic(
+ BaseMessages.getString(
+ PKG,
+ "ExcelWriterTransform.Log.CouldNotDeleteStaleFile",
+ file.getName().toString()));
+ }
+ transform.setErrors(1);
+ throw new HopException("Could not delete stale file " +
file.getName().toString());
+ }
+
+ if (meta.isAddToResultFilenames()) {
+ ResultFile resultFile =
+ new ResultFile(
+ ResultFile.FILE_TYPE_GENERAL,
+ file,
+ transform.getPipelineMeta().getName(),
+ transform.getTransformName());
+ resultFile.setComment(
+ "This file was created with an Excel writer transform by Hop : The
Hop Orchestration Platform");
+ transform.addResultFile(resultFile);
+ }
+
+ boolean appendingToSheet = true;
+ if (!file.exists()) {
+ if (meta.getTemplate().isTemplateEnabled()) {
+ ensureTemplateExtensionMatches();
+ FileObject templateFile =
HopVfs.getFileObject(data.realTemplateFileName, transform);
+ if (templateFile.exists()) {
+ ExcelWriterTransform.copyFile(templateFile, file);
+ } else {
+ if (transform.isBasic()) {
+ transform.logBasic(
+ BaseMessages.getString(
+ PKG, "ExcelWriterTransform.Log.TemplateMissing",
data.realTemplateFileName));
+ }
+ transform.setErrors(1);
+ throw new HopException("Template file missing: " +
data.realTemplateFileName);
+ }
+ } else {
+ createEmptyOdsFile(file);
+ }
+ appendingToSheet = false;
+ }
+
+ OdfSpreadsheetDocument document;
+ try (InputStream inputStream =
HopVfs.getInputStream(HopVfs.getFilename(file), transform)) {
+ document = OdfSpreadsheetDocument.loadDocument(inputStream);
+ }
+
+ ResolvedTable resolved = resolveTable(document);
+ OdfTable table = resolved.table();
+ if (resolved.newlyCreated()) {
+ appendingToSheet = false;
+ }
+
+ if (!Utils.isEmpty(data.realStartingCell)) {
+ CellReference cellRef = new CellReference(data.realStartingCell);
+ data.startingRow = cellRef.getRow();
+ data.startingCol = cellRef.getCol();
+ } else {
+ data.startingRow = 0;
+ data.startingCol = 0;
+ }
+
+ int posX = data.startingCol;
+ int posY = data.startingRow;
+
+ if (!data.createNewSheet && meta.isAppendLines() && appendingToSheet) {
+ posY = findLastUsedRow(table) + 1;
+ }
+
+ if (!data.createNewSheet && meta.getAppendOffset() != 0 &&
appendingToSheet) {
+ posY += meta.getAppendOffset();
+ }
+
+ if (!data.createNewSheet && meta.getAppendEmpty() > 0 &&
appendingToSheet) {
+ for (int i = 0; i < meta.getAppendEmpty(); i++) {
+ openLine(table, posY);
+ if (!data.shiftExistingCells || meta.isAppendLines()) {
+ posY++;
+ }
+ }
+ }
+
+ if (meta.getFile().isProtectsheet()) {
+ OdsTableHelper.protectTable(table, data.realPassword);
+ }
+
+ String baseFileName =
+ !meta.getFile().isFileNameInField()
+ ? meta.getFile().getFileName()
+ : file.getName().toString();
+
+ int startY =
+ !Utils.isEmpty(data.realStartingCell) ? posY : Math.max(posY,
findLastUsedRow(table));
+
+ ExcelWriterWorkbookDefinition workbookDefinition =
+ prepareWorkbookDefinition(
+ numOfFields, splitNr, file, document, table, posX, baseFileName,
startY);
+
+ if (meta.isHeaderEnabled()
+ && !(!data.createNewSheet && meta.isAppendOmitHeader() &&
appendingToSheet)) {
+ writeHeader(workbookDefinition, posX, posY);
+ }
+
+ if (transform.isDebug()) {
+ transform.logDebug(
+ BaseMessages.getString(
+ PKG, "ExcelWriterTransform.Log.FileOpened",
file.getName().toString()));
+ }
+ } catch (Exception e) {
+ transform.logError("Error opening new ODS file", e);
+ transform.setErrors(1);
+ throw new HopException("Error opening new ODS file", e);
+ }
+ }
+
+ public void writeNextLine(ExcelWriterWorkbookDefinition workbookDefinition,
Object[] row)
+ throws HopException {
+ try {
+ OdfTable table = workbookDefinition.getOdsWorkbookHandle().getTable();
+ openLine(table, workbookDefinition.getPosY());
+ int rowIndex = workbookDefinition.getPosY();
+
+ if (Utils.isEmpty(meta.getOutputFields())) {
+ int nr = data.inputRowMeta.size();
+ int x = workbookDefinition.getPosX();
+ for (int i = 0; i < nr; i++) {
+ writeField(
+ workbookDefinition,
+ table,
+ rowIndex,
+ x++,
+ row[i],
+ data.inputRowMeta.getValueMeta(i),
+ null,
+ row,
+ i,
+ false);
+ }
+ workbookDefinition.setPosX(data.startingCol);
+ workbookDefinition.incrementY();
+ } else {
+ int x = workbookDefinition.getPosX();
+ for (int i = 0; i < meta.getOutputFields().size(); i++) {
+ ExcelWriterOutputField field = meta.getOutputFields().get(i);
+ writeField(
+ workbookDefinition,
+ table,
+ rowIndex,
+ x++,
+ row[data.fieldnrs[i]],
+ data.inputRowMeta.getValueMeta(data.fieldnrs[i]),
+ field,
+ row,
+ i,
+ false);
+ }
+ workbookDefinition.setPosX(data.startingCol);
+ workbookDefinition.incrementY();
+ }
+ } catch (Exception e) {
+ transform.logError("Error writing ODS line: " + e);
+ throw new HopException(e);
+ }
+ }
+
+ public void closeOutputFile(ExcelWriterWorkbookDefinition fileDefinition)
throws HopException {
+ OutputStream out = null;
+ CountingOutputStream countingOut = null;
+ try {
+ countingOut =
+ new
CountingOutputStream(HopVfs.getOutputStream(fileDefinition.getFile(), false));
+ out = new BufferedOutputStream(countingOut);
+
+ if (meta.isFooterEnabled()) {
+ writeHeader(fileDefinition, fileDefinition.getPosX(),
fileDefinition.getPosY());
+ }
+
+ OdfSpreadsheetDocument document =
fileDefinition.getOdsWorkbookHandle().getDocument();
+ OdfTable table = fileDefinition.getOdsWorkbookHandle().getTable();
+ if (meta.getFile().isAutosizecolums()) {
+ int columnCount =
+ !Utils.isEmpty(meta.getOutputFields())
+ ? meta.getOutputFields().size()
+ : (data.inputRowMeta != null ? data.inputRowMeta.size() : 0);
+ OdsTableHelper.autoSizeColumns(table, data.startingCol, columnCount);
+ }
+ if (meta.isForceFormulaRecalculation()) {
+ OdsFormulaHelper.prepareForRecalculation(document);
+ }
+ document.save(out);
+ document.close();
+ } catch (Exception e) {
+ throw new HopException(e);
+ } finally {
+ if (out != null) {
+ try {
+ out.flush();
+ if (countingOut != null) {
+ long written = countingOut.getCount();
+ transform.recordBytesWritten(written, fileDefinition.getFile());
+ }
+ out.close();
+ } catch (Exception e) {
+ throw new HopException("Error closing ODS file " +
fileDefinition.getFile(), e);
+ }
+ }
+ }
+ }
+
+ private void writeHeader(ExcelWriterWorkbookDefinition workbookDefinition,
int posX, int posY)
+ throws HopException {
+ try {
+ OdfTable table = workbookDefinition.getOdsWorkbookHandle().getTable();
+ openLine(table, posY);
+ int x = posX;
+ if (!Utils.isEmpty(meta.getOutputFields())) {
+ for (int i = 0; i < meta.getOutputFields().size(); i++) {
+ ExcelWriterOutputField field = meta.getOutputFields().get(i);
+ String fieldName = !Utils.isEmpty(field.getTitle()) ?
field.getTitle() : field.getName();
+ writeField(
+ workbookDefinition,
+ table,
+ posY,
+ x++,
+ fieldName,
+ new ValueMetaString(fieldName),
+ field,
+ null,
+ i,
+ true);
+ }
+ } else if (data.inputRowMeta != null) {
+ for (int i = 0; i < data.inputRowMeta.size(); i++) {
+ String fieldName = data.inputRowMeta.getFieldNames()[i];
+ writeField(
+ workbookDefinition,
+ table,
+ posY,
+ x++,
+ fieldName,
+ new ValueMetaString(fieldName),
+ null,
+ null,
+ -1,
+ true);
+ }
+ }
+ workbookDefinition.setPosY(posY + 1);
+ transform.incrementLinesOutput();
+ } catch (Exception e) {
+ throw new HopException(e);
+ }
+ }
+
+ private void writeField(
+ ExcelWriterWorkbookDefinition workbookDefinition,
+ OdfTable table,
+ int rowIndex,
+ int colIndex,
+ Object value,
+ IValueMeta vMeta,
+ ExcelWriterOutputField excelField,
+ Object[] row,
+ int fieldNr,
+ boolean isTitle)
+ throws Exception {
+ OdfSpreadsheetDocument document =
workbookDefinition.getOdsWorkbookHandle().getDocument();
+ OdfTableCell cell = table.getCellByPosition(colIndex, rowIndex);
+ boolean cellExisted = OdsStyleHelper.cellHadContent(cell);
+
+ if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
+ if (!isTitle
+ && fieldNr >= 0
+ && !Utils.isEmpty(workbookDefinition.getCachedOdsStyle(fieldNr))) {
+ applyStyleName(cell, workbookDefinition.getCachedOdsStyle(fieldNr));
+ } else {
+ if (excelField != null) {
+ String styleRef = null;
+ if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) {
+ styleRef = excelField.getStyleCell();
+ } else if (isTitle &&
!Utils.isEmpty(excelField.getTitleStyleCell())) {
+ styleRef = excelField.getTitleStyleCell();
+ }
+ if (styleRef != null) {
+ OdfTableCell styleCell =
OdsStyleHelper.getCellFromReference(document, table, styleRef);
+ if (styleCell != null && styleCell != cell) {
+ OdsStyleHelper.copyStyle(cell, styleCell);
+ }
+ }
+ }
+
+ if (!isTitle && fieldNr >= 0) {
+ workbookDefinition.cacheOdsStyle(fieldNr, cell.getStyleName());
+ }
+ }
+ }
+
+ boolean isFormulaField = !isTitle && excelField != null &&
excelField.isFormula();
+
+ if (!isFormulaField) {
+ setCellValue(cell, value, vMeta);
+ }
+
+ if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
+ if (!isTitle
+ && excelField != null
+ && !Utils.isEmpty(excelField.getFormat())
+ && !excelField.getFormat().startsWith("Image")) {
+ OdsStyleHelper.applyFormat(cell, excelField.getFormat());
+ } else if (!isTitle
+ && excelField != null
+ && Utils.isEmpty(excelField.getFormat())
+ && (vMeta.getType() == IValueMeta.TYPE_DATE
+ || vMeta.getType() == IValueMeta.TYPE_TIMESTAMP)) {
+ String format = vMeta.getFormatMask();
+ if (!Utils.isEmpty(format)) {
+ OdsStyleHelper.applyFormat(cell, format);
+ }
+ }
+ }
+
+ if (!isTitle && excelField != null && fieldNr >= 0 &&
data.linkfieldnrs[fieldNr] >= 0) {
+ String link =
+ data.inputRowMeta
+ .getValueMeta(data.linkfieldnrs[fieldNr])
+ .getString(row[data.linkfieldnrs[fieldNr]]);
+ if (!Utils.isEmpty(link)) {
+ String displayText = value != null ? vMeta.getString(value) : "";
+ if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
+ if
(!Utils.isEmpty(workbookDefinition.getCachedOdsLinkStyle(fieldNr))) {
+ applyStyleName(cell,
workbookDefinition.getCachedOdsLinkStyle(fieldNr));
+ }
+ }
+ OdsStyleHelper.applyHyperlink(document, table, cell, link,
displayText);
+ if (!(cellExisted && meta.isLeaveExistingStylesUnchanged()) && fieldNr
>= 0) {
+ workbookDefinition.cacheOdsLinkStyle(fieldNr, cell.getStyleName());
+ }
+ }
+ }
+
+ if (!isTitle && excelField != null && fieldNr >= 0 &&
data.commentfieldnrs[fieldNr] >= 0) {
+ String comment =
+ data.inputRowMeta
+ .getValueMeta(data.commentfieldnrs[fieldNr])
+ .getString(row[data.commentfieldnrs[fieldNr]]);
+ if (!Utils.isEmpty(comment)) {
+ String author =
+ data.commentauthorfieldnrs[fieldNr] >= 0
+ ? data.inputRowMeta
+ .getValueMeta(data.commentauthorfieldnrs[fieldNr])
+ .getString(row[data.commentauthorfieldnrs[fieldNr]])
+ : "Apache Hop";
+ OdsStyleHelper.applyComment(cell, author, comment);
+ }
+ }
+
+ if (isFormulaField && value != null) {
+ OdsFormulaHelper.applyFormula(cell, vMeta.getString(value));
+ }
+ }
+
+ private void applyStyleName(OdfTableCell cell, String styleName) {
+ if (cell != null && !Utils.isEmpty(styleName)) {
+ ((org.odftoolkit.odfdom.dom.element.OdfStylableElement)
cell.getOdfElement())
+ .setStyleName(styleName);
+ }
+ }
+
+ private void setCellValue(OdfTableCell cell, Object value, IValueMeta vMeta)
throws Exception {
+ if (value == null) {
+ return;
+ }
+ switch (vMeta.getType()) {
+ case IValueMeta.TYPE_DATE, IValueMeta.TYPE_TIMESTAMP -> {
+ if (vMeta.getDate(value) != null) {
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(vMeta.getDate(value));
+ cell.setDateValue(calendar);
+ }
+ }
+ case IValueMeta.TYPE_BOOLEAN ->
cell.setBooleanValue(vMeta.getBoolean(value));
+ case IValueMeta.TYPE_BIGNUMBER, IValueMeta.TYPE_NUMBER,
IValueMeta.TYPE_INTEGER ->
+ cell.setDoubleValue(vMeta.getNumber(value));
+ default -> cell.setStringValue(vMeta.getString(value));
+ }
+ }
+
+ private void createEmptyOdsFile(FileObject file) throws Exception {
+ OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.newSpreadsheetDocument();
+ try {
+ OdfTable table = document.getTableByName(data.realSheetname);
+ if (table == null) {
+ if (!document.getTableList().isEmpty()) {
+ table = document.getTableList().get(0);
+ table.setTableName(data.realSheetname);
+ } else {
+ table = OdfTable.newTable(document, 1, 1);
+ table.setTableName(data.realSheetname);
+ }
+ }
+ try (OutputStream out = HopVfs.getOutputStream(file, false)) {
+ document.save(out);
+ }
+ } finally {
+ document.close();
+ }
+ }
+
+ private void openLine(OdfTable table, int rowIndex) {
+ if (data.shiftExistingCells) {
+ OdsTableHelper.shiftRowsDown(table, rowIndex);
+ }
+ }
+
+ private ResolvedTable resolveTable(OdfSpreadsheetDocument document) throws
Exception {
+ String existingActiveTable = OdsTableHelper.getActiveTableName(document);
+ int replacingTableAt = -1;
+ boolean newlyCreated = false;
+
+ OdfTable table = document.getTableByName(data.realSheetname);
+ if (table != null && data.createNewSheet) {
+ replacingTableAt = OdsTableHelper.getTableIndex(document,
data.realSheetname);
+ removeTable(document, table);
+ table = null;
+ }
+
+ if (table == null) {
+ if (meta.getTemplate().isTemplateSheetEnabled()) {
+ OdfTable templateTable =
document.getTableByName(data.realTemplateSheetName);
+ if (templateTable == null) {
+ throw new HopException(
+ BaseMessages.getString(
+ PKG,
+ "ExcelWriterTransform.Exception.TemplateNotFound",
+ data.realTemplateSheetName));
+ }
+ table = OdsTableHelper.cloneTable(document, templateTable,
data.realSheetname);
+ if (meta.getTemplate().isTemplateSheetHidden()) {
+ OdsTableHelper.setTableVisible(templateTable, false);
+ }
+ } else {
+ table = OdfTable.newTable(document, 1, 1);
+ table.setTableName(data.realSheetname);
+ }
+ newlyCreated = true;
+ if (replacingTableAt > -1) {
+ OdsTableHelper.moveTableToIndex(document, table, replacingTableAt);
+ }
+ if (!Utils.isEmpty(existingActiveTable) && !meta.isMakeSheetActive()) {
+ OdsTableHelper.setActiveTableName(document, existingActiveTable);
+ }
+ }
+
+ if (meta.isMakeSheetActive()) {
+ OdsTableHelper.setActiveTableName(document, data.realSheetname);
+ }
+ return new ResolvedTable(table, newlyCreated);
+ }
+
+ private record ResolvedTable(OdfTable table, boolean newlyCreated) {}
+
+ private void removeTable(OdfSpreadsheetDocument document, OdfTable table) {
+ TableTableElement tableElement = table.getOdfElement();
+ Node parent = tableElement.getParentNode();
+ if (parent != null) {
+ parent.removeChild(tableElement);
+ }
+ }
+
+ private int findLastUsedRow(OdfTable table) {
+ int rowCount = table.getRowCount();
+ for (int row = rowCount - 1; row >= 0; row--) {
+ if (rowHasContent(table, row)) {
+ return row;
+ }
+ }
+ return -1;
+ }
+
+ private boolean rowHasContent(OdfTable table, int rowIndex) {
+ int columnCount = table.getColumnCount();
+ for (int col = 0; col < columnCount; col++) {
+ OdfTableCell cell = table.getCellByPosition(col, rowIndex);
+ if (cell != null && !Utils.isEmpty(cell.getDisplayText())) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ private void ensureTemplateExtensionMatches() throws HopException {
+ String templateExt =
+ HopVfs.getFileObject(data.realTemplateFileName,
transform).getName().getExtension();
+ if (!meta.getFile().getExtension().equalsIgnoreCase(templateExt)) {
+ throw new HopException(
+ "Template Format Mismatch: Template has extension: "
+ + templateExt
+ + ", but output file has extension: "
+ + meta.getFile().getExtension()
+ + ". Template and output file must share the same format!");
+ }
+ }
+
+ private ExcelWriterWorkbookDefinition prepareWorkbookDefinition(
+ int numOfFields,
+ int splitNr,
+ FileObject file,
+ OdfSpreadsheetDocument document,
+ OdfTable table,
+ int posX,
+ String baseFileName,
+ int startY) {
+ OdsWorkbookHandle handle = new OdsWorkbookHandle(document, table);
+ ExcelWriterWorkbookDefinition workbookDefinition =
+ new ExcelWriterWorkbookDefinition(baseFileName, file, handle, posX,
startY);
+ workbookDefinition.setSplitNr(splitNr);
+ data.usedFiles.add(workbookDefinition);
+ data.currentWorkbookDefinition = workbookDefinition;
+ workbookDefinition.clearStyleCache(numOfFields);
+ return workbookDefinition;
+ }
+
+ private FileObject getFileLocation(Object[] row) throws HopFileException {
+ String buildFilename =
+ (!meta.getFile().isFileNameInField())
+ ?
transform.buildFilename(transform.getNextSplitNr(meta.getFile().getFileName()))
+ : transform.buildFilename(data.inputRowMeta, row);
+ return HopVfs.getFileObject(buildFilename, transform);
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverter.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverter.java
new file mode 100644
index 0000000000..94ca2055cf
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverter.java
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import org.apache.hop.core.util.Utils;
+
+/** Best-effort conversion from Excel-style format masks to ODF format
strings. */
+final class OdsFormatConverter {
+
+ private OdsFormatConverter() {}
+
+ static String toOdfFormat(String excelFormat) {
+ if (Utils.isEmpty(excelFormat)) {
+ return excelFormat;
+ }
+ return excelFormat
+ .replace("yyyy", "YYYY")
+ .replace("yy", "YY")
+ .replace("dd", "DD")
+ .replace("hh", "HH")
+ .replace("mm", "MM")
+ .replace("ss", "SS");
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverter.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverter.java
new file mode 100644
index 0000000000..bc0924067f
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverter.java
@@ -0,0 +1,96 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import org.apache.hop.core.util.Utils;
+
+/** Converts Excel-style formula strings to OpenFormula syntax for ODS output.
*/
+public final class OdsFormulaConverter {
+
+ private static final Pattern SHEET_CELL =
+ Pattern.compile(
+ "((?:'[^']+'|[A-Za-z0-9_]+))!(\\$?)([A-Za-z]{1,3})(\\$?)(\\d+)",
+ Pattern.CASE_INSENSITIVE);
+
+ private static final Pattern CELL_RANGE =
+ Pattern.compile(
+
"(\\$?)([A-Za-z]{1,3})(\\$?)(\\d+):(\\$?)([A-Za-z]{1,3})(\\$?)(\\d+)",
+ Pattern.CASE_INSENSITIVE);
+
+ private static final Pattern CELL_REFERENCE =
+ Pattern.compile("(?<![\\[\\.])\\$?([A-Za-z]{1,3})\\$?(\\d+)",
Pattern.CASE_INSENSITIVE);
+
+ private OdsFormulaConverter() {}
+
+ public static String toOdfFormula(String excelFormula) {
+ if (Utils.isEmpty(excelFormula)) {
+ return excelFormula;
+ }
+ String trimmed = excelFormula.trim();
+ if (trimmed.regionMatches(true, 0, "of:", 0, 3)) {
+ return trimmed;
+ }
+
+ String body = trimmed.startsWith("=") ? trimmed.substring(1) : trimmed;
+ body = convertSheetCellReferences(body);
+ body = convertCellRanges(body);
+ body = convertCellReferences(body);
+ return "of:=" + body;
+ }
+
+ private static String convertSheetCellReferences(String body) {
+ Matcher matcher = SHEET_CELL.matcher(body);
+ StringBuilder result = new StringBuilder();
+ while (matcher.find()) {
+ String sheet = matcher.group(1);
+ String column = matcher.group(3).toUpperCase();
+ String row = matcher.group(5);
+ matcher.appendReplacement(result, sheet + ".[" + column + row + "]");
+ }
+ matcher.appendTail(result);
+ return result.toString();
+ }
+
+ private static String convertCellRanges(String body) {
+ Matcher matcher = CELL_RANGE.matcher(body);
+ StringBuilder result = new StringBuilder();
+ while (matcher.find()) {
+ String startCol = matcher.group(2).toUpperCase();
+ String startRow = matcher.group(4);
+ String endCol = matcher.group(6).toUpperCase();
+ String endRow = matcher.group(8);
+ matcher.appendReplacement(result, "[." + startCol + startRow + ":." +
endCol + endRow + "]");
+ }
+ matcher.appendTail(result);
+ return result.toString();
+ }
+
+ private static String convertCellReferences(String body) {
+ Matcher matcher = CELL_REFERENCE.matcher(body);
+ StringBuilder result = new StringBuilder();
+ while (matcher.find()) {
+ String column = matcher.group(1).toUpperCase();
+ String row = matcher.group(2);
+ matcher.appendReplacement(result, ".[" + column + row + "]");
+ }
+ matcher.appendTail(result);
+ return result.toString();
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaHelper.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaHelper.java
new file mode 100644
index 0000000000..6760206180
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaHelper.java
@@ -0,0 +1,75 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import org.apache.hop.core.util.Utils;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableCell;
+import org.odftoolkit.odfdom.dom.OdfDocumentNamespace;
+import org.w3c.dom.Element;
+
+final class OdsFormulaHelper {
+
+ private OdsFormulaHelper() {}
+
+ static void applyFormula(OdfTableCell cell, String excelFormula) throws
Exception {
+ if (cell == null || Utils.isEmpty(excelFormula)) {
+ return;
+ }
+ cell.setFormula(OdsFormulaConverter.toOdfFormula(excelFormula));
+ }
+
+ /**
+ * Clears cached formula results so ODF spreadsheet applications recalculate
on open. ODF has no
+ * direct equivalent to POI's force-formula-recalculation flag.
+ */
+ static void prepareForRecalculation(OdfSpreadsheetDocument document) throws
Exception {
+ for (OdfTable table : document.getTableList()) {
+ int rows = table.getRowCount();
+ int cols = table.getColumnCount();
+ for (int row = 0; row < rows; row++) {
+ for (int col = 0; col < cols; col++) {
+ OdfTableCell cell = table.getCellByPosition(col, row);
+ if (cell == null || Utils.isEmpty(cell.getFormula())) {
+ continue;
+ }
+ clearCachedFormulaResult(cell);
+ }
+ }
+ }
+ }
+
+ private static void clearCachedFormulaResult(OdfTableCell cell) {
+ Element element = cell.getOdfElement();
+ String officeNs = OdfDocumentNamespace.OFFICE.getUri();
+ removeAttributeIfPresent(element, officeNs, "value");
+ removeAttributeIfPresent(element, officeNs, "string-value");
+ removeAttributeIfPresent(element, officeNs, "boolean-value");
+ removeAttributeIfPresent(element, officeNs, "date-value");
+ removeAttributeIfPresent(element, officeNs, "time-value");
+ cell.removeTextContent();
+ }
+
+ private static void removeAttributeIfPresent(
+ Element element, String namespace, String localName) {
+ if (element.hasAttributeNS(namespace, localName)) {
+ element.removeAttributeNS(namespace, localName);
+ }
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsStyleHelper.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsStyleHelper.java
new file mode 100644
index 0000000000..ab8f8d5e13
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsStyleHelper.java
@@ -0,0 +1,155 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import org.apache.hop.core.util.Utils;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableCell;
+import org.odftoolkit.odfdom.dom.element.OdfStylableElement;
+import org.odftoolkit.odfdom.dom.element.dc.DcCreatorElement;
+import org.odftoolkit.odfdom.dom.element.office.OfficeAnnotationElement;
+import org.odftoolkit.odfdom.dom.element.table.TableTableCellElement;
+import org.odftoolkit.odfdom.dom.element.text.TextAElement;
+import org.odftoolkit.odfdom.dom.element.text.TextPElement;
+import org.odftoolkit.odfdom.dom.style.OdfStyleFamily;
+import org.odftoolkit.odfdom.dom.style.props.OdfTextProperties;
+import org.odftoolkit.odfdom.incubator.doc.office.OdfOfficeAutomaticStyles;
+import org.odftoolkit.odfdom.incubator.doc.style.OdfStyle;
+
+final class OdsStyleHelper {
+
+ private static final String HYPERLINK_TEXT_STYLE = "HopOdsHyperlink";
+
+ private OdsStyleHelper() {}
+
+ static boolean cellHadContent(OdfTableCell cell) {
+ if (cell == null) {
+ return false;
+ }
+ if (!Utils.isEmpty(cell.getDisplayText())) {
+ return true;
+ }
+ return !Utils.isEmpty(cell.getStyleName());
+ }
+
+ static void copyStyle(OdfTableCell target, OdfTableCell source) {
+ if (target == null || source == null || target == source) {
+ return;
+ }
+ String styleName = source.getStyleName();
+ if (!Utils.isEmpty(styleName)) {
+ asStylable(target).setStyleName(styleName);
+ }
+ }
+
+ static void applyFormat(OdfTableCell cell, String format) {
+ if (cell == null || Utils.isEmpty(format)) {
+ return;
+ }
+ cell.setFormatString(OdsFormatConverter.toOdfFormat(format));
+ }
+
+ static OdfTableCell getCellFromReference(
+ OdfSpreadsheetDocument document, OdfTable defaultTable, String
reference) {
+ if (Utils.isEmpty(reference)) {
+ return null;
+ }
+ org.apache.poi.ss.util.CellReference cellRef =
+ new org.apache.poi.ss.util.CellReference(reference);
+ OdfTable table = defaultTable;
+ String sheetName = cellRef.getSheetName();
+ if (!Utils.isEmpty(sheetName)) {
+ table = document.getTableByName(sheetName);
+ }
+ if (table == null) {
+ return null;
+ }
+ return table.getCellByPosition(cellRef.getCol(), cellRef.getRow());
+ }
+
+ static void applyHyperlink(
+ OdfSpreadsheetDocument document,
+ OdfTable table,
+ OdfTableCell cell,
+ String link,
+ String displayText)
+ throws Exception {
+ if (cell == null || Utils.isEmpty(link)) {
+ return;
+ }
+ String href = toOdfHref(link);
+ String text = Utils.isEmpty(displayText) ? link : displayText;
+ String linkStyleName = getOrCreateHyperlinkTextStyle(document, table);
+
+ cell.removeTextContent();
+ TableTableCellElement cellElement = (TableTableCellElement)
cell.getOdfElement();
+ TextPElement paragraph = cellElement.newTextPElement();
+ TextAElement anchor = paragraph.newTextAElement(href, linkStyleName);
+ anchor.setXlinkTypeAttribute("simple");
+ anchor.setTextContent(text);
+ }
+
+ static void applyComment(OdfTableCell cell, String author, String comment) {
+ if (cell == null || Utils.isEmpty(comment)) {
+ return;
+ }
+ TableTableCellElement cellElement = (TableTableCellElement)
cell.getOdfElement();
+ OfficeAnnotationElement annotation =
cellElement.newOfficeAnnotationElement();
+ if (!Utils.isEmpty(author)) {
+ DcCreatorElement creator = annotation.newDcCreatorElement();
+ creator.setTextContent(author);
+ }
+ TextPElement paragraph = annotation.newTextPElement();
+ paragraph.setTextContent(comment);
+ }
+
+ private static String toOdfHref(String link) {
+ if (link.startsWith("http:") || link.startsWith("https:") ||
link.startsWith("ftp:")) {
+ return link;
+ }
+ if (link.startsWith("mailto:";)) {
+ return link;
+ }
+ if (link.startsWith("'")) {
+ return link.substring(1);
+ }
+ return link;
+ }
+
+ private static OdfStylableElement asStylable(OdfTableCell cell) {
+ return (OdfStylableElement) cell.getOdfElement();
+ }
+
+ private static String getOrCreateHyperlinkTextStyle(
+ OdfSpreadsheetDocument document, OdfTable table) throws Exception {
+ OdfTableCell anchorCell = table.getCellByPosition(0, 0);
+ OdfOfficeAutomaticStyles automaticStyles =
+ ((OdfStylableElement) anchorCell.getOdfElement()).getAutomaticStyles();
+ OdfStyle existing = automaticStyles.getStyle(HYPERLINK_TEXT_STYLE,
OdfStyleFamily.Text);
+ if (existing != null) {
+ return HYPERLINK_TEXT_STYLE;
+ }
+ OdfStyle style = automaticStyles.newStyle(OdfStyleFamily.Text);
+ style.setStyleNameAttribute(HYPERLINK_TEXT_STYLE);
+ style.setProperty(OdfTextProperties.Color, "#0000ff");
+ style.setProperty(OdfTextProperties.TextUnderlineStyle, "solid");
+ style.setProperty(OdfTextProperties.TextUnderlineType, "single");
+ return HYPERLINK_TEXT_STYLE;
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsTableHelper.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsTableHelper.java
new file mode 100644
index 0000000000..df0560c48a
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsTableHelper.java
@@ -0,0 +1,179 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import java.nio.charset.StandardCharsets;
+import java.security.MessageDigest;
+import java.util.Base64;
+import java.util.List;
+import org.apache.hop.core.util.Utils;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableColumn;
+import org.odftoolkit.odfdom.dom.OdfDocumentNamespace;
+import org.odftoolkit.odfdom.dom.OdfSettingsDom;
+import org.odftoolkit.odfdom.dom.attribute.table.TableVisibilityAttribute;
+import org.odftoolkit.odfdom.dom.element.config.ConfigConfigItemElement;
+import org.odftoolkit.odfdom.dom.element.table.TableTableElement;
+import org.w3c.dom.Node;
+import org.w3c.dom.NodeList;
+
+/** Sheet-level operations for ODS output (clone, reorder, active tab,
auto-size, row insert). */
+final class OdsTableHelper {
+
+ private static final String TABLE_VISIBILITY = "table:visibility";
+ private static final String ACTIVE_TABLE = "ActiveTable";
+ private static final String SHA1_DIGEST_ALGORITHM =
"http://www.w3.org/2000/09/xmldsig#sha1";;
+
+ private OdsTableHelper() {}
+
+ static OdfTable cloneTable(OdfSpreadsheetDocument document, OdfTable
sourceTable, String newName)
+ throws Exception {
+ TableTableElement sourceElement = sourceTable.getOdfElement();
+ Node clonedNode = document.getContentDom().importNode(sourceElement, true);
+ sourceElement.getParentNode().appendChild(clonedNode);
+ OdfTable clonedTable = OdfTable.getInstance((TableTableElement)
clonedNode);
+ clonedTable.setTableName(newName);
+ setTableVisible(clonedTable, true);
+ return clonedTable;
+ }
+
+ static void setTableVisible(OdfTable table, boolean visible) {
+ TableTableElement element = table.getOdfElement();
+ if (visible) {
+ element.removeAttributeNS(OdfDocumentNamespace.TABLE.getUri(),
TABLE_VISIBILITY);
+ } else {
+ element.setAttributeNS(
+ OdfDocumentNamespace.TABLE.getUri(),
+ TABLE_VISIBILITY,
+ TableVisibilityAttribute.Value.COLLAPSE.toString());
+ }
+ }
+
+ static int getTableIndex(OdfSpreadsheetDocument document, String tableName) {
+ List<OdfTable> tables = document.getTableList();
+ for (int i = 0; i < tables.size(); i++) {
+ if (tableName.equals(tables.get(i).getTableName())) {
+ return i;
+ }
+ }
+ return -1;
+ }
+
+ static void moveTableToIndex(OdfSpreadsheetDocument document, OdfTable
table, int targetIndex) {
+ List<OdfTable> tables = document.getTableList();
+ if (targetIndex < 0 || targetIndex >= tables.size()) {
+ return;
+ }
+ TableTableElement element = table.getOdfElement();
+ Node parent = element.getParentNode();
+ Node reference = tables.get(targetIndex).getOdfElement();
+ if (reference == element) {
+ return;
+ }
+ parent.removeChild(element);
+ parent.insertBefore(element, reference);
+ }
+
+ static String getActiveTableName(OdfSpreadsheetDocument document) throws
Exception {
+ OdfSettingsDom settingsDom = document.getSettingsDom();
+ if (settingsDom == null || settingsDom.getRootElement() == null) {
+ return null;
+ }
+ return findConfigItemValue(settingsDom.getRootElement(), ACTIVE_TABLE);
+ }
+
+ static void setActiveTableName(OdfSpreadsheetDocument document, String
tableName)
+ throws Exception {
+ if (Utils.isEmpty(tableName)) {
+ return;
+ }
+ OdfSettingsDom settingsDom = document.getSettingsDom();
+ if (settingsDom == null || settingsDom.getRootElement() == null) {
+ return;
+ }
+ if (!updateConfigItemValue(settingsDom.getRootElement(), ACTIVE_TABLE,
tableName)) {
+ // Settings from minimal documents may not contain view settings yet;
ignore quietly.
+ }
+ }
+
+ static void shiftRowsDown(OdfTable table, int rowIndex) {
+ if (rowIndex < 0) {
+ return;
+ }
+ table.insertRowsBefore(rowIndex, 1);
+ }
+
+ static void autoSizeColumns(OdfTable table, int startColumn, int
columnCount) {
+ if (columnCount <= 0) {
+ return;
+ }
+ for (int i = 0; i < columnCount; i++) {
+ int columnIndex = startColumn + i;
+ if (columnIndex < 0 || columnIndex >= table.getColumnCount()) {
+ continue;
+ }
+ OdfTableColumn column = table.getColumnByIndex(columnIndex);
+ if (column != null) {
+ column.setUseOptimalWidth(true);
+ }
+ }
+ }
+
+ static void protectTable(OdfTable table, String password) throws Exception {
+ TableTableElement element = table.getOdfElement();
+ element.setTableProtectedAttribute(true);
+ if (Utils.isEmpty(password)) {
+ return;
+ }
+ MessageDigest digest = MessageDigest.getInstance("SHA-1");
+ digest.update(password.getBytes(StandardCharsets.UTF_8));
+
element.setTableProtectionKeyAttribute(Base64.getEncoder().encodeToString(digest.digest()));
+
element.setTableProtectionKeyDigestAlgorithmAttribute(SHA1_DIGEST_ALGORITHM);
+ }
+
+ private static String findConfigItemValue(Node node, String itemName) {
+ if (node instanceof ConfigConfigItemElement configItem
+ && itemName.equals(configItem.getConfigNameAttribute())) {
+ return configItem.getTextContent();
+ }
+ NodeList children = node.getChildNodes();
+ for (int i = 0; i < children.getLength(); i++) {
+ String value = findConfigItemValue(children.item(i), itemName);
+ if (!Utils.isEmpty(value)) {
+ return value;
+ }
+ }
+ return null;
+ }
+
+ private static boolean updateConfigItemValue(Node node, String itemName,
String value) {
+ if (node instanceof ConfigConfigItemElement configItem
+ && itemName.equals(configItem.getConfigNameAttribute())) {
+ configItem.setTextContent(value);
+ return true;
+ }
+ NodeList children = node.getChildNodes();
+ for (int i = 0; i < children.getLength(); i++) {
+ if (updateConfigItemValue(children.item(i), itemName, value)) {
+ return true;
+ }
+ }
+ return false;
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsWorkbookHandle.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsWorkbookHandle.java
new file mode 100644
index 0000000000..b281c9e536
--- /dev/null
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsWorkbookHandle.java
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+
+/** Holds ODF-specific workbook state for the Excel Writer transform. */
+public class OdsWorkbookHandle {
+
+ private final OdfSpreadsheetDocument document;
+ private OdfTable table;
+
+ public OdsWorkbookHandle(OdfSpreadsheetDocument document, OdfTable table) {
+ this.document = document;
+ this.table = table;
+ }
+
+ public OdfSpreadsheetDocument getDocument() {
+ return document;
+ }
+
+ public OdfTable getTable() {
+ return table;
+ }
+
+ public void setTable(OdfTable table) {
+ this.table = table;
+ }
+}
diff --git
a/plugins/transforms/excel/src/main/resources/org/apache/hop/pipeline/transforms/excelwriter/messages/messages_en_US.properties
b/plugins/transforms/excel/src/main/resources/org/apache/hop/pipeline/transforms/excelwriter/messages/messages_en_US.properties
index 5426784386..1122ae2b2c 100644
---
a/plugins/transforms/excel/src/main/resources/org/apache/hop/pipeline/transforms/excelwriter/messages/messages_en_US.properties
+++
b/plugins/transforms/excel/src/main/resources/org/apache/hop/pipeline/transforms/excelwriter/messages/messages_en_US.properties
@@ -58,6 +58,7 @@ ExcelWriterDialog.ForceFormulaRecalculation.Tooltip=Check
this if you want Hop t
ExcelWriterDialog.FormatColumn.Column=Format
ExcelWriterDialog.FormatXLS.Label=xls [Excel 97 and above]
ExcelWriterDialog.FormatXLSX.Label=xlsx [Excel 2007 and above]
+ExcelWriterDialog.FormatODS.Label=ods [OpenDocument Spreadsheet]
ExcelWriterDialog.FormulaField.Column=Field contains formula
ExcelWriterDialog.Header.Label=Write Header
ExcelWriterDialog.Header.Tooltip=Writes the field names (or field titles if
specified) as the first row of the output.
@@ -88,8 +89,8 @@ ExcelWriterDialog.Password.Label=Password
ExcelWriterDialog.Password.Tooltip=Password to protect the sheet
ExcelWriterDialog.ProtectedBy.Label=Protected by user
ExcelWriterDialog.ProtectedBy.Tooltip=The name of the user protecting the sheet
-ExcelWriterDialog.ProtectSheet.Label=Protect sheet? (XLS format only)
-ExcelWriterDialog.ProtectSheet.Tooltip=Lock the sheet for modification by
setting a password
+ExcelWriterDialog.ProtectSheet.Label=Protect sheet? (XLS and ODS formats)
+ExcelWriterDialog.ProtectSheet.Tooltip=Lock the sheet for modification by
setting a password. Supported for XLS and ODS output. The protected-by user
field applies to XLS only. XLSX sheet protection is not supported.
ExcelWriterDialog.RowWritingMethod.Label=When writing rows
ExcelWriterDialog.RowWritingMethod.Overwrite.Label=overwrite existing cells
ExcelWriterDialog.RowWritingMethod.PushDown.Label=shift existing cells down
@@ -136,7 +137,7 @@ ExcelWriterMeta.Injection.CreateParentFolder.Field=Create
parent folder
ExcelWriterMeta.Injection.DateInFilename.Field=Include date in filename?
ExcelWriterMeta.Injection.DateTimeFormat.Field=Date time format field
ExcelWriterMeta.Injection.DoNotOpenNewFileInit.Field=Wait for first row before
creating new file?
-ExcelWriterMeta.Injection.Extension.Field=Extension (xls/xlsx)
+ExcelWriterMeta.Injection.Extension.Field=Extension (xls/xlsx/ods)
ExcelWriterMeta.Injection.Field=Field
ExcelWriterMeta.Injection.Fields=Fields
ExcelWriterMeta.Injection.FileName.Field=Filename
@@ -149,8 +150,8 @@ ExcelWriterMeta.Injection.IfFileExists.Field=If output file
exists (reuse/new)?
ExcelWriterMeta.Injection.IfSheetExists.Field=If sheet exists in output file
(reuse/new)?
ExcelWriterMeta.Injection.LeaveExistingStylesUnchanged.Field=Leave styles of
existing cells unchanged?
ExcelWriterMeta.Injection.MakeSheetActive.Field=Make this the active sheet?
-ExcelWriterMeta.Injection.Output.Comment.Field=Cell comment (XLSX)
-ExcelWriterMeta.Injection.Output.CommentAuthor.Field=Cell comment author (XLSX)
+ExcelWriterMeta.Injection.Output.Comment.Field=Cell comment (XLSX/ODS)
+ExcelWriterMeta.Injection.Output.CommentAuthor.Field=Cell comment author
(XLSX/ODS)
ExcelWriterMeta.Injection.Output.FieldContainFormula.Field=Field contains
formula?
ExcelWriterMeta.Injection.Output.FieldName.Field=Field name
ExcelWriterMeta.Injection.Output.Format.Field=Format
@@ -161,7 +162,7 @@
ExcelWriterMeta.Injection.Output.TitleStyleCell.Field=Header/footer style from c
ExcelWriterMeta.Injection.Output.Type.Field=Type
ExcelWriterMeta.Injection.Password.Field=Password
ExcelWriterMeta.Injection.ProtectedBy.Field=Protected by user
-ExcelWriterMeta.Injection.ProtectSheet.Field=Protect sheet (XLS format only)?
+ExcelWriterMeta.Injection.ProtectSheet.Field=Protect sheet (XLS and ODS
formats)?
ExcelWriterMeta.Injection.RowWritingMethod.Field=Row writing method
(overwrite/pus)
ExcelWriterMeta.Injection.SchemaDefinition.Field=Schema definition
ExcelWriterMeta.Injection.SheetName.Field=Sheet name
@@ -194,6 +195,6 @@
ExcelWriterTransformMeta.CheckResult.FieldsReceived=Transform is connected to pr
ExcelWriterTransformMeta.CheckResult.FilenameFieldNotFound=Filename field
''{0}'' not found in input stream.
ExcelWriterTransformMeta.CheckResult.FilesNotChecked=File specifications are
not checked.
ExcelWriterTransformMeta.keyword=excel,writer,transform
-TypeExitExcelWriterTransform.Description=Writes or appends data to an Excel
file
+TypeExitExcelWriterTransform.Description=Writes or appends data to an Excel or
OpenDocument Spreadsheet (.xls, .xlsx, .ods) file
TypeExitExcelWriterTransform.Name=Microsoft Excel writer
ExcelWriterDialog.IgnoreTransformFields.Label=Ignore manual fields
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterIntegrationTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterIntegrationTest.java
new file mode 100644
index 0000000000..c372d3f49b
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterIntegrationTest.java
@@ -0,0 +1,217 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.when;
+
+import java.io.File;
+import org.apache.hop.core.HopEnvironment;
+import org.apache.hop.core.logging.ILoggingObject;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterOutputField;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterOutputFormat;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransform;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformData;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformMeta;
+import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableCell;
+import org.odftoolkit.odfdom.dom.element.office.OfficeAnnotationElement;
+import org.odftoolkit.odfdom.dom.element.text.TextAElement;
+
+/**
+ * End-to-end style test exercising the main ODS writer features together in
one workflow: file
+ * template, header, data types, formula, hyperlink, comment, append, and
active sheet.
+ */
+class OdsExcelWriterIntegrationTest {
+
+ private static final String SHEET_NAME = "Report";
+
+ @TempDir File tempDir;
+
+ private TransformMockHelper<ExcelWriterTransformMeta,
ExcelWriterTransformData> mockHelper;
+ private ExcelWriterTransform transform;
+ private ExcelWriterTransformMeta meta;
+ private ExcelWriterTransformData data;
+
+ @BeforeAll
+ static void setUpBeforeClass() throws Exception {
+ HopEnvironment.init();
+ }
+
+ @BeforeEach
+ void setUp() throws Exception {
+ mockHelper =
+ new TransformMockHelper<>(
+ "ODS Excel Writer Integration",
+ ExcelWriterTransformMeta.class,
+ ExcelWriterTransformData.class);
+ when(mockHelper.logChannelFactory.create(
+ org.mockito.ArgumentMatchers.any(),
+ org.mockito.ArgumentMatchers.any(ILoggingObject.class)))
+ .thenReturn(mockHelper.iLogChannel);
+
+ meta = new ExcelWriterTransformMeta();
+ meta.setDefault();
+ meta.getFile().setExtension(ExcelWriterOutputFormat.EXT_ODS);
+ meta.getFile().setSheetname(SHEET_NAME);
+ meta.setHeaderEnabled(true);
+ meta.setMakeSheetActive(true);
+ meta.setForceFormulaRecalculation(true);
+ meta.getFile().setAutosizecolums(true);
+
+ data = new ExcelWriterTransformData();
+ data.realSheetname = SHEET_NAME;
+ data.createNewFile = true;
+ data.createNewSheet = true;
+
+ data.inputRowMeta = new org.apache.hop.core.row.RowMeta();
+ data.inputRowMeta.addValueMeta(new ValueMetaString("label"));
+ data.inputRowMeta.addValueMeta(new ValueMetaInteger("amount"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("totalFormula"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("url"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("note"));
+
+ ExcelWriterOutputField labelField = new ExcelWriterOutputField();
+ labelField.setName("label");
+ ExcelWriterOutputField amountField = new ExcelWriterOutputField();
+ amountField.setName("amount");
+ ExcelWriterOutputField totalField = new ExcelWriterOutputField();
+ totalField.setName("totalFormula");
+ totalField.setFormula(true);
+ ExcelWriterOutputField urlField = new ExcelWriterOutputField();
+ urlField.setName("url");
+ urlField.setHyperlinkField("url");
+ ExcelWriterOutputField noteField = new ExcelWriterOutputField();
+ noteField.setName("note");
+ noteField.setCommentField("note");
+ noteField.setCommentAuthorField("label");
+ meta.setOutputFields(
+ java.util.List.of(labelField, amountField, totalField, urlField,
noteField));
+
+ data.fieldnrs = new int[] {0, 1, 2, 3, 4};
+ data.linkfieldnrs = new int[] {-1, -1, -1, 3, -1};
+ data.commentfieldnrs = new int[] {-1, -1, -1, -1, 4};
+ data.commentauthorfieldnrs = new int[] {0, -1, -1, -1, -1};
+
+ transform =
+ spy(
+ new ExcelWriterTransform(
+ mockHelper.transformMeta,
+ meta,
+ data,
+ 0,
+ mockHelper.pipelineMeta,
+ mockHelper.pipeline));
+ }
+
+ @AfterEach
+ void cleanUp() {
+ mockHelper.cleanUp();
+ }
+
+ @Test
+ void testFullOdsWorkflow() throws Exception {
+ File templateFile = new File(tempDir, "workbook-template.ods");
+ try (OdfSpreadsheetDocument template =
OdfSpreadsheetDocument.newSpreadsheetDocument()) {
+ template.getTableList().get(0).setTableName("Unused");
+ template.save(templateFile);
+ }
+
+ File outputFile = new File(tempDir, "report.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.getTemplate().setTemplateEnabled(true);
+ meta.getTemplate().setTemplateFileName(templateFile.getAbsolutePath());
+ data.realTemplateFileName = templateFile.getAbsolutePath();
+
+ assertTrue(transform.init());
+ Object[] row1 =
+ new Object[] {"Alpha", 10L, "=A2+B2", "https://hop.apache.org";, "first
row comment"};
+ Object[] row2 = new Object[] {"Beta", 20L, "=A3+B3", "https://apache.org";,
"second row"};
+
+ transform.prepareNextOutputFile(row1);
+ transform.writeNextLine(data.currentWorkbookDefinition, row1);
+ transform.writeNextLine(data.currentWorkbookDefinition, row2);
+ transform.closeFiles();
+
+ meta.setAppendLines(true);
+ meta.setHeaderEnabled(false);
+
meta.getFile().setIfFileExists(ExcelWriterTransformMeta.IF_FILE_EXISTS_REUSE);
+
meta.getFile().setIfSheetExists(ExcelWriterTransformMeta.IF_SHEET_EXISTS_REUSE);
+ data.createNewFile = false;
+ data.createNewSheet = false;
+ data.usedFiles.clear();
+
+ transform.prepareNextOutputFile(
+ new Object[] {"Gamma", 30L, "=A4+B4", "https://example.com";, ""});
+ transform.writeNextLine(
+ data.currentWorkbookDefinition,
+ new Object[] {"Gamma", 30L, "=A4+B4", "https://example.com";, ""});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertNotNull(table);
+ assertEquals(SHEET_NAME, OdsTableHelper.getActiveTableName(document));
+
+ assertEquals("label", cellText(table, 0, 0));
+ assertEquals("amount", cellText(table, 1, 0));
+ assertEquals("Alpha", cellText(table, 0, 1));
+ assertEquals("10.0", cellText(table, 1, 1));
+ assertEquals("Beta", cellText(table, 0, 2));
+ assertEquals("Gamma", cellText(table, 0, 3));
+
+ OdfTableCell formulaCell = table.getCellByPosition(2, 1);
+ assertEquals("of:=.[A2]+.[B2]", formulaCell.getFormula());
+
+ TextAElement link =
+ (TextAElement)
+ table.getCellByPosition(3,
1).getOdfElement().getElementsByTagName("text:a").item(0);
+ assertNotNull(link);
+ assertEquals("https://hop.apache.org";, link.getXlinkHrefAttribute());
+
+ OfficeAnnotationElement annotation =
+ (OfficeAnnotationElement)
+ table
+ .getCellByPosition(4, 1)
+ .getOdfElement()
+ .getElementsByTagName("office:annotation")
+ .item(0);
+ assertNotNull(annotation);
+ assertTrue(annotation.getTextContent().contains("first row comment"));
+
+ assertTrue(table.getColumnByIndex(0).isOptimalWidth());
+ }
+ }
+
+ private String cellText(OdfTable table, int col, int row) throws Exception {
+ return table.getCellByPosition(col, row).getDisplayText();
+ }
+}
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterTest.java
new file mode 100644
index 0000000000..d1959c5df6
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsExcelWriterTest.java
@@ -0,0 +1,480 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.when;
+
+import java.io.File;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.hop.core.HopEnvironment;
+import org.apache.hop.core.logging.ILoggingObject;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaNumber;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterOutputField;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterOutputFormat;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransform;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformData;
+import org.apache.hop.pipeline.transforms.excelwriter.ExcelWriterTransformMeta;
+import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
+import org.odftoolkit.odfdom.doc.OdfSpreadsheetDocument;
+import org.odftoolkit.odfdom.doc.table.OdfTable;
+import org.odftoolkit.odfdom.doc.table.OdfTableCell;
+import org.odftoolkit.odfdom.dom.element.office.OfficeAnnotationElement;
+import org.odftoolkit.odfdom.dom.element.text.TextAElement;
+import org.odftoolkit.odfdom.type.Color;
+
+class OdsExcelWriterTest {
+
+ private static final String SHEET_NAME = "Output";
+
+ @TempDir File tempDir;
+
+ private TransformMockHelper<ExcelWriterTransformMeta,
ExcelWriterTransformData> mockHelper;
+ private ExcelWriterTransform transform;
+ private ExcelWriterTransformMeta meta;
+ private ExcelWriterTransformData data;
+
+ @BeforeAll
+ static void setUpBeforeClass() throws Exception {
+ HopEnvironment.init();
+ }
+
+ @BeforeEach
+ void setUp() throws Exception {
+ mockHelper =
+ new TransformMockHelper<>(
+ "ODS Excel Writer Test",
+ ExcelWriterTransformMeta.class,
+ ExcelWriterTransformData.class);
+ when(mockHelper.logChannelFactory.create(
+ org.mockito.ArgumentMatchers.any(),
+ org.mockito.ArgumentMatchers.any(ILoggingObject.class)))
+ .thenReturn(mockHelper.iLogChannel);
+
+ meta = new ExcelWriterTransformMeta();
+ meta.setDefault();
+ meta.getFile().setExtension(ExcelWriterOutputFormat.EXT_ODS);
+ meta.getFile().setSheetname(SHEET_NAME);
+ meta.setHeaderEnabled(true);
+
+ data = new ExcelWriterTransformData();
+ data.realSheetname = SHEET_NAME;
+ data.createNewFile = true;
+ data.createNewSheet = true;
+ data.inputRowMeta = new org.apache.hop.core.row.RowMeta();
+ data.inputRowMeta.addValueMeta(new ValueMetaString("name"));
+ data.inputRowMeta.addValueMeta(new ValueMetaInteger("count"));
+
+ ExcelWriterOutputField nameField = new ExcelWriterOutputField();
+ nameField.setName("name");
+ ExcelWriterOutputField countField = new ExcelWriterOutputField();
+ countField.setName("count");
+ List<ExcelWriterOutputField> fields = new ArrayList<>();
+ fields.add(nameField);
+ fields.add(countField);
+ meta.setOutputFields(fields);
+
+ data.fieldnrs = new int[] {0, 1};
+ data.linkfieldnrs = new int[] {-1, -1};
+ data.commentfieldnrs = new int[] {-1, -1};
+ data.commentauthorfieldnrs = new int[] {-1, -1};
+
+ transform =
+ spy(
+ new ExcelWriterTransform(
+ mockHelper.transformMeta,
+ meta,
+ data,
+ 0,
+ mockHelper.pipelineMeta,
+ mockHelper.pipeline));
+ }
+
+ @AfterEach
+ void cleanUp() {
+ mockHelper.cleanUp();
+ }
+
+ @Test
+ void testWriteOdsWithHeaderAndData() throws Exception {
+ File outputFile = new File(tempDir, "output.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ assertTrue(transform.init());
+
+ transform.prepareNextOutputFile(new Object[] {"alpha", 42L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"alpha", 42L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertEquals("name", getCellText(table, 0, 0));
+ assertEquals("count", getCellText(table, 1, 0));
+ assertEquals("alpha", getCellText(table, 0, 1));
+ assertEquals("42.0", getCellText(table, 1, 1));
+ }
+ }
+
+ @Test
+ void testAppendToExistingOds() throws Exception {
+ File outputFile = new File(tempDir, "append.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ assertTrue(transform.init());
+
+ transform.prepareNextOutputFile(new Object[] {"first", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"first", 1L});
+ transform.closeFiles();
+
+ meta.setAppendLines(true);
+
meta.getFile().setIfFileExists(ExcelWriterTransformMeta.IF_FILE_EXISTS_REUSE);
+
meta.getFile().setIfSheetExists(ExcelWriterTransformMeta.IF_SHEET_EXISTS_REUSE);
+ meta.setHeaderEnabled(false);
+ data.createNewFile = false;
+ data.createNewSheet = false;
+ data.usedFiles.clear();
+
+ transform.prepareNextOutputFile(new Object[] {"second", 2L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"second", 2L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertEquals("name", getCellText(table, 0, 0));
+ assertEquals("first", getCellText(table, 0, 1));
+ assertEquals("second", getCellText(table, 0, 2));
+ }
+ }
+
+ @Test
+ void testStyleCellReference() throws Exception {
+ File templateFile = new File(tempDir, "styled-template.ods");
+ try (OdfSpreadsheetDocument template =
OdfSpreadsheetDocument.newSpreadsheetDocument()) {
+ OdfTable templateTable = template.getTableList().get(0);
+ templateTable.setTableName(SHEET_NAME);
+ OdfTableCell styleSource = templateTable.getCellByPosition(1, 0);
+ styleSource.setCellBackgroundColor(Color.RED);
+ styleSource.setStringValue("style-source");
+ template.save(templateFile);
+ }
+
+ File outputFile = new File(tempDir, "styled-output.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.getTemplate().setTemplateEnabled(true);
+ meta.getTemplate().setTemplateFileName(templateFile.getAbsolutePath());
+ data.realTemplateFileName = templateFile.getAbsolutePath();
+ meta.getOutputFields().get(0).setStyleCell("B1");
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {"styled-value", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"styled-value", 1L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ OdfTableCell writtenCell = table.getCellByPosition(0, 0);
+ OdfTableCell referenceCell = table.getCellByPosition(1, 0);
+ assertEquals("styled-value", writtenCell.getDisplayText());
+ assertEquals(referenceCell.getStyleName(), writtenCell.getStyleName());
+ assertNotNull(writtenCell.getStyleName());
+ }
+ }
+
+ @Test
+ void testHyperlinkAndComment() throws Exception {
+ File outputFile = new File(tempDir, "link-comment.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+
+ data.inputRowMeta = new org.apache.hop.core.row.RowMeta();
+ data.inputRowMeta.addValueMeta(new ValueMetaString("label"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("url"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("note"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("author"));
+
+ ExcelWriterOutputField labelField = new ExcelWriterOutputField();
+ labelField.setName("label");
+ labelField.setHyperlinkField("url");
+ labelField.setCommentField("note");
+ labelField.setCommentAuthorField("author");
+ meta.setOutputFields(List.of(labelField));
+
+ data.fieldnrs = new int[] {0};
+ data.linkfieldnrs = new int[] {1};
+ data.commentfieldnrs = new int[] {2};
+ data.commentauthorfieldnrs = new int[] {3};
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(
+ new Object[] {"Hop", "https://hop.apache.org";, "A note", "Tester"});
+ transform.writeNextLine(
+ data.currentWorkbookDefinition,
+ new Object[] {"Hop", "https://hop.apache.org";, "A note", "Tester"});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ OdfTableCell cell = table.getCellByPosition(0, 0);
+ TextAElement link =
+ (TextAElement)
cell.getOdfElement().getElementsByTagName("text:a").item(0);
+ assertNotNull(link);
+ assertEquals("Hop", link.getTextContent());
+ assertEquals("https://hop.apache.org";, link.getXlinkHrefAttribute());
+
+ OfficeAnnotationElement annotation =
+ (OfficeAnnotationElement)
+
cell.getOdfElement().getElementsByTagName("office:annotation").item(0);
+ assertNotNull(annotation);
+ assertFalse(annotation.getTextContent().isBlank());
+ }
+ }
+
+ @Test
+ void testFormatMask() throws Exception {
+ File outputFile = new File(tempDir, "format.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+
+ data.inputRowMeta = new org.apache.hop.core.row.RowMeta();
+ data.inputRowMeta.addValueMeta(new ValueMetaNumber("amount"));
+
+ ExcelWriterOutputField amountField = new ExcelWriterOutputField();
+ amountField.setName("amount");
+ amountField.setFormat("0.00");
+ meta.setOutputFields(List.of(amountField));
+ data.fieldnrs = new int[] {0};
+ data.linkfieldnrs = new int[] {-1};
+ data.commentfieldnrs = new int[] {-1};
+ data.commentauthorfieldnrs = new int[] {-1};
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {12.3});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{12.3});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ OdfTableCell cell = table.getCellByPosition(0, 0);
+ assertEquals("12.30", cell.getDisplayText());
+ }
+ }
+
+ @Test
+ void testFormulaField() throws Exception {
+ File outputFile = new File(tempDir, "formula.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.setForceFormulaRecalculation(true);
+
+ data.inputRowMeta = new org.apache.hop.core.row.RowMeta();
+ data.inputRowMeta.addValueMeta(new ValueMetaNumber("left"));
+ data.inputRowMeta.addValueMeta(new ValueMetaNumber("right"));
+ data.inputRowMeta.addValueMeta(new ValueMetaString("totalFormula"));
+
+ ExcelWriterOutputField leftField = new ExcelWriterOutputField();
+ leftField.setName("left");
+ ExcelWriterOutputField rightField = new ExcelWriterOutputField();
+ rightField.setName("right");
+ ExcelWriterOutputField totalField = new ExcelWriterOutputField();
+ totalField.setName("totalFormula");
+ totalField.setFormula(true);
+ meta.setOutputFields(List.of(leftField, rightField, totalField));
+
+ data.fieldnrs = new int[] {0, 1, 2};
+ data.linkfieldnrs = new int[] {-1, -1, -1};
+ data.commentfieldnrs = new int[] {-1, -1, -1};
+ data.commentauthorfieldnrs = new int[] {-1, -1, -1};
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {10.0, 20.0, "=A1+B1"});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{10.0, 20.0, "=A1+B1"});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ OdfTableCell formulaCell = table.getCellByPosition(2, 0);
+ assertEquals("of:=.[A1]+.[B1]", formulaCell.getFormula());
+ assertNull(formulaCell.getOdfElement().getOfficeValueAttribute());
+ }
+ }
+
+ @Test
+ void testPushDownExistingCells() throws Exception {
+ File outputFile = new File(tempDir, "pushdown.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ assertTrue(transform.init());
+
+ transform.prepareNextOutputFile(new Object[] {"existing", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"existing", 1L});
+ transform.closeFiles();
+
+ meta.setHeaderEnabled(false);
+ meta.setRowWritingMethod(ExcelWriterTransformMeta.ROW_WRITE_PUSH_DOWN);
+
meta.getFile().setIfFileExists(ExcelWriterTransformMeta.IF_FILE_EXISTS_REUSE);
+
meta.getFile().setIfSheetExists(ExcelWriterTransformMeta.IF_SHEET_EXISTS_REUSE);
+ data.createNewFile = false;
+ data.createNewSheet = false;
+ data.shiftExistingCells = true;
+ data.usedFiles.clear();
+
+ transform.prepareNextOutputFile(new Object[] {"inserted", 2L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"inserted", 2L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertEquals("inserted", getCellText(table, 0, 0));
+ assertEquals("existing", getCellText(table, 0, 1));
+ }
+ }
+
+ @Test
+ void testTemplateSheetCloneWithPushDown() throws Exception {
+ File templateFile = new File(tempDir, "sheet-template.ods");
+ String templateSheetName = "TemplateSheet";
+ try (OdfSpreadsheetDocument template =
OdfSpreadsheetDocument.newSpreadsheetDocument()) {
+ OdfTable templateTable = template.getTableList().get(0);
+ templateTable.setTableName(templateSheetName);
+ templateTable.getCellByPosition(0, 0).setStringValue("header");
+ templateTable.getCellByPosition(0, 1).setStringValue("footer");
+ template.save(templateFile);
+ }
+
+ File outputFile = new File(tempDir, "cloned-sheet.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.setRowWritingMethod(ExcelWriterTransformMeta.ROW_WRITE_PUSH_DOWN);
+ meta.getTemplate().setTemplateEnabled(true);
+ meta.getTemplate().setTemplateFileName(templateFile.getAbsolutePath());
+ meta.getTemplate().setTemplateSheetEnabled(true);
+ meta.getTemplate().setTemplateSheetHidden(true);
+ meta.getTemplate().setTemplateSheetName(templateSheetName);
+ data.realTemplateFileName = templateFile.getAbsolutePath();
+ data.realTemplateSheetName = templateSheetName;
+
+ assertTrue(transform.init());
+ data.shiftExistingCells = true;
+ transform.prepareNextOutputFile(new Object[] {"row-data", 5L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"row-data", 5L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable outputTable = document.getTableByName(SHEET_NAME);
+ assertNotNull(outputTable);
+ assertEquals("row-data", getCellText(outputTable, 0, 0));
+ assertEquals("header", getCellText(outputTable, 0, 1));
+ assertEquals("footer", getCellText(outputTable, 0, 2));
+
+ OdfTable hiddenTemplate = document.getTableByName(templateSheetName);
+ assertNotNull(hiddenTemplate);
+ assertEquals(
+ "collapse",
+ hiddenTemplate
+ .getOdfElement()
+ .getAttributeNS(
+
org.odftoolkit.odfdom.dom.OdfDocumentNamespace.TABLE.getUri(), "visibility"));
+ }
+ }
+
+ @Test
+ void testMakeActiveSheet() throws Exception {
+ File outputFile = new File(tempDir, "active.ods");
+ try (OdfSpreadsheetDocument seed =
OdfSpreadsheetDocument.newSpreadsheetDocument()) {
+ seed.getTableList().get(0).setTableName("Other");
+ OdfTable outputSeed =
org.odftoolkit.odfdom.doc.table.OdfTable.newTable(seed, 1, 1);
+ outputSeed.setTableName(SHEET_NAME);
+ seed.save(outputFile);
+ }
+
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.setMakeSheetActive(true);
+
meta.getFile().setIfFileExists(ExcelWriterTransformMeta.IF_FILE_EXISTS_REUSE);
+
meta.getFile().setIfSheetExists(ExcelWriterTransformMeta.IF_SHEET_EXISTS_REUSE);
+ data.createNewFile = false;
+ data.createNewSheet = false;
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {"active", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"active", 1L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ assertEquals(SHEET_NAME, OdsTableHelper.getActiveTableName(document));
+ }
+ }
+
+ @Test
+ void testAutoSizeColumns() throws Exception {
+ File outputFile = new File(tempDir, "autosize.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.getFile().setAutosizecolums(true);
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {"short", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"much-longer-value", 2L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertTrue(table.getColumnByIndex(0).isOptimalWidth());
+ assertTrue(table.getColumnByIndex(1).isOptimalWidth());
+ }
+ }
+
+ @Test
+ void testProtectSheet() throws Exception {
+ File outputFile = new File(tempDir, "protected.ods");
+ doReturn(outputFile.getAbsolutePath()).when(transform).buildFilename(0);
+ meta.setHeaderEnabled(false);
+ meta.getFile().setProtectsheet(true);
+ meta.getFile().setPassword("secret");
+
+ assertTrue(transform.init());
+ transform.prepareNextOutputFile(new Object[] {"locked", 1L});
+ transform.writeNextLine(data.currentWorkbookDefinition, new Object[]
{"locked", 1L});
+ transform.closeFiles();
+
+ try (OdfSpreadsheetDocument document =
OdfSpreadsheetDocument.loadDocument(outputFile)) {
+ OdfTable table = document.getTableByName(SHEET_NAME);
+ assertTrue(table.getOdfElement().getTableProtectedAttribute());
+ assertEquals(
+ "5en6G6MezRroT3XKqkdPOmY/BfQ=",
table.getOdfElement().getTableProtectionKeyAttribute());
+ }
+ }
+
+ private String getCellText(OdfTable table, int col, int row) throws
Exception {
+ OdfTableCell cell = table.getCellByPosition(col, row);
+ return cell.getDisplayText();
+ }
+}
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverterTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverterTest.java
new file mode 100644
index 0000000000..7b35530358
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormatConverterTest.java
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+class OdsFormatConverterTest {
+
+ @Test
+ void convertsCommonExcelDateTokens() {
+ assertEquals("YYYY-MM-DD", OdsFormatConverter.toOdfFormat("yyyy-MM-dd"));
+ }
+}
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverterTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverterTest.java
new file mode 100644
index 0000000000..5709a3cc5f
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ods/OdsFormulaConverterTest.java
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelwriter.ods;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import org.junit.jupiter.api.Test;
+
+class OdsFormulaConverterTest {
+
+ @Test
+ void convertsExcelFormulaPrefix() {
+ assertEquals("of:=.[A1]+.[B2]",
OdsFormulaConverter.toOdfFormula("=A1+B2"));
+ }
+
+ @Test
+ void convertsSumRange() {
+ assertEquals("of:=SUM([.A1:.A2])",
OdsFormulaConverter.toOdfFormula("=SUM(A1:A2)"));
+ }
+
+ @Test
+ void keepsOpenFormulaUntouched() {
+ assertEquals("of:=.[A1]+.[A2]",
OdsFormulaConverter.toOdfFormula("of:=.[A1]+.[A2]"));
+ }
+
+ @Test
+ void convertsSheetReference() {
+ assertEquals("of:=Data.[A1]+.[A2]",
OdsFormulaConverter.toOdfFormula("=Data!A1+A2"));
+ }
+}
