匯出示例：
后臺：
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
@Getter
public 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------
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/236687.html
標籤：java