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 ?? **

Answer 1

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.

Answer 2

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);
  }
}

Answer 3

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;
}