List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle
@Override
public HSSFCellStyle createCellStyle()
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 . j ava 2s. c om 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 w w . java 2 s .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 != 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 www . ja v a2s. com 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:bean.GlReportExport.java
public void export() { Integer columnNo;/*from w w w. ja v a 2s.com*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "GL Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook.createSheet("GL Report " + exportDate()); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("GL REPORT"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(exportDate()); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("Date"); cell.setCellStyle(boldStyle); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue("Account Title"); cell.setCellStyle(boldStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue("Journal Type"); cell.setCellStyle(boldStyle); columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellValue("Debit"); cell.setCellStyle(boldStyle); columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue("Credit"); cell.setCellStyle(boldStyle); columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int i = 0; i < getGlReportData().getGlReport().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); //DATE columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Date) getGlReportData().getGlReport().get(i)[0])); cell.setCellStyle(cellStyle); cell.getDateCellValue(); // dataRow.createCell(columnNo++).setCellValue(""); //ACCOUNT TITLE columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getGlReportData().getGlReport().get(i)[1]); cell.setCellStyle(cellStyle); //JOURNAL TYPE columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getGlReportData().getGlReport().get(i)[2]); cell.setCellStyle(cellStyle); //DEBIT columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[3]).doubleValue()); cell.setCellStyle(cellStyle); //CREDIT columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[4]).doubleValue()); cell.setCellStyle(cellStyle); //BALANCE columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[5]).doubleValue()); cell.setCellStyle(cellStyle); // if (i == getSlReportData().getSlReport().size() - 1) { // dataRow = sheet.createRow(dataRow.getRowNum() + 1); // dataRow = sheet.createRow(dataRow.getRowNum() + 1); // columnNo = 0; // // cell = dataRow.createCell(columnNo++); // cell.setCellValue("TOTAL:"); // cell.setCellStyle(boldStyle); // // dataRow.createCell(columnNo++).setCellValue(""); // // columnNo = 3; // // cell = dataRow.createCell(columnNo++); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal())); // cell.setCellStyle(boldStyle); // // columnNo = 4; // // cell = dataRow.createCell(columnNo++); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal())); // cell.setCellStyle(boldStyle); // } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "GL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("glReportExport().export() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.SlReportExport.java
public void export() { Integer columnNo;//w ww .j av a2 s . c o m HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "SL Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook.createSheet("SL Report " + exportDate()); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("SL REPORT"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(exportDate()); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("Date"); cell.setCellStyle(boldStyle); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue("Account Title"); cell.setCellStyle(boldStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue("Journal Type"); cell.setCellStyle(boldStyle); columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellValue("Debit"); cell.setCellStyle(boldStyle); columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue("Credit"); cell.setCellStyle(boldStyle); columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int i = 0; i < getSlReportData().getSlReport().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); //DATE columnNo = 0; cell = dataRow.createCell(columnNo++); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Date) getSlReportData().getSlReport().get(i)[0]); cell.setCellStyle(cellStyle); // dataRow.createCell(columnNo++).setCellValue(""); //ACCOUNT TITLE columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getSlReportData().getSlReport().get(i)[1]); cell.setCellStyle(cellStyle); //JOURNAL TYPE columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getSlReportData().getSlReport().get(i)[2]); cell.setCellStyle(cellStyle); //DEBIT columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[3]).doubleValue()); cell.setCellStyle(cellStyle); //CREDIT columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[4]).doubleValue()); cell.setCellStyle(cellStyle); //BALANCE columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[5]).doubleValue()); cell.setCellStyle(cellStyle); // if (i == getSlReportData().getSlReport().size() - 1) { // dataRow = sheet.createRow(dataRow.getRowNum() + 1); // dataRow = sheet.createRow(dataRow.getRowNum() + 1); // columnNo = 0; // // cell = dataRow.createCell(columnNo++); // cell.setCellValue("TOTAL:"); // cell.setCellStyle(boldStyle); // // dataRow.createCell(columnNo++).setCellValue(""); // // columnNo = 3; // // cell = dataRow.createCell(columnNo++); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal())); // cell.setCellStyle(boldStyle); // // columnNo = 4; // // cell = dataRow.createCell(columnNo++); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal())); // cell.setCellStyle(boldStyle); // } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "SL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("slReportExport().export() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.StatementOfFinancialConditionExport.java
public void export0() { Integer columnNo;//from w w w . ja v a 2s.c om HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Statement of Financial Condition Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook.createSheet( "Statement of Financial Condition Report " + getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("STATEMENT OF FINANCIAL CONDITION"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("ASSETS"); cell.setCellStyle(boldStyle); // for (int i = 0; i < getStatementOfFinancialConditionData().getAssetsLevel2().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2().get(i).getAcctTitle()); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); if (getStatementOfFinancialConditionData().getAssetsLevel3().get(i).isEmpty()) { cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2().get(i).getAmount() .doubleValue()); cell.setCellStyle(cellStyle); } // for (int ii = 0; ii < getStatementOfFinancialConditionData().getAssetsLevel3().get(i) .size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); dataRow.createCell(columnNo++).setCellValue( getStatementOfFinancialConditionData().getAssetsLevel3().get(i).get(ii).getAcctTitle()); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel3().get(i).get(ii) .getAmount().doubleValue()); cell.setCellStyle(cellStyle); if (ii == getStatementOfFinancialConditionData().getAssetsLevel3().get(i).size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfFinancialConditionData().getAssetsLevel2Total().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } if (i == getStatementOfFinancialConditionData().getAssetsLevel2().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL ASSETS"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2Total().get(i).doubleValue()); cell.setCellValue(getStatementOfFinancialConditionData().getAssetLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); } } // dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("LIABILITIES AND EQUITY"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("LIABILITIES"); cell.setCellStyle(boldStyle); // for (int i = 0; i < getStatementOfFinancialConditionData().getLiabilitiesLevel2().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue( getStatementOfFinancialConditionData().getLiabilitiesLevel2().get(i).getAcctTitle()); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); if (getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).isEmpty()) { cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2().get(i) .getAmount().doubleValue()); cell.setCellStyle(cellStyle); } // for (int ii = 0; ii < getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i) .size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); dataRow.createCell(columnNo++).setCellValue(getStatementOfFinancialConditionData() .getLiabilitiesLevel3().get(i).get(ii).getAcctTitle()); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).get(ii) .getAmount().doubleValue()); cell.setCellStyle(cellStyle); if (ii == getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2Total().get(i) .doubleValue()); cell.setCellStyle(cellStyle); } } if (i == getStatementOfFinancialConditionData().getLiabilitiesLevel2().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL LIABILITIES"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2Total().get(i).doubleValue()); cell.setCellValue( getStatementOfFinancialConditionData().getLiabilityLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); } } // dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("EQUITY"); cell.setCellStyle(boldStyle); // for (int i = 0; i < getStatementOfFinancialConditionData().getEquitiesLevel2().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2().get(i).getAcctTitle()); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); if (getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).isEmpty()) { cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2().get(i).getAmount() .doubleValue()); cell.setCellStyle(cellStyle); } // for (int ii = 0; ii < getStatementOfFinancialConditionData().getEquitiesLevel3().get(i) .size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); dataRow.createCell(columnNo++).setCellValue(getStatementOfFinancialConditionData() .getEquitiesLevel3().get(i).get(ii).getAcctTitle()); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).get(ii) .getAmount().doubleValue()); cell.setCellStyle(cellStyle); if (ii == getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2Total().get(i) .doubleValue()); cell.setCellStyle(cellStyle); } } if (i == getStatementOfFinancialConditionData().getEquitiesLevel2().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL EQUITY"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2Total().get(i).doubleValue()); cell.setCellValue(getStatementOfFinancialConditionData().getEquityLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL LIABILITIES AND EQUITY"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfFinancialConditionData().getLiabilityEquityLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Statement of Financial Condition Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("statementOfFinancialConditionExport().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.StatementOfOperationsExport.java
public void export0() { Integer columnNo;/*from www.j av a 2 s . c o m*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Statement of Operations Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook.createSheet( "Statement of Operations Report " + getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("STATEMENT OF OPERATIONS"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("INCOME"); cell.setCellStyle(boldStyle); // for (int i = 0; i < getStatementOfOperationsData().getIncomeLevel2().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getStatementOfOperationsData().getIncomeLevel2().get(i).getAcctTitle()); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); if (getStatementOfOperationsData().getIncomeLevel3().get(i).isEmpty()) { cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfOperationsData().getIncomeLevel2().get(i).getAmount().doubleValue()); cell.setCellStyle(cellStyle); } // for (int ii = 0; ii < getStatementOfOperationsData().getIncomeLevel3().get(i).size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); dataRow.createCell(columnNo++).setCellValue( getStatementOfOperationsData().getIncomeLevel3().get(i).get(ii).getAcctTitle()); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfOperationsData().getIncomeLevel3().get(i).get(ii).getAmount() .doubleValue()); cell.setCellStyle(cellStyle); if (ii == getStatementOfOperationsData().getIncomeLevel3().get(i).size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfOperationsData().getIncomeLevel2Total().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } if (i == getStatementOfOperationsData().getIncomeLevel2().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL INCOME"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getStatementOfOperationsData().getIncomeLevel2Total().get(i).doubleValue()); cell.setCellValue(getStatementOfOperationsData().getIncomeLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); } } // dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("EXPENSES"); cell.setCellStyle(boldStyle); // for (int i = 0; i < getStatementOfOperationsData().getExpensesLevel2().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getStatementOfOperationsData().getExpensesLevel2().get(i).getAcctTitle()); cell.setCellStyle(boldStyle); // dataRow.createCell(columnNo++).setCellValue(""); if (getStatementOfOperationsData().getExpensesLevel3().get(i).isEmpty()) { cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfOperationsData().getExpensesLevel2().get(i).getAmount().doubleValue()); cell.setCellStyle(cellStyle); } // for (int ii = 0; ii < getStatementOfOperationsData().getExpensesLevel3().get(i).size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); dataRow.createCell(columnNo++).setCellValue( getStatementOfOperationsData().getExpensesLevel3().get(i).get(ii).getAcctTitle()); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getStatementOfOperationsData().getExpensesLevel3().get(i).get(ii).getAmount() .doubleValue()); cell.setCellStyle(cellStyle); if (ii == getStatementOfOperationsData().getExpensesLevel3().get(i).size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getStatementOfOperationsData().getExpensesLevel2Total().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } if (i == getStatementOfOperationsData().getExpensesLevel2().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL EXPENSES"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(getStatementOfOperationsData().getExpensesLevel2Total().get(i).doubleValue()); cell.setCellValue(getStatementOfOperationsData().getExpenseLevel1Total().doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Statement of Operations Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("statementOfOperationsExport().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.TrialBalanceExport.java
public void export() { Integer columnNo;/*from w ww .j a va 2s. c om*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow dataRow; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Trial Balance Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); sheet = workbook .createSheet("Trial Balance Report " + getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TRIAL BALANCE"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getFinancialReportData().getCurrentMonth() != null ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " " + getFinancialReportData().getCurrentYear().toString() : getFinancialReportData().getCurrentYear().toString()); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("Account Title"); cell.setCellStyle(boldStyle); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue("Debit"); cell.setCellStyle(boldStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue("Credit"); cell.setCellStyle(boldStyle); if (getTrialBalanceData().getType() == 1) { for (int i = 0; i < getTrialBalanceData().getCombotb().size(); i++) { for (int ii = 0; ii < getTrialBalanceData().getCombotb().get(i).size(); ii++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert((String) getTrialBalanceData().getCombotb().get(i).get(ii)[0])); cell.setCellStyle(cellStyle); // dataRow.createCell(columnNo++).setCellValue(""); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( ((BigDecimal) getTrialBalanceData().getCombotb().get(i).get(ii)[1]).doubleValue()); cell.setCellStyle(cellStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( ((BigDecimal) getTrialBalanceData().getCombotb().get(i).get(ii)[2]).doubleValue()); cell.setCellStyle(cellStyle); } if (i == getTrialBalanceData().getCombotb().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL:"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((getTrialBalanceData().getComboDebitTotal()).doubleValue())); cell.setCellStyle(boldStyle); System.out.println("debit total " + getDataConvert().convertAmount(getTrialBalanceData().getComboDebitTotal())); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((getTrialBalanceData().getComboCreditTotal()).doubleValue())); cell.setCellStyle(boldStyle); System.out.println("credit total " + getDataConvert().convertAmount(getTrialBalanceData().getComboCreditTotal())); } } } else if (getTrialBalanceData().getType() == 2) { for (int i = 0; i < getTrialBalanceData().getTrialBalance().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getTrialBalanceData().getTrialBalance().get(i).getAcctTitle()); cell.setCellStyle(cellStyle); // dataRow.createCell(columnNo++).setCellValue(""); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getDataConvert() .convertAmount((BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getDebit())); cell.setCellStyle(cellStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getDataConvert().convertAmount( (BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getCredit())); cell.setCellStyle(cellStyle); if (i == getTrialBalanceData().getTrialBalance().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL:"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal())); cell.setCellStyle(boldStyle); columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue( getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal())); cell.setCellStyle(boldStyle); } } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Trial Balance Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("trialBalanceExport().export() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:Beans.GeminusCompen.java
public void postProcessXLS(Object document) throws IOException { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from w w w . j ava 2s .c o m*/ HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:beans.ReviewsBeans.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w. j a va 2 s .co m CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); for (Row row : sheet) { for (Cell cell : row) { cell.setCellValue(cell.getStringCellValue().toUpperCase()); cell.setCellStyle(style); } } }