动态创建excel文件，动态合并单元格并提供下载 java

1.动态生成excel的原因

文章图片

**
出现的效果： **

文章图片

员工姓名没有自动合并单元格，在http://jxls.sourceforge.net/reference/xls_area.html 上面找了半天没有相关的案例，于是就自己代码生成
【动态创建excel文件，动态合并单元格并提供下载】2.动态生成excel文件，并动态合并单元格

package com.baptechs.zeus.domain.base.xls; import com.baptechs.zeus.domain.headquarters.customerapp.posprepay.NozzleAuthExport; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import java.io.FileNotFoundException; import java.io.OutputStream; import java.util.List; /** * Created by Amence on 2017/9/14. * * 该类只针对动态创建员工考核报表 */ public class AttendanceXLSXUtil {public static void createXLSX(List nozzleAuthExports, String startTime, String endTime, OutputStream os) throws FileNotFoundException { //新建excel报表 HSSFWorkbook excel = new HSSFWorkbook(); //添加一个sheet，名字叫"加油工考核表" HSSFSheet hssfSheet = excel.createSheet("加油工考核表"); //excel格式 HSSFCellStyle style = excel.createCellStyle(); //字体样式 HSSFFont font1 = excel.createFont(); CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 2, 1, 10); //在sheet里增加合并单元格 hssfSheet.addMergedRegion(cellRangeAddress1); //生成第一行 Row row = hssfSheet.createRow(0); Cell cell = row.createCell(1); cell.setCellValue("加油站员工考核表"); _setBorder(style, font1, 26, true, HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(style); //在sheet里增加合并单元格时间 CellRangeAddress cellRangeAddress2 = new CellRangeAddress(4, 4, 1, 2); //第四行到第四行第一列到第二列 hssfSheet.addMergedRegion(cellRangeAddress2); //在sheet里增加合并单元格时间(获取) CellRangeAddress cellRangeAddress3 = new CellRangeAddress(4, 4, 3, 4); hssfSheet.addMergedRegion(cellRangeAddress3); Row row1 = hssfSheet.createRow(4); row1.setHeightInPoints(20); Cell cell1 = row1.createCell(1); cell1.setCellValue("开始时间："); HSSFFont font = excel.createFont(); HSSFCellStyle style1 = excel.createCellStyle(); _setBorder(style1, font, 11, false, HSSFCellStyle.ALIGN_RIGHT); cell1.setCellStyle(style1); Cell cell2 = row1.createCell(3); cell2.setCellValue(startTime); HSSFCellStyle style2 = excel.createCellStyle(); _setBorder(style2, font, 11, false, HSSFCellStyle.ALIGN_LEFT); cell2.setCellStyle(style2); //在sheet里增加合并单元格结束时间 CellRangeAddress cellRangeAddress4 = new CellRangeAddress(4, 4, 7, 8); hssfSheet.addMergedRegion(cellRangeAddress4); //在sheet里增加合并单元格时间(获取) CellRangeAddress cellRangeAddress5 = new CellRangeAddress(4, 4, 9, 10); hssfSheet.addMergedRegion(cellRangeAddress5); Cell cell3 = row1.createCell(7); cell3.setCellValue("结束时间："); HSSFCellStyle style3 = excel.createCellStyle(); _setBorder(style3, font, 11, false, HSSFCellStyle.ALIGN_RIGHT); cell3.setCellStyle(style3); Cell cell4 = row1.createCell(9); cell4.setCellValue(endTime); HSSFCellStyle style4 = excel.createCellStyle(); _setBorder(style4, font, 11, false, HSSFCellStyle.ALIGN_LEFT); cell4.setCellStyle(style4); String[] title = {"员工姓名", "油品", "加油次数", "加油升数", "销售额"}; Row row2 = hssfSheet.createRow(5); row2.setHeightInPoints(40); //设置行高 for (int i = 0; i < title.length; i++) { CellRangeAddress cellRangeAddress6 = new CellRangeAddress(5, 5, 2 * i + 1, 2 * (i + 1)); hssfSheet.addMergedRegion(cellRangeAddress6); //表头第6行 //从第二列开始 for (int y = 2 * i + 1; y < 2 * (i + 1) + 1; y++) { Cell cell5 = row2.createCell(y); cell5.setCellValue(title[i]); HSSFCellStyle style5 = excel.createCellStyle(); HSSFFont font2 = excel.createFont(); font2.setBold(true); setBorder(style5, font2, 18); cell5.setCellStyle(style5); } }int weight = 0; //上一行的高度 //填充XMSL数据 for (int i = 0; i < nozzleAuthExports.size(); i++) { // 员工数int high = nozzleAuthExports.get(i).getInfos().size(); //获取新增高度 if (i != 0) { weight += nozzleAuthExports.get(i - 1).getInfos().size() + 1; //从第二行算起 } int rowHigh = 0; //姓名初始单元格初始化高度 Row row3; Cell cell6; for (int y = 0; y < high + 1; y++) {//油品数 + 小计rowHigh = 6 + y + weight; //动态定位行row3 = hssfSheet.createRow(rowHigh); //创建行for (int x = 1; x < 3; x++) {//必须要每个cell都要创建 //创建姓名 cell6 = row3.createCell(x); cell6.setCellValue(nozzleAuthExports.get(i).getUserName()); HSSFCellStyle style6 = excel.createCellStyle(); HSSFFont font3 = excel.createFont(); setBorder(style6, font3, 11); cell6.setCellStyle(style6); }row3.setHeightInPoints(20); //设置行高 for (int z = 0; z < 4; z++) { //填充数据 //创建单元格CellRangeAddress cellRangeAddress6 = new CellRangeAddress(rowHigh, rowHigh, 2 * z + 3, 2 * z + 4); //合并单元格，行-行。列-列hssfSheet.addMergedRegion(cellRangeAddress6); for (int m = 2 * z + 3; m < 2 * z + 5; m++) { Cell cell5 = row3.createCell(m); //定义列HSSFCellStyle style5 = excel.createCellStyle(); HSSFFont font2 = excel.createFont(); if (y == nozzleAuthExports.get(i).getInfos().size()) { //小计 switch (z) { case 0: cell5.setCellValue("小计"); break; case 1: cell5.setCellValue(nozzleAuthExports.get(i).getAllOilCount()); break; case 2: cell5.setCellValue(nozzleAuthExports.get(i).getAllVolume().toString()); break; case 3: cell5.setCellValue(nozzleAuthExports.get(i).getAllSale().toString()); break; } font2.setBold(true); } else { switch (z) { case 0: cell5.setCellValue(nozzleAuthExports.get(i).getInfos().get(y).getFlueName()); break; case 1: cell5.setCellValue(nozzleAuthExports.get(i).getInfos().get(y).getOilCount()); break; case 2: cell5.setCellValue(nozzleAuthExports.get(i).getInfos().get(y).getVolume().toString()); break; case 3: cell5.setCellValue(nozzleAuthExports.get(i).getInfos().get(y).getSales().toString()); break; } font2.setBold(false); } setBorder(style5, font2, 11); cell5.setCellStyle(style5); }}}//合并姓名单元格 CellRangeAddress cellRangeAddress7 = new CellRangeAddress(6 + weight, rowHigh, 1, 2); hssfSheet.addMergedRegion(cellRangeAddress7); }try { excel.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); }}public static void setBorder(HSSFCellStyle style, HSSFFont font, int fontSize) { font.setFontHeightInPoints((short) fontSize); style.setFont(font); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框}public static void _setBorder(HSSFCellStyle style, HSSFFont font, int fontSize, boolean bold, short alignment) {font.setFontHeightInPoints((short) fontSize); font.setBold(bold); style.setFont(font); style.setAlignment(alignment); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直} }

直接上代码。效果图：

文章图片

主要的代码就是

CellRangeAddress cellRangeAddress7 = new CellRangeAddress(6 + weight, rowHigh, 1, 2); hssfSheet.addMergedRegion(cellRangeAddress7);

其余的代码都是按需求生成表格。对于合并单元格后的边框问题。需要把合并的单元个全部创建出来。我没有找到更好的方法，其他人的说的不清不楚。

for (int x = 1; x < 3; x++) {//必须要每个cell都要创建 //创建姓名 cell6 = row3.createCell(x); cell6.setCellValue(nozzleAuthExports.get(i).getUserName()); HSSFCellStyle style6 = excel.createCellStyle(); HSSFFont font3 = excel.createFont(); setBorder(style6, font3, 11); cell6.setCellStyle(style6); }

3.提供下载功能
javaWeb的下载直接上代码

HttpServletResponse response = getHttpServletResponse(); String startTime = nozzleAuthInfo.getStartTime() == null ? "不限" : DateUtil.formatDateToString(nozzleAuthInfo.getStartTime()); String endTime = nozzleAuthInfo.getEndTime() == null ? "不限" : DateUtil.formatDateToString(nozzleAuthInfo.getEndTime()); try { response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString()); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String("员工考核".getBytes("gbk"), "iso-8859-1") + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx"); response.setContentType("application/octet-stream"); OutputStream os= new BufferedOutputStream(response.getOutputStream()); AttendanceXLSXUtil.createXLSX(nozzleAuthExports, startTime, endTime,os); } catch (IOException e) { e.printStackTrace(); }