Java如何导入和导出Excel文件的方法和步骤详解

前言

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

准备工作

1. 添加操作Excel的有关依赖&＃xff0c;如下&＃xff1a;

org.apache.poi

poi

3.13

org.apache.poi

poi-ooxml

3.13

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

implementation group: &＃39;org.apache.poi&＃39;, name: &＃39;poi-ooxml&＃39;, version: &＃39;3.17&＃39;

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

package com.reminis.exceldemo.annotation;

import java.lang.annotation.*;

/**

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

*/

&＃64;Target({ElementType.FIELD})

&＃64;Retention(RetentionPolicy.RUNTIME)

&＃64;Documented

public &＃64;interface ExcelColumn {

/**

* Excel标题

* &＃64;return

*/

String value() default "";

/**

* Excel从左往右排列位置

* &＃64;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 &＃61; LoggerFactory.getLogger(ExcelUtils.class);

private final static String EXCEL2003 &＃61; "xls";

private final static String EXCEL2007 &＃61; "xlsx";

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

String fileName &＃61; file.getOriginalFilename();

if (!fileName.matches("^.&＃43;\\.(?i)(xls)$") && !fileName.matches("^.&＃43;\\.(?i)(xlsx)$")) {

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

}

List dataList &＃61; new ArrayList<>();

Workbook workbook &＃61; null;

try {

InputStream is &＃61; file.getInputStream();

if (fileName.endsWith(EXCEL2007)) {

// FileInputStream is &＃61; new FileInputStream(new File(path));

workbook &＃61; new XSSFWorkbook(is);

}

if (fileName.endsWith(EXCEL2003)) {

// FileInputStream is &＃61; new FileInputStream(new File(path));

workbook &＃61; new HSSFWorkbook(is);

}

if (workbook !&＃61; null) {

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

Map> classMap &＃61; new HashMap<>();

List fields &＃61; Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());

fields.forEach(

field -> {

ExcelColumn annotation &＃61; field.getAnnotation(ExcelColumn.class);

if (annotation !&＃61; null) {

String value &＃61; 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 &＃61; new HashMap<>(16);

//默认读取第一个sheet

Sheet sheet &＃61; workbook.getSheetAt(0);

boolean firstRow &＃61; true;

for (int i &＃61; sheet.getFirstRowNum(); i <&＃61; sheet.getLastRowNum(); i&＃43;&＃43;) {

Row row &＃61; sheet.getRow(i);

//首行 提取注解

if (firstRow) {

for (int j &＃61; row.getFirstCellNum(); j <&＃61; row.getLastCellNum(); j&＃43;&＃43;) {

Cell cell &＃61; row.getCell(j);

String cellValue &＃61; getCellValue(cell);

if (classMap.containsKey(cellValue)) {

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

}

}

firstRow &＃61; false;

} else {

//忽略空白行

if (row &＃61;&＃61; null) {

continue;

}

try {

T t &＃61; cls.newInstance();

//判断是否为空白行

boolean allBlank &＃61; true;

for (int j &＃61; row.getFirstCellNum(); j <&＃61; row.getLastCellNum(); j&＃43;&＃43;) {

if (reflectionMap.containsKey(j)) {

Cell cell &＃61; row.getCell(j);

String cellValue &＃61; getCellValue(cell);

if (StringUtils.isNotBlank(cellValue)) {

allBlank &＃61; false;

}

List fieldList &＃61; 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 !&＃61; 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 &＃61; field.getType();

if (type &＃61;&＃61; null || type &＃61;&＃61; void.class || StringUtils.isBlank(value)) {

return;

}

if (type &＃61;&＃61; Object.class) {

field.set(t, value);

//数字类型

} else if (type.getSuperclass() &＃61;&＃61; null || type.getSuperclass() &＃61;&＃61; Number.class) {

if (type &＃61;&＃61; int.class || type &＃61;&＃61; Integer.class) {

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

} else if (type &＃61;&＃61; long.class || type &＃61;&＃61; Long.class) {

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

} else if (type &＃61;&＃61; byte.class || type &＃61;&＃61; Byte.class) {

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

} else if (type &＃61;&＃61; short.class || type &＃61;&＃61; Short.class) {

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

} else if (type &＃61;&＃61; double.class || type &＃61;&＃61; Double.class) {

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

} else if (type &＃61;&＃61; float.class || type &＃61;&＃61; Float.class) {

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

} else if (type &＃61;&＃61; char.class || type &＃61;&＃61; Character.class) {

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

} else if (type &＃61;&＃61; boolean.class) {

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

} else if (type &＃61;&＃61; BigDecimal.class) {

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

}

} else if (type &＃61;&＃61; Boolean.class) {

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

} else if (type &＃61;&＃61; Date.class) {

//

field.set(t, value);

} else if (type &＃61;&＃61; String.class) {

field.set(t, value);

} else if (type &＃61;&＃61; LocalDateTime.class) {

//String 转 LocalDateTime

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

LocalDateTime dt &＃61; LocalDateTime.parse(value, df);

field.set(t, dt);

} else {

Constructor> constructor &＃61; type.getConstructor(String.class);

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

}

}

private static String getCellValue(Cell cell) {

if (cell &＃61;&＃61; null) {

return "";

}

if (cell.getCellType() &＃61;&＃61; 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() &＃61;&＃61; Cell.CELL_TYPE_STRING) {

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

} else if (cell.getCellType() &＃61;&＃61; Cell.CELL_TYPE_FORMULA) {

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

} else if (cell.getCellType() &＃61;&＃61; Cell.CELL_TYPE_BLANK) {

return "";

} else if (cell.getCellType() &＃61;&＃61; Cell.CELL_TYPE_BOOLEAN) {

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

} else if (cell.getCellType() &＃61;&＃61; 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 &＃61; cls.getDeclaredFields();

List fieldList &＃61; Arrays.stream(fields)

.filter(field -> {

ExcelColumn annotation &＃61; field.getAnnotation(ExcelColumn.class);

if (annotation !&＃61; null && annotation.col() > 0) {

field.setAccessible(true);

return true;

}

return false;

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

int col &＃61; 0;

ExcelColumn annotation &＃61; field.getAnnotation(ExcelColumn.class);

if (annotation !&＃61; null) {

col &＃61; annotation.col();

}

return col;

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

Workbook wb &＃61; new XSSFWorkbook();

Sheet sheet &＃61; wb.createSheet("Sheet1");

AtomicInteger ai &＃61; new AtomicInteger();

{

Row row &＃61; sheet.createRow(ai.getAndIncrement());

AtomicInteger aj &＃61; new AtomicInteger();

//写入头部

fieldList.forEach(field -> {

ExcelColumn annotation &＃61; field.getAnnotation(ExcelColumn.class);

String columnName &＃61; "";

if (annotation !&＃61; null) {

columnName &＃61; annotation.value();

}

Cell cell &＃61; row.createCell(aj.getAndIncrement());

CellStyle cellStyle &＃61; wb.createCellStyle();

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

cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

Font font &＃61; 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 &＃61; sheet.createRow(ai.getAndIncrement());

AtomicInteger aj &＃61; new AtomicInteger();

fieldList.forEach(field -> {

Class> type &＃61; field.getType();

Object value &＃61; "";

try {

value &＃61; field.get(t);

} catch (Exception e) {

e.printStackTrace();

}

Cell cell &＃61; row1.createCell(aj.getAndIncrement());

if (value !&＃61; null) {

if (type &＃61;&＃61; Date.class) {

cell.setCellValue(value.toString());

} else if (type &＃61;&＃61; LocalDateTime.class){

DateTimeFormatter df &＃61; 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

* &＃64;param fileName

* &＃64;param wb

* &＃64;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&＃61;"&＃43;URLEncoder.encode(fileName, "utf-8"));

response.flushBuffer();

ServletOutputStream outputStream &＃61; response.getOutputStream();

wb.write(outputStream);

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 生成excel文件

* &＃64;param path 生成excel路径

* &＃64;param wb

*/

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

File file &＃61; 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;

&＃64;Data

public class Emp {

&＃64;ExcelColumn(value &＃61; "员工主键id", col &＃61; 1)

private Integer id;

&＃64;ExcelColumn(value &＃61; "员工编号",col &＃61; 2)

private String empNo;

&＃64;ExcelColumn(value &＃61; "员工名称",col &＃61; 3)

private String empName;

&＃64;ExcelColumn(value &＃61; "员工薪资",col &＃61; 4)

private BigDecimal salary;

&＃64;ExcelColumn(value &＃61; "员工职称",col &＃61; 5)

private String job;

&＃64;ExcelColumn(value &＃61; "入职时间",col &＃61; 6)

private LocalDateTime entryTime;

}

Controller层编写

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

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;

&＃64;CrossOrigin("*")

&＃64;RestController

&＃64;RequestMapping("/api/test")

public class ExcelController {

private static final Logger log &＃61; LogManager.getLogger(ExcelController.class);

/**

* Excel导出

* &＃64;param response

*/

&＃64;GetMapping("/exportExcel")

public void exportExcel(HttpServletRequest request,HttpServletResponse response){

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

List empList &＃61; handleRepositoryData();

long t1 &＃61; System.currentTimeMillis();

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

long t2 &＃61; System.currentTimeMillis();

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

}

/**

* Excel导入

* &＃64;param file

*/

&＃64;PostMapping("/readExcel")

public Result readExcel(&＃64;RequestBody MultipartFile file){

long t1 &＃61; System.currentTimeMillis();

log.info("上传的文件&＃xff1a;"&＃43;file);

List list &＃61; ExcelUtils.readExcel("", Emp.class, file);

long t2 &＃61; 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 &＃61; new ArrayList<>();

Emp emp;

for (int i &＃61; 1; i<&＃61; 10; i&＃43;&＃43;) {

emp &＃61; new Emp();

emp.setId(i);

emp.setEmpName("员工" &＃43; i);

emp.setEmpNo((1000 &＃43; i) &＃43; "");

emp.setJob("JY" &＃43; i);

emp.setSalary(new BigDecimal(i * 1000 &＃43; ""));

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

empList.add(emp);

}

return empList;

}

/**

* 前台页面的数据列表

* &＃64;return

*/

&＃64;GetMapping("/getList")

public Result getList(){

Result> result &＃61; new Result<>();

List empList &＃61; handleRepositoryData();

result.setData(empList);

return result;

}

}

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

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;

&＃64;Configuration

public class LocalDateTimeSerializerConfig {

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

private String pattern;

&＃64;Bean

public LocalDateTimeSerializer localDateTimeDeserializer() {

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

}

&＃64;Bean

public Jackson2ObjectMapperBuilderCustomizer jackson2ObjectMapperBuilderCustomizer() {

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

}

}

最后就是layui展示页面了&＃xff0c;是一个很简单上传下载的列表页面&＃xff0c;代码如下&＃xff1a;

Excel文件的导入导出测试

 导入Excel 

后台接口导出Excel

layui导出选中行的数据

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

layui.use([&＃39;table&＃39;, &＃39;layer&＃39;,&＃39;upload&＃39;], function(){

var table &＃61; layui.table

,$ &＃61; layui.$

,layer &＃61; layui.layer

,upload &＃61; layui.upload;

// layer.msg(&＃39;Hello World&＃39;);

var ins1 &＃61; table.render({

elem: &＃39;#test&＃39;

,url:&＃39;http://localhost:8080/api/test/getList&＃39;

,cols: [[

{type:&＃39;checkbox&＃39;}

,{field:&＃39;id&＃39;, title: &＃39;ID&＃39;, sort: true}

,{field:&＃39;empNo&＃39;,title: &＃39;员工编号&＃39;}

,{field:&＃39;empName&＃39;,title: &＃39;员工名称&＃39;}

,{field:&＃39;salary&＃39;, title: &＃39;薪资&＃39;}

,{field:&＃39;job&＃39;, title: &＃39;职称&＃39;} //minWidth&＃xff1a;局部定义当前单元格的最小宽度&＃xff0c;layui 2.2.1 新增

,{field:&＃39;entryTime&＃39;, title: &＃39;入职时间&＃39;}

]]

});

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

upload.render({

elem: &＃39;#test3&＃39;

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

,accept: &＃39;file&＃39; //普通文件

,done: function(res){

layer.msg(&＃39;上传成功&＃39;);

console.log(res);

}

});

//Excel后台导出

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

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

window.open(&＃39;http://localhost:8080/api/test/exportExcel?fileName&＃61;员工表导出测试.xlsx&＃39;)

})

//Excel通过layui导出

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

// console.log("123")

var checkStatus &＃61; table.checkStatus(&＃39;test&＃39;); //test 即为table绑定的id

//获取选中行的数据

var data &＃61; checkStatus.data;

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

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

})

});

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

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