工作簿的 Apache POI 默认样式 [英] Apache POI default style for workbook

问题描述

我正在尝试使用 Apache POI 更改整个 Excel 工作簿 (XSSF) 的默认单元格样式.这应该应用于用户可能创建的新单元格(在 POI 保存工作簿之后).我试图通过调用 workbook.getCellStyleAt(0) 来做到这一点——我认为这是工作簿的默认样式——然后将此样式修改为我想要的新默认样式.

I am trying to change the default cell style for an entire Excel workbook (XSSF) using Apache POI. This should be applied to new cells a user might create (after the workbook has been saved by POI). I am trying to do this by calling workbook.getCellStyleAt(0) -- which I understand to be the default style for the workbook -- and then by modifying this style to what I want for the new default.

当我读入现有的 XSLX 文件(模板"文件)并修改默认样式时，这会起作用.但是当我使用 POI 从头开始​​创建一个新的 XSLX 文件时，它不起作用.

This works when I read in an existing XSLX file (a "template" file) and modify the default style. But when I create a new XSLX file from scratch using POI, it does not work.

在逐步使用调试器时，我可以看到，当使用模板"文件时，索引 0 处的单元格样式分配了一个主题"(可能是因为模板文件最初是使用 Excel 创建的).但是当从头开始创建文件时(使用 POI)，索引 0 处的单元格样式具有空主题.(这可能是为什么使用一种方法有效而另一种方法无效的一个因素.)

When stepping through using a debugger I can see that, when using a "template" file, there is a "theme" assigned to the cell style at index 0 (probably because the template file was originally created using Excel). But when creating a file from scratch (using POI), the cell style at index 0 has a null theme. (This might be a factor in why this works using one approach but not the other.)

关于如何可靠地更改工作簿 (XSSF) 的默认单元格样式而不管工作簿最初是如何创建的有什么建议吗?谢谢！

Any suggestions on how to reliably change the default cell style for a workbook (XSSF) regardless of how the workbook was originally created? Thanks!

推荐答案

使用 XSSF 有两种可能性可以实现这一点.

There are two possibilities to achieve this with XSSF.

首先:如果您在 Excel 中选择 XSSF 工作表中的所有单元格并对其应用样式，则会将 cols 元素添加到工作表中，其中包含所有列的样式定义:

First: If you select all cells in a XSSF worksheet in Excel and apply a style to them, then a cols element is added to the sheet with a style definition for all columns:

<cols>
 <col min="1" max="16384" style="1"/>
</cols>

这可以通过 apache poi 实现，如下所示:

This can be achieved with apache poi like so:

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

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

class ExcelCellStyleAllColumns
 {

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

    Workbook wb = new XSSFWorkbook();

    Font font = wb.createFont();
    font.setFontHeightInPoints((short)24);
    font.setFontName("Courier New");
    font.setItalic(true);
    font.setBold(true);

    CellStyle style = wb.createCellStyle();
    style.setFont(font);

    Sheet sheet = wb.createSheet();

    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol cTCol = 
      ((XSSFSheet)sheet).getCTWorksheet().getColsArray(0).addNewCol();
    cTCol.setMin(1);
    cTCol.setMax(16384);
    cTCol.setWidth(12.7109375);
    cTCol.setStyle(style.getIndex());

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("test");
    cell.setCellStyle(style);

    FileOutputStream os = new FileOutputStream("ExcelCellStyleAllColumns.xlsx");
    wb.write(os);
    os.close();

  } catch (IOException ioex) {
  }
 }
}

这将更改工作表中所有单元格的默认单元格样式.

This will change the default cell style of all cells in the sheet.

第二:您可以像这样修改普通单元格样式的样式定义:

Second: You can modify the style definitions of the normal cell style like so:

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

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

class ExcelDefaultCellStyle {

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

    Workbook wb = new XSSFWorkbook();

    Font font = wb.getFontAt((short)0);
    font.setFontHeightInPoints((short)24);
    font.setFontName("Courier New");
    ((XSSFFont)font).setFamily(3);
    ((XSSFFont)font).setScheme(FontScheme.NONE);
    font.setItalic(true);
    font.setBold(true);

    CellStyle style = wb.getCellStyleAt(0);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    ((XSSFWorkbook) wb).getStylesSource().getCTStylesheet().addNewCellStyles().addNewCellStyle().setXfId(0);

    ((XSSFCellStyle)style).getStyleXf().addNewAlignment().setVertical(
     org.openxmlformats.schemas.spreadsheetml.x2006.main.STVerticalAlignment.CENTER);
    ((XSSFCellStyle)style).getStyleXf().getAlignment().setWrapText(true);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("test");

    FileOutputStream os = new FileOutputStream("ExcelDefaultCellStyle.xlsx");
    wb.write(os);
    os.close();

  } catch (IOException ioex) {
  }
 }
}

这将更改整个工作簿中所有单元格的默认单元格样式.

This will change the default cell style of all cells in the whole workbook.

styles.xml 中的 XML 显示:

The XML in styles.xml shows:

<cellStyleXfs count="1">
 <xf numFmtId="0" fontId="0" fillId="0" borderId="0">
  <alignment vertical="center" wrapText="true"/>
 </xf>
</cellStyleXfs>
<cellXfs count="1">
 <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
  <alignment vertical="center" wrapText="true"/>
 </xf>
</cellXfs>
<cellStyles>
 <cellStyle xfId="0"/>
</cellStyles>

如您所见，普通单元格样式是 cellStyles 中的第一个样式.它指的是 xfId="0" 指的是 numFmtId="0" fontId="0" fillId="0" borderId="0".这意味着数字格式、字体、填充格式和边框的第一个定义用于正常单元格样式.

As you see the normal cell style is the first one in cellStyles. It refers to xfId="0" which refers to numFmtId="0" fontId="0" fillId="0" borderId="0". That means the very first definitions of number format, font, fill format and border is used in normal cell style.

这篇关于工作簿的 Apache POI 默认样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！