Java POI 两种导出方式
这里面包含了模板导出方法和自定义模板进行导出
package jp.co.syspro.poo.action.hibikoyou; import java.io.ByteArrayOutputStream; import java.io.DataOutput; import java.io.DataOutputStream; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import jp.co.syspro.core.action.BaseDataPilotAction; import jp.co.syspro.poo.common.DBCommon; import jp.co.syspro.poo.dao.ClientDao; import jp.co.syspro.poo.dao.SagyoDao; import jp.co.syspro.poo.dao.System1Dao; import jp.co.syspro.poo.dao.System2Dao; import jp.co.syspro.poo.form.AttendanceConfirmForm; import jp.co.syspro.poo.form.introClientForm; import jp.co.syspro.poo.util.ExcelOutput; import jp.co.syspro.poo.util.PdfOutput; import jp.co.syspro.poo.util.Utility; import jp.co.syspro.poo.vo.AttendanceConfirmVO; import jp.co.syspro.poo.vo.StaffInfoVo; import jp.co.syspro.poo.vo.StaffVo; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; import org.apache.struts.util.LabelValueBean; import com.lowagie.text.Document; import com.lowagie.text.PageSize; import com.lowagie.text.Rectangle; import com.lowagie.text.pdf.BaseFont; import com.lowagie.text.pdf.PdfPTable; import com.lowagie.text.pdf.PdfWriter; /** * <dd>class名:勤怠確認表 * * @version 1.00 2014/03/10 * @author WANGWEI */ public class JobAttendanceConfirmAction extends BaseDataPilotAction { @SuppressWarnings("unchecked") public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(true); String ko_cd = (String) session.getAttribute("username"); AttendanceConfirmForm theForm = (AttendanceConfirmForm) form; //00546-002 begin //紹介元フラッグ(1:紹介会社 0:クライアント) String introducerFlag = ClientDao.getInstance().getIntroducerFlag(ko_cd); request.setAttribute("IntroducerFlag", introducerFlag); //00546-009 ADD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/21 if("0".equals(introducerFlag)){ List listSjCompany=ClientDao.getInstance().getsjCompany1(DBCommon.CLIENT_DB,ko_cd); theForm.setSjCompanyValue(listSjCompany); request.setAttribute("listSjCompany", listSjCompany); } //00546-009 ADD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/21 theForm.setIntroducerFlag(introducerFlag); //00546-002 end String method = request.getParameter("method"); String path = request.getSession().getServletContext().getRealPath(""); if("output".equals(method)){ ActionForward forward = new ActionForward(); forward = Output(mapping, form, request, response,ko_cd); return forward; }else if("excel".equals(method) || "csv".equals(method)){ //00546-009 ADDBEGIN SUNZHIYAN SYSPRO-WH 2015/04/24 String outupType = (String) request.getParameter("outupType");//出力内容 String selkocd =""; String startYmd = (String) request.getParameter("startYmd");//期間 String endYmd = (String) request.getParameter("endYmd");//期間 //①スタッフCDを取得 if("0".equals(introducerFlag)){ //クライアント selkocd = (String) request.getParameter("selkocd");//紹介会社 }else if("1".equals(introducerFlag)){ //「紹介会社」のリスト表示 List selkocdList = ClientDao.getInstance().getIntroducedKokyakuCdList(ko_cd); for(int i=0;i<selkocdList.size();i++){ selkocd += ",'"+selkocdList.get(i)+"'"; } if(selkocd.length()> 0 ){ selkocd = selkocd.substring(1); } } String sa_staff_cdlist = SagyoDao.getInstance().getSaStaffCdByKoCd(DBCommon.SAGYO_DB, introducerFlag, startYmd, endYmd, selkocd); //上記の①で取得したstaff_cdを基に、スタッフ詳細情報を取得する。 List<StaffInfoVo> staffInfolist = ClientDao.getInstance().selectStaffBystaffcd(sa_staff_cdlist); //00546-009 ADD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/24 String fileName = "stafflist_";//.xls SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss"); if("excel".equals(method)){ fileName = fileName+sdf.format(new Date())+".xls"; }else{ fileName = fileName+sdf.format(new Date())+".csv"; } response.reset(); response.setContentType("text/html;charset=SHIFT_JIS"); response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("SJIS"),"iso8859-1") + "\""); ServletOutputStream fileOut = response.getOutputStream(); // restTimeType 0:有り 5:無し try{ FileInputStream fileInputStream = null; if("1".equals(outupType)){ //全項目選択 fileInputStream = new FileInputStream(path+ "/WEB-INF/template/staff_temp01.xls"); }else if("2".equals(outupType)){ //個人情報 fileInputStream = new FileInputStream(path+ "/WEB-INF/template/staff_temp02.xls"); }else if("3".equals(outupType)){ //緊急連絡先 fileInputStream = new FileInputStream(path+ "/WEB-INF/template/staff_temp03.xls"); }else if("4".equals(outupType)){ //金融機関 fileInputStream = new FileInputStream(path+ "/WEB-INF/template/staff_temp04.xls"); }else if("5".equals(outupType)){ //職業 服・靴のサイズ fileInputStream = new FileInputStream(path+ "/WEB-INF/template/staff_temp05.xls"); } //--------------TEST SUNZHIYAN //POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); //HSSFWorkbook wb = new HSSFWorkbook(fs); //HSSFSheet sheet1 = wb.getSheetAt(0); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("Sheet1"); HSSFRow row = sheet1.createRow((int) 0); //--------------TEST SUNZHIYAN //00546-009 MOD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/26 precessStaffToExcel(request,response,sheet1,wb,staffInfolist,outupType); // precessSetDateToExcel(request,response,sheet1,path+"/images/share/workers-logo.gif",wb,ko_cd); //00546-009 MOD END SUNZHIYAN SYSPRO-WH 2015/04/26 wb.write(fileOut); fileOut.close(); } catch (IOException io) { io.printStackTrace(); } finally { if (fileOut != null) { try { fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } } return null; }else{ if (ko_cd == null||"".equals(ko_cd.trim())) { if("0".equals(introducerFlag)){ return mapping.findForward("logon"); }else{ request.setAttribute("theForm",theForm); init(theForm,getYesterday()); return mapping.findForward("success"); } }else{ String pattern = SagyoDao.getInstance().getPattern(DBCommon.SAGYO_DB, ko_cd); request.setAttribute("pattern",pattern); theForm.setKoCd(ko_cd); List cert_list = ClientDao.getInstance().getCert(DBCommon.CLIENT_DB, ko_cd); if (cert_list.size() > 0) { request.setAttribute("certString",cert_list.get(0)); } if(pattern!=null&&pattern.trim().length()>2){ theForm.setOutupType(pattern.substring(0, 1)); theForm.setRestTimeType(pattern.substring(1, 2)); theForm.setStaffNameType(pattern.substring(2, 3)); }else{ theForm.setOutupType("0"); theForm.setRestTimeType("0"); theForm.setStaffNameType("0"); } request.setAttribute("theForm",theForm); //request.setAttribute("ko_cd", ko_cd); init(theForm,getYesterday()); return mapping.findForward("success"); } } } private void precessSetDateToExcel(HttpServletRequest request, HttpServletResponse response,HSSFSheet sheet1, String string,HSSFWorkbook wb,String koCd) { String outupType = (String) request.getParameter("outupType"); String staffNameType = (String) request.getParameter("staffNameType"); String restTimeType = (String) request.getParameter("restTimeType"); String sagyoDate = (String) request.getParameter("sagyoDate"); String path = request.getSession().getServletContext().getRealPath(""); // outupType 0:一括 2:時間帯別 4:作業場所別 6:時間帯・作業場所別 // restTimeType 0:有り 5:無し // staffNameType 0:カナ 5:漢字 List<AttendanceConfirmVO> sagyoVOList = SagyoDao.getInstance().getAttendanceDataForPdf(DBCommon.SAGYO_DB,restTimeType, outupType,koCd,sagyoDate); List<Integer> dataCountList = SagyoDao.getInstance().getAttendanceDataForPdfCount(DBCommon.SAGYO_DB,restTimeType, outupType,koCd,sagyoDate); List<Integer> indexArray = new ArrayList<Integer>();//需要ページング的条数 Map<Integer,String> namOrWomenMap = new HashMap<Integer,String>(); int temp = 0; int pageCount = 0; for (int i = 0; i < dataCountList.size(); i++) { temp = temp+dataCountList.get(i); pageCount = pageCount + ((dataCountList.get(i)-1)/15+1); indexArray.add(temp); } int namCnt = 0; int womenCnt = 0; int mapIndex = 0; String staffCds = ""; for(int m = 0; m < sagyoVOList.size(); m++){ if("".equals(staffCds)){ staffCds = "'"+sagyoVOList.get(m).getEmpCd()+"'"; }else{ staffCds = staffCds +","+"'"+sagyoVOList.get(m).getEmpCd()+"'"; } } HashMap<String,StaffVo> staffMap = ClientDao.getInstance().getStaffNameSexMap(DBCommon.CLIENT_DB, staffCds); List<String> staffExperiencedList1 = System2Dao.getInstance().getStaffListForExperienced(DBCommon.SYSTEM2_DB,staffCds,koCd,sagyoDate); List<String> staffExperiencedList2 = SagyoDao.getInstance().getStaffListForExperienced(DBCommon.SAGYO_DB,staffCds,koCd,sagyoDate); staffExperiencedList1.addAll(staffExperiencedList2); for(int k = 0; k < sagyoVOList.size(); k++){//男性スタッフ1名、女性スタッフ1名 AttendanceConfirmVO attendance = sagyoVOList.get(k); StaffVo staff = staffMap.get(attendance.getEmpCd()); // HashMap<String,String> staffDetail = ClientDao.getInstance().getStaffNameSex(DBCommon.CLIENT_DB, attendance.getEmpCd()); if("0".equals(staffNameType)){//姓名(カナ) attendance.setEmpNam(staff.getKana_name()); }else{//姓名(漢字) attendance.setEmpNam(staff.getStaffName()); } attendance.setSex(staff.getSex()); if(staffExperiencedList1.contains(attendance.getEmpCd())){ attendance.setExperience("有"); }else{ attendance.setExperience("無"); } //10:00 ~ 18:00 (休憩:60分) String timeTypeStr =""; if("0".equals(restTimeType)){ timeTypeStr = attendance.getStartTime()+"~"+attendance.getEndTime()+" (休憩:"+attendance.getRestTime()+"分)"; }else{ timeTypeStr = attendance.getStartTime()+"~"+attendance.getEndTime() ; } attendance.setTimeTypeStr(timeTypeStr); if("男".equals(staff.getSex())){//'男' '女' namCnt++; }else{ womenCnt++; } if(k==indexArray.get(mapIndex)-1){ if(mapIndex == 0){ namOrWomenMap.put(0, "男性スタッフ "+namCnt+"名、女性スタッフ "+womenCnt+"名"); }else{ namOrWomenMap.put(indexArray.get(mapIndex-1), "男性スタッフ "+namCnt+"名、女性スタッフ "+womenCnt+"名"); } namCnt = 0; womenCnt = 0; mapIndex++; } } AttendanceConfirmVO attendanceVo= new AttendanceConfirmVO(); AttendanceConfirmVO vo=ClientDao.getInstance().getKokyakuInfoForAttendance(DBCommon.CLIENT_DB, koCd); // AttendanceConfirmVO vo=null; if(vo.getTenCd()!=null && !"".equals(vo.getTenCd())){ attendanceVo=System1Dao.getInstance().getTenInfoForAttendance(DBCommon.SYSTEM1_DB, vo.getTenCd()); } attendanceVo.setKoCd(koCd); attendanceVo.setKoNam(vo.getKoNam()+ " 御中"); attendanceVo.setKoFax(vo.getKoFax()); SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm"); Date date=new Date(); String ymd=Utility.formatDateKana(sagyoDate); attendanceVo.setDealTime(sf.format(date)); attendanceVo.setSagyoYmd(ymd); Integer index = 0; String namOrWomenStr =""; int tempIndex = 0; for (int i = 1; i <= pageCount; i++) { int startRow = 0; if(i!=1){ startRow = (i-1)*31; ExcelOutput.copyRows(sheet1, 1, 31, startRow); } } HSSFPatriarch patriarch = sheet1.createDrawingPatriarch(); int changePageIndex = 1; for (int i = 1; i <= pageCount; i++) { List<AttendanceConfirmVO> data = new ArrayList<AttendanceConfirmVO>(); if(tempIndex!=15){ namOrWomenStr = namOrWomenMap.get(index); }else if(changePageIndex==index){ namOrWomenStr = namOrWomenMap.get(index); } tempIndex = 0; for (int j = index; j < sagyoVOList.size(); j++) { if(tempIndex==15||indexArray.contains(index)){ if(indexArray.contains(index)){ changePageIndex =index; indexArray.remove(index); } break; }else{ data.add(sagyoVOList.get(index)); index++; tempIndex++; } } attendanceVo.setManOrWonemCnt(namOrWomenStr); int startRow = 0; if(i!=1){ startRow = (i-1)*31; // ExcelOutput.copyRows(sheet1, 1, 31, startRow); } attendanceVo.setFaxFlag(true); ExcelOutput.excelOutputAttendanceConfirm(sheet1,path+"/images/share/workers-logo.gif",patriarch,wb, data, attendanceVo, "ページ "+i+" / "+pageCount,restTimeType,outupType,startRow,request,request.getParameter("introducerFlag")); } if("0".equals(restTimeType)){ wb.setPrintArea(0, 0, 20, 0, pageCount*31+2); }else{ wb.setPrintArea(0, 0, 18, 0, pageCount*31+2); } int times=SagyoDao.getInstance().getTimes(DBCommon.SAGYO_DB, koCd, sagyoDate); AttendanceConfirmVO vo_kintaihis=new AttendanceConfirmVO(); vo_kintaihis.setKoCd(koCd); vo_kintaihis.setSagyoYmd(sagyoDate); vo_kintaihis.setOutupType(outupType); vo_kintaihis.setRestTimeType(restTimeType); vo_kintaihis.setStaffNameType(staffNameType); if(times==0){ SagyoDao.getInstance().insertKintaiPrintHis(DBCommon.SAGYO_DB, vo_kintaihis); }else{ SagyoDao.getInstance().updateKintaiPrintHis(DBCommon.SAGYO_DB, vo_kintaihis, times,null); } } private ActionForward Output(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response, String koCd) throws Exception{ String outupType = (String) request.getParameter("outupType"); String staffNameType = (String) request.getParameter("staffNameType"); String restTimeType = (String) request.getParameter("restTimeType"); String sagyoDate = (String) request.getParameter("sagyoDate"); String path = request.getSession().getServletContext().getRealPath(""); // outupType 0:一括 2:時間帯別 4:作業場所別 6:時間帯・作業場所別 // restTimeType 0:有り 5:無し // staffNameType 0:カナ 5:漢字 List<AttendanceConfirmVO> sagyoVOList = SagyoDao.getInstance().getAttendanceDataForPdf(DBCommon.SAGYO_DB,restTimeType, outupType,koCd,sagyoDate); List<Integer> dataCountList = SagyoDao.getInstance().getAttendanceDataForPdfCount(DBCommon.SAGYO_DB,restTimeType, outupType,koCd,sagyoDate); List<Integer> indexArray = new ArrayList<Integer>();//需要ページング的条数 Map<Integer,String> namOrWomenMap = new HashMap<Integer,String>(); int temp = 0; int pageCount = 0; for (int i = 0; i < dataCountList.size(); i++) { temp = temp+dataCountList.get(i); pageCount = pageCount + ((dataCountList.get(i)-1)/15+1); indexArray.add(temp); } int namCnt = 0; int womenCnt = 0; int mapIndex = 0; String staffCds = ""; for(int m = 0; m < sagyoVOList.size(); m++){ if("".equals(staffCds)){ staffCds = "'"+sagyoVOList.get(m).getEmpCd()+"'"; }else{ staffCds = staffCds +","+"'"+sagyoVOList.get(m).getEmpCd()+"'"; } } HashMap<String,StaffVo> staffMap = ClientDao.getInstance().getStaffNameSexMap(DBCommon.CLIENT_DB, staffCds); List<String> staffExperiencedList1 = System2Dao.getInstance().getStaffListForExperienced(DBCommon.SYSTEM2_DB,staffCds,koCd,sagyoDate); List<String> staffExperiencedList2 = SagyoDao.getInstance().getStaffListForExperienced(DBCommon.SAGYO_DB,staffCds,koCd,sagyoDate); staffExperiencedList1.addAll(staffExperiencedList2); for(int k = 0; k < sagyoVOList.size(); k++){//男性スタッフ1名、女性スタッフ1名 AttendanceConfirmVO attendance = sagyoVOList.get(k); StaffVo staff = staffMap.get(attendance.getEmpCd()); // HashMap<String,String> staffDetail = ClientDao.getInstance().getStaffNameSex(DBCommon.CLIENT_DB, attendance.getEmpCd()); if("0".equals(staffNameType)){//姓名(カナ) attendance.setEmpNam(staff.getKana_name()); }else{//姓名(漢字) attendance.setEmpNam(staff.getStaffName()); } attendance.setSex(staff.getSex()); if(staffExperiencedList1.contains(attendance.getEmpCd())){ attendance.setExperience("有"); }else{ attendance.setExperience("無"); } //10:00 ~ 18:00 (休憩:60分) String timeTypeStr =""; if("0".equals(restTimeType)){ timeTypeStr = attendance.getStartTime()+"~"+attendance.getEndTime()+" (休憩:"+attendance.getRestTime()+"分)"; }else{ timeTypeStr = attendance.getStartTime()+"~"+attendance.getEndTime() ; } attendance.setTimeTypeStr(timeTypeStr); if("男".equals(staff.getSex())){//'男' '女' namCnt++; }else{ womenCnt++; } if(k==indexArray.get(mapIndex)-1){ if(mapIndex == 0){ namOrWomenMap.put(0, "男性スタッフ "+namCnt+"名、女性スタッフ "+womenCnt+"名"); }else{ namOrWomenMap.put(indexArray.get(mapIndex-1), "男性スタッフ "+namCnt+"名、女性スタッフ "+womenCnt+"名"); } namCnt = 0; womenCnt = 0; mapIndex++; } } AttendanceConfirmVO attendanceVo= new AttendanceConfirmVO(); AttendanceConfirmVO vo=ClientDao.getInstance().getKokyakuInfoForAttendance(DBCommon.CLIENT_DB, koCd); // AttendanceConfirmVO vo=null; if(vo.getTenCd()!=null && !"".equals(vo.getTenCd())){ attendanceVo=System1Dao.getInstance().getTenInfoForAttendance(DBCommon.SYSTEM1_DB, vo.getTenCd()); } attendanceVo.setKoCd(koCd); attendanceVo.setKoNam(vo.getKoNam()+ " 御中"); attendanceVo.setKoFax(vo.getKoFax()); SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm"); Date date=new Date(); String ymd=Utility.formatDateKana(sagyoDate); attendanceVo.setDealTime(sf.format(date)); attendanceVo.setSagyoYmd(ymd); Rectangle rectPageSize = new Rectangle(PageSize.A4).rotate(); Document document = new Document(rectPageSize, 10, 10, 10, 10); ByteArrayOutputStream buffer = new ByteArrayOutputStream(); PdfWriter writer = PdfWriter.getInstance(document, buffer); // 表示画面で 道具とメニュbars表示しない writer.setViewerPreferences(PdfWriter.HideMenubar | PdfWriter.HideToolbar); BaseFont bf = BaseFont.createFont("HeiseiKakuGo-W5", "UniJIS-UCS2-HW-H", false); response.setContentType("application/pdf"); // ファイルをオープンする document.open(); Integer index = 0; // String namOrWomenStr = namOrWomenMap.get(0); String namOrWomenStr =""; int tempIndex = 0; int changePageIndex = 1; for (int i = 1; i <= pageCount; i++) { List<AttendanceConfirmVO> data = new ArrayList<AttendanceConfirmVO>(); if(tempIndex!=15){ namOrWomenStr = namOrWomenMap.get(index); }else if(changePageIndex==index){ namOrWomenStr = namOrWomenMap.get(index); } tempIndex = 0; for (int j = index; j < sagyoVOList.size(); j++) { if(tempIndex==15||indexArray.contains(index)){ if(indexArray.contains(index)){ changePageIndex =index; indexArray.remove(index); // namOrWomenStr = namOrWomenMap.get(index); } // tempIndex=0; break; }else{ data.add(sagyoVOList.get(index)); index++; tempIndex++; } } float[] widths = { 0f, 0.165f }; PdfPTable titletable = new PdfPTable(widths); titletable.setWidthPercentage(100f); titletable.getDefaultCell().setPadding(0); attendanceVo.setManOrWonemCnt(namOrWomenStr); titletable = PdfOutput.attendanceConfirm(bf, titletable, data, attendanceVo, "ページ "+i+" / "+pageCount,restTimeType,outupType,path); document.add(titletable); // 新ページ生成 document.newPage(); } // ここまで data処理を終わります // ファイルをクローズする document.close(); // 画面でPDFファイルを表示 DataOutput output = new DataOutputStream(response.getOutputStream()); byte[] bytes = buffer.toByteArray(); response.setContentLength(bytes.length); for (int i = 0; i < bytes.length; i++) { output.writeByte(bytes[i]); } request.setAttribute("msg",""); int times=SagyoDao.getInstance().getTimes(DBCommon.SAGYO_DB, koCd, sagyoDate); AttendanceConfirmVO vo_kintaihis=new AttendanceConfirmVO(); vo_kintaihis.setKoCd(koCd); vo_kintaihis.setSagyoYmd(sagyoDate); vo_kintaihis.setOutupType(outupType); vo_kintaihis.setRestTimeType(restTimeType); vo_kintaihis.setStaffNameType(staffNameType); if(times==0){ SagyoDao.getInstance().insertKintaiPrintHis(DBCommon.SAGYO_DB, vo_kintaihis); }else{ SagyoDao.getInstance().updateKintaiPrintHis(DBCommon.SAGYO_DB, vo_kintaihis, times,null); } return null; } private void init(AttendanceConfirmForm theForm,String sagyoYmd) { String nowYear = getYesterday().split("-")[0]; String nowMonth = getYesterday().split("-")[1]; String nowDay = getYesterday().split("-")[2]; theForm.setYear(nowYear); theForm.setYearList(getYearList()); theForm.setMonth(nowMonth); theForm.setDay(nowDay); if("1".equals(theForm)){ theForm.setMonthList(getMonthList(theForm.getSagyoYear())); theForm.setDayList(getDayList(theForm.getSagyoMonth(),theForm.getSagyoYear())); }else{ sagyoYmd = sagyoYmd.replaceAll("/","-"); String sagyoYear = sagyoYmd.split("-")[0]; String sagyoMonth = sagyoYmd.split("-")[1]; String sagyoDay = sagyoYmd.split("-")[2]; theForm.setSagyoYear(sagyoYear); theForm.setSagyoMonth(sagyoMonth); theForm.setSagyoDay(sagyoDay); theForm.setMonthList(getMonthList(sagyoYmd.split("-")[0])); theForm.setDayList(getDayList(sagyoYmd.split("-")[1],sagyoYmd.split("-")[0])); } } /** * システム日付を取得する<br> * * @return String システム日付(yyyy-mm-dd) */ private String getYesterday() { Calendar cal = Calendar.getInstance(); cal.add(Calendar.DAY_OF_MONTH,+1); SimpleDateFormat formatter =new SimpleDateFormat("yyyy-MM-dd"); return formatter.format(cal.getTime()); } /** * 2009~システム日付の年の年リストを取得する<br> * * @return reList */ private List<String> getYearList() { List<String> reList = new ArrayList<String>(); String nowYear = getYesterday().split("-")[0]; for(int i = 2009; i <= Integer.parseInt(nowYear); i++) { reList.add(String.valueOf(i)); } return reList; } /** * 月リストを取得する<br> * ①年<システム日付の年 1~12;②年=システム日付の年 1~システム日付の月 * * @param year * @return reList */ private List<String> getMonthList(String year) { List<String> reList = new ArrayList<String>(); String nowTime = getYesterday(); int maxMonth = 0; if(year.equals(nowTime.split("-")[0])) { maxMonth = Integer.parseInt(nowTime.split("-")[1]); } else { maxMonth = 12; } for(int i = 1; i <= maxMonth; i++) { String monthStr = ""; if(i < 10) { monthStr = "0" + i; } else { monthStr = "" + i; } reList.add(monthStr); } return reList; } /** * 日リストを取得する<br> * @param month,year * @return reList */ private List<String> getDayList(String month,String year) { List<String> reList = new ArrayList<String>(); int maxDay = 0; int intMonth = Integer.parseInt(month); int intYear = Integer.parseInt(year); if(intMonth==1||intMonth==3||intMonth==5||intMonth==7||intMonth==8||intMonth==10||intMonth==12) { maxDay = 31; } else if(intMonth==4||intMonth==6||intMonth==9||intMonth==11) { maxDay = 30; } else { if((intYear%4==0&&intYear%100!=0)||intYear%400==0){ maxDay = 29; }else{ maxDay = 28; } } for(int i = 1; i <= maxDay; i++) { String dayStr = ""; if(i < 10) { dayStr = "0" + i; } else { dayStr = "" + i; } reList.add(dayStr); } return reList; } //00546-009 ADD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/24 /*** * * @param request * @param response * @param sheet1 * @param string * @param wb * @param koCd * @return staff_cd 関わるexcelの出力 */ private void precessStaffToExcel(HttpServletRequest request, HttpServletResponse response,HSSFSheet sheet,HSSFWorkbook wb,List staffCdList,String outupType) { String path = request.getSession().getServletContext().getRealPath(""); List<Integer> indexArray = new ArrayList<Integer>();//需要ページング的条数 //HSSFWorkbook wb = new HSSFWorkbook(); //HSSFSheet sheet = wb.createSheet("sheet1"); // sheet.setColumnWidth(0, 10*256); // sheet.setColumnWidth(2, 5*256); // HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow row = sheet.createRow((int) 0); int j_index = 0; //第1行を取得 HSSFCell cell = row.createCell(j_index ++, 0 ); cell.setCellValue("氏名(姓)"); cell.setCellStyle(style); //第2行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("氏名(名)"); cell.setCellStyle(style); //第3行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("姓カナ"); cell.setCellStyle(style); //第4行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("名カナ"); cell.setCellStyle(style); //第5行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("性別"); cell.setCellStyle(style); //第6行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("生年月日"); cell.setCellStyle(style); //第7行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("〒"); cell.setCellStyle(style); //第8行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("住所(都道府県)"); cell.setCellStyle(style); //第9行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("住所(市区町村)"); cell.setCellStyle(style); //第10行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("住所(番地以降)"); cell.setCellStyle(style); //第11行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("連絡先"); cell.setCellStyle(style); //第12行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("携帯電話"); cell.setCellStyle(style); //第13行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("メールアドレス"); cell.setCellStyle(style); //第14行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("移住状況"); cell.setCellStyle(style); //第15行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("最寄駅(路線)"); cell.setCellStyle(style); //第16行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("最寄駅(駅)"); cell.setCellStyle(style); //第17行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("移動種類"); cell.setCellStyle(style); //第18行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("移動時間"); cell.setCellStyle(style); //第19行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("社会保険加入"); cell.setCellStyle(style); //第20行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先(相手)"); cell.setCellStyle(style); //第21行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先(氏名)"); cell.setCellStyle(style); //第22行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先(〒)"); cell.setCellStyle(style); //第23行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先住所(都道府県)"); cell.setCellStyle(style); //第24行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先住所(市区町村)"); cell.setCellStyle(style); //第25行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("緊急連絡先住所(番地以降)"); cell.setCellStyle(style); //第26行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("金融連絡先(連絡先番号)"); cell.setCellStyle(style); //第27行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("金融機関名"); cell.setCellStyle(style); //第28行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("支店名"); cell.setCellStyle(style); //第29行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("口座種類"); cell.setCellStyle(style); //第30行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("口座番号"); cell.setCellStyle(style); //第31行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("名義人名"); cell.setCellStyle(style); //第32行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("フリガナ"); cell.setCellStyle(style); //第33行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("連絡先電話番号"); cell.setCellStyle(style); //第34行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("職業"); cell.setCellStyle(style); //第35行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("服のサイズ"); cell.setCellStyle(style); //第36行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("靴のサイズ"); cell.setCellStyle(style); //第37行を取得 cell = row.createCell(j_index ++, 0 ); cell.setCellValue("備考"); cell.setCellStyle(style); for (int i = 0; i < staffCdList.size(); i++) { //row = sheet.createRow((int) i + 1); row = sheet.createRow(i + 1); int j = 0 ; StaffInfoVo staffInfolist = (StaffInfoVo) staffCdList.get(i); //############################# 個人情報 if("1".equals(outupType) || "2".equals(outupType)){ //個人情報 //第1行を取得 氏名(姓) staffInfolist.getNameSei() row.createCell((short)j++).setCellValue(staffInfolist.getNameSei()); //第2行を取得 氏名(名) row.createCell((short)j++).setCellValue(staffInfolist.getNameMei()); //第3行を取得 姓カナ row.createCell((short)j++).setCellValue(staffInfolist.getSeiKana()); //第4行を取得 名カナ row.createCell((short)j++).setCellValue(staffInfolist.getMeiKana()); //第5行を取得 性別 if("1".equals(staffInfolist.getSex())){ row.createCell((short)j++).setCellValue("男"); }else if("2".equals(staffInfolist.getSex())){ row.createCell((short)j++).setCellValue("女"); } //第6行を取得 生年月日 row.createCell((short)j++).setCellValue(staffInfolist.getBirthday()); //第7行を取得 〒 row.createCell((short)j++).setCellValue(staffInfolist.getPostCode()); //第8行を取得 住所(都道府県) row.createCell((short)j++).setCellValue(staffInfolist.getAddress1()); //第9行を取得 住所(市区町村) row.createCell((short)j++).setCellValue(staffInfolist.getAddress2()); //第10行を取得 住所(番地以降) row.createCell((short)j++).setCellValue(staffInfolist.getAddress3()); //第11行を取得 連絡先 row.createCell((short)j++).setCellValue(staffInfolist.getHomePhone()); //第12行を取得 携帯電話 row.createCell((short)j++).setCellValue(staffInfolist.getPortablePhone()); //第13行を取得 メールアドレス row.createCell((short)j++).setCellValue(staffInfolist.getPortableMail()); //第14行を取得 移住状況 if("1".equals(staffInfolist.getHouseType())){ row.createCell((short)j++).setCellValue("一人暮らし"); }else if("2".equals(staffInfolist.getHouseType())){ row.createCell((short)j++).setCellValue("家族同居"); }else if("3".equals(staffInfolist.getHouseType())){ row.createCell((short)j++).setCellValue("寮"); } //第15行を取得 最寄駅(路線) row.createCell((short)j++).setCellValue(staffInfolist.getLine()); //第16行を取得 最寄駅(駅) row.createCell((short)j++).setCellValue(staffInfolist.getStation()); //第17行を取得 移動種類 if("1".equals(staffInfolist.getWalkOrBus())){ row.createCell((short)j++).setCellValue("徒歩"); }else if("2".equals(staffInfolist.getWalkOrBus())){ row.createCell((short)j++).setCellValue("バス"); } //第18行を取得 移動時間 row.createCell((short)j++).setCellValue(staffInfolist.getToStationTime()); //第19行を取得 社会保険加入 //0:未加入、-1:加入 syakai_hoken=1:本人、syakai_hoken=2:家族 String HokenFlg = ""; if("1".equals(staffInfolist.getSyakaiHoken())){ HokenFlg = "本人"; }else if("2".equals(staffInfolist.getSyakaiHoken())){ HokenFlg = "家族"; } if("0".equals(staffInfolist.getSyakaiHokenFlg())){ HokenFlg += "未加入"; }else if("-1".equals(staffInfolist.getSyakaiHokenFlg())){ HokenFlg += "加入"; } row.createCell((short)j++).setCellValue(HokenFlg); } //############################# 緊急連絡先 if("1".equals(outupType) || "3".equals(outupType)){ //第20行を取得 緊急連絡先(相手) if("1".equals(staffInfolist.getSosType())){ row.createCell((short)j++).setCellValue("自宅"); }else if("2".equals(staffInfolist.getSosType())){ row.createCell((short)j++).setCellValue("家族"); }else if("3".equals(staffInfolist.getSosType())){ row.createCell((short)j++).setCellValue("親類"); }else if("4".equals(staffInfolist.getSosType())){ row.createCell((short)j++).setCellValue("友人"); }else if("5".equals(staffInfolist.getSosType())){ row.createCell((short)j++).setCellValue("その他"); } //第21行を取得 緊急連絡先(氏名) row.createCell((short)j++).setCellValue(staffInfolist.getSosName()); //第22行を取得 緊急連絡先(〒) row.createCell((short)j++).setCellValue(staffInfolist.getSosPostcode()); //第23行を取得 緊急連絡先住所(都道府県) row.createCell((short)j++).setCellValue(staffInfolist.getSosAddress1()); //第24行を取得 緊急連絡先住所(市区町村) row.createCell((short)j++).setCellValue(staffInfolist.getSosAddress2()); //第25行を取得 緊急連絡先住所(番地以降) row.createCell((short)j++).setCellValue(staffInfolist.getSosAddress3()); //第26行を取得 緊急連絡先(連絡先番号) row.createCell((short)j++).setCellValue(staffInfolist.getSosPhone()); } //############################# 金融機関 if("1".equals(outupType) || "4".equals(outupType)){ //第27行を取得 金融機関名 row.createCell((short)j++).setCellValue(System1Dao.getInstance().getBankName(DBCommon.SYSTEM1_DB,staffInfolist.getBankCd())); //第28行を取得 支店名 row.createCell((short)j++).setCellValue(System1Dao.getInstance().getBranchName(DBCommon.SYSTEM1_DB,staffInfolist.getBankCd(),staffInfolist.getBranchCd(),staffInfolist.getBranchNo())); //第29行を取得 口座種類 koza_flg=1:、2: if("1".equals(staffInfolist.getKozaFlg())){ row.createCell((short)j++).setCellValue("普通"); }else if("2".equals(staffInfolist.getKozaFlg())){ row.createCell((short)j++).setCellValue("当座"); } //第30行を取得 口座番号 row.createCell((short)j++).setCellValue(staffInfolist.getKozaNo()); //第31行を取得 名義人名 row.createCell((short)j++).setCellValue(staffInfolist.getKozaName()); //第32行を取得 フリガナ row.createCell((short)j++).setCellValue(staffInfolist.getKozaKana()); //第33行を取得 連絡先電話番号 row.createCell((short)j++).setCellValue(staffInfolist.getHomePhone()); } //############################# 職業 服・靴のサイズ if("1".equals(outupType) || "5".equals(outupType)){ //第34行を取得 職業 //now_job=1:フリーター、2:就職活動中、3:無職、4:会社員、 //5:主婦、6:自営業、7:学生、8:その他、9:派遣 if("1".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("フリーター"); }else if("2".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("就職活動中"); }else if("3".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("無職"); }else if("4".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("会社員"); }else if("5".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("主婦"); }else if("6".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("自営業"); }else if("7".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("学生"); }else if("8".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("その他"); }else if("9".equals(staffInfolist.getNowJob())){ row.createCell((short)j++).setCellValue("派遣"); } //第35行を取得 服のサイズ if("1".equals(staffInfolist.getSuitSize())){ row.createCell((short)j++).setCellValue("S"); }else if("2".equals(staffInfolist.getSuitSize())){ row.createCell((short)j++).setCellValue("M"); }else if("3".equals(staffInfolist.getSuitSize())){ row.createCell((short)j++).setCellValue("L"); }else if("4".equals(staffInfolist.getSuitSize())){ row.createCell((short)j++).setCellValue("LL"); }else if("5".equals(staffInfolist.getSuitSize())){ row.createCell((short)j++).setCellValue("XL"); } //第36行を取得 靴のサイズ row.createCell((short)j++).setCellValue(staffInfolist.getShoes()); //第37行を取得 備考 row.createCell((short)j++).setCellValue(staffInfolist.getInterviewMemo()); } j = 0 ; } /* try { FileOutputStream fout = new FileOutputStream("E:/students.xls"); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } */ //ExcelOutput.excelOutputStaff(templePath, dataList, staffCdList, path, fileNam); //ExcelOutput.excelOutputAttendanceConfirm(sheet1,path+"/images/share/workers-logo.gif",patriarch,wb, data, attendanceVo, "ページ "+i+" / "+pageCount,restTimeType,outupType,startRow,request,request.getParameter("introducerFlag")); } //00546-009 ADD BEGIN SUNZHIYAN SYSPRO-WH 2015/04/24 }