Java根据注解导出数据到Excel

た入场券 2022-05-10 06:44 205阅读 0赞

1.  开发背景win10专业版、eclipse4.7.3a、Junit5![70][]
2.  建立一个maven工程，pom文件如下：
    
        <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
          <modelVersion>4.0.0</modelVersion>
          <groupId>com.szcatic</groupId>
          <artifactId>export</artifactId>
          <version>0.0.1-SNAPSHOT</version>
          <dependencies>
          	
        	<dependency>
        	    <groupId>org.junit.jupiter</groupId>
        	    <artifactId>junit-jupiter-engine</artifactId>
        	    <version>5.2.0</version>
        	    <scope>test</scope>
        	</dependency>
        	<dependency>
        	    <groupId>org.junit.platform</groupId>
        	    <artifactId>junit-platform-runner</artifactId>
        	    <version>1.2.0</version>
        	    <scope>test</scope>
        	</dependency>
        	
        	<dependency>
        	    <groupId>org.apache.poi</groupId>
        	    <artifactId>poi</artifactId>
        	    <version>3.17</version>
        	    <exclusions>
        	    	<exclusion>
        	    		<groupId>org.apache.commons</groupId>
        	    		<artifactId>commons-collections4</artifactId>
        	    	</exclusion>
        	    </exclusions>
        	</dependency>
        	<dependency>
        	    <groupId>org.apache.commons</groupId>
        	    <artifactId>commons-collections4</artifactId>
        	    <version>4.2</version>
        	    <exclusions>
        	    	<exclusion>
        	    		<groupId>junit</groupId>
        	    		<artifactId>junit</artifactId>
        	    	</exclusion>
        	    </exclusions>
        	</dependency>
        	<dependency>
        	    <groupId>org.apache.poi</groupId>
        	    <artifactId>poi-ooxml</artifactId>
        	    <version>3.17</version>
        	</dependency>
        	
        	<dependency>
        	    <groupId>com.google.guava</groupId>
        	    <artifactId>guava</artifactId>
        	    <version>26.0-jre</version>
        	</dependency>
        	<dependency>
        	    <groupId>javax.servlet</groupId>
        	    <artifactId>javax.servlet-api</artifactId>
        	    <version>4.0.1</version>
        	    <scope>provided</scope>
        	</dependency>
        	
        	<dependency>
        	    <groupId>org.apache.logging.log4j</groupId>
        	    <artifactId>log4j-api</artifactId>
        	    <version>2.11.0</version>
        	</dependency>
        	<dependency>
        	    <groupId>org.apache.logging.log4j</groupId>
        	    <artifactId>log4j-core</artifactId>
        	    <version>2.11.0</version>
        	</dependency>
        	<dependency>
        	    <groupId>org.apache.logging.log4j</groupId>
        	    <artifactId>log4j-slf4j-impl</artifactId>
        	    <version>2.11.0</version>
        	</dependency>
        	<dependency>
        	    <groupId>org.slf4j</groupId>
        	    <artifactId>slf4j-api</artifactId>
        	    <version>1.7.25</version>
        	</dependency>
          </dependencies>
        </project>
    
     
3.  定义注解接口
    
        package com.szcatic.service;
        
        import java.lang.annotation.ElementType;
        import java.lang.annotation.Retention;
        import java.lang.annotation.RetentionPolicy;
        import java.lang.annotation.Target;
        
        /**
         * Excel注解定义
         * @author zsx
         * @version 2018-09-26
         */
        @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
        @Retention(RetentionPolicy.RUNTIME)
        public @interface ExcelField {
        
        	/**
        	 * 导出字段标题
        	 */
        	String title();
        	
        	
        	/**
        	 * 导出字段字段排序（升序）
        	 */
        	int sort() default 0;
        
        }
    
     
4.  导出工具类
    
        package com.szcatic.util;
        
        import java.io.File;
        import java.io.FileOutputStream;
        import java.io.IOException;
        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.text.SimpleDateFormat;
        import java.util.ArrayList;
        import java.util.Collections;
        import java.util.Comparator;
        import java.util.Date;
        import java.util.List;
        import java.util.Map;
        import java.util.Map.Entry;
        
        import javax.servlet.http.HttpServletResponse;
        
        import org.apache.poi.hssf.usermodel.HSSFWorkbook;
        import org.apache.poi.ss.usermodel.Cell;
        import org.apache.poi.ss.usermodel.CellStyle;
        import org.apache.poi.ss.usermodel.Font;
        import org.apache.poi.ss.usermodel.HorizontalAlignment;
        import org.apache.poi.ss.usermodel.Row;
        import org.apache.poi.ss.usermodel.Sheet;
        import org.apache.poi.ss.usermodel.VerticalAlignment;
        import org.apache.poi.ss.usermodel.Workbook;
        import org.apache.poi.ss.util.CellRangeAddress;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;
        import org.slf4j.Logger;
        import org.slf4j.LoggerFactory;
        
        import com.google.common.collect.Lists;
        import com.google.common.collect.Maps;
        import com.szcatic.entity.ExcelFoot;
        import com.szcatic.entity.ExcelTitleHead;
        import com.szcatic.service.ExcelField;
        
        public class ExportExcelUtils3 {
        	
        	private static Logger logger = LoggerFactory.getLogger(ExportExcelUtils3.class);
        	
        	public static <T1, T2> void exportDateToExcel(Class<T1> clzz, List<T1> list, HttpServletResponse response, 
        			String fileName, T2 t2, ExcelTitleHead ... heads) {
        		ExcelFoot<T2> excelFoot = new ExcelFoot<T2>(new ExcelTitleHead("汇总：", null, null, 0, 0), t2);
        		exportDateToExcel(clzz, ExcelField.class, "sort", "title", list, response, fileName, excelFoot, heads);
        	}
        	
        	public static <T1, T2> void exportDateToExcel(Class<T1> clzz, List<T1> list, String fileName, 
        			T2 t2, ExcelTitleHead ... heads) {
        		ExcelFoot<T2> excelFoot = new ExcelFoot<T2>(new ExcelTitleHead("汇总：", null, null, 0, 0), t2);
        		exportDateToExcel(clzz, ExcelField.class, "sort", "title", list, fileName, excelFoot, heads);
        	}
        	
        	/**
        	 * 导出数据到Excel
        	 * @param clzz
        	 * @param annotationClass
        	 * @param sortName
        	 * @param titleName
        	 * @param list
        	 * @param response
        	 * @param fileName
        	 * @param heads
        	 */
        	private static <T extends Annotation, E, T2> void exportDateToExcel(Class<E> clzz, Class<T> annotationClass, 
        			String sortName, String titleName, List<E> list, String fileName, ExcelFoot<T2> excelFoot, 
        			ExcelTitleHead ... heads) {
        		FileOutputStream fos = null;
        		Workbook wb = null;
        		try {
        			fos = new FileOutputStream(new File(fileName));
        			wb = getWorkbook(clzz, annotationClass, sortName, titleName, list, fileName, excelFoot, heads);
        			// 进行输出，下载到本地
        	        wb.write(fos);
        	        fos.flush();
        			// 导出需要的数据到表格
        			logger.info("export excel success");
        		} catch (UnsupportedEncodingException e) {
        			e.printStackTrace();
        			logger.error("ExportExcelUtils3-》exportDateToExcel-》UnsupportedEncodingException");
        			logger.error(e.getMessage());
        		} catch (IOException e) {
        			e.printStackTrace();
        			logger.error("ExportExcelUtils3-》exportDateToExcel-》IOException");
        			logger.error(e.getMessage());
        		}finally {
        			if(fos != null) {
        				try {
        					fos.close();
        				} catch (IOException e) {
        					e.printStackTrace();
        					logger.error("ExportExcelUtils3-》exportDateToExcel-》finally-》IOException");
        					logger.error(e.getMessage());
        				}
        			}
        			if (wb != null) {
        				try {
        					wb.close();
        				} catch (IOException e) {
        					e.printStackTrace();
        				}
        			}
        		}
        	}
        	
        	/**
        	 * 导出数据到Excel
        	 * @param clzz
        	 * @param annotationClass
        	 * @param sortName
        	 * @param titleName
        	 * @param list
        	 * @param response
        	 * @param fileName
        	 * @param heads
        	 */
        	public static <T extends Annotation, E, T2> void exportDateToExcel(Class<E> clzz, Class<T> annotationClass, String sortName, 
        			String titleName, List<E> list, HttpServletResponse response, String fileName, ExcelFoot<T2> excelFoot, 
        			ExcelTitleHead ... heads) {
        		OutputStream os = null;
        		try {
                	// 设置导出的编码格式，此处统一为UTF-8
                	response.setContentType("application/vnd.ms-excel;charset=utf-8");
                	// 设置导出文件的名称
        			response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
        			os = response.getOutputStream();
        			Workbook wb = getWorkbook(clzz, annotationClass, sortName, titleName, list, fileName, excelFoot, heads);
        			// 进行输出，下载到本地
        	        wb.write(os);
        	        os.flush();
        			// 导出需要的数据到表格
        			logger.info("export excel success");
        		} catch (UnsupportedEncodingException e) {
        			e.printStackTrace();
        			logger.error("ExportExcelUtils3-》exportDateToExcel-》UnsupportedEncodingException");
        			logger.error(e.getMessage());
        		} catch (IOException e) {
        			e.printStackTrace();
        			logger.error("ExportExcelUtils3-》exportDateToExcel-》IOException");
        			logger.error(e.getMessage());
        		}finally {
        			if(os != null) {
        				try {
        					os.close();
        				} catch (IOException e) {
        					e.printStackTrace();
        					logger.error("ExportExcelUtils3-》exportDateToExcel-》finally-》IOException");
        					logger.error(e.getMessage());
        				}
        			}
        		}
        	}
        
        	private static <T extends Annotation, E, T2> Workbook getWorkbook(Class<E> clzz, Class<T> annotationClass, String sortName, 
        			String titleName, List<E> list, String fileName, ExcelFoot<T2> excelFoot, ExcelTitleHead ... heads) {
        		Workbook wb;
        		//创建工作表
        		if (isExcel2003(fileName)) {
        			wb = new HSSFWorkbook();
        		}else {
        			wb = new XSSFWorkbook();
        		}
        		
        		if ((list == null || list.isEmpty()) && (heads == null || heads.length == 0)) {
        			return wb;
        		}
        		// 创建sheet页
        		Sheet sheet = wb.createSheet();
        		sheet.autoSizeColumn(1, true);
                // 单元格样式
                CellStyle titleStyle = getTitleStyle(wb);
                CellStyle cellStyle = getCellStyle(wb);
                CellStyle footStyle = getFootStyle(wb);
        		Integer maxTitleRow = fillTitle(sheet, titleStyle, heads);
        		Integer maxTitleRow2 = fillDataToExcel(sheet, clzz, annotationClass, list, maxTitleRow, sortName, titleName, titleStyle, cellStyle, excelFoot);
        		fillFootData(sheet, annotationClass, sortName, maxTitleRow2, footStyle, excelFoot);
        		return wb;
        	}
        	
        	/**
        	 * 填充数据到Excel
        	 * @param <E>
        	 * @param <T>
        	 * @param clzz 
        	 * @param annotationClass 提供注解的类
        	 * @param list 数据列表
        	 * @param maxTitleRow 
        	 * @param sortName 排序的方法名
        	 * @param titleName 导出字段的标题方法名
        	 * @param titleName 标题头单元格样式
        	 * @param titleName 数据单元格样式
        	 */
        	private static <T extends Annotation, E, T2> Integer fillDataToExcel(Sheet sheet, Class<E> clzz, Class<T> annotationClass, 
        			List<E> list, Integer maxTitleRow, String sortName, String titleName, CellStyle titleStyle, CellStyle cellStyle,
        			ExcelFoot<T2> excelFoot) {
        		Map<String, T> map = getClassAnnotationMap(clzz, annotationClass);
        		List<Map.Entry<String, T>> sortList = mapSort(map, annotationClass, sortName);
        		List<String> headerList = getHeaderList(annotationClass, titleName, sortList);
        		Integer maxTitleRow2 = fileColumnTitle(headerList, titleStyle, sheet, maxTitleRow);
        		if (list == null || list.isEmpty()) {
        			return maxTitleRow2;
        		}
        		List<String> methodNames = getMethodNameList(sortList);
        		Integer maxDadaRow = fileColumnData(list, methodNames, cellStyle, sheet, maxTitleRow2);
        		return maxDadaRow;
        	}
        	
        	/**
        	 * 填充foot行数据
        	 * @param excelFoot
        	 */
        	private static <T1, T2 extends Annotation> void fillFootData(Sheet sheet, Class<T2> annotationClass, String sortName, 
        			Integer maxTitleRow, CellStyle footStyle, ExcelFoot<T1> excelFoot) {
        		if (excelFoot == null) {
        			return;
        		}
        		ExcelTitleHead head = excelFoot.getExcelTitleHead();
        		head.setStartRow(maxTitleRow + 1);
        		head.setLastRow(maxTitleRow + 1);
        		fillTitle(sheet, footStyle, head);
        		Cell cell;
        		Object obj;
        		Row row = sheet.getRow(head.getStartRow());
        		T1 t = excelFoot.getT();
        		Map<String, T2> map = getClassAnnotationMap(t.getClass(), annotationClass);
        		List<Map.Entry<String, T2>> sortList = mapSort(map, annotationClass, sortName);
        		List<String> methodNames = getMethodNameList(sortList);
        		for(int i = 0; i < methodNames.size(); i++) {
        			cell = row.createCell(head.getLastColmun() + (i + 1));
        			obj = getMethodReturnValue(t, methodNames.get(i));
        			cell.setCellValue(objectToString(obj));
        			cell.setCellStyle(footStyle);
        		}
        	}
        
        	/**
        	 * 填充主数据到Excel
        	 * @param list 主数据列表
        	 * @param methodNames
        	 * @param titleStyle
        	 * @param sheet
        	 * @param maxTitleRow
        	 * @return
        	 */
        	private static <T> Integer fileColumnData(List<T> list, List<String> methodNames, CellStyle cellStyle, Sheet sheet,
        			Integer maxDadaRow) {
        		Row row;
        		Cell cell;
        		Object obj;
        		T t;
        		for (int i = 0; i < list.size(); i++) {
        			row = sheet.createRow(++maxDadaRow);
        			t = list.get(i);
        			for(int j = 0; j < methodNames.size(); j++) {
        				cell = row.createCell(j);
        				obj = getMethodReturnValue(t, methodNames.get(j));
        				cell.setCellValue(objectToString(obj));
        				cell.setCellStyle(cellStyle);
        			}
        		}
        		return maxDadaRow;
        	}
        
        	/**
        	 * 获取方法名列表
        	 * @param sortList
        	 * @return List
        	 */
        	private static <T> List<String> getMethodNameList(List<Entry<String, T>> list) {
        		List<String> list2 = Lists.newArrayList();
        		for (Entry<String, T> entry : list) {
        			list2.add(entry.getKey());
        		}
        		return list2;
        	}
        
        	/**
        	 * 
        	 * @param list
        	 * @param cellStyle
        	 * @param sheet
        	 * @param maxTitleRow
        	 * @return
        	 */
        	private static Integer fileColumnTitle(List<String> list, CellStyle cellStyle, Sheet sheet, Integer maxTitleRow) {
        		Row row = sheet.createRow(++maxTitleRow);
        		Cell cell;
        		for (int i = 0; i < list.size(); i++) {
        			cell = row.createCell(i);
        			cell.setCellValue(list.get(i));
        			cell.setCellStyle(cellStyle);
        		}
        		return maxTitleRow;
        	}
        
        	/**
        	 * 获取列标题头列表
        	 * @param annotationClass
        	 * @param list2
        	 * @param titleName
        	 * @return List
        	 */
        	private static <T> List<String> getHeaderList(Class<T> annotationClass, String titleName, List<Map.Entry<String, T>> list) {
        		List<String> list2 = Lists.newArrayList();
        		for (Entry<String, T> entry : list) {
        			list2.add(String.valueOf(getReturnValue(annotationClass, titleName, entry.getValue())));
        		}
        		return list2;
        	}
        	
        	/**
        	 * 标题头单元格样式设置
        	 * @param wb
        	 * @return
        	 */
        	private static CellStyle getTitleStyle(Workbook wb) {
        		CellStyle titleStyle = wb.createCellStyle();
        		// 字体样式
                Font font = wb.createFont();
                // 粗体
                font.setBold(true);
                titleStyle.setFont(font);
                // 水平居中
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                // 垂直居中
                titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        		return titleStyle;
        	}
        	
        	/**
        	 * 数据单元格样式设置
        	 * @param wb
        	 * @return
        	 */
        	private static CellStyle getCellStyle(Workbook wb) {
        		CellStyle cellStyle = wb.createCellStyle();
        		cellStyle.setAlignment(HorizontalAlignment.CENTER);
        		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        		return cellStyle;
        	}
        	
        	private static CellStyle getFootStyle(Workbook wb) {
        		CellStyle cellStyle = wb.createCellStyle();
        		cellStyle.setAlignment(HorizontalAlignment.CENTER);
        		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        		Font font = wb.createFont();
        		font.setColor(Font.COLOR_RED);
        		cellStyle.setFont(font);
        		return cellStyle;
        	}
        	
        	
        	/**
        	 * 填充标题头
        	 * @param sheet 
        	 * @param titleStyle
        	 * @param heads
        	 * @return Integer 标题头的最大行，如果为-1，表示没有标题行
        	 */
        	private static Integer fillTitle(Sheet sheet, CellStyle titleStyle, ExcelTitleHead... heads) {
        		Integer maxRow = -1;
        		if (heads == null || heads.length == 0) {
        			return maxRow;
        		}
        		Row row;
        		Cell cell;
        		CellRangeAddress region;
        		Integer lastRow;
        		for(int i = 0; i < heads.length; i++) {
        			//设置单元格范围：参数（int firstRow, int lastRow, int firstCol, int lastCol)
        			region = new CellRangeAddress(heads[i].getStartRow(), heads[i].getLastRow(), heads[i].getStartColmun(), heads[i].getLastColmun());
        			if (heads[i].getLastRow() + heads[i].getLastColmun() - heads[i].getStartRow() - heads[i].getStartColmun() > 0) {
        				//合并单元格
        				sheet.addMergedRegion(region);
        			}
        			//生成行
        			row = sheet.createRow(heads[i].getStartRow());
        		}
        		for(int i = 0; i < heads.length; i++) {
        			//获取生成的行
        			row = sheet.getRow(heads[i].getStartRow());
        			//生成列
        			cell = row.createCell(heads[i].getStartColmun());
        			cell.setCellValue(heads[i].getName());
        			cell.setCellStyle(titleStyle);
        			lastRow = heads[i].getLastRow();
        			if (maxRow < lastRow) {
        				maxRow = lastRow;
        			}
        		}
        		return maxRow;
        	}
        	
        	/**
        	 * 获取包含@T注解的方法以及注解的内容
        	 * @param clzz
        	 * @param annotationClass 注解接口类
        	 * @return Map:方法名为key,注解内容为value
        	 */
        	private static <T extends Annotation, E> Map<String, T> getClassAnnotationMap(Class<E> clzz, Class<T> annotationClass) {
        		Map<String, T> map = Maps.newHashMap();
        		Method[] methods = clzz.getDeclaredMethods();
        		if (methods == null || methods.length == 0) {
        			return map;
        		}
        		T annotation;
        		for (Method method : methods) {
        			if(method.isAnnotationPresent(annotationClass)) {
        				//得到注解
        				annotation = method.getAnnotation(annotationClass);
        				map.put(method.getName(), annotation);
        			}
        		}
        		return map;
        	}
        	
        	/**
        	 * 对Map集合进行排序
        	 * @param map 需要排序的Map集合
        	 * @param clzz class类
        	 * @param name 方法名
        	 * @return List
        	 */
        	private static <T> List<Map.Entry<String, T>> mapSort(Map<String, T> map, Class<T> clzz, String name) {
        		List<Map.Entry<String, T>> list = new ArrayList<Map.Entry<String, T>>(map.entrySet());
        		Collections.sort(list, new Comparator<Map.Entry<String, T>>() {
        			// 升序排序
        			public int compare(Entry<String, T> o1, Entry<String, T> o2) {
        				return (Integer) getReturnValue(clzz, name, o1.getValue()) 
        						- (Integer) getReturnValue(clzz, name, o2.getValue());
        			}
        		});
        		return list;
        	}
        	
        	/**
             * 获取方法的返回值
             * @param clzz 
             * @param name 方法名
             * @return
             */
            private static <T> Object getReturnValue(Class<T> clzz, String name, T t) {
            	Object value = null;
            	try {
            		Method method = clzz.getMethod(name);
            		value = method.invoke(t);
            		return value;
            	}catch (NoSuchMethodException e) {
            		e.printStackTrace();
            	}catch (SecurityException e) {
            		e.printStackTrace();
            	}catch (IllegalAccessException e) {
            		e.printStackTrace();
            	}catch (IllegalArgumentException e) {
            		e.printStackTrace();
            	}catch (InvocationTargetException e) {
            		e.printStackTrace();
            	}
            	return value;
            }
            
            /**
             * 获取方法的返回值
             * @param T 实体类 
             * @param methodName 方法名
             * @return Object
             */
        	private static <T> Object getMethodReturnValue(T t, String methodName) {
            	Object obj = null;
            	try {
        			obj = t.getClass().getMethod(methodName).invoke(t);
        		}catch (NoSuchMethodException e) {
        			e.printStackTrace();
        	    }catch (SecurityException e) {
        	    	e.printStackTrace();
        		} catch (IllegalAccessException e) {
        			e.printStackTrace();
        		} catch (IllegalArgumentException e) {
        			e.printStackTrace();
        		} catch (InvocationTargetException e) {
        			e.printStackTrace();
        		}
            	return obj;
            }
            
            private static String objectToString(Object obj) {
            	if (obj == null) {
        			return "";
        		}
            	String str;
            	try {
            		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            		if(obj instanceof Date) {
            			str = sdf.format(obj);
            		}else {
            			str = String.valueOf(obj);
            		}
        		} catch (Exception e) {
        			str = "";
        		}
            	return str;
            }
            
            /**
        	 * 判断是不是2003格式的Excel
        	 * @param filePath
        	 * @return boolean : true表示是2003格式的Excel，false表示不是
        	 */
        	private static boolean isExcel2003(String filePath) {
        		return filePath.matches("^.+\\.(?i)(xls)$");
        	}
        }
    
     
5.  实体类 
    
        package com.szcatic.entity;
        
        import java.io.Serializable;
        
        /**
         * Excel标题头实体类
         * 
         * @author zsx
         * @version 2018-09-26
         *
         */
        public class ExcelTitleHead implements Serializable {
        
        	private static final long serialVersionUID = 1L;
        
        	private String name; // 标题名
        	private Integer startRow; // 起始行
        	private Integer lastRow; // 最后行
        	private Integer startColmun; // 起始列
        	private Integer lastColmun; // 最后列
        	
        	public ExcelTitleHead() {
        		super();
        	}
        	
        	/**
        	 * 
        	 * @param name 标题名
        	 * @param startRow 起始行
        	 * @param lastRow 最后行
        	 * @param startColmun 起始列
        	 * @param lastColmun 最后列
        	 */
        	public ExcelTitleHead(String name, Integer startRow, Integer lastRow, Integer startColmun, Integer lastColmun) {
        		super();
        		this.name = name;
        		this.startRow = startRow;
        		this.lastRow = lastRow;
        		this.startColmun = startColmun;
        		this.lastColmun = lastColmun;
        	}
        
        	public String getName() {
        		return name;
        	}
        
        	public void setName(String name) {
        		this.name = name;
        	}
        
        	public Integer getStartRow() {
        		return startRow;
        	}
        
        	public void setStartRow(Integer startRow) {
        		this.startRow = startRow;
        	}
        
        	public Integer getLastRow() {
        		return lastRow;
        	}
        
        	public void setLastRow(Integer lastRow) {
        		this.lastRow = lastRow;
        	}
        
        	public Integer getStartColmun() {
        		return startColmun;
        	}
        
        	public void setStartColmun(Integer startColmun) {
        		this.startColmun = startColmun;
        	}
        
        	public Integer getLastColmun() {
        		return lastColmun;
        	}
        
        	public void setLastColmun(Integer lastColmun) {
        		this.lastColmun = lastColmun;
        	}
        
        	@Override
        	public String toString() {
        		return "ExcelTitleHead [name=" + name + ", startRow=" + startRow + ", lastRow=" + lastRow + ", startColmun="
        				+ startColmun + ", lastColmun=" + lastColmun + "]";
        	}
        	
        	
        }
    
        package com.szcatic.entity;
        
        import com.szcatic.entity.ExcelTitleHead;
        
        /**
         * ExcelFoot实体类
         * 
         * @author zsx
         * @version 2018-09-26
         *
         */
        public class ExcelFoot<T> {
        
        	private ExcelTitleHead excelTitleHead;
        	private T t;
        
        	public ExcelFoot() {
        		super();
        	}
        	
        	public ExcelFoot(ExcelTitleHead excelTitleHead, T t) {
        		super();
        		this.excelTitleHead = excelTitleHead;
        		this.t = t;
        	}
        
        	public ExcelTitleHead getExcelTitleHead() {
        		return excelTitleHead;
        	}
        
        	public void setExcelTitleHead(ExcelTitleHead excelTitleHead) {
        		this.excelTitleHead = excelTitleHead;
        	}
        
        	public T getT() {
        		return t;
        	}
        
        	public void setT(T t) {
        		this.t = t;
        	}
        
        	@Override
        	public String toString() {
        		return "ExcelFoot [excelTitleHead=" + excelTitleHead + ", t=" + t + "]";
        	}
        
        }
    
        package com.szcatic.entity;
        
        import java.io.Serializable;
        import java.util.List;
        
        import com.szcatic.service.ExcelField;
        
        /**
         * 人实体类
         * @author zsx
         * @version 2018-09-26
         */
        public class Person implements Serializable {
        
        	private static final long serialVersionUID = 1L;
        	
        	private Long id;
        	private String name;
        	private Integer age;
        	private List<String> hobby;
        	
        	public void setId(Long id) {
        		this.id = id;
        	}
        	
        	@ExcelField(title="id", sort=10)
        	public Long getId() {
        		return this.id;
        	}
        
        	public void setName(String name) {
        		this.name = name;
        	}
        	
        	@ExcelField(title="姓名", sort=30)
        	public String getName() {
        		return this.name;
        	}
        
        	public void setAge(Integer age) {
        		this.age = age;
        	}
        	
        	@ExcelField(title="年龄", sort=20)
        	public Integer getAge() {
        		return this.age;
        	}
        
        	public void setHobby(List<String> hobby) {
        		this.hobby = hobby;
        	}
        
        	public List<String> getHobby() {
        		return this.hobby;
        	}
        
        	@Override
        	public String toString() {
        		return "Person [id=" + id + ", name=" + name + ", age=" + age + ", hobby=" + hobby + "]";
        	}
        	
        }
    
        package com.szcatic.entity;
        
        import java.io.Serializable;
        import com.szcatic.service.ExcelField;
        
        /**
         * 用户实体类
         * @author zsx
         * @version 2018-09-26
         */
        public class User implements Serializable {
        
        	private static final long serialVersionUID = 1L;
        	
        	private String userName; // 用户名
        	private String password; // 密码
        	private Integer age; // 年龄
        	private String gender; // 性别
        	private String email; // 邮箱
        	
        	@ExcelField(title="用户名", sort=10)
        	public String getUserName() {
        		return userName;
        	}
        
        	public void setUserName(String userName) {
        		this.userName = userName;
        	}
        	
        	@ExcelField(title="密码", sort=30)
        	public String getPassword() {
        		return password;
        	}
        
        	public void setPassword(String password) {
        		this.password = password;
        	}
        	
        	@ExcelField(title="年龄", sort=20)
        	public Integer getAge() {
        		return age;
        	}
        
        	public void setAge(Integer age) {
        		this.age = age;
        	}
        	
        	@ExcelField(title="性别", sort=50)
        	public String getGender() {
        		return gender;
        	}
        	
        	public void setGender(String gender) {
        		this.gender = gender;
        	}
        
        	@ExcelField(title="邮箱", sort=40)
        	public String getEmail() {
        		return email;
        	}
        	
        	public void setEmail(String email) {
        		this.email = email;
        	}
        
        	@Override
        	public String toString() {
        		return "User [userName=" + userName + ", password=" + password + ", age=" + age + ", gender=" + gender
        				+ ", email=" + email + "]";
        	}
        
        
        }
    
     
6.  测试类
    
        package com.szcatic.test;
        
        import java.util.ArrayList;
        import java.util.List;
        
        import org.junit.jupiter.api.Test;
        
        import com.szcatic.entity.ExcelTitleHead;
        import com.szcatic.entity.Person;
        import com.szcatic.entity.User;
        import com.szcatic.util.ExportExcelUtils3;
        
        /**
         * 导出工具类测试类
         * @author zsx
         * @version 2018-09-26
         *
         */
        public class ExportExcelUtils3Test {
        	@Test
        	void testExportDateToExcel() {
        		ExcelTitleHead head = new ExcelTitleHead("主标题", 0, 1, 0, 4);
        		ExcelTitleHead head0 = new ExcelTitleHead("主标题1", 0, 1, 5, 6);
        		ExcelTitleHead head1 = new ExcelTitleHead("副标题1", 2, 3, 0, 0);
        		ExcelTitleHead head2 = new ExcelTitleHead("副标题2", 2, 3, 1, 1);
        		
        		ExcelTitleHead head3 = new ExcelTitleHead("副标题3", 2, 3, 2, 4);
        		List<User> list = new ArrayList<>();
        		User user = new User();
        		user.setUserName("zhangsan");
        		user.setAge(20);
        		user.setGender("男");
        		user.setEmail("zhangsan@163.com");
        		user.setPassword("1234");
        		list.add(user);
        		User user2 = new User();
        		user2.setUserName("lisi");
        		user2.setAge(25);
        		user2.setGender("男");
        		user2.setEmail("lisi@163.com");
        		user2.setPassword("1234");
        		list.add(user2);
        		Person person = new Person();
        		person.setId(2L);
        		person.setAge(10);
        		person.setName("zhaoyun");
        		String fileName = "F:/newProject/export.xls";
        		String fileName1 = "F:/newProject/export.xlsx";
        		ExportExcelUtils3.exportDateToExcel(User.class, list, fileName, person, head, head0, head1, head2, head3);
        		ExportExcelUtils3.exportDateToExcel(User.class, list, fileName1, person, head, head0, head1, head2, head3);
        		System.out.println("生成文件成功");
        	}
        }
    
     
7.  运行结果
    
        [23:23:55:177] [INFO] - com.szcatic.util.ExportExcelUtils3.exportDateToExcel(ExportExcelUtils3.java:81) - export excel success
        [23:23:56:258] [INFO] - com.szcatic.util.ExportExcelUtils3.exportDateToExcel(ExportExcelUtils3.java:81) - export excel success
        生成文件成功
    
    ![70 1][]

[70]: /images/20220510/d4e6d6f9af4c49628790e80f611f2df9.png
[70 1]: /images/20220510/0906134755784dfaaa649bab2591bd76.png