apache POI中单元的直接寻址

I want to modify an existing xlsm file with apache POI.

我想用apache POI修改一个现有的xlsm文件。

As I only have to change single cells I don't want to loop over the whole sheet, but all the examples I have found so far suggest to loop over all the rows and over all cells in a row, checking if the current row/col-index matches the one to be changed.

我只需要改变单一的细胞我不想循环在整个表,但是我发现到目前为止的示例显示在所有细胞循环遍历所有的行和连续检查如果当前行/ col-index匹配的被改变。

Is there no "random access" mode to get a single cell by its coordinates directly? Event the promising getCell method of the CellUtil class needs a row object instead of just an integer coordinate.

是否没有“随机访问”模式来直接通过一个单元格的坐标获取它?CellUtil类有前途的getCell方法需要一个行对象，而不仅仅是一个整数坐标。

2 个解决方案

#1

The Excel file format is a (normally) sparse file format, so unused + unstyled rows and cells are normally skipped from the file. So, you do need to check for null values when fetching rows and cells. But, other than that, you can fetch cells pretty much directly. You can use the CellReference class to turn an Excel-style set of co-ordinates (eg B3) into a POI-style one (eg row=2, column=1), then fetch

Excel文件格式是一种(通常)稀疏文件格式，因此未使用的+无样式的行和单元通常从文件中跳过。因此，在获取行和单元格时，确实需要检查空值。但是，除此之外，您可以直接获取单元格。您可以使用CellReference类将优秀样式的坐标集(如B3)转换为poi样式的坐标集(如row=2, column=1)，然后获取

CellReference ref = new CellReference("B3");

Workbook workbook = WorkbookFactory.create(new File("input.xls"));
Sheet sheet = workbook.getSheetAt(0);

Row r = sheet.getRow(ref.getRow());
if (r == null) {
   // Handle this whole row being empty
} else {
   Cell c = r.getCell(ref.getCol(), Row.RETURN_BLANK_AS_NULL);
   if (c == null) {
      // Handle this cell being empty
   } else {
      // Do something with the cell
   }
}

Depending on how you want to handle missing rows or cells, you can make it shorter, but that'll be specific to your needs

根据您希望如何处理丢失的行或单元，您可以使它更短，但这将针对您的需要。

#2

You get first the row from the sheet and then get the cell at x,y from it:

首先从表格中得到行然后从表格中得到x y的单元格:

Row myRow = getRow(int x, sheet);
Cell myCell = getCell(myRow, int y);

#1

CellReference ref = new CellReference("B3");

Workbook workbook = WorkbookFactory.create(new File("input.xls"));
Sheet sheet = workbook.getSheetAt(0);

Row r = sheet.getRow(ref.getRow());
if (r == null) {
   // Handle this whole row being empty
} else {
   Cell c = r.getCell(ref.getCol(), Row.RETURN_BLANK_AS_NULL);
   if (c == null) {
      // Handle this cell being empty
   } else {
      // Do something with the cell
   }
}