Java POI工具类之 - 根据POJO的类字段，自定义Excel导入导出。

╰半夏微凉° 2023-07-25 14:05 4阅读 0赞

## 0、pom.xml 依赖 ##

<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<spring.version>4.2.6.RELEASE</spring.version>
    		<jackson.version>2.7.4</jackson.version>
    		<junit>4.10</junit>
    	</properties>
    
    <dependencies>
    <dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-web</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    <dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-core</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		
    
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.7</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.7</version>
    		</dependency>
    
    		<dependency>
    			<groupId>net.sourceforge.jexcelapi</groupId>
    			<artifactId>jxl</artifactId>
    			<version>2.6</version>
    		</dependency>
    		
    		
    
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-lang3</artifactId>
    			<version>3.4</version>
    		</dependency>
    		
    
    		<dependency>
    			<groupId>org.slf4j</groupId>
    			<artifactId>slf4j-log4j12</artifactId>
    			<version>1.7.12</version>
    		</dependency>
    	</dependencies>

## 1、定义 excel导入导出注解类(ExcelAnnotation)，方便与POJO类互动 ##

import java.lang.annotation.Documented;
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Inherited;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /** * excel导出时标题显示的名字，如果没有设置Annotation属性，将不会被导出和导入 */
    @Documented
    @Inherited
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    public @interface ExcelAnnotation { 
    
    	/** 与excel标题头对应 */
    	public String exportName();
    
    	/** 转换格式, 如时间类型 yyyy-MM-dd HH:mm:ss */
    	public String pattern() default "";
    
    	/** 在excel中位置 */
    	public int order() default 0;
    
    	/** 对应字典名称,wutiancai **/
    	public String dictName() default "";
    
    	/** 列宽,wutiancai **/
    	public int columnWidth() default 0;
    
    }

## 2、POJO字段加入注解(@ExcelAnnotation) ##

package com.bdxh.framework.commons.excel;
    
    import java.util.Date;
    
    /** * excel元注释导入, 测试excel */
    public class Testpojo { 
    	@ExcelAnnotation(exportName = "用户名", order = 4)
    	String username;
    	@ExcelAnnotation(exportName = "登录名", order = 3)
    	String loginname;
    	@ExcelAnnotation(exportName = "年龄", order = 2)
    	Integer age;
    	@ExcelAnnotation(exportName = "收入", order = 1)
    	Long money;
    	@ExcelAnnotation(exportName = "时间", pattern = "yyyy-MM-dd HH:mm:ss", order = 0)
    	Date createtime;
    
    	public String getUsername() { 
    		return username;
    	}
    
    	public void setUsername(String username) { 
    		this.username = username;
    	}
    
    	public String getLoginname() { 
    		return loginname;
    	}
    
    	public void setLoginname(String loginname) { 
    		this.loginname = loginname;
    	}
    
    	public Integer getAge() { 
    		return age;
    	}
    
    	public void setAge(Integer age) { 
    		this.age = age;
    	}
    
    	public Long getMoney() { 
    		return money;
    	}
    
    	public void setMoney(Long money) { 
    		this.money = money;
    	}
    
    	public Date getCreatetime() { 
    		return createtime;
    	}
    
    	public void setCreatetime(Date createtime) { 
    		this.createtime = createtime;
    	}
    
    }

## 3、Excel导入工具类(ExcelImportUtils) ##

import java.io.File;
    import java.io.FileInputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.lang.reflect.Type;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import org.apache.commons.util.DateUtil;
    import org.springframework.web.multipart.MultipartFile;
    
    /** * 导入EXCEL导入工具, 产出指定pojo 列表 */
    public class ExcekImportUtils<T> { 
    
    	private static final Logger logger = LoggerFactory.getLogger(ExcekImportUtils.class);
    
    	public ExcekImportUtils() { 
    	}
    
    	/** * 解析excel文档 */
    	public List<T> importExcel(File file, Class<T> clazz) { 
    		List<T> dist = new ArrayList<T>();
    		try { 
    			Field filed[] = clazz.getDeclaredFields();
    			// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
    			Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
    			// 循环读取所有字段
    			for (int i = 0; i < filed.length; i++) { 
    				Field f = filed[i];
    				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
    				if (exa != null) { 
    					// 构造设置了Annotation的字段的Setter方法
    					String fieldname = f.getName();
    					String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
    					// 构造调用的method，
    					Method setMethod = clazz.getMethod(setMethodName, new Class[] {  f.getType() });
    					String pattern = exa.pattern();
    					// 将这个method以Annotaion的名字为key来存入。
    					fieldmap.put(exa.exportName(), new Object[] {  setMethod, pattern });
    				}
    			}
    
    			FileInputStream in = new FileInputStream(file);
    			Workbook workbook = WorkbookFactory.create(in);
    			Sheet sheet = workbook.getSheetAt(0);  //示意访问sheet
    // XSSFWorkbook xwb = new XSSFWorkbook(in);
    // XSSFSheet sheet = xwb.getSheetAt(0);
    			Iterator<Row> rows = sheet.rowIterator();
    			Row title = rows.next(); // 取得标题头行
    			Iterator<Cell> cellTitle = title.cellIterator(); // 得到第一行的所有列
    			Map<Integer, String> titlemap = new HashMap<Integer, String>();// 将标题的文字内容放入到一个map中。
    			// 循环标题所有的列
    			for (int i = 0; cellTitle.hasNext(); i++) { 
    				Cell cell = cellTitle.next();
    				String value = cell.getStringCellValue();
    				titlemap.put(i, value);
    			}
    			// 解析内容行
    			while (rows.hasNext()) { 
    				Row rown = rows.next();
    				T tObject = clazz.newInstance(); // 行的所有列
    				// 遍历一行的列
    				for (int j = 0; j < rown.getLastCellNum(); j++) { 
    					Cell cell = rown.getCell(j, Row.RETURN_BLANK_AS_NULL);
    					String titleString = (String) titlemap.get(j);// 这里得到此列的对应的标题
    					// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
    					if (fieldmap.containsKey(titleString)) { 
    						Method setMethod = (Method) fieldmap.get(titleString)[0];
    						Type[] ts = setMethod.getGenericParameterTypes();// 得到setter方法的参数
    						String xclass = ts[0].toString(); // 只要一个参数
    						// 判断参数类型
    						if (xclass.equals("class java.lang.String")) { 
    							setMethod.invoke(tObject, this.getCellValue(cell));
    						} else if (xclass.equals("class java.util.Date")) { 
    							String pattern = (String) fieldmap.get(titleString)[1];
    							if (StringUtils.isBlank(pattern))
    								pattern = "yyyy-MM-dd HH:mm:ss";
    							setMethod.invoke(tObject, DateUtil.DateFormatString(this.getCellValue(cell), pattern));
    						} else if (xclass.equals("class java.lang.Boolean")) { 
    							Boolean boolname = true;
    							if (this.getCellValue(cell).equals("否")) { 
    								boolname = false;
    							}
    							setMethod.invoke(tObject, boolname);
    						} else if (xclass.equals("class java.lang.Integer")) { 
    							setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
    						} else if (xclass.equals("class java.lang.Long")) { 
    							setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
    						} else if (xclass.equals("double")){ 
    							setMethod.invoke(tObject, new Double(this.getCellValue(cell)));
    						}
    					}
    				}
    				dist.add(tObject);
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    			logger.error(e.getMessage(), e);
    			return null;
    		}
    		return dist;
    	}
    
    	/** * 解析excel文档 */
    	public List<T> importExcel(MultipartFile multipartFile, Class<T> clazz) { 
    		List<T> dist = new ArrayList<T>();
    		try { 
    			Field filed[] = clazz.getDeclaredFields();
    			// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
    			Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
    			// 循环读取所有字段
    			for (int i = 0; i < filed.length; i++) { 
    				Field f = filed[i];
    				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
    				if (exa != null) { 
    					// 构造设置了Annotation的字段的Setter方法
    					String fieldname = f.getName();
    					String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
    					// 构造调用的method，
    					Method setMethod = clazz.getMethod(setMethodName, new Class[] {  f.getType() });
    					String pattern = exa.pattern();
    					// 将这个method以Annotaion的名字为key来存入。
    					fieldmap.put(exa.exportName(), new Object[] {  setMethod, pattern });
    				}
    			}
    
    			Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
    			Sheet sheet = workbook.getSheetAt(0);  //示意访问sheet
    			sheet.getSheetName();
    // XSSFWorkbook xwb = new XSSFWorkbook(in);
    // XSSFSheet sheet = xwb.getSheetAt(0);
    			Iterator<Row> rows = sheet.rowIterator();
    			Row title = rows.next(); // 取得标题头行
    			Iterator<Cell> cellTitle = title.cellIterator(); // 得到第一行的所有列
    			Map<Integer, String> titlemap = new HashMap<Integer, String>();// 将标题的文字内容放入到一个map中。
    			// 循环标题所有的列
    			for (int i = 0; cellTitle.hasNext(); i++) { 
    				Cell cell = cellTitle.next();
    				String value = cell.getStringCellValue().trim();
    				titlemap.put(i, value);
    			}
    			// 解析内容行
    			while (rows.hasNext()) { 
    				Row rown = rows.next();
    				T tObject = clazz.newInstance(); // 行的所有列
    				// 遍历一行的列
    				for (int j = 0; j < rown.getLastCellNum(); j++) { 
    					Cell cell = rown.getCell(j, Row.RETURN_BLANK_AS_NULL);
    					String titleString = (String) titlemap.get(j);// 这里得到此列的对应的标题
    					// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
    					if (fieldmap.containsKey(titleString)) { 
    						Method setMethod = (Method) fieldmap.get(titleString)[0];
    						Type[] ts = setMethod.getGenericParameterTypes();// 得到setter方法的参数
    						String xclass = ts[0].toString(); // 只要一个参数
    						// 判断参数类型
    						if (xclass.equals("class java.lang.String")) { 
    							setMethod.invoke(tObject, this.getCellValue(cell));
    						} else if (xclass.equals("class java.util.Date")) { 
    							String pattern = (String) fieldmap.get(titleString)[1];
    							if (StringUtils.isBlank(pattern))
    								pattern = "yyyy-MM-dd HH:mm:ss";
    							setMethod.invoke(tObject, DateUtil.DateFormatString(this.getCellValue(cell), pattern));
    						} else if (xclass.equals("class java.lang.Boolean")) { 
    							Boolean boolname = true;
    							if (this.getCellValue(cell).equals("否")) { 
    								boolname = false;
    							}
    							setMethod.invoke(tObject, boolname);
    						} else if (xclass.equals("class java.lang.Integer")) { 
    							setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
    						} else if (xclass.equals("class java.lang.Long")) { 
    							setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
    						}
    					}
    				}
    				dist.add(tObject);
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    			logger.error(e.getMessage(), e);
    			return null;
    		}
    		return dist;
    	}
    
    	public String getCellValue(Cell cell) { 
    		if(cell==null){ 
    			return null;
    		}
    		cell.setCellType(Cell.CELL_TYPE_STRING);
    		return cell.getStringCellValue().trim();
    	}
    
    	public static void main(String[] args) { 
    		ExcekImportUtils<Testpojo> test = new ExcekImportUtils<Testpojo>();
    		File file = new File("c:\\导入.xlsx");
    		Long befor = System.currentTimeMillis();
    		List<Testpojo> result = (ArrayList<Testpojo>) test.importExcel(file, Testpojo.class);
    		Long after = System.currentTimeMillis();
    		System.out.println("此次操作共耗时：" + (after - befor) + "毫秒");
    		for (int i = 0; i < result.size(); i++) { 
    			Testpojo testpojo = result.get(i);
    			System.out.println(testpojo.getUsername());
    		}
    		System.out.println("共转化为List的行数为：" + result.size());
    }

## 4、Excel导出工具类（ExcelExportUtils） ##

import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.Comparator;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.List;
     
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import org.apache.commons.lang3.time.DateFormatUtils;
    
    public class ExcelExportUtils { 
    	private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtils.class);
    
    	/** * 导出Excel */
    	public <T> HSSFWorkbook exportExcel(String title, List<T> dataset) { 
    		HSSFWorkbook workbook = new HSSFWorkbook();
    		try { 
    			// 首先检查数据看是否是正确的
    			if (dataset == null || title == null) { 
    				throw new Exception("传入的数据异常！");
    			}
    			Iterator<T> its = dataset.iterator();
    			if (!its.hasNext()) { 
    				throw new Exception("传入的数据异常！");
    			}
    			T ts = its.next();
    			HSSFSheet sheet = workbook.createSheet(title);
    // sheet.setDefaultColumnWidth(5);// 设置表格默认列宽度为15个字节
    			HSSFCellStyle headStyle = workbook.createCellStyle();
    			headStyle = ExcelStyle.setHeadStyle(workbook, headStyle);
    			HSSFCellStyle bodyStyle = workbook.createCellStyle();
    			bodyStyle = ExcelStyle.setBodyStyle(workbook, bodyStyle);
    			Field filed[] = ts.getClass().getDeclaredFields();
    			List<Object[]> exportMetas = new ArrayList<>();
    
    			// 遍历整个filed
    			for (int i = 0; i < filed.length; i++) { 
    				Field f = filed[i];
    				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
    				// 如果设置了annottion
    				if (exa != null) { 
    					String exprot = exa.exportName();
    					String pattern = exa.pattern();
    					Integer order = Integer.valueOf(exa.order());
    					// 添加到标题
    					exportMetas.add(new Object[] {  f.getName(), exprot, pattern, order });
    					
    					int columnWidth = exa.columnWidth();
    					if(columnWidth > 0)
    						sheet.setColumnWidth(order,  columnWidth);
    					else { 
    						sheet.setColumnWidth(order, 6000);
    					}
    				}
    			}
    			// 排序exportMetas
    			Collections.sort(exportMetas, new Comparator<Object[]>() { 
    				/** 根据元注释order 排列顺序 */
    				public int compare(Object[] o1, Object[] o2) { 
    					Integer order1 = (Integer) o1[3];
    					Integer order2 = (Integer) o2[3];
    					return order1.compareTo(order2);
    				}
    			});
    			// 产生表格标题行
    			HSSFRow row = sheet.createRow(0);
    			for (int i = 0; i < exportMetas.size(); i++) { 
    				HSSFCell cell = row.createCell(i);
    				cell.setCellStyle(headStyle);
    				HSSFRichTextString text = new HSSFRichTextString((String) exportMetas.get(i)[1]);
    				cell.setCellValue(text);
    			}
    			// 循环整个集合
    			for (int i = 0; i < dataset.size(); i++) { 
    				row = sheet.createRow(i + 1); // 第一行为标题列, 从1开始写excel
    				T t = dataset.get(i);
    				for (int k = 0; k < exportMetas.size(); k++) { 
    					HSSFCell cell = row.createCell(k);
    					String fieldname = (String) exportMetas.get(k)[0];
    					String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
    					Class<?> tCls = t.getClass();
    					Method getMethod = tCls.getMethod(getMethodName, new Class[] { });
    					Object value = getMethod.invoke(t, new Object[] { });
    					String textValue = getValue(value, exportMetas.get(k));
    					HSSFRichTextString richString = new HSSFRichTextString(textValue);
    					cell.setCellValue(richString);
    					cell.setCellStyle(bodyStyle);
    				}
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    			logger.error(e.getMessage(), e);
    		}
    		return workbook;
    	}
    
    	/** * 导出Excel - 自定义表格内容颜色(实体类必须包含cellColor字段的颜色值) */
    	public <T> HSSFWorkbook exportExcelByColor(String title, List<T> dataset) { 
    		HSSFWorkbook workbook = new HSSFWorkbook();
    		try { 
    			// 首先检查数据看是否是正确的
    			if (dataset == null || title == null) { 
    				throw new Exception("传入的数据异常！");
    			}
    			Iterator<T> its = dataset.iterator();
    			if (!its.hasNext()) { 
    				throw new Exception("传入的数据异常！");
    			}
    			T ts = its.next();
    			HSSFSheet sheet = workbook.createSheet(title);
    // sheet.setDefaultColumnWidth(5);// 设置表格默认列宽度为15个字节
    			HSSFCellStyle headStyle = workbook.createCellStyle();
    			headStyle = ExcelStyle.setHeadStyle(workbook, headStyle);
    			HSSFCellStyle bodyStyle = workbook.createCellStyle();
    			bodyStyle = ExcelStyle.setBodyStyle(workbook, bodyStyle);
    			Field filed[] = ts.getClass().getDeclaredFields();
    			List<Object[]> exportMetas = new ArrayList<>();
    
    			// 遍历整个filed
    			for (int i = 0; i < filed.length; i++) { 
    				Field f = filed[i];
    				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
    				// 如果设置了annottion
    				if (exa != null) { 
    					String exprot = exa.exportName();
    					String pattern = exa.pattern();
    					Integer order = Integer.valueOf(exa.order());
    					// 添加到标题
    					exportMetas.add(new Object[] {  f.getName(), exprot, pattern, order });
    
    					int columnWidth = exa.columnWidth();
    					if(columnWidth > 0)
    						sheet.setColumnWidth(order,  columnWidth);
    					else { 
    						sheet.setColumnWidth(order, 6000);
    					}
    				}
    			}
    			// 排序exportMetas
    			Collections.sort(exportMetas, new Comparator<Object[]>() { 
    				/** 根据元注释order 排列顺序 */
    				public int compare(Object[] o1, Object[] o2) { 
    					Integer order1 = (Integer) o1[3];
    					Integer order2 = (Integer) o2[3];
    					return order1.compareTo(order2);
    				}
    			});
    			// 产生表格标题行
    			HSSFRow row = sheet.createRow(0);
    			for (int i = 0; i < exportMetas.size(); i++) { 
    				HSSFCell cell = row.createCell(i);
    				cell.setCellStyle(headStyle);
    				HSSFRichTextString text = new HSSFRichTextString((String) exportMetas.get(i)[1]);
    				cell.setCellValue(text);
    			}
    			// 循环整个集合
    			for (int i = 0; i < dataset.size(); i++) { 
    				row = sheet.createRow(i + 1); // 第一行为标题列, 从1开始写excel
    				T t = dataset.get(i);
    				for (int k = 0; k < exportMetas.size(); k++) { 
    					HSSFCell cell = row.createCell(k);
    					String fieldname = (String) exportMetas.get(k)[0];
    					String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
    					Class<?> tCls = t.getClass();
    					Method getMethod = tCls.getMethod(getMethodName, new Class[] { });
    					Object value = getMethod.invoke(t, new Object[] { });
    					String textValue = getValue(value, exportMetas.get(k));
    					HSSFRichTextString richString = new HSSFRichTextString(textValue);
    					cell.setCellValue(richString);
    
    					try{ 
    						// 增加内容自定义颜色
    						Method getCellColor = tCls.getMethod("getCellColor", new Class[]{ });
    						Object color = getCellColor.invoke(t, new Object[]{ });
    						if(color==null){ 
    							continue;
    						}
    						HSSFCellStyle myStyle = workbook.createCellStyle();
    						myStyle = ExcelStyle.setMyStyle(workbook, myStyle , (short)color);
    // myStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    						cell.setCellStyle(myStyle);
    					}catch (Exception e){ 
    						logger.warn("设置自定义表格背景色失败，没有获取到颜色值。");
    						cell.setCellStyle(bodyStyle);
    					}
    				}
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    			logger.error(e.getMessage(), e);
    		}
    		return workbook;
    	}
    
    	public String getValue(Object value, Object[] meta) { 
    		String textValue = "";
    		if (value == null)
    			return textValue;
    		if (value instanceof Boolean) { 
    			boolean bValue = (Boolean) value;
    			textValue = "是";
    			if (!bValue) { 
    				textValue = "否";
    			}
    		} else if (value instanceof Date) { 
    			String pattern = (String) meta[2];
    			if (StringUtils.isBlank(pattern))
    				pattern = "yyyy-MM-dd";
    			textValue = .format((Date) value, pattern);
    		} else { 
    			textValue = DateFormatUtils.format((Date) value, pattern);
    		}
    		return textValue;
    	}
    
    	public static void main(String[] args) throws Exception { 
    		List<Testpojo> list = new ArrayList<>();
    		for (int i = 0; i < 10; i++) { 
    			Testpojo testpojo = new Testpojo();
    			testpojo.setAge(new Integer(i));
    			testpojo.setCreatetime(new Date());
    			testpojo.setLoginname("chenld_" + i);
    			testpojo.setAge(Integer.valueOf(i));
    			testpojo.setUsername("chenliangdeng_" + i);
    			list.add(testpojo);
    		}
    		OutputStream out = new FileOutputStream("C:\\导出.xls");
    		Long l = System.currentTimeMillis();
    		ExcelExportUtils ex = new ExcelExportUtils();
    		HSSFWorkbook workbook = ex.exportExcel("测试", list);
    		workbook.write(out);
    		out.close();
    		Long s = System.currentTimeMillis();
    		System.out.println("总共耗时：" + (s - l));
    	}
    }

# 附加：Excel导入图片支持 #

/** * 获得图片所在的行记录集合 * * @param workbook * 工作簿 * @param sheetIndex * sheet索引 * @return */
    	public static Map<Integer, List<HSSFPictureData>> getSheetPicList(HSSFWorkbook workbook, int sheetIndex) { 
    		Map<Integer, List<HSSFPictureData>> map = new HashMap<Integer, List<HSSFPictureData>>();
    		HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
    		List<HSSFPictureData> picturelist = workbook.getAllPictures();
    		for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) { 
    			HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
    			// 获得图形所在的行
    			int rowIndex = anchor.getRow1();
    			if (shape instanceof HSSFPicture) { 
    				Integer rowmark = rowIndex;
    				HSSFPicture picture = (HSSFPicture) shape;
    				int pictureIndex = picture.getPictureIndex() - 1;
    				List<HSSFPictureData> piclist = map.get(rowmark);
    				if (piclist == null || piclist.size() > 0)
    					piclist = new ArrayList<HSSFPictureData>();
    				piclist.add(picturelist.get(pictureIndex));
    				map.put(rowmark, piclist);
    			}
    		}
    		return map;
    	}
    
    	/** * 获得图片所在的行记录集合 * * @param workbook * 工作簿 * @param sheetIndex * sheet索引 * @return */
    	public static Map<String, PictureData> getSheetPicList2007(XSSFWorkbook workbook, int sheetIndex) { 
    		Map<String, PictureData> map = new HashMap<String, PictureData>();
    		XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
    		List<POIXMLDocumentPart> list = sheet.getRelations();
    		for (POIXMLDocumentPart part : list) { 
    			if (part instanceof XSSFDrawing) { 
    				XSSFDrawing drawing = (XSSFDrawing) part;
    				List<XSSFShape> shapes = drawing.getShapes();
    				for (XSSFShape shape : shapes) { 
    					XSSFPicture picture = (XSSFPicture) shape;
    					XSSFClientAnchor anchor = picture.getPreferredSize();
    					CTMarker marker = anchor.getFrom();
    					String key = marker.getRow() + "-" + marker.getCol();
    					System.out.println(key);
    					map.put(key, picture.getPictureData());
    				}
    			}
    		}
    		return map;
    	}
    
    	//图片写出
    	public static void writeImg(Map<String, PictureData> sheetList) throws IOException { 
    
    		//for (Map<String, PictureData> map : sheetList) { 
    		Object key[] = sheetList.keySet().toArray();
    		for (int i = 0; i < sheetList.size(); i++) { 
    			// 获取图片流
    			PictureData pic = sheetList.get(key[i]);
    			// 获取图片索引
    			String picName = key[i].toString();
    			// 获取图片格式
    			String ext = pic.suggestFileExtension();
    
    			byte[] data = pic.getData();
    
    			//图片保存路径
    			FileOutputStream out = new FileOutputStream("G:\\pic" + picName + "." + ext);
    			out.write(data);
    			out.close();
    		}
    		// }
    
    	}

## 附加 DateUtil.java 日期工具类 ##

import java.sql.Timestamp;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    import java.util.Locale;
    
    /** * 功能说明:日期辅助类 */
    public class DateUtil { 
    
    	public static Date getYesterday(Date date) { 
    		Date yesDate = new Date(date.getTime() - 24 * 60 * 60 * 1000);
    		return yesDate;
    	}
    
    	public static String getNowDateString() { 
    		Date date = new Date();
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		return sdf.format(date);
    	}
    
    	/** * string to Date */
    	public static Date DateFormatString(String dateStr) { 
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		Date date;
    		try { 
    			date = sdf.parse(dateStr);
    		} catch (Exception e) { 
    			date = new Date();
    		}
    		return date;
    	}
    
    	public static Date DateFormatString(String dateStr, String pattern) { 
    		SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    		Date date;
    		try { 
    			date = sdf.parse(dateStr);
    		} catch (Exception e) { 
    			date = new Date();
    		}
    		return date;
    	}
    
    	/** * 时间转String */
    	public static String DateToString(Date date) { 
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String str;
    		try { 
    			str = sdf.format(date);
    		} catch (Exception e) { 
    			str = "";
    		}
    		return str;
    	}
    
    	public static String DateToString(Date date, String pattern) { 
    		SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    		String str;
    		try { 
    			str = sdf.format(date);
    		} catch (Exception e) { 
    			str = "";
    		}
    		return str;
    	}
    	
    	public static String getWeekOfDate(Date date) { 
    		Locale localeCN = Locale.SIMPLIFIED_CHINESE;
    		SimpleDateFormat sdf = new SimpleDateFormat("EEEE", localeCN);
    		String str;
    		try { 
    			str = sdf.format(date);
    		} catch (Exception e) { 
    			str = "";
    		}
    		return str;
    	}
    
    	/** * 将java.sql.Timestamp转化为java.util.Date */
    	public static Date timeStampToDate(Timestamp timestamp) { 
    		if (timestamp == null)
    			return null;
    		return new Date(timestamp.getTime());
    	}
    
    	public static Date parseDate(String value, Class<?> targetType, String... formats) { 
    		for (String format : formats) { 
    			try { 
    				long v = new SimpleDateFormat(format).parse(value).getTime();
    				return (Date) targetType.getConstructor(long.class).newInstance(v);
    			} catch (ParseException e) { 
    			} catch (Exception e) { 
    				throw new RuntimeException(e);
    			}
    			try { 
    				return (Date) targetType.getConstructor(String.class).newInstance(value);
    			} catch (Exception e) { 
    			}
    		}
    		throw new IllegalArgumentException("cannot parse:" + value + " for date by formats:" + Arrays.asList(formats));
    	}
    
    	public static boolean isDateType(Class<?> targetType) { 
    		if (targetType == null)
    			return false;
    		return targetType == java.util.Date.class || targetType == java.sql.Timestamp.class || targetType == java.sql.Date.class
    				|| targetType == java.sql.Time.class;
    	}
    
    	/** * sql.Date */
    	public static java.sql.Date toSQLDate(Date date) { 
    		return date instanceof java.sql.Date ? (java.sql.Date) date : toSQLDate(date.getTime());
    	}
    
    	public static java.sql.Date toSQLDate(long time) { 
    		return new java.sql.Date(time);
    	}
    
    	public static java.sql.Date getSQLDate() { 
    		return toSQLDate(System.currentTimeMillis());
    	}
    
    	/** * sql.Timestamp */
    	public static java.sql.Timestamp toTimestamp(Date date) { 
    		return date instanceof java.sql.Timestamp ? (java.sql.Timestamp) date : toTimestamp(date.getTime());
    	}
    
    	public static java.sql.Timestamp toTimestamp(long time) { 
    		return new java.sql.Timestamp(time);
    	}
    
    	public static java.sql.Timestamp getTimestamp() { 
    		return toTimestamp(System.currentTimeMillis());
    	}
    
    	public static List<Date> dateList(String dateStr) { 
    		String[] ss = dateStr.split(" - ");
    		if (ss == null || ss.length < 2) { 
    			return null;
    		}
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    		List<Date> tt = new ArrayList<Date>();
    		try { 
    			tt.add(sdf.parse(ss[0]));
    			tt.add(sdf.parse(ss[1]));
    		} catch (Exception e) { 
    			return null;
    		}
    		return tt;
    	}
    	
    	/** * 获取几秒前时间 * @param seconds * @return */
    	public static Date getBeforeDateBySeconds(int seconds){ 
    		/* 核心代码 */
    		Calendar beforeTime = Calendar.getInstance();
    		beforeTime.add(Calendar.SECOND, -seconds);// 5分钟之前的时间
    		return beforeTime.getTime();
    	}
    	
    	public static void main(String[] args) { 
    		System.out.println(getBeforeDateBySeconds(60));
    		
    	}
    }

## 演示效果： ##

### 1- 导入excel ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70]

### 导入结果： ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 1]

### 2- 导出 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 2]

### 3- 导出结果 ###

我们看到列表名称是按我们 pojo 类的order进行排序的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70]: /images/20230528/6557b668b6484b9180b57172a94dab1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 1]: /images/20230528/fd789d9920ff4e3daa73cf332abc903d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 2]: /images/20230528/e261568984ed49de93244fb65ca10514.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhbmdjaGFvMjAxMQ_size_16_color_FFFFFF_t_70 3]: /images/20230528/8735e22f96bd4b488ecd45f663d1e7fe.png