模拟登录系列2-java 根据excl模板填充数据

需求：每日会在指定文件夹下生成一个excl文件，系统需要定时读取该文件，根据这个生成文件的数据去匹配填充指定excl模板数据，excl模板文件每天也需要从指定网站下载，并完成上传到指定网站；这里只关注excl的 数据读取 和 模板数据填充，定时任务 和 模板指定网站下载，模板上传指定网站不在该文章中。

该需求分为两块，一个是excl的 数据读取，另一个是模板数据填充；技术实现采用poi。

jar包：poi-3.10.1.jar,poi-ooxml-3.10.1.jar,poi-ooxml-schemas-3.10.1.jar

代码如下：

package com.cjhx.datamining.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel组件
 * 
 * @author 杨敏
 * @version 1.0
 * @since 1.0
 */
public  class ExcelHelper {

	/**
	 * Excel 2003
	 */
	private final static String XLS = "xls";
	/**
	 * Excel 2007
	 */
	private final static String XLSX = "xlsx";
	/**
	 * 分隔符
	 */
	private final static String SEPARATOR = "|";
	
	
	/**
	 * 解析Excel文件的Sheet，并返回List集合
	 * 
	 * @param fileUrl 文件地址
	 * @param sheetNum sheet页，默认0页
	 * @return
	 */
	public static List<List<Object>> getExportListFromExcel(String fileUrl, int sheetNum)
			throws IOException {
		return ExcelHelper.exportListFromExcel (new File(fileUrl), sheetNum);
		 
	}


	/**
	 * 由指定的Sheet导出至List - 根据url生产文件流程
	 * 
	 * @param file
	 * @param sheetNum
	 * @return
	 */
	public static List<List<Object>> exportListFromExcel(File file, int sheetNum)
			throws IOException {
		return exportListFromExcel(new FileInputStream(file),
				FilenameUtils.getExtension(file.getName()), sheetNum);
	}

	/**
	 * 由指定的Sheet导出至List - 判断excel格式版本
	 * 
	 * @param is
	 * @param extensionName
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
	public static List<List<Object>> exportListFromExcel(InputStream is,
			String extensionName, int sheetNum) throws IOException {

		Workbook workbook = null;

		if (extensionName.toLowerCase().equals(XLS)) {
			workbook = new HSSFWorkbook(is);
		} else if (extensionName.toLowerCase().equals(XLSX)) {
			workbook = new XSSFWorkbook(is);
		}

		return exportListFromExcel(workbook, sheetNum);
	}

	/**
	 * 由指定的Sheet导出至List - 数据解析处理
	 * 
	 * @param workbook
	 * @param sheetNum
	 * @return
	 * @throws IOException
	 */
	private static List<List<Object>> exportListFromExcel(Workbook workbook,
			int sheetNum) {

		Sheet sheet = workbook.getSheetAt(sheetNum);

		// 解析公式结果
		FormulaEvaluator evaluator = workbook.getCreationHelper()
				.createFormulaEvaluator();

		List<List<Object>> list = new ArrayList<List<Object>>();

		int minRowIx = sheet.getFirstRowNum();
		int maxRowIx = sheet.getLastRowNum();
		//System.out.println(minRowIx+"---->"+maxRowIx);
		short minColIx = 0;
		short maxColIx = 0;
		int k=0;
		for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
			Row row = sheet.getRow(rowIx);
			
			List<Object> l = new ArrayList<Object>();
			if(k==0){
				minColIx = row.getFirstCellNum();
				maxColIx = row.getLastCellNum();
				k++;
			}
			//System.out.println(minColIx+"---->"+maxColIx);
			int cellNum=0;//空单元格数量
			for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
				//System.out.println(colIx+":");
				Cell cell = row.getCell(new Integer(colIx));

				if (cell == null || cell.toString().trim().equals("")) {
					cellNum++;
					l.add(null);
					continue;
				}
				/**解决时间问题**/
				String strMerge=null;
				//判断是否为日期类型
				if(cell!=null){
					if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
						//用于转化为日期格式
						//System.out.println("date："+cell.getDateCellValue()+"==int:"+cell.getNumericCellValue()+"==判断值："+cell.getCellStyle().getDataFormat());
						SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						try{
							 boolean b = org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell);
							 if(b){
								 Date date = cell.getDateCellValue();
						         strMerge = formater.format(date);
						         cell.setCellValue(strMerge);
						         System.out.println("时间："+strMerge);
							 }
						}catch(Exception e){
							System.out.println("[error]导入excl时间转换异常！");
						}
					}
				}
				
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);//改变单元格的类型为String
				CellValue cellValue = evaluator.evaluate(cell);
				if (cellValue == null) {
					l.add(null);
					continue;
				}
				// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
				// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
				
				switch (cellValue.getCellType()) {
				case Cell.CELL_TYPE_BOOLEAN:
					
					l.add(cellValue.getBooleanValue());
					//sb.append(SEPARATOR + cellValue.getBooleanValue());
					break;
				case Cell.CELL_TYPE_NUMERIC:
					l.add(cell.getDateCellValue());
					// 这里的日期类型会被转换为数字类型，需要判别后区分处理
					// 注意这个类的应用 是org.apache.poi.ss.usermodel.DateUtil
					/*if (DateUtil.isCellDateFormatted(cell)) {
						l.add(cell.getDateCellValue());
						//sb.append(SEPARATOR + cell.getDateCellValue());
					} else {
//						int num = Integer.parseInt(cellValue.getNumberValue());
						String str = String.valueOf(cell.getNumericCellValue());
						l.add(str.substring(0, str.lastIndexOf(".")));
						//sb.append(SEPARATOR + cellValue.getNumberValue());
					}*/
					//System.out.println("非string:"+cellValue);
					break;
				case Cell.CELL_TYPE_STRING:
					//System.out.println(cellValue);
					l.add(cellValue.getStringValue());
					//sb.append(SEPARATOR + cellValue.getStringValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					//System.out.println(cellValue);
					break;
				case Cell.CELL_TYPE_BLANK:
					//System.out.println(cellValue);
					break;
				case Cell.CELL_TYPE_ERROR:
					//System.out.println(cellValue);
					break;
				default:
					//System.out.println(cellValue);
					break;
				}
				
			}
			if(cellNum < maxColIx){
				list.add(l);
			}
			
		}
		return list;
	}
	 /**
     * 根据指定的excl文件模板填充数据-银行间非法人产品净值日报
     * @param fileUrl 文件模板url
     * @param sheetNum 填充的sheet 位置
     * @param fieldstr 表头标题，逗号隔开
     * @param startRow 填充shett 的起始行
     * @return
     */
    public static String dwexcl(List<List<Object>> list,String fileUrl,int sheetNum,String fieldstr,int startRow){

    	Workbook workbook = null;
    	//创建文件对象
    	File file = new File(fileUrl);
    	InputStream is;
		try {
			is = new FileInputStream(file);
	    	String extensionName = FilenameUtils.getExtension(file.getName());
	    	//判断excel格式版本
			if (extensionName.toLowerCase().equals(XLS)) {
					workbook = new HSSFWorkbook(is);
			} else if (extensionName.toLowerCase().equals(XLSX)) {
				workbook = new XSSFWorkbook(is);
			}
    	} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	//获取填充的sheet页
        Sheet sheet = workbook.getSheetAt(sheetNum);
        //填充表头
        Row topRow = sheet.getRow(startRow);// 获取插入开始行
        if("".equals(fieldstr)){
        	 String[] strArray = fieldstr.split(",");//解析表头字符串
             for (int i = 0; i < strArray.length; i++) {
             	Cell cellTop = topRow.getCell(i);//获取单元格对象
             	cellTop.setCellValue(strArray[i]);//设置表头
             }
        }
       
        //注意 以下 下标都是0开始。
        for(int i=0;i<list.size();i++){
        	List<Object> t = list.get(i);
        	String value = t.get(3).toString();//获取净值，数据固定第4位
        	String name = t.get(1).toString();//获取产品全称，数据固定第2位
        	
        	int rowNum=sheet.getLastRowNum();//获取总行数
        	//遍历模板行数据
        	for(int j=startRow+1;j<rowNum;j++){//startRow+1 开始，不用表头和表头前的行
        		Row row = sheet.getRow(j);// 获取模板插入开始行startRow+1
        		String tempName = row.getCell(0).getStringCellValue();//获取模板的产品全称，模板固定第1位
        		if(tempName.equals(name)){//当产品名称相等时 则填充数据
        			Cell cellValue = row.getCell(4);//获取净值列对象，固定低5位
        			cellValue.setCellValue(value);//填充净值
        			System.out.println("产品 【"+tempName+"】已匹配到数据，正在填充数据.....");
        			continue;
        		}
        		if(j==rowNum-1){
        			System.out.println("数据没有在模板中找到匹配项，产品全称为："+name);
        		}
        	}
        }

       
        // 第六步，将文件存到指定位置
        String[] fileUrlStr = fileUrl.split("\\.");
        String dateStr = DateUtil.formatDate(new Date(),"yyyyMMdd");
        String fileOutUrl = fileUrlStr[0]+dateStr+"."+fileUrlStr[1];
        try {
            FileOutputStream fout = new FileOutputStream(fileOutUrl);
            workbook.write(fout);
            fout.close();
            System.out.println("已生成文件："+fileOutUrl);
        } catch (Exception e) {
            e.printStackTrace();
        }
		return fileOutUrl;
    	
    }


    /**
     * 导出新建excl
     * @param Fieldstr 列明
     * @param tableName 
     * @return
     */
    public static String dwexcl(String Fieldstr,String tableName){
    	
    	String[] strArray = Fieldstr.split(",");
    	// 第一步，创建一个webbook，对应一个Excel文件
        //HSSFWorkbook wb = new HSSFWorkbook();
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet("sheet1");
       // sheet.setDefaultColumnWidth(20);// 默认列宽
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //String[] strArray={"序号","姓名","年龄"};
        // 添加excel title
        XSSFCell cell = null;
        
       
        for (int i = 0; i < strArray.length; i++) {
            cell = row.createCell((short) i);
            cell.setCellValue(strArray[i]);
            cell.setCellStyle(style);
        }

       String dateStr = DateUtil.formatDate(new Date(),"yyyyMMdd");
        // 第六步，将文件存到指定位置
        String fileUrl = "D:\\oms_baogao\\yhjrb\\非法人产品列表模板"+dateStr+".xls";
        try {
            FileOutputStream fout = new FileOutputStream(fileUrl);
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
		return fileUrl;
    	
    }
   
    public static void main(String[] args) {
    	//20191113 022713
    	List<List<Object>> list;
		try {
			list = ExcelHelper.getExportListFromExcel("D:\\oms_baogao\\yhjrb\\银行间每日报表.xls",0);
			String Fieldstr ="产品全称1,产品简称1,产品类型1,所属日期1,净资产（亿元）1 ";
	    	ExcelHelper.dwexcl(list,"D:\\oms_baogao\\yhjrb\\非法人产品列表模板.xls", 0, "", 1);
		
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	}
   
}

引用的时间处理类的转换函数，DateUtil 有很多其他内容，我这里就不贴出来了，如下是时间转换方法代码。

/**
	 * 将Date对象转换为指定格式的字符串 支持 yyyyMMdd  数字格式
	 * @param date 日期
	 * @param pattern 格式
	 * @return
	 * @throws Exception 
	 */
	public static String formatDate(Date date, String pattern) {
		try {
			DateFormat formatter = new SimpleDateFormat(pattern);
			return formatter.format(date);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

关于 excl文件的数据格式，我代码中有很多写死的下标，具体需求 需要具体 来情况处理，我这里是情况如下：

定时读取的excl格式：

被填充的模板excl中，已经自带了部分数据了，只要根据产品全称匹配完成，然后将净值数据填入到对应单元格即可：

运行结果如下：