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

Merge and align center cell using apache poi

提问by shrey

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 Listmerge and make it align center.

现在我想ItemWise List合并并使其居中对齐。

回答by dev

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#Alignment

回答by Sankumarsingh

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
    CellReference startCellRef= new CellReference(startCell.getRowIndex(),startCell.getColumnIndex());
    CellReference endCellRef = new CellReference(endCell.getRowIndex(),endCell.getColumnIndex());
    // forming string of references; will result like $A:$B 
    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);
}

回答by shrey

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

回答by RageAgainstTheMachine

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

回答by Fritz

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 设置为 CENTER ALIGN。是在将 cellstyle 值设置为 center 之前还是之后放置 XSSFSheet.addMergedRegion() 方法并不重要。

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

}

回答by Nikhil G

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

快乐编码

回答by Ivan Huang

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

回答by Mahaveer Jangir

Use

用

style.setVerticalAlignment()

to set the vertical alignments instead of

设置垂直对齐而不是

style.setAlignment().                             

回答by Sarath Avanavu

We can merge the column along with we can vertically and horizontally align too.

我们可以合并列，也可以垂直和水平对齐。

I had the rows 2 to 10 of column A having the same values.

我有 A 列的第 2 到 10 行具有相同的值。

I used the below code to merge the data where the variable sheetis XSSFSheet. The parameters of CellRangeAddresshave the parameters are start row, last row, start column and last column. In my example, the value USAstarts from 2nd row (index is 1) and the last value of USAis in 10th row and column is the 1st column.

我用下面的代码进行合并，其中变量的数据sheet是XSSFSheet。参数CellRangeAddress有参数是起始行、最后一行、起始列和最后一列。在我的示例中，值USA从第 2 行（索引为 1）开始，最后一个值USA在第 10 行，列是第 1 列。

CellRangeAddress ca = new CellRangeAddress(1,9,0,0);
sheet.addMergedRegion(ca);

When I executed the above code, the cell was merged but the text was not aligned to center.

当我执行上面的代码时，单元格被合并了，但文本没有居中对齐。

To overcome this issue, I utilized the class CellStyleand Cell. Get the 2nd row of 1st column text to cellvariable. Now set the vertical and horizontal alignment to the cellStyleand set this style to the cellwhich will align the text to the center.

为了解决这个问题，我使用了类CellStyle和Cell. 获取第一列文本的第二行cell变量。现在将垂直和水平对齐设置为cellStyle，并将此样式设置为 ，cell这将使文本居中对齐。

Cell cell = sheet.getRow(1).getCell(0);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(cellStyle);

Below is the final result

下面是最终结果

Additional references:

其他参考：

1. Jar files used

1. 使用的 Jar 文件

1. Import statements

   

2. 导入语句