poi带表头多sheet导出

导出工具类

package com.hieasy.comm.core.excel;import com.hieasy.comm.core.excel.fragment.ExcelFragment;
import com.hieasy.comm.core.utils.mine.MineDateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;import java.lang.reflect.Field;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;public class CsExcelUtils<T> {HSSFRow row=null;//行HSSFCell cell=null;//单元格CellStyle style =null;//单元格样式Class<T> clazz;public CsExcelUtils(Class<T> clazz) {super();this.clazz = clazz;}/*同表头多sheet导出*/public  void   myExcelExportClassList(String fileName, String sheetName, String[][] excelHeader, Class clazz, List<T> list, HSSFWorkbook workbook) throws Exception{HSSFSheet sheet = workbook.createSheet(sheetName); // 为excel创建一个sheet页Integer rowIndex=0; //行号List<Field> fields = ExcelFragment.fltMappedFileds(ExcelFragment.getMappedFiled(clazz, null),1);Integer maxcols=fields.size()>excelHeader[0].length?fields.size():excelHeader[0].length; //列号//导出Excel时间：2018-12-27HSSFRow row = sheet.createRow(rowIndex++);sheet.addMergedRegion(new CellRangeAddress(0,0,0,maxcols-1));HSSFCell cell = row.createCell(0);cell.setCellValue("导出Excel时间："+ MineDateUtil.getNowDateTime());CellStyle cellStyle=getTitle1CellStyler(workbook);cell.setCellStyle(cellStyle); // 为B2单元格添加样式//大标题row = sheet.createRow(rowIndex++);row.setHeight((short) (25*20));sheet.addMergedRegion(new CellRangeAddress(1,1,0,maxcols-1));cell = row.createCell(0);cell.setCellValue(fileName);cellStyle=getTitle2CellStyler(workbook);cell.setCellStyle(cellStyle);//Excel 头部占位Integer eHBeginRowIndex=rowIndex; //记录Excel头开始行rowIndex+=excelHeader.length;     //留空Excel头//Table 表头数据Integer chRowIndex=    writeExcelHeader(maxcols,eHBeginRowIndex,excelHeader, sheet, workbook);//Table BODY的数据writeTableBodyOrFooterFromTList(list, chRowIndex,fields,sheet,workbook);}private Integer writeTableBodyOrFooterFromTList(List<T> list, Integer rowIndex,List<java.lang.reflect.Field> fields, HSSFSheet sheet, HSSFWorkbook workbook) throws NoSuchFieldException, SecurityException {//正式的数据row=sheet.createRow(rowIndex);autoColumnWidthForChineseChar(sheet,0,fields.size());for (int i = 0; i < fields.size(); i++) {java.lang.reflect.Field field = fields.get(i);ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);int col = getExcelCol(attr.column());// 获得列号cell = row.createCell(col);// 创建列cell.setCellType(Cell.CELL_TYPE_STRING);// 设置列中写入内容为String类型cell.setCellValue(attr.name());style = getHeadCellStyler(workbook);// 如果设置了提示信息则鼠标放上去提示.if (!attr.prompt().trim().equals("")) {setSXSSFPrompt(sheet, "", attr.prompt(), 3, 100, col, col);// 这里默认设了2-101列提示.}// 如果设置了combo属性则本列只能选择不能输入if (attr.combo().length > 0) {setSXSSFValidation(sheet, attr.combo(), 3, 100, col, col);// 这里默认设了2-101列只能选择不能输入.}cell.setCellStyle(style);}rowIndex++;//4.设置具体内容int startNo = 0;int z = rowIndex;int endNo = list.size();// 写入各条记录,每条记录对应excel表中的一行for (int i = startNo; i < endNo; i++) {row =sheet.createRow(i + z);T vo = (T) list.get(i); // 得到导出对象.for (int j = 0; j < fields.size(); j++) {java.lang.reflect.Field field = fields.get(j);// 获得field.field.setAccessible(true);// 设置实体类私有属性可访问ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);try {// 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.if (attr.isExport()) {cell = row.createCell(getExcelCol(attr.column()));// 创建cell// 取得类型,并根据对象类型设置值.Class<?> fieldType = field.getType();if (String.class == fieldType) {cell.setCellType(Cell.CELL_TYPE_STRING);cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(field.get(vo) == null ? 0 : Integer.parseInt(field.get(vo).toString()));} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(field.get(vo) == null ? 0 : Long.parseLong(field.get(vo).toString()));} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(field.get(vo) == null ? 0 : Double.parseDouble(field.get(vo).toString()));} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(field.get(vo) == null ? 0 : Double.parseDouble(field.get(vo).toString()));} else if (Character.TYPE == fieldType) {}}} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}rowIndex++;}return rowIndex;}private static Integer writeExcelHeader(int maxcols, Integer eHBeginRowIndex, String[][] excelHeader, Sheet sheet, HSSFWorkbook workbook) {for (String[] hrows : excelHeader) {/*CellRangeAddress region =new CellRangeAddress(eHBeginRowIndex,eHBeginRowIndex,0,maxcols-1);sheet.addMergedRegion(region);*/Row row = sheet.createRow(eHBeginRowIndex);row.setHeight((short) (25 * 20));int cellIndex = 0;for (int i = 0; i < maxcols; i++) {Cell cell = row.createCell(i);cell.setCellStyle(getExcelHeadCellStyler(workbook));if ((!sheet.isColumnHidden(i)) && cellIndex < hrows.length) {cell.setCellValue(hrows[cellIndex++]);}}autoColumnWidthForChineseChar(sheet,0,maxcols);eHBeginRowIndex++;}return eHBeginRowIndex;}//设置样式(导出时间)private static CellStyle getTitle1CellStyler(HSSFWorkbook workbook ){CellStyle style = workbook.createCellStyle();// 设置样式style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(XSSFCellStyle.NO_FILL);style.setBorderLeft(XSSFCellStyle.NO_FILL);style.setBorderRight(XSSFCellStyle.NO_FILL);style.setBorderTop(XSSFCellStyle.NO_FILL);style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 生成表头字体Font font = workbook.createFont();font.setColor(IndexedColors.BLACK.getIndex());font.setFontHeightInPoints((short) 10);//font.setFontName("微软雅黑");// 把字体应用到当前的样式style.setFont(font);return style;}//设置样式(导出标题)private static CellStyle getTitle2CellStyler(HSSFWorkbook workbook ){CellStyle style = workbook.createCellStyle();// 设置样式style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(XSSFCellStyle.NO_FILL);style.setBorderLeft(XSSFCellStyle.NO_FILL);style.setBorderRight(XSSFCellStyle.NO_FILL);style.setBorderTop(XSSFCellStyle.NO_FILL);style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 生成表头字体Font font = workbook.createFont();font.setColor(IndexedColors.BLACK.getIndex());font.setFontHeightInPoints((short) 20);font.setFontName("微软雅黑");font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//font.setFontName("微软雅黑");// 把字体应用到当前的样式style.setFont(font);return style;}//设置样式(EXCEL表头)public static CellStyle getExcelHeadCellStyler(HSSFWorkbook workbook ){CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(XSSFCellStyle.NO_FILL);style.setBorderLeft(XSSFCellStyle.NO_FILL);style.setBorderRight(XSSFCellStyle.NO_FILL);style.setBorderTop(XSSFCellStyle.NO_FILL);// 生成表头字体Font font = workbook.createFont();font.setColor(IndexedColors.DARK_TEAL.getIndex());font.setFontHeightInPoints((short) 11);font.setFontName("微软雅黑");font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);style.setFont(font);return style;}//设置样式(表头)public static CellStyle getHeadCellStyler(HSSFWorkbook workbook ){CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(XSSFCellStyle.BORDER_THIN);style.setBorderLeft(XSSFCellStyle.BORDER_THIN);style.setBorderRight(XSSFCellStyle.BORDER_THIN);style.setBorderTop(XSSFCellStyle.BORDER_THIN);style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 生成表头字体Font font = workbook.createFont();font.setColor(IndexedColors.DARK_BLUE.getIndex());font.setFontHeightInPoints((short) 10);font.setFontName("微软雅黑");font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);style.setFont(font);return style;}//将EXCEL中A,B,C,D,E列映射成0,1,2,3private static int getExcelCol(String col) {col = col.toUpperCase();// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。int count = -1;char[] cs = col.toCharArray();for (int i = 0; i < cs.length; i++) {count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);}return count;}// 设置单元格上提示private Sheet setSXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) {// 构造constraint对象DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");// 四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);// 数据有效性对象DataValidation data_validation_view = new HSSFDataValidation(regions, constraint);data_validation_view.createPromptBox(promptTitle, promptContent);sheet.addValidationData(data_validation_view);return sheet;}//设置某些列的值只能输入预制的数据,显示下拉框.private Sheet setSXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {// 加载下拉列表内容DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);// 数据有效性对象DataValidation data_validation_list = new HSSFDataValidation(regions, constraint);sheet.addValidationData(data_validation_list);return sheet;}/*** 自动调整列表宽度适应中文字符串* @param sheet* @param startColumnNum 要调整的起始列表号* @param size  要调整的列表数量*/private static void autoColumnWidthForChineseChar(Sheet sheet, int startColumnNum, int size) {for (int columnNum = 0; columnNum < size; columnNum++) {/** 调整每一列宽度 */sheet.autoSizeColumn(columnNum);/** 获取列宽 */final int columnWidth = sheet.getColumnWidth(columnNum);if(columnNum >= 256*256 ){/** 列宽已经超过最大列宽则放弃当前列遍历 */continue;}/** 新的列宽 */int newWidth = columnWidth;/** 遍历所有的行,查找有汉字的列计算新的最大列宽 */for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {Row currentRow;if (sheet.getRow(rowNum) == null) {continue;} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(columnNum) != null) {Cell currentCell = currentRow.getCell(columnNum);if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {String value = currentCell.getStringCellValue();/** 计算字符串中中文字符的数量 */int count = chineseCharCountOf(value);/**在该列字符长度的基础上加上汉字个数计算列宽 */int length = value.length()*256+count*256*2;/** 使用字符串的字节长度计算列宽 */
//	                    int length = value.getBytes().length*256;if (newWidth < length && length < 256*256) {newWidth = length;}}}}if(newWidth != columnWidth){//设置列宽sheet.setColumnWidth(columnNum, newWidth);}}}/*** 计算字符串中中文字符的数量* 参见 <a hrft="https://www.cnblogs.com/straybirds/p/6392306.html">《汉字unicode编码范围》</a>* @param input* @return*/private static int chineseCharCountOf(String input){int count = 0;//汉字数量if(null != input){String regEx = "[\\u4e00-\\u9fa5]";Pattern p = Pattern.compile(regEx);Matcher m = p.matcher(input);int len = m.groupCount();//获取汉字个数while (m.find()) {for (int i = 0; i <= len; i++) {count = count + 1;}}}return count;}
}

导出实体主表类

public class Daochuzb {private String zbzd1;private String zbzd2;private String zbzd3;private String zbzd4;private String zbzd5;public String getZbzd1() {return zbzd1;}public void setZbzd1(String zbzd1) {this.zbzd1 = zbzd1;}public String getZbzd2() {return zbzd2;}public void setZbzd2(String zbzd2) {this.zbzd2 = zbzd2;}public String getZbzd3() {return zbzd3;}public void setZbzd3(String zbzd3) {this.zbzd3 = zbzd3;}public String getZbzd4() {return zbzd4;}public void setZbzd4(String zbzd4) {this.zbzd4 = zbzd4;}public String getZbzd5() {return zbzd5;}public void setZbzd5(String zbzd5) {this.zbzd5 = zbzd5;}
}

导出sheet1表

public class Daochusheet1 {@ExcelVOAttribute(name = "字段1",column="A")private String zd1;@ExcelVOAttribute(name = "字段2",column="B")private String zd2;@ExcelVOAttribute(name = "字段3",column="C")private String zd3;@ExcelVOAttribute(name = "字段4",column="D")private String zd4;public Daochusheet1() {}public Daochusheet1(String zd1, String zd2, String zd3, String zd4) {this.zd1 = zd1;this.zd2 = zd2;this.zd3 = zd3;this.zd4 = zd4;}public String getZd1() {return zd1;}public void setZd1(String zd1) {this.zd1 = zd1;}public String getZd2() {return zd2;}public void setZd2(String zd2) {this.zd2 = zd2;}public String getZd3() {return zd3;}public void setZd3(String zd3) {this.zd3 = zd3;}public String getZd4() {return zd4;}public void setZd4(String zd4) {this.zd4 = zd4;}
}

导出sheet2表

public class Daochusheet2 {@ExcelVOAttribute(name = "字段1",column="A")private String zd1;@ExcelVOAttribute(name = "字段2",column="B")private String zd2;@ExcelVOAttribute(name = "字段3",column="C")private String zd3;@ExcelVOAttribute(name = "字段4",column="D")private String zd4;@ExcelVOAttribute(name = "字段4",column="E")private String zd5;public Daochusheet2() {}public Daochusheet2(String zd1, String zd2, String zd3, String zd4, String zd5) {this.zd1 = zd1;this.zd2 = zd2;this.zd3 = zd3;this.zd4 = zd4;this.zd5 = zd5;}public String getZd1() {return zd1;}public void setZd1(String zd1) {this.zd1 = zd1;}public String getZd2() {return zd2;}public void setZd2(String zd2) {this.zd2 = zd2;}public String getZd3() {return zd3;}public void setZd3(String zd3) {this.zd3 = zd3;}public String getZd4() {return zd4;}public void setZd4(String zd4) {this.zd4 = zd4;}public String getZd5() {return zd5;}public void setZd5(String zd5) {this.zd5 = zd5;}
}

导出

@NoRepeatSubmit@PostMapping("/exportcs")@ApiOperation(value = "导出测试", notes = "")public void exportcs(HttpServletResponse response) throws Exception {response.setContentType("application/octet-stream");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(" 导出测试.xls","UTF-8"));HSSFWorkbook workbook = new HSSFWorkbook();  // 创建一个excelString[][] excelHeader=new String[2][3];excelHeader[0][0]="主表字段1";excelHeader[0][1]="主表字段2";excelHeader[0][2]="主表字段3";excelHeader[1][0]="主表字段4";excelHeader[1][1]="主表字段5";excelHeader[1][2]="";List<Daochusheet1> list1=new ArrayList<>();Daochusheet1 daochusheet1=new Daochusheet1("sheet11","sheet12","sheet13","sheet14");list1.add(daochusheet1);List<Daochusheet2> list2=new ArrayList<>();Daochusheet2 daochusheet2=new Daochusheet2("sheet21","sheet22","sheet23","sheet24","sheet25");list2.add(daochusheet2);CsExcelUtils<Daochusheet1> myExcelUtils=new CsExcelUtils(Daochusheet1.class);myExcelUtils.myExcelExportClassList("导出sheet1","导出sheet1",excelHeader,Daochusheet1.class,list1,workbook);CsExcelUtils<Daochusheet2> myExcelUtils1=new CsExcelUtils(Daochusheet2.class);myExcelUtils1.myExcelExportClassList("导出sheet2","导出sheet2",excelHeader,Daochusheet2.class, list2,workbook);FileOutputStream fos =null;//输出流try {fos = new FileOutputStream("D:\\导出测试.xls");workbook.write(fos);fos.flush();} catch (IOException e) {e.printStackTrace();} finally {try {fos.close();} catch (IOException e) {e.printStackTrace();}}}

导出结果