引入依賴
easyExcel依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
只引入easyexcel依賴運(yùn)行可能會報錯擂送，就還需要引入poi的相關(guān)依賴回溺，注意easyExcel和poi的版本兼容問題
<!--POI start-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<!--POI end-->
編寫工具類
/**
* @Description: easyExcel工具類
* @Author along
* @Date 2022/9/16 17:56
*/
public class EasyExcelUtils {
private static final Logger log = LoggerFactory.getLogger(EasyExcelUtils.class);
/**
* 單sheet頁導(dǎo)出
*
* @param response HttpServletResponse對象
* @param excelName 導(dǎo)出文件名
* @param sheetName sheet名稱
* @param clazz 類
* @param data 數(shù)據(jù)
* @throws Exception
*/
public static void export(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 這里URLEncoder.encode可以防止中文亂碼
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
} catch (Exception e) {
log.error("", e);
}
}
/**
* 多sheet頁導(dǎo)出
*
* @param response
* @param excelName excel名稱
* @param sheetMessages sheet信息列表
*/
public static void exportMoreSheet(HttpServletResponse response, String excelName, List<SheetMessage> sheetMessages) {
ExcelWriter excelWriter = null;
try {
// 設(shè)置響應(yīng)頭信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
excelName = URLEncoder.encode(excelName, "UTF-8"); // 這里URLEncoder.encode可以防止中文亂碼
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
// 創(chuàng)建ExcelWriter對象
excelWriter = EasyExcel.write(response.getOutputStream()).build();
AtomicInteger count = new AtomicInteger(); // 計數(shù)器變量
for (EasyExcelUtils.SheetMessage message : sheetMessages) {
WriteSheet sheet = EasyExcel.writerSheet(count.get(), message.getSheetName()).head(message.getClazz()).build();
// 寫入Sheet數(shù)據(jù)
excelWriter.write(message.getData(), sheet);
count.getAndIncrement();
}
} catch (Exception e) {
log.error("", e);
} finally {
// 關(guān)閉ExcelWriter
if (excelWriter != null) {
excelWriter.finish();
}
}
}
@Data
@NoArgsConstructor // 自動生成無參構(gòu)造方法
@AllArgsConstructor // 自動生成全參構(gòu)造方法
public static class SheetMessage {
/**
* sheet頁名稱
*/
private String sheetName;
/**
* Class
*/
private Class<?> clazz;
/**
* 數(shù)據(jù)
*/
private List data;
}
}
應(yīng)用
單sheet頁excel導(dǎo)出
構(gòu)建導(dǎo)出對象實(shí)體
/**
* @Description: excel導(dǎo)出對象
* @Author along
* @Date 2022/9/16 17:11
*/
@Data
public class Demo {
/**
* value:定義表頭名稱春贸，下面是簡單表頭混萝，復(fù)雜表頭寫法：value={"aaa","bbb"}
* index:定義位置，0代表第一列萍恕，不寫就默認(rèn)從上往下排序
*/
@ColumnWidth(20)
@ExcelProperty(value = "學(xué)號",index = 0)
private Long id;
@ColumnWidth(20)
@ExcelProperty(value = "名字",index = 1)
private String name;
@ColumnWidth(20)
@ExcelProperty(value = "性別",index = 2)
private String gender;
@ColumnWidth(20)
@ExcelProperty(value = "愛好",index = 3)
private String hobby;
}
導(dǎo)出邏輯代碼逸嘀，下面是偽代碼，意思一下
// 獲取數(shù)據(jù)源
List<Demo> list = demoDao.list();
// 填充導(dǎo)出對象ExportTaskObjectVo
List<ExportTaskObjectVo> exportVos = new ArrayList<>();
for (VTopicDialTestInternetTask e : list) {
ExportTaskObjectVo v = new ExportTaskObjectVo();
v.setCustomersNum(e.getCustomersNum());
v.setCustomersName(e.getCustomersName());
v.setLineId(e.getLineId());
v.setLineType(e.getLineType());
exportVos.add(v);
}
// 調(diào)用工具類導(dǎo)出Excel
EasyExcelUtils.export(response,
"demo", // 導(dǎo)出的excel文件的文件名
"sheet1", // excel第一個sheet的名稱
ExportTaskObjectVo.class,
exportVos
);
完畢允粤，單sheet頁導(dǎo)出是開發(fā)中最常用的崭倘，有時候我們需要導(dǎo)出多sheet頁的excel，如下：
多sheet頁導(dǎo)出
// 獲取數(shù)據(jù)源
List<Demo1> list1 = demoDao.list1();
List<Demo2> list2 = demoDao.list2();
List<Demo3> list3 = demoDao.list3();
List<Demo4> list4 = demoDao.list4();
List<EasyExcelUtils.SheetMessage> sheetMessages = new LinkedList<>();
sheetMessages.add(new EasyExcelUtils.SheetMessage("sheet1", Demo1.class, list1));
sheetMessages.add(new EasyExcelUtils.SheetMessage("sheet2", Demo2.class, list2));
sheetMessages.add(new EasyExcelUtils.SheetMessage("sheet3", Demo3.class, list3));
sheetMessages.add(new EasyExcelUtils.SheetMessage("sheet4", Demo4.class, list4));
String excelName = "output.xlsx"; // 導(dǎo)出文件名
EasyExcelUtils.exportMoreSheet(response, excelName, sheetMessages);
完事类垫，導(dǎo)出就這樣簡單司光，基本一看就能用
導(dǎo)入Excel并解析
導(dǎo)入的excel模板如下：
導(dǎo)入excel模板
根據(jù)模板Excel內(nèi)容構(gòu)建與之匹配的接收類
@Data
public class DemoExcelModel {
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "愛好", index = 1)
private String hobby;
}
下面是Controller里的接收解析邏輯
@RestController
@RequestMapping("/demo")
@CrossOrigin
@Slf4j
public class DemoController {
@PostMapping("/parsing/excel")
public void excel2Object(@RequestParam("file") MultipartFile file) throws IOException {
log.info("接收并解析excel");
InputStream inputStream = file.getInputStream();
List<Object> excelModels = EasyExcel.read(inputStream)
.head(DemoExcelModel.class)
.sheet() // 設(shè)置sheet,默認(rèn)讀取第一個
.headRowNumber(1) // 設(shè)置標(biāo)題所在行數(shù)
.doReadSync();
log.info("Excel解析完成：{}", excelModels);
// 解析完畢，下面想怎么處理這個數(shù)據(jù)就怎么處理
for (Object object : excelModels) {
// 強(qiáng)轉(zhuǎn)
DemoExcelModel model = (DemoExcelModel) object;
// do something
}
}
}
完事
