java如何导入和导出ex_SpringBoot中关于Excel的导入和导出

前言

由于在最近的项目中使用Excel导入和导出较为频繁，以此篇博客作为记录，方便日后查阅。本文前台页面将使用layui，来演示对Excel文件导入和导出的效果。本文代码已上传至我的gitHub，欢迎访问，地址：https://github.com/rename123/excel-demo

准备工作

1. 添加操作Excel的有关依赖，如下：

org.apache.poi

poi

3.13

org.apache.poi

poi-ooxml

3.13

说明：由于我的项目是使用的maven管理，所以通过如上方式添加依赖，如果是通过gradle构建的项目，请按如下方式导入项目依赖：

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'

2. 自定义注解，用来表示实体类中的属性在Excel中的标题、位置等

package com.reminis.exceldemo.annotation;

import java.lang.annotation.*;

/**

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

*/

@Target({ElementType.FIELD})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface ExcelColumn {

/**

* Excel标题

* @return

*/

String value() default "";

/**

* Excel从左往右排列位置

* @return

*/

int col() default 0;

}

3. 编写ExcelUtils工具类

package com.reminis.exceldemo.util;

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.time.LocalDateTime;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Comparator;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.concurrent.atomic.AtomicInteger;

import java.util.stream.Collectors;

import java.util.stream.Stream;

import com.reminis.exceldemo.annotation.ExcelColumn;

import org.apache.commons.collections.CollectionUtils;

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.Cell;

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

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

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

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

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

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

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.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class ExcelUtils {

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

private final static String EXCEL2003 = "xls";

private final static String EXCEL2007 = "xlsx";

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

String fileName = file.getOriginalFilename();

if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(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;//return起到的作用和continue是相同的 语法

}

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 = sheet.getFirstRowNum(); 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));

}

}

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 if (type == LocalDateTime.class) {

//String 转 LocalDateTime

DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

LocalDateTime dt = LocalDateTime.parse(value, df);

field.set(t, dt);

} 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(HttpServletRequest request, 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_FOREGROUND);

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 if (type == LocalDateTime.class){

DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

cell.setCellValue(df.format((LocalDateTime) value));

} else {

cell.setCellValue(value.toString());

}

}

});

});

}

//冻结窗格

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

//浏览器下载excel

buildExcelDocument(request.getParameter("fileName"),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();

ServletOutputStream outputStream = response.getOutputStream();

wb.write(outputStream);

} 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();

}

}

}

4. 定义需要导出的实体类

package com.reminis.exceldemo.entity;

import com.reminis.exceldemo.annotation.ExcelColumn;

import lombok.Data;

import org.springframework.format.annotation.DateTimeFormat;

import java.math.BigDecimal;

import java.time.LocalDateTime;

import java.util.Date;

@Data

public class Emp {

@ExcelColumn(value = "员工主键id", col = 1)

private Integer id;

@ExcelColumn(value = "员工编号",col = 2)

private String empNo;

@ExcelColumn(value = "员工名称",col = 3)

private String empName;

@ExcelColumn(value = "员工薪资",col = 4)

private BigDecimal salary;

@ExcelColumn(value = "员工职称",col = 5)

private String job;

@ExcelColumn(value = "入职时间",col = 6)

private LocalDateTime entryTime;

}

Controller层编写

在我们做完准备工作后，就可以在我们的Controller层编写访问接口了，由于我们没有连接数据库，所以我准备了一些测试数据，具体代码如下：

package com.reminis.exceldemo.web;

import com.alibaba.fastjson.JSON;

import com.reminis.exceldemo.entity.Emp;

import com.reminis.exceldemo.util.ExcelUtils;

import com.reminis.exceldemo.util.Result;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.math.BigDecimal;

import java.time.LocalDateTime;

import java.util.ArrayList;

import java.util.List;

@CrossOrigin("*")

@RestController

@RequestMapping("/api/test")

public class ExcelController {

private static final Logger log = LogManager.getLogger(ExcelController.class);

/**

* Excel导出

* @param response

*/

@GetMapping("/exportExcel")

public void exportExcel(HttpServletRequest request,HttpServletResponse response){

//使用假数据代替从数据库查出来的需要导出的数据

List empList = handleRepositoryData();

long t1 = System.currentTimeMillis();

ExcelUtils.writeExcel(request,response, empList, Emp.class);

long t2 = System.currentTimeMillis();

System.out.println(String.format("write over! cost:%sms", (t2 - t1)));

}

/**

* Excel导入

* @param file

*/

@PostMapping("/readExcel")

public Result readExcel(@RequestBody MultipartFile file){

long t1 = System.currentTimeMillis();

log.info("上传的文件："+file);

List list = ExcelUtils.readExcel("", Emp.class, file);

long t2 = System.currentTimeMillis();

System.out.println(String.format("read over! cost:%sms", (t2 - t1)));

list.forEach(

b -> System.out.println(JSON.toJSONString(b))

);

return new Result<>();

}

public List handleRepositoryData() {

List empList = new ArrayList<>();

Emp emp;

for (int i = 1; i<= 10; i++) {

emp = new Emp();

emp.setId(i);

emp.setEmpName("员工" + i);

emp.setEmpNo((1000 + i) + "");

emp.setJob("JY" + i);

emp.setSalary(new BigDecimal(i * 1000 + ""));

emp.setEntryTime(LocalDateTime.now().minusHours(Long.valueOf(i)));

empList.add(emp);

}

return empList;

}

/**

* 前台页面的数据列表

* @return

*/

@GetMapping("/getList")

public Result getList(){

Result> result = new Result<>();

List empList = handleRepositoryData();

result.setData(empList);

return result;

}

}

关于Excel导入导出功能的后台接口，到这里就写好了。由于本文示例代码中使用了Java8中的新时间，所以在将数据返回给前台页面时，我们需要对时间格式进行处理，如下：

package com.reminis.exceldemo.config;

import java.time.LocalDateTime;

import java.time.format.DateTimeFormatter;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.boot.autoconfigure.jackson.Jackson2ObjectMapperBuilderCustomizer;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;

@Configuration

public class LocalDateTimeSerializerConfig {

@Value("${spring.jackson.date-format:yyyy-MM-dd HH:mm:ss}")

private String pattern;

@Bean

public LocalDateTimeSerializer localDateTimeDeserializer() {

return new LocalDateTimeSerializer(DateTimeFormatter.ofPattern(pattern));

}

@Bean

public Jackson2ObjectMapperBuilderCustomizer jackson2ObjectMapperBuilderCustomizer() {

return builder -> builder.serializerByType(LocalDateTime.class, localDateTimeDeserializer());

}

}

最后就是layui展示页面了，是一个很简单上传下载的列表页面，代码如下：

Excel文件的导入导出测试

Excel文件的导入导出测试

 导入Excel 

后台接口导出Excel

layui导出选中行的数据

//一般直接写在一个js文件中

layui.use(['table', 'layer','upload'], function(){

var table = layui.table

,$ = layui.$

,layer = layui.layer

,upload = layui.upload;

// layer.msg('Hello World');

var ins1 = table.render({

elem: '#test'

,url:'http://localhost:8080/api/test/getList'

,cols: [[

{type:'checkbox'}

,{field:'id', title: 'ID', sort: true}

,{field:'empNo',title: '员工编号'}

,{field:'empName',title: '员工名称'}

,{field:'salary', title: '薪资'}

,{field:'job', title: '职称'} //minWidth：局部定义当前单元格的最小宽度，layui 2.2.1 新增

,{field:'entryTime', title: '入职时间'}

]]

});

//指定允许上传的文件类型

upload.render({

elem: '#test3'

,url: 'http://localhost:8080/api/test/readExcel' //改成您自己的上传接口

,accept: 'file' //普通文件

,done: function(res){

layer.msg('上传成功');

console.log(res);

}

});

//Excel后台导出

$("#test4").click(function () {

// 文件名称可以根据自己需要进行设置

window.open('http://localhost:8080/api/test/exportExcel?fileName=员工表导出测试.xlsx')

})

//Excel通过layui导出

$("#test5").click(function () {

// console.log("123")

var checkStatus = table.checkStatus('test'); //test 即为table绑定的id

//获取选中行的数据

var data = checkStatus.data;

//将上述表格示例中的指定数据导出为 Excel 文件

table.exportFile(ins1.config.id, data); //data 为该实例中的任意数量的数据

})

});

由于博客园还不支持上传视频，我就放几张运行的效果图吧，本文代码也已经上传至gitHub，本文有些代码没有写出来，可以到gitHub上把代码拉下来进行测试：

因为本文只是对excel的导入和导出进行测试，并没有来连接数据进行入库操作，但在导入Excel这个接口中，我已经获取到了导入的数据，并在控制台打印了出来，如下：