Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getRowNum.

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.carlos.projects.billing.ExcelToMySQLImporter.java

License:Open Source License

public Long importData(MultipartFile excelFile) throws ImportException {
    XSSFWorkbook workbook;/* w w w  .j a  v a2 s .  co  m*/
    File componentsFile;
    try {
        componentsFile = new File("components-" + new Date().getTime() + ".xlsx");
        excelFile.transferTo(componentsFile);
        workbook = new XSSFWorkbook(componentsFile.getAbsolutePath());
    } catch (IOException e) {
        throw new ImportException(messages.getProperty("import.error"), e);
    }
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    Iterator<Row> rowIterator = workbook.getSheetAt(workbook.getActiveSheetIndex()).iterator();
    Long numberOfImportedItems = 0L;
    log.info("Starting reading from file " + excelFile.getOriginalFilename()
            + " to import components to database");
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String familyCode = row.getCell(FAMILY_CODE).getStringCellValue().trim();
        //The first row of the excel file is the one with the titles
        if (row.getRowNum() != 0 && StringUtils.isNotBlank(familyCode)) {
            Family family = familyDAO.getById(Family.class, familyCode);
            boolean saveFamily = false;
            if (family == null) {
                family = createFamilyFromRow(row);
                saveFamily = true;
            }
            String componentCode = row.getCell(COMPONENT_CODE).getStringCellValue().trim();
            Component component = componentDAO.getById(Component.class, componentCode);
            boolean addComponent = false;
            if (component == null) {
                addComponent = true;
                component = createComponent(row, family);
                numberOfImportedItems += 1L;
            }
            if (saveFamily) {
                if (addComponent) {
                    family.addComponent(component);
                }
                familyDAO.save(family);
                log.info("Family " + family + " saved into the database");
            } else {
                componentDAO.save(component);
                log.info("Component " + component + " saved into the database");
            }
        }
    }
    closeAndDeleteTemporaryFiles(componentsFile);
    log.info("Components import to database finished");
    return numberOfImportedItems;
}

From source file:com.common.poi.excel.writer.excel.ExportExcel.java

License:Open Source License

/**
 * ?annotation.ExportField?//from  w ww  .ja  v  a2 s .c  o  m
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = Reflections.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }
                // If is dict, get dict label
                if (StringUtils.isNotBlank(ef.dictType())) {
                    //val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
                }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

From source file:com.common.report.util.html.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    //        printColumnHeads();

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;//ww  w . j a va2  s .  c o  m
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:com.company.et.service.XlsService.java

private static void createFullTasksCell(Workbook wb, Sheet s, Row row, int column) {
    Cell cell = row.createCell(column);//from ww  w .j a  va2  s .co m
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("C" + (row.getRowNum() + 1) + "+ F" + (row.getRowNum() + 1) + "+ I"
            + (row.getRowNum() + 1) + "+ L" + (row.getRowNum() + 1));
    setCellStyle(wb, s, cell);
}

From source file:com.company.et.service.XlsService.java

private static void createFullTasksActualCell(Workbook wb, Sheet s, Row row, int column) {
    Cell cell = row.createCell(column);//from  w w w  .  java2s.co m
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("B" + (row.getRowNum() + 1) + "+ E" + (row.getRowNum() + 1) + "+ H"
            + (row.getRowNum() + 1) + "+ K" + (row.getRowNum() + 1));
    setCellStyle(wb, s, cell);
}

From source file:com.company.et.service.XlsService.java

private static void createReserveCell(Workbook wb, Sheet s, Row row, int column) {
    Cell cell = row.createCell(column);//from   ww  w.  j a  v a2  s  .co  m
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula(CellReference.convertNumToColString(column - 2) + (row.getRowNum() + 1) + "-"
            + CellReference.convertNumToColString(column - 1) + (row.getRowNum() + 1));
    setCellStyle(wb, s, cell);
}

From source file:com.company.et.service.XlsService.java

private static void createFullTasksReserveCell(Workbook wb, Sheet s, Row row, int column) {
    Cell cell = row.createCell(column);/*ww w  . java 2 s.c o  m*/
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("D" + (row.getRowNum() + 1) + "+ G" + (row.getRowNum() + 1) + "+ J"
            + (row.getRowNum() + 1) + "+ M" + (row.getRowNum() + 1));
    setCellStyle(wb, s, cell);
}

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();/*from  www  .  j  a  v  a2  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.consensus.qa.framework.ExcelOperations.java

public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString)
        throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    String sheet = sheetName.toString();
    int rowIndex = -1;

    try {/*from w  w w.ja v a 2 s . c  o  m*/
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            if (Name[0].contains("IMEI")) {
                sheet = Name[1] + " " + Name[0];
            } else
                sheet = Name[1];
        }
        worksheet = workBook.getSheet(sheet);
        System.out.println(worksheet.getSheetName());
        Iterator<Row> rowIterator = worksheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String
                        .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) {
                    rowIndex = row.getRowNum();
                    break;
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equals(searchString))
                        rowIndex = row.getRowNum();
                    break;
                }
            }
        }
        if (rowIndex != -1) {
            boolean flag = false;
            Row searchStringRow = worksheet.getRow(rowIndex);
            System.out.println(searchStringRow.getPhysicalNumberOfCells());
            Iterator<Cell> statusChangeCell = searchStringRow.cellIterator();
            while (statusChangeCell.hasNext()) {
                Cell statusCell = statusChangeCell.next();
                if (statusCell.getStringCellValue().toLowerCase()
                        .equals(Status.INUSE.toString().toLowerCase())) {
                    statusCell.setCellValue(Status.USED.toString());
                    flag = true;
                }
            }
            if (flag == false)
                Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath
                        + ", Sheet: " + sheet);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

From source file:com.dotosoft.dotoquiz.tools.OldApp.java

License:Apache License

private boolean showColumnHeader(Object data, String sheetName) {
    if (APPLICATION_TYPE.SHOW_COLUMN_HEADER.toString().equals(settings.getApplicationType())) {
        log.info("Show column header for \"" + sheetName + "\"");
        if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) {
            Row rowData = (Row) data;
            Iterator<Cell> cellIterator = rowData.iterator();
            int columnCount = rowData.getRowNum();
            for (int i = 0; i < columnCount; i++) {
                Cell cell = rowData.getCell(i);
                log.info("\tColumn(" + i + "): " + cell.getStringCellValue());
            }/*from w ww.  j  ava2s  .c  o  m*/
        } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) {
            ListEntry listEntry = (ListEntry) data;
            int index = 0;
            for (String tag : listEntry.getCustomElements().getTags()) {
                log.info("\tColumn" + (index++) + ": " + tag);
            }
        }
        return true;
    }

    return false;
}