说明
之前不知道有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>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.6.1</version>
</dependency>
在这里插入代码片
/**
* 申报查询---动态导出excel
*
* @param param 表头信息{@link DeclarationDTO}
*/
@RequestMapping(value = "/downExcel", method = RequestMethod.POST)
@ResponseBody
public 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 java.net.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 isExcel2003 = true;
String match = "^.+\\.(?i)(xlsx)$";
if (fileName.matches(match)) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb;
if (isExcel2003) {
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)
@Documented
public @interface Column {
String name() default "";
}
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @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);
}
}
}