POI导出Excel JAVA根据POI封装Excel的导出（支持动态表头）

POI导出Excel、JAVA根据POI封装Excel的导出（支持动态表头）

说明

之前不知道有easy-poi这个封装好的包。在使用poi的过程中，慢慢的根据实际业务封装的。

通过注解方式，生成Excel，支持动态表头。

代码

代码从接口调用开始说明

poi版本

<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>3.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>3.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>3.1.0</version></dependency><dependency><groupId>mons</groupId><artifactId>commons-math3</artifactId><version>3.6.1</version></dependency>在这里插入代码片

/*** 申报查询---动态导出excel** @param param 表头信息{@link DeclarationDTO}*/@RequestMapping(value = "/downExcel", method = RequestMethod.POST)@ResponseBodypublic void downExcel(@RequestBody DeclarationParam param, HttpServletResponse response) {try {List<DeclarationDownExcelVO> declarationDownExcelVOS = declarationService.downExcel(param);//param.getColumns() 这个是动态表头，传入的值为DeclarationDownExcelVO实体中的字段名称ExcelUtils.downExcel(declarationDownExcelVOS, DeclarationDownExcelVO.class, param.getColumns(), response);} catch (Exception e) {e.printStackTrace();log.error("导出Excel失败，参数：" + param, e);}}

这个是业务相关的实体类：

import com.owinfo.mpw.cash.util.BaseUtils;import com.owinfo.mpw.cash.util.Constant;import com.owinfo.mpw.cash.util.ExcelUtils;import lombok.Getter;import lombok.Setter;import lombok.ToString;import java.io.Serializable;import java.util.Date;/*** 大量现金,导出excel*/@Setter@Getter@ToString@ExcelUtils.ExcelName(name = "申請表信息")public class DeclarationDownExcelVO implements Serializable {/*** 申报书编号*/@ExcelUtils.Column(name = "現金申報書編號")private String formId;/*** 入境起源地*/@ExcelUtils.Column(name = "入境啓程地")private String entry;/*** 出境目的地*/@ExcelUtils.Column(name = "出境目的地")private String exits;/*** 姓名*/@ExcelUtils.Column(name = "姓名")private String name;/*** 性别；0男，1女*/@ExcelUtils.Column(name = "性别")private String gender;/*** 国籍*/@ExcelUtils.Column(name = "國籍")private String nationality;/*** 出生地*/@ExcelUtils.Column(name = "出生地")private String birthplace;/*** 出生日期*/@ExcelUtils.Column(name = "出生日期")private String birthTime;/*** 证件类别*/@ExcelUtils.Column(name = "證件類型")private String documentType;/*** 证件号码*/@ExcelUtils.Column(name = "證件號碼")private String documentNum;/*** 签发地*/@ExcelUtils.Column(name = "簽發地")private String issue;/*** 永久居住地*/@ExcelUtils.Column(name = "永久居住地")private String address;/*** 电话号码*/@ExcelUtils.Column(name = "聯係電話")private String phone;/*** 类型：0现金，1旅行支票，2支票，3汇票，4付款委托书，5本票，6其他*/@ExcelUtils.Column(name = "票據類型")private String cashType;/*** 货币名称*/@ExcelUtils.Column(name = "貨幣類別")private String currencyType;/*** 数额*/@ExcelUtils.Column(name = "數額(最接近的整數)")private Integer money;/*** 此项目属于声明人所有：0否，1是*/@ExcelUtils.Column(name = "此項目屬於聲明人所有")private String myself;/*** 0法人，1自然人*/private Integer dataType;/*** 法人或自然人姓名*/private String aliasName;/*** 法人姓名*/@ExcelUtils.Column(name = "名稱(法人)")private String legalPerson;/*** 自然人姓名*/@ExcelUtils.Column(name = "名稱(自然人)")private String naturalPerson;/*** 性别；0男，1女*/@ExcelUtils.Column(name = "所有人性別")private String aliasGender;/*** 法人或自然人居住地址*/@ExcelUtils.Column(name = "法人住所/自然人永久居所住址")private String aliasAddress;/*** 海关签名*/@ExcelUtils.Column(name = "海關人員簽名及海關簽章")private String customsSignature;/*** 用户申请时间*/@ExcelUtils.Column(name = "申報日期")private String applyTime;/*** 申报书流水号*/@ExcelUtils.Column(name = "申報書流水號")private String formSerialNum;private static final long serialVersionUID = 1L;}

以下为封装需要的代码

public class ExcleVO {/*** 字段名称*/private List<String> colums;/*** 表名*/private String name;/*** 记录在excle中的列数*/private Map<String, Integer> position;public List<String> getColums() {return colums;}public void setColums(List<String> colums) {this.colums = colums;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Map<String, Integer> getPosition() {return position;}public void setPosition(Map<String, Integer> position) {this.position = position;}}

封装好的ExcelUtils 导出工具类，如果字段内容超过255个字符，请把402行的if else代码注释掉，不然会报错

package com.owinfo.mpw.cash.util;import com.alibaba.druid.util.StringUtils;import com.owinfo.mpw.cash.service.entity.vo.ExcleVO;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.InputStream;import java.io.OutputStream;import java.lang.annotation.*;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.math.BigDecimal;import .URLEncoder;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.*;public class ExcelUtils {/*** 导入Excel* 样例：List<DeclarationElementExcelParam> params = ExcelUtils.importExcel(file, DeclarationElementExcelParam.class, NumberConstant.THREE);** @param file 文件；文件格式要求，“第一行”为表头，从“第二行”为正式数据，并且“第一列”必须是序号* @param clazz接收解析Excel内容的实体；实体要求，实体的“字段顺序”必须和Excel“表头的顺序”一样;* 实体的字段类型，只支持String，Integer，Double，Date* @param index表示到实体的第几个字段结束。例：Excel表中，除去序号那一列，真实的表头有10个，那么在实体中* 就有10个字段来接收，但是实体中还会有常用字段之类的，所以要指定字段的数量，这里index就传10。* @param sheetIndex 表示获取第几个sheet的数据* @param rowIndex 表示从第几行开始读数据* @param cell 表示从第几列读* @return 返回Excel中的数据*/public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz, int index, int sheetIndex, int rowIndex, int cell) throws Exception {List<T> params = new ArrayList<>();String fileName = file.getOriginalFilename();if (ExcelUtils.checkExcel(file)) {throw new Exception("请上传Excel格式");}boolean isExcel = true;String match = "^.+\\.(?i)(xlsx)$";if (fileName.matches(match)) {isExcel = false;}InputStream is = file.getInputStream();Workbook wb;if (isExcel) {wb = new HSSFWorkbook(is);} else {wb = new XSSFWorkbook(is);}Sheet sheet = wb.getSheetAt(sheetIndex);if (sheet == null) {throw new Exception("文件为空");}for (int r = rowIndex; r <= sheet.getLastRowNum(); r++) {Row row = sheet.getRow(r);if (isRowEmpty(row)) {continue;}//接收参数对象T t = clazz.newInstance();//NumberConstant.TWENTY_FOUR 表示循环到对象里面的第几个参数ExcelUtils.getRowValue(t, row, index, cell);params.add(t);}return params;}/*** 判断行是否为空** @param row 行对象* @return 判断结果*/private static boolean isRowEmpty(Row row) {for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {Cell cell = row.getCell(c);if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {return false;}}return true;}/*** 导出Excel* * @param datas导出的数据* @param selColumns 动态字段集合，不需要动态字段传null* @param clazz 封装导出数据的实体，就是T是什么，这里就传他的class对象* @param response 下载响应*/public static <T> void downExcel(List<T> datas, Class<?> clazz, List<String> selColumns, HttpServletResponse response) throws Exception {XSSFWorkbook wb = new XSSFWorkbook();ExcleVO excleVO = getNameAndColumn(clazz, selColumns);SimpleDateFormat formatter1 = new SimpleDateFormat("yyyyMMddHHmmss");try {// 告诉浏览器用什么软件可以打开此文件response.setHeader("content-Type", "application/vnd.ms-excel");// 下载文件的默认名称response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excleVO.getName() + formatter1.format(new Date()) + ".xlsx", "utf-8"));//======第一个sheet=========//表格1的名称String sheetName1 = excleVO.getName();XSSFSheet sheet1 = wb.createSheet(sheetName1);XSSFDrawing patriarch = sheet1.createDrawingPatriarch();sheet1.setDefaultColumnWidth(20);sheet1.setDefaultRowHeight((short) (45 * 10));String[] titles = excleVO.getColums().toArray(new String[excleVO.getColums().size()]);//定义表头//返回写到第几行了int rowIndex2 = ExcelUtils.writeTitlesToExcel1(wb, sheet1, titles);//写正式内容writeRowsToExcel1(wb, sheet1, datas, rowIndex2, excleVO.getPosition(), patriarch);//设置宽度ExcelUtils.autoSizeColumns(sheet1, titles.length + 1);OutputStream out = response.getOutputStream();wb.write(out);out.flush();out.close();} catch (Exception e) {e.printStackTrace();}}/*** 获取单元格内容** @param obj 生成的对象* @param row 航对象* @param index 表示赋值到第几个字段结束*/public static <T> void getRowValue(T obj, Row row, int index, int cell) throws Exception {Class<?> clazz = obj.getClass();Field[] fields = clazz.getDeclaredFields();for (int i = 0; i < index; i++) {Field field = fields[i];field.setAccessible(true);Class<?> type = field.getType();Method m = clazz.getMethod("set" + BaseUtils.upperCase(field.getName()), type);//给该方法设置值if (type == Date.class) {//处理日期Cell cell1 = row.getCell(i + cell);Date d = null;if (cell1 != null && !StringUtils.isEmpty(cell1.toString())) {d = cell1.getDateCellValue();}if (d != null) {DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String res = formater.format(d);m.invoke(obj, formater.parse(res));}} else if (type == Integer.class) {String s = setFiled(row, i + cell);if (!StringUtils.isEmpty(s)) {m.invoke(obj, Integer.valueOf(s));}} else if (type == Double.class) {String s = setFiled(row, i + cell);if (!StringUtils.isEmpty(s)) {m.invoke(obj, Double.valueOf(s));}} else if (type == BigDecimal.class) {String s = setFiled(row, i + cell);if (!StringUtils.isEmpty(s)) {m.invoke(obj, new BigDecimal(s));}} else {String s = setFiled(row, i + cell);m.invoke(obj, s);}}}/*** 获取单元格内容** @param i 第几列* @param row 航对象* @return 获取结果*/private static String setFiled(Row row, int i) throws Exception {if (row.getCell(i) != null) {row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);return row.getCell(i).getStringCellValue().trim();}return "";}/*** 讲对象写入单元格** @param g 填入的数据* @param dataRow 航对象*/private static <T> void createExcel(Row dataRow, T g, XSSFCellStyle dataStyle, XSSFCellStyle dataStyle1, Map<String, Integer> position, XSSFWorkbook wb, XSSFDrawing patriarch) throws Exception {Class<?> clazz = g.getClass();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {//设置是否允许访问，不是修改原来的访问权限修饰词。if (position.containsKey(field.getName())) {field.setAccessible(true);Cell cell1 = dataRow.createCell(1 + position.get(field.getName()));cell1.setCellValue("" + (field.get(g) == null ? "" : "" + field.get(g)));if (field.getType() == String.class && !field.getName().equals("filePath")) {cell1.setCellStyle(dataStyle1);} else {cell1.setCellStyle(dataStyle);}}}}/*** 判断是否是excel** @param file 文件* @return 获取结果*/public static boolean checkExcel(@RequestBody MultipartFile file) {String fileName = file.getOriginalFilename();String match1 = "^.+\\.(?i)(xls)$";String match2 = "^.+\\.(?i)(xlsx)$";return !fileName.matches(match1) && !fileName.matches(match2);}/*** sheet1写正式数据** @param wb 表信息*/private static XSSFCellStyle getDataStyle(XSSFWorkbook wb) {Font dataFont = wb.createFont();dataFont.setFontName("宋体");dataFont.setColor(IndexedColors.BLACK.index);dataFont.setFontHeightInPoints((short) 16);XSSFCellStyle dataStyle = wb.createCellStyle();dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);dataStyle.setFont(dataFont);dataStyle.setWrapText(true);setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));//设置标题单元格类型//下边框dataStyle.setBorderBottom(CellStyle.BORDER_THIN);//左边框dataStyle.setBorderLeft(CellStyle.BORDER_THIN);//上边框dataStyle.setBorderTop(CellStyle.BORDER_THIN);//右边框dataStyle.setBorderRight(CellStyle.BORDER_THIN);return dataStyle;}/*** sheet1 设置表头，包括格式和背景颜色** @param wb 表信息* @param sheet 表信息* @param heads 表头信息* @return 返回到第几行*/private static int writeTitlesToExcel1(XSSFWorkbook wb, Sheet sheet, String[] heads) {//字体样式Font dataFont = wb.createFont();dataFont.setFontName("simsun");dataFont.setColor(IndexedColors.BLACK.index);XSSFCellStyle dataStyle = wb.createCellStyle();dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);dataStyle.setFont(dataFont);dataStyle.setWrapText(true);setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));XSSFCellStyle black = getBlack(wb);//创建第一行Row titleRow = sheet.createRow(0);for (int i = 0; i < heads.length; i++) {Cell cell = titleRow.createCell(i);cell.setCellValue(heads[i]);cell.setCellStyle(black);}//返回用了几行return 1;}/*** sheet1 设置表头，包括格式和背景颜色** @param style 表信息* @param border 表信息* @param color 颜色*/public static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {style.setBorderTop(border);style.setBorderLeft(border);style.setBorderRight(border);style.setBorderBottom(border);style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);}/*** sheet1 设置颜色** @param wb 表信息* @return XSSFCellStyle*/public static XSSFCellStyle getBlack(XSSFWorkbook wb) {//字体样式Font titleFont = wb.createFont();titleFont.setFontName("宋体");titleFont.setItalic(false);titleFont.setFontHeightInPoints((short) 16);titleFont.setColor(IndexedColors.BLACK.index);//表头样式XSSFCellStyle titleStyle = wb.createCellStyle();titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);titleStyle.setFont(titleFont);setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(133, 5, 11)));return titleStyle;}/*** sheet1 标题居中** @param wb 表信息* @return XSSFCellStyle*/public static XSSFCellStyle getBlackTitle(XSSFWorkbook wb) {//字体样式Font titleFont = wb.createFont();titleFont.setFontName("宋体");titleFont.setItalic(false);titleFont.setFontHeightInPoints((short) 22);titleFont.setColor(IndexedColors.BLACK.index);//表头样式XSSFCellStyle titleStyle = wb.createCellStyle();titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);titleStyle.setFont(titleFont);return titleStyle;}/*** sheet1 标题下面的信息格式** @param wb 表信息* @return XSSFCellStyle*/public static XSSFCellStyle getBlack2(XSSFWorkbook wb) {//字体样式Font titleFont = wb.createFont();titleFont.setFontName("宋体");titleFont.setItalic(false);titleFont.setFontHeightInPoints((short) 16);titleFont.setColor(IndexedColors.BLACK.index);//表头样式XSSFCellStyle titleStyle = wb.createCellStyle();titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);titleStyle.setFont(titleFont);return titleStyle;}/*** sheet1 设置表头，包括格式和背景颜色** @param sheet 表信息* @param columnNumber 颜色号*/public static void autoSizeColumns(Sheet sheet, int columnNumber) {for (int i = 0; i < columnNumber; i++) {int orgWidth = sheet.getColumnWidth(i);sheet.autoSizeColumn(i, true);int newWidth = (sheet.getColumnWidth(i) + 100);if (newWidth > orgWidth) {sheet.setColumnWidth(i, newWidth);} else {sheet.setColumnWidth(i, orgWidth);}}}@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface Column {String name() default "";}@Target({ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface ExcelName {String name() default "";}public static ExcleVO getNameAndColumn(Class<?> clazz, List<String> selColumns) {ExcleVO excleVO = new ExcleVO();List<String> heads = new ArrayList<>();heads.add("序号");Map<String, Integer> position = new HashMap<>(16);excleVO.setColums(heads);excleVO.setPosition(position);Class<ExcelName> excelNameClass = ExcelName.class;Class<Column> columnClass = Column.class;if (clazz.isAnnotationPresent(excelNameClass)) {//获取类上的名称ExcelName annotation = clazz.getAnnotation(excelNameClass);excleVO.setName(annotation.name());}Field[] declaredFields = clazz.getDeclaredFields();int i = 0;for (Field field : declaredFields) {//((selColumns==null || selColumns.size() == 0)?true:selColumns.contains(field.getName())) 这个判断意思是，如果selColumns为空时，就表示不是动态字段，不需要再做后续判断if (field.isAnnotationPresent(columnClass) && ((selColumns==null || selColumns.size() == 0)?true:selColumns.contains(field.getName()))) {Column annotation = field.getAnnotation(columnClass);heads.add(annotation.name());position.put(field.getName(), i);i++;}}return excleVO;}/*** sheet1写正式数据** @param wb 表信息* @param sheet 表信息* @param datas写入的数据* @param rowIndex 从第几行开始*/public static <T> void writeRowsToExcel1(XSSFWorkbook wb, Sheet sheet, List<T> datas, int rowIndex, Map<String, Integer> position, XSSFDrawing patriarch) throws Exception {XSSFCellStyle dataStyle = ExcelUtils.getDataStyle(wb);XSSFCellStyle dataStyle1 = ExcelUtils.getDataStyle(wb);XSSFDataFormat dataFormat = wb.createDataFormat();dataStyle1.setDataFormat(dataFormat.getFormat("@"));for (int i = 0; i < datas.size(); i++) {T g = datas.get(i);Row dataRow = sheet.createRow(i + rowIndex);Cell cell0 = dataRow.createCell(0);cell0.setCellValue(i + 1);cell0.setCellStyle(dataStyle);ExcelUtils.createExcel(dataRow, g, dataStyle, dataStyle1, position, wb, patriarch);}}}