转载在C站上面看到一个大神写的自定义封装方法
转载地址:springboot 使用Poi 自定义封装方法 合并excel中的单元格_Tongyao-CSDN博客_springboot合并单元格
之前都是使用sheet.addMergedRegion()方法一个一个计算合并单元格,还浪费大量时间,后来我自己特意封装了一个方法分享给大家,即拿即用,加几个就合并几个单元格方法。下面会给大家提供完整代码!
//合并单元格(你的sheet页,你的标题所占用的行,你的哪一列单元格需要合并)
mergeCell(sheet,1,3);
使用前
使用后:
合并单元格封装的方法:
/**
* 合并单元格
* @author tongyao
* @param sheet sheet页
* @param titleColumn 标题占用行
* @param cellIndex 那个单元格
*/
public static void mergeCell(HSSFSheet sheet,int titleColumn,int cellIndex){
//多少行
int rowCount = sheet.getPhysicalNumberOfRows();
String cellText = "";
//开始下标
int startIndex = 0;
//结束下标
int endIndex = 0;
HSSFRow row = null;
Cell cell = null;
for(int i = titleColumn;i<rowCount;i++){
row = sheet.getRow(i);
cell = row.getCell(cellIndex);
if(!cell.getStringCellValue().equals(cellText)){
if(startIndex != 0){
endIndex = (i-1);
sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
}
cellText = cell.getStringCellValue();
startIndex = i;
}
}
endIndex = (rowCount-1);
sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
}
完整代码:
@GetMapping("/export")
public void export(HttpServletResponse response){
HSSFWorkbook wb = new HSSFWorkbook();
// 根据页面index 获取sheet页
HSSFSheet sheet = wb.createSheet("Sheet1");
sheet.setColumnWidth(0,10000);
sheet.setColumnWidth(1,10000);
sheet.setColumnWidth(2,10000);
sheet.setColumnWidth(3,10000);
HSSFRow row1Title1 = sheet.createRow(0);
Cell row1Cell1Title1 = row1Title1.createCell(0);
row1Cell1Title1.setCellValue("序号");
Cell row1Cell1Title2 = row1Title1.createCell(1);
row1Cell1Title2.setCellValue("码云主页");
Cell row1Cell1Title3 = row1Title1.createCell(2);
row1Cell1Title3.setCellValue("CSDN主页");
Cell row1Cell1Title4 = row1Title1.createCell(3);
row1Cell1Title4.setCellValue("姓名");
for(int i = 1;i< 20;i++){
HSSFRow row = sheet.createRow(i);
Cell row_cell1 = row.createCell(0);
if(i <= 5){
row_cell1.setCellValue("1");
}else if(i <= 10 && i > 5){
row_cell1.setCellValue("2");
}else{
row_cell1.setCellValue("3");
}
Cell row_cell2 = row.createCell(1);
row_cell2.setCellValue("码云:https://gitee.com/Super_TongYao");
Cell row_cell3 = row.createCell(2);
row_cell3.setCellValue("CSDN主页:https://blog.csdn.net/u014641168");
Cell row_cell4 = row.createCell(3);
row_cell4.setCellValue("Tongyao");
}
//合并单元格(你的sheet页,你的标题所占用的行,你的哪一列单元格需要合并)
mergeCell(sheet,1,0);
mergeCell(sheet,1,3);
try {
OutputStream output = response.getOutputStream();
response.reset();
String fileName = "导出合并后的表单(码云:gitee.com/Super_TongYao).xls";
// 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 合并单元格
* @author tongyao
* @param sheet sheet页
* @param titleColumn 标题占用行
* @param cellIndex 那个单元格
*/
public static void mergeCell(HSSFSheet sheet,int titleColumn,int cellIndex){
//多少行
int rowCount = sheet.getPhysicalNumberOfRows();
String cellText = "";
//开始下标
int startIndex = 0;
//结束下标
int endIndex = 0;
HSSFRow row = null;
Cell cell = null;
for(int i = titleColumn;i<rowCount;i++){
row = sheet.getRow(i);
cell = row.getCell(cellIndex);
if(!cell.getStringCellValue().equals(cellText)){
if(startIndex != 0){
endIndex = (i-1);
sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
}
cellText = cell.getStringCellValue();
startIndex = i;
}
}
endIndex = (rowCount-1);
sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
}
转载地址: springboot 使用Poi 自定义封装方法 合并excel中的单元格_Tongyao-CSDN博客_springboot合并单元格