我想在特定列中排列整个单元格，而不是单个单元格 [英] 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.

下面的代码是我排列单个单元格的代码.

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 不会在 xf 中设置 applyAlignment="true"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 本身不会将此样式应用于该列中的新单元格.应该在Sheet1.xml对应的c标签中设置s="1"，其中1为样式编号.

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.

所以我们必须解决:

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