List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:com.cms.utils.ExportExcell.java
private void buildContentSheet(Sheet sheetTmp, ExportExcellCelltype customCellType, List<?> lstData, List<FormatExcell> formatExcell) { int i = 1;/*www. j av a2 s.c om*/ for (Object itemData : lstData) { Row rowData = sheetTmp.createRow(i); Cell cellStt = rowData.createCell(0); cellStt.setCellValue(i); cellStt.setCellStyle(customCellType.cellTypeAlignCenter(false)); i++; createCell(itemData, rowData, formatExcell); } }
From source file:com.company.et.service.XlsService.java
public static void generateFile(List<TreeItem<Task>> root, List<ArrayList<ArrayList<Double>>> waitingParts, ObservableList<Professor> professors) throws FileNotFoundException, IOException { FileOutputStream out = new FileOutputStream(filename); Workbook wb = new HSSFWorkbook(); for (int k = 0; k < professors.size(); k++) { Sheet s = wb.createSheet(professors.get(k).getFio()); Row rowFirst = s.createRow(0); createConstStringCells(wb, s, "??", rowFirst, 0, 0, 1, 0, 0); createConstStringCells(wb, s, "?? ", rowFirst, 1, 0, 0, 1, 3); createConstStringCells(wb, s, "? ", rowFirst, 4, 0, 0, 4, 6); createConstStringCells(wb, s, "?? ", rowFirst, 7, 0, 0, 7, 9); createConstStringCells(wb, s, "?? ", rowFirst, 10, 0, 0, 10, 12); createConstStringCells(wb, s, "?? ", rowFirst, 13, 0, 0, 13, 15); Row rowSecond = s.createRow(1);/*w ww . j ava 2 s .c om*/ for (int i = 0, j = 1; i < 5; i++, j += 3) { createConstStringCells(wb, s, "", rowSecond, j, 0, 0, 0, 0); createConstStringCells(wb, s, "", rowSecond, j + 1, 0, 0, 0, 0); createConstStringCells(wb, s, "", rowSecond, j + 2, 0, 0, 0, 0); } for (int i = 1; i < 16; i++) { Row row = s.createRow(i + 1); for (int j = 0; j < root.get(k).getChildren().size(); j++) { createCellOfDouble(wb, s, row, j * 3 + 2, root.get(k).getChildren().get(j).getValue().getCapacities().get(i)); createCellOfDouble(wb, s, row, j * 3 + 1, waitingParts.get(k).get(j).get(i)); createReserveCell(wb, s, row, (j + 1) * 3); } createMonthCell(wb, s, row, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString()); createFullTasksActualCell(wb, s, row, root.get(k).getChildren().size() * 3 + 1); createFullTasksCell(wb, s, row, root.get(k).getChildren().size() * 3 + 2); createFullTasksReserveCell(wb, s, row, (root.get(k).getChildren().size() + 1) * 3); } } FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator(); //all year report Sheet s = wb.createSheet("?"); Row rowFirst = s.createRow(0); Row rowSecond = s.createRow(1); createConstStringCells(wb, s, "/", rowFirst, 0, 0, 1, 0, 0); createConstStringCells(wb, s, "", rowFirst, 1, 0, 1, 1, 1); createConstStringCells(wb, s, "?", rowFirst, 2, 0, 1, 2, 2); createConstStringCells(wb, s, ". .", rowFirst, 3, 0, 1, 3, 3); for (int i = 1; i < 16; i++) { createConstStringCells(wb, s, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString(), rowFirst, (i * 3) + 1, 0, 0, (i * 3) + 1, (i * 3) + 3); createConstStringCells(wb, s, "", rowSecond, (i * 3) + 1, 1, 1, (i * 3) + 1, (i * 3) + 1); createConstStringCells(wb, s, "", rowSecond, (i * 3) + 2, 1, 1, (i * 3) + 2, (i * 3) + 2); createConstStringCells(wb, s, "", rowSecond, (i * 3) + 3, 1, 1, (i * 3) + 3, (i * 3) + 3); } for (int i = 0; i < professors.size(); i++) { Row row = s.createRow(i + 2); createConstStringCells(wb, s, Integer.toString(i + 1), row, 0, i + 2, i + 2, 0, 0); createConstStringCells(wb, s, professors.get(i).getFio(), row, 1, i + 2, i + 2, 1, 1); createConstStringCells(wb, s, professors.get(i).getRate().toString(), row, 3, i + 2, i + 2, 3, 3); for (int j = 1; j < 16; j++) { createConstStringCells(wb, s, formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(13)) .formatAsString(), row, (j * 3) + 1, i + 2, i + 2, (j * 3) + 1, (j * 3) + 1); createConstStringCells(wb, s, formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(14)) .formatAsString(), row, (j * 3) + 2, i + 2, i + 2, (j * 3) + 2, (j * 3) + 2); createConstStringCells(wb, s, formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(15)) .formatAsString(), row, (j * 3) + 3, i + 2, i + 2, (j * 3) + 3, (j * 3) + 3); } } wb.write(out); out.close(); }
From source file:com.company.et.service.XlsService.java
public static void createReportForMonth(Workbook wb, Sheet s, int numOfMonth, Professor professor, ArrayList<ArrayList<Double>> waitingParts, TreeItem<Task> root) { Row row = s.createRow(0); createConstStringCells(wb, s, " ?", row, 0, 0, 0, 0, 0); createConstStringCells(wb, s, "", row, 1, 0, 0, 1, 1); createConstStringCells(wb, s, "", row, 2, 0, 0, 2, 2); createConstStringCells(wb, s, DoubleCapacities.getDoubleCapacitiesByIndex(numOfMonth).toString(), row, 3, 0, 0, 3, 3);// w ww .j a v a 2 s.co m int rowCounter = 1; for (int i = 0; i < root.getChildren().size(); i++) { row = s.createRow(rowCounter); createConstStringCells(wb, s, (i + 1) + ". " + root.getChildren().get(i).getValue().getProfessorsWork(), row, 0, rowCounter, rowCounter, 0, 0); createConstStringCells(wb, s, root.getChildren().get(i).getValue().getPeriod(), row, 1, rowCounter, rowCounter, 1, 1); createCellOfDouble(wb, s, row, 2, root.getChildren().get(i).getValue().getCapacities().get(0)); createCellOfDouble(wb, s, row, 3, waitingParts.get(i).get(numOfMonth)); rowCounter++; for (TreeItem<Task> treeItem : root.getChildren().get(i).getChildren()) { Task task = treeItem.getValue(); row = s.createRow(rowCounter); createConstStringCells(wb, s, task.getProfessorsWork(), row, 0, rowCounter, rowCounter, 0, 0); createConstStringCells(wb, s, task.getPeriod(), row, 1, rowCounter, rowCounter, 1, 1); createCellOfDouble(wb, s, row, 2, task.getCapacities().get(0)); createCellOfDouble(wb, s, row, 3, task.getCapacities().get(numOfMonth)); rowCounter++; } } row = s.createRow(rowCounter); createConstStringCells(wb, s, "(1+2+3+4)", row, 0, rowCounter, rowCounter, 0, 0); createConstStringCells(wb, s, "", row, 1, rowCounter, rowCounter, 1, 1); createCellOfDouble(wb, s, row, 2, root.getValue().getCapacities().get(0)); createCellOfDouble(wb, s, row, 3, root.getValue().getCapacities().get(numOfMonth)); }
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();/*w w w . j a va 2 s .c o m*/ { 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.compomics.pepshell.controllers.dataexport.ExcelExport.java
License:Apache License
private static void addHeadersToProteinsSheet(Sheet sheet, boolean quant) { Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Uniport accession"); headerRow.createCell(1).setCellValue("peptide for protein"); if (quant) {/*from w w w.j a v a2s.co m*/ headerRow.createCell(2).setCellValue("peptideRatio"); } }
From source file:com.compomics.pepshell.controllers.dataexport.ExcelExport.java
License:Apache License
private static void addHeadersToPDBSheet(Sheet PDBSheet) { Row PDBHeaderRow = PDBSheet.createRow(0); PDBHeaderRow.createCell(0).setCellValue("Uniprot Accession"); PDBHeaderRow.createCell(1).setCellValue("PDB Accessions"); }
From source file:com.compomics.pepshell.controllers.dataexport.ExcelExport.java
License:Apache License
public static void exportProteinToExcelWorkBook(JList listOfProteins, Workbook workBook) throws ArrayIndexOutOfBoundsException { int row = 1;//from w w w. j av a 2s . co m PepshellProtein pepshellProteinToExport; int listWalkCounter = 0; Sheet proteinSheet = workBook.createSheet("proteins"); Sheet PDBSheet = workBook.createSheet("PDB"); addHeadersToProteinsSheet(proteinSheet, ((PepshellProtein) listOfProteins.getModel().getElementAt(0)) .getPeptideGroups().get(0) instanceof QuantedPeptideGroup); addHeadersToPDBSheet(PDBSheet); while (listWalkCounter < listOfProteins.getModel().getSize()) { pepshellProteinToExport = (PepshellProtein) listOfProteins.getModel().getElementAt(listWalkCounter); proteinSheet.createRow(row); excelProteinInfoExportOfProtein(pepshellProteinToExport, proteinSheet); excelPDBExportOfProtein(pepshellProteinToExport, PDBSheet.createRow(listWalkCounter + 1)); listWalkCounter++; } }
From source file:com.compomics.pepshell.controllers.dataexport.ExcelExport.java
License:Apache License
private static void excelProteinInfoExportOfProtein(PepshellProtein pepshellProteinToExport, Sheet proteinSheet) { int column = 0; Row proteinRow = proteinSheet.createRow(proteinSheet.getLastRowNum() + 1); proteinRow.createCell(column).setCellValue(pepshellProteinToExport.getVisibleAccession()); column++;//from w ww . j a v a 2 s . com for (PeptideGroup peptideGroup : pepshellProteinToExport.getPeptideGroups()) { proteinRow.createCell(column).setCellValue(peptideGroup.getRepresentativePeptide().getSequence()); if (peptideGroup instanceof QuantedPeptideGroup) { proteinRow.createCell(column + 1) .setCellValue(Integer.toString(((QuantedPeptideGroup) peptideGroup).getRatio())); } proteinRow = proteinSheet.createRow(proteinSheet.getLastRowNum() + 1); } }
From source file:com.controller.DownloadController.java
private void downloadCourseSpreadsheet(HttpServletRequest request, HttpServletResponse response) { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Course Table"); Row row = sheet.createRow(0); row.createCell(2).setCellValue("Currently Course Enrollment Table"); row = sheet.createRow(2);//from w ww . j ava 2 s . co m row.createCell(0).setCellValue("Number"); row.createCell(1).setCellValue("Name"); row.createCell(2).setCellValue("Room"); row.createCell(3).setCellValue("Instructor"); row.createCell(4).setCellValue("Days&Time"); row.createCell(5).setCellValue("Credit"); row.createCell(6).setCellValue("Registration Time"); HttpSession session = request.getSession(); List<CourseRegistration> enrolled = (List<CourseRegistration>) session.getAttribute("enrolled"); int i = 4; for (CourseRegistration cr : enrolled) { row = sheet.createRow(i); row.createCell(0).setCellValue(cr.getCourse().getCourseNum()); row.createCell(1).setCellValue(cr.getCourse().getName()); row.createCell(2).setCellValue(cr.getCourse().getRoom()); row.createCell(3).setCellValue(cr.getCourse().getInstructor()); row.createCell(4).setCellValue(cr.getCourse().getDt()); row.createCell(5).setCellValue(cr.getCourse().getCredit() - '0'); row.createCell(6).setCellValue(cr.getCourseRegisterationDateDefaultFormat()); i++; } response.setHeader("content-disposition", "attachment; filename=CourseEnrollment.xls"); response.setHeader("cache-control", "no-cache"); try (OutputStream out = response.getOutputStream()) { workbook.write(out); } catch (IOException ex) { System.out.println(ex.getStackTrace()); } }
From source file:com.crimelab.service.ChemistryServiceImpl.java
@Override public Workbook createMonthlyReport(HttpSession session, String month) { Workbook wb = null;/* ww w .ja v a2 s.c o m*/ try { InputStream inp = session.getServletContext() .getResourceAsStream("/WEB-INF/templates/DrugMonthlyReport.xls"); wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); CellStyle cs1 = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle bl = wb.createCellStyle(); CellStyle br = wb.createCellStyle(); CellStyle bt = wb.createCellStyle(); CellStyle bb = wb.createCellStyle(); CellStyle stf = wb.createCellStyle(); cs1.setWrapText(true); cs2.setAlignment(ALIGN_CENTER); bt.setBorderTop(BORDER_THIN); bb.setBorderBottom(BORDER_THIN); bl.setBorderLeft(BORDER_THIN); br.setBorderRight(BORDER_THIN); stf.setShrinkToFit(true); Row intro1 = sheet.createRow(7); Cell in1 = intro1.createCell(0);//.setCellValue("DRUG INVETORY COVERED PERIOD JANUARY-DECEMBER CY-" +month.split("-")[0]); in1.setCellValue("DRUG INVENTORY COVERED PERIOD JANUARY-DECEMBER CY-" + month.split("-")[0]); in1.setCellStyle(cs1); in1.setCellStyle(cs2); Row intro2 = sheet.createRow(9); Cell in2 = intro2.createCell(0);//.setCellValue("SUMMARY OF SEIZED/SURRENDERED/RECOVERED OF DRUG EVIDENCES FROM NEGATION OPERATIONS FROM LAW ENFORCEMENTS, PHARMACEUTICAL COMPANIES AND SIMILAR ESTABLISHMENTS FOR THE MONTH OF "+month); in2.setCellValue( "SUMMARY OF SEIZED/SURRENDERED/RECOVERED OF DRUG EVIDENCES FROM NEGATION OPERATIONS FROM LAW ENFORCEMENTS, PHARMACEUTICAL COMPANIES AND SIMILAR ESTABLISHMENTS FOR THE MONTH OF " + month); in2.setCellStyle(cs1); in2.setCellStyle(cs2); in2.setCellStyle(stf); int ctr = 12; //initial Row row = sheet.createRow(ctr); month = month.split("-")[1]; //System.out.println("GAC " + chemistryDAO.getAllChemistry(month).isEmpty()); for (Chemistry chemistry : chemistryDAO.getAllChemistry(month)) { //System.out.println("Test " + chemistry.getTimeDateReceived()); Cell cell0 = row.createCell(0); cell0.setCellValue(chemistry.getTimeDateReceived()); cell0.setCellStyle(bt); cell0.setCellStyle(bb); cell0.setCellStyle(bl); cell0.setCellStyle(br); Cell cell1 = row.createCell(1);//.setCellValue(chemistry.getReportNo()); cell1.setCellValue(chemistry.getReportNo()); cell1.setCellStyle(bt); cell1.setCellStyle(bb); cell1.setCellStyle(bl); cell1.setCellStyle(br); Cell cell2 = row.createCell(2);//.setCellValue(chemistry.getRequestingParty()); cell2.setCellValue(chemistry.getRequestingParty()); cell2.setCellStyle(bt); cell2.setCellStyle(bb); cell2.setCellStyle(bl); cell2.setCellStyle(br); Cell cell3 = row.createCell(3);//.setCellValue(chemistry.getDescriptionOfEvidence()); cell3.setCellValue(chemistry.getDescriptionOfEvidence()); cell3.setCellStyle(bt); cell3.setCellStyle(bb); cell3.setCellStyle(bl); cell3.setCellStyle(br); Cell cell4 = row.createCell(4);//.setCellValue(chemistry.getSpecimenWeight()); cell4.setCellValue(chemistry.getSpecimenWeight()); cell4.setCellStyle(bt); cell4.setCellStyle(bb); cell4.setCellStyle(bl); cell4.setCellStyle(br); Cell cell5 = row.createCell(5);//.setCellValue(chemistry.getCustody()); cell5.setCellValue(chemistry.getCustody()); cell5.setCellStyle(bt); cell5.setCellStyle(bb); cell5.setCellStyle(bl); cell5.setCellStyle(br); Cell cell6 = row.createCell(6);//.setCellValue(chemistry.getSuspects()); cell6.setCellValue(chemistry.getSuspects()); cell6.setCellStyle(bt); cell6.setCellStyle(bb); cell6.setCellStyle(bl); cell6.setCellStyle(br); Cell cell7 = row.createCell(7);//.setCellValue(chemistry.getTypeOfOperation()); cell7.setCellValue(chemistry.getTypeOfOperation()); cell7.setCellStyle(bt); cell7.setCellStyle(bb); cell7.setCellStyle(bl); cell7.setCellStyle(br); Cell cell8 = row.createCell(8);//.setCellValue(chemistry.getPlaceOfOperation()); cell8.setCellValue(chemistry.getPlaceOfOperation()); cell8.setCellStyle(bt); cell8.setCellStyle(bb); cell8.setCellStyle(bl); cell8.setCellStyle(br); row = sheet.createRow(ctr += 1); } } catch (Exception e) { e.printStackTrace(); } return wb; }