相關(guān)文章鏈接

1. Easyexcel（1-注解使用）
2. Easyexcel（2-文件讀取）
3. Easyexcel（3-文件導(dǎo)出）

響應(yīng)頭設(shè)置

通過設(shè)置文件導(dǎo)出的響應(yīng)頭，可以自定義文件導(dǎo)出的名字信息等

//編碼格式為UTF-8
response.setCharacterEncoding("UTF-8");//讓服務(wù)器告訴瀏覽器它發(fā)送的數(shù)據(jù)屬于excel文件類型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");//描述內(nèi)容在傳輸過程中的編碼格式，BINARY可能不止包含非ASCII字符，還可能不是一個短行（超過1000字符）。
response.setHeader("Content-Transfer-Encoding", "binary");//must-revalidate：強制頁面不緩存，post-check=0, pre-check=0：0秒后，在顯示給用戶之前，該對象被選中進行更新過
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");//表示響應(yīng)可能是任何緩存的，即使它只是通常是非緩存或可緩存的僅在非共享緩存中
response.setHeader("Pragma", "public");//告訴瀏覽器這個文件的名字和類型，attachment：作為附件下載；inline：直接打開
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

寫入單個Sheet

一次性寫入數(shù)據(jù)

指定導(dǎo)出內(nèi)容所對應(yīng)的對象信息，通過doWrite寫入數(shù)據(jù)

注意：doWrite方法必須傳入的是集合

@Data
public class User {@ExcelProperty(value = "用戶Id")private Integer userId;@ExcelProperty(value = "姓名")private String name;@ExcelProperty(value = "手機")private String phone;@ExcelProperty(value = "郵箱")private String email;@ExcelProperty(value = "創(chuàng)建時間")private Date createTime;
}

@GetMapping("/download1")
public void download1(HttpServletResponse response) {try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");User user = new User();user.setUserId(123);user.setName("as");user.setPhone("15213");user.setEmail("5456");user.setCreateTime(new Date());EasyExcel.write(response.getOutputStream(), User.class).sheet("模板").doWrite(Arrays.asList(user));} catch (Exception e) {e.printStackTrace();}
}

分批寫入數(shù)據(jù)

@GetMapping("/download2")
public void download2(HttpServletResponse response) {ExcelWriter excelWriter = null;try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");User user = new User();user.setUserId(123);user.setName("as");user.setPhone("15213");user.setEmail("5456");user.setCreateTime(new Date());excelWriter = EasyExcel.write(response.getOutputStream(), User.class).build();WriteSheet writeSheet = EasyExcel.writerSheet("測試").build();// 業(yè)務(wù)邏輯處理，分頁查詢excelWriter.write(Arrays.asList(user), writeSheet);excelWriter.write(Arrays.asList(user), writeSheet);} catch (Exception e) {e.printStackTrace();} finally {if (excelWriter != null) {excelWriter.finish();}}
}

通過WriteSheet對象可以指定要寫入的Sheet，通過上面方式我們可以手工控制流的關(guān)閉，這樣我們就可以實現(xiàn)多次寫?？梢詫崿F(xiàn)分頁查詢獲取數(shù)據(jù)，然后將數(shù)據(jù)寫入Excel中，避免一次性加載的數(shù)據(jù)過多，導(dǎo)致內(nèi)存溢出

在使用excelWriter.write方式時務(wù)必保證至少執(zhí)行一次write，這樣是為了將sheet和表頭寫入excel，否則打開excel時會報錯。write的第一個參數(shù)可以為null

導(dǎo)出表頭自定義

使用注解的方式定義表頭時不能動態(tài)控制，每次修改表頭內(nèi)容時只能重新修改代碼，這時可以通過head方法動態(tài)傳參自定義表頭

注意：內(nèi)容結(jié)構(gòu)必須是List<List<T>>，如果使用List<T>會出現(xiàn)問題

@GetMapping("/download3")
public void download3(HttpServletResponse response) {ExcelWriter excelWriter = null;try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");User user = new User();user.setUserId(123);user.setName("as");user.setPhone("15213");user.setEmail("5456");user.setCreateTime(new Date());List<List<String>> heads = new ArrayList<>();heads.add(Arrays.asList("姓名"));heads.add(Arrays.asList("年齡"));heads.add(Arrays.asList("地址"));excelWriter = EasyExcel.write(response.getOutputStream()).head(heads).build();WriteSheet writeSheet = EasyExcel.writerSheet("測試").build();excelWriter.write(Arrays.asList(user), writeSheet);} catch (Exception e) {e.printStackTrace();} finally {if (excelWriter != null) {excelWriter.finish();}}
}

導(dǎo)出內(nèi)容自定義

當(dāng)導(dǎo)出的內(nèi)容不是某個固定的實體類時，希望導(dǎo)出不同的內(nèi)容時可以通過List<List<String>>自定義要寫入的內(nèi)容

@GetMapping("/download5")
public void download5(HttpServletResponse response) {ExcelWriter excelWriter = null;try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");List<List<String>> heads = new ArrayList<>();heads.add(Arrays.asList("姓名"));heads.add(Arrays.asList("年齡"));heads.add(Arrays.asList("地址"));excelWriter = EasyExcel.write(response.getOutputStream()).head(heads).build();WriteSheet writeSheet = EasyExcel.writerSheet("測試").build();List<List<String>> dataList = new ArrayList<>();dataList.add(Arrays.asList("張三", "18", "上海"));dataList.add(Arrays.asList("李四", "28"));excelWriter.write(dataList, writeSheet);} catch (Exception e) {e.printStackTrace();} finally {if (excelWriter != null) {excelWriter.finish();}}
}

寫入多個表頭

若業(yè)務(wù)需求要求在同一個Sheet中寫多個表，就需要用到WriteTable了。只定義一個WriteSheet，有幾個表就定義幾個WriteTable即可

@GetMapping("/download4")
public void download4(HttpServletResponse response) {ExcelWriter excelWriter = null;try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");User user = new User();user.setUserId(123);user.setName("as");user.setPhone("15213");user.setEmail("5456");user.setCreateTime(new Date());excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet writeSheet = EasyExcel.writerSheet("測試").build();List<List<String>> heads1 = new ArrayList<>();heads1.add(Arrays.asList("姓名"));heads1.add(Arrays.asList("年齡"));heads1.add(Arrays.asList("地址"));WriteTable writeTable1 = EasyExcel.writerTable(1).head(heads1).needHead(true).build();List<List<String>> heads2 = new ArrayList<>();heads2.add(Arrays.asList("姓名"));heads2.add(Arrays.asList("年齡"));heads2.add(Arrays.asList("地址"));heads2.add(Arrays.asList("出生日期"));WriteTable writeTable2 = EasyExcel.writerTable(2).head(heads2).needHead(true).build();excelWriter.write(Arrays.asList(user), writeSheet, writeTable1);excelWriter.write(Arrays.asList(user), writeSheet, writeTable2);} catch (Exception e) {e.printStackTrace();} finally {if (excelWriter != null) {excelWriter.finish();}}
}

寫入多個Sheet

通過EasyExcel.writerSheet創(chuàng)建對應(yīng)的sheet，然后在寫入sheet時指定對應(yīng)的WriteSheet即可，同時可指定每個Sheet對應(yīng)的對象

@GetMapping("/download6")
public void download6(HttpServletResponse response) {ExcelWriter excelWriter = null;try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");List<List<String>> heads = new ArrayList<>();heads.add(Arrays.asList("姓名"));heads.add(Arrays.asList("年齡"));heads.add(Arrays.asList("地址"));excelWriter = EasyExcel.write(response.getOutputStream()).head(heads).build();WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "測試1").build();WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "測試2").build();User user = new User();user.setUserId(123);user.setName("as");user.setPhone("15213");user.setEmail("5456");user.setCreateTime(new Date());excelWriter.write(Arrays.asList(user), writeSheet1);excelWriter.write(Arrays.asList(user), writeSheet2);} catch (Exception e) {e.printStackTrace();} finally {if (excelWriter != null) {excelWriter.finish();}}
}