List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetIndex
int getSheetIndex(Sheet sheet);
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook; if (!outputFile.exists()) { outputFile.createNewFile();/*from w ww . ja v a 2s .c o m*/ outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
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); }// w w w. j a v a 2 s.c om 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 a v a 2 s .c om 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.compassplus.gui.MainForm.java
private void save(String file) throws IOException, InvalidFormatException { final String sfile = file; FileInputStream inp = new FileInputStream(sfile); final Workbook wb = WorkbookFactory.create(inp); inp.close();// ww w .ja v a 2 s . com { final ArrayList<String> sheets = new ArrayList<String>(0); for (int i = 0; i < wb.getNumberOfSheets(); i++) { if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) { sheets.add(wb.getSheetName(i)); } } if (sheets.size() == 0) { JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error", JOptionPane.ERROR_MESSAGE); return; } Integer rowsCountInt = null; Integer cellIndexInt = null; Integer rowIndexInt = null; String sheetIndexStr = null; Integer sheetIndexInt = null; Integer psRowsCountInt = null; Integer psCellIndexInt = null; Integer psRowIndexInt = null; String psSheetIndexStr = null; Integer psSheetIndexInt = null; Integer oRowsCountInt = null; Integer oCellIndexInt = null; Integer oRowIndexInt = null; String oSheetIndexStr = null; Integer oSheetIndexInt = null; Sheet settingsSheet = wb.getSheet("PCTSettings"); final List<RowStyle> rowStyles = new ArrayList<RowStyle>(); final List<RowStyle> psRowStyles = new ArrayList<RowStyle>(); final List<RowStyle> oRowStyles = new ArrayList<RowStyle>(); boolean sameCurrency = false; if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(1); Cell cellIndexCell = currentSettingsRow.getCell(2); Cell rowIndexCell = currentSettingsRow.getCell(3); Cell sheetIndexCell = currentSettingsRow.getCell(4); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { rowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); cellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); rowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); rowIndexInt++; cellIndexInt++; //sheetIndexInt = Integer.parseInt(sheetIndexCell.getStringCellValue()); sheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(sheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < rowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(sheetIndexStr).getRow(rowIndexInt - 1)); rowStyles.add(rowStyle); removeRow(wb.getSheet(sheetIndexStr), rowIndexInt - 1, wb); } } } catch (Exception ex) { } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(5); Cell cellIndexCell = currentSettingsRow.getCell(6); Cell rowIndexCell = currentSettingsRow.getCell(7); Cell sheetIndexCell = currentSettingsRow.getCell(8); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { psRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); psCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); psRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); psRowIndexInt++; psCellIndexInt++; psSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(psSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < psRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(psSheetIndexStr).getRow(psRowIndexInt - 1)); psRowStyles.add(rowStyle); removeRow(wb.getSheet(psSheetIndexStr), psRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(9); Cell cellIndexCell = currentSettingsRow.getCell(10); Cell rowIndexCell = currentSettingsRow.getCell(11); Cell sheetIndexCell = currentSettingsRow.getCell(12); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { oRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); oCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); oRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); oRowIndexInt++; oCellIndexInt++; oSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(oSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < oRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(oSheetIndexStr).getRow(oRowIndexInt - 1)); oRowStyles.add(rowStyle); removeRow(wb.getSheet(oSheetIndexStr), oRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } final boolean isPSQ = getCurrentProposalForm().getProposal().getPSQuote().enabled(); final boolean isOQ = !getCurrentProposalForm().getProposal().getConfig().isSalesSupport() && getCurrentProposalForm().getProposal().getOracleQuote().enabled() && getCurrentProposalForm().getProposal().getOracleQuote().getOracleLicenses().size() > 0; final JComboBox sheetIndexField = sheets.size() > 1 ? new JComboBox(sheets.toArray()) : null; if (sheetIndexField != null && sheetIndexStr != null) { for (String key : sheets) { if (key.equals(sheetIndexStr)) { sheetIndexField.setSelectedItem(key); break; } } } String selectedSheet1 = sheetIndexField != null ? (String) sheetIndexField.getSelectedItem() : null; final JComboBox psSheetIndexField = sheets.size() > 1 && isPSQ ? new JComboBox(sheets.toArray()) : null; if (psSheetIndexField != null && psSheetIndexStr != null) { for (String key : sheets) { if (key.equals(psSheetIndexStr)) { psSheetIndexField.setSelectedItem(key); break; } } } else if (psSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1)) { psSheetIndexField.setSelectedItem(key); break; } } } String selectedSheet2 = psSheetIndexField != null ? (String) psSheetIndexField.getSelectedItem() : null; final JComboBox oSheetIndexField = sheets.size() > 1 && isOQ ? new JComboBox(sheets.toArray()) : null; if (oSheetIndexField != null && oSheetIndexStr != null) { for (String key : sheets) { if (key.equals(oSheetIndexStr)) { oSheetIndexField.setSelectedItem(key); break; } } } else if (oSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1) && !key.equals(selectedSheet2)) { oSheetIndexField.setSelectedItem(key); break; } } } final JTextField rowIndexField = new JTextField(rowIndexInt != null ? rowIndexInt.toString() : "1"); final JTextField cellIndexField = new JTextField(cellIndexInt != null ? cellIndexInt.toString() : "1"); final JTextField psRowIndexField = new JTextField( psRowIndexInt != null ? psRowIndexInt.toString() : "1"); final JTextField psCellIndexField = new JTextField( psCellIndexInt != null ? psCellIndexInt.toString() : "1"); final JTextField oRowIndexField = new JTextField(oRowIndexInt != null ? oRowIndexInt.toString() : "1"); final JTextField oCellIndexField = new JTextField( oCellIndexInt != null ? oCellIndexInt.toString() : "1"); final JOptionPane optionPane = new JOptionPane(new JComponent[] { sheets.size() > 1 ? new JLabel("Products sheet") : null, sheetIndexField, new JLabel("Products row index"), rowIndexField, new JLabel("Products cell index"), cellIndexField, (sheets.size() > 1 && isPSQ) ? new JLabel("Prof. services sheet") : null, isPSQ ? psSheetIndexField : null, isPSQ ? new JLabel("Prof. services row index") : null, isPSQ ? psRowIndexField : null, isPSQ ? new JLabel("Prof. services cell index") : null, isPSQ ? psCellIndexField : null, (sheets.size() > 1 && isOQ) ? new JLabel("Oracle calculation sheet") : null, isOQ ? oSheetIndexField : null, isOQ ? new JLabel("Oracle calculation row index") : null, isOQ ? oRowIndexField : null, isOQ ? new JLabel("Oracle calculation cell index") : null, isOQ ? oCellIndexField : null }, JOptionPane.QUESTION_MESSAGE, JOptionPane.OK_CANCEL_OPTION); final JDialog dialog = new JDialog(getFrame(), "Export position", true); dialog.setResizable(false); dialog.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent we) { dialog.dispose(); } }); dialog.setContentPane(optionPane); dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE); final boolean fSameCurrency = false;//sameCurrency; optionPane.addPropertyChangeListener(new PropertyChangeListener() { public void propertyChange(PropertyChangeEvent e) { if (optionPane.getValue() != null) { String prop = e.getPropertyName(); if (dialog.isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY))) { try { if (optionPane.getValue() instanceof Integer) { int value = (Integer) optionPane.getValue(); if (value == JOptionPane.OK_OPTION) { Sheet s = null; Sheet psS = null; Sheet oS = null; if (sheetIndexField != null) { try { s = wb.getSheet((String) sheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { s = wb.getSheet(sheets.get(0)); } //PS if (psSheetIndexField != null) { try { psS = wb.getSheet((String) psSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { psS = wb.getSheet(sheets.get(0)); } //Oracle if (oSheetIndexField != null) { try { oS = wb.getSheet((String) oSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { oS = wb.getSheet(sheets.get(0)); } if (getCurrentProposalForm().getProposal().getPSQuote().enabled()) { if (s.equals(psS)) { JOptionPane.showMessageDialog(getRoot(), "Products and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (getCurrentProposalForm().getProposal().getPSQuote().enabled() && isOQ) { if (psS.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (isOQ) { if (s.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Products and oracle calculation can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } Integer rowIndex = null; try { rowIndex = Integer.parseInt(rowIndexField.getText()); rowIndex--; } catch (Exception exception) { } if (rowIndex == null || rowIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); rowIndexField.requestFocus(); rowIndexField.selectAll(); throw new Exception(); } //PS Integer psRowIndex = null; try { psRowIndex = Integer.parseInt(psRowIndexField.getText()); psRowIndex--; } catch (Exception exception) { } if ((psRowIndex == null || psRowIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psRowIndexField.requestFocus(); psRowIndexField.selectAll(); throw new Exception(); } //Oracle Integer oRowIndex = null; try { oRowIndex = Integer.parseInt(oRowIndexField.getText()); oRowIndex--; } catch (Exception exception) { } if ((oRowIndex == null || oRowIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oRowIndexField.requestFocus(); oRowIndexField.selectAll(); throw new Exception(); } Integer cellIndex = null; try { cellIndex = Integer.parseInt(cellIndexField.getText()); cellIndex--; } catch (Exception exception) { } if (cellIndex == null || cellIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); cellIndexField.requestFocus(); cellIndexField.selectAll(); throw new Exception(); } Integer psCellIndex = null; try { psCellIndex = Integer.parseInt(psCellIndexField.getText()); psCellIndex--; } catch (Exception exception) { } if ((psCellIndex == null || psCellIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psCellIndexField.requestFocus(); psCellIndexField.selectAll(); throw new Exception(); } Integer oCellIndex = null; try { oCellIndex = Integer.parseInt(oCellIndexField.getText()); oCellIndex--; } catch (Exception exception) { } if ((oCellIndex == null || oCellIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oCellIndexField.requestFocus(); oCellIndexField.selectAll(); throw new Exception(); } dialog.dispose(); try { int i = 0; Sheet settingsSheet = wb.getSheet("PCTSettings"); if (settingsSheet == null) { settingsSheet = wb.createSheet("PCTSettings"); } else { } if (rowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, s.getRow(rowIndex)); rowStyles.add(rowStyle); } //PS if (psRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, psS.getRow(psRowIndex)); psRowStyles.add(rowStyle); } //Oracle if (oRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, oS.getRow(oRowIndex)); oRowStyles.add(rowStyle); } String regPriceCol = CellReference.convertNumToColString(1 + cellIndex); String regPriceDiscount = CellReference .convertNumToColString(2 + cellIndex); String supPriceCol = CellReference.convertNumToColString(4 + cellIndex); String supPriceDiscount = CellReference .convertNumToColString(5 + cellIndex); int currentRowIndex = 0; for (Product p : getCurrentProposalForm().getProposal().getProducts() .values()) { if (s.getLastRowNum() >= rowIndex + i) { s.shiftRows(rowIndex + i, s.getLastRowNum(), 1); } RowStyle rowStyle = rowStyles .get((int) (currentRowIndex - rowStyles.size() * Math.floor(currentRowIndex / rowStyles.size()))); currentRowIndex++; Row r = s.createRow(rowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + cellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + cellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + cellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(p.getDescription()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + cellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + cellIndex, wb.createCellStyle()); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); if (!fSameCurrency) cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); c2.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getRegionPrice()); Cell c3 = r.createCell(2 + cellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + cellIndex, wb.createCellStyle()); cs3.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c3.setCellStyle(cs3); c3.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getDiscount()); Cell c4 = r.createCell(3 + cellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs4.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c4.setCellStyle(cs4); int rowIndexTotal = rowIndex + i + 1; c4.setCellFormula("CEILING(" + regPriceCol + rowIndexTotal + "*(1-" + regPriceDiscount + rowIndexTotal + "),1)"); Cell c5 = r.createCell(4 + cellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs5.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c5.setCellStyle(cs5); c5.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportPriceUndiscounted()); Cell c6 = r.createCell(5 + cellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + cellIndex, wb.createCellStyle()); cs6.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c6.setCellStyle(cs6); c6.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportDiscount()); Cell c7 = r.createCell(6 + cellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs7.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c7.setCellStyle(cs7); c7.setCellFormula("CEILING(" + supPriceCol + rowIndexTotal + "*(1-" + supPriceDiscount + rowIndexTotal + "),1)"); for (int y = 7 + cellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } PSQuote psq = getCurrentProposalForm().getProposal().getPSQuote(); //PS i = 0; currentRowIndex = 0; if (isPSQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + psCellIndex); String chargeCol = CellReference .convertNumToColString(2 + psCellIndex); String totalCol = CellReference .convertNumToColString(3 + psCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? getCurrentProposalForm().getProposal() .getRegion().getMDRate() * getCurrentProposalForm().getProposal() .getCurrency().getRate() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellStyle(cs2); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getMDDiscount() : 0); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite daily rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getOnsiteDailyCost() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite trip rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getTripPrice() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Professional Services Description:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("M/D:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Chargeable:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Onsite days:"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Onsite trips:"); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (ServicesGroup sg : getCurrentProposalForm().getProposal() .getConfig().getServicesRoot().getGroups()) { if (sg.notEmpty(psq)) { //vigrujaem gruppu { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; if (currentRowIndexFrom < 0) { currentRowIndexFrom = currentRowIndex; } Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(sg.getName()); c1.setCellStyle(cs1); if (psq.isExportable(sg.getKey())) { Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(sg.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(sg.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat().getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(sg.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(sg.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ } for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } if (!psq.isExportable(sg.getKey())) { for (Service srv : sg.getServices().values()) { if (srv.notEmpty(psq)) { //System.out.println("\n\nsg.getKey()="+sg.getKey()); //System.out.println("psq.isHidden(sg.getKey())="+psq.isHidden(sg.getKey())); if (psq.isExportable(srv.getKey())) { //vigrujaem service { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(srv.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(srv.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue( srv.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(srv.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(srv.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } else { for (com.compassplus.proposalModel.Service inst : psq .getServices().values()) { if (inst.getService().getGroupKey() .equals(sg.getKey()) && inst.getService().getKey() .equals(srv.getKey())) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows( psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles .size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow( psRowIndex + i); for (int y = rowStyle .getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell( 0 + psCellIndex); CellStyle cs1 = rowStyle .getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(inst.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell( 1 + psCellIndex); CellStyle cs2 = rowStyle .getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( inst.getTotalValue()); c2.setCellStyle(cs2); Cell c3 = r.createCell( 2 + psCellIndex); CellStyle cs3 = rowStyle .getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(inst.getCharge() ? inst.getTotalValue() : 0); c3.setCellStyle(cs3); Cell c4 = r.createCell( 3 + psCellIndex); CellStyle cs4 = rowStyle .getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS .getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(inst.getOnsiteTotalValue()); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(inst.getTripTotalValue()); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } i++; } } } } } } } } } currentRowIndexTo = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { sTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Services total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellFormula("SUM(" + rCol + (psRowIndex + currentRowIndexFrom) + ":" + rCol + (psRowIndex + currentRowIndexTo) + ")"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFrom) + ":" + totalCol + (psRowIndex + currentRowIndexTo) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } int currentRowIndexFromTC = -1; int currentRowIndexToTC = -1; if (psq.getTrainingCoursesCount() > 0) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training course:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("Cost per person:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Number of participants:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (TrainingCourse ttc : psq.getTrainingCourses().values()) { if (!ttc.getInclude()) { continue; } if (currentRowIndexFromTC < 0) { currentRowIndexFromTC = currentRowIndex + 1; } if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ttc.getTrainingCourse().getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ttc.getPricePerAttendee() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? ttc.getRegionalPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } currentRowIndexToTC = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { tTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training courses total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFromTC) + ":" + totalCol + (psRowIndex + currentRowIndexToTC) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total cost discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getPSDiscount() : 0); cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("(1-" + rCol + (psRowIndex + currentRowIndex) + ")*(" + totalCol + (psRowIndex + sTotal + 1) + "+" + totalCol + (psRowIndex + tTotal + 1) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } com.compassplus.proposalModel.OracleQuote oq = getCurrentProposalForm() .getProposal().getOracleQuote(); //Oracle int j = i; i = 0; currentRowIndex = 0; if (isOQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + oCellIndex); String licCol = CellReference.convertNumToColString(2 + oCellIndex); String supCol = CellReference.convertNumToColString(4 + oCellIndex); String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference .convertNumToColString(8 + oCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); boolean first = true; for (com.compassplus.proposalModel.OracleLicense ol : oq .getOracleLicenses().values()) { if (ol.isMemberOfAnotherBox()) { continue; } if (!first) { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } else { first = false; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ol.getProductKey()); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("License price"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("Options price"); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Support price"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Total price"); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setWrapText(true); c7.setCellValue("Discount"); c7.setCellStyle(cs7); Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); cs8.setWrapText(true); c8.setCellValue("CP price (total)"); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); cs9.setWrapText(true); c9.setCellValue("Customer price"); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); cs10.setWrapText(true); c10.setCellValue("Margin"); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /* * Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?ttc.getPricePerAttendee():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); * */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("License type"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getOracleLicense().getName()); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getLicensePrice() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOptionsPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOracleSupportPrice() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellFormula("SUM(" + licCol + (oRowIndex + currentRowIndex) + ":" + supCol + (oRowIndex + currentRowIndex) + ")"); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(ol.getDiscount()); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + rCol + (oRowIndex + currentRowIndex + 6) + "),1)"); //Customer Price if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); //Customer Price //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellFormula(custCol + (oRowIndex + currentRowIndex) + "-" + cpCol + (oRowIndex + currentRowIndex)); //Customer Price if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Number of cores"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCores()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Model"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getLicensingModel()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Coefficient"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCoefficient().getValue()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Included options"); c1.setCellStyle(cs1); String options = ""; for (String ss : ol.getProduct().getOracleOptions()) { OracleOption op = getCurrentProposalForm().getProposal() .getConfig().getOracleOptions().get(ss); options += ", " + op.getShortName(); } Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue( options.length() > 0 ? options.substring(1) : ""); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Support rate"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellValue(ol.getOracleLicense().getSupportRate()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Maximum discount"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellValue(ol.getCPDiscount() / 100d); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Shared"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); String sharedString = "Yes, with"; for (String ss : ol.getChildren()) { sharedString += " " + ss + ","; } c2.setCellValue( ol.isShared() ? sharedString.substring(0, sharedString.length() - 1) : "No"); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getLicenseTotal() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getOptionsTotal() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getSupportTotal() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getTotalTotal() : 0); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); //cs7.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(""); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getCPTotal() : 0); if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getCustomerTotal() : 0); //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getTotalMargin() : 0); if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } ArrayList<Row> rowsToRemove = new ArrayList(); ScriptEngineManager factory = new ScriptEngineManager(); ScriptEngine engine = factory.getEngineByName("JavaScript"); Bindings bindings = getBindings(getCurrentProposalForm().getProposal(), engine); int dec = 0; int psDec = 0; for (int si = 0; si < wb.getNumberOfSheets(); si++) { Sheet sis = wb.getSheetAt(si); Iterator<Row> riter = sis.rowIterator(); while (riter.hasNext()) { Row row = riter.next(); Iterator<Cell> citer = row.cellIterator(); while (citer.hasNext()) { Cell cell = citer.next(); if (analyzeCell(wb, sis, row, cell, engine, bindings)) { if (sis.getSheetName() == s.getSheetName() && row.getRowNum() < rowIndex) { dec++; } if (sis.getSheetName() == psS.getSheetName() && row.getRowNum() < psRowIndex) { psDec++; } if (!rowsToRemove.contains(row)) { rowsToRemove.add(row); } } } } for (Row ri : rowsToRemove) { removeRow(sis, ri.getRowNum(), wb); } rowsToRemove.clear(); } rowIndex = rowIndex - dec; psRowIndex = psRowIndex - psDec; Row settingsRow = settingsSheet.getRow(0); if (settingsRow == null) { settingsRow = settingsSheet.createRow(0); } CellUtil.createCell(settingsRow, 0, getCurrentProposalForm().getProposal().toString()); CellUtil.createCell(settingsRow, 1, new Integer( getCurrentProposalForm().getProposal().getProducts().size()) .toString()); CellUtil.createCell(settingsRow, 2, cellIndex.toString()); CellUtil.createCell(settingsRow, 3, rowIndex.toString()); CellUtil.createCell(settingsRow, 4, s.getSheetName()); //PS if (isPSQ) { CellUtil.createCell(settingsRow, 5, new Integer(j).toString()); CellUtil.createCell(settingsRow, 6, psCellIndex.toString()); CellUtil.createCell(settingsRow, 7, psRowIndex.toString()); CellUtil.createCell(settingsRow, 8, psS.getSheetName()); } //Oracle if (isOQ) { CellUtil.createCell(settingsRow, 9, new Integer(i).toString()); CellUtil.createCell(settingsRow, 10, oCellIndex.toString()); CellUtil.createCell(settingsRow, 11, oRowIndex.toString()); CellUtil.createCell(settingsRow, 12, oS.getSheetName()); } wb.setSheetHidden(wb.getSheetIndex(settingsSheet), true); OutputStream out = new FileOutputStream(sfile); wb.write(out); out.close(); getCurrentProposalForm().setChanged(false); JOptionPane.showMessageDialog(getRoot(), "Proposal successfully exported", "Result", JOptionPane.INFORMATION_MESSAGE); } catch (Exception exception) { exception.printStackTrace(); JOptionPane.showMessageDialog(getRoot(), "Proposal can't be exported", "Error", JOptionPane.ERROR_MESSAGE); } } else if (value == JOptionPane.CANCEL_OPTION) { dialog.dispose(); } } } catch (Exception exception) { optionPane.setValue(null); } } } } }); dialog.pack(); dialog.setLocationRelativeTo(getRoot()); dialog.setVisible(true); } }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ??//from ww w . j a va 2 s . co m * @param config * @param sheet * @param columnIndex * @param valueSet */ protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex, Set<String> valueSet) { Workbook workbook = sheet.getWorkbook(); Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME); if (codeSheet == null) { log.debug("?Sheet?Sheet"); codeSheet = workbook.createSheet(DICCODE_SHEET_NAME); } int codeIndex = config.columnName() - 'A'; log.debug("codeIndex{}", codeIndex); if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) { log.debug("????"); int i = 0; for (String dic : valueSet) { Row row = codeSheet.getRow(i); if (row == null) row = codeSheet.createRow(i); Cell cell = row.createCell(codeIndex); cell.setCellValue(dic); i++; } } else { log.debug("????"); } Name name = workbook.getName(config.columnName() + ""); if (name == null || name.isDeleted()) { log.debug("?Name?Name"); name = workbook.createName(); name.setNameName(config.columnName() + ""); } name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$" + valueSet.size()); DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName()); CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN); setValidationTip(validation, config); sheet.addValidationData(validation); log.debug("??"); }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam) throws ExceptionWrapper { try {//from w w w . j a v a 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 w w.j av a 2 s . 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:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
/** * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat} * //from w w w . j av a2 s . c o m * @param cell * @param excell * @param style * @param font */ private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle, Workbook workbook, Font font, Font invisibleFont) { if (excell != null) { // [1] format cell formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont); // [2] set enum if (!excell.isConsiderEnum()) { if (StringUtils.isNotBlank(excell.getDisplayText())) { cell.setCellValue(excell.getDisplayText()); } if (!excell.isMultiSelect() && excell.isNumberValidation()) { addNumberValidation(cell); } } else { String[] list = (String[]) excell.getRestriction().getEnumValues() .toArray(new String[excell.getRestriction().getEnumValues().size()]); SXSSFSheet sheet = (SXSSFSheet) cell.getSheet(); DataValidationHelper dvHelper = sheet.getDataValidationHelper(); DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper .createExplicitListConstraint(list); CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()); DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.setSuppressDropDownArrow(true); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); try { if (isValidEnumList(list)) { sheet.addValidationData(dataValidation); } else { Sheet hidden = null; String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText()); Workbook wBook = cell.getSheet().getWorkbook(); if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) { hidden = wBook.getSheet(hiddenName); } else { hidden = wBook.createSheet(hiddenName); for (int i = 0, length = list.length; i < length; i++) { String name = list[i]; Row row = hidden.createRow(i); Cell cell1 = row.createCell(0); cell1.setCellValue(name); } Name namedCell = hidden.getWorkbook().getName(hiddenName); namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName(); namedCell.setNameName(hiddenName); namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length); } dvConstraint = (XSSFDataValidationConstraint) dvHelper .createFormulaListConstraint(hiddenName); dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); cell.getSheet().addValidationData(dataValidation); wBook.setSheetHidden(wBook.getSheetIndex(hidden), true); } } catch (Exception e) { String msg = "Excel creation failed while building cell: " + excell.getDisplayText(); throw new IllegalStateException(msg, e); } // cell.setCellValue(excelConfig.getDropDownMsg()); } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Adds a new sheet to the given Spreadsheet and Workbook. * * @param spreadsheet/*from w ww. ja v a 2s .c om*/ * Target Spreadsheet * @param workbook * Target Workbook * @param sheetName * Name of the new sheet * @param rows * Row count for the new sheet * @param columns * Column count for the new sheet */ static void addNewSheet(final Spreadsheet spreadsheet, final Workbook workbook, final String sheetName, int rows, int columns) { final Sheet sheet; if (sheetName == null) { sheet = createNewSheet(workbook); } else { sheet = workbook.createSheet(sheetName); } int sheetIndex = workbook.getSheetIndex(sheet); workbook.setActiveSheet(sheetIndex); spreadsheet.reloadActiveSheetData(); spreadsheet.reloadActiveSheetStyles(); final SpreadsheetState state = spreadsheet.getState(); int[] verticalScrollPositions = Arrays.copyOf(state.verticalScrollPositions, state.sheetNames.length); int[] horizontalScrollPositions = Arrays.copyOf(state.horizontalScrollPositions, state.sheetNames.length); state.verticalScrollPositions = verticalScrollPositions; state.horizontalScrollPositions = horizontalScrollPositions; generateNewSpreadsheet(spreadsheet, sheet, rows, columns); }
From source file:de.enerko.reports2.engine.ReportEngineTest.java
License:Apache License
@Test public void shouldHandleSheetManipulation() throws IOException { final ReportEngine reportEngine = new ReportEngine(connection); final Report report = reportEngine.createReport("pck_enerko_reports2_test.f_sheet_manipulation", this.getClass().getResource("/sheet_manipulation.xls").openStream()); File outFile = File.createTempFile(ReportEngineTest.class.getSimpleName() + "-", ".xls"); report.write(new BufferedOutputStream(new FileOutputStream(outFile))); final Workbook workbook = new HSSFWorkbook(new FileInputStream(outFile)); Assert.assertTrue(workbook.isSheetHidden(workbook.getSheetIndex("hide_me"))); Assert.assertEquals(-1, workbook.getSheetIndex("delete_me")); ReportEngine.logger.log(Level.INFO, String.format("Report written to %s", outFile.getAbsolutePath())); }