- 前言
- 添加依赖
- 配置导出字段的类型
- 查出数据变成表格
- 上传表格变成数据
- 配合使用OSS
- 一行多列
- 附加 -- 增加工具类
基本开发莫过于配置 mysql以及mybatis 进行一系列操作的增删改查，在此基础上面有时候又需要一些后台所使用的导入导出。目前我经常使用的就是此款Easy POI 相比较 阿帕奇的poi 更加灵活且封装较好。
Easy POI 官网
视频演示
只介绍常规使用，关于高级使用，请查阅官网
文章只对于springBoot 如果项目里面使用Spring 查询官网任然可以装配。
添加依赖配置导出字段的类型cn.afterturn easypoi-spring-boot-starter 3.3.0
@Data
public class Company {
@Excel(name = "公司名称",width =20) // 标注为宽度，以及头部名称
private String name;
@Excel(name = "创建日期", width = 30, exportFormat = "yyyy/MM/dd") // 标注时间格式
private String create_time;
@Excel(name = "公司logo",width =20,type = 2,imageType = 1)
private String logo;
@Excel(name = "公司介绍",width =100)
private String dec;
查出数据变成表格
// 导出抬头 文件类型
ExportParams exportParams = new ExportParams("抬头名称", "sheel名称", ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Company .class, dataList);
// 拿到comany集合进行导出变为WorkBook 然后写出去就可以了
workbook.write(new FileOutputStream(new File("文件地址路径")));
上传表格变成数据
importParams params = new importParams();
params.setHeadRows(1);
// 取第二页基础配置
params.setStartSheetIndex(i);
// 导出Exel 解析 入参 传过来的流 解析的类
List companyList =
ExcelimportUtil.importExcel(fileInputStream, Company .class, params);
配合使用OSS
一般来说导入导出是在项目里面的，供下载上传，那么很少写到本地
添加依赖
com.aliyun.oss aliyun-sdk-oss 3.10.2
上传oss 返回下载连接
ExportParams exportParams = new ExportParams("抬头", "sheel名称", ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Company.class, dataList);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
try {
workbook.write(byteArrayOutputStream);
} catch (IOException e) {
e.printStackTrace();
}
byte[] bytes = byteArrayOutputStream.toByteArray();
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
String fileName = System.currentTimeMillis() + ".xlsx";
OssUtile.uploadFileToOSS(fileName, byteArrayInputStream);
String url = OssUtile.generalUrl("oss桶/" + fileName).toString();
return url
Oss临时访问配置 generalUrl 上传到 oss 方法 uploadFileToOSS 具体OSS配置可以参考阿里云官网。
文中同时写了一个下载的方法，用于项目中需要对上传的 oss 文件下载进行解析处理
public static URL generalUrl(String ossFilePath) {
OSSClient ossClient = new OSSClient(endpoint, accessId, accessKey);
Date expiration = new Date(System.currentTimeMillis() + 3600 * 1000 * 24); // 一天内有效
URL url = ossClient.generatePresignedUrl(bucketName, ossFilePath, expiration);
ossClient.shutdown();
return url;
}
public static void uploadFileToOSS(String ossFileFullName, String localFile) {
OSSClient ossClient = new OSSClient(endpoint, accessId, accessKey);
String ossFileFull = "tmjl-user-voice/" + ossFileFullName;
try {
File file = new File(localFile);
ossClient.putObject(bucketName, ossFileFull, file);
file.delete();
} catch (Exception e) {
} finally {
ossClient.shutdown();
}
}
public static void downloadOSSToFile(String ossFileFullName, String localFile) {
OSSClient ossClient = new OSSClient(endpoint, accessId, accessKey);
String ossFileFull = "tmjl-user-voice/" + ossFileFullName;
try {
ossClient.getObject(new GetObjectRequest(bucketName, ossFileFull), new File(localFile));
} catch (Exception e) {
e.printStackTrace();
} finally {
ossClient.shutdown();
}
}
一行多列
如下图所示
@ExcelTarget("courseEntity")
public class CourseEntity implements java.io.Serializable {
private String id;
@Excel(name = "课程名称", orderNum = "1", width = 25)
private String name;
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@ExcelCollection(name = "学生", orderNum = "4")
private List students;
}
@ExcelTarget("teacherEntity")
public class TeacherEntity implements java.io.Serializable {
private String id;
@Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isimportField = "true_major,true_absent")
private String name;
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("2412312", "测试", "测试"),
CourseEntity.class, list);
附加 – 增加工具类
public class ExcelUtils {
public static void exportExcel(List> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
private static void defaultExport(List> list, String fileName, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
private static void defaultExport(List list, Class pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
public static void exportExcel(List list, Class pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
public static List importExcel(MultipartFile file, Class pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
importParams params = new importParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelimportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
public static List importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
importParams params = new importParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
try {
return ExcelimportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
