1.基本介紹
EasyExcel是一個基于Java的簡單、省記憶體的讀寫Excel的開源項目。在盡可能節約記憶體的情況下支援讀寫百M的Excel。
依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
2.簡單的導出一個excel
對象
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
@Data
public class DemoExcel {
@ExcelProperty(value = {"數字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"字元"}, index = 1)
@ColumnWidth(20)
private String string;
/**
* 自定義的時間格式
*/
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"時間"}, index = 2)
@ColumnWidth(30)
private Date date;
}
Controller
import com.alibaba.excel.EasyExcel;
@GetMapping("/download")
public void download(HttpServletResponse response) {
// 模拟從資料庫查詢資料,不一定要轉成demoExcel對象,隻要字段能對應上就行
List<DemoExcel> list = getList();
try {
// 導出資料
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DemoExcel.class).sheet("sheet1")
.doWrite(list);
} catch (Exception e) {
log.debug("導出檔案失敗:{}", e.getMessage());
throw new RuntimeException("導出檔案失敗");
}
}
使用postman調試
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuIGO4EGO1UDZ1gTO40yM3ATYtIzYhRTL4M2Yw0yN4QTY1kTZz0yN1AjN4IDM1QTMwYTMvwlNxgDM3IzLcdmbw9CXwIDMy8CXw8CXlVXc1l3Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
點選send and download就能下載下傳到excel
如果excel打不開,報錯,多半是抛異常了,可以右鍵用記事本打開看到傳回的json,也可以直接點一下send再調一次接口直接看到報錯資訊
3.簡單的導入一個excel
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
@Data
public class DemoExcel {
@ExcelProperty(value = {"數字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"字元"}, index = 1)
@ColumnWidth(20)
private String string;
/**
* 自定義的時間格式
*/
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"時間"}, index = 2)
@ColumnWidth(30)
private Date date;
}
監聽
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
@Data
@Slf4j
public class DemoDataListener extends AnalysisEventListener<DemoExcel> {
private List<DemoExcel> list = new ArrayList();
@Override
public void invoke(DemoExcel data, AnalysisContext context) {
// 這裡取到的data就是單獨的一行資料,也可以在這個方法裡對資料進行一些簡單的處理
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 這個方法是在excel解析完成後能對資料進行操作,也能在這裡對資料進行各種處理
log.info("擷取資料量:" + list.size());
}
}
import com.alibaba.excel.EasyExcel;
@PostMapping("/upload")
public void upload(@RequestParam("file") MultipartFile file) throws IOException {
DemoDataListener listener = new DemoDataListener();
//headRowNumber(1)從第二行讀資料
//sheet()預設讀第一個sheet頁,當然想讀第二頁就往裡填個1
EasyExcel.read(file.getInputStream(), DemoExcel.class, listener).sheet().headRowNumber(1).doRead();
List<DemoExcel> list = listener.getList();
// 我們取到了excel中的資料後就能用來進行想要的操作了
list.forEach(s -> {
log.info("讀取到資料------" + s);
});
}
我還是用剛才導出來的那個檔案進行一次導入
postman向上面這樣設定就能進行導入檔案了
控制台的日志告訴我取到了這個數組
4.導出的excel要自帶下拉框,以及多個sheet頁的導出
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
@GetMapping("/download/default")
public void downloadDefault(HttpServletResponse response) {
try {
// 導出資料
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 這裡URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
// 預設值
List<DefaultData> defaultData = getDefault();
// 設定第1個sheet為我們的模版 同時設定一個下拉框
// 把sheet設定為不需要頭 不然會輸出sheet的頭 這樣看起來第一個table 就有2個頭了
WriteSheet writeSheet = EasyExcel.writerSheet(0,"模闆").needHead(false)
.registerWriteHandler(new CustomSheetWriteHandler(defaultData))
.build();
// 填寫表單的表頭
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(true).build();
writeTable0.setClazz(DemoExcel.class);
writer.write(Lists.newArrayList(), writeSheet, writeTable0);
// 根據預設值的pid整理一下
List<DemoDefaultExcel> defaultExcelList = getFormDataList(defaultData);
// 設定第2個sheet為字段可選範圍 ,把可以選擇的範圍列出來,沒有也沒事
WriteSheet writeDefaultDataSheet = EasyExcel.writerSheet(1,"字段選填範圍")
.head(DemoDefaultExcel.class).build();
writer.write(defaultExcelList, writeDefaultDataSheet);
writer.finish();
} catch (Exception e) {
log.debug("導出檔案失敗:{}", e.getMessage());
throw new RuntimeException("導出檔案失敗");
}
}
Handler
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
public class CustomSheetWriteHandler implements SheetWriteHandler {
private List<DefaultData> defaultDataList;
public CustomSheetWriteHandler(List<DefaultData> defaultDataList){
this.defaultDataList = defaultDataList;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 篩選出pid為1的值,添加到第2列的下拉框中
writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(1L, 1, writeSheetHolder));
writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(2L, 2, writeSheetHolder));
}
private DataValidation getValidationDataByPid(Long pid, int col, WriteSheetHolder writeSheetHolder){
List<String> dataValues = defaultDataList.stream().filter(item -> null != item.getPid() && item.getPid().equals(pid))
.map(DefaultData::getNodeName).collect(Collectors.toList());
return getValidationDataBySelects(writeSheetHolder, col, dataValues.toArray(new String[dataValues.size()]));
}
private DataValidation getValidationDataBySelects(WriteSheetHolder writeSheetHolder, int col, String[] valus){
// 這裡4個參數代表從第二行到第65536行,從第幾列到第幾列。可以根據需求增加
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, col, col);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(valus);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
return dataValidation;
}
}
DefaultData.java
@Data
public class DefaultData {
private Long pid;
private String nodeName;
}
預設值的結構是這樣的
導出的檔案第一頁,第二列和第三列有了下拉框選項
第二頁有可選範圍的示範
5.導出的excel要有示例
DemoExampleExcel.java
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
@Data
// 以注解形式設定樣式
// 頭背景設定成黃色 IndexedColors.YELLOW.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
// 内容的背景設定成黃色 IndexedColors.YELLOW.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
public class DemoExampleExcel {
// 相同的表頭會自動合并
@ExcelProperty(value = {"示例", "示例", "數字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"示例", "示例", "字元"}, index = 1)
@ColumnWidth(20)
private String string;
@ExcelProperty(value = {"示例", "示例", "字元2"}, index = 2)
@ColumnWidth(20)
private String string2;
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"示例", "示例", "時間"}, index = 3)
@ColumnWidth(30)
private Date date;
}
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
@GetMapping("/download/example")
public void downloadExample(HttpServletResponse response) {
try {
// 導出資料
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 這裡URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
// 把sheet設定為不需要頭 不然會輸出sheet的頭 這樣看起來第一個table 就有2個頭了
WriteSheet writeSheet = EasyExcel.writerSheet(0,"模闆").needHead(false).build();
// 擷取示例list 填寫示例
List<DemoExampleExcel> exampleExcels = getExampleList();
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(true).build();
writeTable0.setClazz(DemoExampleExcel.class);
writer.write(exampleExcels, writeSheet, writeTable0);
// 示例和表頭間增加一欄空行
writer.write(Lists.newArrayList(""), writeSheet);
// 填寫表單的表頭
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(true).build();
writeTable1.setClazz(DemoExcel.class);
writer.write(Lists.newArrayList(), writeSheet, writeTable1);
writer.finish();
} catch (Exception e) {
log.debug("導出檔案失敗:{}", e.getMessage());
throw new RuntimeException("導出檔案失敗");
}
}
這裡隻列舉了幾種實用并且可能用的上的小功能。
更多的進階技巧可以檢視官方語雀。也歡迎大家繼續補充這篇文章。
6.參考資料
github位址:
https://github.com/alibaba/easyexcel官方語雀:
https://www.yuque.com/easyexcel/doc/easyexcel