miachm/sods

A simple Java library for handle ODS (Open Document Spreadsheet, compatible with Excel and Libreoffice)

sods's Issues

Support writing strings with line breaks

Consider this example of writing a spreadsheet that contains a string with a line break:

import com.github.miachm.sods.*;
import java.io.*;

public class Main {
    public static void main(String[] args) throws IOException {
        Sheet sheet = new Sheet("Test", 1, 1);
        sheet.getDataRange().setValue("a\nb");
        SpreadSheet spread = new SpreadSheet();
        spread.appendSheet(sheet);
        spread.save(new File("out.ods"));
    }
}

When opening out.ods, we see that LibreOffice actually renders a space instead of the newline:

Looking into the file, this XML defines the cell's value:

<table:table-cell office:value-type="string" office:string-value="a
b"><text:p>a</text:p><text:p>b</text:p></table:table-cell>

I've observed that if the office:string-value attribute is removed, LibreOffice correctly renders the newline:

So the issue seems to be that LibreOffice replaces newlines in the office:string-value attribute with spaces. Sadly, this behavior is not documented in the spec (http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1417668_253892949).

To find a way to encode newlines s.t. they are preserved, I manually typed the formula

="a
b"

into the spreadsheet and let LibreOffice save the file. This is what it wrote:

<table:table-cell table:formula="of:=&quot;a&#x0a;b&quot;" office:value-type="string" office:string-value="a&#x0a;b" calcext:value-type="string">

On first sight, it appears as if encoding \n as the entity 
 in the office:string-value attribute solves the issue. However, when removing the table:formula attribute, the newline disappears again. So it seems like the formula is what actually enforces the newline here.

So all in all, I think the only solution to this problem is to drop the office:string-value attribute. Seeing as LibreOffice doesn't even write it itself for plain non-formulaic content, does it really need to be written by SODS?

Center text vertically in a cell

Hi, first of all thanks for this tool :)

It looks like it's not possible to center a cell vertically.
In the picture the 2 cell are been centered using the libreoffice's tool

Do you know how hard is to implement that? I'm might add this feature if I have time. Could you point me to the right portion of the code that I should modify?

imports in code example

Not sure if this is the right place for this, but the imports in the code example include ".spreadsheet.", which should be removed as this package does not exist anymore in the latest release.

OutOfMemoryError: Java heap space

version:1.1.1

Memory overflow when reading 500 records. Stack info:

Caused by: java.lang.OutOfMemoryError: Java heap space
at com.github.miachm.sods.Sheet.insertRowsBefore(Sheet.java:321)
at com.github.miachm.sods.Sheet.insertRowsAfter(Sheet.java:333)
at com.github.miachm.sods.Sheet.appendRows(Sheet.java:51)
at com.github.miachm.sods.Sheet.appendRow(Sheet.java:43)
at com.github.miachm.sods.OdsReader.processTable(OdsReader.java:256)
at com.github.miachm.sods.OdsReader.processSpreadsheet(OdsReader.java:205)
at com.github.miachm.sods.OdsReader.iterateFilesEntries(OdsReader.java:198)
at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:77)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:43)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:35)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:52)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:39)
at com.center.module.transfer.util.ODSParserUtils.getSpreadSheet(ODSParserUtils.java:82)

ods file generate by pyexcel-ods failed to read

Hi Miachm,

Lately I'm trying to use pyexel-ods to generate ods file, and use java SODS tool to read it.

However, I'm facing problem with below error message :
// ------------------------------------------------------- //
Exception in thread "main" java.lang.IndexOutOfBoundsException: Range goes out of
bounds: (end_of_range: 1, maxcolumns in sheet: 0
at com.github.miachm.sods.Range.(Range.java:31)
at com.github.miachm.sods.Sheet.getRange(Sheet.java:279)
at com.github.miachm.sods.Sheet.getRange(Sheet.java:250)
at com.github.miachm.sods.OdsReader.processCells(OdsReader.java:437)
at com.github.miachm.sods.OdsReader.processTable(OdsReader.java:330)
at com.github.miachm.sods.OdsReader.processSpreadsheet(OdsReader.java:262)
at com.github.miachm.sods.OdsReader.iterateFilesEntries(OdsReader.java:255)
at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:80)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:46)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:38)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:52)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:39)
at BasicUsage.main(BasicUsage.java:17)
// ------------------------------------------------------- //

// ----------------------- pyexcel-ods script generate ods file ------------------------- //
from collections import OrderedDict
from pyexcel_ods import save_data

    data = OrderedDict() # from collections import OrderedDict
    data.update({"Sheet 1": [[1, 2, 3], [4, 5, 6]]})
    data.update({"Sheet 2": [["row 1", "row 2", "row 3"]]})
    save_data("Jay.ods", data)

// ---------------------------------------------------------------------------------------- //

Attachment will be the ods file caused above issue

Jay.ods

Possibility to set date/time pattern

Actually I'm getting this output for a LocalDate:

02/05/2020 00:00:00

It'll would be interesting that same valut outputs only the date.

Cell that contains <![CDATA[xxx]]> is not interpreted

Hello,
I am trying to read a file produced by Excel and the header cells contains the <![CDATA markup
The headers basically looks like that (albeit simplified)

<table:table-header-rows>
  <table:table-row table:style-name="ro1">
  <table:table-cell table:style-name="ce1" office:value-type="string">
    <text:p><![CDATA[Column 1]]></text:p>
  </table:table-cell>
  <table:table-cell table:style-name="ce1" office:value-type="string">
    <text:p><![CDATA[Column 2]]></text:p>
  </table:table-cell>
  <table:table-cell table:style-name="ce1" office:value-type="string">
    <text:p><![CDATA[Column 3]]></text:p>
  </table:table-cell>
  </table:table-row>
</table:table-header-rows>

However in the sods SpreadSheet created with the file, the first row only contains null column. The other rowns are fine since they don´t have the cdata tag.

I have re-saved with LibreOffice and this time the cdata markup is gone and sods reads my file just fine.

I thus conclude that the cdata is what is preventing the cell values from being loaded.
After looking it up it turns out that the cdata markup marks data that contains character that could be interpreted as xml but should not.
Thus I think sods should be able interpret my cells as text values.

SODS version 1.6.2

As for providing a full file, sadly the file I have contains confidential data and I cannot reproduce the bug myself while trying to create a clean speadsheet on Excel

Wrong detection empty cells

Hi, bug with file.
When i creating SpreadSheet from this file. It creates million cells and styles in it. Because detecting empty cells is incorrect.
this is loadded spreadSheet in debugger. Attention to dimension.

And this is memory snapshot screenshot in visualVM

Will be nice if answer me.

Allow setting default column cell style in written sheets

It's often useful to specify the cell style of an entire column, especially when the user is expected to add new rows himself. Thankfully, ODS makes this very easy: just add the table:default-cell-style-name attribute to the table:table-column element:

<table:table-column ... table:default-cell-style-name="cel0"></table:table-column>

API-wise, this could be integrated via a new method Sheet::setDefaultColumnCellStyle(int, Style). Looking at the current backend code, adding the feature should be straightforward.

As with my other suggestions, I would be very happy to hear your thoughts! Thank you a lot for your amazing work :)

java.lang.NumberFormatException: For input string: "10.5"

It seems that SODS have a problem with parsing spritesheet where font is 10.5 point

   InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("test.ods");
   try {
        SpreadSheet spread = new SpreadSheet(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

cause:
java.lang.NumberFormatException: For input string: "10.5" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Integer.parseInt(Integer.java:580) at java.lang.Integer.parseInt(Integer.java:615) at com.github.miachm.sods.OdsReader.readCellStyleEntry(OdsReader.java:147) at com.github.miachm.sods.OdsReader.iterateStyleEntries(OdsReader.java:95) at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:74) at com.github.miachm.sods.OdsReader.load(OdsReader.java:43) at com.github.miachm.sods.OdsReader.load(OdsReader.java:35) at com.github.miachm.sods.SpreadSheet.<init>(SpreadSheet.java:52)

Version: 1.2.2
reproducible: yes

something like this cause the problem i think: style:font-size-complex="10.5pt"

Try to parse attached file
link: https://drive.google.com/file/d/1ZrSObMqqxD70Bo0PfG-DrML_daEgjFsj/view?usp=sharing

OfficeCurrency#equals throws NullPointerException when currency is null

It's me again 😃

After the pull-request #48 the default currency was set to null instead of non-null currency (e.g : locale default currency). That's your choice but this creates a regression on OfficeCurrency#equals method because variable that.currency can be null.

Here is a simple JUnit test for this use case

@Test
void testOfficeCurrencyEqualsWithNullCurrency() {
  final OfficeCurrency nonNullCurrency = new OfficeCurrency(Currency.getInstance(Locale.getDefault()), 5D);
  final OfficeCurrency nullCurrency = new OfficeCurrency(null, 5D);
  Assertions.assertFalse(nonNullCurrency.equals(nullCurrency));
}

Result :

java.lang.NullPointerException
	at com.github.miachm.sods.OfficeCurrency.equals(OfficeCurrency.java:61)

Issues when opened in Microsoft Excel

This issue is related to this discussion.

Many incoherences remain when the .ods files generated with SODS are opened in Microsoft Excel.

I'll focus my example on 3 columns of the following generated file: sodsExample.ods

Here, the file is opened in LibreOffice:

And here how it looks in Excel (without any edit, it is the exact same file):

We can notice that some cells don't keep their original value once opened in Excel, and they can even be duplicated. I have no idea why to be honest :/ Hopefully, you might !

Please tell me if you need anything more,
Matthieu

force currency display with 2 decimal

HI
didn't fine any function to custom write currency style.
it is a bug or other?

Problem with cross sheets formula, am I doing it wrong ?

Hello

first, let me thanks for this project, I have been searching for something equivalent in .net and have given up so I am coding in Java with yours, it's easy to use and efficient.

I am running into a strange problem.
If I define a formula that does a very simple sum with data from the same sheet, it works properly.
If I define exactly the same formula but taking the data from another sheet, it just does not compute the result.

Sheet sheet = spread.getSheet("Operations"); Range range = sheet.getRange("C2"); range.setValue(13.42); sheet.getRange("C3").setValue(42.65); sheet.getRange("E3").setFormula("SUM(C:C)"); // works fine spread.getSheet("Calculs").getRange("B1").setFormula("SUM(Operations!C:C)"); // the sum is not computed

here is the formula in the generated file : =SUM(operations C:C) instead of =SUM(operations!C:C)
note the missing !

I have tried different ways to write it, the result is always the same : the result is not computed. I can still open the file and rewrite the ! and it will do the job, but I need automation.

Am I doing something wrong ?

Thank you

Regression: Cannot open file in 1.5.0

Timelog.ods

Exception in thread "main" java.lang.IndexOutOfBoundsException: Range goes out of bounds: (end_of_range: 1024, maxcolumns in sheet: 35
	at com.github.miachm.sods.Range.<init>(Range.java:37)
	at com.github.miachm.sods.Sheet.getRange(Sheet.java:407)
	at com.github.miachm.sods.OdsReader.processCells(OdsReader.java:459)
	at com.github.miachm.sods.OdsReader.processTable(OdsReader.java:347)
	at com.github.miachm.sods.OdsReader.processSpreadsheet(OdsReader.java:280)
	at com.github.miachm.sods.OdsReader.iterateFilesEntries(OdsReader.java:273)
	at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:81)
	at com.github.miachm.sods.OdsReader.load(OdsReader.java:46)
	at com.github.miachm.sods.OdsReader.load(OdsReader.java:38)
	at com.github.miachm.sods.SpreadSheet.<init>(SpreadSheet.java:52)
	at com.github.miachm.sods.SpreadSheet.<init>(SpreadSheet.java:39)
	at Main.execute(Main.java:46)

        SpreadSheet spread = new SpreadSheet(new File("/home/.../Timelog.ods"));

Works in 1.4.0

Possible error when reading the font size

Hi, reading an ods file I have encountered a possible error reading the fontsize, it expects an int value and it is arriving a decimal one. The class is OdsReader.java line 147

Publish with Module support in Maven Repository

Please publish a version with module support.

I have the following error when I use your maven dependency :

/usr/lib/jvm/jre-17-openjdk/bin/java -Dmaven.multiModuleProjectDirectory=/home/user/ods_test -Dmaven.home=/home/user/idea-IC-213.6461.79/plugins/maven/lib/maven3 -Dclassworlds.conf=/home/user/idea-IC-213.6461.79/plugins/maven/lib/maven3/bin/m2.conf -Dmaven.ext.class.path=/home/user/idea-IC-213.6461.79/plugins/maven/lib/maven-event-listener.jar -javaagent:/home/user/idea-IC-213.6461.79/lib/idea_rt.jar=36873:/home/user/idea-IC-213.6461.79/bin -Dfile.encoding=UTF-8 -classpath /home/user/idea-IC-213.6461.79/plugins/maven/lib/maven3/boot/plexus-classworlds-2.6.0.jar:/home/user/idea-IC-213.6461.79/plugins/maven/lib/maven3/boot/plexus-classworlds.license org.codehaus.classworlds.Launcher -Didea.version=2022.1 org.apache.maven.plugins:maven-jlink-plugin:3.1.0:jlink
[INFO] Scanning for projects...
[INFO] 
[INFO] -----------------------< org.atis.ods:ods_test >------------------------
[INFO] Building ods_test 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- maven-jlink-plugin:3.1.0:jlink (default-cli) @ ods_test ---
[INFO]  -> module: com.github.miachm.sods ( /home/user/.m2/repository/com/github/miachm/sods/SODS/1.4.0/SODS-1.4.0.jar )
[ERROR] 
[ERROR] Error: automatic module cannot be used with jlink: com.github.miachm.sods from file:///home/user/.m2/repository/com/github/miachm/sods/SODS/1.4.0/SODS-1.4.0.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  0.933 s
[INFO] Finished at: 2022-04-28T11:38:38+02:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-jlink-plugin:3.1.0:jlink (default-cli) on project ods_test: 
[ERROR] Exit code: 1
[ERROR] Command line was: jlink [--module-path, /home/user/.m2/repository/com/github/miachm/sods/SODS/1.4.0/SODS-1.4.0.jar, --add-modules, com.github.miachm.sods, --output, /home/user/ods_test/target/maven-jlink/default]
[ERROR] 
[ERROR] 
[ERROR] -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

Process finished with exit code 1

I am using the following versions of java and Maven :

[user@centos ~]$ java -version
openjdk version "17.0.2" 2022-01-18 LTS
OpenJDK Runtime Environment 21.9 (build 17.0.2+8-LTS)
OpenJDK 64-Bit Server VM 21.9 (build 17.0.2+8-LTS, mixed mode, sharing)
[user@centos ~]$ JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.2.0.8-15.el8.x86_64 mvn --version
Apache Maven 3.5.4 (Red Hat 3.5.4-5)
Maven home: /usr/share/maven
Java version: 17.0.2, vendor: Red Hat, Inc., runtime: /usr/lib/jvm/java-17-openjdk-17.0.2.0.8-15.el8.x86_64
Default locale: fr_FR, platform encoding: UTF-8
OS name: "linux", version: "4.18.0-383.el8.x86_64", arch: "amd64", family: "unix"

After some investigation, I see that there is a module-info.java, but the version 1.4.0 was published before the commit.
But even if you publish a new release, it is seems that the module will be not present (I do a test with JitPack, the profile JDK9 is not activated by default).

Edit : I am using Maven embedded in Intellij (maven 3.8.1), not from my system.

I have recompiled myself your SODS code and install it to my local maven repository (~/.m2), and I am now able to build the JLINK image.

Please release a version with module in the Maven repository.

Leading space

It seems that for cells with text with leading space (or spaces) the lib returns null value.
Example:
<table:table-cell office:value-type="string" calcext:value-type="string">text:ptext:s/Test </text:p></table:table-cell>
Value of that cell is null.

Rename sheet name

I am using a template file for my application with a default sheet name. I need to rename the sheet name when I write my new file. So I have added a setName method in the Sheet class.

    /**
     * Rename this sheet
     * @param newName The new name of the sheet
     */
    public void setName(String newName) {
        this.name = newName;
    }

Is it possible to add this method ?

Thank you in advance.

org.w3c.dom.DOMException: NAMESPACE_ERR: An attempt is made to create or change an object in a way which is incorrect with regard to namespaces.

When woodstox was in the classpath (or deployed to JBoss EAP application server), SDOS encountered an issue outputting attributes with namespaces, such as "office:version".

Here's the complete exception stack trace:

Tests run: 185, Failures: 5, Errors: 0, Skipped: 0, Time elapsed: 6.205 sec <<< FAILURE!
testWriteBoolean(com.github.miachm.sods.OfficeValueTypeTest)  Time elapsed: 0.565 sec  <<< FAILURE!
org.w3c.dom.DOMException: NAMESPACE_ERR: An attempt is made to create or change an object in a way which is incorrect with regard to namespaces.
	at com.sun.org.apache.xerces.internal.dom.CoreDocumentImpl.checkDOMNSErr(CoreDocumentImpl.java:2546)
	at com.sun.org.apache.xerces.internal.dom.AttrNSImpl.setName(AttrNSImpl.java:117)
	at com.sun.org.apache.xerces.internal.dom.AttrNSImpl.<init>(AttrNSImpl.java:78)
	at com.sun.org.apache.xerces.internal.dom.CoreDocumentImpl.createAttributeNS(CoreDocumentImpl.java:2163)
	at com.sun.org.apache.xerces.internal.dom.ElementImpl.setAttributeNS(ElementImpl.java:659)
	at com.ctc.wstx.dom.DOMOutputElement.addAttribute(DOMOutputElement.java:210)
	at com.ctc.wstx.dom.WstxDOMWrappingWriter.outputAttribute(WstxDOMWrappingWriter.java:561)
	at com.ctc.wstx.dom.WstxDOMWrappingWriter.writeAttribute(WstxDOMWrappingWriter.java:251)
	at com.github.miachm.sods.OfficeValueType$1.write(OfficeValueType.java:41)
	at com.github.miachm.sods.OfficeValueTypeTest.lambda$assertWrite$0(OfficeValueTypeTest.java:217)
	at com.github.miachm.sods.OfficeValueTypeTest$NodeAssert.of(OfficeValueTypeTest.java:286)
	at com.github.miachm.sods.OfficeValueTypeTest.assertWrite(OfficeValueTypeTest.java:214)
	at com.github.miachm.sods.OfficeValueTypeTest.testWriteBoolean(OfficeValueTypeTest.java:60)

Refer to https://github.com/miachm/SODS/pull/78/checks

Add support for macros

Hello,

one feature I would need is to preserve any macro inside the ODS file. As far as I have seen, they seem to be stored in folders named with the langage used for the macro (Basic in my case).

Would it be possible to store them somewhere when reading an ODS file to be able to restore them when saving the spreadsheet as a new file ?

For now I'll be handling this be injecting them manually in the produced ODS, but it would be much usefull to handle this directly in SODS.

Thank you

Make `XMLInputFactory` configurable.

We require the XMLInputFactory configuration capability as described in #39.

Context

Our application is deployed on JBoss EAP, which incorporates Woodstox as its StAX implementation. While attempting to parse .ods files with a Dtd declared in metadata.xml (which was generated by using JasperReports) like the following:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE manifest:manifest PUBLIC "-//OpenOffice.org//DTD Manifest 1.0//EN" "Manifest.dtd">
<manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0">
    ...

We encountered the following exception:

com.github.miachm.sods.NotAnOdsException: (previously java.io.FileNotFoundException) /Manifest.dtd (No such file or directory)
 at [row,col {unknown-source}]: [2,92]

	at com.ctc.wstx.sr.StreamScanner.constructWfcException(StreamScanner.java:634)
	at com.ctc.wstx.sr.StreamScanner.throwParseError(StreamScanner.java:504)
	at com.ctc.wstx.sr.ValidatingStreamReader.findDtdExtSubset(ValidatingStreamReader.java:464)
	at com.ctc.wstx.sr.ValidatingStreamReader.finishDTD(ValidatingStreamReader.java:326)
	at com.ctc.wstx.sr.BasicStreamReader.skipToken(BasicStreamReader.java:3466)
	at com.ctc.wstx.sr.BasicStreamReader.nextFromProlog(BasicStreamReader.java:2089)
	at com.ctc.wstx.sr.BasicStreamReader.next(BasicStreamReader.java:1180)
	at com.github.miachm.sods.XmlReaderInstanceEventImpl.nextElement(XmlReaderInstanceEventImpl.java:46)
	at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:86)
      ...

Please note that the exception above occurred due to this StAX implementation trying to validate the XML against the missed DTD resource, and this issue has been encountered during the process of reading and parsing the .ods file.

Workaround

I can verify that when we follow the provided instructions to configure Woodstox not to perform validation, the .ods file can be successfully read:

--- a/src/com/github/miachm/sods/XmlReaderEventImpl.java	(revision d36c400d804f7eb4f7103addeb7533d6faa5042c)
+++ b/src/com/github/miachm/sods/XmlReaderEventImpl.java	(date 1693920428753)
@@ -13,6 +13,7 @@
     @Override
     public XmlReaderInstanceEventImpl load(InputStream in) throws IOException {
         try {
+            inputFactory.setProperty(XMLInputFactory.SUPPORT_DTD, Boolean.FALSE);
             reader = inputFactory.createXMLStreamReader(in);
             // Skip start of document
             try {

Discussion

Exploring ways to extend XMLInputFactory customization:

1. One approach is to adopt the suggestion outlined in #39 (comment)

https://github.com/miachm/SODS/issues/39#issuecomment-1039036961

2. Another strategy involves utilizing the ServiceLoader mechanism as demonstrated below:

        // Try to load XmlReader or use default XmlReaderEventImpl
        ServiceLoader<XmlReader> loader = ServiceLoader.load(XmlReader.class);
        Iterator<XmlReader> iterator = loader.iterator();
        if (iterator.hasNext()) {
            reader = iterator.next();
        } else {
            reader = new XmlReaderEventImpl();
        }

3. Or we can simply apply the mentioned patch directly to set XMLInputFactory.SUPPORT_DTD to true. This adjustment will ensure that JasperReports-generated ODS files function correctly on JBoss EAP without affecting any existing usages.

4. Are there any alternative approaches that should be considered?

Copyright Notice

Could you include a copyright notice somewhere in your distribution? A copyright line in the README would suffice.

nên bổ sung thêm Font cho ô tính, bảo vệ trang tính, bảo vệ ô tính

Tôi rất thích thư viện SODS (link: https://github.com/miachm/SODS) để đọc/ghi tệp ods. Tuy nhiên, tôi không tìm thấy thấy định dạng font chữ. Ví dụ: sheet.getRange(2,2).setFontName("Times New Roman");
và tôi muốn đặt mật khẩu bảo vệ sheet, bảo vệ ô tính, ví dụ:
sheet.setPassWord("PassWord"); hoặc sheet.setPassWord(""); //Mật khẩu bảo vệ sheet
sheet.getRange(2,2).setProtected(true); //Bảo vệ ô tính
Rất mong bạn sớm code bổ sung.
Xin cảm ơn!

Regression: LibreOffice complains about corrupt file from MRE

Consider this minimal reproducible example:

public class Main {
    public static void main(String[] args) throws IOException {
        Sheet sheet = new Sheet("Test", 3, 2);
        sheet.getDataRange().setValues(1, 2, 3, 4, 5, 6);
        SpreadSheet spread = new SpreadSheet();
        spread.appendSheet(sheet);
        spread.save(new File("/path/to/output.ods"));
    }
}

On version 1.4.0, this produces a file that LibreOffice 7.3.7.2 (latest STILL version, build from the Arch Linux repository) can read without any issues. From version 1.5.0 onward however (i.e., including 1.5.1 and 1.5.2), LibreOffice complains with the following message:

The file 'MRE.ods' is corrupt and therefore cannot be opened. LibreOffice can try to repair the file.

The corruption could be the result of document manipulation or of structural document damage due to data transmission.

We recommend that you do not trust the content of the repaired document.
Execution of macros is disabled for this document.

Should LibreOffice repair the file?

Clicking "Yes" then succeeds in opening the allegedly damaged file. While this technically works, it's of course not good user experience, so we're currently blocked from upgrading past SODS 1.4.0.

Unpacking and diffing two ODS files generated by the above MRE with SODS 1.4.0 and 1.5.0 respectively shows that there are just two differences between the two versions:

1. In content.xml, 1.4.0 writes <table:table-column></table:table-column><table:table-column></table:table-column>, while 1.5.0 writes <table:table-column table:number-columns-repeated="2"></table:table-column>.
2. 1.5.0 writes a new file styles.xml which contains <?xml version="1.0" encoding="UTF-8"?><office:document-styles xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-styles>. SODS 1.4.0 does not write this file.

Manually reverting each of these differences individually in the ODS file generated by SODS 1.5.0 shows that difference 2 is single-handedly responsible for the error message, as deleting the file from the archive gets rid of the error.

Sadly, I'm neither an expert on the ODS format nor have I managed to extract useful logging information from LibreOffice that would shine some light on why exactly this hiccup happens. Still, I hope you can reproduce this issue and have enough information to be able to fix it.

Thanks for sharing this handy library and all the best,
Felix

Possible error when reading cells with comments

Hi again, when reading a cell which has comments, when reading the cell value it concatenates the cell's value with the comment. Is this an error?

Thanks in advance

Greetings

Maven Central Repository

Add it to "Maven Central Repository".

Allow to enforce text data format in written sheets

Spreadsheet editors often interpret data input by the users in ways they are not supposed to. This can be prevented by setting the cell's data format to "text", or, as it is usually called, "@". For my use case, I'd really like to set this data format in the sheets written by SODS.

On the XML side, this is easy to do. Just define the following number:text-style element, and then reference it from cell styles:

<number:text-style style:name="textstyle">
  <number:text-content />
</number:text-style>
...
<style:style style:family="table-cell" style:name="cel0" style:data-style-name="textstyle" />

Implementation-wise, I'd suggest always writing the new textstyle style into the spreadsheet like you are already doing with <number:date-style style:name="datestyle"> at the moment. Then add an API like Style::setRawText(boolean) or, if you want a more forward compatible API, Style::setDataStyle(String) which only accepts "@" at this point. If this option is set by the user, the style:data-style-name="textstyle" attribute is added to the style's XML element.

I'd be very happy to hear your thoughts on this.

java.lang.OutOfMemoryError: Java heap space

Maven dependency：

com.github.miachm.sods
SODS
1.2.2

Memory overflow when the memory is less than 280m. Stack info:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.github.miachm.sods.Range.getValues(Range.java:231)
at com.ams.module.transfer.util.ODSParserUtils.getHeaderFromSheet(ODSParserUtils.java:92)
at com.ams.module.transfer.util.ODSParserUtils.getDataFromSheet(ODSParserUtils.java:111)
at com.ams.module.transfer.util.ODSParserUtils.getDataFromSpreadsheet(ODSParserUtils.java:68)
at com.ams.module.transfer.util.ODSParserUtils.main(ODSParserUtils.java:145)

ODS file size is only 25KB, only 50 lines of data. Why does it take so much memory to read files.

missing data for consequent duplicite lines

oo-ods-test.zip
Hello

I just try sods for first time and it seems there is bug (maybe I don't understant it)

I try to make scenario where create and read ods file line by line.

sk.antons.sods.CreateDoc

• reads ./src/data/duplicate-lines.csv
• copy content of csv to ./target/sods.ods

sk.antons.sods.ReadDoc

• reads ./target/sods.ods
• copy content to ./target/sods-output-copied.csv
• copy content to ./target/sods-output2-copied.csv (just other way of sods use)

scenario 1

• start CreateDoc
• start ReadDoc
• when I open ./target/sods.ods in libreoffice it seems ok
• when you look to ./target/sods-output-copied.csv or ./target/sods-output-copied2.csv data of duplicate consequent lines are mising (only lastone is present)

scenario 2

• start CreateDoc
• open ./target/sods.ods in libreoffice and save it
• start ReadDoc
• when I open ./target/sods.ods in libreoffice it seems ok
• when you look to ./target/sods-output-copied.csv or ./target/sods-output-copied.csv all data are present

So it seems that created ods file is somehow wrong for consequent reading by sods.

Thanks

Sods-1.2.2-snapshot supports office very well, but it is not compatible with open office.

Version :SODS-1.2.2-SNAPSHOT.jar.zip

Sods-1.2.2-snapshot supports office very well, but it is not compatible with open office.

When reading a file edited with Apache OpenOffice, there is a memory overflow.Stack info:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.github.miachm.sods.Cell.(Cell.java:6)
at com.github.miachm.sods.Sheet.checkLazyRowLoading(Sheet.java:306)
at com.github.miachm.sods.Sheet.setRowHeight(Sheet.java:502)
at com.github.miachm.sods.OdsReader.processTable(OdsReader.java:277)
at com.github.miachm.sods.OdsReader.processSpreadsheet(OdsReader.java:223)
at com.github.miachm.sods.OdsReader.iterateFilesEntries(OdsReader.java:216)
at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:77)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:43)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:35)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:52)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:39)
at com.center.test.ODSParserHelper.getDataFromSpreadsheet(ODSParserHelper.java:72)

My file download link is: https://github.com/lbyzx123/SODS/blob/master/alarm_config_rule_openoffice.ods

License declaration

Could you please clarify which License is correct?

GitHub Tag/ License file
https://github.com/miachm/SODS/blob/master/LICENSE

The Unlicense

or

pom.xml

    <licenses>
        <license>
            <name>The Apache License, Version 2.0</name>
            <url>http://www.apache.org/licenses/LICENSE-2.0.txt</url>
        </license>
    </licenses>

Excel doen't like files generated by SODS

https://stackoverflow.com/questions/66869342/excel-having-issues-with-an-ods-file-created-with-sods/73628160

Data format

Hello, I find Range.getValue() is a double type no matter what the format of the range in spreadsheet is. I set it to text, it is also double. I think it is a problem. By the way, can you refer functions of range to return type what I want. For example, Range.getIntValue will give me a integer no matter it is a text or double or other format of the range. Now I have to deal with null pointer and type converting.

Currency fields don't seem to work (format and larger values)

The formatting with currency fields don't seem to be displayed. I've tried the code in your ValueTypeTest.java and the resulting Spreadsheet is not formatted, it just displays -5. Also the values seem to be cut weirdly if they are greater than 999, e.g. try the following:

OfficeCurrency canada = new OfficeCurrency(Currency.getInstance(Locale.CANADA), -3050.5d);
OfficeCurrency eur = new OfficeCurrency(Currency.getInstance("EUR"), -2567.5d);
Sheet sheet = new Sheet("A", 1, 2);

sheet.getDataRange().setValues(canada, eur);
// ...

This results in a sheet displaying -3 and -2 for me...

Formulas and cell content

i don't know why, but it completely mess with my file,
i have a file with some simple formulas like this one:
"=$REGENTRADA.D5"
but after open and save as:
"=$regentradad5"
no idea how to make it work ok adn, also any cell with multiple lines are converted to single lines

"java.lang.OutOfMemoryError: GC overhead limit exceeded" when initializing SpreadSheet object with given ODS file

"java.lang.OutOfMemoryError: GC overhead limit exceeded" is thrown when initializing SpreadSheet object with given ODS file (SODS version 1.2.1)

Attached sods.oom.tar.gz contains the problematic ODS file and a tiny Groovy script reproducing the error.

Steps
Run Groovy script from the archive:

cd src
groovy brp-testdata-import.groovy

Result

Caught: java.lang.OutOfMemoryError: GC overhead limit exceeded
java.lang.OutOfMemoryError: GC overhead limit exceeded
at com.github.miachm.sods.Sheet.checkLazyRowLoading(Sheet.java:306)
at com.github.miachm.sods.Sheet.getCell(Sheet.java:296)
at com.github.miachm.sods.Range.iterateRange(Range.java:624)
at com.github.miachm.sods.Range.setStyle(Range.java:718)
at com.github.miachm.sods.OdsReader.processCells(OdsReader.java:413)
at com.github.miachm.sods.OdsReader.processTable(OdsReader.java:279)
at com.github.miachm.sods.OdsReader.processSpreadsheet(OdsReader.java:223)
at com.github.miachm.sods.OdsReader.iterateFilesEntries(OdsReader.java:216)
at com.github.miachm.sods.OdsReader.processContent(OdsReader.java:77)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:43)
at com.github.miachm.sods.OdsReader.load(OdsReader.java:35)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:52)
at com.github.miachm.sods.SpreadSheet.(SpreadSheet.java:39)
at brp-testdata-import.run(brp-testdata-import.groovy:4)

Environment

$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

$ groovy -v
Groovy Version: 2.5.9 JVM: 1.8.0_201 Vendor: Oracle Corporation OS: Linux

Notes
ODS file downloaded from https://www.rvig.nl/documenten/richtlijnen/2018/09/20/testdataset-persoonslijsten-proefomgevingen-gba-v

The character type cannot be output.

The character type cannot be output.
e.g.

public static void main(String[] args) throws Exception{
        SpreadSheet spreadSheet = new SpreadSheet();
        String projectPath = System.getProperty("user.dir");
        int rows = 3;
        int columns = 3;
        Sheet sheet = new Sheet("A", rows, columns);
        Range range = sheet.getRange(1, 1);
        range.setValue("ABC");
        spreadSheet.appendSheet(sheet);
        File file = new File(projectPath + "/src/main/resources/file/test2.ods");
        if (!file.exists()) {
            file.createNewFile();
        }
        spreadSheet.save(file);
    }

Is the string type not supported?

Range.getValue() returns LocalDate instead of Date

The JavaDoc for Range.getValue() says

Returns the value of the top-left cell in the range.
The values could be String, Float, Integer, OfficeCurrency, OfficePercentage or a Date
Empty cells returns a null object

On my first try of SODS (version 1.5.2), reading a cell with a date gave me a java.time.LocalDate instead of a Date.

I like this because I want a LocalDate and don't have to write a conversion Date->LocalDate, but the documentation is a bit off.

Is the resulting type depending on the cell formatting? I have explicitely set the cell format to tt.mm.jjjj (like yyyy-mm-dd but for German locale) in the spreadsheet.

Reading problem with empty cells

Hello,

There is a problem when attempting to read lines which contain empty cells and non empty cells.

Here is the content of the ods file :
:

The complete file

And this is the result of reading :

content | content | 
content | content | 
content | content | 

Please tell me if you need more informations.

Thanks

Let the user to set a custom date format

Currently, SODS is using the ISO format style.

Related to issue #17

Convert ods to csv - possible bug !

I try to read a sheet and convert it into a CSV file.

Code :

        String inputFile = "See attached file";
        SpreadSheet spread = new SpreadSheet(new File(inputFile));
        System.out.println("Number of sheets: " + spread.getNumSheets());

        List<Sheet> sheets = spread.getSheets();

        for (Sheet sheet : sheets) {
            System.out.println("In sheet " + sheet.getName());

            Range data = sheet.getDataRange();

            Object[][] values = data.getValues();

            for (Object[] row : values) {
                ArrayList forJoin = new ArrayList();
                for (Object value : row) {
                    if (value instanceof Double) {
                        value = value.toString();
                    }
                    forJoin.add(value);
                }
                System.out.println(String.join(",", forJoin));
            }
        }

It returns the following result :

Number of sheets: 1
In sheet fuller
id,lon,lat,nom_lieu,elephant_mer,baleine,cachalot,globicephal_noir,description
1.0,-72.063440,41.286780,New london,null,Départ le 15 juillet 1859.,null,null,null

instead of

Number of sheets: 1
In sheet fuller
id,lon,lat,nom_lieu,elephant_mer,baleine,cachalot,globicephal_noir,description
1.0,-72.063440,41.286780,New london,null,null,null,null,Départ le 15 juillet 1859.

There is an offset that seems to be related to the null values. Or maybe I'm not using the library properly.

Thanks.

example_ods.ods.zip

Incorrect row count

Hi

Frist of all thanks a lot for this software. I think I found a regression of #23.

Here is an image that shows the file I am using (2 sheets, both having contents on 2 by 2 Range A:1 - B:2, the rest of the file is empty. I even deleted all other rows and columns) and the place where I think the error is, is at OdsReader#311. The way I see it the error of incorrect cells count is being caused by the default behavior of just adding 100 row (numRow on line 312) causing my file being reported as having 103 rows when I call getLastRow() on it. I think the value of numRow should be set to 0 for that scenario.

As you can see at that point the values of numColumns and numRows are correctly describing the contents of that sheet on my file. I can and would like to do the MR, if you think that would be a valid fix. I'll start catching up with the CONTRIBUTING.md guidelines anyway.

Greetings

P.S: If my file is needed for further debugging, or any other info please let me know. I'll gladly provide it on any mean you need

Edit: I am using 1.4.0 version BTW

XMLParser problem when using poi-on-android and SODS at the same time

Hi there,
I was recently looking for a solution that'd help me with processing spreadsheet files, which wasn't an easy task.
For processing xls/xlsx files I settled wtih using a "poi-on-android" library, which puts together all the pieces and libraries necessary to "just work".
Now when I try to use SODS as a complementary library to parse ODS files, I run into couple of problems:

Running SODS completely standalone throws following error:
java.lang.NoClassDefFoundError: Failed resolution of: Ljavax/xml/stream/XMLInputFactory;
Which I'm able to solve like so and SODS is running just fine then:
implementation group: 'stax', name: 'stax', version: '1.2.0'

Running SODS side by side with poi-on-android without previous import throws the exact same error.

Running SODS side by side with poi-on-android, which is what I'd like to achieve, with the inclusion of stax library throws following error:
Process: com.moonglasses.odsimporttest, PID: 31140 java.lang.ExceptionInInitializerError at com.github.miachm.sods.OdsReader.<init>(OdsReader.java:19) at com.github.miachm.sods.OdsReader.load(OdsReader.java:37) at com.github.miachm.sods.SpreadSheet.<init>(SpreadSheet.java:52) at com.moonglasses.odsimporttest.ODSReader.read(ODSReader.java:35) at com.moonglasses.odsimporttest.MainActivity.lambda$initActivityLauncher$1$com-moonglasses-odsimporttest-MainActivity(MainActivity.java:65) at com.moonglasses.odsimporttest.MainActivity$$ExternalSyntheticLambda1.run(Unknown Source:6) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641) at java.lang.Thread.run(Thread.java:923) Caused by: java.lang.ClassCastException: com.fasterxml.aalto.stax.InputFactoryImpl cannot be cast to javax.xml.stream.XMLInputFactory at javax.xml.stream.XMLInputFactory.newInstance(XMLInputFactory.java:136) at com.github.miachm.sods.XmlReaderEventImpl.<clinit>(XmlReaderEventImpl.java:10) at com.github.miachm.sods.OdsReader.<init>(OdsReader.java:19)  at com.github.miachm.sods.OdsReader.load(OdsReader.java:37)  at com.github.miachm.sods.SpreadSheet.<init>(SpreadSheet.java:52)  at com.moonglasses.odsimporttest.ODSReader.read(ODSReader.java:35)  at com.moonglasses.odsimporttest.MainActivity.lambda$initActivityLauncher$1$com-moonglasses-odsimporttest-MainActivity(MainActivity.java:65)  at com.moonglasses.odsimporttest.MainActivity$$ExternalSyntheticLambda1.run(Unknown Source:6)  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)  at java.lang.Thread.run(Thread.java:923) 

The poi-on-android library itself requires a certain hack to make it find a suitable XML Parser, the recomedation is to use following code:
System.setProperty("org.apache.poi.javax.xml.stream.XMLInputFactory", "com.fasterxml.aalto.stax.InputFactoryImpl");
System.setProperty("org.apache.poi.javax.xml.stream.XMLOutputFactory", "com.fasterxml.aalto.stax.OutputFactoryImpl");
System.setProperty("org.apache.poi.javax.xml.stream.XMLEventFactory", "com.fasterxml.aalto.stax.EventFactoryImpl");

Using or not using this doesn't change the way SODS behaves. I was guessing I could maybe use this hack separately for poi and for SODS to convince both to use the XML Parser of their liking before I use them, but I'm just lost in all the stuff surrounding the topic of processing spreadsheet files on android in general.

Would someone more knowledgeable in the topic be so kind to maybe suggest some solutions please?