List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:com.verticon.treatment.poi.handlers.EventImportHandler.java
License:Open Source License
private int processWorkSheet(Object o, Program program, EditingDomain ed, IProgressMonitor monitor) throws Exception { File f = convert(o);//from ww w.j a v a2 s . co m HSSFSheet ws = getWorkSheet(f); ExecutableProcreator procreator; if (PoiUtils.isWorkSheetMatch(PoiUtils.TESTLOG_HEADER, ws)) { procreator = ExecutableProcreatorFactory.newTestEventProcreator(); System.out.printf("Processing Test Event Log with %s rows%n", ws.getLastRowNum()); } else { procreator = ExecutableProcreatorFactory.newTreatmentEventProcreator(); System.out.printf("Processing Treatment Event Log with %s rows%n", ws.getLastRowNum()); } // Exception ex = null; int count = 0; try { int rowsInSheet = ws.getPhysicalNumberOfRows(); for (int i = 1; i < rowsInSheet; i++) { System.out.printf("Processing row %s in spreadsheet with %s rows.%n", i + 1, rowsInSheet); monitor.worked(1); procreator.prepare(program, ws.getRow(i), ed); count++; } procreator.execute(ed); message.append(procreator.getStatus()); } catch (Exception e) { ex = e; } finally { procreator.dispose(); } if (ex != null) { throw ex; } return count; }
From source file:com.verticon.treatment.poi.handlers.NamingImportHandler.java
License:Open Source License
private int processWorkSheet(Object o, Map<String, String> nameMap, IProgressMonitor monitor) throws Exception { File f = convert(o);//from w w w. j a v a 2 s. c om HSSFSheet ws = getWorkSheet(f); // Exception ex = null; int count = 0; try { for (int i = 1; i < ws.getLastRowNum() + 1; i++) { System.out.printf("Processing row %s%n", i); monitor.worked(1); String account = getStringValue(ws.getRow(i), PoiUtils.ACCOUNT_COL); if (account == null || account.length() == 0) { break; } else { String fName = getStringValue(ws.getRow(i), PoiUtils.FNAME_COL); if (fName == null || fName.length() == 0) { throw new Exception("Failed to find first Name in Row " + i); } String lName = getStringValue(ws.getRow(i), PoiUtils.LNAME_COL); if (lName == null || lName.length() == 0) { throw new Exception("Failed to find last Name in Row " + i); } StringBuilder builder = new StringBuilder(fName).append(' ').append(lName); System.out.printf("Adding row %s account=%s name=%s %n", i, account, builder.toString()); nameMap.put(account, builder.toString()); } count++; } } catch (Exception e) { e.printStackTrace(); ex = e; } if (ex != null) { throw ex; } return count; }
From source file:com.viettel.vsaadmin.database.DAO.UsersDAO.java
License:Open Source License
public String importStaffFromExcel() throws Exception { List customInfo = new ArrayList();//lst users Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO if (att == null) {// if att null return error users customInfo.add("error"); } else {//from w ww .j a v a 2s . c o m ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi String dir = rb.getString("directory"); String linkFile = att.getAttachPath(); linkFile = dir + linkFile; InputStream myxls = new FileInputStream(linkFile);//get file excel //Get the workbook instance for XLS file HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; // HSSFRow firstRow = sheet.getRow(1); int rowNums = sheet.getLastRowNum(); // UsersDAOHE sdhe = new UsersDAOHE(); SimpleDateFormat formatter = new SimpleDateFormat("dd/mm/yyyy"); String userError = ""; for (int i = 1; i <= rowNums; i++) { try { row = sheet.getRow(i); if (row != null) { Users entity = new Users(); HSSFCell cellUserName = row.getCell((short) 0); HSSFCell cellFullName = row.getCell((short) 1); HSSFCell cellEmail = row.getCell((short) 2); HSSFCell cellCellPhone = row.getCell((short) 3); HSSFCell cellDeptName = row.getCell((short) 4); HSSFCell cellBusiness = row.getCell((short) 5); HSSFCell cellPosition = row.getCell((short) 6); HSSFCell cellGender = row.getCell((short) 7); HSSFCell cellTelephone = row.getCell((short) 8); HSSFCell cellFax = row.getCell((short) 9); HSSFCell cellDateOfBirth = row.getCell((short) 10); HSSFCell cellBirthPlace = row.getCell((short) 11); HSSFCell cellStaffCode = row.getCell((short) 12); HSSFCell cellIdentityCard = row.getCell((short) 13); HSSFCell cellIssueDateIdent = row.getCell((short) 14); HSSFCell cellIssuePlaceIdent = row.getCell((short) 15); HSSFCell cellDescription = row.getCell((short) 16); //validate input if (cellUserName != null) { entity.setUserName(cellUserName.toString()); } else { userError += i + " li Ti khon,"; customInfo.add(userError); } if (cellFullName != null) { entity.setFullName(cellFullName.toString()); } else { userError += i + " li Tn y ,"; customInfo.add(userError); } if (cellEmail.toString() != null && cellEmail.toString().length() > 0) { entity.setEmail(cellEmail.toString()); } if (cellCellPhone.toString() != null && cellCellPhone.toString().length() > 0) { entity.setCellphone(cellCellPhone.toString()); } //get dept DepartmentDAOHE deptdhe = new DepartmentDAOHE(); Department deptBo = deptdhe.findByName(cellDeptName.toString()); if (deptBo != null) { entity.setDeptName(deptBo.getDeptName()); entity.setDeptId(deptBo.getDeptId()); } //get business BusinessDAOHE busdhe = new BusinessDAOHE(); Business busbo = busdhe.findByName(cellBusiness.toString()); if (busbo != null) { entity.setBusinessId(busbo.getBusinessId()); entity.setBusinessName(busbo.getBusinessName()); } //get posId PositionDAOHE posdhe = new PositionDAOHE(); Position pos = posdhe.findByName(cellPosition.toString()); if (pos != null) { entity.setPosId(pos.getPosId()); } else { userError += i + " li Chc v,"; customInfo.add(userError); } if (cellTelephone != null) { entity.setTelephone(cellTelephone.toString()); } if (cellFax != null) { entity.setFax(cellFax.toString()); } if (cellBirthPlace != null) { entity.setBirthPlace(cellBirthPlace.toString()); } if (cellStaffCode != null) { entity.setStaffCode(cellStaffCode.toString()); } if (cellIdentityCard != null) { entity.setIdentityCard(cellIdentityCard.toString()); } if (cellIssuePlaceIdent != null) { entity.setIssuePlaceIdent(cellIssuePlaceIdent.toString()); } if (cellIssueDateIdent != null && cellIssueDateIdent.toString().length() > 0) { entity.setIssueDateIdent(formatter.parse(cellIssueDateIdent.toString())); } if (cellDateOfBirth != null) { entity.setDateOfBirth(formatter.parse(cellDateOfBirth.toString())); } if (cellDescription != null) { entity.setDescription(cellDescription.toString()); } // end validate input String passwordEncrypt = PasswordService.getInstance().encrypt("Attp@123"); entity.setPassword(passwordEncrypt); entity.setPasswordchanged(0L); entity.setStatus(1L); // entity.setDeptId(Long.parseLong(cellDeptId.toString())); // entity.setPosId(Long.parseLong(cellPosId.toString())); // entity.setStatus(Long.parseLong(cellStatus.toString())); // entity.setGender(Long.parseLong(cellGender.toString())); String gender; if (cellGender == null) { userError += i + " li Gii tnh,"; customInfo.add(userError); } else { gender = cellGender.toString().trim().toLowerCase(); if (gender.contains("Nam") || gender.contains("man") || gender.contains("male")) { entity.setGender(0L); } else { entity.setGender(1L); } } if (entity != null) { getSession().saveOrUpdate(entity); RoleUser roleUser = new RoleUser(); roleUser.setIsActive(1L); roleUser.setIsAdmin(0L); roleUser.setUserId(entity.getUserId()); roleUser.setRoleId(323L); roleUser.setRoleUserPK(new RoleUserPK(322, entity.getUserId())); getSession().saveOrUpdate(roleUser); customInfo.add("success"); } else { userError += i + ","; customInfo.add(userError); } } // end if row != null } // end if att != null catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 userError += i + ","; customInfo.add(userError); // jsonDataGrid.setCustomInfo(customInfo); // return "gridData"; } } // end loop } this.jsonDataGrid.setCustomInfo(customInfo); return "gridData"; }
From source file:com.vportal.portlet.vcms.action.ReportAction.java
License:Open Source License
public void setReportByType(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, long statusId, Date fromDate, Date toDate, String byUser) throws Exception { HSSFRow row;//from w w w. j av a 2 s.c o m List listType = VcmsTypeServiceUtil.getTypesByS_L(groupId, language); sheet.shiftRows(y, sheet.getLastRowNum(), 1); //date row = sheet.createRow(y - 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(fromDate) + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(toDate), wb); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "Lo\u1ea1i tin", wb); ReportUtil.createCellBold(row, (short) (x + 2), "T\u00e1c ph\u1ea9m", wb); int totalArticle = 0; int stt = 0; for (int j = 0; j < listType.size(); j++) { VcmsType vcmsType = (VcmsType) listType.get(j); sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); int total = VcmsArticleServiceUtil.countByType(groupId, language, statusId, Long.valueOf(vcmsType.getTypeId()), fromDate, toDate, byUser); totalArticle += total; if (vcmsType.getTypeId() != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsType.getName() + "", wb); ReportUtil.createCell(row, (short) (x + 2), total, wb); } } //total count row = sheet.createRow(y + 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb); ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream( PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportByType.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.vportal.portlet.vcms.action.ReportAction.java
License:Open Source License
public void setCategoryReport(List portionResults, int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, String parentId, Date dateFrom, Date dateTo, String byUser) throws Exception { int totalStatus = VcmsStatusLocalServiceUtil.countByGroupId(groupId); List statusResults = VcmsStatusLocalServiceUtil.getByGroupId(groupId, 0, totalStatus); HSSFRow row;/*from w w w . j a va 2 s .c om*/ HSSFCell cell; User userBean; sheet.shiftRows(y, sheet.getLastRowNum(), 1); row = sheet.createRow(y - 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom) + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "Chuy\u00ean m\u1ee5c", wb); for (int j = 0; j < totalStatus; j++) { VcmsStatus vcmsStatus = (VcmsStatus) statusResults.get(j); ReportUtil.createCellBold(row, (short) (x + 2 + j), "" + vcmsStatus.getName() + "", wb); } int stt = 0; int totalArticle = 0; for (int i = portionResults.size() - 1; i >= 0; --i) { VcmsPortion vcmsPortion = (VcmsPortion) portionResults.get(i); List categoryByPortion = ActionUtil.getListCategoryReport(groupId, vcmsPortion.getPortionId(), parentId, language); for (int j = 0; j < categoryByPortion.size(); j++) { VcmsCategory vcmsCategory = (VcmsCategory) categoryByPortion.get(j); sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); if (vcmsCategory.getName() != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); } if (vcmsCategory.getName() != null) { ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsCategory.getName() + "", wb); } for (int k = 0; k < totalStatus; k++) { int countArticles = 0; VcmsStatus status = (VcmsStatus) statusResults.get(k); countArticles = VcmsArticleServiceUtil.countByC_P_L_S_D(vcmsCategory.getCategoryId(), groupId, language, status.getStatusId(), dateFrom, dateTo, byUser); totalArticle += countArticles; ReportUtil.createCell(row, (short) (x + 2 + k), countArticles, wb); } } } row = sheet.createRow(y + 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb); ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportCategory.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.vportal.portlet.vcms.action.ReportAction.java
License:Open Source License
public void setReportByDate(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, long statusId, Date dateFrom, Date dateTo, String byUser, String[] strType, int begin, int end) throws Exception { List listNews = new ArrayList(); listNews = VcmsArticleServiceUtil.listArticleByDateTypes(groupId, language, statusId, dateFrom, dateTo, byUser, strType, begin, end); HSSFRow row;/*from w w w . j av a 2s .co m*/ HSSFCell cell; User userBean; VcmsStatus vcmsStatus = VcmsStatusLocalServiceUtil.getVcmsStatus(statusId); sheet.shiftRows(y, sheet.getLastRowNum(), 1); row = sheet.createRow(y - 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom) + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "Ti\u00eau \u0111\u1ec1", wb); ReportUtil.createCellBold(row, (short) (x + 2), "Ng\u00e0y \u0111\u0103ng", wb); ReportUtil.createCellBold(row, (short) (x + 3), "Ng\u01b0\u1eddi t\u1ea1o", wb); ReportUtil.createCellBold(row, (short) (x + 4), "Ng\u01b0\u1eddi duy\u1ec7t", wb); ReportUtil.createCellBold(row, (short) (x + 5), "Ng\u01b0\u1eddi xu\u1ea5t b\u1ea3n", wb); ReportUtil.createCellBold(row, (short) (x + 6), "Lo\u1ea1i tin", wb); int stt = 0; for (int j = 0; j < listNews.size(); j++) { VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j); String typeNames = ""; try { typeNames += ReportUtil.getTypeNames(vcmsAr.getArticleId()); } catch (Exception ex) { ex.printStackTrace(); } sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); if (vcmsAr != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsAr.getTitle() + "", wb); ReportUtil.createCell(row, (short) (x + 2), ActionUtil.dateParser(vcmsAr.getPublishedDate()), wb); try { User userCreate = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getCreatedByUser())); ReportUtil.createCellAlignLeft(row, (short) (x + 3), userCreate.getFullName(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 3), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb); } try { User modifieddUser = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getModifiedByUser())); ReportUtil.createCellAlignLeft(row, (short) (x + 4), modifieddUser.getFullName(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 4), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb); } try { User publishedUser = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getPublishedByUser())); ReportUtil.createCellAlignLeft(row, (short) (x + 5), publishedUser.getFullName(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 5), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb); } ReportUtil.createCellAlignLeft(row, (short) (x + 6), typeNames, wb); } } row = sheet.createRow(y + 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb); ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(listNews.size()), wb); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream( PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportByDate.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.vportal.portlet.vcms.action.ReportAction.java
License:Open Source License
public void setUserReport(List userList, int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, String parentId, long statusId, Date dateFrom, Date dateTo) throws Exception { List listType = VcmsTypeServiceUtil.getTypesByS_L(groupId, language); HSSFRow row;/*from w w w.j av a2s . co m*/ User userBean; sheet.shiftRows(y, sheet.getLastRowNum(), 1); row = sheet.createRow(y - 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom) + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "H\u1ecd T\u00ean", wb); for (int j = 0; j < listType.size(); j++) { VcmsType vcmsType = (VcmsType) listType.get(j); ReportUtil.createCellBold(row, (short) (x + 2 + j), "" + vcmsType.getName() + "", wb); } ReportUtil.createCellBold(row, (short) (x + 2 + listType.size()), "C\u00e1c lo\u1ea1i tin kh\u00e1c", wb); int stt = 0; int totalArticle = 0; for (int i = userList.size() - 1; i >= 0; --i) { userBean = (User) userList.get(i); sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); if (userBean != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); } if (userBean != null) { ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + userBean.getFullName() + "", wb); } for (int j = 0; j < listType.size(); j++) { VcmsType vcmsType = (VcmsType) listType.get(j); int countArticle = 0; if (!parentId.equals("0")) { countArticle = VcmsArticleServiceUtil.countByUser(groupId, language, parentId, statusId, Long.valueOf(vcmsType.getTypeId()), dateFrom, dateTo, String.valueOf(userBean.getUserId())); } else { countArticle = VcmsArticleServiceUtil.countByType(groupId, language, statusId, Long.valueOf(vcmsType.getTypeId()), dateFrom, dateTo, String.valueOf(userBean.getUserId())); } ReportUtil.createCell(row, (short) (x + 2 + j), countArticle, wb); totalArticle += countArticle; } //other type int countArticleAllCatNotInType = 0; if (!parentId.equals("0")) { countArticleAllCatNotInType = VcmsArticleServiceUtil.countArticleNotInType(groupId, language, parentId, statusId, dateFrom, dateTo, String.valueOf(userBean.getUserId())); } else { countArticleAllCatNotInType = VcmsArticleServiceUtil.countArticleAllCatNotInType(groupId, language, statusId, dateFrom, dateTo, String.valueOf(userBean.getUserId())); } ReportUtil.createCell(row, (short) (x + 2 + listType.size()), countArticleAllCatNotInType, wb); totalArticle += countArticleAllCatNotInType; } row = sheet.createRow(y + 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb); ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream( PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportUser.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.vportal.portlet.vdoc.action.VDocManage.java
License:Open Source License
public void setReportByDate(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, int statusId, Date dateFrom, Date dateTo) throws Exception { List listnew = new ArrayList(); listnew = vdocDocumentServiceUtil.getDocbyModifyDate(groupId, language, dateFrom, dateTo, statusId); HSSFRow row;//w w w . j a v a 2s . c om HSSFCell cell; sheet.shiftRows(sheet.getLastRowNum(), y, 1); row = sheet.createRow(y - 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom) + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "Ti\u00eau \u0111\u1ec1", wb); ReportUtil.createCellBold(row, (short) (x + 2), "Nga\u0300y xu\u00E2\u0301t ba\u0309n", wb); ReportUtil.createCellBold(row, (short) (x + 3), "Nga\u0300y chi\u0309nh s\u01B0\u0309a", wb); ReportUtil.createCellBold(row, (short) (x + 4), "Ng\u01B0\u01A1\u0300i duy\u00EA\u0323t", wb); ReportUtil.createCellBold(row, (short) (x + 5), "Ng\u01B0\u01A1\u0300i chi\u0309nh s\u01B0\u0309a", wb); ReportUtil.createCellBold(row, (short) (x + 6), "\u0110\u01A1n vi\u0323", wb); int stt = 0; for (int j = 0; j < listnew.size(); j++) { // VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j); vdocDocument vdoc = (vdocDocument) listnew.get(j); sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); if (vdoc != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vdoc.getTitle() + "", wb); ReportUtil.createCell(row, (short) (x + 2), ActionUtil.dateParser(vdoc.getPublishedDate()), wb); try { // User userCreate = // UserServiceUtil.getUserById(vdoc.getCreatedByUser()); ReportUtil.createCellAlignLeft(row, (short) (x + 3), ActionUtil.dateParser(vdoc.getModifiedDate()), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 3), "Ngày chỉnh sửa", wb); } try { User modifieddUser = UserServiceUtil.getUserById(vdoc.getPublishedByUser()); ReportUtil.createCellAlignLeft(row, (short) (x + 4), modifieddUser.getFullName(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 4), "Ng\u01B0\u01A1\u0300i duy\u00EA\u0323t", wb); } try { User publishedUser = UserServiceUtil.getUserById(vdoc.getModifiedByUser()); ReportUtil.createCellAlignLeft(row, (short) (x + 5), publishedUser.getFullName(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 5), "Ng\u01B0\u01A1\u0300i chi\u0309nh s\u01B0\u0309a", wb); } try { ReportUtil.createCellAlignLeft(row, (short) (x + 6), vdoc.getOrgRels(), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 6), "\u0110\u01A1n vi\u0323", wb); } } } row = sheet.createRow(y + 2); ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb); ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(listnew.size()), wb); FileOutputStream fileOut = null; String porttalDir = PortalUtil.getPortalWebDir(); try { fileOut = new FileOutputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT")) + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByDate.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.vportal.portlet.vdoc.action.VDocManage.java
License:Open Source License
public void setReportByOrg(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language, int statusId) throws Exception { List listnew = new ArrayList(); listnew = vdocOrgServiceUtil.getOrgByG_L(groupId, language); HSSFRow row;/*from ww w . j a v a 2 s. c o m*/ HSSFCell cell; sheet.shiftRows(sheet.getLastRowNum(), y, 1); row = sheet.createRow(y - 2); row = sheet.createRow(y); ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb); ReportUtil.createCellBold(row, (short) (x + 1), "C\u01A1 quan - \u0110\u01A1n vi\u0323", wb); ReportUtil.createCellBold(row, (short) (x + 2), "Ch\u01A1\u0300 xu\u00E2\u0301t ba\u0309n", wb); ReportUtil.createCellBold(row, (short) (x + 3), "\u0110a\u0303 xu\u00E2\u0301t ba\u0309n", wb); ReportUtil.createCellBold(row, (short) (x + 4), "Chi\u0309nh s\u01B0\u0309a", wb); int stt = 0; for (int j = 0; j < listnew.size(); j++) { // VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j); vdocOrg org = (vdocOrg) listnew.get(j); sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1); row = sheet.createRow(1 + y++); if (org != null) { ReportUtil.createCell(row, (short) (x), ++stt, wb); ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + org.getName() + "", wb); ReportUtil.createCell(row, (short) (x + 2), vdocDORelServiceUtil.countCategory_approving(org.getOrgId()), wb); try { // User userCreate = // UserServiceUtil.getUserById(vdoc.getCreatedByUser()); ReportUtil.createCellAlignLeft(row, (short) (x + 3), String.valueOf(vdocDORelServiceUtil.countCategory_approving(org.getOrgId())), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 3), "...", wb); } try { ReportUtil.createCellAlignLeft(row, (short) (x + 4), String.valueOf(vdocDORelServiceUtil.countCategory_approving(org.getOrgId())), wb); } catch (Exception ex) { ReportUtil.createCellAlignLeft(row, (short) (x + 4), "...", wb); } } } FileOutputStream fileOut = null; String porttalDir = PortalUtil.getPortalWebDir(); try { fileOut = new FileOutputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT")) + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByOrg.xls"); wb.write(fileOut); } catch (Exception ex) { System.out.println(ex.toString()); } finally { fileOut.flush(); fileOut.close(); } }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
protected void processSheet(HSSFSheet sheet) { // this.processSheetHeader(this.htmlDocumentFacade.getBody(), sheet); final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); if (physicalNumberOfRows <= 0) { return;// ww w . j a va 2s . co m } Element table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); Element tableBody = this.htmlDocumentFacade.createTableBody(); final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet); final List emptyRowElements = new ArrayList(physicalNumberOfRows); int maxSheetColumns = 1; for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } if (!this.isOutputHiddenRows() && row.getZeroHeight()) { continue; } Element tableRowElement = this.htmlDocumentFacade.createTableRow(); this.htmlDocumentFacade.addStyleClass(tableRowElement, this.cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;"); int maxRowColumnNumber = this.processRow(mergedRanges, row, tableRowElement); if (maxRowColumnNumber == 0) { emptyRowElements.add(tableRowElement); } else { if (!emptyRowElements.isEmpty()) { for (Iterator iterator = emptyRowElements.iterator(); iterator.hasNext();) { Element emptyRowElement = (Element) iterator.next(); tableBody.appendChild(emptyRowElement); } emptyRowElements.clear(); } tableBody.appendChild(tableRowElement); } maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber); } this.processColumnWidths(sheet, maxSheetColumns, table); if (this.isOutputColumnHeaders()) { this.processColumnHeaders(sheet, maxSheetColumns, table); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); if (null != this.getExcelImageManager()) { table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); tableBody = this.htmlDocumentFacade.createTableBody(); List<String> urlPaths = this.getExcelImageManager() .getImagePath(sheet.getDrawingPatriarch().getChildren()); if ((urlPaths != null) && (urlPaths.size() != 0)) { Document document = this.htmlDocumentFacade.getDocument(); for (int i = 0, size = urlPaths.size(); i < size; i++) { Element tableRowElement = this.htmlDocumentFacade.createTableRow(); String[] urlPathArr = urlPaths.get(i).split("@"); Element result = document.createElement("img"); result.setAttribute("src", urlPathArr[0]); String imageWidth = urlPathArr[1]; String imageHeight = urlPathArr[2]; result.setAttribute("style", "width:" + imageWidth + "in;height:" + imageHeight + "in;vertical-align:text-bottom;"); Element tableCellElement = this.htmlDocumentFacade.createTableCell(); tableCellElement.appendChild(result); tableRowElement.appendChild(tableCellElement); tableBody.appendChild(tableRowElement); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); } } }