java 工作簿的 Apache POI 默认样式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39686790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache POI default style for workbook
提问by Gurtz
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.
我正在尝试XSSF使用Apache POI. 这应该应用于用户可能创建的新单元格（在工作簿被 保存后POI）。我试图通过调用来做到这一点workbook.getCellStyleAt(0)——我理解这是工作簿的默认样式——然后将此样式修改为我想要的新默认样式。
This works when I read in an existing XSLXfile (a "template" file) and modify the default style. But when I create a new XSLXfile from scratch using POI, it does notwork.
当我读入现有XSLX文件（“模板”文件）并修改默认样式时，这会起作用。但是，当我创建一个新的XSLX使用从临时文件POI，它并没有正常工作。
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.)
在逐步使用调试器时，我可以看到，当使用“模板”文件时，索引 0 处的单元格样式分配了一个“主题”（可能是因为模板文件最初是使用 Excel 创建的）。但是当从头开始创建文件时（使用POI），索引 0 处的单元格样式具有空主题。（这可能是为什么使用一种方法而不是另一种方法有效的一个因素。）
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无论工作簿最初是如何创建的，有关如何可靠地更改工作簿 ( )的默认单元格样式的任何建议？谢谢！
回答by Axel Richter
There are two possibilities to achieve this with XSSF.
使用 XSSF 有两种可能性可以实现这一点。
First: If you select all cells in a XSSF worksheet in Excel and apply a style to them, then a colselement is added to the sheet with a style definition for all columns:
第一：如果您在 Excel 中选择 XSSF 工作表中的所有单元格并对其应用样式，则会向工作表中cols添加一个元素，其中包含所有列的样式定义：
<cols>
<col min="1" max="16384" style="1"/>
</cols>
This can be achieved with apache poi like so:
这可以通过 apache poi 实现，如下所示：
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.
这将更改整个工作簿中所有单元格的默认单元格样式。
The XML in styles.xmlshows:
中的 XMLstyles.xml显示：
<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>
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.
如您所见，正常的单元格样式是cellStyles. 它指的xfId="0"是指的是numFmtId="0"fontId="0"fillId="0"borderId="0"。这意味着数字格式、字体、填充格式和边框的第一个定义用于正常单元格样式。
