List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createDataFormat
@Override
public HSSFDataFormat createDataFormat()
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
private static boolean isDateCell(HSSFWorkbook book, HSSFCell cell) { if (HSSFDateUtil.isCellDateFormatted(cell)) { // knows only excel buildin date formats return true; } else {/*from w w w. j a v a2 s .c om*/ short format = cell.getCellStyle().getDataFormat(); String formatString = book.createDataFormat().getFormat(format); if (formatString.indexOf("d") >= 0 && formatString.indexOf("m") >= 0 && formatString.indexOf("yy") >= 0) { return true; // day, month and year are contained -> must be a date } else { return false; } } }
From source file:bean.ExportData.java
public void memberList(List<CoopMember> memberData, MemberFilterData filterData) { String fName = getFilename(); ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); createFolder(null, themeDisplay, "Membership export", "Description"); int x = 0, y = 0; if (getFilename() == null || getFilename().length() == 0) { setFilename("Filtered Data List(" + new Date() + ")"); }//from w w w .ja v a 2s .c o m try { setFilename("" + getFilename() + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(y++).setCellValue("Member No."); rowhead.createCell(y++).setCellValue("Full Name"); rowhead.createCell(y++).setCellValue("PT Number"); rowhead.createCell(y++).setCellValue("Account No."); if (getExportColumns().get(0)) { rowhead.createCell(y++).setCellValue("Address"); } if (getExportColumns().get(1)) { rowhead.createCell(y++).setCellValue("Gender"); } if (getExportColumns().get(2)) { rowhead.createCell(y++).setCellValue("Civil Status"); } if (getExportColumns().get(3)) { rowhead.createCell(y++).setCellValue("Birthdate"); } if (getExportColumns().get(4)) { rowhead.createCell(y++).setCellValue("TIN"); } if (getExportColumns().get(5)) { rowhead.createCell(y++).setCellValue("Pag-Ibig"); } if (getExportColumns().get(6)) { rowhead.createCell(y++).setCellValue("Philhealth"); } if (getExportColumns().get(7)) { rowhead.createCell(y++).setCellValue("SSS"); } if (getExportColumns().get(8)) { rowhead.createCell(y++).setCellValue("GSIS"); } if (getExportColumns().get(9)) { rowhead.createCell(y++).setCellValue("Membership Date"); } if (getExportColumns().get(10)) { rowhead.createCell(y++).setCellValue("Membership Status"); } if (getExportColumns().get(11)) { rowhead.createCell(y++).setCellValue("Skill"); } if (getExportColumns().get(12)) { rowhead.createCell(y++).setCellValue("Compensation Bracket"); } if (getExportColumns().get(13)) { rowhead.createCell(y++).setCellValue("Rank Position"); } if (getExportColumns().get(14)) { rowhead.createCell(y++).setCellValue("Educational Attainment"); } for (int i = 0; i != memberData.size(); i++) { HSSFRow row = sheet.createRow((short) i + 1); row.createCell(x++).setCellValue(memberData.get(i).getMemNo()); row.createCell(x++) .setCellValue(getDataConvert().fullname(memberData.get(i).getPPrefix(), memberData.get(i).getLastName(), memberData.get(i).getFirstName(), memberData.get(i).getMiddleName(), memberData.get(i).getSuffix())); try { row.createCell(x++).setCellValue(memberData.get(i).getOuCode().getOuShortName()); } catch (Exception e) { row.createCell(x++).setCellValue(""); } row.createCell(x++).setCellValue(memberData.get(i).getScAcctno()); if (getExportColumns().get(0)) { row.createCell(x++).setCellValue(memberData.get(i).getStreet() + " " + memberData.get(i).getBarangay() + " " + memberData.get(i).getCityMun() + " " + (memberData.get(i).getProvince() != null ? memberData.get(i).getProvince() : "")); } if (getExportColumns().get(1)) { row.createCell(x++).setCellValue(getDataConvert().genderConv(memberData.get(i).getGender())); } if (getExportColumns().get(2)) { row.createCell(x++) .setCellValue(getDataConvert().civilStatusConv(memberData.get(i).getCivilStatus())); } if (getExportColumns().get(3)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getCustomDate().formatDate(memberData.get(i).getBirthdate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(4)) { row.createCell(x++).setCellValue(memberData.get(i).getTaxIdNo()); } if (getExportColumns().get(5)) { row.createCell(x++).setCellValue(memberData.get(i).getPagIbig()); } if (getExportColumns().get(6)) { row.createCell(x++).setCellValue(memberData.get(i).getPhilhealth()); } if (getExportColumns().get(7)) { row.createCell(x++).setCellValue(memberData.get(i).getSss()); } if (getExportColumns().get(8)) { row.createCell(x++).setCellValue(memberData.get(i).getGsis()); } if (getExportColumns().get(9)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getCustomDate().formatDate(memberData.get(i).getMemDate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(10)) { row.createCell(x++).setCellValue(memberData.get(i).getStatusId().getStatusName()); } if (getExportColumns().get(11)) { //this is conditional try { String skl = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory() .createEntityManager() .createQuery("SELECT c.skillsCode.skillsName FROM CoopSkillsMem c " + "WHERE UPPER(c.skillsCode.skillsName) LIKE UPPER('%" + filterData.getSkill() + "%') " + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(skl); } catch (Exception e) { row.createCell(x++).setCellValue(""); } } if (getExportColumns().get(12)) { // if (filterData.getCompensation() != null) { // row.createCell(x++).setCellValue(filterData.getCompensation()); // } else { // try { // String cmpBrckt = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlMem c " // + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList().get(0); // row.createCell(x++).setCellValue(cmpBrckt); // } catch (Exception e) { // row.createCell(x++).setCellValue(""); // } // } //new start if (filterData.getCompensation() != null) { row.createCell(x++).setCellValue(filterData.getCompensation()); } else { try { String cmpBrckt = (String) getCustomEntityManagerFactory() .getLportalMemOrgEntityManagerFactory().createEntityManager() .createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlMem c " + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(cmpBrckt); } catch (Exception e) { row.createCell(x++).setCellValue(""); } } //new end } if (getExportColumns().get(13)) { // try { // String rnkPos = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlMem c " // + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList().get(0); // row.createCell(x++).setCellValue(rnkPos); // } catch (Exception e) { // row.createCell(x++).setCellValue(""); // } //new start //this is conditional try { String rnkPos = (String) getCustomEntityManagerFactory() .getLportalMemOrgEntityManagerFactory().createEntityManager() .createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlMem c " + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') " + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(rnkPos); } catch (Exception e) { row.createCell(x++).setCellValue(""); } //new end } if (getExportColumns().get(14)) { List<CoopEducInfo> educInfo = new ArrayList<>(); //new start educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory() .createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'") .getResultList(); for (int a = 0; a != educInfo.size(); a++) { rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); } //new end // //POST-GRADUATE // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " // + "WHERE c.educInfoNum.schoolLevel ='POST-GRADUATE' " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // // //COLLEGE // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " // + "WHERE c.educInfoNum.schoolLevel ='COLLEGE' " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //VOCATIONAL // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " // + "WHERE c.educInfoNum.schoolLevel ='VOCATIONAL' " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //HIGH SCHOOL // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " // + "WHERE c.educInfoNum.schoolLevel ='HIGH SCHOOL' " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //ELEMENTARY // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c " // + "WHERE c.educInfoNum.schoolLevel ='ELEMENTARY' " // + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } if (educInfo.isEmpty()) { row.createCell(x++).setCellValue(""); } } x = 0; } FileOutputStream fileOut = new FileOutputStream(getFilename()); workbook.write(fileOut); fileOut.close(); fileUploadByDL(getFilename(), "Membership export", themeDisplay, null); //delete file File file = new File(getFilename()); if (file.exists()) { file.delete(); } } catch (Exception ex) { System.out.println(ex); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } setFilename("" + fName + ""); }
From source file:bean.ExportData.java
public void associateList(List<CoopAssociate> associateData, MemberFilterData filterData) { String fName = getFilename(); ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); createFolder(null, themeDisplay, "Membership export", "Description"); int x = 0, y = 0; if (getFilename() == null || getFilename().length() == 0) { setFilename("Filtered Data List(" + new Date() + ")"); }/*w ww .j a va 2 s . co m*/ try { setFilename("" + getFilename() + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(y++).setCellValue("Member No."); rowhead.createCell(y++).setCellValue("Full Name"); rowhead.createCell(y++).setCellValue("PT Number"); rowhead.createCell(y++).setCellValue("Account No."); if (getExportColumns().get(0)) { rowhead.createCell(y++).setCellValue("Address"); } if (getExportColumns().get(1)) { rowhead.createCell(y++).setCellValue("Gender"); } if (getExportColumns().get(2)) { rowhead.createCell(y++).setCellValue("Civil Status"); } if (getExportColumns().get(3)) { rowhead.createCell(y++).setCellValue("Birthdate"); } if (getExportColumns().get(4)) { rowhead.createCell(y++).setCellValue("TIN"); } if (getExportColumns().get(5)) { rowhead.createCell(y++).setCellValue("Pag-Ibig"); } if (getExportColumns().get(6)) { rowhead.createCell(y++).setCellValue("Philhealth"); } if (getExportColumns().get(7)) { rowhead.createCell(y++).setCellValue("SSS"); } if (getExportColumns().get(8)) { rowhead.createCell(y++).setCellValue("GSIS"); } if (getExportColumns().get(9)) { rowhead.createCell(y++).setCellValue("Membership Date"); } if (getExportColumns().get(10)) { rowhead.createCell(y++).setCellValue("Membership Status"); } if (getExportColumns().get(11)) { rowhead.createCell(y++).setCellValue("Skill"); } if (getExportColumns().get(12)) { rowhead.createCell(y++).setCellValue("Compensation Bracket"); } if (getExportColumns().get(13)) { rowhead.createCell(y++).setCellValue("Rank Position"); } if (getExportColumns().get(14)) { rowhead.createCell(y++).setCellValue("Educational Attainment"); } for (int i = 0; i != associateData.size(); i++) { HSSFRow row = sheet.createRow((short) i + 1); row.createCell(x++).setCellValue(associateData.get(i).getMemNo()); row.createCell(x++) .setCellValue(getDataConvert().fullname(associateData.get(i).getPPrefix(), associateData.get(i).getLastName(), associateData.get(i).getFirstName(), associateData.get(i).getMiddleName(), associateData.get(i).getSuffix())); try { row.createCell(x++).setCellValue(associateData.get(i).getOuCode().getOuShortName()); } catch (Exception e) { } row.createCell(x++).setCellValue(associateData.get(i).getScAcctno()); if (getExportColumns().get(0)) { row.createCell(x++).setCellValue(associateData.get(i).getStreet() + " " + associateData.get(i).getBarangay() + " " + associateData.get(i).getCityMun() + " " + (associateData.get(i).getProvince() != null ? associateData.get(i).getProvince() : "")); } if (getExportColumns().get(1)) { row.createCell(x++).setCellValue(getDataConvert().genderConv(associateData.get(i).getGender())); } if (getExportColumns().get(2)) { row.createCell(x++) .setCellValue(getDataConvert().civilStatusConv(associateData.get(i).getCivilStatus())); } if (getExportColumns().get(3)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getCustomDate().formatDate(associateData.get(i).getBirthdate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(4)) { row.createCell(x++).setCellValue(associateData.get(i).getTaxIdNo()); } if (getExportColumns().get(5)) { row.createCell(x++).setCellValue(associateData.get(i).getPagIbig()); } if (getExportColumns().get(6)) { row.createCell(x++).setCellValue(associateData.get(i).getPhilhealth()); } if (getExportColumns().get(7)) { row.createCell(x++).setCellValue(associateData.get(i).getSss()); } if (getExportColumns().get(8)) { row.createCell(x++).setCellValue(associateData.get(i).getGsis()); } if (getExportColumns().get(9)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getCustomDate().formatDate(associateData.get(i).getMemDate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(10)) { row.createCell(x++).setCellValue(associateData.get(i).getStatusId().getStatusName()); } if (getExportColumns().get(11)) { //this is conditional try { String skl = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory() .createEntityManager() .createQuery("SELECT c.skillsCode.skillsName FROM CoopSkillsAssoc c " + "WHERE UPPER(c.skillsCode.skillsName) LIKE UPPER('%" + filterData.getSkill() + "%') " + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(skl); } catch (Exception e) { row.createCell(x++).setCellValue(""); } } if (getExportColumns().get(12)) { // if (filterData.getCompensation() != null) { // row.createCell(x++).setCellValue(filterData.getCompensation()); // } else { // try { // String cmpBrckt = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlAssoc c " // + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList().get(0); // row.createCell(x++).setCellValue(cmpBrckt); // } catch (Exception e) { // row.createCell(x++).setCellValue(""); // } // } //new start if (filterData.getCompensation() != null) { row.createCell(x++).setCellValue(filterData.getCompensation()); } else { try { String cmpBrckt = (String) getCustomEntityManagerFactory() .getLportalMemOrgEntityManagerFactory().createEntityManager() .createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlAssoc c " + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(cmpBrckt); } catch (Exception e) { row.createCell(x++).setCellValue(""); } } //new end } if (getExportColumns().get(13)) { // try { // String rnkPos = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlAssoc c " // + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList().get(0); // row.createCell(x++).setCellValue(rnkPos); // } catch (Exception e) { // row.createCell(x++).setCellValue(""); // } //new start //this is conditional try { String rnkPos = (String) getCustomEntityManagerFactory() .getLportalMemOrgEntityManagerFactory().createEntityManager() .createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlAssoc c " + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') " + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'") .getResultList().get(0); row.createCell(x++).setCellValue(rnkPos); } catch (Exception e) { row.createCell(x++).setCellValue(""); } //new end } if (getExportColumns().get(14)) { List<CoopEducInfo> educInfo = new ArrayList<>(); //new start educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory() .createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'") .getResultList(); for (int a = 0; a != educInfo.size(); a++) { rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); } //new end // //POST-GRADUATE // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " // + "WHERE c.educInfoNum.schoolLevel ='POST-GRADUATE' " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // // //COLLEGE // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " // + "WHERE c.educInfoNum.schoolLevel ='COLLEGE' " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //VOCATIONAL // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " // + "WHERE c.educInfoNum.schoolLevel ='VOCATIONAL' " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //HIGH SCHOOL // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " // + "WHERE c.educInfoNum.schoolLevel ='HIGH SCHOOL' " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } // // //ELEMENTARY // if (educInfo.isEmpty()) { // educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c " // + "WHERE c.educInfoNum.schoolLevel ='ELEMENTARY' " // + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList(); // for (int a = 0; a != educInfo.size(); a++) { // rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1)); // row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a))); // } // } if (educInfo.isEmpty()) { row.createCell(x++).setCellValue(""); } } x = 0; } FileOutputStream fileOut = new FileOutputStream(getFilename()); workbook.write(fileOut); fileOut.close(); fileUploadByDL(getFilename(), "Membership export", themeDisplay, null); //delete file File file = new File(getFilename()); if (file.exists()) { file.delete(); } } catch (Exception ex) { System.out.println(ex); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } setFilename("" + fName + ""); }
From source file:bean.ExportData.java
public void applicantList(List<CoopApplicant> applicantData, ApplicantFilterData filterData) { String fName = getFilename(); ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); createFolder(null, themeDisplay, "Applicant export", "Description"); int x = 0, y = 0; if (getFilename() == null || getFilename().length() == 0) { setFilename("Filtered Data List(" + new Date() + ")"); }//from w ww . jav a 2 s .c o m try { setFilename(getFilename().concat(".xls")); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(y++).setCellValue("Applicant No."); rowhead.createCell(y++).setCellValue("Full Name"); rowhead.createCell(y++).setCellValue("PT Number"); if (getExportColumns().get(0)) { rowhead.createCell(y++).setCellValue("Address"); } if (getExportColumns().get(1)) { rowhead.createCell(y++).setCellValue("Gender"); } if (getExportColumns().get(2)) { rowhead.createCell(y++).setCellValue("Civil Status"); } if (getExportColumns().get(3)) { rowhead.createCell(y++).setCellValue("Birthdate"); } if (getExportColumns().get(4)) { rowhead.createCell(y++).setCellValue("Contact Number"); } if (getExportColumns().get(5)) { rowhead.createCell(y++).setCellValue("Educational Attainment"); } if (getExportColumns().get(6)) { rowhead.createCell(y++).setCellValue("Occupation"); } if (getExportColumns().get(7)) { rowhead.createCell(y++).setCellValue("Application Date"); } if (getExportColumns().get(8)) { rowhead.createCell(y++).setCellValue("Application Status"); } if (getExportColumns().get(9)) { rowhead.createCell(y++).setCellValue("Applicant Type"); } for (int i = 0; i != applicantData.size(); i++) { HSSFRow row = sheet.createRow((short) i + 1); row.createCell(x++).setCellValue(applicantData.get(i).getApplicantNo()); row.createCell(x++).setCellValue(getDataConvert().fullname("", applicantData.get(i).getLastName(), applicantData.get(i).getFirstName(), applicantData.get(i).getMiddleName(), "")); try { row.createCell(x++).setCellValue(applicantData.get(i).getOuCode().getOuShortName()); } catch (Exception e) { row.createCell(x++).setCellValue(""); } if (getExportColumns().get(0)) { row.createCell(x++).setCellValue(applicantData.get(i).getStreet() + " " + applicantData.get(i).getBarangay() + " " + applicantData.get(i).getCityMun() + " " + (applicantData.get(i).getProvince() != null ? applicantData.get(i).getProvince() : "")); } if (getExportColumns().get(1)) { row.createCell(x++).setCellValue(getDataConvert().genderConv(applicantData.get(i).getGender())); } if (getExportColumns().get(2)) { row.createCell(x++).setCellValue( getDataConvert().civilStatusConv(applicantData.get(i).getCivilStatus().toString())); } if (getExportColumns().get(3)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getCustomDate().formatDate(applicantData.get(i).getBirthdate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(4)) { row.createCell(x++).setCellValue(applicantData.get(i).getContactNumber()); } if (getExportColumns().get(5)) { row.createCell(x++) .setCellValue(getDataConvert().educConv(applicantData.get(i).getEducation())); } if (getExportColumns().get(6)) { row.createCell(x++).setCellValue(applicantData.get(i).getOccupation()); } if (getExportColumns().get(7)) { HSSFCell cell = row.createCell(x++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getCustomDate().formatDate(applicantData.get(i).getApplicationDate(), "MM/dd/YYYY")); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("MM/dd/YYYY"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle); } if (getExportColumns().get(8)) { row.createCell(x++) .setCellValue(getDataConvert().appStatConv(applicantData.get(i).getApplicationStat())); } if (getExportColumns().get(9)) { row.createCell(x++).setCellValue(applicantData.get(i).getApplicantType()); } x = 0; } FileOutputStream fileOut = new FileOutputStream(getFilename()); workbook.write(fileOut); fileOut.close(); fileUploadByDL(getFilename(), "Applicant export", themeDisplay, null); //delete file File file = new File(getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.println(e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } setFilename("" + fName + ""); }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) { try {//w w w . j a v a 2 s . c o m List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } int r = 1; for (Funcionario f : funcionarios) { Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); Cell cargo = row.createCell(1); cargo.setCellValue(f.getCargo()); Cell dtAdmissao = row.createCell(2); dtAdmissao.setCellValue(f.getDtAdmissao()); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfAdmissao = workbook.createDataFormat(); styleDate.setDataFormat(dfAdmissao.getFormat("dd/mm/yyyy")); dtAdmissao.setCellStyle(styleDate); Cell area = row.createCell(3); area.setCellValue(f.getArea()); Cell gestor = row.createCell(4); gestor.setCellValue(f.getGestor()); try { Cell email = row.createCell(5); email.setCellValue(f.getEmail()); } catch (NullPointerException ne) { } try { Cell telefone = row.createCell(6); telefone.setCellValue(f.getTelefone()); } catch (NullPointerException e) { } try { Cell celular = row.createCell(7); celular.setCellValue(f.getCelular()); } catch (NullPointerException e) { } r++; } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } } catch (Exception e) { logger.error("Error gerate Report: " + e); System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) { try {//from ww w. ja v a 2 s . c o m List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getCertificacoes().isEmpty()) { for (Certificacao ct : f.getCertificacoes()) { Row row = sheet.createRow(r); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfExame = workbook.createDataFormat(); styleDate.setDataFormat(dfExame.getFormat("dd/mm/yyyy")); try { Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); } catch (NullPointerException e) { } try { Cell cod = row.createCell(1); cod.setCellValue(ct.getCodigo()); } catch (NullPointerException e) { } try { Cell nome = row.createCell(3); nome.setCellValue(ct.getNome()); } catch (NullPointerException e) { } try { Cell empresa = row.createCell(2); empresa.setCellValue(ct.getEmpresa()); } catch (NullPointerException e) { } try { Cell dtExame = row.createCell(4); dtExame.setCellValue(ct.getDtExame()); dtExame.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell dtValidade = row.createCell(5); dtValidade.setCellValue(ct.getDtValidade()); dtValidade.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell copia = row.createCell(6); copia.setCellValue(ct.getCopia()); } catch (NullPointerException e) { } r++; } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } // String file = "C:/Users/ebranco.TELESULCORP/new.xls"; // FileOutputStream out = new FileOutputStream(file); // workbook.write(out); // out.close(); // workbook.close(); // System.out.println("Excell write succesfully"); } catch (Exception e) { System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelSingle(Long id, HSSFWorkbook workbook) { String[] columns = { "Nome", "Cargo", "Data de Admissao", "?rea", "Gestor", "Email", "Telefone", "Celular" }; String[] colFormacao = { "Formao", "Curso", "Instituio", "Cpia de Certificao" }; String[] colIdioma = { "Idioma", "Nvel" }; String[] colCertificacao = { "Certificadora", "Exame", "Cdigo", "Data de Exame", "Data de Validade", "Cpia de Certificado" }; try {//from www.ja v a 2 s . c o m Funcionario funcionario = this.funcionarioService.searchById(id); HSSFSheet sheet = workbook.createSheet("Funcionrio"); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } stylizeHeader(rowHeading, workbook, columns); int r = 1; Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(funcionario.getNome()); Cell cargo = row.createCell(1); cargo.setCellValue(funcionario.getCargo()); Cell dtAdmissao = row.createCell(2); dtAdmissao.setCellValue(funcionario.getDtAdmissao()); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfAdmissao = workbook.createDataFormat(); styleDate.setDataFormat(dfAdmissao.getFormat("dd/mm/yyyy")); dtAdmissao.setCellStyle(styleDate); Cell area = row.createCell(3); area.setCellValue(funcionario.getArea()); Cell gestor = row.createCell(4); gestor.setCellValue(funcionario.getGestor()); try { Cell email = row.createCell(5); email.setCellValue(funcionario.getEmail()); } catch (NullPointerException ne) { } try { Cell telefone = row.createCell(6); telefone.setCellValue(funcionario.getTelefone()); } catch (NullPointerException ne) { } try { Cell celular = row.createCell(7); celular.setCellValue(funcionario.getCelular()); } catch (NullPointerException ne) { } int auxRow = 12; for (Formacao form : funcionario.getFormacoes()) { if (!form.getCurso().isEmpty() || !form.getNivel().isEmpty() || !form.getInstituicao().isEmpty()) { int headerFormacao = 11; Row rowHeadingForm = sheet.createRow(headerFormacao); for (int i = 0; i < colFormacao.length; i++) { rowHeadingForm.createCell(i).setCellValue(colFormacao[i]); } stylizeHeader(rowHeadingForm, workbook, colFormacao); int rowFormacao = 12; Row rowFormacaoDatas = sheet.createRow(rowFormacao); Cell formacao = rowFormacaoDatas.createCell(0); Cell curso = rowFormacaoDatas.createCell(1); Cell instituicao = rowFormacaoDatas.createCell(2); Cell copy = rowFormacaoDatas.createCell(3); for (Formacao f : funcionario.getFormacoes()) { try { formacao.setCellValue(f.getNivel()); } catch (NullPointerException e) { } try { curso.setCellValue(f.getCurso()); } catch (NullPointerException e) { } try { instituicao.setCellValue(f.getInstituicao()); } catch (NullPointerException e) { } try { copy.setCellValue(f.getCopiaCertificado()); } catch (NullPointerException e) { } rowFormacao++; auxRow = rowFormacao; } autoSizeColum(sheet, colFormacao); } } for (Idioma i : funcionario.getIdiomas()) { try { if (!i.getNome().toString().isEmpty() && !i.getNivel().toString().isEmpty()) { int headerIdiomas = auxRow + 9; Row rowHeadingIdioma = sheet.createRow(headerIdiomas); for (int j = 0; j < colIdioma.length; j++) { rowHeadingIdioma.createCell(j).setCellValue(colIdioma[j]); } stylizeHeader(rowHeadingIdioma, workbook, colIdioma); int rowIdioma = headerIdiomas + 1; Row rowIdiomasDatas = sheet.createRow(rowIdioma); Cell nivelIdm = rowIdiomasDatas.createCell(0); Cell language = rowIdiomasDatas.createCell(1); for (Idioma j : funcionario.getIdiomas()) { nivelIdm.setCellValue(j.getNivel().toString()); language.setCellValue(j.getNome().toString()); rowIdioma++; auxRow = rowIdioma; } autoSizeColum(sheet, colIdioma); } } catch (NullPointerException ne) { logger.error("Idiomas" + ne); break; } } if (funcionario.getCertificacoes().size() > 0) { int headerCertificacao = auxRow + 9; Row rowHeadingCert = sheet.createRow(headerCertificacao); for (int j = 0; j < colCertificacao.length; j++) { rowHeadingCert.createCell(j).setCellValue(colCertificacao[j]); } stylizeHeader(rowHeadingCert, workbook, colCertificacao); int rowCert = headerCertificacao + 1; for (Certificacao c : funcionario.getCertificacoes()) { Row rowCertDatas = sheet.createRow(rowCert); Cell certificadora = rowCertDatas.createCell(0); Cell exame = rowCertDatas.createCell(1); Cell codigo = rowCertDatas.createCell(2); Cell dtExame = rowCertDatas.createCell(3); dtExame.setCellStyle(styleDate); Cell dtValidade = rowCertDatas.createCell(4); dtValidade.setCellStyle(styleDate); Cell copia = rowCertDatas.createCell(5); try { certificadora.setCellValue(c.getEmpresa()); } catch (NullPointerException e) { } try { exame.setCellValue(c.getNome()); } catch (NullPointerException e) { } try { codigo.setCellValue(c.getCodigo()); } catch (NullPointerException e) { } try { dtExame.setCellValue(c.getDtExame()); } catch (NullPointerException e) { } try { dtValidade.setCellValue(c.getDtValidade()); } catch (NullPointerException e) { } try { copia.setCellValue(c.getCopia()); } catch (NullPointerException e) { } rowCert++; } autoSizeColum(sheet, colCertificacao); } //r++; } catch (Exception e) { logger.error("Error Writing Single Report: " + e); System.out.println("rror Writing Single Report: " + e); } }
From source file:com.accenture.control.GerenciaPlanilhaTS.java
public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS) throws FileNotFoundException, IOException { FileInputStream arquivo = new FileInputStream(new File(dir)); HSSFWorkbook workbook = new HSSFWorkbook(arquivo); HSSFSheet sheetTS = workbook.getSheetAt(0); HSSFDataFormat format = workbook.createDataFormat(); HSSFCellStyle estilo = workbook.createCellStyle(); String formatData = "aaaa-mm-dd\"T12:00:00-03:00\""; int linha = 1; Row row = sheetTS.getRow(linha);//from ww w.j a va2 s . c o m Cell descriptionPlan = row.getCell(0); Cell prj = row.getCell(1); Cell fase = row.getCell(2); Cell testPhase = row.getCell(3); Cell testScriptName = row.getCell(4); Cell testScriptDescription = row.getCell(5); Cell stepNo = row.getCell(6); Cell stepDescription = row.getCell(7); Cell expectedResults = row.getCell(8); Cell product = row.getCell(9); Cell dataPlanejada = row.getCell(10); for (int i = 0; i < listTS.size(); i++) { estilo.setDataFormat(format.getFormat(formatData)); estilo.setFillBackgroundColor(HSSFColor.GREEN.index); row = sheetTS.getRow(linha); descriptionPlan = row.getCell(0); prj = row.getCell(1); fase = row.getCell(2); testPhase = row.getCell(3); testScriptName = row.getCell(4); testScriptDescription = row.getCell(5); stepNo = row.getCell(6); stepDescription = row.getCell(7); expectedResults = row.getCell(8); product = row.getCell(9); dataPlanejada = row.getCell(10); descriptionPlan.setCellValue(listTS.get(i).getTestPlan()); prj.setCellValue(listTS.get(i).getSTIPRJ()); fase.setCellValue(listTS.get(i).getFASE()); testPhase.setCellValue(listTS.get(i).getTestPhase()); testScriptName.setCellValue(listTS.get(i).getTestScriptName()); testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription()); stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO()); stepDescription.setCellValue(listTS.get(i).getStepDescription()); expectedResults.setCellValue(listTS.get(i).getExpectedResults()); product.setCellValue(listTS.get(i).getProduct()); dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada()); dataPlanejada.setCellStyle(estilo); linha = linha + 2; } FileOutputStream fileOut = new FileOutputStream(new File(dir)); workbook.write(fileOut); fileOut.close(); arquivo.close(); }
From source file:com.demo.common.extreme.view.XlsView.java
License:Apache License
private Map initStyles(HSSFWorkbook wb, short fontHeight) { Map result = new HashMap(); HSSFCellStyle titleStyle = wb.createCellStyle(); HSSFCellStyle textStyle = wb.createCellStyle(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFCellStyle numericStyle = wb.createCellStyle(); HSSFCellStyle numericStyleBold = wb.createCellStyle(); HSSFCellStyle moneyStyle = wb.createCellStyle(); HSSFCellStyle moneyStyleBold = wb.createCellStyle(); HSSFCellStyle percentStyle = wb.createCellStyle(); HSSFCellStyle percentStyleBold = wb.createCellStyle(); // Add to export totals HSSFCellStyle moneyStyle_Totals = wb.createCellStyle(); HSSFCellStyle naStyle_Totals = wb.createCellStyle(); HSSFCellStyle numericStyle_Totals = wb.createCellStyle(); HSSFCellStyle percentStyle_Totals = wb.createCellStyle(); HSSFCellStyle textStyle_Totals = wb.createCellStyle(); result.put("titleStyle", titleStyle); result.put("textStyle", textStyle); result.put("boldStyle", boldStyle); result.put("numericStyle", numericStyle); result.put("numericStyleBold", numericStyleBold); result.put("moneyStyle", moneyStyle); result.put("moneyStyleBold", moneyStyleBold); result.put("percentStyle", percentStyle); result.put("percentStyleBold", percentStyleBold); // Add to export totals result.put("moneyStyle_Totals", moneyStyle_Totals); result.put("naStyle_Totals", naStyle_Totals); result.put("numericStyle_Totals", numericStyle_Totals); result.put("percentStyle_Totals", percentStyle_Totals); result.put("textStyle_Totals", textStyle_Totals); HSSFDataFormat format = wb.createDataFormat(); // Global fonts HSSFFont font = wb.createFont();/*from w w w . ja v a 2s .c o m*/ font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setColor(HSSFColor.BLACK.index); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints(fontHeight); HSSFFont fontBold = wb.createFont(); fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontBold.setColor(HSSFColor.BLACK.index); fontBold.setFontName(HSSFFont.FONT_ARIAL); fontBold.setFontHeightInPoints(fontHeight); // Money Style moneyStyle.setFont(font); moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle.setDataFormat(format.getFormat(moneyFormat)); // Money Style Bold moneyStyleBold.setFont(fontBold); moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyleBold.setDataFormat(format.getFormat(moneyFormat)); // Percent Style percentStyle.setFont(font); percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle.setDataFormat(format.getFormat(percentFormat)); // Percent Style Bold percentStyleBold.setFont(fontBold); percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyleBold.setDataFormat(format.getFormat(percentFormat)); // Standard Numeric Style numericStyle.setFont(font); numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Standard Numeric Style Bold numericStyleBold.setFont(fontBold); numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Title Style titleStyle.setFont(font); titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBottomBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setLeftBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setRightBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setTopBorderColor(HSSFColor.BLACK.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Standard Text Style textStyle.setFont(font); textStyle.setWrapText(true); // Standard Text Style boldStyle.setFont(fontBold); boldStyle.setWrapText(true); // Money Style Total moneyStyle_Totals.setFont(fontBold); moneyStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); moneyStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); moneyStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); moneyStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); moneyStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); moneyStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); moneyStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); moneyStyle_Totals.setDataFormat(format.getFormat(moneyFormat)); // n/a Style Total naStyle_Totals.setFont(fontBold); naStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); naStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); naStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); naStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); naStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); naStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); naStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); naStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Numeric Style Total numericStyle_Totals.setFont(fontBold); numericStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); numericStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); numericStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); numericStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); numericStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); numericStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); numericStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); numericStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Percent Style Total percentStyle_Totals.setFont(fontBold); percentStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); percentStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); percentStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); percentStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); percentStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); percentStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); percentStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); percentStyle_Totals.setDataFormat(format.getFormat(percentFormat)); // Text Style Total textStyle_Totals.setFont(fontBold); textStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); textStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); textStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); textStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); textStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); textStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); textStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_LEFT); textStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return result; }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java
License:Open Source License
private void writeObservationRow(int currentRowNum, HSSFSheet xlsSheet, MeasurementRow dataRow, HSSFWorkbook xlsBook) { HSSFRow row = xlsSheet.createRow(currentRowNum); int currentColNum = 0; CellStyle style = xlsBook.createCellStyle(); DataFormat format = xlsBook.createDataFormat(); style.setDataFormat(format.getFormat("0.#")); for (MeasurementData dataCell : dataRow.getDataList()) { HSSFCell cell = row.createCell(currentColNum++); /*/*w w w . java2 s .c om*/ if(AppConstants.NUMERIC_DATA_TYPE.getString().equalsIgnoreCase(dataCell.getDataType())){ cell.setCellType(Cell.CELL_TYPE_BLANK); cell.setCellType(Cell.CELL_TYPE_NUMERIC); }*/ cell.setCellValue(dataCell.getValue()); } }