匯出示例：
後臺：
1、引入依賴：需要引入easyExcel的依賴，但是我在使用過程中發現也是需要poi的依賴，不然會報錯，就同時引入了。
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version></dependency><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version></dependency>2、實體類定義匯出欄位及Title：@ExcelProperty
@Setter@Getterpublic class TradingRecord { @ExcelProperty({"沃聯之家VGM對賬清單", "賬單建立時間","ID"}) private String uuid; @ExcelProperty({"沃聯之家VGM對賬清單", "賬單建立時間","關聯業務編號"}) private String referenceNo; @ExcelProperty({"沃聯之家VGM對賬清單", "賬單建立時間","金額"}) private Double changeTicket; @ExcelProperty({"沃聯之家VGM對賬清單", "賬單建立時間","交易時間"}) private Date recordDate; @ExcelProperty({"沃聯之家VGM對賬清單", "賬單總金額","交易說明"}) private String remark; }3、直接呼叫
public void writeTradingRecord(HttpServletResponse response,AppreciationServiceRequest appreciationServiceRequest) throws IOException { List<TradingRecord> list = new ArrayList<TradingRecord>(); String name = "VGM對賬清單.xlsx"; // 匯出時候會出現中文無法識別問題，需要轉碼 String fileName = new String(name.getBytes("gb2312"),"ISO8859-1"); response.setContentType("application/vnd.ms-excel;chartset=utf-8"); response.setHeader("Content-Disposition","attachment;filename=" + fileName); //呼叫工具類 ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build(); WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(TradingRecord.class).build(); writer.write(list,sheet); writer.finish(); // 使用完畢之後要關閉 }也支援動態複雜表頭：
public void writeTradingRecord(HttpServletResponse response,AppreciationServiceRequest appreciationServiceRequest) throws IOException { List<TradingRecord> list = new ArrayList<TradingRecord>(); String name = "VGM對賬清單.xlsx"; // 匯出時候會出現中文無法識別問題，需要轉碼 String fileName = new String(name.getBytes("gb2312"),"ISO8859-1"); response.setContentType("application/vnd.ms-excel;chartset=utf-8"); response.setHeader("Content-Disposition","attachment;filename=" + fileName); //呼叫工具類 // 自定義動態Title List<List<String>> headTitles = Lists.newArrayList(); // 第一行表頭 String basicInfo = "沃聯之家VGM對賬清單"; // 第二行表頭 SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); String createDate = "賬單建立時間: " + sdf.format(new Date()); String amount1 = "賬單總金額: ￥" + df2.format(amount); // 第三行表頭 headTitles.add( Lists.newArrayList(basicInfo , createDate,"ID")); headTitles.add( Lists.newArrayList(basicInfo , createDate,"關聯業務編號")); headTitles.add( Lists.newArrayList(basicInfo , createDate,"金額")); headTitles.add( Lists.newArrayList(basicInfo , createDate,"交易時間")); headTitles.add( Lists.newArrayList(basicInfo , amount1,"交易說明")); ExcelWriter writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build(); WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(headTitles).build(); writer.write(list,sheet); writer.finish(); }若想使匯出的表格寬度自適應：新增寬度自適應工具類，並在使用時.registerWriteHandler。如：EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();
package com.walltech.oms.util; import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;import org.apache.commons.collections4.CollectionUtils;import org.apache.poi.ss.usermodel.Cell; import java.util.HashMap;import java.util.List;import java.util.Map; /** * Excel 匯出列寬度自適應 * @author phli */public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > 255) { columnWidth = 255; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } }}4、VUE端：在使用過程中，發現不能直接使用axios直接呼叫，會報錯，需要使用window.localtion呼叫。
5、匯出示例：
----匯出end-----
匯入示例：1、VUE:使用element的el-upload
<el-upload class="upload-demo" name="file" :action="url" :with-credentials="true" :on-change="handleChange" :file-list="fileList" :limit="1" :on-exceed="handleExceed" :on-preview="handlePreview" accept=".xlsx" :auto-upload="false" > <el-button size="mini" style="border-radius:25px;"> <i class="el-icon-upload2"/>點選上傳</el-button> <div slot="tip" class="el-upload__tip">一次只能上傳一個檔案，檔案大小不可超過20M</div> </el-upload>2、傳參呼叫後臺：
importExcel () { let self = this if (self.fileList.length <= 0) { self.$message("請選擇要匯入的檔案！") } let formData = new FormData(); let file = this.fileList[0] formData.append('file', file.raw) self.importLoding = true axios.post('/appreciation/import', formData).then(res => { self.$message('匯入成功') self.importDialog = false self.fetchVgmList(1) self.fileList = [] self.importLoding = false }).catch (err=> { self.importLoding = false }) }3、後臺處理：
EasyExcel.read(file.getInputStream(), TradingRecord.class, new ImportTradingListener(appreciationService)).sheet().doRead();4、新增監聽，處理excel表格內容：
package com.walltech.oms.listener; import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.walltech.oms.pojo.excelModel.TradingRecord;import com.walltech.oms.service.AppreciationService; import java.util.ArrayList;import java.util.List; /** * 監聽：交易記錄匯入獲取UUID * @author phli */public class ImportTradingListener extends AnalysisEventListener<TradingRecord> { /** * 每隔1000條儲存資料庫，然後清理list,方便記憶體回收 */ private static final Integer BATCH_COUNT = 1000; List<String> list = new ArrayList<>(); private AppreciationService appreciationService; public ImportTradingListener(AppreciationService appreciationService){ this.appreciationService = appreciationService; } @Override public void invoke(TradingRecord tradingRecord, AnalysisContext analysisContext) { list.add(tradingRecord.getUuid()); if (list.size() >= BATCH_COUNT) { list.clear(); //保證doAfterAllAnalysed方法執行資料為不重複資料 } } //這裡是資料全部讀完之後走 為保證資料不夠批次最小值也能存進資料庫 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 這個方法便是拿到excel的資料之後，進行修改資料庫的操作。 appreciationService.updateWriteOffState(list); }}------匯入END------
最新評論