excel带公式导出表

/**
* 新的补贴表
* 
* @param mapping
* @param form
* @param request
* @param response
*/
public void exportSubsidiesExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
List<BatchConset> batchConset = batchConsetService
.findBatchConsetByIsUse(1);
if (batchConset != null && batchConset.size() == 1) {
List<Hdsetting> hdSettingList = hdsettingService
.findHdsettingByStartEndDate(batchConset.get(0)
.getStatisStartDay(), batchConset.get(0)
.getStatisEndDay());
String statisMon = batchConset.get(0).getStatisMon();
response.reset();
response.setHeader("Content-Type", "application/vnd.ms-excel");
try {
response.setHeader("Content-Disposition",
"attachment;filename="
+ java.net.URLEncoder.encode("new补贴表.xls",
"UTF-8"));
ServletOutputStream os = response.getOutputStream(); // 获得输出流
String filePath = request.getSession().getServletContext()
.getRealPath("/excel/model/new补贴表.xls");
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filePath));
HSSFSheet sheetOne = workbook.getSheet("补贴统计"); // 读取第一个工作簿
HSSFRow oneRow;
int oneRowNum = 5; // 添加的起始行
Set<USRecord> userList = this
.getAllUSRecordByBatchConset(batchConset);
Set<Subsidies> subsidiesSet = new HashSet<Subsidies>();
for (USRecord record : userList) {
Subsidies subsidies = subsidiesService
.findSubsidiesByStatisMon(statisMon, record
.getUsid());
if (subsidies != null) {
subsidiesSet.add(subsidies);
}

}
// 得第一行
oneRow = sheetOne.getRow(0);
oneRow.getCell(0).setCellValue(
"*****" + batchConset.get(0).getStatisMon() + "各项补贴统计表");
// 得第二行
oneRow = sheetOne.getRow(1);
Integer realAttendance = Tools.twoDateDay(batchConset.get(0)
.getStatisStartDay(), batchConset.get(0)
.getStatisEndDay(), hdSettingList);
oneRow.getCell(11).setCellValue(realAttendance);
Iterator<Subsidies> subsidiesIterator = subsidiesSet.iterator();
int i = 1; // 序号初始值
Subsidies subsidie = null;
while (subsidiesIterator.hasNext()) {
subsidie = (Subsidies) subsidiesIterator.next();
if (oneRowNum > 5) {
oneRow = sheetOne.createRow(oneRowNum);
this.copyCellStyleOfRow(sheetOne, sheetOne.getRow(5),
oneRow, 37);
} else {
oneRow = sheetOne.getRow(oneRowNum);
}
USRecord usrecordByUsid = usrecordService
.findUSRecordByUSID(subsidie.getUsid());
DPRecord dpRecordByDpid = dprecordService
.findDPRecordByDPID(usrecordByUsid.getDpid());
oneRow.getCell(0).setCellValue(i++);// 序号
oneRow.getCell(1).setCellValue(dpRecordByDpid.getDpname());// 部门
oneRow.getCell(2).setCellValue(dpRecordByDpid.getDpid());// 部门序号
oneRow.getCell(3).setCellValue(
usrecordByUsid.getEnglishName());// 英文名
oneRow.getCell(4).setCellValue(
usrecordByUsid.getJobNumber());// 员工号
oneRow.getCell(5).setCellValue(
usrecordByUsid.getChineseName());// 姓名
String sixBuf = "ROUND(AE"+(oneRowNum+1)+"-AF"+(oneRowNum+1)+"-AH"+(oneRowNum+1)+"-AG"+(oneRowNum+1)+",2)";
oneRow.getCell(6).setCellFormula(sixBuf.toString());// 实际出勤天数(带公式) =ROUND(AE6-AF6-AH6-AG6,2)
oneRow.getCell(7).setCellFormula("ROUND(G6*"+(oneRowNum+1)+",2)");// 计薪小时(带公式) =ROUND(G6*8,2)
double doTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getLdoTotalTimeq()));
oneRow.getCell(8).setCellValue(doTotalTime);// 调休小时
double anTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getLanTotalTime()));
oneRow.getCell(9).setCellValue(anTotalTime);// 年假小时
double thTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getLthTotalTime()));
oneRow.getCell(10).setCellValue(thTotalTime);// 事假小时
double siTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getLsiTotalTime()));
oneRow.getCell(11).setCellValue(siTotalTime);// 病假小时
double wdoaTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getWdoatotalTime()));
oneRow.getCell(12).setCellValue(wdoaTotalTime);// 平时加班23点前
double wdocTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getWdoctotalTime()));
oneRow.getCell(13).setCellValue(wdocTotalTime);// 平时加班23点后
String t14 = "M"+(oneRowNum+1)+"*15+N"+(oneRowNum+1)+"*30";
oneRow.getCell(14).setCellFormula(t14);// 加班津贴1(带公式) =M6*15+N6*30
double hdobTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getHdobtotalTime()));
oneRow.getCell(15).setCellValue(hdobTotalTime);// 节假日加班23点前
double hdodTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidie.getHdodtotalTime()));
oneRow.getCell(16).setCellValue(hdodTotalTime);// 节假日加班23点后
String t17 = "P"+(oneRowNum+1)+"*20+Q"+(oneRowNum+1)+"*30";
oneRow.getCell(17).setCellFormula(t17);// 加班津贴2(带公式) =P6*20+Q6*30
String t18 = "ROUND(O"+(oneRowNum+1)+"+R"+(oneRowNum+1)+",2)";
oneRow.getCell(18).setCellFormula(t18);// 加班津贴合计(带公式) =ROUND(O6+R6,2)
oneRow.getCell(19).setCellValue(
Tools.numIsNULL(subsidie.getLunchAtotalNum()));// 午餐补贴次数
oneRow.getCell(20).setCellFormula("T"+(oneRowNum+1)+"*16");// 午餐补贴(带公式) =T6*16
oneRow.getCell(21).setCellValue(
Tools.numIsNULL(subsidie.getDinnerAtotalNum()));// 晚餐补贴次数
oneRow.getCell(22).setCellFormula("V"+(oneRowNum+1)+"*16");// 晚餐补贴(带公式) =V7*16
oneRow.getCell(23).setCellValue(
Tools.numIsNULL(subsidie.getHdcarAtotalNum()));// 节假日车贴次数
oneRow.getCell(24).setCellFormula("X"+(oneRowNum+1)+"*15");// 节假日车贴(带公式) =X7*15
oneRow.getCell(25).setCellFormula("U"+(oneRowNum+1)+"+W"+(oneRowNum+1)+"+Y"+(oneRowNum+1)+"");// 本月实际餐/车贴小计(带公式) =U6+W6+Y6
oneRow.getCell(26).setCellValue(
Tools.doubleformat(subsidie.getMakeupLessMoney()));// 补上月少计金额
oneRow.getCell(27).setCellFormula("ROUND(AA"+(oneRowNum+1)+"+Z"+(oneRowNum+1)+",2)");// 相应发放餐/车贴总额(带公式) =ROUND(AA6+Z6,2)
oneRow.getCell(28).setCellFormula("AB"+(oneRowNum+1)+"+S"+(oneRowNum+1));// 合计(带公式) =AB6+S6
String t29 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",离入职!A:E,4,FALSE))=TRUE,\"\",CONCATENATE(MONTH(VLOOKUP(E"+(oneRowNum+1)+",离入职!A:E,4,FALSE)),\"月\",DAY(VLOOKUP(E"+(oneRowNum+1)+",离入职!A:E,4,FALSE)),\"日\",VLOOKUP(E"+(oneRowNum+1)+",离入职!A:E,5,FALSE)))";
oneRow.getCell(29).setCellFormula(t29);// 备注(带公式)
oneRow.getCell(30).setCellFormula("L2");// 当月工作天数(带公式) =L2
String t31 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",缺勤扣除!D:I,5,FALSE))=TRUE,0,VLOOKUP(E"+(oneRowNum+1)+",缺勤扣除!D:I,5,FALSE)*24/8)";
oneRow.getCell(31).setCellFormula(t31);// 缺勤扣除天数(带公式) 
oneRow.getCell(32).setCellFormula("K"+(oneRowNum+1)+"/8");// 事假扣除天数(带公式) =K6/8
String t33 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",离入职!A:F,6,FALSE))=TRUE,0,AE"+(oneRowNum+1)+"-VLOOKUP(E"+(oneRowNum+1)+",离入职!A:F,6,FALSE))";
oneRow.getCell(33).setCellFormula(t33);// 离入职缺勤天数扣除天数(带公式)
String t34 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",自备车!A:C,4,FALSE))=TRUE,\"\",\"是\")";
oneRow.getCell(34).setCellFormula(t34);// 自备车贴享受人员(带公式)
String t35 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",特殊处理!A:E,4,FALSE))=TRUE,\"\",VLOOKUP(E"+(oneRowNum+1)+",特殊处理!A:E,4,FALSE))";
oneRow.getCell(35).setCellFormula(t35);// 特殊处理 事项(带公式)
String t36 = "IF(ISNA(VLOOKUP(E"+(oneRowNum+1)+",特殊处理!A:E,5,FALSE))=TRUE,\"\",VLOOKUP(E"+(oneRowNum+1)+",特殊处理!A:E,5,FALSE))";
oneRow.getCell(36).setCellFormula(t36);// 特殊处理 操作(带公式)
oneRowNum++;
}
sheetOne.setForceFormulaRecalculation(true);
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
Tools.log.error(e.getMessage());
}
}
}

/**
* 导出Excel补贴表 doSubsidiesExcel
* 
* @param userList
* @param request
* @param response
*/
public void doSubsidiesExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
List<BatchConset> batchConset = batchConsetService
.findBatchConsetByIsUse(1);
response.reset();
response.setHeader("Content-Type", "application/vnd.ms-excel");
try {
response.setHeader("Content-Disposition", "attachment;filename="
+ java.net.URLEncoder.encode("补贴表.xls", "UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
Tools.log.error(e.getMessage());
}
try {
ServletOutputStream os = response.getOutputStream(); // 获得输出流
String filePath = request.getSession().getServletContext()
.getRealPath("/excel/model/补贴表.xls");
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filePath));
HSSFSheet sheet = workbook.getSheetAt(0); // 读取第一个工作簿
HSSFRow row;
int rownum = 5; // 添加的起始行
Double total1Sum = new Double(0);
Double total2Sum = new Double(0);
Double totalSum = new Double(0);
Double lunchTime = new Double(0);
Double lunchAtotal = new Double(0);
Double dinnerTime = new Double(0);
Double dinnerAtotal = new Double(0);
Double hdcarAtotalTime = new Double(0);
Double hdcarAtotalNum = new Double(0);
Double btotalSum = new Double(0);
Double mappingakeupLessMoney = new Double(0);
Double mtotal = new Double(0);
Double sum = new Double(0);
// 得第一行
row = sheet.getRow(0);
List<BatchConset> batchConsets = batchConsetService
.findBatchConsetByIsUse(1);
row.getCell(0).setCellValue(
"****" + batchConsets.get(0).getStatisMon() + "各项补贴统计表");
// 的第二行
row = sheet.getRow(1);
Integer danceDay = batchConsets.get(0).getThisAttendanceDay() == null ? 0
: batchConsets.get(0).getThisAttendanceDay();
row.getCell(10).setCellValue("当月出勤日: " + danceDay + "天");
Set<USRecord> userList = this
.getAllUSRecordByBatchConset(batchConset);
if (Tools.checkObjectsIsNotNullSet(userList)) {
for (USRecord record : userList) {
if (batchConsets.size() == 1 || batchConsets != null) {
List<Subsidies> subsidiesList = subsidiesService
.findSubsidiesByUsid(record.getUsid(),
batchConsets.get(0).getStatisMon());
Iterator<Subsidies> iterator = subsidiesList.iterator();
Subsidies subsidies = null;
while (iterator.hasNext()) {
subsidies = (Subsidies) iterator.next();
double prinWdoaTotalTime = Tools
.formatTimeTen(subsidies.getWdoatotalTime());
double prinWdocTotalTime = Tools
.formatTimeTen(subsidies.getWdoctotalTime());
WdoaSetting wdoaSetting = wdoaSettingService
.findWdoaSettingByUse();
// A C 类加班津贴
double total1 = (prinWdoaTotalTime * wdoaSetting
.getAllowancesA())
+ (prinWdocTotalTime * wdoaSetting
.getAllowancesC());// 加班津贴1
double prinHdobTotalTime = Tools
.formatTimeTen(subsidies.getHdobtotalTime());
double prinHdodTotalTime = Tools
.formatTimeTen(subsidies.getHdodtotalTime());
HdoaSetting hdoaSetting = hdoaSettingService
.findHdoaSettingsByUse();
double total2 = 0;
if (prinHdodTotalTime >= 0) {
total2 = (prinHdobTotalTime * hdoaSetting
.getAllowancesB())
+ (prinHdodTotalTime * hdoaSetting
.getAllowancesD());// 加班津贴2
}
MaSetting maSetting = maSettingService
.findMaSettingsByUseTwo();
HDCASetting hdcaSetting = hdcaSettingService
.findHdcaSettingByUseTwo();
long btotal = subsidies.getLunchAtotalNum()
* maSetting.getLunchAllowances()
+ subsidies.getDinnerAtotalNum()
* maSetting.getDinnerAllowances()
+ subsidies.getHdcarAtotalNum()
* hdcaSetting.getAllowances();// 本月实际餐/车贴小计

// 合计行 第五行
row = sheet.getRow(4);
total1Sum += total1;
total2Sum += total2;
totalSum += total1 + total2;
lunchTime += subsidies.getLunchAtotalNum();
lunchAtotal += subsidies.getLunchAtotalNum()
* maSetting.getLunchAllowances();
dinnerTime += subsidies.getDinnerAtotalNum();
dinnerAtotal += subsidies.getDinnerAtotalNum()
* maSetting.getDinnerAllowances();
hdcarAtotalTime += subsidies.getHdcarAtotalNum();
hdcarAtotalNum += subsidies.getHdcarAtotalNum()
* hdcaSetting.getAllowances();
btotalSum += btotal;
mappingakeupLessMoney += subsidies
.getMakeupLessMoney();
mtotal += btotal + subsidies.getMakeupLessMoney();
sum += btotal + subsidies.getMakeupLessMoney()
+ total1 + total2;
row.getCell(14).setCellValue(
Tools.doubleformat(total1Sum));// 加班津贴（1）
row.getCell(17).setCellValue(
Tools.doubleformat(total2Sum));// 加班津贴（2）
row.getCell(18).setCellValue(
Tools.doubleformat(totalSum));// 加班津贴合计
row.getCell(19).setCellValue(lunchTime);// 午餐费（次）
row.getCell(20).setCellValue(
Tools.doubleformat(lunchAtotal));// 午餐补贴
row.getCell(21).setCellValue(dinnerTime);// 晚餐费（次）
row.getCell(22).setCellValue(
Tools.doubleformat(dinnerAtotal));// 晚餐补贴
row.getCell(23).setCellValue(hdcarAtotalTime);// 节假日车贴（次）
row.getCell(24).setCellValue(
Tools.doubleformat(hdcarAtotalNum));// 节假日车贴
row.getCell(25).setCellValue(
Tools.doubleformat(btotalSum));// 本月实际餐/车贴小计
row.getCell(26).setCellValue(
Tools.doubleformat(mappingakeupLessMoney));// 补上月少计入金额
row.getCell(27).setCellValue(
Tools.doubleformat(mtotal));// //本月应发放餐/车贴总额（前两项相加）
row.getCell(28).setCellValue(
Tools.doubleformat(sum));// 合计
// 创建第五行数据行
if (rownum > 5) {
row = sheet.createRow(rownum);
this.copyCellStyleOfRow(sheet, sheet.getRow(5),
row, 30);
} else {
row = sheet.getRow(rownum);
}
row.getCell(0).setCellValue(0);// 序号
row.getCell(1).setCellValue(subsidies.getDpname());// 部门
row.getCell(2).setCellValue(
subsidies.getDpid().substring(2,
subsidies.getDpid().length()));// 部门序号
row.getCell(3).setCellValue(
subsidies.getEnglishName());// 英文名
row.getCell(4).setCellValue(record.getJobNumber());// 员工号
row.getCell(5).setCellValue(
subsidies.getChineseName());// 姓名
row.getCell(6).setCellValue(
subsidies.getRealAttendance());// 实际出勤天数
row.getCell(7).setCellValue(
subsidies.getRealAttendance() * 8);// 记薪小时
double doTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidies.getLdoTotalTimeq()));
row.getCell(8).setCellValue(doTotalTime);// 调休(H)
double anTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidies.getLanTotalTime()));
row.getCell(9).setCellValue(anTotalTime);// 年假(H)
double thTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidies.getLthTotalTime()));
row.getCell(10).setCellValue(thTotalTime);// 事假(H)
double siTotalTime = Tools.formatTimeTen(Tools
.strIsNull(subsidies.getLsiTotalTime()));
row.getCell(11).setCellValue(siTotalTime);// 病假(H)
row.getCell(12).setCellValue(prinWdoaTotalTime);// 平常加班时间(23点前)
row.getCell(13).setCellValue(prinWdocTotalTime);// 平常加班时间(23点后)
row.getCell(14).setCellValue(
Tools.doubleformat(total1));// 加班津贴(1)【平常加班时间(23点前)*15+平常加班时间(23点后)*30】
row.getCell(15).setCellValue(prinHdobTotalTime);// 节假日加班时间(23点前)
row.getCell(16).setCellValue(prinHdodTotalTime);// 节假日加班时间(23点后)
row.getCell(17).setCellValue(
Tools.doubleformat(total2));// 加班津贴(2)【节假日加班时间(23点前)*15+节假日加班时间(23点后)*30】
row.getCell(18).setCellValue(
Tools.doubleformat(total1 + total2));// 加班津贴(合计)【加班津贴(1)+加班津贴(2)】
row.getCell(19).setCellValue(
Tools.numIsNULL(subsidies
.getLunchAtotalNum()));// 午餐费(次)
row.getCell(20).setCellValue(
Tools.doubleformat(subsidies
.getLunchAtotalNum()
* maSetting.getLunchAllowances()));// 午餐补贴【午餐费(次)*16】
row.getCell(21).setCellValue(
Tools.numIsNULL(subsidies
.getDinnerAtotalNum()));// 晚餐费(次)
row.getCell(22).setCellValue(
Tools.doubleformat(subsidies
.getDinnerAtotalNum()
* maSetting.getDinnerAllowances()));// 晚餐补贴【晚餐费(次)*16】
row.getCell(23).setCellValue(
Tools.numIsNULL(subsidies
.getHdcarAtotalNum()));// 节假日车贴(次)
row.getCell(24).setCellValue(
Tools.doubleformat(subsidies
.getHdcarAtotalNum()
* hdcaSetting.getAllowances()));// 节假日车贴【节假日车贴(次)*15】
row.getCell(25).setCellValue(
Tools.doubleformat(btotal));// 本月实际餐/车贴小计
row.getCell(26).setCellValue(
Tools.doubleformat(subsidies
.getMakeupLessMoney()));// 补上月上计入金额
row.getCell(27).setCellValue(
Tools.doubleformat(btotal
+ subsidies.getMakeupLessMoney()));// 本月应发放餐/车贴总额（前两项相加）
row.getCell(28).setCellValue(
Tools.doubleformat(btotal
+ subsidies.getMakeupLessMoney()
+ total1 + total2));// 合计
row.getCell(29).setCellValue(subsidies.getRemark());// 备注
rownum++;
}
sheet.setForceFormulaRecalculation(true);
}
}
}
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
Tools.log.error(e.getMessage());
}

}

/**
* 缺勤表导出
* 
* @param mapping
* @param form
* @param request
* @param response
* @return
*/
public void doDudyAdetailExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
List<BatchConset> batchConset = batchConsetService
.findBatchConsetByIsUse(1);
if (batchConset != null && batchConset.size() == 1) {
String statiMonth = batchConset.get(0).getStatisStartDay() + "/"
+ batchConset.get(0).getStatisEndDay();
String startDay = batchConset.get(0).getStatisStartDay();
String endDay = batchConset.get(0).getStatisEndDay();
response.reset();
response.setHeader("Content-Type", "application/vnd.ms-excel");
try {
response.setHeader("Content-Disposition",
"attachment;filename="
+ java.net.URLEncoder.encode("new缺勤统计表.xls",
"UTF-8"));
ServletOutputStream os = response.getOutputStream(); // 获得输出流
String filePath = request.getSession().getServletContext()
.getRealPath("/excel/model/new缺勤统计表.xls");
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filePath));
Set<USRecord> userList = this
.getAllUSRecordByBatchConset(batchConset);
Set<AStatisDetails> astatisDetailSet = new HashSet<AStatisDetails>();
Set<ADetails> adetailSet = new HashSet<ADetails>();
for (USRecord record : userList) {
AStatisDetails astatisDetails = astatisDetailService
.findAStatisDetailsByUsidAndMonth(record.getUsid(),
statiMonth);
astatisDetailSet.add(astatisDetails);
List<ADetails> adetails = adetailService
.findDudyADetailsByUsid(record.getUsid(), startDay,
endDay);
adetailSet.addAll(adetails);
}
// 缺勤人员统计表
HSSFSheet sheetOne = workbook.getSheet("Sheet1");
HSSFRow oneRow;
int oneRowNum = 3;
Iterator<AStatisDetails> astatisDetailIterator = astatisDetailSet
.iterator();
int i = 1;
AStatisDetails astatisDetail = null;
while (astatisDetailIterator.hasNext()) {
astatisDetail = astatisDetailIterator.next();
if (oneRowNum > 3) {
oneRow = sheetOne.createRow(oneRowNum);
this.copyCellStyleOfRow(sheetOne, sheetOne.getRow(3),
oneRow, 9);
} else {
oneRow = sheetOne.getRow(oneRowNum);
}
oneRow.getCell(0).setCellValue(i++);// NO
oneRow.getCell(1).setCellValue(astatisDetail.getBuName()); // 事业部
oneRow.getCell(2).setCellValue(astatisDetail.getDpName()); // 部门
USRecord usrecordByUsid = usrecordService
.findUSRecordByUSID(astatisDetail.getUsid());
oneRow.getCell(3).setCellValue(
usrecordByUsid.getJobNumber());// 员工号
oneRow.getCell(4).setCellValue(
astatisDetail.getChineseName()); // 中文名
oneRow.getCell(5).setCellValue(
astatisDetail.getEnglishName()); // 英文名
oneRow.getCell(6)
.setCellValue(
Tools.numIsNULL(astatisDetail
.getAbsenceTotalNum())); // 累计次数
if (!Tools.checkTimeIsNotNull(astatisDetail
.getAbsenceTotalTime())) {
oneRow.getCell(7).setCellValue(""); // 累计时间
} else {
oneRow.getCell(7).setCellValue(
Tools.strIsNull(astatisDetail
.getAbsenceTotalTime())); // 累计时间
}
String remark = astatisDetail.getRemark() == null ? ""
: astatisDetail.getRemark();
oneRow.getCell(8).setCellValue(remark); // 备注
oneRowNum++;
}
// 缺勤统计表
HSSFSheet sheetTwo = workbook.getSheet("Sheet2"); // 缺勤统计表
HSSFRow twoRow;
int twoRownum = 4; // 添加的起始行
twoRow = sheetTwo.getRow(1);
twoRow.getCell(4).setCellValue(statiMonth);
Iterator<ADetails> iterator = adetailSet.iterator();
ADetails adetail = null;
while (iterator.hasNext()) {
adetail = iterator.next();
USRecord usrecord = usrecordService
.findUSRecordByUSID(adetail.getUsid());
DPRecord dprecord = dprecordService
.findDPRecordByDPID(usrecord.getDpid());
BURecord burecord = burecordService
.findBURecordByBUID(dprecord.getCorrespondBuid());
if (twoRownum > 4) {
twoRow = sheetTwo.createRow(twoRownum);
this.copyCellStyleOfRow(sheetTwo, sheetTwo.getRow(4),
twoRow, 36);
} else {
twoRow = sheetTwo.getRow(twoRownum);
}
twoRow.getCell(0).setCellValue(burecord.getBuname()); // 事业部/中心
twoRow.getCell(1).setCellValue(dprecord.getDpname()); // 部门
twoRow.getCell(2).setCellValue(usrecord.getChineseName()); // 中文名
twoRow.getCell(3).setCellValue(usrecord.getEnglishName()); // 英文名
twoRow.getCell(4).setCellValue(adetail.getEmonth()); // 统计月
// 月份
twoRow.getCell(5).setCellValue(adetail.getEday()); // 统计月
// 日期
twoRow.getCell(6).setCellValue(adetail.getEweek()); // 统计月
// 星期
twoRow.getCell(7).setCellValue(adetail.getEdayType()); // 统计月
// 日期类型
twoRow.getCell(8).setCellValue(
Tools.strIsNull(adetail.getEpunchATime())); // 打卡
// 出勤时间
twoRow.getCell(9).setCellValue(
Tools.strIsNull(adetail.getEpunchBTime())); // 打卡
// 退勤时间
twoRow.getCell(10).setCellValue(
Tools.strIsNull(adetail.getElrTime())); // 实际缺勤 迟到时间
twoRow.getCell(11).setCellValue(
Tools.strIsNull(adetail.getEleTime())); // 实际缺勤 早退时间
twoRow.getCell(12).setCellValue(
Tools.strIsNull(adetail.getEloffUnitTime())); // 实际调休时间
twoRow.getCell(13).setCellValue(
Tools.strIsNull(adetail.getEremainAbTime())); // 实际缺勤时间
twoRow.getCell(14).setCellValue(
Tools.strIsNull(adetail.getEatime())); // 正常上班时间
twoRow.getCell(15).setCellValue(
Tools.strIsNull(adetail.getEbtime())); // 正常下班时间
twoRow.getCell(16).setCellValue(
Tools.strIsNull(adetail.getEleaveATime())); // 请假开始时间
twoRow.getCell(17).setCellValue(
Tools.strIsNull(adetail.getEleaveBTime())); // 请假结束时间
twoRow.getCell(18).setCellValue(
Tools.strIsNull(adetail.getEleaveTotalTime())); // 请假累计总时间
twoRow.getCell(19).setCellValue(
Tools.strIsNull(adetail.getEldoTotalTime())); // 请假
// -调休时间
twoRow.getCell(20).setCellValue(
Tools.strIsNull(adetail.getElanTotalTime())); // 请假
// -年休时间
twoRow.getCell(21).setCellValue(
Tools.strIsNull(adetail.getElthTotalTime())); // 请假
// -事假
twoRow.getCell(22).setCellValue(
Tools.strIsNull(adetail.getElsiTotalTime())); // 请假
// -病假
twoRow.getCell(23).setCellValue(
Tools.strIsNull(adetail.getElMBBTotalTime())); // 请假
// -婚/丧/产假
twoRow.getCell(24).setCellValue(
Tools.strIsNull(adetail.getEtravelATime())); // 出差
// -开始时间
twoRow.getCell(25).setCellValue(
Tools.strIsNull(adetail.getEtravelBTime())); // 出差
// -结束时间
twoRow.getCell(26).setCellValue(
Tools.strIsNull(adetail.getEwdoATime())); // 出勤补贴
// -开始时间
twoRow.getCell(27).setCellValue(
Tools.strIsNull(adetail.getEwdoBTime())); // 出勤补贴
// -结束时间
twoRow.getCell(28).setCellValue(
Tools.strIsNull(adetail.getEastartTime())); // 工作日
// -起算时间
twoRow.getCell(29).setCellValue(
Tools.strIsNull(adetail.getEwdoatotalTime())); // 工作日
// -A补贴23前
twoRow.getCell(30).setCellValue(
Tools.strIsNull(adetail.getEwdoctotalTime())); // 工作日
// -C补贴23后
twoRow.getCell(31).setCellValue(
Tools.strIsNull(adetail.getEhdobtotalTime())); // 节假日
// -B补贴23前
twoRow.getCell(32).setCellValue(
Tools.strIsNull(adetail.getEhdodtotalTime())); // 节假日
// -D补贴23后
twoRow.getCell(33).setCellValue(
Tools.numIsNULL(adetail.getElunchATotalNum())); // 其他补贴核算
// -午餐补贴
twoRow.getCell(34).setCellValue(
Tools.numIsNULL(adetail.getEdinnerATotalNum())); // 其他补贴核算
// -晚餐补贴
twoRow.getCell(35).setCellValue(
Tools.numIsNULL(adetail.getEhdcarATotalNum())); // 其他补贴核算
// -假日车贴
twoRownum++;
}
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
Tools.log.error(e.getMessage());
}
}
}

/**
* 判断批处理类型
* 
* @param batchConsets
* @return
*/
public Set<USRecord> getAllUSRecordByBatchConset(
List<BatchConset> batchConsets) {
Set<USRecord> userSet = new HashSet<USRecord>();
if (batchConsets != null && batchConsets.size() != 0) {
BatchConset batchConset = batchConsets.get(0);
// 按个人 得到所有人
if (batchConset.getStatisUsid() != null
&& !batchConset.getStatisUsid().equals("")) {
String usids = batchConset.getStatisUsid();
String[] usid = usids.split(",");
for (String string : usid) {
userSet.add(usrecordService.findUSRecordByUSID(string));
}
// 按部门 得到所有人
} else if (batchConset.getStatisDpid() != null
&& !batchConset.getStatisDpid().equals("")) {
String dpids = batchConset.getStatisDpid();
String[] dpid = dpids.split(",");
for (String string : dpid) {
userSet.addAll(usrecordService.findUSRecordByDPIDs(string));
}
// 按事业部
} else if (batchConset.getStatisBuid() != null
&& !batchConset.getStatisBuid().equals("")) {
String buids = batchConset.getStatisBuid();
String[] buid = buids.split(",");
for (String string : buid) {
List<DPRecord> dprecord = dprecordService
.findDPRecordByBUID(string);
if (dprecord != null && dprecord.size() != 0) {
for (DPRecord record : dprecord) {
userSet.addAll(usrecordService
.findUSRecordByDPIDs(record.getDpid()));
}
}
}
// 按公司
} else if (batchConset.getStatisCpid() != null
&& !batchConset.getStatisCpid().equals("")) {
String cpids = batchConset.getStatisCpid();
String[] cpid = cpids.split(",");
for (String string : cpid) {
userSet.addAll(usrecordService.findUSRecordByCPIDs(string));
}
}
}
return userSet;
}

/**
* 复制行
* 
* @param sheet
* @param fromRow
* @param toRow
* @param count
*/
@SuppressWarnings("deprecation")
private void copyCellStyleOfRow(HSSFSheet sheet, HSSFRow fromRow,
HSSFRow toRow, int count) {
for (int i = 0; i < count; i++) {
if (toRow.getCell(i) != null) {
toRow.getCell(i)
.setCellStyle(fromRow.getCell(i).getCellStyle());
} else {
toRow.createCell(i).setCellStyle(
fromRow.getCell(i).getCellStyle());
}
}
}