企业项目中导出功能很常见,不同的项目不同的开发者能写出不同的实现,今天想把这个功能写成一个通用的工具类。
代码实现
1.创建ExcelUtil工具类
public class ExcelUtil<T> {
private Workbook workbook = null;
//导出excel版本
private Version version;
public ExcelUtil(Version version){
if(version == Version.EXCEL2003){
workbook = new HSSFWorkbook();
}else if(version == Version.EXCEL2007){
workbook = new XSSFWorkbook();
}
this.version = version;
}
/**
* 以默认样式导出
**/
public Workbook exportDefaultStyle(String title,List<T> data){
CellStyle titleStyle = createCellStyle(null, IndexedColors.WHITE, true,
false);
CellStyle headStyle = createCellStyle(null, IndexedColors.LIGHT_GREEN, true,
false);
CellStyle bodyStyle = createCellStyle(null, IndexedColors.LIGHT_YELLOW, true,
true);
return export(title, data, titleStyle, headStyle, bodyStyle);
}
public Workbook exportDefaultStyle(List<T> data){
return exportDefaultStyle("", data);
}
public static Workbook createEmptyWorkbook(Version version,String tips){
Workbook workbook = null;
if(version == Version.EXCEL2003){
workbook = new HSSFWorkbook();
}else{
workbook = new XSSFWorkbook();
}
if(StringUtils.isNotBlank(tips)){
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(tips);
//设置列宽为 tips中文列宽
sheet.setColumnWidth(0, (int)(tips.length() * BASE_CHINESE));
}
return workbook;
}
public CellStyle createCellStyle(Font font,IndexedColors background,boolean center,boolean wrapText){
CellStyle createCellStyle = workbook.createCellStyle();
if(font != null){
createCellStyle.setFont(font);
}
createCellStyle.setFillForegroundColor(background.getIndex());
createCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
createCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
createCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
createCellStyle.setBorderRight(CellStyle.BORDER_THIN);
createCellStyle.setBorderTop(CellStyle.BORDER_THIN);
if(center){//水平居中,垂直居中
createCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
createCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
createCellStyle.setWrapText(wrapText);
return createCellStyle;
}
public Font createCellFont(String family,IndexedColors color,short size,boolean b){
Font createFont = workbook.createFont();
createFont.setCharSet(Font.DEFAULT_CHARSET);
createFont.setColor(color.getIndex());
createFont.setFontName(family);
// createFont.setFontHeight(size);
//字体使用点数
createFont.setFontHeightInPoints(size);
if(b){
createFont.setBoldweight((short)700);
}
return createFont;
}
public Workbook export(String title,List<T> data,CellStyle titleStyle,CellStyle headStyle,CellStyle bodyStyle){
if(data == null || data.size() == 0){
//无数据直接反回空
return null;
}
//取得当前需要导出的类型
T dataType = data.get(0);
Class<? extends Object> clazz = dataType.getClass();
ExcelExport excelExport = clazz.getAnnotation(ExcelExport.class);
if(excelExport == null){
throw new ConfigException("配置错误,需要在目标类加注解 ExcelExport 才可导出");
}
int sheetMaxRow = -1;
if(excelExport.sheetMaxRow() == -1 && version == Version.EXCEL2003){
//设置配置为最大行数
sheetMaxRow = 60000;
}
try {
List<ColumnConfig> columnConfigs = parseColumnConfig(clazz,true);
//计算数据是否超量,是否需要创建多个 sheet
List<Sheet> sheets = new ArrayList<Sheet>();
if(sheetMaxRow == -1 || data.size() <= sheetMaxRow){
//只会创建一个 sheet
//使用标题做为 sheet 名称会有问题如有特殊字符
// String sheetName = title;
// if(StringUtils.isBlank(sheetName)){
// sheetName = "全部数据";
// }
Sheet createSheet = workbook.createSheet("全部数据");
sheets.add(createSheet);
}else{
int sheetCount = (data.size() -1 ) / sheetMaxRow + 1;
for (int i = 0; i < sheetCount; i++) {
// String sheetName = title+"_part"+i;
// if(StringUtils.isBlank(title)){
// sheetName = "部分数据_part"+i;
// }
Sheet createSheet = workbook.createSheet("部分数据_part"+i);
sheets.add(createSheet);
}
}
//正式添加数据
if(sheets.size() == 1){ //添加全部数据到一张 sheet 页中,如果只有一张 sheet 页的话
Sheet sheet = sheets.get(0);
int startRow = createSheetTitle(title,titleStyle,excelExport,columnConfigs,sheet);
insertDataToSheet(sheet,data,columnConfigs,startRow,headStyle,bodyStyle,excelExport);
}else{
for (int i=0;i<sheets.size();i++) {
Sheet sheet = sheets.get(i);
//如果有标题,添加标题
int startRow = createSheetTitle(title, titleStyle, excelExport, columnConfigs, sheet);
//复制截断的数据,到数据表 sheet 页
int startDataIndex = i * sheetMaxRow;
int endDataIndex = (i + 1) * sheetMaxRow;
if(endDataIndex > data.size()){
endDataIndex = data.size();
}
List<T> partData = new ArrayList<T>();
for (int j = startDataIndex; j < endDataIndex; j++) {
partData.add(data.get(j));
}
insertDataToSheet(sheet,partData,columnConfigs,startRow,headStyle,bodyStyle,excelExport);
}
}
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}
return workbook;
}
//获取导出列头
private static List<ColumnConfig> parseColumnConfig(Class<? extends Object> clazz,boolean readWrite) throws IntrospectionException,
ConfigException, NoSuchFieldException, SecurityException {
//获取列配置,所有需要导出的类,最后应该都是从 Object 继承
BeanInfo beanInfo = Introspector.getBeanInfo(clazz, Object.class);
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
if(propertyDescriptors == null || propertyDescriptors.length == 0 ){
//必须要有属性配置
throw new ConfigException("bean 和其父类, 必须至少包含一个属性");
}
//获取 bean 上所有的列配置
List<ColumnConfig> columnConfigs = new ArrayList<ColumnConfig>();
for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
Method readMethod = propertyDescriptor.getReadMethod();
Method writeMethod = propertyDescriptor.getWriteMethod();
String propertyName = propertyDescriptor.getName();
Class<?> propertyType = propertyDescriptor.getPropertyType();
if(!typeSupport(propertyType)){
throw new ConfigException("不支持的类型:"+propertyType);
}
//只导出属性可读的属性,没有 get 方法的属性不进行导出
if((readMethod != null && readWrite) || (writeMethod != null && !readWrite)){
//先从属性列上获取配置,如果属性列上没有,就从读方法上获取,并覆盖属性列上的配置
ColumnConfig columnConfig = new ColumnConfig(propertyName, readMethod, writeMethod);
columnConfig.setDataType(propertyType);
Field propertyField = null;
Class<?> currentClass = clazz;
while(currentClass != Object.class && propertyField == null){
try{
propertyField = currentClass.getDeclaredField(propertyName);
}catch(NoSuchFieldException e){
currentClass = currentClass.getSuperclass();
}
}
if(propertyField == null){
throw new NoSuchFieldException("没有此属性:"+propertyName);
}
ExcelColumn excelColumn = propertyField.getAnnotation(ExcelColumn.class);
if(excelColumn != null){
columnConfig.config(excelColumn.value(), excelColumn.width(),excelColumn.charWidth(),excelColumn.pxWidth(), excelColumn.index(), excelColumn.hidden(), excelColumn.pattern(),excelColumn.chineseWidth());
}
//使用方法上的配置,覆盖属性上的配置
ExcelColumn methodExcelColumn = null;
if(readWrite){
//从读方法上覆盖配置
methodExcelColumn = readMethod.getAnnotation(ExcelColumn.class);
}else{
//从写方法上覆盖配置
methodExcelColumn = writeMethod.getAnnotation(ExcelColumn.class);
}
if(methodExcelColumn != null){
columnConfig.config(methodExcelColumn.value(), methodExcelColumn.width(),excelColumn.charWidth(),excelColumn.pxWidth(), methodExcelColumn.index(), methodExcelColumn.hidden(), methodExcelColumn.pattern(),excelColumn.chineseWidth());
}
// 只有配置了 ExcelColumn 的属性才可进行导入导出
if(excelColumn != null || methodExcelColumn != null){
columnConfigs.add(columnConfig);
}
}
}
//对导出的属性配置进行排序
Collections.sort(columnConfigs);
return columnConfigs;
}
private static int createSheetTitle(String title, CellStyle titleStyle, ExcelExport excelExport, List<ColumnConfig> columnConfigs, Sheet sheet) {
int startRow = 0;
if(StringUtils.isNotBlank(title)){
Row titleRow = sheet.createRow(startRow++);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(title);
if(titleStyle != null){
titleCell.setCellStyle(titleStyle);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnConfigs.size() - 1));
short titleRowHeight = excelExport.titleRowHeight();
titleRowHeight = (short) (titleRowHeight * BASE_HEIGHT_1_PX);
titleRow.setHeight(titleRowHeight);
}
return startRow;
}
private static <T> void insertDataToSheet(Sheet sheet,List<T> partData, List<ColumnConfig> columnConfigs,int startRow,CellStyle headStyle,CellStyle bodyStyle,ExcelExport excelExport) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException{
Row headRow = sheet.createRow(startRow++);
headRow.setHeight((short)(excelExport.headRowHeight() * BASE_HEIGHT_1_PX));
//创建标题列
for (int i=0;i<columnConfigs.size();i++) {
ColumnConfig columnConfig = columnConfigs.get(i);
String chinese = columnConfig.getChinese();
Cell headCell = headRow.createCell(i);
headCell.setCellValue(chinese);
if(headStyle != null){
headCell.setCellStyle(headStyle);
}
}
//创建数据列
for (int i = 0; i < partData.size(); i++) {
Row bodyRow = sheet.createRow(startRow ++);
bodyRow.setHeight((short)(excelExport.bodyRowHeight() * BASE_HEIGHT_1_PX));
T dataItem = partData.get(i);
for (int j=0;j<columnConfigs.size();j++) {
ColumnConfig columnConfig = columnConfigs.get(j);
Method readMethod = columnConfig.getReadMethod();
Cell bodyCell = bodyRow.createCell(j);
if(bodyStyle != null){
bodyCell.setCellStyle(bodyStyle);
}
Object cellData = readMethod.invoke(dataItem);
Class<?> dataType = columnConfig.getDataType();
if(dataType == Date.class){
//获取日期对象数据
Date cellDataReal = null;
if(cellData != null){
cellDataReal = (Date)cellData;
}
//如果是日期类型,则调用转换规则进行转换
String pattern = columnConfig.getPattern();
if(StringUtils.isBlank(pattern)){
//如果是空格式,直接设置日期数据
bodyCell.setCellValue(cellDataReal);
}else{
if(cellDataReal != null){
bodyCell.setCellValue(DateFormatUtils.format(cellDataReal,pattern));
}
}
}else if(dataType == Boolean.class || dataType == boolean.class){
//必须有值
boolean cellBooleanReal = Boolean.parseBoolean(ObjectUtils.toString(cellData));
if(cellBooleanReal){
bodyCell.setCellValue("是");
}else{
bodyCell.setCellValue("否");
}
}else{
bodyCell.setCellValue(ObjectUtils.toString(cellData));
}
}
}
//设置列宽
boolean autoWidth = excelExport.autoWidth();
if(autoWidth){
//自动列宽后使用两倍自动列宽
for (int i=0;i<columnConfigs.size();i++) {
sheet.autoSizeColumn(i);
ColumnConfig columnConfig = columnConfigs.get(i);
if(columnConfig.isChineseWidth()){
int width = sheet.getColumnWidth(i);
// 宽度设置为原来两倍,并且加一个中文字宽度
int width_2 = (int) (width * 2 + 1 * BASE_CHINESE);
//解决最大宽度超出限制问题
if(width > 65280){
width = 65280;
}
sheet.setColumnWidth(i, width_2);
}
}
}else{
//宽度配置策略 如果没有配置任何宽度,则取标题中文字宽度,如果有配置,则使用配置
for (int i = 0; i < columnConfigs.size(); i++) {
ColumnConfig columnConfig = columnConfigs.get(i);
//增加列宽配置策略
int width = columnConfig.getWidth();
int charWidth = columnConfig.getCharWidth();
int pxWidth = columnConfig.getPxWidth();
int finalWidth = -1;
if(width == -1 && charWidth == -1 && pxWidth == -1){
//没有配置任何宽度,使用标题中文字宽度
finalWidth = (int) (columnConfig.getChinese().length() * BASE_CHINESE);
}else{
if(width != -1){
finalWidth = width;
}else if(charWidth != -1){
finalWidth = (int) (charWidth * BASE_CHINESE);
}else{
finalWidth = (int) (pxWidth * BASE_WIDTH_1_PX);
}
}
// if(width < columnConfig.getChinese().length()){
// //如果默认宽度是小于了中文字的宽度,则取中文字的宽度
// width = (int) (columnConfig.getChinese().length() * BASE_CHINESE);
// }
// 解决最大宽度超出限制问题
if(finalWidth > 65280){
finalWidth = 65280;
}
sheet.setColumnWidth(i,finalWidth);
//增加列宽配置策略
}
}
//隐藏列配置
for (int i = 0; i < columnConfigs.size(); i++) {
ColumnConfig columnConfig = columnConfigs.get(i);
boolean hidden = columnConfig.isHidden();
sheet.setColumnHidden(i,hidden);
}
}
}
2.自定义注解
2.1注解要导出的类
该注解写在需要导出的类上
package com.william.annotation;
import com.william.contants.Version;
import java.lang.annotation.*;
/**
* @Auther: williamdream
* @Date: 2019/7/29 18:20
* @Description:
*/
@Target(value= ElementType.TYPE)
@Retention(value= RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelExport {
/**
* 功能:导出excel版本,默认导出 2007 版本 <br/>
* @return
*/
Version version() default Version.EXCEL2007;
/**
* 功能:最顶部的标题行高度,需要设置 title 才能使其生效 <br/>
* @return
*/
short titleRowHeight() default 40;
/**
* 功能: 头标题行高度,以像素为单位<br/>
* @return
*/
short headRowHeight() default 30;
/**
* 功能:内容行高度,以像素为单位 <br/>
* @return
*/
short bodyRowHeight() default 25;
/**
* 功能:是否自动宽度,默认为 true <br/>
* @return
*/
boolean autoWidth() default true;
/**
* 功能:一个sheet 页的最大记录数,默认是不限制的,如果是 2003 版本,限制为 60000 行 <br/>
* @return
*/
int sheetMaxRow() default -1;
}
枚举类:指定excel版本
package com.william.contants;
/**
* @Auther: william
* @Date: 2019/7/29 18:22
* @Description:
*/
public enum Version {
EXCEL2007(2007),EXCEL2003(2003);
private int version;
private Version(int version){
this.version = version;
}
public int getVersion(int version) {
return this.version;
}
}
2.2注解需要导出的属性
package com.william.annotation;
import java.lang.annotation.*;
/**
* @Auther: williamdream
* @Date: 2019/7/29 18:26
* @Description:
*/
@Target(value={ElementType.METHOD,ElementType.FIELD})
@Retention(value= RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
/**
* 功能:导出,导入的单元格标题 ,必填<br/>
* @return
*/
String value() ;
/**
* 功能:导入,导出时的索引配置,从 0 开始,必须提供 <br/>
* @return
*/
int index();
/**
*
* 功能: 列的宽度配置,如果这里有配置,则使用这里的配置,否则使用自动宽度(如果配置为 true 的话,为 false 不设置)<br/>
* 注:使用 excel 的宽度设置,一个中文字对应 2 * 256 长度单位
* @return
*/
int width() default -1;
/**
* 功能:使用字符宽度,一个中文字填写 1 <br/>
* @return
*/
int charWidth() default -1;
/**
* 功能:使用像素宽度, 1 像素填写 1 <br/>
* @return
*/
int pxWidth() default -1;
/**
*
* 功能:由于自动宽度对中文支持不太好,所以这里加个中文的自动宽度支持,这个只在自动宽度设置为 true 时生效<br/>
*/
boolean chineseWidth() default false;
/**
* 功能: 当前列是否隐藏 默认 false<br/>
* @return
*/
boolean hidden() default false;
/**
* 功能: 时间格式化,默认 yyyy-MM-dd <br/>
* @return
*/
String pattern() default "yyyy-MM-dd";
}
列配置
package com.william;
import java.lang.reflect.Method;
import org.apache.commons.lang.StringUtils;
/**
* @Auther: williamdream
* @Date: 2019/7/29 18:30
* @Description: 列的配置
*/
public class ColumnConfig implements Comparable<ColumnConfig>{
private boolean hidden;
private boolean chineseWidth;
private String pattern;
private String chinese;
private int width;
private int charWidth;
private int pxWidth;
private int index;
//属性名,必须不为空
private String propertyName;
private Method readMethod;
private Method writeMethod;
private Class<?> dataType;
public ColumnConfig(String propertyName, Method readMethod, Method writeMethod) {
super();
this.propertyName = propertyName;
this.readMethod = readMethod;
this.writeMethod = writeMethod;
}
/**
*
* 功能:配置所有的配置,如果传入值为非默认值,则配置 <br/>
* @param chinese
* @param width
* @param index
* @param hidden
* @param pattern
*/
public void config(String chinese,int width,int charWidth,int pxWidth,int index,boolean hidden,String pattern,boolean chineseWidth){
if(StringUtils.isNotBlank(chinese)){
this.chinese = chinese;
}
this.width = width;
this.charWidth = charWidth;
this.pxWidth = pxWidth;
if(index != -1){
this.index = index;
}
this.hidden = hidden;
if(StringUtils.isNotBlank(pattern)){
this.pattern = pattern;
}
this.chineseWidth = chineseWidth;
}
@Override
public int compareTo(ColumnConfig o) {
if(this.index != -1 && o.index != -1){
return this.index - o.index;
}
return this.propertyName.compareTo(o.propertyName);
}
//get set ......
}
使用方法
@Component
public class ExportService {
public <T> InputStream export(Class<T> clazz , List<T> data, String title) throws IOException {
ExcelUtil<T> excelUtil = new ExcelUtil<T>(Version.EXCEL2007);
excelUtil.exportDefaultStyle(title,data);
InputStream inputStream = excelUtil.toInputStream();
return inputStream;
}
}
InputStream batchExport = exportService.export(UserInfoVo.class, userInfoList, "导出用户_标题");