一子房、技術(shù)選型
ava解析、生成Excel比較有名的框架有Apache poi就轧、jxl证杭。但他們都存在一個(gè)嚴(yán)重的問題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題妒御，但POI還是有一些缺陷解愤，比如07版Excel解壓縮以及解壓后存儲都是在內(nèi)存中完成的，內(nèi)存消耗依然很大乎莉。easyexcel重寫了poi對07版Excel的解析琢歇，能夠原本一個(gè)3M的excel用POI sax依然需要100M左右內(nèi)存降低到KB級別，并且再大的excel不會出現(xiàn)內(nèi)存溢出梦鉴，03版依賴POI的sax模式。在上層做了模型轉(zhuǎn)換的封裝揭保，讓使用者更加簡單方便
easyExcel官方GitHub地址：內(nèi)有詳細(xì)的講解
小編最近在工作中剛好遇到了這么一個(gè)需求肥橙，將一系列數(shù)據(jù)導(dǎo)出為Excel表格。然后就開始百度秸侣，就看到了上面這段話存筏。 經(jīng)過小編對 poi 和 easyExcel 的demo的對比宠互，決定使用easyExcel實(shí)現(xiàn)這個(gè)需求。
二椭坚、實(shí)現(xiàn)過程
1予跌、導(dǎo)入依賴
建議去GitHub查看最新版本號：
<!-- 阿里開源EXCEL -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
2、編寫工具類 EasyExcelUtil
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hanclouds.teamwork.entity.EasyExcelParams;
import org.apache.poi.ss.usermodel.IndexedColors;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author MouFangCai
* @date 2019/7/23 11:48
*/
public class EasyExcelUtil {
/**
* 下載EXCEL文件2007版本
*
* @throws IOException IO異常
*/
public static void exportExcel2007Format(EasyExcelParams excelParams) throws IOException {
exportExcel(excelParams, ExcelTypeEnum.XLSX);
}
/**
* 下載EXCEL文件2003版本
*
* @throws IOException IO異常
*/
public static void exportExcel2003Format(EasyExcelParams excelParams) throws IOException {
exportExcel(excelParams, ExcelTypeEnum.XLS);
}
/**
* 根據(jù)參數(shù)和版本枚舉導(dǎo)出excel文件
*
* @param excelParams 參數(shù)實(shí)體
* @param typeEnum excel類型枚舉
* @throws IOException
*/
private static void exportExcel(EasyExcelParams excelParams, ExcelTypeEnum typeEnum) throws IOException {
HttpServletResponse response = excelParams.getResponse();
ServletOutputStream out = response.getOutputStream();
// ExcelWriter提供了多種構(gòu)造方式善茎，可自行查看選擇所需要的
ExcelWriter writer = new ExcelWriter(null, out, typeEnum,
true, excelParams.getWriteHandler());
// 設(shè)置web下載等的信息
prepareResponds(response, typeEnum);
// 創(chuàng)建一個(gè)sheet
Sheet sheet = new Sheet(1, 0, excelParams.getDataModelClazz());
sheet.setSheetName(excelParams.getSheetName());
// 設(shè)置列寬 設(shè)置每列的寬度
Map<Integer,Integer> columnWidth = new HashMap<>(6);
columnWidth.put(0,6666);
columnWidth.put(1,5000);
columnWidth.put(2,15000);
columnWidth.put(3,3000);
columnWidth.put(4,20000);
columnWidth.put(5,10000);
sheet.setColumnWidthMap(columnWidth);
sheet.setAutoWidth(Boolean.TRUE);
// 用于設(shè)置 表格樣式
sheet.setTableStyle(createTableStyle());
// 寫入Excel中券册，也提供了多個(gè)重載方法，根據(jù)需要選擇
writer.write(excelParams.getData(), sheet);
// 根據(jù)數(shù)據(jù)展示需要垂涯，用于合并單元格
List<int[]> mergeList = excelParams.getMergeList();
if (mergeList != null && mergeList.size() > 0){
for (int[] arr : mergeList) {
// 待合并的單元格參數(shù)：開始的行數(shù)烁焙，結(jié)束的行數(shù)，開始的列數(shù)耕赘，結(jié)束的列數(shù)
writer.merge(arr[0], arr[1], arr[2], arr[3]);
}
}
writer.finish();
out.flush();
}
/**
* 將文件輸出到瀏覽器（導(dǎo)出文件）
*
* @param response 響應(yīng)
* @param typeEnum excel類型
*/
private static void prepareResponds(HttpServletResponse response,ExcelTypeEnum typeEnum) throws UnsupportedEncodingException {
String fileName = new String((new SimpleDateFormat("MMddHHmm").format(new Date()))
.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
response.setContentType("multipart/form-data");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + typeEnum.getValue());
}
/**
* 表格樣式：目前easyExcel的樣式調(diào)整骄蝇，僅支持表頭和內(nèi)容兩部分別統(tǒng)一設(shè)置
* @return
*/
private static TableStyle createTableStyle(){
TableStyle tableStyle = new TableStyle();
tableStyle.setTableHeadBackGroundColor(IndexedColors.GREY_50_PERCENT);
tableStyle.setTableContentBackGroundColor(IndexedColors.GREY_25_PERCENT);
Font contentFont = new Font();
contentFont.setFontName("黑體");
contentFont.setFontHeightInPoints((short)12);
tableStyle.setTableContentFont(contentFont);
Font headFont = new Font();
headFont.setFontName("黑體");
headFont.setFontHeightInPoints((short)10);
tableStyle.setTableHeadFont(headFont);
return tableStyle;
}
}
writer.merge(arr[0], arr[1], arr[2], arr[3]); 關(guān)于合并單元的這個(gè)merge方法
public ExcelWriter merge(int firstRow, int lastRow, int firstCol, int lastCol) { this.excelBuilder.merge(firstRow, lastRow, firstCol, lastCol); return this; }通過查看方法詳情，即可理解：開始合并的行數(shù)操骡，結(jié)束合并的行數(shù)九火，開始合并的列數(shù)，結(jié)束合并的列數(shù)
需要注意的是：對應(yīng)的 last 的值必須大于等于 first 的值
3册招、公用參數(shù)類 EasyExcelParams
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Table;
import com.hanclouds.teamwork.util.MyWriteHandler;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* @author MouFangCai
* @date 2019/7/23 20:18
*/
public class EasyExcelParams {
/**
* 表格樣式：使用
*/
private MyWriteHandler writeHandler;
/**
* 表格樣式:未使用
*/
private Table table;
/**
* 需合并的單元格參數(shù)配置集合 list
*/
private List<int[]> mergeList;
/**
* excel文件名（不帶拓展名)
*/
private String excelNameWithoutExt;
/**
* sheet名稱
*/
private String sheetName;
/**
* 是否需要表頭
*/
private boolean needHead = true;
/**
* 數(shù)據(jù)
*/
private List<? extends BaseRowModel> data;
/**
* 數(shù)據(jù)模型類型
*/
private Class<? extends BaseRowModel> dataModelClazz;
/**
* 響應(yīng)
*/
private HttpServletResponse response;
public EasyExcelParams() {
}
public MyWriteHandler getWriteHandler() {
return writeHandler;
}
public void setWriteHandler(MyWriteHandler writeHandler) {
this.writeHandler = writeHandler;
}
public Table getTable() {
return table;
}
public void setTable(Table table) {
this.table = table;
}
public List<int[]> getMergeList() {
return mergeList;
}
public void setMergeList(List<int[]> mergeList) {
this.mergeList = mergeList;
}
public String getExcelNameWithoutExt() {
return excelNameWithoutExt;
}
public void setExcelNameWithoutExt(String excelNameWithoutExt) {
this.excelNameWithoutExt = excelNameWithoutExt;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public boolean isNeedHead() {
return needHead;
}
public void setNeedHead(boolean needHead) {
this.needHead = needHead;
}
public List<? extends BaseRowModel> getData() {
return data;
}
public void setData(List<? extends BaseRowModel> data) {
this.data = data;
}
public Class<? extends BaseRowModel> getDataModelClazz() {
return dataModelClazz;
}
public void setDataModelClazz(Class<? extends BaseRowModel> dataModelClazz) {
this.dataModelClazz = dataModelClazz;
}
public HttpServletResponse getResponse() {
return response;
}
public void setResponse(HttpServletResponse response) {
this.response = response;
}
}
4岔激、表格樣式實(shí)體類 MyWriteHandler
關(guān)于使用easyExcel去進(jìn)行表格樣式的設(shè)置，個(gè)人感覺對于復(fù)雜的樣式成本是比較的跨细，所以小編這里的樣式鹦倚，僅僅只有邊框、水平居中冀惭、垂直居中震叙、內(nèi)容自適應(yīng)。而且整個(gè)表格內(nèi)容的格式是一樣的散休。
package com.hanclouds.teamwork.util;
import com.alibaba.excel.event.WriteHandler;
import org.apache.poi.ss.usermodel.*;
/**
* @author MouFangCai
* @date 2019/7/28 18:06
*/
public class MyWriteHandler implements WriteHandler {
private CellStyle cellStyle;
@Override
public void sheet(int i, Sheet sheet) {
Workbook workbook = sheet.getWorkbook();
// 創(chuàng)建樣式
cellStyle = workbook.createCellStyle();
}
@Override
public void row(int i, Row row) {
}
@Override
public void cell(int i, Cell cell) {
if (cell.getRowIndex() >0) {
createStyle();
cell.setCellStyle(this.cellStyle);
}
}
private void createStyle() {
// 填充色：和EasyExcelUtil里的createTableStyle效果一樣
// cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 下邊框
cellStyle.setBorderBottom(BorderStyle.THIN);
// 左邊框
cellStyle.setBorderLeft(BorderStyle.THIN);
// 右邊框
cellStyle.setBorderRight(BorderStyle.THIN);
// 上邊框
cellStyle.setBorderTop(BorderStyle.THIN);
// 設(shè)置自動(dòng)換行
cellStyle.setWrapText(true);
// 水平對齊方式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillBackgroundColor((short)22);
// 垂直對齊方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
}
5媒楼、數(shù)據(jù)實(shí)體類（也是表頭）BaseRowModel
注意，必須繼承** BaseRowModel**
** @ExcelProperty(value = "部門",index = 0) 該注解的作用就是：聲明表頭名戚丸，和對應(yīng)的位置划址，index=0表示在列數(shù)“第一列”**
package com.hanclouds.teamwork.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
/**
* @author MouFangCai
* @date 2019/7/24 9:18
*/
public class WeeklyExportDto extends BaseRowModel {
@ExcelProperty(value = "部門",index = 0)
private String groupName;
@ExcelProperty(value = "項(xiàng)目名稱",index = 1)
private String projectName;
@ExcelProperty(value = "任務(wù)名",index = 2)
private String weeklyTaskName;
@ExcelProperty(value = "責(zé)任人",index = 3)
private String dutyUserName;
@ExcelProperty(value = "完成情況、下周計(jì)劃",index = 4)
private String detail;
@ExcelProperty(value = "任務(wù)描述",index = 5)
private String taskDescription;
public WeeklyExportDto() {
}
public String getGroupName() {
return groupName;
}
public void setGroupName(String groupName) {
this.groupName = groupName;
}
public String getProjectName() {
return projectName;
}
public void setProjectName(String projectName) {
this.projectName = projectName;
}
public String getWeeklyTaskName() {
return weeklyTaskName;
}
public void setWeeklyTaskName(String weeklyTaskName) {
this.weeklyTaskName = weeklyTaskName;
}
public String getDutyUserName() {
return dutyUserName;
}
public void setDutyUserName(String dutyUserName) {
this.dutyUserName = dutyUserName;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
public String getTaskDescription() {
return taskDescription;
}
public void setTaskDescription(String taskDescription) {
this.taskDescription = taskDescription;
}
}
6限府、主程序
數(shù)據(jù)結(jié)構(gòu)說明：
待導(dǎo)出的數(shù)據(jù)結(jié)構(gòu)如下：
---有多個(gè)分組：List<WeeklyGroupRes>
----一個(gè)分組下，有多個(gè)項(xiàng)目： List<WeeklyProjectDto> weeklyProjectList = weeklyGroupRes.getWeeklyProjectList();
----一個(gè)項(xiàng)目下胁勺，有多個(gè)任務(wù)： List<WeeklyItemDto> itemDtoList = weeklyProjectDto.getItemDtoList();同一個(gè)分組世澜，需合并單元格；同一個(gè)項(xiàng)目署穗，需合并單元格寥裂。
@GetMapping("/export")
public boolean exportExcel( HttpServletResponse response) {
// 先從數(shù)據(jù)庫 獲取 需要導(dǎo)出的數(shù)據(jù)（此處自查）
List<WeeklyGroupRes> dataList = new ArrayList<>();
// mergeList 用于存放合并單元格的配置信息
// int[] 數(shù)據(jù)存放4個(gè)參數(shù)嵌洼，分別對應(yīng)merge方法的參數(shù)
// (int firstRow, int lastRow, int firstCol, int lastCol)
List<int[]> mergeLis =new ArrayList<>();
// 用于存放 寫入Excel的數(shù)據(jù)
List<WeeklyExportDto> resultList = new ArrayList<>();
// 用于生成merge
int lastRow = 0;
// 遍歷處理需要導(dǎo)出的數(shù)據(jù),下面就是小編對數(shù)據(jù)結(jié)構(gòu)的遍歷處理，可以忽略
for (WeeklyGroupRes weeklyGroupRes : dataList) {
int[] groupInt = new int[4];
groupInt[0] = lastRow + 1;
List<WeeklyProjectDto> weeklyProjectList = weeklyGroupRes.getWeeklyProjectList();
for (WeeklyProjectDto weeklyProjectDto : weeklyProjectList) {
int[] projectInt = new int[4];
projectInt[0] = lastRow + 1;
List<WeeklyItemDto> itemDtoList = weeklyProjectDto.getItemDtoList();
for (WeeklyItemDto weeklyItemDto : itemDtoList) {
String detail = thisComplete + weeklyItemDto.getComplete()
+ "\n" + nextPlan + weeklyItemDto.getPlan();
Task task = taskMap.get(weeklyItemDto.getTaskId());
WeeklyExportDto exportDto = new WeeklyExportDto();
exportDto.setGroupName(weeklyGroupRes.getMemberGroupName());
exportDto.setProjectName(weeklyProjectDto.getProjectName());
exportDto.setWeeklyTaskName(weeklyItemDto.getWeeklyTaskName());
exportDto.setDutyUserName(weeklyItemDto.getUserNickName());
exportDto.setDetail(detail);
exportDto.setTaskDescription(task.getDescription());
resultList.add(exportDto);
}
lastRow = lastRow + itemDtoList.size();
projectInt[1] = lastRow;
projectInt[2] = 1;
projectInt[3] = 1;
if (projectInt[0] != projectInt[1]) {
mergeLis.add(projectInt);
}
}
groupInt[1] = lastRow;
groupInt[2] = 0;
groupInt[3] = 0;
if (groupInt[0] != groupInt[1]) {
mergeLis.add(groupInt);
}
}
// 設(shè)置各種參數(shù)封恰，用于導(dǎo)出Excel
EasyExcelParams easyExcelParams = new EasyExcelParams();
easyExcelParams.setSheetName("導(dǎo)出Excel");
easyExcelParams.setResponse(response);
easyExcelParams.setData(resultList);
easyExcelParams.setDataModelClazz(WeeklyExportDto.class);
easyExcelParams.setNeedHead(false);
easyExcelParams.setMergeList(mergeLis);
easyExcelParams.setWriteHandler(new MyWriteHandler());
try {
EasyExcelUtil.exportExcel2007Format(easyExcelParams);
} catch (IOException e) {
e.printStackTrace();
throw new HanCloudsException(CommonErrorCode.INTERNAL_SERVER_ERROR.getErrorCode(),
"Export failed. Please try again");
}
return true;
}
7麻养、運(yùn)行程序，效果如下：
8诺舔、導(dǎo)出的Excel效果
?
