EasyPoi工具类
- 文档 http://doc.wupaas.com/docs/easypoi/easypoi-1c2cp5rf3hnqv
- Gitee项目地址 https://gitee.com/lemur/easypoi
Maven依赖
<!--EasyPoi相关依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.4.0</version>
</dependency>
ExcelExportService
public class ExcelExportService extends BaseExportService {
/**
* 最大行数,超过自动多Sheet
*/
private static int MAX_NUM = 60000;
protected int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook,
List<ExcelExportEntity> excelParams) {
int rows = 0, fieldLength = getFieldLength(excelParams);
if (entity.getTitle() != null) {
rows += createTitle2Row(entity, sheet, workbook, fieldLength);
}
createHeaderRow(entity, sheet, rows, excelParams, 0);
rows += getRowNums(excelParams, true);
if (entity.isFixedTitle()) {
sheet.createFreezePane(0, rows, 0, rows);
}
return rows;
}
/**
* 创建表头
*/
private int createHeaderRow(ExportParams title, Sheet sheet, int index,
List<ExcelExportEntity> excelParams, int cellIndex) {
Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
int rows = getRowNums(excelParams, true);
row.setHeight(title.getHeaderHeight());
Row listRow = null;
if (rows >= 2) {
listRow = sheet.createRow(index + 1);
listRow.setHeight(title.getHeaderHeight());
}
int groupCellLength = 0;
CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
ExcelExportEntity entity = excelParams.get(i);
// 加入换了groupName或者结束就，就把之前的那个换行
if (StringUtils.isBlank(entity.getGroupName()) || i == 0 || !entity.getGroupName().equals(excelParams.get(i - 1).getGroupName())) {
if (groupCellLength > 1) {
sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex - groupCellLength, cellIndex - 1));
}
groupCellLength = 0;
}
if (StringUtils.isNotBlank(entity.getGroupName())) {
createStringCell(row, cellIndex, entity.getGroupName(), titleStyle, entity);
createStringCell(listRow, cellIndex, entity.getName(), titleStyle, entity);
groupCellLength++;
} else if (StringUtils.isNotBlank(entity.getName())) {
createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
}
if (entity.getList() != null) {
// 保持原来的
int tempCellIndex = cellIndex;
cellIndex = createHeaderRow(title, sheet, rows == 1 ? index : index + 1, entity.getList(), cellIndex);
List<ExcelExportEntity> sTitel = entity.getList();
if (StringUtils.isNotBlank(entity.getName()) && sTitel.size() > 1) {
PoiMergeCellUtil.addMergedRegion(sheet, index, index, tempCellIndex, tempCellIndex + sTitel.size() - 1);
}
cellIndex--;
} else if (rows > 1 && StringUtils.isBlank(entity.getGroupName())) {
createStringCell(listRow, cellIndex, "", titleStyle, entity);
PoiMergeCellUtil.addMergedRegion(sheet, index, index + rows - 1, cellIndex, cellIndex);
}
cellIndex++;
}
if (groupCellLength > 1) {
PoiMergeCellUtil.addMergedRegion(sheet, index, index, cellIndex - groupCellLength, cellIndex - 1);
}
return cellIndex;
}
/**
* 创建 表头改变
*/
public int createTitle2Row(ExportParams entity, Sheet sheet, Workbook workbook,
int fieldWidth) {
Row row = sheet.createRow(0);
row.setHeight(entity.getTitleHeight());
createStringCell(row, 0, entity.getTitle(),
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
for (int i = 1; i <= fieldWidth; i++) {
createStringCell(row, i, "",
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
}
PoiMergeCellUtil.addMergedRegion(sheet, 0, 0, 0, fieldWidth);
if (entity.getSecondTitle() != null) {
row = sheet.createRow(1);
row.setHeight(entity.getSecondTitleHeight());
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
createStringCell(row, 0, entity.getSecondTitle(), style, null);
for (int i = 1; i <= fieldWidth; i++) {
createStringCell(row, i, "",
getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
}
PoiMergeCellUtil.addMergedRegion(sheet, 1, 1, 0, fieldWidth);
return 2;
}
return 1;
}
public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet, String lastCol) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Excel export start ,class is {}", pojoClass);
LOGGER.debug("Excel version is {}",
entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
}
if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
try {
List<ExcelExportEntity> excelParams = new ArrayList<>();
// 得到所有字段
Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
String targetId = etarget == null ? null : etarget.value();
getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,
null, null);
//获取所有参数后,后面的逻辑判断就一致了
createSheetForMap(workbook, entity, excelParams, dataSet, lastCol);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
}
}
public void createSheetForMap(Workbook workbook, ExportParams entity,
List<ExcelExportEntity> entityList, Collection<?> dataSet, String lastCol) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Excel version is {}",
entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
}
if (workbook == null || entity == null || entityList == null || dataSet == null) {
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
super.type = entity.getType();
if (type.equals(ExcelType.XSSF)) {
MAX_NUM = 1000000;
}
if (entity.getMaxNum() > 0) {
MAX_NUM = entity.getMaxNum();
}
Sheet sheet;
try {
sheet = workbook.createSheet(entity.getSheetName());
} catch (Exception e) {
// 重复遍历,出现了重名现象,创建非指定的名称Sheet
sheet = workbook.createSheet();
}
insertDataToSheet(workbook, entity, entityList, dataSet, sheet, lastCol);
}
protected void insertDataToSheet(Workbook workbook, ExportParams entity,
List<ExcelExportEntity> entityList, Collection<?> dataSet,
Sheet sheet, String lastCol) {
try {
dataHandler = entity.getDataHandler();
if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {
needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
}
dictHandler = entity.getDictHandler();
i18nHandler = entity.getI18nHandler();
// 创建表格样式
setExcelExportStyler((IExcelExportStyler) entity.getStyle()
.getConstructor(Workbook.class).newInstance(workbook));
Drawing<?> patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
List<ExcelExportEntity> excelParams = new ArrayList<>();
if (entity.isAddIndex()) {
excelParams.add(indexExcelEntity(entity));
}
excelParams.addAll(entityList);
sortAllParams(excelParams);
int index = entity.isCreateHeadRows()
? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
int titleHeight = index;
setCellWith(excelParams, sheet);
setColumnHidden(excelParams, sheet);
short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);
setCurrentIndex(1);
Iterator<?> its = dataSet.iterator();
List<Object> tempList = new ArrayList<>();
while (its.hasNext()) {
Object t = its.next();
index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
tempList.add(t);
if (index >= MAX_NUM) {
break;
}
}
//加最后一行合计
int columnNum = 30;
if(sheet.getRow(0) != null) {
columnNum = sheet.getRow(0).getPhysicalNumberOfCells();
}
int rowNum = sheet.getLastRowNum();
int fieldWidth = columnNum - 1;
Row row;
if (!StringUtils.isNullOrEmpty(lastCol)) {
row = sheet.createRow(rowNum + 1);
sheet.getRow(index);
row.setHeight((short)400);
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
createStringCell(row, 0, lastCol, style, null);
for (int i = 1; i <= fieldWidth; i++) {
createStringCell(row, i, "", style, null);
}
PoiMergeCellUtil.addMergedRegion(sheet, rowNum + 1, rowNum + 1, 0, fieldWidth);
}
if (entity.getFreezeCol() != 0) {
sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
}
mergeCells(sheet, excelParams, titleHeight);
its = dataSet.iterator();
for (int i = 0, le = tempList.size(); i < le; i++) {
its.next();
its.remove();
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("List data more than max ,data size is {}",
dataSet.size());
}
// 发现还有剩余list 继续循环创建Sheet
if (dataSet.size() > 0) {
createSheetForMap(workbook, entity, entityList, dataSet,lastCol);
} else {
// 创建合计信息
addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
}
}
}
ExcelStyleUtil
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short GENERAL_FORMAT = (short) BuiltinFormats.getBuiltinFormat("General");
private static final short FONT_SIZE_TWELVE = 11;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook Workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 大标题样式
*
* @param color short
* @return CellStyle
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
*
* @param color short
* @return CellStyle
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--大标题样式
*
* @param workbook Workbook
* @return CellStyle
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, true));
return style;
}
/**
* 初始化--每列标题样式
*
* @param workbook Workbook
* @return CellStyle
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, false));
//背景色
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook Workbook
* @return CellStyle
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, false));
style.setDataFormat(GENERAL_FORMAT);
return style;
}
/**
* 基础样式
*
* @return CellStyle
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param isBold 是否加粗
* @return Font
*/
private Font getFont(Workbook workbook, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(ExcelStyleUtil.FONT_SIZE_TWELVE);
return font;
}
}
ExcelUtils
/**
* Excel 工具类，导出通用方法
* ExcelUtils
*/
@Slf4j
public class ExcelUtils {
/**
* 用实体导出excel
* @param list 导出数据集合
* @param title excel标题名称
* @param sheetName sheet页名称
* @param pojoClass 实体的类型
* @param fileName 导出的文件名
* @param isCreateHeader 是否生成表头 true 生成，false不生成
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
exportParams.setCreateHeadRows(isCreateHeader);
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 用实体导出excel
* @param list 导出数据集合
* @param title excel标题名称
* @param sheetName sheet页名称
* @param pojoClass 实体的类型
* @param fileName 导出的文件名
* @param isCreateHeader 是否生成表头 true 生成，false不生成
* @param response HttpServletResponse
*/
@Deprecated
public static void exportExcelXlsx(List<?> list, String title, String sheetName, Class<?> pojoClass,
String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* @param entity 表格标题属性
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
* @param lastCol 最后一行文字
*/
public static void exportExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet, String lastCol, HttpServletResponse response){
try {
//表头样式
entity.setStyle(ExcelStyleUtil.class);
//生成HSSFWorkbook样式的表格
Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet,lastCol);
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(entity.getTitle(),"UTF-8") + ".xls");
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());
} catch (Exception e) {
log.info("Excel导出异常：{}",e);
}
}
/**
* @param entity 表格标题属性
* @param pojoClass Excel对象Classzxc
* @param dataSet Excel对象数据List
* @param lastCol 最后一行文字
*/
public static void exportExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet, String lastCol, HttpServletResponse response, Class<? extends IExcelExportStyler> styleClass){
try {
//表头样式
entity.setStyle(styleClass);
//生成HSSFWorkbook样式的表格
Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet, lastCol);
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(entity.getTitle(),"UTF-8") + ".xlsx");
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());
} catch (Exception e) {
log.info("Excel导出异常：{}",e);
}
}
/**
* 多sheet页导出
* @param list 一个sheet页数据是一个map集合，多个map集合放入list中,其中map集合中key为"title"是，value为ExportParams类型，ExportParams中设置sheetName ，titleName
* key是"entity" ,value 为实体类类型,key是"data" value 为实体集合
* @param fileName xxx.xls
* @param response HttpServletResponse
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
/**
* 用List-map导出excel
* @param fileName 导出文件的名称
* @param title excel表题
* @param sheetName sheet页名称
* @param dataSet 导出数据的集合
* @param response HttpServletResponse
* @param excelHeader 表头和excel字段对应关系集合，key为字符串"name"时，value 是字段描述，key为字符串"key"是，value为字段名
*/
public static void exportExcel(String fileName, String title, String sheetName, List<Map<String, Object>> dataSet,
HttpServletResponse response, List<Map<String, String>> excelHeader)
{
ExportParams exportParams = new ExportParams(title, sheetName,ExcelType.HSSF);
exportParams.setCreateHeadRows(true);
List<ExcelExportEntity> entityList= new ArrayList<>();
for(Map<String, String> map:excelHeader){
ExcelExportEntity entity=new ExcelExportEntity(map.get("name"), map.get("key"));
entityList.add(entity);
}
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, dataSet);
downLoadExcel(fileName, response, workbook);
}
private static Workbook getWorkbook(ExcelType type, int size) {
if (ExcelType.HSSF.equals(type)) {
return new HSSFWorkbook();
} else if (size < USE_SXSSF_LIMIT) {
return new XSSFWorkbook();
} else {
return new SXSSFWorkbook();
}
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
log.info("下载异常：{}",e);
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
/**
* @param entity 表格标题属性
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
* @param lastCol 最后一行文字
*/
public static Workbook exportExcelWorkbook(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet, String lastCol){
//表头样式
entity.setStyle(ExcelStyleUtil.class);
//生成HSSFWorkbook样式的表格
Workbook workbook = getWorkbook(ExcelType.HSSF, dataSet.size());
new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet,lastCol);
return workbook;
}
}
导出示例
@GetMapping("/export")
public void export(HttpServletResponse response ) {
List<XXX> xXXList = xXXService.get();
ExportParams exportParams = new ExportParams("XXX导出", "XXX导出");
exportParams.setType(ExcelType.XSSF);
ExcelUtils.exportExcel(exportParams, XXX.class, xXXList , "", response);
442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
