how to read .ods document by java and jopendocument package

Question

I have .ods file and I want to read and display it by java program I used this program :

import java.io.File;
import java.io.IOException;

import org.jopendocument.dom.spreadsheet.MutableCell;
import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

public class ODSReader {
  public void readODS(File file)
  {
    Sheet sheet;
    try {
         //Getting the 0th sheet for manipulation| pass sheet name as string
         sheet = SpreadSheet.createFromFile(file).getSheet(0);

         //Get row count and column count
         int nColCount = sheet.getColumnCount();
         int nRowCount = sheet.getRowCount();

         System.out.println("Rows :"+nRowCount);
         System.out.println("Cols :"+nColCount);
         //Iterating through each row of the selected sheet
         MutableCell cell = null;
         for(int nRowIndex = 0; nRowIndex < nRowCount; nRowIndex++)
         {
           //Iterating through each column
           int nColIndex = 0;
           for( ;nColIndex < nColCount; nColIndex++)
           {
             cell = sheet.getCellAt(nColIndex, nRowIndex);
             System.out.print(cell.getValue()+ " ");
            }
            System.out.println();
          }

        } catch (IOException e) {
          e.printStackTrace();
        }
  }
  public static void main(String[] args) {
        //Creating File object of .ods file
        File file = new File("D:\\TestData\\test.ods");
        ODSReader objODSReader = new ODSReader();
        objODSReader.readODS(file);
  }
}

and the .ods file is that:

and the output appears like that:

> Date 
  Volume 
  Open 
  Low 
  High 
  Close 
  Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 7
    at org.jopendocument.dom.spreadsheet.Row.getCellAt(Unknown Source)
    at org.jopendocument.dom.spreadsheet.Row.getValidCellAt(UnknownSource)
    at org.jopendocument.dom.spreadsheet.Row.getMutableCellAt(Unknown
Source)
    at org.jopendocument.dom.spreadsheet.Table.getCellAt(Unknown Source)
    at com.spreadSheets.java.ODSReader.readODS(ODSReader.java:38)
    at com.spreadSheets.java.Main.main(Main.java:20)

**The QUESTION is how I could display the charachters,numbers,and Symbols using this jopendocument package and avoid or solve these exceptions ?? **

score 2 · Answer 1 · answered Dec 08 '18 at 08:18

I couldn't reproduce your error using your code. I think you should update to version 1.3 of jOpenDocument. I only made a spreadsheet of 5 lines to test your code. Nonetheless, it worked great.

Just one thing in your code though, you don't need to bring nColIndex outside of the "for" loop declaration.

Your code is great for ods files that only have 1 sheet but you may run into a problem if you have multiple sheets. I just modified your code a bit into a version that you could easily edit in the future to give the program the capability to work with spreadsheets that have multiple sheets that are similar in design.

import java.io.File;
import java.io.IOException;

import org.jopendocument.dom.spreadsheet.MutableCell;
import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

public class ODSReader {
  public void readODS(File file)
  {
    SpreadSheet spreadsheet;
    try {
         //Getting the 0th sheet for manipulation| pass sheet name as string

         spreadsheet = SpreadSheet.createFromFile(file);

         //Get row count and column count
         int nColCount = spreadsheet.getSheet(0).getColumnCount();
         int nRowCount = spreadsheet.getSheet(0).getRowCount();

         System.out.println("Rows :"+nRowCount);
         System.out.println("Cols :"+nColCount);
         //Iterating through each row of the selected sheet
         MutableCell cell = null;
         for(int nRowIndex = 0; nRowIndex < nRowCount; nRowIndex++)
         {
           //Iterating through each column
           for(int nColIndex = 0; nColIndex < nColCount; nColIndex++)
           {
             cell = spreadsheet.getSheet(0).getCellAt(nColIndex, nRowIndex);
             System.out.print(cell.getValue()+ " ");
            }
            System.out.println();
          }

        } catch (IOException e) {
          e.printStackTrace();
        }
  }
  public static void main(String[] args) {
        //Creating File object of .ods file
        File file = new File("test.ods");
        ODSReader objODSReader = new ODSReader();
        objODSReader.readODS(file);
  }
}

score 0 · Answer 2 · answered Mar 30 '18 at 08:08

0

sheet.getRowCount()

This give you a maximum number of rows in a sheet it's like 19534667,so you should not use it for this.In my project i manually add row and column count.

answered Mar 30 '18 at 08:08

Nusret Özateş

84
1
8

So, what you have used instead? I have the same problem.. getting millions of rows but there are only 1000 ones having data in them. – Giorgi Tsiklauri Mar 24 '19 at 01:58
Actually I just enter the number of rows manually because It was temporary code for me. But make it automatic I really don't know how to get true number. – Nusret Özateş Mar 25 '19 at 06:45
Seems like bug in library.. it takes all possible (rendered) rows and columns.. which are really huge numbers, whereas we need actual records. Ok, thanks. – Giorgi Tsiklauri Mar 25 '19 at 08:05

score 0 · Answer 3 · answered Sep 01 '23 at 17:10

if someone is using a maven project you can import the com.github.miachm.sods, you can see complete documentation here : https://com.github.miachm.sods

in this example i am reading a sample ods file with movie data in the first column

first import the maven dependency

    <dependency>
        <groupId>com.github.miachm.sods</groupId>
        <artifactId>SODS</artifactId>
        <version>1.6.2</version>
    </dependency>

take a look at this sample code to read the first column

 public static List<String> readColumnA(String filePath) {
    List<String> movieList = new ArrayList<>();
    try {
        SpreadSheet spread = new SpreadSheet(new File(filePath));
        //     System.out.println("Number of sheets: " + spread.getNumSheets());
        Sheet sheet = spread.getSheets().get(0);
        //     System.out.println("In sheet " + sheet.getName());
        Range movies = sheet.getDataRange();
        Object[][] movieColumn = movies.getValues();
        for (int i = 0; i < sheet.getMaxRows(); i++) {
        //optional    System.out.println(movieColumn[i][0].toString());
            movieList.add(movieColumn[i][0].toString());
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
    return movieList;
}

how to read .ods document by java and jopendocument package

3 Answers3