在Apache POI中实现VBA的Sheet.range()函数 [英] Implementing VBA's Sheet.range() function in Apache POI

问题描述

全部:

我对Apache POI还是陌生的，正如标题所述，我想根据Range()函数的API或可以共同构建该函数的API进行查找，例如:

I am pretty new to Apache POI, as the title said, I want to find according API of that Range() function, or APIs that can work together to build that function, for example:

在VBA中:

Application.getSheets("Sheet1").Range("A2:F3")

在POI中，获取工作表后，我不确定实现Range()的最佳方法是什么:

In POI, after I get the sheet, I am not sure what is best way to implement that Range():

new WorkBook(/* excel file input stream*/).getSheet("Sheet1") // then what?

推荐答案

到目前为止，在apache poi中还没有类似VBA Range的东西.但是，要使用什么取决于您需要对单元格范围进行什么操作.如果需要从类似"A2:F3"的字符串中获取Cell.然后 CellRangeAddress 可能是帮助.

There is nothing like VBA Range in apache poi until now. But what to use instead depends on what exactly you needs to do with the range of cells. If the need is getting the Cells from a string like "A2:F3". Then CellRangeAddress may be of a help.

示例:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileInputStream;

class ExcelCellRange {

 public static void main(String[] args) throws Exception {

  DataFormatter formatter = new DataFormatter();

  Workbook workbook = WorkbookFactory.create(new FileInputStream("file.xlsx"));
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("file.xls"));

  Sheet sheet = workbook.getSheetAt(0);
  System.out.println("This is sheet: " + sheet.getSheetName());

  CellRangeAddress cellRangeAddress = CellRangeAddress.valueOf("D1:G8");
  int firstRow = cellRangeAddress.getFirstRow();
  int lastRow = cellRangeAddress.getLastRow();
  int firstColumn = cellRangeAddress.getFirstColumn();
  int lastColumn = cellRangeAddress.getLastColumn();

  for (int r = firstRow; r <= lastRow; r++) {
   for (int c = firstColumn; c <= lastColumn; c++) {
    Row row = sheet.getRow(r);
    if (row == null) {
     System.out.println(new CellAddress(r, c) + " is in totally empty row.");
    } else {
     Cell cell = row.getCell(c);
     String content = formatter.formatCellValue(cell);
     System.out.println(new CellAddress(r, c) + " contains " + content);
    }
   }
  }

  workbook.close();

 }
}

顺便说一句:整个 Package org .apache.poi.ss.util 值得关注.

Btw.: The whole Package org.apache.poi.ss.util is worth attention.

这篇关于在Apache POI中实现VBA的Sheet.range()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！