java动态导出Excel功能工具类，含二级表头合并以及列合并
java动态导出Excel功能工具类，含二级表头合并以及列合并
传参请仔细阅读开头注释，其中列合并功能其实还可以继续完善，目前只支持全列合并
代码如下：
import com.common.util.common.StringUtil;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @Author: gongPing* ExportData迭代更新版本，增加了添加二级表头功能*传参1 String[][] headers {{一级表头,key},{一级表头,key},{一级表头,二级表头1,二级表头2...}}这里二级表头的key就是它的名称*传参2 title 标题 为空默认sheet*传参3 String[] mergeLine 要合并的列，这里是将指定的列全部合并** 参考方法：/centerMeetingDayReport/excel* @Date: 2020/11/15 10:26*/
public class ExportDataNew {private XSSFCellStyle headStyle;private SXSSFWorkbook workbook;private SXSSFSheet sheet;/*** 创建一行*/private SXSSFRow row = null;private SXSSFCell cell = null;private int currentRow = 0;private XSSFCellStyle stringStyle;private String[][] headers;private String sheetName;private String sheetTitle;private Integer[] mergeLines;/**** @param headers 表头列* @param title 标题* @param mergeLine 要合并单元格的列*/public ExportDataNew(String[][] headers,String title,Integer[] mergeLine) throws FileNotFoundException {this.headers = headers;if(StringUtil.isBlank(title)){this.sheetTitle ="sheet";}else{this.sheetTitle = title;}this.sheetName = "sheet1";this.mergeLines = mergeLine;try {workbook = new SXSSFWorkbook(1000);this.headStyle = (XSSFCellStyle) this.workbook.createCellStyle();headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);headStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);XSSFFont headFont = (XSSFFont) workbook.createFont();// 设置头部字体为宋体headFont.setFontName("宋体");// 粗体headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);headFont.setFontHeightInPoints((short) 11);// 单元格样式使用字体this.headStyle.setFont(headFont);createSheet(sheetName, headers,sheetTitle);} catch (Exception exc) {exc.printStackTrace();}}/*** 创建表头** @param sheetName* @param headers*/private void createSheet(String sheetName, String[][] headers,String sheetTitle) {sheet = (SXSSFSheet) workbook.createSheet(sheetName);//创建第一行表头row = (SXSSFRow) sheet.createRow(currentRow);int realLen = 0;int startLen = 0;cell = (SXSSFCell) row.createCell(realLen);cell.setCellType(XSSFCell.CELL_TYPE_STRING);headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());cell.setCellStyle(headStyle);//设置样式cell.setCellValue(sheetTitle);//设置标题int titleLen = getTitleLen(headers);sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleLen-1)); // 合并单元格currentRow++;row = (SXSSFRow) sheet.createRow(currentRow);for (int i = 0; i < headers.length; i++) {String[] headerT = headers[i];if (headerT.length > 2) {//一级表头Integer len = headerT.length;for (int j = 0; j < len - 1; j++) {cell = (SXSSFCell) row.createCell(realLen);realLen++;cell.setCellType(XSSFCell.CELL_TYPE_STRING);cell.setCellStyle(headStyle);cell.setCellValue(headers[i][0]);}sheet.addMergedRegion(new CellRangeAddress(1, 1, startLen, realLen - 1)); // 合并单元格startLen = realLen;} else {cell = (SXSSFCell) row.createCell(i);cell.setCellType(XSSFCell.CELL_TYPE_STRING);cell.setCellValue(headers[i][0]);cell.setCellStyle(headStyle);realLen++;startLen++;}}currentRow++;//创建第二行表头realLen = 0;row = (SXSSFRow) sheet.createRow(currentRow);for (int i = 0; i < headers.length; i++) {String[] headerT = headers[i];if (headerT.length > 2) {//二级表头Integer len = headerT.length;for (int j = 1; j < len; j++) {String title = headerT[j];cell = (SXSSFCell) row.createCell(realLen);realLen++;cell.setCellType(XSSFCell.CELL_TYPE_STRING);cell.setCellStyle(headStyle);cell.setCellValue(title);}} else {cell = (SXSSFCell) row.createCell(i);cell.setCellType(XSSFCell.CELL_TYPE_STRING);cell.setCellStyle(headStyle);cell.setCellValue("");//合并1-2行sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));realLen++;}}currentRow++;}/*** 获取表格宽度** @param headers* @return*/private int getTitleLen(String[][] headers) {int realLen = 0;for (int i = 0; i < headers.length; i++) {String[] headerT = headers[i];if (headerT.length > 2) {//一级表头Integer len = headerT.length;for (int j = 0; j < len - 1; j++) {realLen++;}} else {realLen++;}}return realLen;}/*** 导出excel** @param listRows* @throws ParseException*/private void poiWriteExcelTo2007(List> listRows, OutputStream out)throws ParseException {for (int i = 0; i < listRows.size(); i++) {row = (SXSSFRow) sheet.createRow(currentRow);Map listCells = listRows.get(i);int realLen=0;for (int j = 0; j < this.headers.length; j++) {String[] headerT=this.headers[j];if(headerT.length>2){for(int k=1;k> listRows, OutputStream out)throws IOException, ParseException {ExportDataNew exportData = new ExportDataNew(this.headers,this.sheetTitle,this.mergeLines);exportData.poiWriteExcelTo2007(listRows, out);}/*** 导出** @throws IOException* @throws ParseException*/public void exportData(String fileName, List> listRows, HttpServletResponse response)throws IOException, ParseException {response.setCharacterEncoding("UTF-8");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;Filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));exportData(listRows, response.getOutputStream());}public static void main(String[] args) throws IOException, ParseException {//定义一个对象数组！String[][] headers=new String[15][];headers[0]= new String[]{"日期", "DATE"};headers[1]= new String[]{"星期","DATE_STR"};headers[2]= new String[]{"时间","TIME"};headers[3]= new String[]{"会议室","NAME"};headers[4]= new String[]{"订会部门","RESERVE_DEPT_NAME"};headers[5]= new String[]{"订会人","MEETING_RESERVATION"};headers[6]= new String[]{"订会人电话","MOBILE"};headers[7]= new String[]{"会议名称","THEME"};headers[8]= new String[]{"参会领导","LEADERS"};headers[9]= new String[]{"会议等级","LEVELS"};headers[10]= new String[]{"会议人数","NUM"};headers[11]= new String[]{"预定是否符合流程","IS_TRUE"};//会议设备String[] eqment=new String[6];eqment[0]="会议设备";//会议物资String[] materials=new String[6];materials[0]="会议物资";//会议服务String[] server=new String[6];server[0]="会议服务";for(int i =0;i<5;i++){eqment[i+1]="测试服务A"+i+1;materials[i+1]="测试服务B"+i+1;server[i+1]="测试服务C"+i+1;}headers[12]= eqment;headers[13]= materials;headers[14]= server;//数据集合List> listMap =new ArrayList>();for (int i=0;i<5;i++){Map map= new HashMap();map.put("TIME","vo.getTime()");map.put("DATE","vo.getDate()");map.put("DATE_STR","vo.getDateStr()");map.put("IS_TRUE","vo.getIsTrue()");map.put("LEADERS","vo.getLeaders()");map.put("LEVELS","vo.getLevels()");map.put("MEETING_RESERVATION","vo.getMeetingReservation()");map.put("MOBILE","vo.getMobile()");map.put("THEME","vo.getTheme()");map.put("NUM","vo.getNum().toString()");map.put("RESERVE_DEPT_NAME","vo.getReserveDeptName()");map.put("NAME","vo.getName()");//二级表头数据赋值for(int j=0;j main运行结果：
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！