一.引入MAVEN依赖
这里主要使用poi-ooxml,lombok和commons-lang3
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
二.自定义注解
1.ExcelColumn注解
ExcelColumn主要用来标记Excel的数据字段的一些信息
@Documented
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
/**
* 标题
*
* @return
*/
String title();
/**
* 下标
*
* @return
*/
int index() default 0;
/**
* 标题行坐标
*
* @return
*/
int rowIndex() default 0;
/**
* 类型
*
* @return
*/
ExcelType type() default ExcelType.STRING;
/**
* 格式
*
* @return
*/
String format() default "";
}
2.ExcelSheet 注解
ExcelSheet注解用来描述Excel中工作簿的一些信息
@Documented
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSheet {
/**
* Sheet 标题
*
* @return
*/
String title() default "";
/**
* Sheet下标
*
* @return
*/
int index() default 0;
}
3.ExcelTemplate注解
ExcelTemplate注解用来工作簿中的一些标题信息和表头信息,用来处理一些导出时需要用的双表头或者需要给工作表添加标题的场合.
@Documented
@Target({ElementType.TYPE, ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTemplate {
/**
* 单元格内的值
*
* @return
*/
String value() default "";
/**
* 合并行的数量
*
* @return
*/
int rowspan() default 0;
/**
* 起始列
*
* @return
*/
int colIndex();
/**
* 列合并的数量
*
* @return
*/
int colspan() default 0;
}
三.自定义类型
这里定义一个枚举类型的类,用来标记导出时每个字段的类型,这里和ExcelColumn配合使用
public enum ExcelType {
STRING,
DOUBLE,
INT,
DECIMAL,
DATE,
LOCAL_DATE,
LOCAL_DATE_TIME,
LOCAL_TIME;
}
四.导入导出工具类
1.导出数据模型
ExcelData用于存放导出数据,fileName用来存放要导出Excel文件的文件名,data用来存放导出到Excel的数据
@Data
public class ExcelData<T> {
private String fileName;
private List<T> data;
}
2.工具类
@Slf4j
@UtilityClass
public class ExcelUtils {
/**
* xls 后缀
*/
private final String XLS = ".xls";
/**
* xlsx 后缀
*/
private final String XLS_X = ".xlsx";
/**
* sheet页的第一行
*/
private final int FIRST_ROW = 0;
/**
* 第一个工作簿
*/
private final int FIRST_SHEET = 0;
/**
* sheet页的第一列
*/
private final int FIRST_COL = 0;
/**
* 科学计数
*/
private final static String E = "e";
private final String TIMEF_FORMAT = "yyyy-MM-dd HH:mm:ss";
private final String DATE_FORMAT = "yyyy-MM-dd";
public <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
checkFile(file);
Workbook workbook = getWorkBook(file);
List<T> list = new ArrayList<T>();
Field[] fields = getFields(clazz);
if (Objects.nonNull(workbook)) {
Sheet sheet = getSheet(workbook, clazz);
if (sheet == null || sheet.getLastRowNum() == 0) {
return list;
}
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
Object obj;
try {
obj = clazz.newInstance();
} catch (IllegalAccessException e) {
log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！");
throw new RuntimeException("excel导入异常", e);
} catch (InstantiationException e) {
log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！");
throw new RuntimeException("excel导入异常", e);
}
boolean setValue = false;
for (Field field : fields) {
ExcelColumn excelColumn = field.getDeclaredAnnotation(ExcelColumn.class);
if (Objects.isNull(excelColumn)) {
return null;
}
Cell cell = row.getCell(excelColumn.index());
if (excelColumn.rowIndex() >= rowNum) {
break;
}
if (!setValue) {
setValue = true;
}
Object value = getCellValue(cell, field);
createBean(field, obj, value);
}
if (setValue) {
list.add((T) obj);
}
}
}
return list;
}
private <T> void createBean(Field field, T newInstance, Object value) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
try {
if (value == null) {
field.set(newInstance, null);
} else if (Long.class.equals(field.getType())) {
field.set(newInstance, Long.valueOf(String.valueOf(value)));
} else if (String.class.equals(field.getType())) {
field.set(newInstance, String.valueOf(value));
} else if (Integer.class.equals(field.getType())) {
field.set(newInstance, Integer.valueOf(String.valueOf(value)));
} else if (int.class.equals(field.getType())) {
field.set(newInstance, Integer.parseInt(String.valueOf(value)));
} else if (Date.class.equals(field.getType())) {
field.set(newInstance, (Date) value);
} else if (Boolean.class.equals(field.getType())) {
field.set(newInstance, (Boolean) value);
} else if (Double.class.equals(field.getType())) {
field.set(newInstance, Double.valueOf(String.valueOf(value)));
} else if (LocalDate.class.equals(field.getType())) {
field.set(newInstance, ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDate());
} else if (LocalDateTime.class.equals(field.getType())) {
field.set(newInstance, ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
} else {
field.set(newInstance, value);
}
} catch (IllegalAccessException e) {
log.error("【excel导入】clazz映射地址：{},{},{}", newInstance, "excel实体赋值类型转换异常！", e);
throw new RuntimeException("excel实体赋值类型转换异常", e);
}
}
private Object getCellValue(Cell cell, Field field) {
Object cellValue = null;
if (cell == null) {
return cellValue;
}
// 把数字当成String来读，避免出现1读成1.0的情况
// 判断数据的类型
switch (cell.getCellType()) {
case NUMERIC:
if (cell.getCellType() == CellType.NUMERIC) {
if (DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
CellStyle style = cell.getCellStyle();
if (style == null) {
return false;
}
int i = style.getDataFormat();
String f = style.getDataFormatString();
boolean isDate = DateUtil.isADateFormat(i, f);
if (isDate) {
Date date = cell.getDateCellValue();
return cellValue = date;
}
}
}
// 防止科学计数进入
if (String.valueOf(cell.getNumericCellValue()).toLowerCase().contains(E)) {
throw new RuntimeException("excel数据类型错误，请将数字转文本类型！！");
}
if ((int) cell.getNumericCellValue() != cell.getNumericCellValue()) {
// double 类型
cellValue = cell.getNumericCellValue();
} else {
cellValue = (int) cell.getNumericCellValue();
}
break;
// 字符串
case STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
// Boolean
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 公式
case FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
// 空值
case BLANK:
cellValue = null;
break;
// 故障
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
private <T> Sheet getSheet(Workbook workbook, Class<T> clazz) {
Sheet sheet = null;
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
ExcelSheet excelSheet = clazz.getDeclaredAnnotation(ExcelSheet.class);
sheet = workbook.getSheetAt(excelSheet.index());
} else {
sheet = workbook.getSheetAt(FIRST_SHEET);
}
return sheet;
}
private <T> Field[] getFields(Class<T> clazz) {
//获取对象总数量
Field[] fields = clazz.getDeclaredFields();
if (fields == null || fields.length == 0) {
log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！");
throw new RuntimeException("excel导入】clazz映射地址：" + clazz.getCanonicalName() + ",实体空异常！");
}
for (Field field : fields) {
if (!field.isAnnotationPresent(ExcelColumn.class)) {
log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空Excel注解异常！");
throw new RuntimeException("【excel导入】clazz映射地址：" + clazz.getCanonicalName() + ", 实体空Excel注解异常！");
}
}
return fields;
}
private Workbook getWorkBook(MultipartFile file) {
// 获得文件名
String fileName = file.getOriginalFilename();
// 创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
// 获取excel文件的io流
InputStream is;
try {
is = file.getInputStream();
// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith(XLS)) {
// 2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith(XLS_X)) {
// 2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
throw new RuntimeException("excel 转换 HSSFWorkbook 异常！", e);
}
return workbook;
}
private void checkFile(MultipartFile file) {
// 判断文件是否存在
if (null == file) {
throw new RuntimeException("文件不存在!!");
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith(XLS) && !fileName.endsWith(XLS_X)) {
throw new RuntimeException(fileName + "不是excel文件");
}
}
public <T> void exportExcel(HttpServletResponse response, ExcelData data, Class<T> clazz) {
log.info("导出解析开始，fileName:{}", data.getFileName());
try {
//实例化XSSFWorkbook
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个Excel表单，参数为sheet的名字
XSSFSheet sheet = setSheet(clazz, workbook);
//设置单元格并赋值
setData(workbook, sheet, data.getData(), setTitle(workbook, sheet, clazz));
//设置浏览器下载
setBrowser(response, workbook, data.getFileName() + XLS_X);
log.info("导出解析成功!");
} catch (Exception e) {
log.info("导出解析失败!");
e.printStackTrace();
}
}
private <T> XSSFSheet setSheet(Class<T> clazz, XSSFWorkbook workbook) {
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
ExcelSheet excelSheet = clazz.getDeclaredAnnotation(ExcelSheet.class);
return workbook.createSheet(excelSheet.title());
}
return workbook.createSheet("sheet");
}
private Field[] setTitle(XSSFWorkbook workbook, XSSFSheet sheet, Class clazz) {
Field[] fields = clazz.getDeclaredFields();
try {
XSSFCellStyle style = createXssfCellStyle(workbook);
setHeaderTemplate(sheet, clazz, style);
setColumnTemplate(sheet, fields, style);
setColumnTitle(sheet, fields, style);
} catch (Exception e) {
log.info("导出时设置表头失败！");
e.printStackTrace();
} finally {
return fields;
}
}
private XSSFCellStyle createXssfCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
style.setFont(font);
XSSFDataFormat fmt = workbook.createDataFormat();
style.setDataFormat(fmt.getFormat("m/d/yy h:mm"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
private void setColumnTemplate(XSSFSheet sheet, Field[] fields, XSSFCellStyle style) {
int nextRow = sheet.getLastRowNum() + 1;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelTemplate.class)) {
ExcelTemplate template = field.getDeclaredAnnotation(ExcelTemplate.class);
CellRangeAddress region = new CellRangeAddress(nextRow, nextRow + template.rowspan(), template.colIndex(), template.colIndex() + template.colspan());
XSSFRow row = sheet.getRow(nextRow);
if (Objects.isNull(row)) {
row = sheet.createRow(nextRow);
}
sheet.addMergedRegion(region);
XSSFCell cell = row.createCell(template.colIndex());
cell.setCellValue(template.value());
cell.setCellStyle(style);
XSSFRow lastRow = sheet.getRow(nextRow + template.rowspan());
if (Objects.isNull(lastRow)) {
sheet.createRow(nextRow + template.rowspan());
}
}
}
}
private void setColumnTitle(XSSFSheet sheet, Field[] fields, XSSFCellStyle style) {
int nextRow = sheet.getLastRowNum() + 1;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getDeclaredAnnotation(ExcelColumn.class);
sheet.setColumnWidth(excelColumn.index(), 15 * 256);
XSSFRow row = sheet.getRow(nextRow);
if (Objects.isNull(row)) {
row = sheet.createRow(nextRow);
}
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue(excelColumn.title());
cell.setCellStyle(style);
}
}
}
private void setHeaderTemplate(XSSFSheet sheet, Class clazz, XSSFCellStyle style) {
if (clazz.isAnnotationPresent(ExcelTemplate.class)) {
ExcelTemplate template = (ExcelTemplate) clazz.getDeclaredAnnotation(ExcelTemplate.class);
CellRangeAddress region = new CellRangeAddress(FIRST_ROW, FIRST_ROW + template.rowspan(), template.colIndex(), template.colIndex() + template.colspan());
XSSFRow row = sheet.createRow(FIRST_ROW);
sheet.addMergedRegion(region);
XSSFCell cell = row.createCell(FIRST_COL);
cell.setCellValue(template.value());
cell.setCellStyle(style);
}
}
private <T> void setData(XSSFWorkbook workbook, XSSFSheet sheet, List<T> data, Field[] fields) {
try {
int lastRow = sheet.getLastRowNum();
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(lastRow + i + 1);
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
Object value = field.get(data.get(i));
if (Objects.isNull(value)) {
continue;
}
if (field.getType().equals(Double.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Double) value);
setDataCellStyle(workbook, excelColumn, cell);
} else if (field.getType().equals(Date.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Date) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : TIMEF_FORMAT);
} else if (field.getType().equals(LocalDate.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((LocalDate) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : DATE_FORMAT);
} else if (field.getType().equals(LocalDateTime.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((LocalDateTime) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : TIMEF_FORMAT);
} else if (field.getType().equals(Integer.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Integer) value);
setDataCellStyle(workbook, excelColumn, cell);
} else {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((String) value);
setDataCellStyle(workbook, excelColumn, cell);
}
}
}
}
log.info("表格赋值成功！");
} catch (Exception e) {
log.info("表格赋值失败！");
e.printStackTrace();
}
}
private void setDataCellStyle(XSSFWorkbook workbook, XSSFCell cell, String format) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFDataFormat fmt = workbook.createDataFormat();
style.setDataFormat(fmt.getFormat(format));
cell.setCellStyle(style);
}
private void setDataCellStyle(XSSFWorkbook workbook, ExcelColumn excelColumn, XSSFCell cell) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFDataFormat fmt = workbook.createDataFormat();
if (StringUtils.isNoneBlank(excelColumn.format())) {
style.setDataFormat(fmt.getFormat(excelColumn.format()));
}
cell.setCellStyle(style);
}
private void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
log.info("设置浏览器下载成功！");
} catch (Exception e) {
log.info("设置浏览器下载失败！");
e.printStackTrace();
}
}
}
五.示例
创建实体类:
@Data
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelSheet(title = "用户信息")
@ExcelTemplate(value = "用户列表", rowspan = 0, colIndex = 0, colspan = 5)
public class User {
@ExcelTemplate(value = "行合并", rowspan = 1, colIndex = 0)
@ExcelColumn(title = "用户名", index = 0, rowIndex = 3)
private String username;
@ExcelColumn(title = "姓名", index = 1, rowIndex = 3)
private String name;
@ExcelTemplate(value = "列合并", colIndex = 2, colspan = 1)
@ExcelColumn(title = "性别", index = 2, rowIndex = 3)
private String sex;
@ExcelColumn(title = "年龄", index = 3, rowIndex = 3)
private Integer age;
@ExcelColumn(title = "时间", index = 4, type = ExcelType.DATE, format = "yyyy-MM-dd", rowIndex = 3)
private Date date;
}
1.导出程序测试
@GetMapping("export")
public void excelTest(HttpServletResponse response) {
List<User> list = new ArrayList<>();
list.add(User.builder().username("0001").name("张三").sex("男").age(10).date(new Date()).build());
list.add(User.builder().username("0002").name("李四").sex("女").age(11).build());
list.add(User.builder().username("0003").name("王五").sex("男").age(12).build());
list.add(User.builder().username("0004").name("赵六").sex("女").age(13).build());
list.add(User.builder().username("0005").name("孙七").sex("男").age(14).build());
ExcelData<User> data = new ExcelData<>();
data.setFileName("测试");
data.setData(list);
ExcelUtils.exportExcel(response, data, User.class);
}
导出结果:
image-20200302175316912.png
2.导入程序测试
@PostMapping("upload")
public void uploadTest(MultipartFile file) throws IOException {
List<User> list = ExcelUtils.importExcel(file, User.class);
for (User user : list) {
System.out.println(user.toString());
}
}
这里我将刚刚导出的数据通过表单导入到后台,得到打印结果:
image-20200302175751084.png
六 总结
目前来说这套代码还是有很多不足之处,目前只能处理一些简单的导入导出功能,遇到一些复杂的业务场景可能就有点无能为力了,用的时候还是要根据实际需求来,这里只是提供一种思路,代码仅供参考.
