List of usage examples for org.apache.poi.ss.usermodel Workbook cloneSheet
Sheet cloneSheet(int sheetNum);
From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
public Workbook createExcelCloneByTemplate(TemplateExportParams params, Map<Integer, List<Map<String, Object>>> map) { // step 1. ?? if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); }// ww w.j av a2 s. c o m Workbook wb = null; // step 2. ?Excel,?? try { this.teplateParams = params; wb = ExcelCache.getWorkbook(teplateParams.getTemplateUrl(), teplateParams.getSheetNum(), true); int oldSheetNum = wb.getNumberOfSheets(); List<String> oldSheetName = new ArrayList<>(); for (int i = 0; i < oldSheetNum; i++) { oldSheetName.add(wb.getSheetName(i)); } // KEY? List<Map<String, Object>> mapList; List<Integer> sheetNumList = new ArrayList<>(); sheetNumList.addAll(map.keySet()); Collections.sort(sheetNumList); //??? for (Integer sheetNum : sheetNumList) { mapList = map.get(sheetNum); for (int i = mapList.size(); i > 0; i--) { wb.cloneSheet(sheetNum); } } for (int i = 0; i < oldSheetName.size(); i++) { wb.removeSheetAt(wb.getSheetIndex(oldSheetName.get(i))); } // ? setExcelExportStyler( (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb)); // step 3. ?? int sheetIndex = 0; for (Integer sheetNum : sheetNumList) { mapList = map.get(sheetNum); for (int i = mapList.size() - 1; i >= 0; i--) { tempCreateCellSet.clear(); if (mapList.get(i).containsKey(SHEET_NAME)) { wb.setSheetName(sheetIndex, mapList.get(i).get(SHEET_NAME).toString()); } parseTemplate(wb.getSheetAt(sheetIndex), mapList.get(i), params.isColForEach()); sheetIndex++; } } } catch (Exception e) { LOGGER.error(e.getMessage(), e); return null; } return wb; }
From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java
License:Apache License
/** * Creates a clone of the specified sheet. * @param oldName the name of original sheet * @param newName the created sheet name * @throws IllegalArgumentException if some parameters were {@code null} *///from ww w.j av a 2s . c o m public void copy(String oldName, String newName) { if (oldName == null) { throw new IllegalArgumentException("oldName must not be null"); //$NON-NLS-1$ } if (newName == null) { throw new IllegalArgumentException("newName must not be null"); //$NON-NLS-1$ } Workbook workbook = info.workbook; int oldIndex = workbook.getSheetIndex(oldName); if (oldIndex < 0) { throw new IllegalArgumentException(); } Sheet newSheet = workbook.cloneSheet(oldIndex); int newIndex = workbook.getSheetIndex(newSheet); workbook.setSheetName(newIndex, newName); }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam) throws ExceptionWrapper { try {/* w w w . j ava 2 s .c o m*/ FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); int iSheetCounter = 1; for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) { GroupBO groupBO = groupsBL.getAllGroups().get(groupID); if (isScrumTeam && !groupBO.getRallyGroup()) continue; if (!isScrumTeam && groupBO.getRallyGroup()) continue; Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName()); //wb.setSheetName(iSheetCounter,groupBO.getGroupName()); int iRowCounter = 1; for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) { Row row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); if (isScrumTeam) { cell = row.getCell(8); if (cell == null) cell = row.createCell(8); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam()); } iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper { try {/*from w ww.ja va 2s. com*/ FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo(); //Create The Summary Sheet Sheet sheetSummary = wb.getSheetAt(1); int iSummaryRowCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Row row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(licenseType); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); iSummaryRowCounter = iSummaryRowCounter + 1; } int iSheetCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), licenseType); CellReference cellReference = new CellReference("B1"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B2"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B3"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>( employeeBL.getAllEmployees().values()); final String selectedLicenseTypeName = licenseType; CollectionUtils.filter(allEmployees, new Predicate() { @Override public boolean evaluate(Object o) { EmployeeBO emp = (EmployeeBO) o; if (CollectionUtils.exists(emp.getLicenses(), new Predicate() { @Override public boolean evaluate(Object o) { return ((LicenseBO) o).getLicenseTypeName() .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates. } })) return true; else return false; } }); int iRowCounter = 5; for (EmployeeBO employeeBO : allEmployees) { row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @param workBook//w ww. j av a2s . c o m * @param template * @param competition * @param event * @param round * @param includeCompetitors */ private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition, Event event, String round, boolean includeCompetitors) { Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template)); String sheetName = null; String eventNameFormatted = null; if (round == null) { sheetName = event.getName(); // TODO: format to short sheetname eventNameFormatted = event.getName(); } else { sheetName = event.getName() + " - " + getRoundTypesMap().get(round); eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); } log.debug("Building result sheet: {}", sheetName); workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName); workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING); eventName.setCellValue(eventNameFormatted); // get cell styles from template List<CellStyle> cellStyles = new ArrayList<CellStyle>(); Row startRow = template.getRow(4); int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells(); if (startRow != null) { log.debug("Start row contains {} cells.", numberOfColumns); for (int i = 0; i < numberOfColumns; i++) { Cell cell = startRow.getCell(i); if (cell != null) { cellStyles.add(cell.getCellStyle()); } else { cellStyles.add(workBook.createCellStyle()); } } } // adjust formulas int numberOfCompetitors = (round == null) ? event.getResults().size() : competition.getCompetitors().size(); for (int i = 0; i < numberOfCompetitors; i++) { for (int j = 0; j < numberOfColumns; j++) { if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))"); break; } } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))"); break; } } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula( "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))"); break; } } else if (SHEET_TYPE_MEAN3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",AVERAGE(" + range + ")))"); break; } } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName()) || SHEET_TYPE_BEST1M.equals(template.getSheetName()) || SHEET_TYPE_BEST1N.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_TEAMBEST1M.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName()) || SHEET_TYPE_BEST2N.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "F" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 6: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_TEAMBEST2M.equals(template.getSheetName())) { String range = "H" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(J" + (i + 4) + "=J" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName()) || SHEET_TYPE_BEST3N.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_TEAMBEST3M.equals(template.getSheetName())) { String range = "H" + (i + 5) + ":" + "J" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(K" + (i + 4) + "=K" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 10: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result case 8: Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + ")"); break; } } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result1 case 7: Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))"); break; // result2 case 11: Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5) + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5) + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))"); break; // best case 12: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5) + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5) + ",L" + (i + 5) + "))))"); break; } } else { log.error("Unsupported sheet type: {}", template.getSheetName()); } // set cell style Row row = resultSheet.getRow(i + 4); if (row != null) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellStyle(cellStyles.get(j)); } } } } // fill sheet with competitors for this event if (includeCompetitors) { if (round == null) { try { generateCompetitorResultsRows(workBook, resultSheet, event); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors and results in this sheet.", e); } } else { try { generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors in this sheet.", e); } } } }
From source file:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java
License:Open Source License
/** * @param workBook/*from w ww .j av a2 s .c om*/ * @param template * @param competition * @param event * @param round * @param includeCompetitors */ private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition, Event event, String round, boolean includeCompetitors) { Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template)); String sheetName = event.getName() + " - " + getRoundTypesMap().get(round); log.debug("Building result sheet: {}", sheetName); String eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName); workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING); eventName.setCellValue(eventNameFormatted); // get cell styles from template List<CellStyle> cellStyles = new ArrayList<CellStyle>(); Row startRow = template.getRow(4); int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells(); if (startRow != null) { log.debug("Start row contains {} cells.", numberOfColumns); for (int i = 0; i < numberOfColumns; i++) { Cell cell = startRow.getCell(i); if (cell != null) { cellStyles.add(cell.getCellStyle()); } else { cellStyles.add(workBook.createCellStyle()); } } } // adjust formulas int numberOfCompetitors = competition.getCompetitors().size(); for (int i = 0; i < numberOfCompetitors; i++) { for (int j = 0; j < numberOfColumns; j++) { if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))"); break; } } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))"); break; } } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName()) || SHEET_TYPE_MEAN3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula( "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))"); break; } } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName()) || SHEET_TYPE_BEST1M.equals(template.getSheetName()) || SHEET_TYPE_BEST1N.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "F" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 6: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result case 8: Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + ")"); break; } } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result1 case 7: Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))"); break; // result2 case 11: Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5) + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5) + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))"); break; // best case 12: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5) + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5) + ",L" + (i + 5) + "))))"); break; } } else { log.error("Unsupported sheet type: {}", template.getSheetName()); } // set cell style Row row = resultSheet.getRow(i + 4); if (row != null) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellStyle(cellStyles.get(j)); } } } } // fill sheet with competitors for this event if (includeCompetitors) { try { generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors in this sheet.", e); } } }
From source file:net.sf.excelutils.tags.SheetTag.java
License:Apache License
/** * Parse #sheet detail in list by sheetName *//*from ww w. j ava 2 s . c om*/ public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String sheetExpr = curCell.getStringCellValue(); StringTokenizer st = new StringTokenizer(sheetExpr, " "); String properties = ""; String property = ""; String sheetName = ""; // parse the collection an object int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } if (pos == 5) { sheetName = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // remove #sheet tag sheet.removeRow(curRow); // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) { sheet.removeMergedRegion(i); i = i - 1; } } sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true); // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); if (null != iterator) { // first obj, use parse method Object firstObj = null; if (iterator.hasNext()) { firstObj = iterator.next(); } // next obj, clone sheet and use parseSheet method while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); try { int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); // clone sheet Sheet cloneSheet = wb.cloneSheet(sheetIndex); // set cloneSheet name int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet); setSheetName(obj, wb, cloneSheetIndex, sheetName); // parse cloneSheet ExcelUtils.parseSheet(context, wb, cloneSheet); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error("parse sheet error", e); } } } if (null != firstObj) { ExcelUtils.addValue(context, property, firstObj); // set sheet name int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); setSheetName(firstObj, wb, sheetIndex, sheetName); } } return new int[] { 0, -1, 0 }; }
From source file:org.bbreak.excella.reports.processor.ReportProcessor.java
License:Open Source License
/** * ???????/* w w w.ja v a 2 s . c o m*/ * * @param workbook * @param reportBook * @return ????? */ private Set<String> expandTemplate(Workbook workbook, ReportBook reportBook) { Set<String> delSheetNames = new TreeSet<String>(Collections.reverseOrder()); Set<String> useSheetNames = new HashSet<String>(); // ???? for (ReportSheet reportSheet : reportBook.getReportSheets()) { if (reportSheet != null) { if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) { // ????? int lastSheetIndex = workbook.getNumberOfSheets() - 1; workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex); useSheetNames.add(reportSheet.getTemplateName()); } else { int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName()); Sheet sheet = workbook.cloneSheet(tempIdx); ReportsUtil.copyPrintSetup(workbook, tempIdx, sheet); workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName()); delSheetNames.add(reportSheet.getTemplateName()); } } } // ? for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (!isOutputSheet(sheet, reportBook)) { delSheetNames.add(sheet.getSheetName()); } } delSheetNames.removeAll(useSheetNames); // ????? return delSheetNames; }
From source file:org.bbreak.excella.reports.tag.ImageParamParserTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.tag.ImageParamParser#parse(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell, java.lang.Object)} ???? *//*w ww . j a v a2 s .c om*/ @Test public void testParseSheetCellObject() { // ----------------------- // []? // ----------------------- Workbook workbook = getWorkbook(); Sheet sheet1 = workbook.getSheetAt(0); ImageParamParser parser = new ImageParamParser(); ReportsParserInfo reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet1, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ??? Set<Integer> delSheetIndexs = new TreeSet<Integer>(Collections.reverseOrder()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i != 0) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? Workbook expectedWorkbook = getExpectedWorkbook(); Sheet expectedSheet = expectedWorkbook.getSheet("Sheet1"); try { // ? ReportsTestUtil.checkSheet(expectedSheet, sheet1, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir"); File file = new File(tmpDirPath); if (!file.exists()) { file.mkdirs(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest1.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest1.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } // ----------------------- // [] // ----------------------- workbook = getWorkbook(); Sheet sheet2 = workbook.getSheetAt(1); parser = new ImageParamParser("$Image"); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createTestData("$Image")); try { parseSheet(parser, sheet2, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ??? delSheetIndexs.clear(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i != 1) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? expectedWorkbook = getExpectedWorkbook(); expectedSheet = expectedWorkbook.getSheet("Sheet2"); try { // ? ReportsTestUtil.checkSheet(expectedSheet, sheet2, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");; File file = new File(tmpDirPath); if (!file.exists()) { file.mkdir(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest2.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest2.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } String filename = this.getClass().getSimpleName(); if (version.equals("2007")) { filename = filename + "_err.xlsx"; } else if (version.equals("2003")) { filename = filename + "_err.xls"; } URL url = this.getClass().getResource(filename); String path = null; try { path = URLDecoder.decode(url.getFile(), "UTF-8"); } catch (UnsupportedEncodingException e) { Assert.fail(); } // ----------------------- // []? // ----------------------- workbook = getWorkbook(path); Sheet sheet3 = workbook.getSheetAt(2); parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet3, reportsParserInfo); fail("?????????"); } catch (ParseException e) { } // ----------------------- // []???? // ----------------------- workbook = getWorkbook(); Sheet sheet4 = workbook.getSheetAt(2); parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet4, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ??? expectedWorkbook = getExpectedWorkbook(); expectedSheet = expectedWorkbook.getSheet("Sheet4"); try { // ? ReportsTestUtil.checkSheet(expectedSheet, sheet4, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } // ---------------------------------------------------------------------- // []1 / ?? / ??? // ---------------------------------------------------------------------- workbook = getWorkbook(); Sheet sheet5 = workbook.getSheetAt(INDEX_OF_SHEET5); parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet5, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ??? delSheetIndexs.clear(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i != INDEX_OF_SHEET5) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? expectedWorkbook = getExpectedWorkbook(); expectedSheet = expectedWorkbook.getSheet("Sheet5"); try { // ? ReportsTestUtil.checkSheet(expectedSheet, sheet5, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir"); File file = new File(tmpDirPath); if (!file.exists()) { file.mkdirs(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest3.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest3.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } // ---------------------------------------------------------------------- // []1 / ?? / ?1 // ---------------------------------------------------------------------- workbook = getWorkbook(); Sheet sheet6 = workbook.cloneSheet(INDEX_OF_SHEET5); parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet6, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ??? delSheetIndexs.clear(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i != workbook.getNumberOfSheets() - 1) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? expectedWorkbook = getExpectedWorkbook(); expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5); try { // ? ReportsTestUtil.checkSheet(expectedSheet, sheet6, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir"); File file = new File(tmpDirPath); if (!file.exists()) { file.mkdirs(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest4.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest4.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } // ---------------------------------------------------------------------- // [] / ?? / ? // ---------------------------------------------------------------------- workbook = getWorkbook(); for (int i = 1; i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) { Sheet sheet = workbook.cloneSheet(INDEX_OF_SHEET5); parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } } // ??? delSheetIndexs.clear(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i < workbook.getNumberOfSheets() - PLURAL_COPY_FIRST_NUM_OF_SHEETS) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? expectedWorkbook = getExpectedWorkbook(); for (int i = 1; i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) { expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5); } try { int startOfTargetSheet = expectedWorkbook.getNumberOfSheets() - PLURAL_COPY_FIRST_NUM_OF_SHEETS; for (int i = 0; i < PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) { // ? ReportsTestUtil.checkSheet(expectedWorkbook.getSheetAt(startOfTargetSheet + i), workbook.getSheetAt(i), false); } } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir"); File file = new File(tmpDirPath); if (!file.exists()) { file.mkdirs(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest5.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest5.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } // ---------------------------------------------------------------------- // [] / ?? / (2)? // ---------------------------------------------------------------------- workbook = getWorkbook(); Sheet sheet = null; int totalNumOfCopies = PLURAL_COPY_FIRST_NUM_OF_SHEETS + PLURAL_COPY_SECOND_NUM_OF_SHEETS; for (int i = 1; i <= totalNumOfCopies; i++) { if (i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS) { sheet = workbook.cloneSheet(INDEX_OF_SHEET5); } else { sheet = workbook.cloneSheet(INDEX_OF_SHEET6); } parser = new ImageParamParser(); reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG)); try { parseSheet(parser, sheet, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } } // ??? delSheetIndexs.clear(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (i < workbook.getNumberOfSheets() - totalNumOfCopies) { delSheetIndexs.add(i); } } for (Integer i : delSheetIndexs) { workbook.removeSheetAt(i); } // ??? expectedWorkbook = getExpectedWorkbook(); for (int i = 1; i <= totalNumOfCopies; i++) { if (i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS) { expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5); } else { expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET6); } } try { int startOfTargetSheet = expectedWorkbook.getNumberOfSheets() - totalNumOfCopies; for (int i = 0; i < totalNumOfCopies; i++) { // ? ReportsTestUtil.checkSheet(expectedWorkbook.getSheetAt(startOfTargetSheet + i), workbook.getSheetAt(i), false); } } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { // ????????????? String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir"); File file = new File(tmpDirPath); if (!file.exists()) { file.mkdirs(); } try { String filepath = null; if (version.equals("2007")) { filepath = tmpDirPath + "ImageParamParserTest6.xlsx"; } else { filepath = tmpDirPath + "ImageParamParserTest6.xls"; } PoiUtil.writeBook(workbook, filepath); log.info("????????" + filepath); } catch (IOException e) { fail(e.toString()); } } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.SearchTermsSummaryXLS.java
License:Open Source License
/** * This method is used to generate the xls file for the ProjectLeading institutions. * //from ww w. j a v a2 s. c om * @param projectList is the list with the projects partner leaders * @return a byte array with the information provided for the xls file. */ public byte[] generateXLS(List<Map<String, Object>> projectList, List<Map<String, Object>> activityList, List<Map<String, Object>> deliverableList, String[] termsToSearch) { Workbook workbook = xls.initializeWorkbook(true); /***************** Gender Contribution Report Project Level ******************/ // Defining headers String[] headersProject = new String[] { "Project Id", "Title", "Summary", "Outcome statement", "Start date", "End date", "Flagship(s)", "Region(s)", "Lead institution", "Leader", "Coordinator", "Total budget W1/W2", "Total budget W3/Bilateral", "Total gender W1/W2", "Total gender W3/Bilateral" }; // Defining header types int[] headerTypesProject = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET }; // creating sheet Sheet[] sheets = new Sheet[3]; sheets[0] = workbook.getSheetAt(0); sheets[1] = workbook.cloneSheet(0); sheets[2] = workbook.cloneSheet(0); workbook.setSheetName(0, "Projects"); workbook.setSheetName(1, "Activities "); workbook.setSheetName(2, "Deliverables "); try { xls.initializeSheet(sheets[0], headerTypesProject); xls.writeHeaders(sheets[0], headersProject); this.addContent(projectList, sheets[0], 0, termsToSearch); // Set description xls.writeDescription(sheets[0], xls.getText("summaries.gender.summary.sheetone.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[0], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[0]); /***************** Gender Contribution Report Activity Level ******************/ // Defining headers String[] headersActivity = new String[] { "Project Id", "Project Title", "Activity Id", "Activity Title", "Description", "Start date", "End date", "Leader Institution", "Leader Person" }; // Defining header types int[] headerTypesActivity = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; xls.initializeSheet(sheets[1], headerTypesActivity); xls.writeHeaders(sheets[1], headersActivity); this.addContent(activityList, sheets[1], 1, termsToSearch); // Set description xls.writeDescription(sheets[1], xls.getText("summaries.gender.summary.sheettwo.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[1], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[1]); /***************** Gender Contribution Report Deliverable Level ******************/ // Defining headers String[] headersDeliverable = new String[] { "Project Id", "Project Title", "Deliverable Id", "Deliverable Title", "Deliverable Type", "Deliverable Sub-Type", "Next User", "Knowledge, attitude, skills and practice changes ", " Strategies", "Leader Institution", "Responsible Person" }; // Defining header types int[] headerTypesDeliverable = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; xls.initializeSheet(sheets[2], headerTypesDeliverable); xls.writeHeaders(sheets[2], headersDeliverable); this.addContent(deliverableList, sheets[2], 2, termsToSearch); // Set description xls.writeDescription(sheets[2], xls.getText("summaries.gender.summary.sheetthree.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[2], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[2]); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }