Read data from ODS (Open Document spreadsheet) file

Off-topic
, ,
1

Hi Team,

I am looking for a way to read data from an .ods (OpenDocument Spreadsheet) file within a Katalon Studio test script. My goal is to extract the data and store it in a List<Map<String, String>> format, where each map represents a row with column headers as keys and corresponding cell values as values.

Here’s what I need:

  • Read all rows and columns from the ODS file.
  • Store the data in a List where each entry is a Map<String, String>.
  • Column headers should be taken from the first row of the ODS file.

I explored using Apache POI, but it does not support .ods files directly. I also looked into odftoolkit, but faced compatibility issues when integrating with Katalon.

Is there a recommended or supported way to handle .ods file reading in Katalon?
If not, are there any suggested workarounds (such as converting .ods to .xlsx) that can be automated within Katalon?

Thanks in advance!

1 Like
2

It seems that

provides what you need.

3

To read .ods files in Katalon Studio, use the Apache ODF Toolkit with a Groovy script. Here’s how to implement it:

1. Add ODF Toolkit Dependencies

  1. Download the latest JARs from ODF Toolkit Releases:
  • odfdom-java-<version>.jar
  • simple-odf-<version>.jar
  1. Place them in your Katalon project’s Drivers folder.
  2. Add to build.gradle:
dependencies {
  implementation fileTree(dir: 'Drivers', includes: ['*.jar'])
}

2. Script to Read .ods File

groovy

import org.odftoolkit.simple.SpreadsheetDocument
import org.odftoolkit.simple.table.Table
import org.odftoolkit.simple.table.Row

List<Map<String, String>> readODS(String filePath) {
  List<Map<String, String>> data = []
  SpreadsheetDocument doc = SpreadsheetDocument.loadDocument(new File(filePath))
  Table sheet = doc.getSheetByIndex(0) // First sheet
  
  // Get headers from first row
  Row headerRow = sheet.getRowByIndex(0)
  List<String> headers = (0..<headerRow.getCellCount()).collect { 
    headerRow.getCellByIndex(it).getStringValue() 
  }
  
  // Read data rows
  for (int rowIdx = 1; rowIdx < sheet.getRowCount(); rowIdx++) {
    Row row = sheet.getRowByIndex(rowIdx)
    Map<String, String> rowData = [:]
    headers.eachWithIndex { header, colIdx ->
      rowData[header] = row.getCellByIndex(colIdx).getStringValue()
    }
    data.add(rowData)
  }
  
  doc.close()
  return data
}

// Usage
List<Map<String, String>> testData = readODS("path/to/file.ods")
println testData

3. Alternative: Convert ODS to XLSX

If dependency issues persist, automate conversion using LibreOffice:

groovy

void convertODStoXLSX(String odsPath, String xlsxPath) {
  String command = "soffice --headless --convert-to xlsx --outdir ${new File(xlsxPath).parent} ${odsPath}"
  Process process = Runtime.getRuntime().exec(command)
  process.waitFor()
}

// Usage
convertODStoXLSX("input.ods", "output.xlsx")
// Now use Apache POI to read XLSX

Key Notes

  • ODF Toolkit Limitations:
    • Avoid empty rows/columns in the .ods file.
    • Handle merged cells manually.
  • Conversion Workflow:
    • Install LibreOffice on the execution machine.
    • Use soffice command-line tool for conversion.

Dependency Conflicts

If you face NoClassDefFoundError, exclude conflicting transitive dependencies in build.gradle:

dependencies {
  implementation('org.odftoolkit:odfdom-java:0.10.0') {
    exclude group: 'org.apache.xmlbeans', module: 'xmlbeans'
  }
}

This approach allows direct .ods parsing or automated conversion to XLSX for seamless integration