1.封裝工具類
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {
private final List<T> rows = new ArrayList();
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("解析到一條頭數(shù)據(jù):{}", JSON.toJSONString(headMap));
}
@Override
public void invoke(T object, AnalysisContext context) {
rows.add(object);
// 實(shí)際數(shù)據(jù)量比較大時(shí)，rows里的數(shù)據(jù)可以存到一定量之后進(jìn)行批量處理（比如存到數(shù)據(jù)庫(kù)）泳秀，
// 然后清空列表秘血，以防止內(nèi)存占用過(guò)多造成OOM
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("read {} rows", rows.size());
}
/**
* 在轉(zhuǎn)換異常 獲取其他異常下會(huì)調(diào)用本接口尽纽。拋出異常則停止讀取。如果這里不拋出異常則 繼續(xù)讀取下一行明也。
*
* @param exception
* @param context
* @throws Exception
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("解析失敗女嘲，但是繼續(xù)解析下一行:{}", exception.getMessage());
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
log.error("第{}行，第{}列解析異常顿仇，數(shù)據(jù)為:{}", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}
}
public List<T> getRows() {
return rows;
}
}
public class EasyExcelUtil {
/**
* 同步無(wú)模型讀（默認(rèn)讀取sheet0,從第2行開(kāi)始讀）
*
* @param filePath
* @return
*/
public static List<Map<Integer, String>> syncRead(String filePath) {
return EasyExcelFactory.read(filePath).sheet().doReadSync();
}
/**
* 同步無(wú)模型讀（默認(rèn)表頭占一行，從第2行開(kāi)始讀）
*
* @param filePath
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @return
*/
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).doReadSync();
}
/**
* 同步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param inputStream
* @param sheetNo sheet頁(yè)號(hào)臼闻，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)鸿吆，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return List<Map < colNum, cellValue>>
*/
public static List<Map<Integer, String>> syncRead(InputStream inputStream, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
/**
* 同步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param file
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)述呐，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return List<Map < colNum, cellValue>>
*/
public static List<Map<Integer, String>> syncRead(File file, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
/**
* 同步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param filePath
* @param sheetNo sheet頁(yè)號(hào)惩淳，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return List<Map < colNum, cellValue>>
*/
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
/**
* 同步按模型讀（默認(rèn)讀取sheet0,從第2行開(kāi)始讀）
*
* @param filePath
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @return
*/
public static <T> List<T> syncReadModel(String filePath, Class<T> clazz) {
return EasyExcelFactory.read(filePath).sheet().head(clazz).doReadSync();
}
/**
* 同步按模型讀（默認(rèn)表頭占一行市埋，從第2行開(kāi)始讀）
*
* @param filePath
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)黎泣，從0開(kāi)始
* @return
*/
public static List<T> syncReadModel(String filePath, Class<T> clazz, Integer sheetNo) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).head(clazz).doReadSync();
}
/**
* 同步按模型讀（指定sheet和表頭占的行數(shù)）
*
* @param inputStream
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)缤谎，從1開(kāi)始（不帶表頭）
* @return
*/
public static <T> List<T> syncReadModel(InputStream inputStream, Class<T> clazz) {
return syncReadModel(inputStream,clazz,0,1);
}
/**
* 同步按模型讀（指定sheet和表頭占的行數(shù)）
*
* @param inputStream
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)抒倚，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static <T> List<T> syncReadModel(InputStream inputStream, Class<T> clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 同步按模型讀（指定sheet和表頭占的行數(shù)）
*
* @param file
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)坷澡，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)托呕，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static <T> List<T> syncReadModel(File file, Class<T> clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 同步按模型讀（指定sheet和表頭占的行數(shù)）
*
* @param filePath
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)频敛，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static <T> List<T> syncReadModel(String filePath, Class<T> clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 異步無(wú)模型讀（默認(rèn)讀取sheet0,從第2行開(kāi)始讀）
*
* @param excelListener 監(jiān)聽(tīng)器项郊，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap，表頭數(shù)據(jù)斟赚，異常處理等
* @param filePath 表頭占的行數(shù)着降，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener) {
EasyExcelFactory.read(filePath, excelListener).sheet().doRead();
}
/**
* 異步無(wú)模型讀（默認(rèn)表頭占一行，從第2行開(kāi)始讀）
*
* @param filePath 表頭占的行數(shù)拗军，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @param excelListener 監(jiān)聽(tīng)器任洞，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap，表頭數(shù)據(jù)发侵，異常處理等
* @param sheetNo sheet頁(yè)號(hào)交掏，從0開(kāi)始
* @return
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener, Integer sheetNo) {
EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).doRead();
}
/**
* 異步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param inputStream
* @param excelListener 監(jiān)聽(tīng)器，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap刃鳄，表頭數(shù)據(jù)盅弛，異常處理等
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)叔锐，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static void asyncRead(InputStream inputStream, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(inputStream, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 異步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param file
* @param excelListener 監(jiān)聽(tīng)器挪鹏，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap，表頭數(shù)據(jù)愉烙，異常處理等
* @param sheetNo sheet頁(yè)號(hào)狰住，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static void asyncRead(File file, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(file, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 異步無(wú)模型讀（指定sheet和表頭占的行數(shù)）
*
* @param filePath
* @param excelListener 監(jiān)聽(tīng)器齿梁，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap，表頭數(shù)據(jù)，異常處理等
* @param sheetNo sheet頁(yè)號(hào)勺择，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)创南，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
* @return
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 異步按模型讀取（默認(rèn)讀取sheet0,從第2行開(kāi)始讀）
*
* @param filePath
* @param excelListener 監(jiān)聽(tīng)器省核，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap稿辙，表頭數(shù)據(jù)，異常處理等
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz) {
EasyExcelFactory.read(filePath, clazz, excelListener).sheet().doRead();
}
/**
* 異步按模型讀绕摇（默認(rèn)表頭占一行邻储，從第2行開(kāi)始讀）
*
* @param filePath
* @param excelListener 監(jiān)聽(tīng)器，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap旧噪，表頭數(shù)據(jù)吨娜，異常處理等
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo) {
EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).doRead();
}
/**
* 異步按模型讀取
*
* @param inputStream
* @param excelListener 監(jiān)聽(tīng)器淘钟，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap宦赠，表頭數(shù)據(jù)，異常處理等
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)米母，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)勾扭，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
*/
public static void asyncReadModel(InputStream inputStream, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(inputStream, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 異步按模型讀取
*
* @param file
* @param excelListener 監(jiān)聽(tīng)器，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap铁瞒，表頭數(shù)據(jù)妙色，異常處理等
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)慧耍，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
*/
public static void asyncReadModel(File file, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(file, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 異步按模型讀取
*
* @param filePath
* @param excelListener 監(jiān)聽(tīng)器身辨，在監(jiān)聽(tīng)器中可以處理行數(shù)據(jù)LinkedHashMap，表頭數(shù)據(jù)蜂绎，異常處理等
* @param clazz 模型的類類型（excel數(shù)據(jù)會(huì)按該類型轉(zhuǎn)換成對(duì)象）
* @param sheetNo sheet頁(yè)號(hào)栅表，從0開(kāi)始
* @param headRowNum 表頭占的行數(shù)，從0開(kāi)始（如果要連表頭一起讀出來(lái)則傳0）
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 無(wú)模板寫文件
*
* @param filePath
* @param head 表頭數(shù)據(jù)
* @param data 表內(nèi)容數(shù)據(jù)
*/
public static void write(String filePath, List<List<String>> head, List<List<Object>> data) {
EasyExcel.write(filePath).head(head).sheet().doWrite(data);
}
/**
* 無(wú)模板寫文件
*
* @param filePath
* @param head 表頭數(shù)據(jù)
* @param data 表內(nèi)容數(shù)據(jù)
* @param sheetNo sheet頁(yè)號(hào)师枣，從0開(kāi)始
* @param sheetName sheet名稱
*/
public static void write(String filePath, List<List<String>> head, List<List<Object>> data, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath).head(head).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 根據(jù)excel模板文件寫入文件
*
* @param filePath
* @param templateFileName
* @param headClazz
* @param data
*/
public static void writeTemplate(String filePath, String templateFileName, Class headClazz, List data) {
EasyExcel.write(filePath, headClazz).withTemplate(templateFileName).sheet().doWrite(data);
}
/**
* 根據(jù)excel模板文件寫入文件
*
* @param filePath
* @param templateFileName
* @param data
*/
public static void writeTemplate(String filePath, String templateFileName, List data) {
EasyExcel.write(filePath).withTemplate(templateFileName).sheet().doWrite(data);
}
/**
* 按模板寫文件
*
* @param filePath
* @param headClazz 表頭模板
* @param data 數(shù)據(jù)
*/
public static void write(String filePath, Class headClazz, List data) {
EasyExcel.write(filePath, headClazz).sheet().doWrite(data);
}
/**
* 按模板寫文件
*
* @param filePath
* @param headClazz 表頭模板
* @param data 數(shù)據(jù)
* @param sheetNo sheet頁(yè)號(hào)怪瓶，從0開(kāi)始
* @param sheetName sheet名稱
*/
public static void write(String filePath, Class headClazz, List data, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板寫文件
*
* @param filePath
* @param headClazz 表頭模板
* @param data 數(shù)據(jù)
* @param writeHandler 自定義的處理器，比如設(shè)置table樣式践美，設(shè)置超鏈接洗贰、單元格下拉框等等功能都可以通過(guò)這個(gè)實(shí)現(xiàn)（需要注冊(cè)多個(gè)則自己通過(guò)鏈?zhǔn)饺フ{(diào)用）
* @param sheetNo sheet頁(yè)號(hào)，從0開(kāi)始
* @param sheetName sheet名稱
*/
public static void write(String filePath, Class headClazz, List data, WriteHandler writeHandler, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).registerWriteHandler(writeHandler).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板寫文件（包含某些字段）
*
* @param filePath
* @param headClazz 表頭模板
* @param data 數(shù)據(jù)
* @param includeCols 過(guò)濾包含的字段陨倡，根據(jù)字段名稱過(guò)濾
* @param sheetNo sheet頁(yè)號(hào)敛滋，從0開(kāi)始
* @param sheetName sheet名稱
*/
public static void writeInclude(String filePath, Class headClazz, List data, Set<String> includeCols, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).includeColumnFiledNames(includeCols).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板寫文件（排除某些字段）
*
* @param filePath
* @param headClazz 表頭模板
* @param data 數(shù)據(jù)
* @param excludeCols 過(guò)濾排除的字段，根據(jù)字段名稱過(guò)濾
* @param sheetNo sheet頁(yè)號(hào)兴革，從0開(kāi)始
* @param sheetName sheet名稱
*/
public static void writeExclude(String filePath, Class headClazz, List data, Set<String> excludeCols, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).excludeColumnFiledNames(excludeCols).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 多個(gè)sheet頁(yè)的數(shù)據(jù)鏈?zhǔn)綄懭? * ExcelUtil.writeWithSheets(outputStream)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
*
* @param outputStream
* @return
*/
public static EasyExcelWriterFactory writeWithSheets(OutputStream outputStream) {
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(outputStream);
return excelWriter;
}
/**
* 多個(gè)sheet頁(yè)的數(shù)據(jù)鏈?zhǔn)綄懭? * ExcelUtil.writeWithSheets(file)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
*
* @param file
* @return
*/
public static EasyExcelWriterFactory writeWithSheets(File file) {
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(file);
return excelWriter;
}
/**
* 多個(gè)sheet頁(yè)的數(shù)據(jù)鏈?zhǔn)綄懭? * ExcelUtil.writeWithSheets(filePath)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
*
* @param filePath
* @return
*/
public static EasyExcelWriterFactory writeWithSheets(String filePath) {
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(filePath);
return excelWriter;
}
/**
* 多個(gè)sheet頁(yè)的數(shù)據(jù)鏈?zhǔn)綄懭耄ㄊ×藭?huì)返回一個(gè)有部分?jǐn)?shù)據(jù)的Excel）
* ExcelUtil.writeWithSheets(response, exportFileName)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
*
* @param response
* @param exportFileName 導(dǎo)出的文件名稱
* @return
*/
public static EasyExcelWriterFactory writeWithSheetsWeb(HttpServletResponse response, String exportFileName) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 這里URLEncoder.encode可以防止中文亂碼
String fileName = URLEncoder.encode(exportFileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(response.getOutputStream());
return excelWriter;
}
}
public class EasyExcelWriterFactory {
private int sheetNo = 0;
private ExcelWriter excelWriter = null;
public EasyExcelWriterFactory(OutputStream outputStream) {
excelWriter = EasyExcel.write(outputStream).build();
}
public EasyExcelWriterFactory(File file) {
excelWriter = EasyExcel.write(file).build();
}
public EasyExcelWriterFactory(String filePath) {
excelWriter = EasyExcel.write(filePath).build();
}
/**
* 鏈?zhǔn)侥０灞眍^寫入
*
* @param headClazz 表頭格式
* @param data 數(shù)據(jù) List<ExcelModel> 或者List<List<Object>>
* @return
*/
public EasyExcelWriterFactory writeModel(Class headClazz, List data, String sheetName) {
excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(headClazz).build());
return this;
}
/**
* 鏈?zhǔn)阶远x表頭寫入
*
* @param head
* @param data 數(shù)據(jù) List<ExcelModel> 或者List<List<Object>>
* @param sheetName
* @return
*/
public EasyExcelWriterFactory write(List<List<String>> head, List data, String sheetName) {
excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(head).build());
return this;
}
public void finish() {
excelWriter.finish();
}
}
public class FileUtil {
public static InputStream getResourcesFileInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath() {
return FileUtil.class.getResource("/").getPath();
}
public static File createNewFile(String pathName) {
File file = new File(getPath() + pathName);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName) {
return new File(getPath() + pathName);
}
}
2.使用
@Data
public class DemoData {
@ExcelProperty(value = "stringTitle",index =0)
private String string;
@ExcelProperty(value = "dateTitle",index =1)
private String date;
@ExcelProperty(value = "stringTitle",index =2)
private String doubleData;
}
@Controller
public class ExcelController {
private static final Logger log = LogManager.getLogger(ExcelController.class);
/**
* 用戶導(dǎo)入Excel
*/
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ResponseBody
public CommonResponse<?> importExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) {
try {
List<DemoData> excelModelList = EasyExcelUtil.syncReadModel(file.getInputStream(), DemoData.class);
return CommonResponse.success(excelModelList);
} catch (IOException e) {
log.error("Excel導(dǎo)入失敗", e);
}
return CommonResponse.failure("失敗");
}
/**
* 導(dǎo)出Excel
*/
@RequestMapping(value = "/download")
@ResponseBody
public void writeExcel(HttpServletResponse response) throws IOException {
//初始化模擬數(shù)據(jù)
List<DemoData> excelModelList = new ArrayList<>();
for (int i = 0; i < 700; i++) {
DemoData excelModel = new DemoData();
excelModel.setString("ihuaben" + i);
excelModel.setDate("2020-1-3");
excelModel.setDoubleData(i + "");
excelModelList.add(excelModel);
}
EasyExcelWriterFactory ihuaben = EasyExcelUtil.writeWithSheetsWeb(response, "ihuaben");
ihuaben.writeModel(DemoData.class, excelModelList, "ihuaben");
ihuaben.finish();
}
/**
* 讀取resources下靜態(tài)模板
*/
@RequestMapping(value = "/staticTemplate")
@ResponseBody
public CommonResponse<?> staticTemplate(HttpServletResponse response) {
String fileName = FileUtil.getPath() + "static" + File.separator + "demo.xlsx";
List<DemoData> excelModelList = EasyExcelUtil.syncReadModel(fileName, DemoData.class);
return CommonResponse.success(excelModelList);
}
}
接口測(cè)試
image.png
1.importExcel
image.png
2.download
image.png
3.staticTemplate
image.png
image.png
