Apache POI 专注于生成的 excel 文件中的特定单元格 [英] Apache POI Focus on a particular cell in generated excel file

问题描述

我正在使用 apache poi 生成 excel 文件.我正在使用 XSSFWorkbook 格式.我正在尝试将焦点放在生成的 excel 文件的第一个单元格上.

Hi I am using apache poi to generate excel files. I am using XSSFWorkbook format. I am trying to make focus on the first cell of the generated excel file.

我已经尝试了以下代码片段.

I have tried the following code snippet.

        try {
                Sheet sheet = workbook.getSheetAt(0);
                workbook.setActiveSheet(0);
                Cell cell = sheet.getRow(0).getCell(0);
                cell.setAsActiveCell();
                sheet.setActiveCell(cell.getAddress());
                sheet.showInPane(0, 0);
            } catch (IllegalArgumentException e) {
                LOGGER.error("Failed to set active sheet and cell.", e);
            }

我看过这个SO问题以及.该解决方案似乎对我不起作用.任何人都可以在这里帮助我吗?

I have looked at this SO question as well. The solution doesn't seem to work for me. Can anyone please help me here?

P.S:我使用的是 apache poi 3.15 版.

P.S: I am using apache poi version 3.15.

更新 1:

我还有一个冻结窗格，左上角的单元格为 C1.冻结未正确显示.

I also have a freeze pane with top left cell as C1. The freeze was not being shown properly.

我已经尝试了以下代码

public void setActiveCell(Workbook workbook, int sheetIndex, int row, int column) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        CellAddress cellAddress = new CellAddress(row, column);

        sheet.createFreezePane(2, 0);
        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane()
            .setTopLeftCell("C1");
        ((XSSFSheet) sheet).setActiveCell(cellAddress);

//        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0)
//            .setTopLeftCell(cellAddress.formatAsString());
//        ((XSSFSheet) sheet).setActiveCell(cellAddress);
}

我将上述代码称为 setActiveCell(workbook, 0, 0, 0);.确保工作簿不为空并且至少包含一张纸.上面的代码既不显示 C1 单元格(创建的窗格的左上角单元格)也不显示 A1 单元格(活动单元格集).

I am calling the above code as setActiveCell(workbook, 0, 0, 0);. It is assured that the workbook is not null and contains atleast one sheet. The above code shows neither the C1 cell (the top left cell of the created pane) nor the A1 cell (the active cell set).

更新 2:

根据@AlexRichter 的回答，以下代码对我有用:

Based on the answer from @AlexRichter, the following code works for me:

public void setActiveCell(Workbook workbook, int sheetIndex, int row, int column) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        CellAddress cellAddress = new CellAddress(row, column);

        sheet.createFreezePane(2, 0);
        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane()
            .setTopLeftCell("C1");

        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0)
            .setTopLeftCell(cellAddress.formatAsString());
}

推荐答案

不幸的是 XSSFSheet.showInPane 有问题.

以下对我有用:

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

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");

  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("D10");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("E11"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}

它使用来自基本低级对象的 setTopLeftCell.

It uses setTopLeftCell from the basic low level objects.

如果你有窗格，那么你必须为你需要的窗格设置 TopLeftCell.示例:

If you have panes, then you must set the TopLeftCell for the pane you need. Example:

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

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");
/*
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("D10");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("E11"));
*/

  sheet.createFreezePane(2, 2); //C3 is top left cell of the scrollable pane
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane().setTopLeftCell("C3");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("A1"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}

如果固定窗格不包含行，则似乎有一个例外.然后在 .getPane().setTopLeftCell 中设置行是没有意义的.那么顶行必须直接在SheetView的TopLeftCell中设置.

There seems to be an exception if the fixed pane contains no rows. Then setting the row in .getPane().setTopLeftCell is meaningless. Then the top row must be set directly in TopLeftCell in the SheetView.

示例:

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

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");

  sheet.createFreezePane(2, 0); //C1 is top left cell of the scrollable pane.
  //But if the fixed pane contains no rows, as in this example, then setting the row in 
  //getPane().setTopLeftCell is meaningless. Then the top row must be set in the SheetView.
  //Example: Row 6 shall be the top row:
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("A6");
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane().setTopLeftCell("C1");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("C6"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}

这篇关于Apache POI 专注于生成的 excel 文件中的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！