前言
1、新增依赖
<!--导出-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!--导入-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.22</version>
</dependency>
2、引入第三方Spire.Xls.jar包(excel转pdf)
① 下载地址:Spaire.Xls.jar
② 依赖添加
<!--表格转pdf-->
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>5.1.0</version>
<scope>system</scope>
<systemPath>${project.basedir}/src/main/resources/lib/Spire.Xls.jar</systemPath>
</dependency>
一、Excel导入
1、工具类方法
/**
* 解析excel中的数据
*
* @param file 文件
* @param rowNames excel中的列名称
* @param rowNamesEn class对象中的属性
* @param headerRowIndex 头部所在行数
* @param startRowIndex excel主数据的开始行数
* @param clazz 解析返回的对象类型
* @return
*/
public static <T> List<T> resolveFileToExcel(MultipartFile file, String[] rowNames, String[] rowNamesEn, int headerRowIndex, int startRowIndex, Class<T> clazz) {
//解析文件为workbook
XSSFWorkbook workbook;
try {
workbook = new XSSFWorkbook(file.getInputStream());
} catch (IOException e) {
e.printStackTrace();
throw new ErrorForWebException(ErrorCode.PARAM_EXCEPTION);
}
//获取excel中所有的sheet页数据
List<T> voList = new ArrayList<>();
try {
//解析excel中的头映射关系(即excel中的列映射到class的对象属性)
Map<String, String> map = new HashMap<>(32);
for (int i = 0; i < rowNamesEn.length; i++) {
map.put(rowNames[i], rowNamesEn[i]);
}
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
ExcelReader reader = ExcelUtil.getReader(file.getInputStream(), i);
reader.setHeaderAlias(map);
voList.addAll(reader.read(headerRowIndex, startRowIndex, clazz));
}
} catch (IOException e) {
e.printStackTrace();
throw new ErrorForWebException(ErrorCode.PARAM_EXCEPTION);
}
return voList;
}
2、方法测试
public static void main(String[] args) throws IOException {
String[] rowNames = {"姓名", "性别", "班级", "成绩", "学号"};
String[] rowNamesEn = {"name", "sex", "grade", "score", "sno"};
MultipartFile file = new MockMultipartFile("测试报告.xlsx", new FileInputStream(new File("d:/file/test.xlsx")));
List<Test> tests = resolveFileToExcel(file, rowNames, rowNamesEn, 0, 1, Test.class);
}
二、Excel导出
1、工具类方法
public static void export(List<Test> voList, HttpServletResponse response) {
Map<String, Object> map = new HashMap<>(2);
map.put("list", voList);
Workbook workbook = null;
try {
TemplateExportParams params = new TemplateExportParams("templates/测试模板.xlsx", true);
workbook = ExcelExportUtil.exportExcel(params, map);
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
2、模板内容
![](https://i-blog.csdnimg.cn/blog_migrate/47c6284b77f096f8731baccb1218d3c6.png)
三、下载Excel模板
1、工具类方法
public static void downloadTemplate(HttpServletResponse response) throws IOException {
try {
byte[] bytes = IoUtils.toByteArray(resourceLoader.getResource("classpath:templates/测试.xlsx").getInputStream());
Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bytes));
ByteArrayOutputStream bo = new ByteArrayOutputStream();
workbook.write(bo);
bo.flush();
byte[] array = bo.toByteArray();
response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String("测试.xlsx".getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
response.setCharacterEncoding("UTF-8");
response.getOutputStream().write(array);
response.setContentLength(array.length);
} catch (Exception e) {
log.error("downloadTemplate --> download excel template failed, errorMsg: {}", e.getMessage(), e);
throw new BusinessException("获取模板失败");
} finally {
if (response.getOutputStream() != null) {
response.getOutputStream().flush();
}
}
}
四、Excel转PDF
public static File translateExcelToPdf() {
File file = new File("d:/file/test.excel");
//转pdf
com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
wb.loadFromFile(file.getAbsolutePath());
String outputFilePath = file.getParentFile().getPath() + File.separator + file.getName().split(".xlsx")[0] + ".pdf";
wb.saveToFile(outputFilePath, FileFormat.PDF);
//删除本地缓存excel文件
boolean delete = file.delete();
return new File(outputFilePath);
}
五、网络路径读取Excel中的数据
public static List<String> resolveNetworkFileUrl(String url) {
List<String> voList = new ArrayList<>();
List<Map<Integer, String>> infoList = new ArrayList<>();
try {
//读取网络路径文件数据
EasyExcel.read(new URL(url).openConnection().getInputStream())
.headRowNumber(1)
.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invoke(Map<Integer, String> o, AnalysisContext analysisContext) {
infoList.add(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}).doReadAll();
} catch (IOException e) {
throw new ErrorForWebException(ErrorCode.AMS_SN_CODE_ERROR);
}
infoList.forEach(map -> voList.addAll(map.values()));
return voList;
}