由于某种原因，Apache POI Cellstyle 在第 41 行之后不适用

Question

正如你从标题中了解到的那样，在第 41 行之后，即使通过调试我看到代码运行良好，我的风格也不适用。

我的函数是：

private void writeTable(Table table,Row row,Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
if(row.getRowNum() % 2 == 0) {
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setWrapText(true);
cellStyle.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
}else{
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setWrapText(true);
cellStyle.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
}
Cell cell = row.createCell(0);
cell.setCellValue(Table.index);
cell.setCellStyle(cellStyle);

cell = row.createCell(1);
cell.setCellValue(strCorrecter(Table.Name).isEmpty() ? "-" : strCorrecter(Table.Name));
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(strCorrecter(Table.Surname.toString()).isEmpty() ? "-" : strCorrecter(Table.Surname.toString()));
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue(strCorrecter(Table.Age.toString()).isEmpty() ? "-" : strCorrecter(Table.Age.toString()));
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell =row.createCell(6);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(11);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(12);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(13);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(14);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
}

我看到所有行都在函数开头的 if else 语句中经过。但是当我查看 excel 文件时，它们似乎没有任何属性。这是我调用此函数的部分：

int rowCount = 3;
for (Table table : tableList){
Row row = sheet.createRow(++rowCount);
writeInterlock(table,row,workbook);
}

我不知道发生了什么，所以任何帮助将不胜感激

Answer 1

工作簿中唯一单元格格式/单元格样式的最大数量存在 Excel 限制。

因此，不要为每一行创建单元格样式。据我所知，您只需要两种不同的单元格样式。因此，将这两个创建为方法外部的cellStyle1和cellStyle2：

...
CellStyle cellStyle1 = wb.createCellStyle();
//set all the needed settings
CellStyle cellStyle2 = wb.createCellStyle();
//set all the needed settings
...

然后仅在方法中使用两者：

private void writeTable(Table table,Row row,Workbook wb) {
...
if(row.getRowNum() % 2 == 0) {
//here use cellStyle1
} else {
//here use cellStyle2
}
...
}

Answer 2

你得到的答案并不完全正确，有一个限制，好吧，但是peoblem发生在你身上，因为你使用的是excel 97'(-2007)的实现，这里有一个链接。

如果您真的不需要.xls格式的文件，则在工作示例(类似于您的示例)下方，该示例不需要像您接受的答案那样以静态方式管理变量。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MainWriteCells {
public MainWriteCells() {
// TODO Auto-generated constructor stub
}
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
FileInputStream is = new FileInputStream(new File("C:\ook.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheet("Sheet1");
for(int i=0;i<1000;i++){
writeTable(sheet.createRow(i),wb);
}
wb.write(new FileOutputStream(new File("C:\ook.xlsx")));
}
private static void writeTable(Row row,Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
if(row.getRowNum() % 2 == 0) {
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setWrapText(true);
cellStyle.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
}else{
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setWrapText(true);
cellStyle.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
}
Cell cell = row.createCell(0);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);

cell = row.createCell(1);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell =row.createCell(6);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(11);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(12);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(13);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
cell = row.createCell(14);
cell.setCellValue("TODO");
cell.setCellStyle(cellStyle);
}
}

如您所见，我更改了两个对象和.xslx中的文件扩展名

XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheet("Sheet1");

而不是

HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheet("Sheet1");