把自定义的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;
}
}