apache poi设置单元格边框不工作

kq4fsx7k  于 2021-06-27  发布在  Java
关注(0)|答案(1)|浏览(543)

我在ApachePOI(v4.1.2)中遇到了一个问题,它不能将边界设置得很细。xls和xlsx扩展都支持我的代码。当我将数据导出到xlsx文件时,一切正常,但是对于xls文件,它不会绘制从单元格k5到末尾的边界。

这是我的密码:

private int populateExcelData(Workbook workbook, Sheet sheet, int rowNum, List<List<Object>> excelData,
            ExcelVo excelVo, Boolean isHeader) {
        if (CollectionUtils.isNotEmpty(excelData)) {
            int cellNum = 0;
            Row row;
            for (List<Object> objects : excelData) {
                cellNum = excelVo.getColPadding();
                row = sheet.createRow(rowNum++);
                Cell cell;
                for (Object object : objects) {
                    cell = row.createCell(cellNum++);
                    setCellValue(cell, object, excelVo);
                    configCellStyle(cell, workbook, excelVo, isHeader);
                }
            }
        }
        return rowNum;
    }

private void configCellStyle(Cell cell, Workbook workbook, ExcelVo excelVo, Boolean isHeader) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        if (isHeader) {
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(excelVo.getFontColor().getIndex());
            cellStyle.setFont(headerFont);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(excelVo.getBackgroundColor().getIndex());
        }
        cell.setCellStyle(cellStyle);
    }

我不知道为什么用xls文件它只画从单元格k5开始的边界。代码不会抛出bug或异常。
谢谢大家的支持。

c6ubokkw

c6ubokkw1#

excel对独特的单元格格式/单元格样式以及字体都有限制。单元格样式和字体存储在工作簿级别。所有工作表中的所有单元格都将共享它们。当前excel版本限制为:
独特的单元格格式/单元格样式:65490

独特的字体类型:1024种全局字体可供使用;每个工作簿512个
以前版本的excel(二进制*.xls)甚至有更小的限制。
因此,如果您为每个单元格创建一个单独的单元格样式,您将很快达到极限。 XSSF 可能会工作,因为限制更大。但是以前版本的excel(二进制*.xls)有更小的限制。这就是为什么不使用 HSSF .
所以要做的是,在工作簿级别上为工作簿创建尽可能多的单元格样式。在细胞填充过程之外进行。在创建单元本身时,单元样式只能使用 cell.setCellStyle . 但它不能被创造。
让我们用一个完整的例子来说明这一点。它部分使用了你的代码 populateExcelData 但我只能希望它符合你的想法,因为你没有提供一个完整的例子。

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.util.GregorianCalendar;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.collections4.CollectionUtils;

public class CreateExcel {

 private Workbook workbook;
 private CellStyle textStyle;
 private CellStyle dateStyle;
 private CellStyle numberStyle;
 private CellStyle headerStyle;

 public CreateExcel(String type, String path, List<List<Object>> headerData, List<List<Object>> excelData) {
  try {
   this.workbook = ("HSSF".equals(type)) ? new HSSFWorkbook() : new XSSFWorkbook(); 

   DataFormat dataFormat = workbook.createDataFormat();

   this.textStyle = workbook.createCellStyle();
   setCellStyleAllBorders(textStyle);

   this.dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
   setCellStyleAllBorders(dateStyle);

   this.numberStyle = workbook.createCellStyle();
   numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));
   setCellStyleAllBorders(numberStyle);

   this.headerStyle = workbook.createCellStyle();
   Font headerFont = workbook.createFont();
   headerFont.setBold(true);
   headerFont.setColor(IndexedColors.WHITE.getIndex());
   headerStyle.setFont(headerFont);
   headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   headerStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
   setCellStyleAllBorders(headerStyle);

   Sheet sheet = workbook.createSheet(); 

   int rowNum = 0;
   rowNum = populateExcelData(workbook, sheet, rowNum, headerData, 1, true);

   rowNum = populateExcelData(workbook, sheet, rowNum, excelData, 1, false);

   System.out.println(rowNum);

   for (int c = 0; c < 256; c++) {
    sheet.autoSizeColumn(c); // this is very time consuming, would be better one can set column widths using Sheet.setColumnWidth(int columnIndex, int width) directly
   }

   FileOutputStream fileout = new FileOutputStream(path);
   this.workbook.write(fileout);
   fileout.close();
   this.workbook.close();

  } catch (Exception ex) {
   ex.printStackTrace(); 
  }
 }

 private int populateExcelData(Workbook workbook, Sheet sheet, int rowNum, List<List<Object>> excelData, Integer colPadding, Boolean isHeader) {
  if (CollectionUtils.isNotEmpty(excelData)) {
   int cellNum = 0;
   Row row;
   for (List<Object> objects : excelData) {
    cellNum = colPadding;
    row = sheet.createRow(rowNum++);
    Cell cell;
    for (Object object : objects) {
     cell = row.createCell(cellNum++);
     setCellValueAndStyle(cell, object, isHeader);
    }
   }
  }
  return rowNum;
 }

 private void setCellValueAndStyle(Cell cell, Object object, Boolean isHeader) {
  if (object instanceof String) {
   cell.setCellValue((String) object);
   if (!isHeader) cell.setCellStyle(this.textStyle);
  } else if (object instanceof Double) {
   cell.setCellValue((Double) object);
   if (!isHeader) cell.setCellStyle(this.numberStyle);
  } else if (object instanceof GregorianCalendar) {
   cell.setCellValue((GregorianCalendar) object);
   if (!isHeader) cell.setCellStyle(this.dateStyle);
  }
  if (isHeader) cell.setCellStyle(this.headerStyle);
 }

 private void setCellStyleAllBorders(CellStyle cellStyle) {
  cellStyle.setBorderBottom(BorderStyle.THIN);
  cellStyle.setBorderLeft(BorderStyle.THIN);
  cellStyle.setBorderRight(BorderStyle.THIN);
  cellStyle.setBorderTop(BorderStyle.THIN);
 }

 public static void main(String[] args) throws Exception {
  List<List<Object>> headerData = new ArrayList<>();
  List<Object> headerRow = new ArrayList<>();
  headerRow.add("Text"); headerRow.add("Value"); headerRow.add("Date");
  headerData.add(headerRow);
  headerRow = new ArrayList<>();
  headerRow.add("not formatted"); headerRow.add("in Coins"); headerRow.add("as long date");
  headerData.add(headerRow);

  List<List<Object>> excelData = new ArrayList<>();
  for (int r = 1; r < 1000; r++) {
   List<Object> excelRow = new ArrayList<>();
   excelRow.add("Text" + r); excelRow.add(123.45 * r); excelRow.add(new GregorianCalendar(2020, 0, r));
   excelData.add(excelRow);
  }

  CreateExcel test = new CreateExcel("HSSF", "./Excel.xls", headerData, excelData);
  test = new CreateExcel("XSSF", "./Excel.xlsx", headerData, excelData);
 }
}

相关问题