excel导出工具类（包含合并单元格）-CSDN博客

本文链接：https://blog.csdn.net/qq_34467638/article/details/62889234

package com.hime.util;

import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.criteria.CriteriaBuilder.In;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
//import org.base.util.CellUtil;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.hime.jpa.report.model.TreportHsl;
import com.ibm.db2.jcc.am.j;

/**
* 操作excel的功能类（包括合并单元）
* @author
*
*/
public class ExcelUtil extends AbstractExcelView{

private HSSFWorkbook workbook;
private String fileName ;

/**
*
* @param request
* @param response
* @param dataList 导出数据
* @param sheetName 报表名称
* @param tableTop 表头名称
* @param head3 上报单位、单位
* @param headnum3 上报单位、单位的合并参数（起始行，结束行，起始列，结束列）
* @param head4 报表列名（如："序号" , "业务经办部门或支行","客户名称","业务品种"）
* @param headnum4 列名合并参数（起始行，结束行，起始列，结束列）
* @param head5 报表列名（没有的为空，如："","","","贷款业务","票据贴现业务","进出口押汇业务"）
* @param detail 列名对应的参数字段
* @param date 日期
* @param colWidth 列宽（map(第几列，宽度)）
* @param forHigh 行高(map("title",""),map("date",""),map("sbdw",""),map("headerColumn",""),map("commonRow",""))
* @return
* @throws Exception
*/
public HSSFWorkbook reportMergeXls(
HttpServletRequest request,
HttpServletResponse response,
List<Map<String, Object>> dataList,
String sheetName,String tableTop,
String[] head3, String[] headnum3,String[] head4, String[] headnum4,
String[] head5, String[] detail,String date,List<Map<Integer, Integer>> colWidth,List<Map<String, Integer>> forHigh)
throws Exception {
fileName = sheetName;
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);// 创建一个表
// 表头标题样式
HSSFFont headfont = workbook.createFont();
headfont.setFontName("宋体");
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
headfont.setFontHeightInPoints((short) 24);// 字体大小
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
// 表头时间样式
HSSFFont datefont = workbook.createFont();
datefont.setFontName("宋体");
datefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
datefont.setFontHeightInPoints((short)16);// 字体大小
HSSFCellStyle datestyle = workbook.createCellStyle();
datestyle.setFont(datefont);
datestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
datestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
datestyle.setLocked(true);
// 列名样式
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 12);// 字体大小
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
style.setLocked(true);
//上报单位样式
HSSFFont sbdwfont = workbook.createFont();
sbdwfont.setFontName("宋体");
sbdwfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
sbdwfont.setFontHeightInPoints((short)14);// 字体大小
HSSFCellStyle style1_left = workbook.createCellStyle();
style1_left.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style1_left.setFont(sbdwfont);
//单位样式
HSSFFont dwfont = workbook.createFont();
dwfont.setFontName("宋体");
dwfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
dwfont.setFontHeightInPoints((short)14);// 字体大小
HSSFCellStyle style1_right = workbook.createCellStyle();
style1_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style1_right.setFont(dwfont);
//单元格标题样式
HSSFFont font2 = workbook.createFont();
font2.setFontName("宋体");
font2.setFontHeightInPoints((short) 12);
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setFont(font2);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style2.setWrapText(true); // 换行
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 普通单元格样式
HSSFFont font3 = workbook.createFont();
font3.setFontName("宋体");
font3.setFontHeightInPoints((short) 11);
HSSFCellStyle style3= workbook.createCellStyle();
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style3.setFont(font3);
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style3.setWrapText(true); // 换行
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 设置列宽（第几列，宽度）
for (int i = 0; i < colWidth.get(0).size(); i++) {
sheet.setColumnWidth(i, colWidth.get(0).get(i));
}
// sheet.setDefaultRowHeight((short)360);//设置行高
// 第一行表头标题
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head4.length-1));
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(forHigh.get(0).get("title"));
HSSFCell cell = row.createCell(0);
cell.setCellStyle(headstyle);
cell.setCellValue(tableTop);
// 第二行时间
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, head4.length-1));
HSSFRow row1 = sheet.createRow(1);
row1.setHeightInPoints(forHigh.get(0).get("date"));
HSSFCell cell1 = row1.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue(date);
// 第3行上报单位、单位
HSSFRow row2 = sheet.createRow(2);
row2.setHeightInPoints(forHigh.get(0).get("sbdw"));
HSSFCell cell2 = null;
for (int i = 0; i < head3.length; i++) {
cell2 = row2.createCell(i);
for (int j = 0; j < headnum3.length; j++) {
String[] temp = headnum3[j].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
}
if(i<=22){
cell2.setCellStyle(style1_left);
cell2.setCellValue(head3[i]);
}else{
cell2.setCellStyle(style1_right);
cell2.setCellValue(head3[i]);
}
}


// 第4行表头列名
HSSFRow row3 = sheet.createRow(3);
row3.setHeightInPoints(forHigh.get(0).get("headerColumn"));
for (int i = 0; i < head4.length; i++) {
HSSFCell cell3 = row3.createCell(i);
cell3.setCellValue(head4[i]);
cell3.setCellStyle(style2);
}

//设置第5行
HSSFRow row4 = sheet.createRow(4); //因为下标从0开始，所以这里表示的是excel中的第四行
row4.setHeightInPoints(forHigh.get(0).get("headerColumn"));
for (int i = 0; i < head4.length; i++) {
HSSFCell cell4 = row4.createCell(i);
cell4.setCellStyle(style2); //设置excel中第四行的1、2、3等合并行的列的边框
cell4.setCellValue(head5[i]);
}

//动态合并单元格
for (int i = 0; i < headnum4.length; i++) {
String[] temp = headnum4[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
}

// 设置列值-内容
double num = 1;
for (int i = 0; i < dataList.size(); i++) {
HSSFRow row5 = sheet.createRow(i + 5);
row5.setHeightInPoints(forHigh.get(0).get("commonRow"));
HSSFCell cell5_1 = null;
cell5_1 = row5.createCell(0);
cell5_1.setCellValue(num);
cell5_1.setCellStyle(style3);
for (int j = 1; j < detail.length; j++) {
Map<String, Object> tempmap = (HashMap<String, Object>) dataList.get(i);
cell5_1 = row5.createCell(j);
if (tempmap.get(detail[j]) instanceof Double) {
double data = (double) tempmap.get(detail[j]);
cell5_1 = row5.createCell(j);
cell5_1.setCellValue(data);
}
if (tempmap.get(detail[j]) instanceof String) {
String data1 = (String) tempmap.get(detail[j]);
cell5_1 = row5.createCell(j);
cell5_1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell5_1.setCellValue(data1);
}
cell5_1.setCellStyle(style3);
}
num ++;
}
return workbook;
}

@Override
protected void buildExcelDocument(Map<String, Object> arg0, HSSFWorkbook wb, HttpServletRequest request,
HttpServletResponse response)
throws Exception {
wb = workbook;
String filename = fileName+".xls";
filename = PropertiesUtil.encodeFilename(filename, request);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();

}
}