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.

我尝试了以下代码段.

        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屋！