springboot2.X手冊：Easypoi導(dǎo)出excel，最新版的手感香不香？
“POI 工具類,Excel的快速導(dǎo)入導(dǎo)出,Excel模板導(dǎo)出,Word模板導(dǎo)出,可以僅僅5行代碼就可以完成Excel的導(dǎo)入導(dǎo)出,修改導(dǎo)出格式簡單粗暴,快速有效,easypoi值得你嘗試
”
目前來說，Easypoi確實(shí)方便，官網(wǎng)也提供了三種不同的版本，它在開源中國還，還是非常出名的，用的人非常多，也是對他的一個認(rèn)可。
小編目前的項(xiàng)目，也是用這個來做，今天我們來做個excel的導(dǎo)入導(dǎo)出例子，看看怎么使用？
包體引入
目前官方提供最新版本是4.2.0，但是我在使用過程中，總是報(bào)錯，時(shí)間關(guān)系就沒怎么去查找，有興趣的同學(xué)可以呀研究一下，類找不到，這個是apache的一個類，估計(jì)是新版本需要引入別的包，沒去仔細(xì)追究。
java.lang.NoClassDefFoundError:?org/apache/poi/xssf/usermodel/XSSFWorkbook
????????
????????????org.springframework.boot
????????????spring-boot-starter-web
????????
????????
????????
????????????cn.afterturn
????????????easypoi-spring-boot-starter
????????????4.1.0
????????
????????
????????
????????????commons-fileupload
????????????commons-fileupload
????????????1.4
????????
編寫導(dǎo)入導(dǎo)出工具類
/**
?*?Excel枚舉類型
?*?@author：溪云閣
?*?@date：2020年5月29日
?*/
public?enum?ExcelTypeEnum?{
????XLS("xls"),?XLSX("xlsx");
????private?String?value;
????private?ExcelTypeEnum(String?value)?{
????????this.value?=?value;
????}
????public?String?getValue()?{
????????return?value;
????}
????public?void?setValue(String?value)?{
????????this.value?=?value;
????}
}
/**
?*?Excel導(dǎo)出工具類
?*?@author：溪云閣
?*?@date：2020年5月29日
?*/
@Component
public?class?ExcelExportUtils?{
????@Autowired
????private?HttpServletResponse?response;
????/**
?????*?導(dǎo)出excel
?????*?@author?溪云閣
?????*?@param?list?泛型數(shù)據(jù)
?????*?@param?title?標(biāo)題
?????*?@param?sheetName?sheet的名稱
?????*?@param?pojoClass?需要導(dǎo)出的對象
?????*?@param?fileName?文件名稱
?????*?@param?isCreateHeader?是否創(chuàng)建表頭
?????*?@throws?IOException?void
?????*/
????public?void?exportExcel(List?list,?Class?pojoClass,?String?title,?String?sheetName,?String?fileName,
????????????boolean?isCreateHeader)?throws?IOException?{
????????final?ExportParams?exportParams?=?new?ExportParams(title,?sheetName,?ExcelType.XSSF);
????????exportParams.setCreateHeadRows(isCreateHeader);
????????baseExport(list,?pojoClass,?fileName,?exportParams);
????}
????/**
?????*?導(dǎo)出excel
?????*?@author?溪云閣
?????*?@param?list?泛型數(shù)據(jù)
?????*?@param?title?標(biāo)題
?????*?@param?sheetName?sheet的名稱
?????*?@param?pojoClass?需要導(dǎo)出的對象
?????*?@param?fileName?文件名稱
?????*?@param?response
?????*?@throws?IOException?void
?????*/
????public?void?exportExcel(List?list,?Class?pojoClass,?String?title,?String?sheetName,?String?fileName)
????????????throws?IOException?{
????????baseExport(list,?pojoClass,?fileName,?new?ExportParams(title,?sheetName,?ExcelType.XSSF));
????}
????/**
?????*?導(dǎo)出excel
?????*?@author?溪云閣
?????*?@param?list?泛型數(shù)據(jù)
?????*?@param?pojoClass?需要導(dǎo)出的對象
?????*?@param?fileName?文件名稱
?????*?@param?exportParams?文件書香
?????*?@param?response
?????*?@throws?IOException?void
?????*/
????public?void?exportExcel(List?list,?Class?pojoClass,?String?fileName,?ExportParams?exportParams)
????????????throws?IOException?{
????????baseExport(list,?pojoClass,?fileName,?exportParams);
????}
????/**
?????*?多個sheet導(dǎo)出
?????*?@author?溪云閣
?????*?@param?list
?????*?@param?fileName
?????*?@throws?IOException?void
?????*/
????public?void?exportExcel(List>?list,?String?fileName)?throws?IOException?{
????????baseExport(list,?fileName);
????}
????/**
?????*?最基礎(chǔ)的對象導(dǎo)出
?????*?@author?溪云閣
?????*?@param?list?數(shù)據(jù)列表
?????*?@param?pojoClass?導(dǎo)出對象
?????*?@param?fileName?文件名稱
?????*?@param?exportParams?導(dǎo)出文件屬性
?????*?@throws?IOException?void
?????*/
????private?void?baseExport(List?list,?Class?pojoClass,?String?fileName,?ExportParams?exportParams)
????????????throws?IOException?{
????????final?Workbook?workbook?=?ExcelExportUtil.exportExcel(exportParams,?pojoClass,?list);
????????downLoadExcel(fileName,?workbook);
????}
????/**
?????*?最基礎(chǔ)的多sheet導(dǎo)出
?????*?@author?溪云閣
?????*?@param?list?多個不同數(shù)據(jù)對象的列表
?????*?@param?fileName?文件名稱
?????*?@throws?IOException?void
?????*/
????private?void?baseExport(List>?list,?String?fileName)?throws?IOException?{
????????final?Workbook?workbook?=?ExcelExportUtil.exportExcel(list,?ExcelType.HSSF);
????????downLoadExcel(fileName,?workbook);
????}
????/**
?????*?文件下載
?????*?@author?溪云閣
?????*?@param?fileName?文件名稱
?????*?@param?workbook?exce對象
?????*?@throws?IOException?void
?????*/
????private?void?downLoadExcel(String?fileName,?Workbook?workbook)?throws?IOException?{
????????ServletOutputStream?output?=?null;
????????try?{
????????????final?String?downloadName?=?URLEncoder.encode(fileName?+?"."?+?ExcelTypeEnum.XLSX.getValue(),?"UTF-8");
????????????response.setCharacterEncoding("UTF-8");
????????????response.setHeader("content-Type",?"application/vnd.ms-excel");
????????????response.setHeader("Content-Disposition",?"attachment;filename="?+?downloadName);
????????????output?=?response.getOutputStream();
????????????workbook.write(output);
????????}
????????catch?(final?Exception?e)?{
????????????throw?new?IOException(e.getMessage());
????????}
????????finally?{
????????????if?(output?!=?null)?{
????????????????output.flush();
????????????????output.close();
????????????}
????????}
????}
}
/**
?*?Excel導(dǎo)入工具類
?*?@author：溪云閣
?*?@date：2020年5月29日
?*/
@Component
public?class?ExcelImportUtils?{
????/**
?????*?從指定位置獲取文件后進(jìn)行導(dǎo)入
?????*?@author?溪云閣
?????*?@param?filePath?文件路徑
?????*?@param?titleRows?表格標(biāo)題行數(shù),默認(rèn)0
?????*?@param?headerRows?表頭行數(shù),默認(rèn)1
?????*?@param?pojoClass?上傳后需要轉(zhuǎn)化的對象
?????*?@return
?????*?@throws?IOException?List
?????*/
????public??List?importExcel(String?filePath,?Integer?titleRows,?Integer?headerRows,?Class?pojoClass)
????????????throws?Exception?{
????????if?(Strings.isEmpty(filePath))?{
????????????return?null;
????????}?else?{
????????????final?ImportParams?params?=?new?ImportParams();
????????????//?表格標(biāo)題行數(shù),默認(rèn)0
????????????params.setTitleRows(titleRows);
????????????//?表頭行數(shù),默認(rèn)1
????????????params.setHeadRows(headerRows);
????????????//?是否需要保存上傳的Excel
????????????params.setNeedSave(true);
????????????//?保存上傳的Excel目錄
????????????params.setSaveUrl("/excel/");
????????????return?ExcelImportUtil.importExcel(new?File(filePath),?pojoClass,?params);
????????}
????}
????/**
?????*?上傳文件導(dǎo)入
?????*?@author?溪云閣
?????*?@param?file
?????*?@param?titleRows?標(biāo)題行
?????*?@param?headerRows?表頭行
?????*?@param?needVerfiy?是否檢驗(yàn)excel內(nèi)容
?????*?@param?pojoClass?導(dǎo)入的對象
?????*?@return
?????*?@throws?Exception?List
?????*/
????public??List?importExcel(MultipartFile?file,?Integer?titleRows,?Integer?headerRows,?boolean?needVerfiy,
????????????Class?pojoClass)?throws?Exception?{
????????if?(file?==?null)?{
????????????return?null;
????????}?else?{
????????????return?baseImport(file.getInputStream(),?titleRows,?headerRows,?needVerfiy,?pojoClass);
????????}
????}
????/**
?????*?最基礎(chǔ)導(dǎo)入
?????*?@author?溪云閣
?????*?@param?inputStream
?????*?@param?titleRows?表格標(biāo)題行數(shù),默認(rèn)0
?????*?@param?headerRows?表頭行數(shù),默認(rèn)1
?????*?@param?needVerify?是否需要檢測excel
?????*?@param?pojoClass?導(dǎo)入的對象
?????*?@return
?????*?@throws?IOException?List
?????*/
????private??List?baseImport(InputStream?inputStream,?Integer?titleRows,?Integer?headerRows,
????????????boolean?needVerify,?Class?pojoClass)?throws?Exception?{
????????if?(inputStream?==?null)?{
????????????return?null;
????????}?else?{
????????????final?ImportParams?params?=?new?ImportParams();
????????????params.setTitleRows(titleRows);
????????????params.setHeadRows(headerRows);
????????????params.setSaveUrl("/excel/");
????????????params.setNeedSave(true);
????????????params.setNeedVerify(needVerify);
????????????return?ExcelImportUtil.importExcel(inputStream,?pojoClass,?params);
????????}
????}
}
編寫導(dǎo)入導(dǎo)出對象
這里，為了覆蓋更全一點(diǎn)，我分別用了不同的類型來做實(shí)驗(yàn)，數(shù)字類型采用NumberFormat進(jìn)行格式化操作。
/**
?*?用戶信息
?*?@author：溪云閣
?*?@date：2020年5月29日
?*/
public?class?User?implements?Serializable?{
????//?數(shù)字格式化
????private?NumberFormat?nf?=?NumberFormat.getNumberInstance();
????private?static?final?long?serialVersionUID?=?1L;
????@Excel(name?=?"用戶id",?orderNum?=?"0",?width?=?15)
????@Setter
????@Getter
????private?long?userId;
????@Excel(name?=?"性別",?orderNum?=?"1",?width?=?15,?replace?=?{?"男_1",?"女_2"?},?suffix?=?"孩")
????@Setter
????@Getter
????private?int?sex;
????@Excel(name?=?"金錢",?orderNum?=?"2",?width?=?15)
????@Setter
????private?double?money;
????public?String?getMoney()?{
????????return?nf.format(money);
????}
????@Excel(name?=?"用戶信息",?orderNum?=?"3",?width?=?15)
????@Setter
????@Getter
????private?String?userName;
????@Excel(name?=?"價(jià)格",?orderNum?=?"4",?width?=?15)
????@Setter
????@Getter
????private?float?price;
????@Excel(name?=?"時(shí)間",?orderNum?=?"5",?width?=?15,?format?=?"yyyy-MM-dd")
????@Setter
????@Getter
????private?Date?now;
}
編寫測試方法
/**
?*?excel導(dǎo)入導(dǎo)出
?*?@author：溪云閣
?*?@date：2020年5月29日
?*/
@Api(tags?=?{?"APP服務(wù)：數(shù)據(jù)接口"?})
@RestController
@RequestMapping("view/ie")
public?class?ImportExportController?{
????@Autowired
????private?ExcelExportUtils?excelExportUtils;
????@Autowired
????private?ExcelImportUtils?excelImportUtils;
????/**
?????*?導(dǎo)出用戶信息
?????*?@author?溪云閣?void
?????*/
????@ApiOperation(value?=?"導(dǎo)出excel")
????@GetMapping(value?=?"/exportExcel")
????public?void?exportExcel()?throws?Exception?{
????????final?List?userList?=?new?ArrayList<>();
????????for?(int?i?=?0;?i?????????????final?User?user?=?new?User();
????????????user.setUserId(i);
????????????user.setSex(1);
????????????user.setMoney(12332123?+?i);
????????????user.setUserName("小明"?+?i);
????????????user.setPrice(23.1f?+?i);
????????????user.setNow(new?Date());
????????????userList.add(user);
????????}
????????excelExportUtils.exportExcel(userList,?User.class,?"用戶信息",?"員工信息的sheet",?"用戶信息表");
????}
????/**
?????*?導(dǎo)入用戶信息
?????*?@author?溪云閣
?????*?@param?file
?????*?@return
?????*?@throws?IOException?Object
?????*/
????@ApiOperation(value?=?"導(dǎo)入excel")
????@GetMapping(value?=?"/importExcel")
????public?ResponseMsg>?importExcel(@RequestParam("file")?MultipartFile?file)?throws?Exception?{
????????final?List?userList?=?excelImportUtils.importExcel(file,?1,?1,?false,?User.class);
????????return?MsgUtils.buildSuccessMsg(userList);
????}
}
導(dǎo)出結(jié)果
在導(dǎo)出中，直接在瀏覽器輸入地址接口，結(jié)果如截圖所示
其中，金錢，時(shí)間上，我們分別進(jìn)行了格式化
導(dǎo)入結(jié)果
把剛剛導(dǎo)出來的文件，直接導(dǎo)入進(jìn)去，這里采用postMan進(jìn)行操作，其中要注意的點(diǎn)，我已經(jīng)用紅色的圈圈標(biāo)出來。
從實(shí)驗(yàn)結(jié)果上看，已經(jīng)可以導(dǎo)入進(jìn)去，并且把數(shù)據(jù)返回來
問題
在導(dǎo)入進(jìn)去的構(gòu)成中，這里留下一個問題給同學(xué)自行解決，導(dǎo)入的金額是0，可自行研究解決
作者溪云閣，專注編程教學(xué)，架構(gòu)，JAVA，Python，微服務(wù)，機(jī)器學(xué)習(xí)等領(lǐng)域，歡迎關(guān)注，一起學(xué)習(xí)。
好文章，我在看
好文章，我在看
評論
圖片
表情