myapps调用自定义的Java类读取复杂的Excel

把自定义的Java类 编译后的class：

按包的路径放到classes文件夹下：

F:\program\WeiOA365\bin\apache-tomcat-7.0.57\webapps\obpm\WEB-INF\classes\com\dyiaw\excel

(function(){
		var aa=new Packages.com.dyiaw.excel.ExcelRead();
	    var list = createObject("java.util.ArrayList"); 
var request = $WEB.getParamsTable().getHttpRequest();//获取当前request       
var path = "/uploads/excel/2016/f29489c3-bd26-4f59-9488-af901c72feb9.xls"; 		
        list=aa.showExcel(path);//将该字段的值加密

        println("==================="+list.size());
		
		var formProcess = getFormProcess();  
		var process = getDocumentProcess();   
		var form = formProcess.doViewByFormName("excel_01" , getApplication());   
        
        for ( var i = 0; i < list.size(); i++) {  

		    var itemDoc = process.doNew(form,getWebUser(),createParamsTable());  
			println("==================="+list.get(i).getId());
			println("==================="+list.get(i).getName());
			println("==================="+list.get(i).getAge());
			
			//itemDoc.setParent(doc.getId());  
			itemDoc.setAuthor(getWebUser().getId());  
			//itemDoc.setIstmp(false);  
			itemDoc.setApplicationid(getApplication());  
			itemDoc.setDomainid(getDomainid());  
			  
			itemDoc.addStringItem("nid", list.get(i).getId());   
			itemDoc.addStringItem("username", list.get(i).getName());  
			itemDoc.addStringItem("age", list.get(i).getAge());  

			process.doCreate(itemDoc);  
        } 
	
})()

java代码：

package com.dyiaw.excel;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 考勤情况Excel表读取，
 * 两个实体类  汇总表、
 */
public class ExcelRead {
	
	/**
	 * 读取汇总表
	 * @param path
	 * @return
	 * @throws Exception
	 */
	public List<WorkRecordMonth> readExcelMonth(String path,Date month) throws Exception {
		
		String aPath=ExcelRead.class.getResource("").getFile();
		System.out.println(aPath);
		aPath = aPath.substring(1, aPath.lastIndexOf("WEB-INF"));
		path=aPath+path;
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(
				path)));
		HSSFSheet sheet = null;
		List<WorkRecordMonth> list = new ArrayList<WorkRecordMonth>();
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
			String sheetName = workbook.getSheetName(i);
			if("汇总表".equals(sheetName)){
				sheet = workbook.getSheetAt(i);
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
					HSSFRow row = sheet.getRow(j);
					if(j<4){//从第5行开始读取
						continue;
					}
					
					WorkRecordMonth wrm = new WorkRecordMonth();
					wrm.setWorkId((int) row.getCell(0).getNumericCellValue());//工号 1
					wrm.setUsername(row.getCell(1).getRichStringCellValue().getString().trim());//姓名 2
					wrm.setDepartment(row.getCell(2).getRichStringCellValue().getString().trim());//部门 3
					wrm.setStandardDuration((float) row.getCell(3).getNumericCellValue());//标准工时 4
					wrm.setRealityDuration((float) row.getCell(4).getNumericCellValue());//实际工时 5
					wrm.setLateTime((int) row.getCell(5).getNumericCellValue());//迟到次数 6
					wrm.setLateDuration((int) row.getCell(6).getNumericCellValue());//迟到时长 7
					wrm.setEarlyLeaveTime((int) row.getCell(7).getNumericCellValue());//早退次数 8
					wrm.setEarlyLeaveDuration((int) row.getCell(8).getNumericCellValue());//早退时长 9
					wrm.setOvertimeStandard((float) row.getCell(9).getNumericCellValue());//正常加班（时长）10
					wrm.setOvertimeHoliday((float) row.getCell(10).getNumericCellValue());//假日加班（时长）11
					wrm.setStandardRealityDay((int) row.getCell(11).getNumericCellValue());//标准/实际工作天数 12
					wrm.setEvectionDay((int) row.getCell(12).getNumericCellValue());//出差天数 13
					wrm.setAbsenteeismDay((int) row.getCell(13).getNumericCellValue());//旷工天数 14
					wrm.setLeaveDay((int) row.getCell(14).getNumericCellValue());//请假天数 15
					
					wrm.setMarkWage((float) row.getCell(15).getNumericCellValue());//标注工资 （加项工资）16
					wrm.setOvertimeWage((float) row.getCell(16).getNumericCellValue());//加班工资（加项工资）17
					wrm.setAllowanceWage((float) row.getCell(17).getNumericCellValue());//津贴（加项工资）18
					
					wrm.setLateEarlyWage((float) row.getCell(18).getNumericCellValue());//迟到/早退扣款 19
					wrm.setLeaveWage((float) row.getCell(19).getNumericCellValue());//请假 20
					wrm.setDeductionsWage((float) row.getCell(20).getNumericCellValue());//扣款 21
					
					wrm.setRealityWage((float) row.getCell(21).getNumericCellValue());//实际工资 22
					wrm.setMark(row.getCell(22).getRichStringCellValue().getString().trim());//备注23
					
					wrm.setMonth(month);
					list.add(wrm);
				}
			}
		}
		return list;
	}
	
	/**
	 * 读取刷卡表
	 * @param path
	 * @return
	 * @throws Exception
	 */
	public List<WorkRecordDay> readExcelDay(String path,Date day) throws Exception {
		
		String aPath=ExcelRead.class.getResource("").getFile();
		System.out.println(aPath);
		aPath = aPath.substring(1, aPath.lastIndexOf("WEB-INF"));
		path=aPath+path;
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(
				path)));
		HSSFSheet sheet = null;
		List<WorkRecordDay> list = new ArrayList<WorkRecordDay>();
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
			String sheetName = workbook.getSheetName(i);
			if("刷卡记录".equals(sheetName)){
				sheet = workbook.getSheetAt(i);
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
					HSSFRow row = sheet.getRow(j);
					if(j<4){//从第5行开始读取
						continue;
					}
					
					Integer workId = 0;
					String username = "";
					String department = "";
					if(j%2==0){//工号行
						workId = (int) row.getCell(3-1).getNumericCellValue();
						username = row.getCell(11-1).getRichStringCellValue().getString().trim();
						department = row.getCell(11-1).getRichStringCellValue().getString().trim();
					}else{//时间行
						for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格 (每个单元格是对应一条记录)
							String timeStr =row.getCell(k).toString().trim();
							WorkRecordDay wrd = new WorkRecordDay();
							wrd.setWorkId(workId);
							wrd.setUsername(username);
							wrd.setDepartment(department);
							wrd.setWorkTime(timeStr);
							wrd.setDay(day);
							list.add(wrd);
						}
					}
					

					
				}
			}
		}
		return list;
	}

}

两个sheet 分别对应的实体类：

综合汇总表实体类

package com.dyiaw.excel;

import java.util.Date;

/**
 * 工作记录(月汇总)
 */
public class WorkRecordMonth {
	
	private Integer workId;//工号 1
	
	private String username;//姓名 2
	
	private String department;//部门名称3
	
	private float standardDuration;//标准工时（小时）4
	
	private float realityDuration;//实际工时 （小时）5
	
	private Integer lateDuration;//迟到时长，（单位分钟）6
	
	private Integer lateTime;//迟到次数 7
	
	private Integer earlyLeaveDuration;//早退时长，（单位分钟）8
	
	private Integer earlyLeaveTime;//早退次数 9
	
	private float overtimeStandard;//正常加班（时长）10
	
	private float overtimeHoliday;//假日加班（时长）11
	
	private Integer standardRealityDay;//标准工作天数 /实际工作天数12
	
	//private Integer realityDay;//实际工作天数 
	
	private Integer evectionDay;//出差天数 13
	
	private Integer absenteeismDay;//旷工天数 14
	
	private Integer leaveDay;//请假天数 15
	
	private float markWage;//标注工资 （加项工资） 16
	
	private float overtimeWage;//加班工资（加项工资） 17
	
	private float allowanceWage;//津贴（加项工资）18
	
	private float  lateEarlyWage; //迟到/早退扣款 19
	
	private float leaveWage; //请假扣除的工资 20
	
	private float deductionsWage; // 扣款扣除的工资 21
	
	private float realityWage;// 实际工资 22
	
	private String mark;//备注  23
	
	private Date month;

	public Integer getWorkId() {
		return workId;
	}

	public void setWorkId(Integer workId) {
		this.workId = workId;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public float getStandardDuration() {
		return standardDuration;
	}

	public void setStandardDuration(float standardDuration) {
		this.standardDuration = standardDuration;
	}

	public float getRealityDuration() {
		return realityDuration;
	}

	public void setRealityDuration(float realityDuration) {
		this.realityDuration = realityDuration;
	}

	public Integer getLateDuration() {
		return lateDuration;
	}

	public void setLateDuration(Integer lateDuration) {
		this.lateDuration = lateDuration;
	}

	public Integer getLateTime() {
		return lateTime;
	}

	public void setLateTime(Integer lateTime) {
		this.lateTime = lateTime;
	}

	public Integer getEarlyLeaveDuration() {
		return earlyLeaveDuration;
	}

	public void setEarlyLeaveDuration(Integer earlyLeaveDuration) {
		this.earlyLeaveDuration = earlyLeaveDuration;
	}

	public Integer getEarlyLeaveTime() {
		return earlyLeaveTime;
	}

	public void setEarlyLeaveTime(Integer earlyLeaveTime) {
		this.earlyLeaveTime = earlyLeaveTime;
	}

	public float getOvertimeStandard() {
		return overtimeStandard;
	}

	public void setOvertimeStandard(float overtimeStandard) {
		this.overtimeStandard = overtimeStandard;
	}

	public float getOvertimeHoliday() {
		return overtimeHoliday;
	}

	public void setOvertimeHoliday(float overtimeHoliday) {
		this.overtimeHoliday = overtimeHoliday;
	}

	public Integer getStandardRealityDay() {
		return standardRealityDay;
	}

	public void setStandardRealityDay(Integer standardRealityDay) {
		this.standardRealityDay = standardRealityDay;
	}

	public Integer getEvectionDay() {
		return evectionDay;
	}

	public void setEvectionDay(Integer evectionDay) {
		this.evectionDay = evectionDay;
	}

	public Integer getAbsenteeismDay() {
		return absenteeismDay;
	}

	public void setAbsenteeismDay(Integer absenteeismDay) {
		this.absenteeismDay = absenteeismDay;
	}

	public Integer getLeaveDay() {
		return leaveDay;
	}

	public void setLeaveDay(Integer leaveDay) {
		this.leaveDay = leaveDay;
	}

	public float getMarkWage() {
		return markWage;
	}

	public void setMarkWage(float markWage) {
		this.markWage = markWage;
	}

	public float getOvertimeWage() {
		return overtimeWage;
	}

	public void setOvertimeWage(float overtimeWage) {
		this.overtimeWage = overtimeWage;
	}

	public float getAllowanceWage() {
		return allowanceWage;
	}

	public void setAllowanceWage(float allowanceWage) {
		this.allowanceWage = allowanceWage;
	}

	public float getLateEarlyWage() {
		return lateEarlyWage;
	}

	public void setLateEarlyWage(float lateEarlyWage) {
		this.lateEarlyWage = lateEarlyWage;
	}

	public float getLeaveWage() {
		return leaveWage;
	}

	public void setLeaveWage(float leaveWage) {
		this.leaveWage = leaveWage;
	}

	public float getDeductionsWage() {
		return deductionsWage;
	}

	public void setDeductionsWage(float deductionsWage) {
		this.deductionsWage = deductionsWage;
	}

	public float getRealityWage() {
		return realityWage;
	}

	public void setRealityWage(float realityWage) {
		this.realityWage = realityWage;
	}

	public String getMark() {
		return mark;
	}

	public void setMark(String mark) {
		this.mark = mark;
	}

	public Date getMonth() {
		return month;
	}

	public void setMonth(Date month) {
		this.month = month;
	}

}

刷卡表实体类：

package com.dyiaw.excel;

import java.util.Date;

/**
 * 工作记录(天汇总)
 */
public class WorkRecordDay {
	
	private Integer workId;
	
	private String username;
	
	private String department;
	
	private String workTime;
	//不好处理
//	private Date startTimeAm;
//	
//	private Date endTimeAm;
//	
//	private Date startTimePm;
//	
//	private Date endTimePm;
	
	private Date day;

	public Integer getWorkId() {
		return workId;
	}

	public void setWorkId(Integer workId) {
		this.workId = workId;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public String getWorkTime() {
		return workTime;
	}

	public void setWorkTime(String workTime) {
		this.workTime = workTime;
	}

	public Date getDay() {
		return day;
	}

	public void setDay(Date day) {
		this.day = day;
	}
	
}