poi实现的excel模板数据填充

解析并填充数据：

在这里插入代码片
package com.dys.app.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Author: dys Date: 2020-03-18 Time: 10:21 Description: 填充Excel模板
 */
public class ExcelReader {
	private static Logger logger = Logger.getLogger(ExcelReader.class.getName());

	private static final String XLS = ".xls";
	private static final String XLSX = ".xlsx";

	/**
	 * 读取excel模板
	 * @param excelFile
	 * @param fos	
	 * @param dataList
	 */
	public static void readExcel(File excelFile, OutputStream fos, List<ExcelEntity> dataList) {
		Workbook workbook = null;
		FileInputStream inputStream = null;
		try {
			// 获取Excel文件
			if (!excelFile.exists()) {
				logger.debug("指定的Excel文件不存在！");
			}
			// 获取Excel工作簿
			inputStream = new FileInputStream(excelFile);
			if (excelFile.getName().toLowerCase().endsWith(XLS)) {
				workbook = new HSSFWorkbook(inputStream);
			} else if (excelFile.getName().toLowerCase().endsWith(XLSX)) {
				workbook = new XSSFWorkbook(inputStream);
			}

			// 读取excel中的数据
			parseExcel(workbook, dataList, fos);
		} catch (Exception e) {
			logger.debug("解析Excel失败，文件名：" + excelFile.getName() + " 错误信息：" + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				if (null != workbook) {
					workbook.close();
				}
				if (null != inputStream) {
					inputStream.close();
				}
				if (null != fos) {
					fos.close();
				}
				if(excelFile.exists()){
					excelFile.delete();
				}
			} catch (Exception e) {
				logger.debug("关闭数据流出错！错误信息：" + e.getMessage());
				e.printStackTrace();
			}
		}
	}

	/**
	 * 填充Excel数据
	 * 
	 * @param workbook
	 * @throws IOException 
	 */
	private static void parseExcel(Workbook workbook, List<ExcelEntity> dataList, OutputStream fos) throws IOException {
		// 解析sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = workbook.getSheetAt(sheetNum);
			// 校验sheet是否合法
			if (sheet == null) {
				continue;
			}

			// 判断模板是否有表头
			int firstRowNum = sheet.getFirstRowNum();
			Row firstRow = sheet.getRow(firstRowNum);
			if (null == firstRow) {
				logger.debug("解析Excel失败，在第一行没有读取到任何数据！");
			}

			// 获取总列数
			int rowNum = firstRow.getPhysicalNumberOfCells();
			for (int n = 0; n < dataList.size(); n++) {
				for (int r = 0; r < rowNum; r++) {
					// 获取行
					Cell cell = getCell(sheet, n+1, (short) r);
					Object o = getValueFormObject(dataList.get(n), "cell" + (r+1));
					if(null != o){
						cell.setCellValue(o.toString());
					}
				}
			}
		}
		workbook.write(fos);
	}

	//处理表格
	public static Cell getCell(Sheet sheet, int rowIndex, short columnIndex) {
		// 获取行，不存在的话则创建
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		// 获取列，不存在的话则创建
		Cell cell = row.getCell(columnIndex);
		if (cell == null) {
			cell = row.createCell(columnIndex);
		}
		return cell;
	}

	/**
	 * 根据属性，对象的值
	 * 
	 * @param ob 对象
	 * @param name 属性名
	 * @return
	 * @throws Exception
	 */
	public static Object getValueFormObject(Object ob, String name) {
		Method[] m = ob.getClass().getMethods();
		for (int i = 0; i < m.length; i++) {
			if (("get" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
				try {
					return m[i].invoke(ob);
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
}

excel数据封装实体类：

在这里插入代码片
package com.dys.app.util;

import java.io.Serializable;
/**
 * 
 * 用于excel数据填充时使用
 * @author dys
 * 
 *
 */
public class ExcelEntity implements Serializable{
	private static final long serialVersionUID = 1L;
	
	public Object cell1;
	public Object cell2;
	public Object cell3;
	public Object cell4;
	public Object cell5;
	public Object cell6;
	public Object cell7;
	public Object cell8;
	public Object cell9;
	public Object cell10;
	
	public Object getCell1() {
		return cell1;
	}
	public void setCell1(Object cell1) {
		this.cell1 = cell1;
	}
	public Object getCell2() {
		return cell2;
	}
	public void setCell2(Object cell2) {
		this.cell2 = cell2;
	}
	public Object getCell3() {
		return cell3;
	}
	public void setCell3(Object cell3) {
		this.cell3 = cell3;
	}
	public Object getCell4() {
		return cell4;
	}
	public void setCell4(Object cell4) {
		this.cell4 = cell4;
	}
	public Object getCell5() {
		return cell5;
	}
	public void setCell5(Object cell5) {
		this.cell5 = cell5;
	}
	public Object getCell6() {
		return cell6;
	}
	public void setCell6(Object cell6) {
		this.cell6 = cell6;
	}
	public Object getCell7() {
		return cell7;
	}
	public void setCell7(Object cell7) {
		this.cell7 = cell7;
	}
	public Object getCell8() {
		return cell8;
	}
	public void setCell8(Object cell8) {
		this.cell8 = cell8;
	}
	public Object getCell9() {
		return cell9;
	}
	public void setCell9(Object cell9) {
		this.cell9 = cell9;
	}
	public Object getCell10() {
		return cell10;
	}
	public void setCell10(Object cell10) {
		this.cell10 = cell10;
	}
	
	@Override
	public String toString() {
		return "ExcelEntity [cell1=" + cell1 + ", cell2=" + cell2 + ", cell3=" + cell3 + ", cell4=" + cell4 + ", cell5="
				+ cell5 + ", cell6=" + cell6 + ", cell7=" + cell7 + ", cell8=" + cell8 + ", cell9=" + cell9
				+ ", cell10=" + cell10 + "]";
	}
}