使用 apache poi 合并和对齐中心单元格

java excel apache-poi

2022-09-01 16:49:59

我想使用Apache poi将数据导出到Excel。
现在我面临的问题是我无法合并行并将其居中对齐。

导出数据的代码为：

List<LinkedHashMap<String,Object>> lstReportHeader = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();

//Set Header Font
HSSFFont headerFont = wb.createFont();
headerFont.setBoldweight(headerFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);

//Set Header Style
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
headerStyle.setAlignment(headerStyle.ALIGN_CENTER);
headerStyle.setFont(headerFont);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
int rowCount= 0;
Row header;
header = sheet.createRow(0);//its for header 
Cell cell ;//= header.createCell(0);
for(int j = 0;j < 4; j++) {
    cell = header.createCell(j);
    if(j == 0) {
        cell.setCellValue("ItemWise List");
    }
    cell.setCellStyle(headerStyle);
}
sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, lstReportFormHeader.size()-1));
header = sheet.createRow(0);
        cell = header.createCell(0);
cell.setCellValue("Sr. No");
        cell = header.createCell(1);
cell.setCellValue("Item Name");
        cell = header.createCell(2);
cell.setCellValue("Qty");
        cell = header.createCell(3);
cell.setCellValue("Rate");

现在我想将 ItemWise 列表合并并使其居中对齐。

答案 1

我的解决方案是按位置合并单元格，然后创建一个单元格（引用合并单元格的第一个块）来分配一个值，然后通过CellUtil设置对齐方式

// Merges the cells
CellRangeAddress cellRangeAddress = new CellRangeAddress(start, start, j, j + 1);
sheet.addMergedRegion(cellRangeAddress);

// Creates the cell
Cell cell = CellUtil.createCell(row, j, entry.getKey());

// Sets the allignment to the created cell
CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);

答案 2

合并如下：：：

 Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow((short) 1);
Cell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new CellRangeAddress(
        1, //first row (0-based)
        1, //last row  (0-based)
        1, //first column (0-based)
        2  //last column  (0-based)
));

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

要对齐，请检查以下Apache poi的官方链接：：：

http://poi.apache.org/spreadsheet/quick-guide.html#Alignment