Get cell values for range.

[IndigoStar]

Hi,

I'm trying to read an existing XLSX file and get the values from a given sheet (e.g. 'Sheet2') and a given range (e.g. "C4:C8", or "C4:F4"). For the moment I'm assuming the range includes cells in either a single row or a single column.

I can read the file and get the sheet, but I'm not sure what is the best way to find the cells for the given range.

One way would be to convert the range to row and column numbers (e.g. C4:C8 --> r4c3:r8c3), then call Worksheet.getSheetData and traverse the returned SheetData to get the cells I need. But I notice that Worksheet.getSheetData will omit empty leading rows, so my row and columns numbers may not correspond to the rows and columns in SheetData, e.g. my row 4 may in fact be row 1 in the data returned from Worksheet.getSheetData.

Any ideas on a robust way to do this?

[jason]

If you create a sample worksheet in Excel, then look at the resulting sheetdata, for example:

Syntax: [ Download ] [ Hide ]

Syntax: [ Download ] [ Show ]

Using xml Syntax Highlighting

  <sheetData>
    <row r="3" spans="1:5" x14ac:dyDescent="0.25">
      <c r="A3" t="s">
        <v>1</v>
      </c>
    </row>
    <row r="6" spans="1:5" x14ac:dyDescent="0.25">
      <c r="C6" t="s">
        <v>2</v>
      </c>
    </row>
  </sheetData>
 

Parsed in 0.001 seconds, using GeSHi 1.0.8.4

You can see that the actual row is given by @r. See further http://webapp.docx4java.org/OnlineDemo/ ... row_1.html
and http://webapp.docx4java.org/OnlineDemo/ ... L/c_1.html

Any reason not to use the same representation as the POI guys do, in http://grepcode.com/file/repo1.maven.or ... Sheet.java
namely a TreeMap of row objects, keyed by Integer row number? See initRows (line 201)

It'd be good to add a suitable representation to WorksheetPart, so please make a pull request if you can, when you are done

[IndigoStar]

Jason, thanks for the rapid reply as always. And thanks for confirming that @r is the 'real' row number, I had assumed that but glad to have it confirmed. I'll look at the references you suggested.

How to make a pull request? I have not done that before.