1. 前提
先导入几个包
//poi excel操作
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
//常用工具类
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
2. ExcelUtils
包括excel的读写,浏览器下载excel文件
public class ExcelUtils {
private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
/**
* 读取EXCEL文件
*
* @param cls 封装对象类类型
* @param file 前端上传的MultipartFile对象
* @param <T> 封装对象类型
* @return T对象集合
*/
public static <T> List<T> readExcel(Class<T> cls, MultipartFile file) {
String fileName = file.getOriginalFilename(); //获取上传文件名
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
log.error("上传文件格式不正确");
}
List<T> 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(注解value可能相同,并注释多个字段)
Map<String, List<Field>> classMap = new HashMap<>();
//获取字段集合
List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
//遍历字段集合
fields.forEach(
field -> {
//过滤未加ExcelColumn的字段
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
//获取注解值
String annotationValue = annotation.value();
if (StringUtils.isBlank(annotationValue)) {
//过滤注解value为空的字段
return;//return起到的作用和continue是相同的语法
}
//若注解value未存在则添加映射
if (!classMap.containsKey(annotationValue)) {
classMap.put(annotationValue, new ArrayList<>());
}
//开启字段访问权限
field.setAccessible(true);
classMap.get(annotationValue).add(field);
}
}
);
//索引-->columns
Map<Integer, List<Field>> 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 columnName = getCellValue(cell);
if (classMap.containsKey(columnName)) {
//以列名为key获取对应的封装字段集合
reflectionMap.put(j, classMap.get(columnName));
}
}
firstRow = false;
} else {
//忽略空白行
if (row == null) {
continue;
}
try {
//反射创建封装对象
T t = cls.newInstance();
//是否为空白单元格
boolean cellBlank = 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)) {
cellBlank = false;
}
//通过列索引获取字段集合
List<Field> fieldList = reflectionMap.get(j);
//遍历字段集合,利用反射机制循环封装数据
fieldList.forEach(
x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(String.format("反射写入异常!字段名:%s 值:%s ", x.getName(), cellValue), e);
}
}
);
}
}
//若单元不全为空,则保存封装对象
if (!cellBlank) {
dataList.add(t);
} else {
log.warn(String.format("第%s行为空,已忽略!", i));
}
} catch (Exception e) {
log.error(String.format("解析第%s行异常!", i), e);
}
}
}
}
} catch (Exception e) {
log.error(String.format("解析excel文件异常!"), e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
log.error(String.format("解析excel文件异常!"), e);
}
}
}
return dataList ;
}
/**
* 反射写入属性
*
* @param t
* @param value
* @param field
* @param <T>
* @throws Exception
*/
private static <T> 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));
}
}
/**
* 获取单元格内容
*
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
return new BigDecimal(cell.getNumericCellValue()).toString();
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellTypeEnum() == CellType.BLANK) {
return "";
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellTypeEnum() == CellType.ERROR) {
return "ERROR";
} else {
return cell.toString().trim();
}
}
/**
* 数据写入excel
* @param wb
* @param dataList
* @param cls
* @param sheetName
* @param <T>
*/
public static <T> void writeExcel(Workbook wb , List<T> dataList, Class<T> cls , String sheetName) {
//获取字段集合
Field[] fields = cls.getDeclaredFields();
//过滤没有ExcelColumn注解的字段,并排序
List<Field> 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());
//新建工作页
Sheet sheet = wb.createSheet(sheetName);
//创建线程安全的AtomicInteger,默认为0
AtomicInteger ai = new AtomicInteger();
{
Row headRow = 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 = headRow.createCell(aj.getAndIncrement());
//设置格式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置字体
Font font = wb.createFont();
font.setBold(true);
//应用格式与字体
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
//写入内容
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.forEach(object -> {
Row contentRow = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class<?> type = field.getType();
Object value = "";
try {
value = field.get(object);
} catch (Exception e) {
e.printStackTrace();
}
Cell cell = contentRow.createCell(aj.getAndIncrement());
if (value != null) {
if (type == Date.class) {
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
}
});
});
}
}
/**
* 浏览器下载excel
*
* @param fileName
* @param wb
* @param response
*/
public 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();
}
}
}
3. 演示
此处演示数据写入excel,并导出
3.1 entity
此处创建了两个实体类,来展示一张excel表多sheet的情况
@Setter
@Getter
@ToString
/**
* 身份信息
*/
public class Identity {
@ExcelColumn(value = "姓名", col = 1)
private String name;
@ExcelColumn(value = "年龄", col = 2)
private int age;
}
/**
* 学生信息
*/
@Getter
@Setter
@ToString
public class Student {
@ExcelColumn(value = "学号", col = 1)
private Long studentId;
@ExcelColumn(value = "姓名", col = 2)
private String name;
@ExcelColumn(value = "性别", col = 3)
private String sex;
@ExcelColumn(value = "班级", col = 4)
private String grade;
}
3.2 controller
@RequestMapping(value = "/download" , method = RequestMethod.GET)
public void download(HttpServletResponse response) throws IOException {
//创建测试数据 学生信息
List<Student> studentList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student student = new Student();
student.setStudentId((long) (i + 1));
student.setName("测试"+i);
student.setSex("男"+i);
student.setGrade("大班"+i);
studentList.add(student);
}
//创建测试数据 身份信息
List<Identity> identities = new ArrayList<>();
for(int i = 0 ; i < 10 ; i++){
Identity identity = new Identity();
identity.setName("lin"+i);
identity.setAge(23+i);
identities.add(identity);
}
Workbook workbook = new XSSFWorkbook();
ExcelUtils.writeExcel(workbook, studentList, Student.class,"学生信息");
ExcelUtils.writeExcel(workbook, identities, Identity.class, "身份信息");
ExcelUtils.buildExcelDocument("test.xlsx",workbook,response);
}
3.3 postman模拟
填个路径,点击send and download 即可
4.备注
如果觉得这种方式麻烦,可以直接使用 free spire.xls for java , 以下是链接
free spire.xls for java 官网
不过要注意: