手写一个简单通用的excel导出工具类

最新推荐文章于 2023-08-22 17:10:52 发布

william_cr7

最新推荐文章于 2023-08-22 17:10:52 发布

阅读量593

点赞数

分类专栏： Java # Spring Boot 文章标签： Spring Boot 导出 excel

本文链接：https://blog.csdn.net/CHINACR07/article/details/97646135

版权

Spring Boot 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

Java

7 篇文章 0 订阅

订阅专栏

企业项目中导出功能很常见，不同的项目不同的开发者能写出不同的实现，今天想把这个功能写成一个通用的工具类。

代码实现

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, "导出用户_标题");

william_cr7

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
手写一个简单通用的excel导出工具类

企业项目中导出功能很常见，不同的项目不同的开发者能写出不同的实现，今天想把这个功能写成一个通用的工具类。代码实现1.创建ExcelUtil工具类public class ExcelUtil<T> { private Workbook workbook = null; //导出excel版本 private Version version;...
复制链接

扫一扫