前言
由于在最近的项目中使用Excel导入和导出较为频繁,以此篇博客作为记录,方便日后查阅。本文前台页面将使用layui,来演示对Excel文件导入和导出的效果。本文代码已上传至我的gitHub,欢迎访问,地址:https://github.com/rename123/excel-demo
准备工作
1. 添加操作Excel的有关依赖,如下:
org.apache.poipoi3.13
org.apache.poipoi-ooxml3.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 field