我想安排特定的列整个细胞，而不是单个细胞 [英] I want to arrange entire cells in specific column, instead of individual cells

问题描述

我用 POI ，并试图安排一整列。但只有我找到了办法是安排单独的细胞。虽然我找到 sheet.setDefaultColumnStyle（），并试图使用此功能，它不会在所有的工作。
你可以让我知道使用的方法 setDefaultColumnStyle（）或另一种方式。

I used POI and tried to arrange one entire column. But only the way I found is arrange individual cell. Although I found sheet.setDefaultColumnStyle() and tried to use this function, it doesn't work at all. could you let me know the way of using setDefaultColumnStyle() or another way.

低于code是我的code，安排单个细胞。

below code is my code to arrange individual cell.

    xlsxFile = new File("data.xlsx");
    wb = new XSSFWorkbook();

    cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    row = sheet1.createRow(0);
    cell = row.createCell(1);
    cell.setCellValue("name");
    cell.setCellStyle(cellStyle);

我的英语技能是有点尴尬。谢谢您的阅读。如果有什么奇怪的，请让我知道。

My english skill is a little awkward. Thank you for reading. If there is anything weird, please let me know.

推荐答案

这似乎是在Apache的POI的错误。这里有两个问题：

This seems to be an bug in Apache POI. There are two issues:

第一：使用 Sheet.setDefaultColumnStyle 与定义路线样式之后，POI不会设置 applyAlignment =真正的在 XF 元素在 styles.xml 。但它应该，因为只有这将导致Excel从风格应用​​路线，以新的细胞。

First: After using Sheet.setDefaultColumnStyle with a style which defines alignments, POI does not set applyAlignment="true" in the xf element's tag in styles.xml. But it should, because only that will cause Excel to apply the alignments from that style to new cells.

二：POI本身并没有这种风格适用于该列新细胞。它应该设置 S =1，其中1为风格号，在相应的 C 标记 Sheet1.xml 。

Second: POI itself does not apply this style to new cells in that column. It should set s="1", where 1 is the style number, in the corresponding c tag of Sheet1.xml.

因此​​，我们必须解决方法：

So we have to workaround:

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

import java.io.FileOutputStream;
import java.io.IOException;

class CenteredColumn {

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();

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

   CellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

   sheet.setDefaultColumnStyle(1, cellStyle);

   //Workaround 1: We set setApplyAlignment(true) into the `xf` element's tag in styles.xml.
   //This causes Excel applying alignments from this style to new cells in that column.
   for (int i = 0; i < ((XSSFWorkbook)wb).getStylesSource().getNumCellStyles(); i++) {
    if (((XSSFWorkbook)wb).getStylesSource().getStyleAt(i).equals(cellStyle)) {
     ((XSSFWorkbook)wb).getStylesSource().getCellXfAt(i).setApplyAlignment(true);
    }
   }

   Row row = sheet.getRow(0);
   if (row == null) row = sheet.createRow(0);

   Cell cell = row.getCell(1);
   if (cell == null) cell = row.createCell(1);
   cell.setCellValue("name");
   //Workaround 2: We set the cellStyle to the new cell because POI will not do this itself.
   cell.setCellStyle(cellStyle);

   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);

  } catch (IOException ioex) {
  }
 }
}

这篇关于我想安排特定的列整个细胞，而不是单个细胞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！