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

I want to export data to excel using Apache poi.
Now the problem that I am facing is that I am unable to merge rows and align them in the center.

我想使用Apache poi将数据导出到excel中。现在我面临的问题是，我无法合并行并在中心对齐它们。

Code for export data is:

出口数据代码为:

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");

Now I want to ItemWise List merge and make it align center.

现在我想要列表合并并使它对齐中心。

8 个解决方案

#1

My solution was to merge the cells by their positions, then created a cell (reference to the first block of the merged cells) to assign a value and then set the alignment throught the CellUtil

我的解决方案是按单元格的位置合并单元格，然后创建一个单元格(引用合并单元格的第一个块)来分配一个值，然后通过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

Merge like:::

合并:::

 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();

For aligning also check the below official link of Apache poi:::

如需对齐，请查看以下Apache poi的官方链接:::

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

http://poi.apache.org/spreadsheet/quick-guide.html对齐

#3

After study I found that after merging 7 cells, merged cell id will be 0 so I applied following style to cell id 0 using following style.

经过研究，我发现合并7个单元后，合并的单元id将为0，所以我使用以下样式将如下样式应用到单元id为0。

headerStyle.setAlignment(headerStyle.ALIGN_CENTER);

#4

As per my understanding, you have start and end cells for merging and you want to merge the cell ranges and align the cell content. If I am right, you can use the following method:

根据我的理解，您有用于合并的开始和结束单元格，您希望合并单元格范围并对齐单元格内容。如果我是对的，你可以用以下方法:

/**
 * @param startCell: first cell of merging area
 * @param endCell: last cell of merging area
 */

public static void mergeAndAlignCenter(HSSFCell startCell, HSSFCell endCell){
    //finding reference of start and end cell; will result like $A$1
    CellReference startCellRef= new CellReference(startCell.getRowIndex(),startCell.getColumnIndex());
    CellReference endCellRef = new CellReference(endCell.getRowIndex(),endCell.getColumnIndex());
    // forming string of references; will result like $A$1:$B$5 
    String cellRefernce = startCellRef.formatAsString()+":"+endCellRef.formatAsString();
    //removing $ to make cellRefernce like A1:B5
    cellRefernce = cellRefernce.replace("$","");
    //passing cellRefernce to make a region 
    CellRangeAddress region = CellRangeAddress.valueOf(cellRefernce);
    //use region to merge; though other method like sheet.addMergedRegion(new CellRangeAddress(1,1,4,1));
    // is also available, but facing some problem right now.
    startCell.getRow().getSheet().addMergedRegion( region );
    //setting alignment to center
    CellUtil.setAlignment(startCell, wb, CellStyle.ALIGN_CENTER);
}

#5

Well what worked for me is to set all the merged cells' Cellstyle to CENTER ALIGN. Whether you put the XSSFSheet.addMergedRegion() method before or after setting the cellstyle values to center don't matter.

对我来说起作用的是将所有合并的单元格的Cellstyle设置为居中对齐。无论您将XSSFSheet.addMergedRegion()方法放在居中还是设置cellstyle值之后，都没有关系。

    private void insertXlsHeader(XSSFSheet sheet){
    ....
    //first cell for row1       
    cell = row1.createCell(colstart);
    cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
    cell.setCellValue("COURSES");
    setHeaderCellStyle(sheet,cell);

    //first cell for row2
    cell = row2.createCell(colstart);
    setHeaderCellStyle(sheet,cell);

    //first cell for row3
    cell = row3.createCell(colstart);
    setHeaderCellStyle(sheet,cell);

    //merged the first cells of rows 1 to 3
    sheet.addMergedRegion(new CellRangeAddress(ROW1, ROW3, colstart, colstart));
    ...
    }

private void setHeaderCellStyle(XSSFSheet sheet,org.apache.poi.ss.usermodel.Cell cell) {
    CellStyle s = null;

        s = sheet.getWorkbook().createCellStyle();
        cell.setCellStyle(s);

    Font f = sheet.getWorkbook().createFont();

    f.setBoldweight(Font.BOLDWEIGHT_BOLD);


    s.setBorderBottom(CellStyle.BORDER_THIN);
    s.setBorderLeft(CellStyle.BORDER_THIN);
    s.setBorderRight(CellStyle.BORDER_THIN);
    s.setBorderTop(CellStyle.BORDER_THIN);
    s.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    s.setAlignment(CellStyle.ALIGN_CENTER);
    s.setFont(f);

}

#6

As answered above, merging cells can be achieved using

如上所述，可以使用合并单元格实现合并

sheet.addMergedRegion(new CellRangeAddress(frstRow, lastRow, firstColumnIndex, lastColumnIndex));

But for aligning cells vertically,recently I faced similar issue and I tried above answer, but using

但是对于垂直对齐的单元格，最近我遇到了类似的问题，我尝试了上面的答案，但是使用了

CellUtil.setAlignment(dataCell, workbook, CellStyle.VERTICAL_CENTER);

aligned Date formatted cells to Horizontal Left aligned. So I used following method to set only Vertical Alignment of Cell content.

对齐日期格式化单元格到水平左对齐。因此，我使用以下方法只设置单元内容的垂直对齐。

CellUtil.setCellStyleProperty(dataCell, workbook,CellUtil.VERTICAL_ALIGNMENT,CellStyle.VERTICAL_CENTER);

I hope this helps!!

我希望这有助于! !

Happy Coding

快乐的编码

#7

This worked for me and I think it's cleaner:

这对我很有效，而且我认为它更简洁:

/**
 * Merge and center the cells specified by range
 * @param startCell the first cell in the cells to be merged
 * @param range the range of the cells to be merged
 */
private static void mergeAndCenter(Cell startCell, CellRangeAddress range) {
    startCell.getSheet().addMergedRegion(range);
    CellStyle style = startCell.getSheet().getWorkbook().createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    startCell.setCellStyle(style);
}

#8

Use style.setVerticalAlignment() to set the vertical alignments instead of style.setAlignment().

使用style.setvertical对齐()来设置垂直对齐，而不设置style. setali()。