I have an excel file (I don't know how was generated) which contains cells missing the R value (I guess the R stands for reference). This document has a cell reference only for the first cell in every row. Something like this: <row> <c r="A1"><v>France</v></c> <c><v>93</v></c> <c><v>0.1957</v></c> <c><v>2012/01/01</v></c> </row> <row> <c r="A2"><v>Italy</v></c> <c><v>93</v></c> <c><v>0.1957</v></c> <c><v>2012/01/01</v></c> </row> LibreOffice cannot process this file correctly and displays only the first cell for every row (omits cells without reference). XSSFRow cannot process this file correctly and displays only the last cell for every row (every cell is added to a map using the columnIndex as a key, and columnIndex is zero for every cell, so it overwrites every cell with the next one added). Excel displays this file correctly, but if you save a copy of it, the reference is updated for every cell with the right value. xlsx4j seems to process this file correctly. I have implemented a workaround (two in fact), but since my knowledge of the open document format is not very deep, I would like to know if it is right or there is a better way to do it, and if a fix for this could be added for a future release. Here are my workarounds: The first one makes sure every cell is added to the _cells map even if _cellNum in XSSFCell is wrong. public class XSSFRow implements Row, Comparable<XSSFRow> { protected XSSFRow(CTRow row, XSSFSheet sheet) { _row = row; _sheet = sheet; _cells = new TreeMap<Integer, XSSFCell>(); int columnIndex = -1; for (CTCell c : row.getCArray()) { XSSFCell cell = new XSSFCell(this, c); // Begin modification if (c.getR() != null) { columnIndex = cell.getColumnIndex(); } else { columnIndex++; } // _cells.put(cell.getColumnIndex(), cell); _cells.put(columnIndex, cell); // End modification sheet.onReadCell(cell); } } } The second one sets the R value when it's missing and thus adding the cell to the _cells map "correctly": public class XSSFRow implements Row, Comparable<XSSFRow> { protected XSSFRow(CTRow row, XSSFSheet sheet) { _row = row; _sheet = sheet; _cells = new TreeMap<Integer, XSSFCell>(); int columnIndex = -1; for (CTCell c : row.getCArray()) { // Begin modification if (c.getR() != null) { columnIndex = cell.getColumnIndex(); } else { columnIndex++; c.setR(CellReference.convertNumToColString(columnIndex)); } // End modification XSSFCell cell = new XSSFCell(this, c); _cells.put(cell.getColumnIndex(), cell); sheet.onReadCell(cell); } } } Thank you very much.
Well, in fact the second workaround has some mistakes. I copied the wrong lines. It should be like this (hope it's ok now I have edited it here): public class XSSFRow implements Row, Comparable<XSSFRow> { protected XSSFRow(CTRow row, XSSFSheet sheet) { _row = row; _sheet = sheet; _cells = new TreeMap<Integer, XSSFCell>(); int columnIndex = -1; for (CTCell c : row.getCArray()) { // Begin modification if (c.getR() == null) { columnIndex++; c.setR(CellReference.convertNumToColString(columnIndex)+this.getRowNum()); } // End modification XSSFCell cell = new XSSFCell(this, c); columnIndex = cell.getColumnIndex(); _cells.put(cell.getColumnIndex(), cell); sheet.onReadCell(cell); } } }
Fixed in r r1417379