工具類

package com.example.demo.util.excel;import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelReader; import com.alibaba.excel.read.metadata.ReadSheet; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import lombok.extern.slf4j.Slf4j; import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.io.UnsupportedEncodingException; import java.nio.charset.StandardCharsets; import java.util.*;/*** 導(dǎo)入導(dǎo)出excel*/ @Slf4j public class EasyExcelUtil {/*** 根據(jù)自定義對(duì)象讀取excel* @param <T> 實(shí)體類泛型* @param excel 文件流* @param clazz 實(shí)體類* @return 導(dǎo)入的數(shù)據(jù)集合*/public static <T> ArrayList<T> readerExcel(MultipartFile excel, Class<T> clazz) throws Exception {if (excel.isEmpty()) {throw new Exception("請(qǐng)選擇excel文件");}String fileName = excel.getOriginalFilename();log.info("Excel文件解析：文件名 = " + fileName);if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {throw new Exception("文件格式錯(cuò)誤");}try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();EasyExcel.read(fileStream, clazz, excelListener).autoTrim(true).sheet().doRead();return excelListener.getDatas();} catch (Exception e) {log.error("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性", e);throw new Exception("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性");}}/*** 根據(jù)自定義對(duì)象讀取excel并指定讀取起始行* @param <T> 實(shí)體類泛型* @param excel 文件流* @param rowNum 從第幾行開始讀* @param clazz 實(shí)體類* @return 導(dǎo)入的數(shù)據(jù)集合*/public static <T> ArrayList<T> readExcelWithRowNum(MultipartFile excel, Integer rowNum, Class<T> clazz) throws Exception {if (excel.isEmpty()) {throw new Exception("請(qǐng)選擇excel文件");}String fileName = excel.getOriginalFilename();log.info("Excel文件解析：文件名 = " + fileName);if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {throw new Exception("文件格式錯(cuò)誤");}try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();EasyExcel.read(fileStream, clazz, excelListener).autoTrim(true).sheet().headRowNumber(rowNum).doRead();return excelListener.getDatas();} catch (Exception e) {log.error("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性", e);throw new Exception("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性");}}/*** 根據(jù)自定義對(duì)象讀取excel并指定sheet列表讀取* sheet列表數(shù)據(jù)格式必須一樣* @param excel excel文件* @param sheetNums sheet集合* @param clazz 實(shí)體類* @param <T> 實(shí)體類泛型* @return 導(dǎo)入的數(shù)據(jù)集合*/public static <T> ArrayList<T> readExcelWithSheets(MultipartFile excel, List<Integer> sheetNums, Class<T> clazz) throws Exception {if (excel.isEmpty()) {throw new Exception("請(qǐng)選擇excel文件");}String fileName = excel.getOriginalFilename();log.info("Excel文件解析：文件名 = " + fileName);if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {throw new Exception("文件格式錯(cuò)誤");}try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();ExcelReader excelReader = EasyExcel.read(fileStream,clazz,excelListener).autoTrim(true).build();List<ReadSheet> readSheets = new ArrayList<>();for (Integer sheetNum : sheetNums) {ReadSheet readSheet = EasyExcel.readSheet(sheetNum).build();readSheets.add(readSheet);}excelReader.read(readSheets);excelReader.finish();return excelListener.getDatas();} catch (Exception e) {log.error("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性", e);throw new Exception("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性");}}/*** 根據(jù)自定義對(duì)象讀取excel并自定義sheets讀取* sheet列表數(shù)據(jù)格式必須一樣* @param excel excel文件* @param sheets 自定義sheet* @param clazz 實(shí)體類* @param <T> 實(shí)體類泛型* @return 導(dǎo)入的數(shù)據(jù)集合*/public static <T> ArrayList<T> readExcelWithCustomSheet(MultipartFile excel, List<ReadSheet> sheets, Class<T> clazz) throws Exception {if (excel.isEmpty()) {throw new Exception("請(qǐng)選擇excel文件");}String fileName = excel.getOriginalFilename();log.info("Excel文件解析：文件名 = " + fileName);if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {throw new Exception("文件格式錯(cuò)誤");}if (sheets == null){throw new Exception("請(qǐng)指定sheet");}try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();ExcelReader excelReader = EasyExcel.read(fileStream,clazz,excelListener).autoTrim(true).build();excelReader.read(sheets);excelReader.finish();return excelListener.getDatas();} catch (Exception e) {log.error("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性", e);throw new Exception("導(dǎo)入失敗, 請(qǐng)檢查導(dǎo)入數(shù)據(jù)的準(zhǔn)確性");}}/*** 導(dǎo)出excel* @param fileName 導(dǎo)出的文件名* @param response 響應(yīng)* @param request 請(qǐng)求* @param lists 導(dǎo)出的數(shù)據(jù)* @param <T> 導(dǎo)出的實(shí)體類泛型*/public static <T> void writerExcel(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz) {String sheetName = fileName;try {String userAgent = request.getHeader("User-Agent");if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {// 針對(duì)IE或者以IE為內(nèi)核的瀏覽器：fileName = java.net.URLEncoder.encode(fileName, "UTF-8");} else {// 非IE瀏覽器的處理:fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);}response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));response.setHeader("Cache-Control", "no-cache");response.setHeader("Pragma", "no-cache");response.setDateHeader("Expires", -1);} catch (UnsupportedEncodingException e1) {log.error("導(dǎo)出excel未知編碼異常", e1);}try {EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(lists);} catch (IOException e) {log.error("導(dǎo)出excel文件異常", e);}}/*** 導(dǎo)出帶模板文件的excel* @param fileName 導(dǎo)出文件名* @param response 響應(yīng)* @param request 請(qǐng)求* @param lists 導(dǎo)出的數(shù)據(jù)* @param <T> 導(dǎo)出的實(shí)體類泛型* @param clazz 導(dǎo)出的實(shí)體類* @param templatePath 模版文件路徑*/public static <T> void writerExcelByTemplate(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz, String templatePath) {String sheetName = fileName;try {String userAgent = request.getHeader("User-Agent");if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {fileName = java.net.URLEncoder.encode(fileName, "UTF-8");} else {fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);}response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));response.setHeader("Cache-Control", "no-cache");response.setHeader("Pragma", "no-cache");response.setDateHeader("Expires", -1);} catch (UnsupportedEncodingException e1) {log.error("導(dǎo)出excel未知編碼異常", e1);}try {EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).withTemplate(templatePath).sheet(0, sheetName).doWrite(lists);} catch (IOException e) {log.error("導(dǎo)出excel文件異常", e);}}/*** 導(dǎo)出模板信息（自定義表頭）* @param fileName 導(dǎo)出文件名* @param response 響應(yīng)* @param request 請(qǐng)求* @param heads 表頭信息*/public static void writerExcelWithHead(String fileName, HttpServletResponse response, HttpServletRequest request, List<String> heads) {String sheetName = fileName;try {String userAgent = request.getHeader("User-Agent");if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {fileName = java.net.URLEncoder.encode(fileName, "UTF-8");} else {fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);}response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));response.setHeader("Cache-Control", "no-cache");response.setHeader("Pragma", "no-cache");response.setDateHeader("Expires", -1);} catch (UnsupportedEncodingException e1) {log.error("導(dǎo)出excel未知編碼異常", e1);}try {EasyExcel.write(response.getOutputStream(), null).head(head(heads)).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(new ArrayList());} catch (IOException e) {log.error("導(dǎo)出excel文件異常", e);}}/*** 獲取表頭* @param strings 表頭信息* @return 表頭*/private static List<List<String>> head(List<String> strings) {List<List<String>> list = new ArrayList<>();for (String string : strings) {List<String> head = new ArrayList<>();head.add(string);list.add(head);}return list;}}

測(cè)試說明

通用

使用POSTMAN測(cè)試導(dǎo)入導(dǎo)出
導(dǎo)出

controller

@RequestMapping(value = "/export", method = RequestMethod.GET)public void export(HttpServletResponse response, HttpServletRequest request){UserDTO userDTO1 = new UserDTO(1L,"name1",20,"m",new Date(),false);UserDTO userDTO2 = new UserDTO(2L,"name2",20,"f",new Date(),false);List<UserDTO> list = new ArrayList<>();list.add(userDTO1);list.add(userDTO2);EasyExcelUtil.writerExcel("11",response,request,list,UserDTO.class);}

導(dǎo)入

參考

總結(jié)

以上是生活随笔為你收集整理的使用阿里开源的EasyExcel导入导出EXCEL——工具类的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。