springBoot 一表多sheet页导入导出excel
引言:
一表一sheet页的导入导出,有很多工具类可以直接使用。
而一表多sheet页的导入导出我没找到工具类,所以我就总结了一下,自己写了一个。
项目结构:
前提:
首先自己建一个最简单的web项目。
1.bom文件:
除最基础的外,需要添加如下。
<!--jeecg框架,代码里使用了jeecg的ExcelExportUtil工具类-->
<dependency>
<groupId>org.jeecgframework.boot</groupId>
<artifactId>jeecg-boot-base-core</artifactId>
<version>3.0</version>
</dependency>
<!--lombok表达式-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
2.导入:
工具:
import org.jeecgframework.poi.excel.ExcelExportUtil;
Excel注解:
用于实体类字段的标记。
如果@Excel使用的不对应,一个字段都翻译不出来。
import org.jeecgframework.poi.excel.annotation.Excel;
工具类:
1.ExcelUtils :
package com.example.labor.unit.comm;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author kyer
* @date 2022/03/17 15:38
**/
public class ExcelUtils {
/**
* 文件转类
* 要求:excelClasses中的class顺序与表中要转换的顺序相同。并且数量要大于sheets中sheet数据的数量。
* 也就是提供的要转换的class集合,要与excel表页对应,且不能少于sheet页数。多了无所谓
* @return
*/
public static List<Object> fileToObject(MultipartFile multipartFile,List<ExcelClass> excelClasses){
Iterator<Sheet> sheets = null;
List<Object> data = new ArrayList<>();
int classIndex = 0;
try{
sheets = ExcelUtils.fileToSheets(multipartFile);
while (sheets.hasNext()){
data.add(ExcelUtils.importExcel(sheets.next(),excelClasses.get(classIndex).getAClass()));
classIndex++;
}
}catch (Exception e){
e.printStackTrace();
}
return data;
}
/**文件转Sheets
*
* @param multipartFile
* @return
* @throws Exception
*/
public static Iterator<Sheet> fileToSheets(MultipartFile multipartFile) throws Exception {
File file = MultipartFileToFile.multipartFileToFile(multipartFile);
Workbook wb = null;
// 读取上传文件(excel)的名字,含后缀后
String fileName = file.getName();
// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
Iterator<Sheet> sheets = null;
try {
if (fileName.endsWith("xls")) {
wb = new HSSFWorkbook(new FileInputStream(file));
sheets = wb.iterator();
} else if (fileName.endsWith("xlsx")) {
wb = new XSSFWorkbook(new FileInputStream(file));
sheets = wb.iterator();
}
if (sheets == null) {
throw new Exception("excel中不含有sheet工作表");
}
return sheets;
}catch (Exception ex) {
throw ex;
} finally {
if (wb != null)
{
wb.close();
}
}
}
/**
* 导出多sheet表方法
* @param excelClassList,放名字,数据,类class
* @param excelType 表格类型 HSSF/XSSF
* @param response
*
*/
public static void downloadExcel(String excelName, List<ExcelClass> excelClassList,ExcelType excelType,HttpServletResponse response){
try {
//构建map集合
List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
for (ExcelClass excelClass:excelClassList){
Map<String,Object> map = new HashMap<String,Object>();
map.put("title",new ExportParams(excelClass.getName(),excelClass.getName(), excelType));
map.put("entity",excelClass.getAClass());
map.put("data",excelClass.getData());
mapList.add(map);
}
//调用ExcelExportUtil.exportExcel方法生成workbook
Workbook wb = ExcelExportUtil.exportExcel(mapList, excelType);
String fileId = String.valueOf(System.currentTimeMillis());
String fileName = "";
System.out.println("excelName:"+excelName+";excelType:"+excelType.name());
if ("HSSF".equals(excelType.name())){
fileName = excelName+ File.separator + fileId + ".xls";
}else if ("XSSF".equals(excelType.name())){
fileName = excelName+ File.separator + fileId + ".xlsx";
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
wb.write(response.getOutputStream());
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 导入 excel
* @param sheet
* @param pojoClass sheet要转换成的类的class
* @return
*
*/
public static Collection importExcel(Sheet sheet , Class pojoClass) {
Collection dist = new ArrayList<Object>();
try {
// 得到目标目标类的所有的字段列表
Field filed[] = pojoClass.getDeclaredFields();
// 将所有标有Annotation的字段,也就是允许导入数据的字段,放入到一个map中
Map<String, Method> fieldSetMap = new HashMap<String,Method>();
Map<String,Method> fieldSetConvertMap = new HashMap<String,Method>();
// 循环读取所有字段
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
// 得到单个字段上的Annotation
Excel excel = f.getAnnotation(Excel.class);
// 如果标识了Annotationd的话
if (excel != null) {
// 构造设置了Annotation的字段的Setter方法
String fieldname = f.getName();
String setMethodName = "set"
+ fieldname.substring(0, 1).toUpperCase()
+ fieldname.substring(1);
// 构造调用的method,
Method setMethod = pojoClass.getMethod(setMethodName,
new Class[] { f.getType() });
// 将这个method以Annotaion的名字为key来存入。
//对于重名将导致 覆盖 失败,对于此处的限制需要
fieldSetMap.put(excel.name(), setMethod);
}
}
// // 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
//强调题目
row.next();
// 得到第一行,也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 将标题的文字内容放入到一个map中。
Map titlemap = new HashMap();
// 从标题第一列开始
int i = 0;
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
titlemap.put(i, value);
i = i + 1;
}
//用来格式化日期的DateFormat
SimpleDateFormat sf;
while (row.hasNext()) {
// 标题下的第一行
Row rown = row.next();
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
int num = rown.getLastCellNum();
// 得到传入类的实例
Object tObject = pojoClass.newInstance();
int k = 0;
// 遍历一行的列 cellbody.hasNext()
while (k<rown.getLastCellNum()) {
//Cell cell = cellbody.next();
Cell cell = rown.getCell(k);
// 这里得到此列的对应的标题
String titleString = (String) titlemap.get(k);
// 如果这一列的标题和类中的某一列的Annotation相同,那么则调用此类的的set方法,进行设值
if (fieldSetMap.containsKey(titleString)) {
Method setMethod = (Method) fieldSetMap.get(titleString);
//得到setter方法的参数
Type[] ts = setMethod.getGenericParameterTypes();
//只要一个参数
String xclass = ts[0].toString();
//判断参数类型
if (fieldSetConvertMap.containsKey(titleString)) {
fieldSetConvertMap.get(titleString).invoke(tObject,
cell.getStringCellValue());
} else {
if (xclass.equals("class java.lang.String")) {
//先设置Cell的类型,然后就可以把纯数字作为String类型读进来了:
//cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell==null){
setMethod.invoke(tObject, "");
}else {
cell.setCellType(CellType.STRING);
setMethod.invoke(tObject, cell
.getStringCellValue());
}
}
else if (xclass.equals("class java.util.Date")) {
try{
if (cell==null||cell.getStringCellValue()==""){
setMethod.invoke(tObject, null);
}else {
//如果表格列是常规格式使用
setMethod.invoke(tObject, new SimpleDateFormat("yyyy-MM-dd").parse(cell.getStringCellValue()));
}
}catch(Exception e){
e.printStackTrace();
}
}
else if (xclass.equals("class java.lang.Boolean")) {
if (cell==null||cell.getStringCellValue()==""){
setMethod.invoke(tObject, new Boolean(false));
}else {
cell.setCellType(CellType.BOOLEAN);
setMethod.invoke(tObject, cell
.getBooleanCellValue());
}
}
else if (xclass.equals("class java.lang.Integer")) {
if (cell==null||cell.getStringCellValue()==""){
setMethod.invoke(tObject, new Integer(0));
}else {
cell.setCellType(CellType.STRING);
setMethod.invoke(tObject, new Integer(cell
.getStringCellValue()));
}
}else if(xclass. equals("class java.lang.Long"))
{
if (cell==null||cell.getStringCellValue()==""){
setMethod.invoke(tObject, 0);
}else {
cell.setCellType(CellType.STRING);
setMethod.invoke(tObject,new Long( cell.getStringCellValue()));
}
}else if(xclass.equals("class java.lang.Double"))
{
if (cell==null||cell.getStringCellValue()==""){
setMethod.invoke(tObject, new Double(0));
}else {
cell.setCellType(CellType.STRING);
setMethod.invoke(tObject,new Double( cell.getStringCellValue()));
}
}
}
}
// 下一列
k = k + 1;
}
dist.add(tObject);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return dist;
}
}
2.ExcelClass :
package com.example.labor.unit.comm;
import lombok.Data;
import java.util.List;
/**
* 导出工具数据类
* @author dawei
*/
@Data
public class ExcelClass {
private String name;
private List data;
private Class aClass;
public ExcelClass() {
}
public ExcelClass(Class aClass) {
this.aClass = aClass;
}
public ExcelClass(String name, List data, Class aClass) {
this.name = name;
this.data = data;
this.aClass = aClass;
}
}
3.MultipartFileToFile :
package com.example.labor.unit.comm;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
/**
* @author kyer
* @date 2022/03/17 14:26
**/
public class MultipartFileToFile {
/**
* MultipartFile 转 File
*
* @param file
* @throws Exception
*/
public static File multipartFileToFile(MultipartFile file) throws Exception {
File toFile = null;
if (file.equals("") || file.getSize() <= 0) {
file = null;
} else {
InputStream ins = null;
ins = file.getInputStream();
toFile = new File(file.getOriginalFilename());
inputStreamToFile(ins, toFile);
ins.close();
}
return toFile;
}
//获取流文件
private static void inputStreamToFile(InputStream ins, File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 删除本地临时文件
* @param file
*/
public static void deleteTempFile(File file) {
if (file != null) {
File del = new File(file.toURI());
del.delete();
}
}
}
entity实体类
1.StudentEntity:
package com.example.labor.entity;
import io.swagger.models.auth.In;
import lombok.Data;
import org.jeecgframework.poi.excel.annotation.Excel;
/**
* 学生实体类
* @author dawei
*/
@Data
public class StudentEntity {
@Excel(name = "学号")
private String id;
@Excel(name = "姓名")
private String name;
@Excel(name = "性别")
private String sex;
@Excel(name = "年龄")
private Integer age;
public StudentEntity() {
}
public StudentEntity(String id, String name, String sex, Integer age) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}
}
2.ClassEntity:
package com.example.labor.entity;
import lombok.Data;
import org.jeecgframework.poi.excel.annotation.Excel;
/**
* 选班实体类
* @author dawei
*/
@Data
public class ClassEntity {
@Excel(name = "班级id")
private String id;
@Excel(name = "班级名称")
private String name;
@Excel(name = "班级位置")
private String address;
@Excel(name = "学生学号")
private String studentId;
@Excel(name = "学生名称")
private String studentName;
public ClassEntity() {
}
public ClassEntity(String id, String name, String address, String studentId, String studentName) {
this.id = id;
this.name = name;
this.address = address;
this.studentId = studentId;
this.studentName = studentName;
}
}
测试类:
1.DownloadController
package com.example.labor.controller;
import com.example.labor.service.DemoService;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.NoSuchElementException;
/**
* 导入导出excel
* @author dawei
*/
@RestController
public class DownloadController {
@Autowired
DemoService demoService;
/**导入excel
*
* @param file
* @throws Exception
*/
@PostMapping("/importExcel")
public String importExcel(@RequestParam("file") MultipartFile file) throws Exception {
try {
String message = "";
message = demoService.importExcel(file);
return message;
} catch (NoSuchElementException e) {
return "请传入正确的表格";
} catch (SQLIntegrityConstraintViolationException e2) {
return "材料code重复";
}
}
/**导出excel模板
*
* @param response
*/
@GetMapping("/downloadExcelCandidate")
@ApiOperation(value = "导出模板")
public void downloadModel(HttpServletResponse response) {
demoService.downloadModel(response);
}
/**导出学生管理excel
*
* @param response
*
*/
@GetMapping("/downloadExcel")
@ApiOperation(value = "导出学生管理表(两个sheet页)")
public void downloadMaterials(HttpServletResponse response) {
demoService.downloadMaterials(response);
}
}
2.DemoService :
package com.example.labor.service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
/**
* @author daewi
*/
public interface DemoService {
/**导入excel包含三个sheet分别存入材料设备,单位换算,价格表
*
* @param multipartFile
* @return
* @throws Exception
*/
String importExcel(MultipartFile multipartFile) throws Exception;
/**下载模板
*
* @param response
*/
void downloadModel(HttpServletResponse response);
/**导出材料设备
*
* @param response
*/
void downloadMaterials(HttpServletResponse response);
}
3.DemoServiceImpl :
package com.example.labor.service.impl;
import com.example.labor.entity.ClassEntity;
import com.example.labor.entity.StudentEntity;
import com.example.labor.service.DemoService;
import com.example.labor.unit.comm.ExcelClass;
import com.example.labor.unit.comm.ExcelUtils;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
/**
* @author dawei
*/
@Service
public class DemoServiceImpl implements DemoService {
/**
* 导入excel包含两个sheet分别存入材料设备,学生表,对应班级表
*/
@Override
public String importExcel(MultipartFile multipartFile) throws Exception{
//确定要转换成的类
List<ExcelClass> excelClasses = new ArrayList<>();
excelClasses.add(new ExcelClass(StudentEntity.class));
excelClasses.add(new ExcelClass(ClassEntity.class));
//转换
List<Object> data = ExcelUtils.fileToObject(multipartFile,excelClasses);
List<StudentEntity> studentEntities = (List<StudentEntity>)data.get(0);
List<ClassEntity> classEntities = (List<ClassEntity>)data.get(1);
//输出数据
System.out.println("classEntities数据:");
studentEntities.stream().
map(item->"学号:"+item.getId()+";姓名:"+item.getName()+
";性别:"+item.getSex()+";年龄:"+item.getAge()).
forEach(System.out::println);
System.out.println();
System.out.println("classEntities数据:");
classEntities.stream().
map(item-> "班级号:"+item.getId()+";班级名称:"+item.getName()+
";班级地址:"+item.getAddress()+";学号:"+item.getStudentId()+
";学生名称:"+item.getStudentName()).
forEach(System.out::println);
return "成功";
}
/**
* 下载导入模板
* @param response
*/
@Override
public void downloadModel(HttpServletResponse response){
//制造数据
List<StudentEntity> StudentEntity = new LinkedList<>();
StudentEntity.add(new StudentEntity("1","张三","男",12));
List<ClassEntity> ClassEntity = new LinkedList<>();
ClassEntity.add(new ClassEntity("1","一班","七号楼,304","1","张三"));
List<ExcelClass> excelClassList = new LinkedList<>();
excelClassList.add(new ExcelClass("学生页",StudentEntity,StudentEntity.class));
excelClassList.add(new ExcelClass("选班页",ClassEntity,ClassEntity.class));
//输出表格
ExcelUtils.downloadExcel("学生管理导入模板",excelClassList,ExcelType.XSSF,response);
}
/**导出材料设备为excel
*
* @param response
*
*/
@Override
public void downloadMaterials(HttpServletResponse response){
//制造数据
List<StudentEntity> studentEntities = new ArrayList<>();
studentEntities.add(new StudentEntity("1","张三","男",12));
studentEntities.add(new StudentEntity("2","里斯","女",14));
studentEntities.add(new StudentEntity("3","王五","男",52));
studentEntities.add(new StudentEntity("4","六六","男",19));
List<ClassEntity> classEntities = new ArrayList<>();
classEntities.add(new ClassEntity("1","一班","七号楼,304","1","张三"));
classEntities.add(new ClassEntity("1","一班","七号楼,304","2","里斯"));
classEntities.add(new ClassEntity("2","二班","七号楼,305","3","王五"));
classEntities.add(new ClassEntity("2","二班","七号楼,305","4","六六"));
List<ExcelClass> excelClassList = new LinkedList<>();
excelClassList.add(new ExcelClass("学生页",studentEntities,StudentEntity.class));
excelClassList.add(new ExcelClass("班级页",classEntities,ClassEntity.class));
//输出表格
ExcelUtils.downloadExcel("学生管理表",excelClassList, ExcelType.XSSF,response);
}
}