目录
介绍
Excel导出是我们在项目中经常使用到的功能，市面上成熟的导出工具包用起来比较的繁琐，而且定制化不高，在我们想自己编写一些特殊功能时不太好用，因此决定自己写一个简单的基于注解的Excel多Sheet导出。
在设计Excel导出@ExcelField注解之前，我们项目中也是用到了Swagger工具注解，上面包含了字段的说明标题，因此在编写自定义注解时省略了标题属性，通过直接获取swagger的@ApiModelProperty的字段说明来获取字段的标题，小伙伴们可以通过自己添加@ExcelField注解的自定义属性来替代。
代码
jar pom
本导出工具类是基于阿帕奇的POI5.1.1版本，pom jar描述如下：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.1.0</version>
</dependency>
使用流程为在导出实体类上添加导出注解@ExcelField,设置是否导出数据，或是否是模板导出属性，之后调用下载发放即可，上代码
注解
package com.pride.sun.common;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel自定义注解
*
* @author lpw
* @date 2020-04-19
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFiled {
/**
* 是否是导出属性
*
* @return
*/
boolean isExport() default true;
/**
* 是否是导入属性
*
* @return
*/
boolean isImport() default true;
/**
* 是否是父ID属性
*
* @return
*/
boolean isPId() default false;
}工具类
package com.pride.sun.common;
import com.pride.sun.base.BaseBean;
import com.pride.sun.result.ResultForm;
import io.swagger.annotations.ApiModel;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.minbox.framework.web.response.ResponseWrapper;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.annotation.Annotation;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Excel工具类
*
* @param <T>
* @author lpw
*/
public class ExcelUtils<T extends BaseBean> {
/**
* 行高
*/
private int rowHeight = 400;
/**
* 列宽
*/
private int colWidth = 3000;
/**
* 工作页
*/
private Workbook workbook = new HSSFWorkbook();
/**
* 默认标题
*/
private String defaultTitle;
/**
* sheet页集合
*/
private List<MySheet> sheetList = new ArrayList<>();
/**
* Class类工具
*/
private ClassUtils util = new ClassUtils();
public ExcelUtils() {
}
public ExcelUtils(String title) {
defaultTitle = title;
}
public ExcelUtils(int rowHeight, int colWidth, String title) {
this.rowHeight = rowHeight;
this.colWidth = colWidth;
this.defaultTitle = title;
}
/**
* 默认文档设置文档
*/
private void initWorkbook() {
if (CollectionUtils.isNotEmpty(sheetList)) {
if (StringUtils.isEmpty(defaultTitle)) {
defaultTitle = getDefaultTitle(sheetList.get(0));
}
sheetList.forEach(v -> {
//设置默认标题行
if (StringUtils.isEmpty(v.getSheetTitle())) {
v.setSheetTitle(getDefaultTitle(v));
}
v.parseFieldAnnotation();
// 创建工作页
v.setSheet(workbook.createSheet(v.getSheetTitle()));
// 设置默认列宽
v.getSheet().setDefaultColumnWidth(colWidth);
//设置冻结行
v.getSheet().createFreezePane(0, 2);
//标题样式
titleStyleSetting(v);
});
}
}
/**
* 加载生成Excel
*
* @throws Exception
*/
public ResultForm initAndDownload() throws Exception {
initWorkbook();
createExcel();
downloadExcel();
return new ResultForm(ResultForm.SUCCESS, "导出成功");
}
/**
* 导出Excel
*
* @param dataList
* @param <E>
*/
public static <E extends BaseBean> ResultForm exportExcel(List<E> dataList, Class<E> eClass) {
return exportExcel(dataList, eClass, null);
}
/**
* 导出Excel
*
* @param dataList
* @param <E>
*/
public static <E extends BaseBean> ResultForm exportExcel(List<E> dataList, Class<E> eClass, String title) {
try {
if (eClass == null) {
return new ResultForm("未指定类对象");
}
if (CollectionUtils.isNotEmpty(dataList)) {
ExcelUtils<E> excel = new ExcelUtils<>();
excel.defaultTitle = title;
excel.addSheet(eClass, title, dataList, false);
return excel.initAndDownload();
}
return new ResultForm(ResultForm.TIP, "未查到需要导出的数据");
} catch (Exception e) {
return new ResultForm(e);
}
}
/**
* 下载模板
*
* @param eClass
* @param <E>
*/
public static <E extends BaseBean> ResultForm downLoadModel(Class<E> eClass) {
return downLoadModel(eClass, null);
}
/**
* 下载模板
*
* @param eClass
* @param <E>
*/
public static <E extends BaseBean> ResultForm downLoadModel(Class<E> eClass, String title) {
try {
if (eClass != null) {
ExcelUtils<E> excel = new ExcelUtils<>();
excel.defaultTitle = title;
excel.addSheet(eClass, title, null, true);
return excel.initAndDownload();
}
return new ResultForm(ResultForm.ERROR, "类对象不能为空");
} catch (Exception e) {
return new ResultForm(e);
}
}
/**
* 添加sheet页
*
* @param eClass
* @param sheetTitle
* @param dataList
* @param <E>
*/
public <E extends BaseBean> MySheet addSheet(Class<E> eClass, String sheetTitle, List<E> dataList, Boolean isDownLoad) {
MySheet<E> mySheet = new MySheet<>();
mySheet.setSheetTitle(sheetTitle);
mySheet.setSheetClass(eClass);
mySheet.setDataList(dataList);
mySheet.setDownloadMode(isDownLoad);
sheetList.add(mySheet);
return mySheet;
}
/**
* 获取类标题
*
* @param mySheet
* @return
*/
private String getDefaultTitle(MySheet mySheet) {
Annotation[] ans = mySheet.getSheetClass().getAnnotations();
for (Annotation temp : ans) {
if (temp instanceof ApiModel) {
return ((ApiModel) temp).description();
}
}
return null;
}
/**
* 标题、列名相关设置
*/
private void titleStyleSetting(MySheet mySheet) {
List<Map<String, Object>> colList = mySheet.getColList();
Sheet sheet = mySheet.getSheet();
Row topRow = createRow(mySheet, (short) 600);
//合并标题行
mergedRegion(sheet, 0, 0, 0, colList.size() - 1);
//大标题样式
CellStyle topCellStyle = createCellStyle();
topCellStyle.setFont(createFont(null, (short) 0, (short) 0));
topCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
topCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Cell cell = createCell(topRow, 0, setBorder(topCellStyle));
cell.setCellValue(mySheet.getSheetTitle());
Row textRow = createRow(mySheet, (short) 0);
//列头标题样式
CellStyle titleCellStyle = createCellStyle();
titleCellStyle.setFont(createFont(null, (short) 0, (short) 0));
titleCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置列样式
for (int i = 0; i < colList.size(); i++) {
Map<String, Object> fieldMap = colList.get(i);
String colValue = String.valueOf(fieldMap.get("text"));
sheet.setColumnWidth(i, getColumnWidth(colValue));
Cell cell1 = createCell(textRow, i, setBorder(titleCellStyle));
cell1.setCellValue(colValue);
addColStyle(mySheet.getStyleList(), null, (short) 0, Short.parseShort(fieldMap.get("color").toString()));
}
}
/**
* 获取列宽
*
* @param value
* @return
*/
private Integer getColumnWidth(String value) {
if (StringUtils.isNotEmpty(value)) {
try {
int byteNum = value.getBytes("GBK").length;
int width = byteNum * 350;
return width > colWidth ? width : colWidth;
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
return colWidth;
}
/**
* 创建字体对象
*
* @param fontName 字体库名称
* @param fontSize 字体大小-传0默认14
* @return
*/
private Font createFont(String fontName, short fontSize, short color) {
Font font = workbook.createFont();
if (StringUtils.isEmpty(fontName)) {
fontName = "宋体";
}
if (fontSize == 0) {
fontSize = (short) 12;
}
if (color == 0) {
color = 0x08;
}
//设置为宋体字
font.setFontName("宋体");
//设置字体大小
font.setFontHeightInPoints(fontSize);
font.setColor(color);
return font;
}
/**
* 创建列样式
*
* @return
*/
private CellStyle createCellStyle(short... alignments) {
CellStyle style = workbook.createCellStyle();
short alignment = HorizontalAlignment.CENTER_SELECTION.getCode(), vertical = VerticalAlignment.CENTER.getCode();
if (alignments.length > 0) {
alignment = alignments[0];
if (alignments.length > 1) {
vertical = alignments[1];
}
}
//水平居中
style.setAlignment(HorizontalAlignment.forInt(alignment));
//垂直居中
style.setVerticalAlignment(VerticalAlignment.forInt(vertical));
return style;
}
/**
* 合并行、列
*
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
private void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
if (firstRow > lastRow || firstCol > lastCol) {
return;
}
// 合并行
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(region);
}
/**
* 创建单元格
*
* @param row
* @param index
* @param style
* @return
*/
private Cell createCell(Row row, int index, CellStyle style) {
Cell cell = row.createCell(index);
if (style != null) {
cell.setCellStyle(style);
}
return cell;
}
/**
* 创建行
*
* @return
*/
private Row createRow(MySheet mySheet, short rowHeight) {
Row row = mySheet.getSheet().createRow(mySheet.getRowIndex());
if (rowHeight == 0) {
rowHeight = (short) this.rowHeight;
}
row.setHeight(rowHeight);
mySheet.setRowIndex(mySheet.getRowIndex() + 1);
return row;
}
/**
* 创建每一列的样式
*
* @param fontName
* @param fontSize
* @param color
* @param alignments
*/
private void addColStyle(List<CellStyle> styleList, String fontName, short fontSize, short
最低0.47元/天 解锁文章
1133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
