Springboot实现导入导出Excel的方法

Springboot实现导入导出Excel的方法

一、添加poi的maven依赖

org.apache.poi

poi

3.13

org.apache.poi

poi-ooxml

3.13

二、自定义注解(Excel属性标题、位置等)

package com.cloud.core.annotation;

import java.lang.annotation.*;

/**

* 自定义实体类所需要的bean(Excel属性标题、位置等)

* Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved.

* Company: 大连安琪科技有限公司

*

* @author Rex

* @since 2021/5/19 9:30

*/

@Target({ElementType.FIELD})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface ExcelColumn {

/**

* Excel标题

*

* @return

* @author Rex

*/

String value() default "";

/**

* Excel从左往右排列位置，第一个是0

*

* @return

* @author Rex

*/

int col() default 0;

}

三、CustomExcelUtils编写

package com.cloud.core.utils;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;

import com.cloud.core.annotation.ExcelColumn;

import com.cloud.core.common.CommonConst;

import org.apache.commons.lang.BooleanUtils;

import org.apache.commons.lang.CharUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.lang.math.NumberUtils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.http.MediaType;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Constructor;

import java.lang.reflect.Field;

import java.math.BigDecimal;

import java.net.URLEncoder;

import java.util.*;

import java.util.concurrent.atomic.AtomicInteger;

import java.util.stream.Collectors;

import java.util.stream.Stream;

/**

* 自定义导入导出Excel文件类

* Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved.

* Company: 大连安琪科技有限公司

*

* @author Rex

* @since 2021/5/19 9:31

*/

public class CustomExcelUtils {

private final static Logger log = LoggerFactory.getLogger(CustomExcelUtils.class);

private final static String EXCEL2003 = "xls";

private final static String EXCEL2007 = "xlsx";

/**

* 读取Excel

*

* @param path 为了测试文件用，实际为空

* @param cls 类

* @param startRow 起始行

* @param file 文件

* @return

* @author Rex

*/

public static List readExcel(String path, Class cls, int startRow, MultipartFile file) {

String fileName = file.getOriginalFilename();

if (!fileName.matches(CommonConst.Regex.FILE_EXT_XLS) && !fileName.matches(CommonConst.Regex.FILE_EXT_XLSX)) {

log.error("上传文件格式不正确");

}

List dataList = new ArrayList<>();

Workbook workbook = null;

try {

InputStream is = file.getInputStream();

if (fileName.endsWith(EXCEL2007)) {

// FileInputStream is = new FileInputStream(new File(path));

workbook = new XSSFWorkbook(is);

}

if (fileName.endsWith(EXCEL2003)) {

// FileInputStream is = new FileInputStream(new File(path));

workbook = new HSSFWorkbook(is);

}

if (workbook != null) {

//类映射 注解 value-->bean columns

Map> classMap = new HashMap<>();

List fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());

fields.forEach(

field -> {

ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);

if (annotation != null) {

String value = annotation.value();

if (StringUtils.isBlank(value)) {

// return起到的作用和continue是相同的 语法

return;

}

if (!classMap.containsKey(value)) {

classMap.put(value, new ArrayList<>());

}

field.setAccessible(true);

classMap.get(value).add(field);

}

}

);

//索引-->columns

Map> reflectionMap = new HashMap<>(16);

//默认读取第一个sheet

Sheet sheet = workbook.getSheetAt(0);

boolean firstRow = true;

for (int i = startRow; i <= sheet.getLastRowNum(); i++) {

Row row = sheet.getRow(i);

// 提取注解

if (firstRow) {

for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {

Cell cell = row.getCell(j);

String cellValue = getCellValue(cell);

if (classMap.containsKey(cellValue)) {

reflectionMap.put(j, classMap.get(cellValue));

}

}

if (reflectionMap.size() > 0) {

firstRow = false;

}

} else {

//忽略空白行

if (row == null) {

continue;

}

try {

T t = cls.newInstance();

//判断是否为空白行

boolean allBlank = true;

for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {

if (reflectionMap.containsKey(j)) {

Cell cell = row.getCell(j);

String cellValue = getCellValue(cell);

if (StringUtils.isNotBlank(cellValue)) {

allBlank = false;

}

List fieldList = reflectionMap.get(j);

fieldList.forEach(

x -> {

try {

handleField(t, cellValue, x);

} catch (Exception e) {

log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);

}

}

);

}

}

if (!allBlank) {

dataList.add(t);

} else {

log.warn(String.format("row:%s is blank ignore!", i));

}

} catch (Exception e) {

log.error(String.format("parse row:%s exception!", i), e);

}

}

}

}

} catch (Exception e) {

log.error(String.format("parse excel exception!"), e);

} finally {

if (workbook != null) {

try {

workbook.close();

} catch (Exception e) {

log.error(String.format("parse excel exception!"), e);

}

}

}

return dataList;

}

private static void handleField(T t, String value, Field field) throws Exception {

Class> type = field.getType();

if (type == null || type == void.class || StringUtils.isBlank(value)) {

return;

}

if (type == Object.class) {

field.set(t, value);

//数字类型

} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {

if (type == int.class || type == Integer.class) {

field.set(t, NumberUtils.toInt(value));

} else if (type == long.class || type == Long.class) {

field.set(t, NumberUtils.toLong(value));

} else if (type == byte.class || type == Byte.class) {

field.set(t, NumberUtils.toByte(value));

} else if (type == short.class || type == Short.class) {

field.set(t, NumberUtils.toShort(value));

} else if (type == double.class || type == Double.class) {

field.set(t, NumberUtils.toDouble(value));

} else if (type == float.class || type == Float.class) {

field.set(t, NumberUtils.toFloat(value));

} else if (type == char.class || type == Character.class) {

field.set(t, CharUtils.toChar(value));

} else if (type == boolean.class) {

field.set(t, BooleanUtils.toBoolean(value));

} else if (type == BigDecimal.class) {

field.set(t, new BigDecimal(value));

}

} else if (type == Boolean.class) {

field.set(t, BooleanUtils.toBoolean(value));

} else if (type == Date.class) {

//

field.set(t, value);

} else if (type == String.class) {

field.set(t, value);

} else {

Constructor> constructor = type.getConstructor(String.class);

field.set(t, constructor.newInstance(value));

}

}

private static String getCellValue(Cell cell) {

if (cell == null) {

return "";

}

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

if (HSSFDateUtil.isCellDateFormatted(cell)) {

return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();

} else {

return new BigDecimal(cell.getNumericCellValue()).toString();

}

} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

return StringUtils.trimToEmpty(cell.getStringCellValue());

} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

return StringUtils.trimToEmpty(cell.getCellFormula());

} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {

return "";

} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

return String.valueOf(cell.getBooleanCellValue());

} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {

return "ERROR";

} else {

return cell.toString().trim();

}

}

public static void writeExcel(HttpServletResponse response, List dataList, Class cls) {

Field[] fields = cls.getDeclaredFields();

List fieldList = Arrays.stream(fields)

.filter(field -> {

ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);

if (annotation != null && annotation.col() > 0) {

field.setAccessible(true);

return true;

}

return false;

}).sorted(Comparator.comparing(field -> {

int col = 0;

ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);

if (annotation != null) {

col = annotation.col();

}

return col;

})).collect(Collectors.toList());

Workbook wb = new XSSFWorkbook();

Sheet sheet = wb.createSheet("Sheet1");

AtomicInteger ai = new AtomicInteger();

{

Row row = sheet.createRow(ai.getAndIncrement());

AtomicInteger aj = new AtomicInteger();

//写入头部

fieldList.forEach(field -> {

ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);

String columnName = "";

if (annotation != null) {

columnName = annotation.value();

}

Cell cell = row.createCell(aj.getAndIncrement());

CellStyle cellStyle = wb.createCellStyle();

cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

cellStyle.setFillPattern(CellStyle.SOLID_FOhttp://REGROUND);

cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

Font font = wb.createFont();

font.setBoldweight(Font.BOLDWEIGHT_NORMAL);

cellStyle.setFont(font);

cell.setCellStyle(cellStyle);

cell.setCellValue(columnName);

});

}

if (CollectionUtils.isNotEmpty(dataList)) {

dataList.forEach(t -> {

Row row1 = sheet.createRow(ai.getAndIncrement());

AtomicInteger aj = new AtomicInteger();

fieldList.forEach(field -> {

Class> type = field.getType();

Object value = "";

try {

value = field.get(t);

} catch (Exception e) {

e.printStackTrace();

}

Cell cell = row1.createCell(aj.getAndIncrement());

if (value != null) {

if (type == Date.class) {

cell.setCellValue(value.toString());

} else {

cell.setCellValue(value.toString());

}

cell.setCellValue(value.toString());

}

});

});

}

//冻结窗格

wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);

//浏览器下载excel

buildExcelDocument("导出数据.xlsx", wb, response);

//生成excel文件

// buildExcelFile(".\\default.xlsx", wb);

}

/**

* 浏览器下载excel

*

* @param fileName

* @param wb

* @param response

*/

private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {

try {

response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));

response.flushBuffer();

wb.write(response.getOutputStream());

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 生成excel文件

*

* @param path 生成excel路径

* @param wb

*/

private static void buildExcelFile(String path, Workbook wb) {

File file = new File(path);

if (file.exists()) {

file.delete();

}

try {

wb.write(new FileOutputStream(file));

} catch (Exception e) {

e.printStackTrace();

}

}

}

四、定义导出实体类

主要是使用这里的@ExcelColumn注解，其中的col从0开始的。

package com.cloud.library.model.role;

import com.cloud.core.annotation.ExcelColumn;

import lombok.Data;

/**

* 导入角色用

* Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved.

* Company: 大连安琪科技有限公司

*

* @author Rex

* @since 2021/5/19 16:13

*/

@Data

public class SysRoleExcel {

@ExcelColumn(value = "姓名", col = 1)

private String name;

@ExcelColumn(value = "描述", col = 2)

private String description;

}

Excel对应的模板参考

五、Controller层代码编写

//region 导入数据

/**

* 导入数据

*

* @param file

* @return

* @author Rex

*/

@RequestMapping(value = "/readExcel", method = RequestMethod.POST)

public void readExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) {

List list = CustomExcelUtils.readExcel("", SysRoleExcel.class, 0, file);

List sysRoleList = new ArrayList<>();

list.forEach(e -> {

SysRole sysRole = new SysRole();

BeanUtils.copyProperties(e, sysRole);

sysRoleList.add(sysRole);

});

sysRoleService.saveBatch(sysRoleList);

}

// endregion

这里发现了，这个saveBatch可以直接使用雪花的id来保存数据，因为这里用的是mybatis-plus，单条数据保存使用的是它的配置。然后试了下，批量导入也是可以的，另外，这个批量保存，理论上没有条数限制，这个还等待后续测试。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。