catalogue
POI merge cell -- CellRangeAddress
1. Merge according to the specified column and merge cells with the same content upward
2. Merge according to the specified column and merge cells with the same content upward
In short
EasyExcel is an open source project of simple, memory saving read-write Excel based on Java. Support reading and writing 100M excel while saving memory as much as possible. Excel export has also been done in previous development, but it is exported through tool class calls that have been encapsulated by predecessors of the company (Apache poi used by the company). Add # EasyExcel to your project for practice in case of need.
EasyExcel version
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>POI merge cell -- CellRangeAddress
CellRangeAddress has four parameters: start line number, end line number, start column number and end column number
CellRangeAddress(2, 3, 4, 5)； Represents the combined regional range E3, F4 Starting from line 2, ending at line 3, starting at column 4 and ending at column 5.
be careful:
1. The starting line number cannot be greater than the ending line number (error: new CellRangeAddress(1, 0, 0, 0)).
2. The starting column number cannot be greater than the ending column number (error: new CellRangeAddress(0, 0, 1, 0)).
3. The row and column of EXECL start from 0, not 1.
The cell merge display style of a sheet page is controlled by the CellRangeAddress collection in a sheet page.
EasyExcel user defined merging cells is to operate the CellRangeAddress collection in each sheet page!
Custom policy
The following is the strategy of merging cells (the code is for reference and understanding only, and the specific strategy needs to be customized according to its own actual business!)
Custom cell style
CellStyle:
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
public class CellStyle {
public static HorizontalCellStyleStrategy getCellCenterStyle(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//Set background color
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//Set header font
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//Set header Center
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//Content strategy
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//Set horizontal center
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//Set vertical center
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}Custom cell merge Handler
1. Merge according to the specified column and merge cells with the same content upward
ExcelFillCellLineMergeHandler:
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @author Inori
* @version 1.0.0-SNAPSHOT
* @Description : Custom merge cell policy Handler column merge merges the same cells up
* @create 2022-01-27 9:52
*/
public class ExcelFillCellLineMergeHandler implements CellWriteHandler {
private int[] mergeColumnIndex; //Column example of user-defined merged cells: int[] mergeColumeIndex = {0, 1, 11};
private int mergeRowIndex; //Generally speaking, the row height of the filling header of a user-defined merged cell is 3. For example, if the high order of the row is 3, int mergeRowIndex = 3;
public ExcelFillCellLineMergeHandler() {
}
public ExcelFillCellLineMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
//Operation before cell creation
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
//Operation after cell creation
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
//The afterCellDispose method is an opportunity after the cell is created and before it is destroyed. At this time, we can change the cell content.
//mergeColumnIndex: for example, int [] mergeColumnIndex = {0, 1, 11};
//mergeRowIndex defines the rows of merged cells. / / generally, the height of the filling header row is higher. For example, if the high order of the header row is 3, int mergeRowIndex = 3;
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int curRowIndex = cell.getRowIndex(); //Number of rows in the current cell
int curColIndex = cell.getColumnIndex(); // Number of columns in the current cell
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
//Cell data processing
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* @description Merge current cell up
* cell Current cell
* curRowIndex Current row
* curColIndex Current column
* @author Inori
* @date 2022/1/26 15:50
* @params [writeSheetHolder, cell, curRowIndex, curColIndex]
* @return void
* @throws
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//Data in current cell
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//Gets the previous cell of the current cell
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
//Gets the data in the previous cell of the current cell
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// Compares the current cell data with the previous cell data
if (preData.equals(curData)) {
//Get the current sheet page
Sheet sheet = writeSheetHolder.getSheet();
//Get all merged cells
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//Merge
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
//CellRangeAddress POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If the previous cell has been merged, move out the original merging cell first, and then add the merging cell again
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// If the previous cell has not been merged, a new merged cell will be added
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
Code call (the data is just fun, and I really can't make it up!)
//The same header of Excel will be merged automatically. The content data of Excel table will be merged in the specified column, starting from the specified row! Merge with the same column content!
@PostMapping("/tableDataLineMerge")
public void tableDataLineMerge(HttpServletResponse response) throws IOException {
//Excel header data
List<List<String>> headers=new ArrayList<>();
//Basic information
//Serial number
List<String> baseSerialHead=new ArrayList<>();
baseSerialHead.add("Basic information");
baseSerialHead.add("Basic information");
baseSerialHead.add("Serial number");
//business
List<String> baseShopHead=new ArrayList<>();
baseShopHead.add("Basic information");
baseShopHead.add("Basic information");
baseShopHead.add("business");
//particular year
List<String> baseYearHead=new ArrayList<>();
baseYearHead.add("Basic information");
baseYearHead.add("Basic information");
baseYearHead.add("particular year");
//Product list (drinks)
//mineral water
//Wahaha
List<String> DrinkWahahaHead=new ArrayList<>();
DrinkWahahaHead.add("drink");
DrinkWahahaHead.add("mineral water");
DrinkWahahaHead.add("Wahaha");
//NongFu Spring
List<String> DrinkNongfuHead=new ArrayList<>();
DrinkNongfuHead.add("drink");
DrinkNongfuHead.add("mineral water");
DrinkNongfuHead.add("NongFu Spring");
//Drinks
//arctic ocean
List<String> DrinkArcticHead=new ArrayList<>();
DrinkArcticHead.add("drink");
DrinkArcticHead.add("Drinks");
DrinkArcticHead.add("arctic ocean");
//Hongbaolai
List<String> DrinkHBLHead=new ArrayList<>();
DrinkHBLHead.add("drink");
DrinkHBLHead.add("Drinks");
DrinkHBLHead.add("Hongbaolai");
//Excel header data filling
headers.add(baseSerialHead);
headers.add(baseShopHead);
headers.add(baseYearHead);
headers.add(DrinkWahahaHead);
headers.add(DrinkNongfuHead);
headers.add(DrinkArcticHead);
headers.add(DrinkHBLHead);
//Excel table content data
List<List<String>> datas=new ArrayList<>();
//First row of data
List<String> dataFirst=new ArrayList<>();
dataFirst.add("1");
dataFirst.add("A");
dataFirst.add("2010");
dataFirst.add("1.0");
dataFirst.add("1.5");
dataFirst.add("5.0");
dataFirst.add("1.5");
//Second row data
List<String> dataSecond=new ArrayList<>();
dataSecond.add("2");
dataSecond.add("A");
dataSecond.add("2020");
dataSecond.add("2.0");
dataSecond.add("2.5");
dataSecond.add("8.0");
dataSecond.add("2.0");
//Third row data
List<String> dataThird=new ArrayList<>();
dataThird.add("3");
dataThird.add("B");
dataThird.add("2010");
dataThird.add("0.9");
dataThird.add("1.4");
dataThird.add("4.5");
dataThird.add("2.0");
//Fourth row data
List<String> dataFourth=new ArrayList<>();
dataFourth.add("4");
dataFourth.add("B");
dataFourth.add("2020");
dataFourth.add("2.0");
dataFourth.add("2.5");
dataFourth.add("8.0");
dataFourth.add("2.0");
//Excel table content data filling
datas.add(dataFirst);
datas.add(dataSecond);
datas.add(dataThird);
datas.add(dataFourth);
//excel generation
// Note that some students have reported that using swagger will lead to various problems. Please use the browser or postman directly
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder.encode("Specify column merge", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//Customize the columns of merged cells (specify the merged columns according to their own data): for example, we want to merge businesses
int[] mergeColumeIndex = {1};
//Customize rows of merged cells
int mergeRowIndex = 3;//It is generally set to Excel header data high
//All configurations are inherited, and the configuration of Workbook will be inherited by sheet, so when setting parameters with EasyExcel, in EasyExcel Before the sheet () method, the scope is the whole sheet, and then for a single sheet
EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelFillCellLineMergeHandler(mergeRowIndex, mergeColumeIndex)).head(headers).sheet("sheet1").doWrite(datas);
}Call presentation:
Before consolidation:
After merger
2. Merge according to the specified column and merge cells with the same content upward
ExcelFillCellRowMergeHandler:
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.wty.model.CellLineRange;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @author Inori
* @version 1.0.0-SNAPSHOT
* @Description : Custom merge cell policy Handler row merge merges the same cell to the left
* @create 2022-01-27 9:52
*/
public class ExcelFillCellRowMergeHandler implements CellWriteHandler {
private List<CellLineRange> cellLineRangeList; //If you want to merge the 4th and 5th, 6th and 7th columns of custom merged cells: [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
private int mergeRowIndex; //The row at the beginning of the user-defined merged cell. Generally speaking, the filling header row height of - 1 means that the merging starts from each column under the header: if the high order of the header row is 3, int mergeRowIndex = 2;
public ExcelFillCellRowMergeHandler() {
}
public ExcelFillCellRowMergeHandler(List<CellLineRange> cellLineRangeList, int mergeRowIndex) {
this.cellLineRangeList=cellLineRangeList;
this.mergeRowIndex=mergeRowIndex;
}
//Operation before cell creation
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
//Operation after cell creation
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
//The afterCellDispose method is an opportunity after the cell is created and before it is destroyed. At this time, we can change the cell content.
//cellLineRangeList customize the columns of merged cells. If you want to merge columns 4 and 5, 6 and 7: [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
//mergeRowIndex the rows of custom merged cells. Generally speaking, the height of the filling header row - 1 means merging from each column under the header: if the high order of the header row is 3, int mergeRowIndex = 2;
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int curRowIndex = cell.getRowIndex(); //Number of rows in the current cell
int curColIndex = cell.getColumnIndex(); // Number of columns in the current cell
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < cellLineRangeList.size(); i++) {
if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
//Cell data processing
mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* @description Merge the current cell to the left
* @author Inori
* @date 2022/1/27 15:39
* @params [writeSheetHolder, cell, curRowIndex, curColIndex]
* @return void
* @throws
*/
private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//Data in current cell
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//Gets the left cell of the current cell
Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
//Gets the data in the left cell of the current cell
Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
// Compare the current cell data with the one on the left
if (leftData.equals(curData)) {
//Get the current sheet page
Sheet sheet = writeSheetHolder.getSheet();
//Get all merged cells
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//Merge
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
//CellRangeAddress POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If a cell on the left has been merged, move out the original merging cell first, and then add the merging cell again
if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// If a cell on the left is not merged, a new merged cell will be added
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, curColIndex- 1, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
CellLineRange:
import lombok.AllArgsConstructor;
import lombok.Data;
/**
* @author Inori
* @version 1.0.0-SNAPSHOT
* @Description :
* @create 2022-01-27 14:07
*/
@Data
@AllArgsConstructor
public class CellLineRange {
private int firstCol; //Start column
private int lastCol; //End column
}
Code call (the data is just fun, and I really can't make it up!)
//The same Excel header will be merged automatically. The Excel table content data will be merged within the specified column range, starting after the specified row! Merge lines with the same content!
@PostMapping("/tableDataRowMerge")
public void tableDataRowMerge(HttpServletResponse response) throws IOException {
//Excel header data
List<List<String>> headers=new ArrayList<>();
//Basic information
//Serial number
List<String> baseSerialHead=new ArrayList<>();
baseSerialHead.add("Basic information");
baseSerialHead.add("Basic information");
baseSerialHead.add("Serial number");
//business
List<String> baseShopHead=new ArrayList<>();
baseShopHead.add("Basic information");
baseShopHead.add("Basic information");
baseShopHead.add("business");
//particular year
List<String> baseYearHead=new ArrayList<>();
baseYearHead.add("Basic information");
baseYearHead.add("Basic information");
baseYearHead.add("particular year");
//Product list (drinks)
//mineral water
//Wahaha
List<String> DrinkWahahaHead=new ArrayList<>();
DrinkWahahaHead.add("drink");
DrinkWahahaHead.add("mineral water");
DrinkWahahaHead.add("Wahaha");
//NongFu Spring
List<String> DrinkNongfuHead=new ArrayList<>();
DrinkNongfuHead.add("drink");
DrinkNongfuHead.add("mineral water");
DrinkNongfuHead.add("NongFu Spring");
//Drinks
//arctic ocean
List<String> DrinkArcticHead=new ArrayList<>();
DrinkArcticHead.add("drink");
DrinkArcticHead.add("Drinks");
DrinkArcticHead.add("arctic ocean");
//Hongbaolai
List<String> DrinkHBLHead=new ArrayList<>();
DrinkHBLHead.add("drink");
DrinkHBLHead.add("Drinks");
DrinkHBLHead.add("Hongbaolai");
//Excel header data filling
headers.add(baseSerialHead);
headers.add(baseShopHead);
headers.add(baseYearHead);
headers.add(DrinkWahahaHead);
headers.add(DrinkNongfuHead);
headers.add(DrinkArcticHead);
headers.add(DrinkHBLHead);
//Excel table content data
List<List<String>> datas=new ArrayList<>();
//First row of data
List<String> dataFirst=new ArrayList<>();
dataFirst.add("1");
dataFirst.add("A");
dataFirst.add("2010");
dataFirst.add("1.5");
dataFirst.add("1.5");
dataFirst.add("5.0");
dataFirst.add("1.5");
//Second row data
List<String> dataSecond=new ArrayList<>();
dataSecond.add("2");
dataSecond.add("A");
dataSecond.add("2020");
dataSecond.add("2.0");
dataSecond.add("2.0");
dataSecond.add("2.0");
dataSecond.add("2.0");
//Third row data
List<String> dataThird=new ArrayList<>();
dataThird.add("3");
dataThird.add("B");
dataThird.add("2010");
dataThird.add("0.9");
dataThird.add("1.4");
dataThird.add("4.5");
dataThird.add("2.0");
//Fourth row data
List<String> dataFourth=new ArrayList<>();
dataFourth.add("4");
dataFourth.add("B");
dataFourth.add("2020");
dataFourth.add("2.0");
dataFourth.add("2.5");
dataFourth.add("8.0");
dataFourth.add("8.0");
//Excel table content data filling
datas.add(dataFirst);
datas.add(dataSecond);
datas.add(dataThird);
datas.add(dataFourth);
//excel generation
// Note that some students have reported that using swagger will lead to various problems. Please use the browser or postman directly
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder.encode("Specify column merge", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//Customize the columns of merged cells
ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
cellLineRanges.add(new CellLineRange(3,4));
cellLineRanges.add(new CellLineRange(5,6));
//Customize the row that starts merging cells
int mergeRowIndex = 2;//Generally, if it is set to the high-order 3 of Excel header data, it will be 2, for example: 0,1,2
//All configurations are inherited, and the configuration of Workbook will be inherited by sheet, so when setting parameters with EasyExcel, in EasyExcel Before the sheet () method, the scope is the whole sheet, and then for a single sheet
EasyExcel.write(response.getOutputStream()).registerWriteHandler(getCellCenterStyle()).registerWriteHandler(new ExcelFillCellRowMergeHandler(cellLineRanges,mergeRowIndex)).head(headers).sheet("sheet1").doWrite(datas);
}Call presentation:
Before consolidation:
After consolidation:
3. Automatically merge cells. Rows are merged primarily and columns are merged secondarily (merge if the contents are the same)
(I just merge casually without controlling the range of cell merging! If you want to use it later, you can define the effective range of cell!)
ExcelFillCellMergeHandler:
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @author Inori
* @version 1.0.0-SNAPSHOT
* @Description : Automatically merge cells. Rows are merged primarily and columns are merged secondarily
* @create 2022-01-27 15:28
*/
public class ExcelFillCellMergeHandler implements CellWriteHandler {
private int mergeColumnIndex; // Customize the columns of merged cells, such as starting from the first column, int mergeColumnIndex=0;
private int mergeRowIndex; //The default filling height of the row of user-defined merged cells. For example, if the high order of the row is 3, int mergeRowIndex = 2;
public ExcelFillCellMergeHandler(){
}
public ExcelFillCellMergeHandler(int mergeColumnIndex,int mergeRowIndex){
this.mergeColumnIndex=mergeColumnIndex;
this.mergeRowIndex=mergeRowIndex;
}
//Operation before cell creation
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
//Operation after cell creation
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
//The afterCellDispose method is an opportunity after the cell is created and before it is destroyed. At this time, we can change the cell content.
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int curRowIndex = cell.getRowIndex(); //Number of rows in the current cell
int curColIndex = cell.getColumnIndex(); // Number of columns in the current cell
if (curRowIndex>mergeRowIndex&&curColIndex>mergeColumnIndex){
mergeCell(writeSheetHolder, cell, curRowIndex, curColIndex);
}
}
/**
* @return void
* @throws
* @description Merge current cell up
* cell Current cell
* curRowIndex Current row
* curColIndex Current column
* @author Inori
* @date 2022/1/26 15:50
* @params [writeSheetHolder, cell, curRowIndex, curColIndex]
*/
private void mergeCell(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//Data in current cell
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//Gets the previous cell of the current cell
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
//Gets the data in the previous cell of the current cell
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//Gets the left cell of the current cell
Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
//Gets the data in the left cell of the current cell
Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
//Is the data of a cell on the left of the current cell the same as the current cell
boolean left = leftData.equals(curData);
//Is the data of the previous cell of the current cell the same as that of the current cell
boolean pre = preData.equals(curData);
if (left) {
//Get the current sheet page
Sheet sheet = writeSheetHolder.getSheet();
//Get all merged cells
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//Judge whether the left cell participates in the consolidation. true participates in the consolidation, false does not participate
boolean leftIsMerged = false;
//Merge cells on the left
CellRangeAddress cellRangeLeftAddr = null;
for (int i = 0; i < mergeRegions.size() && !leftIsMerged; i++) {
//CellRangeAddress Description: POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
//Merge cells on the left
cellRangeLeftAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If the left cell has been merged, move out the original merged cell first, and then conduct data processing
if (cellRangeLeftAddr.isInRange(curRowIndex, curColIndex - 1)) {
sheet.removeMergedRegion(i);
// cellRangeLeftAddr.setLastColumn(curColIndex);
leftIsMerged = true;
}
}
//Whether the cell on the left participates in the consolidation. true participates in the consolidation. false does not participate
if (leftIsMerged) {
if (pre) {
//Can I merge with the above cells? true yes false no
boolean preIsMerged = false;
for (int i = 0; i < mergeRegions.size() && !preIsMerged; i++) {
//CellRangeAddress POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
//Merge cells above
CellRangeAddress cellRangePreAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If the previous cell has been merged
if (cellRangePreAddr.isInRange(curRowIndex - 1, curColIndex)) {
//Judge whether to merge with the above merged cells
//Judge whether the starting column of the merged area is the same as the starting column of the merged cell on the left, and whether the last column is the same as the current cell column!
if (cellRangePreAddr.getFirstColumn() == cellRangeLeftAddr.getFirstColumn()
&&
cellRangePreAddr.getLastColumn() == curColIndex
) {
//Merge the current cell with the merged cells above
sheet.removeMergedRegion(i);
cellRangePreAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangePreAddr);
preIsMerged = true;
}
//Jump out of current loop
break;
}
}
if (!preIsMerged) {
//Merge the current cell row
cellRangeLeftAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeLeftAddr);
}
} else {
//If the starting row of the left merged cell is equal to the current row!
if (cellRangeLeftAddr.getFirstRow()==curRowIndex){
//Merge the current cell row
cellRangeLeftAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeLeftAddr);
}else {
//Subtract one row from the left merged cell
cellRangeLeftAddr.setLastRow(curRowIndex-1);
//Generate the current cell row merge from New
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, cellRangeLeftAddr.getFirstColumn(), curColIndex);
sheet.addMergedRegion(cellRangeLeftAddr);
sheet.addMergedRegion(cellRangeAddress);
}
}
} else {
//Generate new row and merge cells!
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex - 1, curColIndex);
if (pre) {
//Can I merge with the above cells? true yes false no
boolean preIsMerged = false;
for (int i = 0; i < mergeRegions.size() && !preIsMerged; i++) {
//CellRangeAddress POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
//Merge cells above
CellRangeAddress cellRangePreAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If the previous cell has been merged
if (cellRangePreAddr.isInRange(curRowIndex - 1, curColIndex)) {
//Judge whether to merge with the above merged cells
//Judge whether the starting column of the merged range is the same as the starting column of the new merged cell, and whether the last column is the same as the current cell column!
if (cellRangePreAddr.getFirstColumn() == cellRangeAddress.getFirstColumn()
&&
cellRangePreAddr.getLastColumn() == cellRangeAddress.getLastColumn()
) {
sheet.removeMergedRegion(i);
cellRangePreAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangePreAddr);
preIsMerged = true;
}
//Jump out of current loop
break;
}
}
if (!preIsMerged) {
//Add merged cell
sheet.addMergedRegion(cellRangeAddress);
}
}else {
//Add merged cell
sheet.addMergedRegion(cellRangeAddress);
}
}
} else {
if (pre) {
//Get the current sheet page
Sheet sheet = writeSheetHolder.getSheet();
//Get all merged cells
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//Judge whether to merge with the above merged cells. false
boolean preIsMerged = false;
for (int i = 0; i < mergeRegions.size() && !preIsMerged; i++) {
//CellRangeAddress POI merge cells
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//Example: CellRangeAddress (2, 6000, 3, 3);
//From line 2, line 6000 ends, column 3 starts and column 3 ends.
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd) determines whether a given coordinate is within the range of this range.
// If the previous cell has been merged, move out the original merging cell first, and then add the merging cell again
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
//Whether to merge the current column. If yes, merge
if (cellRangeAddr.getFirstColumn()==curColIndex&&cellRangeAddr.getLastColumn()==curColIndex){
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
}
preIsMerged = true;
break;
}
}
// If the previous cell has not been merged, a new merged cell will be added
if (!preIsMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
}
Code call (the data is just fun, and I really can't make it up!)
//Automatically merge cells. Rows are merged primarily and columns are merged secondarily (merge if the contents are the same)
@PostMapping("/tableDataMerge")
public void tableDataMerge(HttpServletResponse response) throws IOException {
//Excel header data
List<List<String>> headers=new ArrayList<>();
//Basic information
//Serial number
List<String> baseSerialHead=new ArrayList<>();
baseSerialHead.add("Basic information");
baseSerialHead.add("Basic information");
baseSerialHead.add("Serial number");
//business
List<String> baseShopHead=new ArrayList<>();
baseShopHead.add("Basic information");
baseShopHead.add("Basic information");
baseShopHead.add("business");
//particular year
List<String> baseYearHead=new ArrayList<>();
baseYearHead.add("Basic information");
baseYearHead.add("Basic information");
baseYearHead.add("particular year");
//Product list (drinks)
//mineral water
//Wahaha
List<String> DrinkWahahaHead=new ArrayList<>();
DrinkWahahaHead.add("drink");
DrinkWahahaHead.add("mineral water");
DrinkWahahaHead.add("Wahaha");
//NongFu Spring
List<String> DrinkNongfuHead=new ArrayList<>();
DrinkNongfuHead.add("drink");
DrinkNongfuHead.add("mineral water");
DrinkNongfuHead.add("NongFu Spring");
//Drinks
//arctic ocean
List<String> DrinkArcticHead=new ArrayList<>();
DrinkArcticHead.add("drink");
DrinkArcticHead.add("Drinks");
DrinkArcticHead.add("arctic ocean");
//Hongbaolai
List<String> DrinkHBLHead=new ArrayList<>();
DrinkHBLHead.add("drink");
DrinkHBLHead.add("Drinks");
DrinkHBLHead.add("Hongbaolai");
//Wine
//Tsingtao Beer
List<String> DrinkQDHead=new ArrayList<>();
DrinkQDHead.add("drink");
DrinkQDHead.add("Wine");
DrinkQDHead.add("Tsingtao Beer");
//Excel header data filling
headers.add(baseSerialHead);
headers.add(baseShopHead);
headers.add(baseYearHead);
headers.add(DrinkWahahaHead);
headers.add(DrinkNongfuHead);
headers.add(DrinkArcticHead);
headers.add(DrinkHBLHead);
headers.add(DrinkQDHead);
//Excel table content data
List<List<String>> datas=new ArrayList<>();
//First row of data
List<String> dataFirst=new ArrayList<>();
dataFirst.add("1");
dataFirst.add("A");
dataFirst.add("2010");
dataFirst.add("1.5");
dataFirst.add("1.5");
dataFirst.add("5.0");
dataFirst.add("1.5");
dataFirst.add("6.0");
//Second row data
List<String> dataSecond=new ArrayList<>();
dataSecond.add("2");
dataSecond.add("A");
dataSecond.add("2020");
dataSecond.add("2.0");
dataSecond.add("2.0");
dataSecond.add("2.0");
dataSecond.add("2.0");
dataSecond.add("8.0");
//Third row data
List<String> dataThird=new ArrayList<>();
dataThird.add("3");
dataThird.add("B");
dataThird.add("2010");
dataThird.add("0.9");
dataThird.add("2.5");
dataThird.add("8.0");
dataThird.add("8.0");
dataThird.add("8.0");
//Fourth row data
List<String> dataFourth=new ArrayList<>();
dataFourth.add("4");
dataFourth.add("B");
dataFourth.add("2020");
dataFourth.add("2.0");
dataFourth.add("2.5");
dataFourth.add("8.0");
dataFourth.add("8.0");
dataFourth.add("8.1");
//The fifth row of data
List<String> dataFifth=new ArrayList<>();
dataFifth.add("5");
dataFifth.add("c");
dataFifth.add("2010");
dataFifth.add("2.0");
dataFifth.add("2.5");
dataFifth.add("8.0");
dataFifth.add("8.0");
dataFifth.add("8.1");
//The sixth row of data
List<String> dataSixth=new ArrayList<>();
dataSixth.add("6");
dataSixth.add("c");
dataSixth.add("2020");
dataSixth.add("2.0");
dataSixth.add("2.5");
dataSixth.add("7.0");
dataSixth.add("8.0");
dataSixth.add("8.0");
//Excel table content data filling
datas.add(dataFirst);
datas.add(dataSecond);
datas.add(dataThird);
datas.add(dataFourth);
datas.add(dataFifth);
datas.add(dataSixth);
//excel generation
// Note that some students have reported that using swagger will lead to various problems. Please use the browser or postman directly
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder.encode("Rows and columns are merged automatically, with row merging as the primary and column merging as the secondary", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
int mergeColumnIndex =0;
int mergeRowIndex = 2;//Generally, if it is set to the high-order 3 of Excel header data, it will be 2, for example: 0,1,2
//All configurations are inherited, and the configuration of Workbook will be inherited by sheet, so when setting parameters with EasyExcel, in EasyExcel Before the sheet () method, the scope is the whole sheet, and then for a single sheet
EasyExcel.write(response.getOutputStream()).registerWriteHandler(getCellCenterStyle()).registerWriteHandler(new ExcelFillCellMergeHandler(mergeColumnIndex,mergeRowIndex)).head(headers).sheet("sheet1").doWrite(datas);
}Call presentation:
Before consolidation:
After merger
