Example usage for org.apache.poi.ss.usermodel Sheet rowIterator

List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator

Introduction

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

Prototype

Iterator<Row> rowIterator();

Source Link

Document

Returns an iterator of the physical rows

Usage

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

public void printStyles() {
    ensureOut();//from ww w  .  j a  v a  2  s.c  o  m

    // First, copy the base css
    BufferedReader in = null;
    try {
        in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css")));
        String line;
        while ((line = in.readLine()) != null) {
            out.format("%s%n", line);
        }
    } catch (IOException e) {
        throw new IllegalStateException("Reading standard css", e);
    } finally {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                //noinspection ThrowFromFinallyBlock
                throw new IllegalStateException("Reading standard css", e);
            }
        }
    }

    // now add css for each used style
    Set<CellStyle> seen = new HashSet<CellStyle>();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            for (Cell cell : row) {
                CellStyle style = cell.getCellStyle();
                if (!seen.contains(style)) {
                    printStyle(style);
                    seen.add(style);
                }
            }
        }
    }
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds)
        return;// w  w  w  .  j a  v a2 s.c o  m

    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
        Row row = iter.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    gotBounds = true;
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();/*from   w  w w  .j  a  v  a 2  s  .  com*/

    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;
            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.abixen.platform.service.businessintelligence.multivisualisation.application.service.file.reader.ExcelReaderService.java

License:Open Source License

private DataFileDto parseWorkbook(final Sheet sheet, final FileParserMessage<DataFileColumn> msg,
        final Boolean readFirstColumnAsColumnName) {
    final DataFileDto dataFileDto = new DataFileDto();
    dataFileDto.setRowTypes(buildRowTypes(sheet.getRow(readFirstColumnAsColumnName ? 1 : 0)));
    final Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        dataFileDto.addRow(readRowAsRowInMemory(rowIterator.next(), dataFileDto.getRowTypes()));
    }/*from w w  w .j  av a  2  s  .c om*/
    return dataFileDto;
}

From source file:com.aurel.track.lucene.util.poi.XLSTextStripper.java

License:Open Source License

public XLSTextStripper(FileInputStream fis, String fileExtension) {
    try {/*from   w w  w  .  j ava 2  s  . c  o  m*/
        StringBuffer sb = new StringBuffer();
        Workbook workbook = null;
        if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) {
            workbook = new HSSFWorkbook(fis);
        } else {
            if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) {
                workbook = new XSSFWorkbook(fis);
            }
        }
        if (workbook != null) {
            int numOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String cellStringValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                            boolean booleanValue = cell.getBooleanCellValue();
                            cellStringValue = Boolean.toString(booleanValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            double doubleValue = cell.getNumericCellValue();
                            cellStringValue = Double.toString(doubleValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            cellStringValue = cell.getStringCellValue();
                        }
                        if (cellStringValue != null) {
                            sb.append(cellStringValue);
                            sb.append("\t");
                        }
                    }
                    sb.append("\n");
                }
            }
        }
        _text = sb.toString();
    } catch (Exception e) {
        LOGGER.error(ExceptionUtils.getStackTrace(e));
    }
}

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();/* ww w . j ava  2  s .c  o  m*/

        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;
            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.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 w w w. j a  v  a  2  s  .co  m*/
    {
        final ArrayList<String> sheets = new ArrayList<String>(0);
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) {
                sheets.add(wb.getSheetName(i));
            }
        }
        if (sheets.size() == 0) {
            JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }

        Integer rowsCountInt = null;
        Integer cellIndexInt = null;
        Integer rowIndexInt = null;
        String sheetIndexStr = null;
        Integer sheetIndexInt = null;

        Integer psRowsCountInt = null;
        Integer psCellIndexInt = null;
        Integer psRowIndexInt = null;
        String psSheetIndexStr = null;
        Integer psSheetIndexInt = null;

        Integer oRowsCountInt = null;
        Integer oCellIndexInt = null;
        Integer oRowIndexInt = null;
        String oSheetIndexStr = null;
        Integer oSheetIndexInt = null;

        Sheet settingsSheet = wb.getSheet("PCTSettings");
        final List<RowStyle> rowStyles = new ArrayList<RowStyle>();
        final List<RowStyle> psRowStyles = new ArrayList<RowStyle>();
        final List<RowStyle> oRowStyles = new ArrayList<RowStyle>();
        boolean sameCurrency = false;
        if (settingsSheet != null) {
            Row currentSettingsRow = settingsSheet.getRow(0);
            if (currentSettingsRow != null) {
                Cell oldProposalCell = currentSettingsRow.getCell(0);
                Cell rowsCountCell = currentSettingsRow.getCell(1);
                Cell cellIndexCell = currentSettingsRow.getCell(2);
                Cell rowIndexCell = currentSettingsRow.getCell(3);
                Cell sheetIndexCell = currentSettingsRow.getCell(4);
                try {
                    Proposal oldProposal = new Proposal(config);
                    String proposalString = oldProposalCell.getStringCellValue();
                    if (proposalString != null) {
                        oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString));
                        sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName()
                                .equals(oldProposal.getCurrency().getName());
                    }
                } catch (Exception e) {

                }

                if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null
                        && cellIndexCell != null) {
                    try {
                        rowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue());
                        cellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue());
                        rowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue());
                        rowIndexInt++;
                        cellIndexInt++;
                        //sheetIndexInt = Integer.parseInt(sheetIndexCell.getStringCellValue());
                        sheetIndexStr = sheetIndexCell.getStringCellValue();
                        if (wb.getSheet(sheetIndexStr) != null) {
                            //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName();
                            for (int j = 0; j < rowsCountInt; j++) {
                                RowStyle rowStyle = new RowStyle();
                                rowStyle.init(wb, wb.getSheet(sheetIndexStr).getRow(rowIndexInt - 1));
                                rowStyles.add(rowStyle);
                                removeRow(wb.getSheet(sheetIndexStr), rowIndexInt - 1, wb);
                            }
                        }
                    } catch (Exception ex) {
                    }
                }
            } else {
            }
        }

        if (settingsSheet != null) {
            Row currentSettingsRow = settingsSheet.getRow(0);
            if (currentSettingsRow != null) {
                Cell oldProposalCell = currentSettingsRow.getCell(0);
                Cell rowsCountCell = currentSettingsRow.getCell(5);
                Cell cellIndexCell = currentSettingsRow.getCell(6);
                Cell rowIndexCell = currentSettingsRow.getCell(7);
                Cell sheetIndexCell = currentSettingsRow.getCell(8);
                try {
                    Proposal oldProposal = new Proposal(config);
                    String proposalString = oldProposalCell.getStringCellValue();
                    if (proposalString != null) {
                        oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString));
                        sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName()
                                .equals(oldProposal.getCurrency().getName());
                    }
                } catch (Exception e) {

                }

                if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null
                        && cellIndexCell != null) {
                    try {
                        psRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue());
                        psCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue());
                        psRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue());
                        psRowIndexInt++;
                        psCellIndexInt++;
                        psSheetIndexStr = sheetIndexCell.getStringCellValue();
                        if (wb.getSheet(psSheetIndexStr) != null) {
                            //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName();
                            for (int j = 0; j < psRowsCountInt; j++) {
                                RowStyle rowStyle = new RowStyle();
                                rowStyle.init(wb, wb.getSheet(psSheetIndexStr).getRow(psRowIndexInt - 1));
                                psRowStyles.add(rowStyle);
                                removeRow(wb.getSheet(psSheetIndexStr), psRowIndexInt - 1, wb, true);
                            }
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            } else {
            }
        }

        if (settingsSheet != null) {
            Row currentSettingsRow = settingsSheet.getRow(0);
            if (currentSettingsRow != null) {
                Cell oldProposalCell = currentSettingsRow.getCell(0);
                Cell rowsCountCell = currentSettingsRow.getCell(9);
                Cell cellIndexCell = currentSettingsRow.getCell(10);
                Cell rowIndexCell = currentSettingsRow.getCell(11);
                Cell sheetIndexCell = currentSettingsRow.getCell(12);
                try {
                    Proposal oldProposal = new Proposal(config);
                    String proposalString = oldProposalCell.getStringCellValue();
                    if (proposalString != null) {
                        oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString));
                        sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName()
                                .equals(oldProposal.getCurrency().getName());
                    }
                } catch (Exception e) {

                }

                if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null
                        && cellIndexCell != null) {
                    try {
                        oRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue());
                        oCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue());
                        oRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue());
                        oRowIndexInt++;
                        oCellIndexInt++;
                        oSheetIndexStr = sheetIndexCell.getStringCellValue();
                        if (wb.getSheet(oSheetIndexStr) != null) {
                            //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName();
                            for (int j = 0; j < oRowsCountInt; j++) {
                                RowStyle rowStyle = new RowStyle();
                                rowStyle.init(wb, wb.getSheet(oSheetIndexStr).getRow(oRowIndexInt - 1));
                                oRowStyles.add(rowStyle);
                                removeRow(wb.getSheet(oSheetIndexStr), oRowIndexInt - 1, wb, true);
                            }
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            } else {
            }
        }

        final boolean isPSQ = getCurrentProposalForm().getProposal().getPSQuote().enabled();
        final boolean isOQ = !getCurrentProposalForm().getProposal().getConfig().isSalesSupport()
                && getCurrentProposalForm().getProposal().getOracleQuote().enabled()
                && getCurrentProposalForm().getProposal().getOracleQuote().getOracleLicenses().size() > 0;

        final JComboBox sheetIndexField = sheets.size() > 1 ? new JComboBox(sheets.toArray()) : null;
        if (sheetIndexField != null && sheetIndexStr != null) {
            for (String key : sheets) {
                if (key.equals(sheetIndexStr)) {
                    sheetIndexField.setSelectedItem(key);
                    break;
                }
            }
        }
        String selectedSheet1 = sheetIndexField != null ? (String) sheetIndexField.getSelectedItem() : null;

        final JComboBox psSheetIndexField = sheets.size() > 1 && isPSQ ? new JComboBox(sheets.toArray()) : null;
        if (psSheetIndexField != null && psSheetIndexStr != null) {
            for (String key : sheets) {
                if (key.equals(psSheetIndexStr)) {
                    psSheetIndexField.setSelectedItem(key);
                    break;
                }
            }
        } else if (psSheetIndexField != null) {
            for (String key : sheets) {
                if (!key.equals(selectedSheet1)) {
                    psSheetIndexField.setSelectedItem(key);
                    break;
                }
            }
        }
        String selectedSheet2 = psSheetIndexField != null ? (String) psSheetIndexField.getSelectedItem() : null;

        final JComboBox oSheetIndexField = sheets.size() > 1 && isOQ ? new JComboBox(sheets.toArray()) : null;
        if (oSheetIndexField != null && oSheetIndexStr != null) {
            for (String key : sheets) {
                if (key.equals(oSheetIndexStr)) {
                    oSheetIndexField.setSelectedItem(key);
                    break;
                }
            }
        } else if (oSheetIndexField != null) {
            for (String key : sheets) {
                if (!key.equals(selectedSheet1) && !key.equals(selectedSheet2)) {
                    oSheetIndexField.setSelectedItem(key);
                    break;
                }
            }
        }

        final JTextField rowIndexField = new JTextField(rowIndexInt != null ? rowIndexInt.toString() : "1");
        final JTextField cellIndexField = new JTextField(cellIndexInt != null ? cellIndexInt.toString() : "1");

        final JTextField psRowIndexField = new JTextField(
                psRowIndexInt != null ? psRowIndexInt.toString() : "1");
        final JTextField psCellIndexField = new JTextField(
                psCellIndexInt != null ? psCellIndexInt.toString() : "1");
        final JTextField oRowIndexField = new JTextField(oRowIndexInt != null ? oRowIndexInt.toString() : "1");
        final JTextField oCellIndexField = new JTextField(
                oCellIndexInt != null ? oCellIndexInt.toString() : "1");
        final JOptionPane optionPane = new JOptionPane(new JComponent[] {
                sheets.size() > 1 ? new JLabel("Products sheet") : null, sheetIndexField,
                new JLabel("Products row index"), rowIndexField, new JLabel("Products cell index"),
                cellIndexField, (sheets.size() > 1 && isPSQ) ? new JLabel("Prof. services sheet") : null,
                isPSQ ? psSheetIndexField : null, isPSQ ? new JLabel("Prof. services row index") : null,
                isPSQ ? psRowIndexField : null, isPSQ ? new JLabel("Prof. services cell index") : null,
                isPSQ ? psCellIndexField : null,

                (sheets.size() > 1 && isOQ) ? new JLabel("Oracle calculation sheet") : null,
                isOQ ? oSheetIndexField : null, isOQ ? new JLabel("Oracle calculation row index") : null,
                isOQ ? oRowIndexField : null, isOQ ? new JLabel("Oracle calculation cell index") : null,
                isOQ ? oCellIndexField : null }, JOptionPane.QUESTION_MESSAGE, JOptionPane.OK_CANCEL_OPTION);

        final JDialog dialog = new JDialog(getFrame(), "Export position", true);
        dialog.setResizable(false);
        dialog.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent we) {
                dialog.dispose();
            }
        });
        dialog.setContentPane(optionPane);
        dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE);

        final boolean fSameCurrency = false;//sameCurrency;
        optionPane.addPropertyChangeListener(new PropertyChangeListener() {
            public void propertyChange(PropertyChangeEvent e) {
                if (optionPane.getValue() != null) {
                    String prop = e.getPropertyName();
                    if (dialog.isVisible() && (e.getSource() == optionPane)
                            && (prop.equals(JOptionPane.VALUE_PROPERTY))) {
                        try {
                            if (optionPane.getValue() instanceof Integer) {
                                int value = (Integer) optionPane.getValue();
                                if (value == JOptionPane.OK_OPTION) {
                                    Sheet s = null;
                                    Sheet psS = null;
                                    Sheet oS = null;
                                    if (sheetIndexField != null) {
                                        try {
                                            s = wb.getSheet((String) sheetIndexField.getSelectedItem());
                                        } catch (Exception exception) {
                                        }
                                    } else {
                                        s = wb.getSheet(sheets.get(0));
                                    }

                                    //PS
                                    if (psSheetIndexField != null) {
                                        try {
                                            psS = wb.getSheet((String) psSheetIndexField.getSelectedItem());
                                        } catch (Exception exception) {
                                        }
                                    } else {
                                        psS = wb.getSheet(sheets.get(0));
                                    }

                                    //Oracle
                                    if (oSheetIndexField != null) {
                                        try {
                                            oS = wb.getSheet((String) oSheetIndexField.getSelectedItem());
                                        } catch (Exception exception) {
                                        }
                                    } else {
                                        oS = wb.getSheet(sheets.get(0));
                                    }

                                    if (getCurrentProposalForm().getProposal().getPSQuote().enabled()) {
                                        if (s.equals(psS)) {
                                            JOptionPane.showMessageDialog(getRoot(),
                                                    "Products and prof. services can't be exported on the same sheet",
                                                    "Error", JOptionPane.ERROR_MESSAGE);
                                            throw new Exception();
                                        }
                                    }

                                    if (getCurrentProposalForm().getProposal().getPSQuote().enabled() && isOQ) {
                                        if (psS.equals(oS)) {
                                            JOptionPane.showMessageDialog(getRoot(),
                                                    "Oracle calculation and prof. services can't be exported on the same sheet",
                                                    "Error", JOptionPane.ERROR_MESSAGE);
                                            throw new Exception();
                                        }
                                    }

                                    if (isOQ) {
                                        if (s.equals(oS)) {
                                            JOptionPane.showMessageDialog(getRoot(),
                                                    "Products and oracle calculation can't be exported on the same sheet",
                                                    "Error", JOptionPane.ERROR_MESSAGE);
                                            throw new Exception();
                                        }
                                    }

                                    Integer rowIndex = null;
                                    try {
                                        rowIndex = Integer.parseInt(rowIndexField.getText());
                                        rowIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if (rowIndex == null || rowIndex < 0) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Products row index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        rowIndexField.requestFocus();
                                        rowIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    //PS
                                    Integer psRowIndex = null;
                                    try {
                                        psRowIndex = Integer.parseInt(psRowIndexField.getText());
                                        psRowIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if ((psRowIndex == null || psRowIndex < 0) && isPSQ) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Prof. services row index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        psRowIndexField.requestFocus();
                                        psRowIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    //Oracle
                                    Integer oRowIndex = null;
                                    try {
                                        oRowIndex = Integer.parseInt(oRowIndexField.getText());
                                        oRowIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if ((oRowIndex == null || oRowIndex < 0) && isOQ) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Oracle calculation row index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        oRowIndexField.requestFocus();
                                        oRowIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    Integer cellIndex = null;
                                    try {
                                        cellIndex = Integer.parseInt(cellIndexField.getText());
                                        cellIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if (cellIndex == null || cellIndex < 0) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Products cell index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        cellIndexField.requestFocus();
                                        cellIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    Integer psCellIndex = null;
                                    try {
                                        psCellIndex = Integer.parseInt(psCellIndexField.getText());
                                        psCellIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if ((psCellIndex == null || psCellIndex < 0) && isPSQ) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Prof. services cell index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        psCellIndexField.requestFocus();
                                        psCellIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    Integer oCellIndex = null;
                                    try {
                                        oCellIndex = Integer.parseInt(oCellIndexField.getText());
                                        oCellIndex--;
                                    } catch (Exception exception) {
                                    }
                                    if ((oCellIndex == null || oCellIndex < 0) && isOQ) {
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Oracle calculation cell index is not valid", "Error",
                                                JOptionPane.ERROR_MESSAGE);
                                        oCellIndexField.requestFocus();
                                        oCellIndexField.selectAll();
                                        throw new Exception();
                                    }

                                    dialog.dispose();
                                    try {
                                        int i = 0;
                                        Sheet settingsSheet = wb.getSheet("PCTSettings");
                                        if (settingsSheet == null) {
                                            settingsSheet = wb.createSheet("PCTSettings");
                                        } else {

                                        }
                                        if (rowStyles.size() == 0) {
                                            RowStyle rowStyle = new RowStyle();
                                            rowStyle.init(wb, s.getRow(rowIndex));
                                            rowStyles.add(rowStyle);
                                        }

                                        //PS
                                        if (psRowStyles.size() == 0) {
                                            RowStyle rowStyle = new RowStyle();
                                            rowStyle.init(wb, psS.getRow(psRowIndex));
                                            psRowStyles.add(rowStyle);
                                        }

                                        //Oracle
                                        if (oRowStyles.size() == 0) {
                                            RowStyle rowStyle = new RowStyle();
                                            rowStyle.init(wb, oS.getRow(oRowIndex));
                                            oRowStyles.add(rowStyle);
                                        }

                                        String regPriceCol = CellReference.convertNumToColString(1 + cellIndex);
                                        String regPriceDiscount = CellReference
                                                .convertNumToColString(2 + cellIndex);
                                        String supPriceCol = CellReference.convertNumToColString(4 + cellIndex);
                                        String supPriceDiscount = CellReference
                                                .convertNumToColString(5 + cellIndex);
                                        int currentRowIndex = 0;
                                        for (Product p : getCurrentProposalForm().getProposal().getProducts()
                                                .values()) {
                                            if (s.getLastRowNum() >= rowIndex + i) {
                                                s.shiftRows(rowIndex + i, s.getLastRowNum(), 1);
                                            }
                                            RowStyle rowStyle = rowStyles
                                                    .get((int) (currentRowIndex - rowStyles.size()
                                                            * Math.floor(currentRowIndex / rowStyles.size())));
                                            currentRowIndex++;
                                            Row r = s.createRow(rowIndex + i);
                                            for (int y = rowStyle.getFirst(); y < 0 + cellIndex; y++) {
                                                CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                if (tcs != null) {
                                                    Cell tc = r.createCell(y);
                                                    tc.setCellStyle(tcs);
                                                }
                                            }

                                            Cell c1 = r.createCell(0 + cellIndex);
                                            CellStyle cs1 = rowStyle.getCellStyle(0 + cellIndex,
                                                    wb.createCellStyle());
                                            cs1.setWrapText(true);
                                            c1.setCellValue(p.getDescription());
                                            c1.setCellStyle(cs1);

                                            Cell c2 = r.createCell(1 + cellIndex);
                                            CellStyle cs2 = rowStyle.getCellStyle(1 + cellIndex,
                                                    wb.createCellStyle());
                                            String format = (getCurrentProposalForm().getProposal()
                                                    .getCurrency().getSymbol() != null
                                                            ? "\"" + getCurrentProposalForm().getProposal()
                                                                    .getCurrency().getSymbol() + "\" "
                                                            : "")
                                                    + "#,##0"
                                                    + (getCurrentProposalForm().getProposal().getCurrency()
                                                            .getSymbol() == null ? " \""
                                                                    + getCurrentProposalForm().getProposal()
                                                                            .getCurrency().getName()
                                                                    + "\"" : "");
                                            if (!fSameCurrency)
                                                cs2.setDataFormat(
                                                        s.getWorkbook().createDataFormat().getFormat(format));
                                            c2.setCellStyle(cs2);
                                            c2.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0
                                                    : p.getRegionPrice());

                                            Cell c3 = r.createCell(2 + cellIndex);
                                            CellStyle cs3 = rowStyle.getCellStyle(2 + cellIndex,
                                                    wb.createCellStyle());
                                            cs3.setDataFormat(
                                                    s.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                                            c3.setCellStyle(cs3);
                                            c3.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0
                                                    : p.getDiscount());

                                            Cell c4 = r.createCell(3 + cellIndex);
                                            CellStyle cs4 = rowStyle.getCellStyle(3 + cellIndex,
                                                    wb.createCellStyle());
                                            if (!fSameCurrency)
                                                cs4.setDataFormat(
                                                        s.getWorkbook().createDataFormat().getFormat(format));
                                            c4.setCellStyle(cs4);
                                            int rowIndexTotal = rowIndex + i + 1;
                                            c4.setCellFormula("CEILING(" + regPriceCol + rowIndexTotal + "*(1-"
                                                    + regPriceDiscount + rowIndexTotal + "),1)");

                                            Cell c5 = r.createCell(4 + cellIndex);
                                            CellStyle cs5 = rowStyle.getCellStyle(4 + cellIndex,
                                                    wb.createCellStyle());
                                            if (!fSameCurrency)
                                                cs5.setDataFormat(
                                                        s.getWorkbook().createDataFormat().getFormat(format));
                                            c5.setCellStyle(cs5);
                                            c5.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0
                                                    : p.getSupportPriceUndiscounted());

                                            Cell c6 = r.createCell(5 + cellIndex);
                                            CellStyle cs6 = rowStyle.getCellStyle(5 + cellIndex,
                                                    wb.createCellStyle());
                                            cs6.setDataFormat(
                                                    s.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                                            c6.setCellStyle(cs6);
                                            c6.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0
                                                    : p.getSupportDiscount());

                                            Cell c7 = r.createCell(6 + cellIndex);
                                            CellStyle cs7 = rowStyle.getCellStyle(6 + cellIndex,
                                                    wb.createCellStyle());
                                            if (!fSameCurrency)
                                                cs7.setDataFormat(
                                                        s.getWorkbook().createDataFormat().getFormat(format));
                                            c7.setCellStyle(cs7);
                                            c7.setCellFormula("CEILING(" + supPriceCol + rowIndexTotal + "*(1-"
                                                    + supPriceDiscount + rowIndexTotal + "),1)");

                                            for (int y = 7 + cellIndex; y <= rowStyle.getLast(); y++) {
                                                CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                if (tcs != null) {
                                                    Cell tc = r.createCell(y);
                                                    tc.setCellStyle(tcs);
                                                }
                                            }
                                            i++;
                                        }

                                        PSQuote psq = getCurrentProposalForm().getProposal().getPSQuote();
                                        //PS
                                        i = 0;
                                        currentRowIndex = 0;
                                        if (isPSQ) {
                                            int sTotal = -1;
                                            int tTotal = -1;

                                            int currentRowIndexFrom = -1;
                                            int currentRowIndexTo = -1;
                                            String rCol = CellReference.convertNumToColString(1 + psCellIndex);
                                            String chargeCol = CellReference
                                                    .convertNumToColString(2 + psCellIndex);
                                            String totalCol = CellReference
                                                    .convertNumToColString(3 + psCellIndex);
                                            String format = (getCurrentProposalForm().getProposal()
                                                    .getCurrency().getSymbol() != null
                                                            ? "\"" + getCurrentProposalForm().getProposal()
                                                                    .getCurrency().getSymbol() + "\" "
                                                            : "")
                                                    + "#,##0"
                                                    + (getCurrentProposalForm().getProposal().getCurrency()
                                                            .getSymbol() == null ? " \""
                                                                    + getCurrentProposalForm().getProposal()
                                                                            .getCurrency().getName()
                                                                    + "\"" : "");
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Man-day rate:");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs2.setWrapText(true);
                                                c2.setCellValue(!getCurrentProposalForm().getProposal()
                                                        .getConfig().isSalesSupport()
                                                                ? getCurrentProposalForm().getProposal()
                                                                        .getRegion().getMDRate()
                                                                        * getCurrentProposalForm().getProposal()
                                                                                .getCurrency().getRate()
                                                                : 0);
                                                if (!fSameCurrency)
                                                    cs2.setDataFormat(psS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c2.setCellStyle(cs2);

                                                for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }

                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Man-day discount:");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs2.setDataFormat(psS.getWorkbook().createDataFormat()
                                                        .getFormat("0%;-0%"));
                                                c2.setCellStyle(cs2);
                                                c2.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? psq.getMDDiscount() : 0);

                                                for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }

                                            /*{
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);
                                                    
                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Onsite daily rate:");
                                                c1.setCellStyle(cs1);
                                                    
                                                    
                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle());
                                                cs2.setWrapText(true);
                                                c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getOnsiteDailyCost() * getCurrentProposalForm().getProposal().getCurrency().getRate():0);
                                                if (!fSameCurrency)
                                                    cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format));
                                                c2.setCellStyle(cs2);
                                                    
                                                for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                    
                                                i++;
                                            }
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Onsite trip rate:");
                                                c1.setCellStyle(cs1);
                                                    
                                                    
                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle());
                                                cs2.setWrapText(true);
                                                c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getTripPrice() * getCurrentProposalForm().getProposal().getCurrency().getRate():0);
                                                if (!fSameCurrency)
                                                    cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format));
                                                c2.setCellStyle(cs2);
                                                    
                                                for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                    
                                                i++;
                                            }*/
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }

                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Professional Services Description:");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs2.setWrapText(true);
                                                c2.setCellValue("M/D:");
                                                c2.setCellStyle(cs2);

                                                Cell c3 = r.createCell(2 + psCellIndex);
                                                CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs3.setWrapText(true);
                                                c3.setCellValue("Chargeable:");
                                                c3.setCellStyle(cs3);

                                                Cell c4 = r.createCell(3 + psCellIndex);
                                                CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs4.setWrapText(true);
                                                c4.setCellValue("$$:");
                                                c4.setCellStyle(cs4);

                                                /*Cell c5 = r.createCell(4 + psCellIndex);
                                                CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle());
                                                cs5.setWrapText(true);
                                                c5.setCellValue("Onsite days:");
                                                c5.setCellStyle(cs5);
                                                        
                                                Cell c6 = r.createCell(5 + psCellIndex);
                                                CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle());
                                                cs6.setWrapText(true);
                                                c6.setCellValue("Onsite trips:");
                                                c6.setCellStyle(cs6);*/

                                                for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }

                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }

                                            for (ServicesGroup sg : getCurrentProposalForm().getProposal()
                                                    .getConfig().getServicesRoot().getGroups()) {
                                                if (sg.notEmpty(psq)) {
                                                    //vigrujaem gruppu
                                                    {
                                                        if (psS.getLastRowNum() >= psRowIndex + i) {
                                                            psS.shiftRows(psRowIndex + i, psS.getLastRowNum(),
                                                                    1);
                                                        }
                                                        RowStyle rowStyle = psRowStyles
                                                                .get((int) (currentRowIndex - psRowStyles.size()
                                                                        * Math.floor(currentRowIndex
                                                                                / psRowStyles.size())));
                                                        currentRowIndex++;
                                                        if (currentRowIndexFrom < 0) {
                                                            currentRowIndexFrom = currentRowIndex;
                                                        }
                                                        Row r = psS.createRow(psRowIndex + i);
                                                        for (int y = rowStyle.getFirst(); y < 0
                                                                + psCellIndex; y++) {
                                                            CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                            if (tcs != null) {
                                                                Cell tc = r.createCell(y);
                                                                tc.setCellStyle(tcs);
                                                            }
                                                        }
                                                        Cell c1 = r.createCell(0 + psCellIndex);
                                                        CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                                wb.createCellStyle());
                                                        cs1.setWrapText(true);
                                                        c1.setCellValue(sg.getName());
                                                        c1.setCellStyle(cs1);
                                                        if (psq.isExportable(sg.getKey())) {
                                                            Cell c2 = r.createCell(1 + psCellIndex);
                                                            CellStyle cs2 = rowStyle.getCellStyle(
                                                                    1 + psCellIndex, wb.createCellStyle());
                                                            c2.setCellValue(sg.getTotalMD(psq));
                                                            c2.setCellStyle(cs2);

                                                            Cell c3 = r.createCell(2 + psCellIndex);
                                                            CellStyle cs3 = rowStyle.getCellStyle(
                                                                    2 + psCellIndex, wb.createCellStyle());
                                                            c3.setCellValue(sg.getChargeableMD(psq));
                                                            c3.setCellStyle(cs3);

                                                            Cell c4 = r.createCell(3 + psCellIndex);
                                                            CellStyle cs4 = rowStyle.getCellStyle(
                                                                    3 + psCellIndex, wb.createCellStyle());
                                                            c4.setCellFormula(rCol + (psRowIndex + 1) + "*"
                                                                    + chargeCol + (psRowIndex + currentRowIndex)
                                                                    + "*(1-" + rCol + (psRowIndex + 2) + ")");
                                                            if (!fSameCurrency)
                                                                cs4.setDataFormat(psS.getWorkbook()
                                                                        .createDataFormat().getFormat(format));
                                                            c4.setCellStyle(cs4);

                                                            /*Cell c5 = r.createCell(4 + psCellIndex);
                                                            CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle());
                                                            c5.setCellValue(sg.getTotalOnsiteMD(psq));
                                                            c5.setCellStyle(cs5);
                                                                    
                                                            Cell c6 = r.createCell(5 + psCellIndex);
                                                            CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle());
                                                            c6.setCellValue(sg.getTotalOnsiteTrips(psq));
                                                            c6.setCellStyle(cs6);*/
                                                        }

                                                        for (int y = 4 + psCellIndex; y <= rowStyle
                                                                .getLast(); y++) {
                                                            CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                            if (tcs != null) {
                                                                Cell tc = r.createCell(y);
                                                                tc.setCellStyle(tcs);
                                                            }
                                                        }
                                                        i++;
                                                    }
                                                    if (!psq.isExportable(sg.getKey())) {
                                                        for (Service srv : sg.getServices().values()) {
                                                            if (srv.notEmpty(psq)) {
                                                                //System.out.println("\n\nsg.getKey()="+sg.getKey());
                                                                //System.out.println("psq.isHidden(sg.getKey())="+psq.isHidden(sg.getKey()));
                                                                if (psq.isExportable(srv.getKey())) {
                                                                    //vigrujaem service
                                                                    {
                                                                        if (psS.getLastRowNum() >= psRowIndex
                                                                                + i) {
                                                                            psS.shiftRows(psRowIndex + i,
                                                                                    psS.getLastRowNum(), 1);
                                                                        }
                                                                        RowStyle rowStyle = psRowStyles
                                                                                .get((int) (currentRowIndex
                                                                                        - psRowStyles.size()
                                                                                                * Math.floor(
                                                                                                        currentRowIndex
                                                                                                                / psRowStyles
                                                                                                                        .size())));
                                                                        currentRowIndex++;
                                                                        Row r = psS.createRow(psRowIndex + i);
                                                                        for (int y = rowStyle.getFirst(); y < 0
                                                                                + psCellIndex; y++) {
                                                                            CellStyle tcs = rowStyle
                                                                                    .getCellStyle(y, null);
                                                                            if (tcs != null) {
                                                                                Cell tc = r.createCell(y);
                                                                                tc.setCellStyle(tcs);
                                                                            }
                                                                        }
                                                                        Cell c1 = r.createCell(0 + psCellIndex);
                                                                        CellStyle cs1 = rowStyle.getCellStyle(
                                                                                0 + psCellIndex,
                                                                                wb.createCellStyle());
                                                                        cs1.setWrapText(true);
                                                                        c1.setCellValue(srv.getName());
                                                                        c1.setCellStyle(cs1);
                                                                        Cell c2 = r.createCell(1 + psCellIndex);
                                                                        CellStyle cs2 = rowStyle.getCellStyle(
                                                                                1 + psCellIndex,
                                                                                wb.createCellStyle());
                                                                        c2.setCellValue(srv.getTotalMD(psq));
                                                                        c2.setCellStyle(cs2);

                                                                        Cell c3 = r.createCell(2 + psCellIndex);
                                                                        CellStyle cs3 = rowStyle.getCellStyle(
                                                                                2 + psCellIndex,
                                                                                wb.createCellStyle());
                                                                        c3.setCellValue(
                                                                                srv.getChargeableMD(psq));
                                                                        c3.setCellStyle(cs3);

                                                                        Cell c4 = r.createCell(3 + psCellIndex);
                                                                        CellStyle cs4 = rowStyle.getCellStyle(
                                                                                3 + psCellIndex,
                                                                                wb.createCellStyle());
                                                                        c4.setCellFormula(rCol
                                                                                + (psRowIndex + 1) + "*"
                                                                                + chargeCol
                                                                                + (psRowIndex + currentRowIndex)
                                                                                + "*(1-" + rCol
                                                                                + (psRowIndex + 2) + ")");
                                                                        if (!fSameCurrency)
                                                                            cs4.setDataFormat(psS.getWorkbook()
                                                                                    .createDataFormat()
                                                                                    .getFormat(format));
                                                                        c4.setCellStyle(cs4);

                                                                        /*Cell c5 = r.createCell(4 + psCellIndex);
                                                                        CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle());
                                                                        c5.setCellValue(srv.getTotalOnsiteMD(psq));
                                                                        c5.setCellStyle(cs5);
                                                                                
                                                                        Cell c6 = r.createCell(5 + psCellIndex);
                                                                        CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle());
                                                                        c6.setCellValue(srv.getTotalOnsiteTrips(psq));
                                                                        c6.setCellStyle(cs6);*/

                                                                        for (int y = 4
                                                                                + psCellIndex; y <= rowStyle
                                                                                        .getLast(); y++) {
                                                                            CellStyle tcs = rowStyle
                                                                                    .getCellStyle(y, null);
                                                                            if (tcs != null) {
                                                                                Cell tc = r.createCell(y);
                                                                                tc.setCellStyle(tcs);
                                                                            }
                                                                        }
                                                                        i++;
                                                                    }
                                                                } else {
                                                                    for (com.compassplus.proposalModel.Service inst : psq
                                                                            .getServices().values()) {
                                                                        if (inst.getService().getGroupKey()
                                                                                .equals(sg.getKey())
                                                                                && inst.getService().getKey()
                                                                                        .equals(srv.getKey())) {
                                                                            {
                                                                                if (psS.getLastRowNum() >= psRowIndex
                                                                                        + i) {
                                                                                    psS.shiftRows(
                                                                                            psRowIndex + i,
                                                                                            psS.getLastRowNum(),
                                                                                            1);
                                                                                }
                                                                                RowStyle rowStyle = psRowStyles
                                                                                        .get((int) (currentRowIndex
                                                                                                - psRowStyles
                                                                                                        .size()
                                                                                                        * Math.floor(
                                                                                                                currentRowIndex
                                                                                                                        / psRowStyles
                                                                                                                                .size())));
                                                                                currentRowIndex++;
                                                                                Row r = psS.createRow(
                                                                                        psRowIndex + i);
                                                                                for (int y = rowStyle
                                                                                        .getFirst(); y < 0
                                                                                                + psCellIndex; y++) {
                                                                                    CellStyle tcs = rowStyle
                                                                                            .getCellStyle(y,
                                                                                                    null);
                                                                                    if (tcs != null) {
                                                                                        Cell tc = r
                                                                                                .createCell(y);
                                                                                        tc.setCellStyle(tcs);
                                                                                    }
                                                                                }
                                                                                Cell c1 = r.createCell(
                                                                                        0 + psCellIndex);
                                                                                CellStyle cs1 = rowStyle
                                                                                        .getCellStyle(
                                                                                                0 + psCellIndex,
                                                                                                wb.createCellStyle());
                                                                                cs1.setWrapText(true);
                                                                                c1.setCellValue(inst.getName());
                                                                                c1.setCellStyle(cs1);
                                                                                Cell c2 = r.createCell(
                                                                                        1 + psCellIndex);
                                                                                CellStyle cs2 = rowStyle
                                                                                        .getCellStyle(
                                                                                                1 + psCellIndex,
                                                                                                wb.createCellStyle());
                                                                                c2.setCellValue(
                                                                                        inst.getTotalValue());
                                                                                c2.setCellStyle(cs2);

                                                                                Cell c3 = r.createCell(
                                                                                        2 + psCellIndex);
                                                                                CellStyle cs3 = rowStyle
                                                                                        .getCellStyle(
                                                                                                2 + psCellIndex,
                                                                                                wb.createCellStyle());
                                                                                c3.setCellValue(inst.getCharge()
                                                                                        ? inst.getTotalValue()
                                                                                        : 0);
                                                                                c3.setCellStyle(cs3);

                                                                                Cell c4 = r.createCell(
                                                                                        3 + psCellIndex);
                                                                                CellStyle cs4 = rowStyle
                                                                                        .getCellStyle(
                                                                                                3 + psCellIndex,
                                                                                                wb.createCellStyle());
                                                                                c4.setCellFormula(rCol
                                                                                        + (psRowIndex + 1) + "*"
                                                                                        + chargeCol
                                                                                        + (psRowIndex
                                                                                                + currentRowIndex)
                                                                                        + "*(1-" + rCol
                                                                                        + (psRowIndex + 2)
                                                                                        + ")");
                                                                                if (!fSameCurrency)
                                                                                    cs4.setDataFormat(psS
                                                                                            .getWorkbook()
                                                                                            .createDataFormat()
                                                                                            .getFormat(format));
                                                                                c4.setCellStyle(cs4);

                                                                                /*Cell c5 = r.createCell(4 + psCellIndex);
                                                                                CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle());
                                                                                c5.setCellValue(inst.getOnsiteTotalValue());
                                                                                c5.setCellStyle(cs5);
                                                                                        
                                                                                Cell c6 = r.createCell(5 + psCellIndex);
                                                                                CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle());
                                                                                c6.setCellValue(inst.getTripTotalValue());
                                                                                c6.setCellStyle(cs6);*/

                                                                                for (int y = 4
                                                                                        + psCellIndex; y <= rowStyle
                                                                                                .getLast(); y++) {
                                                                                    CellStyle tcs = rowStyle
                                                                                            .getCellStyle(y,
                                                                                                    null);
                                                                                    if (tcs != null) {
                                                                                        Cell tc = r
                                                                                                .createCell(y);
                                                                                        tc.setCellStyle(tcs);
                                                                                    }
                                                                                }
                                                                                i++;
                                                                            }
                                                                        }
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                            currentRowIndexTo = currentRowIndex;
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            {
                                                sTotal = currentRowIndex;
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Services total:");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                        wb.createCellStyle());
                                                c2.setCellFormula("SUM(" + rCol
                                                        + (psRowIndex + currentRowIndexFrom) + ":" + rCol
                                                        + (psRowIndex + currentRowIndexTo) + ")");
                                                c2.setCellStyle(cs2);

                                                Cell c3 = r.createCell(2 + psCellIndex);
                                                CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs3.setWrapText(true);
                                                c3.setCellValue("");
                                                c3.setCellStyle(cs3);

                                                Cell c4 = r.createCell(3 + psCellIndex);
                                                CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                        wb.createCellStyle());
                                                c4.setCellFormula("SUM(" + totalCol
                                                        + (psRowIndex + currentRowIndexFrom) + ":" + totalCol
                                                        + (psRowIndex + currentRowIndexTo) + ")");
                                                if (!fSameCurrency)
                                                    cs4.setDataFormat(psS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c4.setCellStyle(cs4);

                                                for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }

                                            int currentRowIndexFromTC = -1;
                                            int currentRowIndexToTC = -1;
                                            if (psq.getTrainingCoursesCount() > 0) {
                                                {
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    i++;
                                                }
                                                {
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    i++;
                                                }

                                                {
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Training course:");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + psCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue("Cost per person:");
                                                    c2.setCellStyle(cs2);

                                                    Cell c3 = r.createCell(2 + psCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs3.setWrapText(true);
                                                    c3.setCellValue("Number of participants:");
                                                    c3.setCellStyle(cs3);

                                                    Cell c4 = r.createCell(3 + psCellIndex);
                                                    CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs4.setWrapText(true);
                                                    c4.setCellValue("$$:");
                                                    c4.setCellStyle(cs4);

                                                    for (int y = 4 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    i++;
                                                }
                                                for (TrainingCourse ttc : psq.getTrainingCourses().values()) {
                                                    if (!ttc.getInclude()) {
                                                        continue;
                                                    }
                                                    if (currentRowIndexFromTC < 0) {
                                                        currentRowIndexFromTC = currentRowIndex + 1;
                                                    }
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue(ttc.getTrainingCourse().getName());
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + psCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                            wb.createCellStyle());
                                                    c2.setCellValue(!getCurrentProposalForm().getProposal()
                                                            .getConfig().isSalesSupport()
                                                                    ? ttc.getPricePerAttendee()
                                                                    : 0);
                                                    if (!fSameCurrency)
                                                        cs2.setDataFormat(psS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c2.setCellStyle(cs2);

                                                    Cell c3 = r.createCell(2 + psCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs3.setWrapText(true);
                                                    c3.setCellValue(ttc.getAttendees());
                                                    c3.setCellStyle(cs3);

                                                    Cell c4 = r.createCell(3 + psCellIndex);
                                                    CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                            wb.createCellStyle());
                                                    c4.setCellValue(
                                                            !getCurrentProposalForm().getProposal().getConfig()
                                                                    .isSalesSupport() ? ttc.getRegionalPrice()
                                                                            : 0);
                                                    if (!fSameCurrency)
                                                        cs4.setDataFormat(psS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c4.setCellStyle(cs4);

                                                    for (int y = 4 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                currentRowIndexToTC = currentRowIndex;
                                                {
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    i++;
                                                }
                                                {
                                                    tTotal = currentRowIndex;
                                                    if (psS.getLastRowNum() >= psRowIndex + i) {
                                                        psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex
                                                            - psRowStyles.size() * Math.floor(
                                                                    currentRowIndex / psRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = psS.createRow(psRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0
                                                            + psCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + psCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Training courses total:");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + psCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue("");
                                                    c2.setCellStyle(cs2);

                                                    Cell c3 = r.createCell(2 + psCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                            wb.createCellStyle());
                                                    cs3.setWrapText(true);
                                                    c3.setCellValue("");
                                                    c3.setCellStyle(cs3);

                                                    Cell c4 = r.createCell(3 + psCellIndex);
                                                    CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                            wb.createCellStyle());
                                                    c4.setCellFormula("SUM(" + totalCol
                                                            + (psRowIndex + currentRowIndexFromTC) + ":"
                                                            + totalCol + (psRowIndex + currentRowIndexToTC)
                                                            + ")");
                                                    if (!fSameCurrency)
                                                        cs4.setDataFormat(psS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c4.setCellStyle(cs4);

                                                    for (int y = 4 + psCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                            }

                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            {
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles
                                                        .get((int) (currentRowIndex - psRowStyles.size() * Math
                                                                .floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Total cost discount:");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + psCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex,
                                                        wb.createCellStyle());
                                                c2.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? psq.getPSDiscount() : 0);
                                                cs2.setDataFormat(
                                                        s.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                                                c2.setCellStyle(cs2);

                                                Cell c3 = r.createCell(2 + psCellIndex);
                                                CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex,
                                                        wb.createCellStyle());
                                                cs3.setWrapText(true);
                                                c3.setCellValue("");
                                                c3.setCellStyle(cs3);

                                                Cell c4 = r.createCell(3 + psCellIndex);
                                                CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex,
                                                        wb.createCellStyle());
                                                c4.setCellFormula("(1-" + rCol + (psRowIndex + currentRowIndex)
                                                        + ")*(" + totalCol + (psRowIndex + sTotal + 1) + "+"
                                                        + totalCol + (psRowIndex + tTotal + 1) + ")");
                                                if (!fSameCurrency)
                                                    cs4.setDataFormat(psS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c4.setCellStyle(cs4);

                                                for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }
                                        }

                                        com.compassplus.proposalModel.OracleQuote oq = getCurrentProposalForm()
                                                .getProposal().getOracleQuote();
                                        //Oracle
                                        int j = i;
                                        i = 0;
                                        currentRowIndex = 0;
                                        if (isOQ) {
                                            int sTotal = -1;
                                            int tTotal = -1;

                                            int currentRowIndexFrom = -1;
                                            int currentRowIndexTo = -1;
                                            String rCol = CellReference.convertNumToColString(1 + oCellIndex);
                                            String licCol = CellReference.convertNumToColString(2 + oCellIndex);
                                            String supCol = CellReference.convertNumToColString(4 + oCellIndex);
                                            String totCol = CellReference.convertNumToColString(5 + oCellIndex);
                                            String disCol = CellReference.convertNumToColString(6 + oCellIndex);
                                            String cpCol = CellReference.convertNumToColString(7 + oCellIndex);
                                            String custCol = CellReference
                                                    .convertNumToColString(8 + oCellIndex);

                                            String format = (getCurrentProposalForm().getProposal()
                                                    .getCurrency().getSymbol() != null
                                                            ? "\"" + getCurrentProposalForm().getProposal()
                                                                    .getCurrency().getSymbol() + "\" "
                                                            : "")
                                                    + "#,##0"
                                                    + (getCurrentProposalForm().getProposal().getCurrency()
                                                            .getSymbol() == null ? " \""
                                                                    + getCurrentProposalForm().getProposal()
                                                                            .getCurrency().getName()
                                                                    + "\"" : "");
                                            boolean first = true;
                                            for (com.compassplus.proposalModel.OracleLicense ol : oq
                                                    .getOracleLicenses().values()) {
                                                if (ol.isMemberOfAnotherBox()) {
                                                    continue;
                                                }
                                                if (!first) {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    i++;
                                                } else {
                                                    first = false;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue(ol.getProductKey());
                                                    c1.setCellStyle(cs1);

                                                    for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }

                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue("");
                                                    c2.setCellStyle(cs2);

                                                    Cell c3 = r.createCell(2 + oCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs3.setWrapText(true);
                                                    c3.setCellValue("License price");
                                                    c3.setCellStyle(cs3);

                                                    Cell c4 = r.createCell(3 + oCellIndex);
                                                    CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs4.setWrapText(true);
                                                    c4.setCellValue("Options price");
                                                    c4.setCellStyle(cs4);

                                                    Cell c5 = r.createCell(4 + oCellIndex);
                                                    CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs5.setWrapText(true);
                                                    c5.setCellValue("Support price");
                                                    c5.setCellStyle(cs5);

                                                    Cell c6 = r.createCell(5 + oCellIndex);
                                                    CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs6.setWrapText(true);
                                                    c6.setCellValue("Total price");
                                                    c6.setCellStyle(cs6);

                                                    Cell c7 = r.createCell(6 + oCellIndex);
                                                    CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs7.setWrapText(true);
                                                    c7.setCellValue("Discount");
                                                    c7.setCellStyle(cs7);

                                                    Cell c8 = r.createCell(7 + oCellIndex);
                                                    CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs8.setWrapText(true);
                                                    c8.setCellValue("CP price (total)");
                                                    c8.setCellStyle(cs8);

                                                    Cell c9 = r.createCell(8 + oCellIndex);
                                                    CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs9.setWrapText(true);
                                                    c9.setCellValue("Customer price");
                                                    c9.setCellStyle(cs9);

                                                    Cell c10 = r.createCell(9 + oCellIndex);
                                                    CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs10.setWrapText(true);
                                                    c10.setCellValue("Margin");
                                                    c10.setCellStyle(cs10);

                                                    for (int y = 10 + oCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                /*
                                                * Cell c2 = r.createCell(1 + psCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle());
                                                    c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?ttc.getPricePerAttendee():0);
                                                    if (!fSameCurrency)
                                                        cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format));
                                                    c2.setCellStyle(cs2);
                                                        
                                                        
                                                    Cell c3 = r.createCell(2 + psCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle());
                                                    cs3.setWrapText(true);
                                                    c3.setCellValue(ttc.getAttendees());
                                                    c3.setCellStyle(cs3);
                                                * */

                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("License type");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue(ol.getOracleLicense().getName());
                                                    c2.setCellStyle(cs2);

                                                    Cell c3 = r.createCell(2 + oCellIndex);
                                                    CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c3.setCellValue(!getCurrentProposalForm().getProposal()
                                                            .getConfig().isSalesSupport() ? ol.getLicensePrice()
                                                                    : 0);
                                                    if (!fSameCurrency)
                                                        cs3.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c3.setCellStyle(cs3);

                                                    Cell c4 = r.createCell(3 + oCellIndex);
                                                    CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c4.setCellValue(!getCurrentProposalForm().getProposal()
                                                            .getConfig().isSalesSupport() ? ol.getOptionsPrice()
                                                                    : 0);
                                                    if (!fSameCurrency)
                                                        cs4.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c4.setCellStyle(cs4);

                                                    Cell c5 = r.createCell(4 + oCellIndex);
                                                    CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c5.setCellValue(!getCurrentProposalForm().getProposal()
                                                            .getConfig().isSalesSupport()
                                                                    ? ol.getOracleSupportPrice()
                                                                    : 0);
                                                    if (!fSameCurrency)
                                                        cs5.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c5.setCellStyle(cs5);

                                                    Cell c6 = r.createCell(5 + oCellIndex);
                                                    CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c6.setCellFormula("SUM(" + licCol
                                                            + (oRowIndex + currentRowIndex) + ":" + supCol
                                                            + (oRowIndex + currentRowIndex) + ")");
                                                    //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")");
                                                    if (!fSameCurrency)
                                                        cs6.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c6.setCellStyle(cs6);

                                                    Cell c7 = r.createCell(6 + oCellIndex);
                                                    CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs7.setDataFormat(psS.getWorkbook().createDataFormat()
                                                            .getFormat("0%;-0%"));
                                                    cs7.setWrapText(true);
                                                    c7.setCellValue(ol.getDiscount());
                                                    c7.setCellStyle(cs7);

                                                    /*
                                                    *
                                                    String totCol = CellReference.convertNumToColString(5 + oCellIndex);
                                                    String disCol = CellReference.convertNumToColString(6 + oCellIndex);
                                                    String cpCol = CellReference.convertNumToColString(7 + oCellIndex);
                                                    String custCol = CellReference.convertNumToColString(8 + oCellIndex);
                                                    * */

                                                    Cell c8 = r.createCell(7 + oCellIndex);
                                                    CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c8.setCellFormula("CEILING(" + totCol
                                                            + (oRowIndex + currentRowIndex) + "*(1-" + rCol
                                                            + (oRowIndex + currentRowIndex + 6) + "),1)"); //Customer Price
                                                    if (!fSameCurrency)
                                                        cs8.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c8.setCellStyle(cs8);

                                                    Cell c9 = r.createCell(8 + oCellIndex);
                                                    CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c9.setCellFormula("CEILING(" + totCol
                                                            + (oRowIndex + currentRowIndex) + "*(1-" + disCol
                                                            + (oRowIndex + currentRowIndex) + "),1)"); //Customer Price
                                                    //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)");
                                                    if (!fSameCurrency)
                                                        cs9.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c9.setCellStyle(cs9);

                                                    Cell c10 = r.createCell(9 + oCellIndex);
                                                    CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex,
                                                            wb.createCellStyle());
                                                    c10.setCellFormula(custCol + (oRowIndex + currentRowIndex)
                                                            + "-" + cpCol + (oRowIndex + currentRowIndex)); //Customer Price
                                                    if (!fSameCurrency)
                                                        cs10.setDataFormat(oS.getWorkbook().createDataFormat()
                                                                .getFormat(format));
                                                    c10.setCellStyle(cs10);

                                                    for (int y = 10 + oCellIndex; y <= rowStyle
                                                            .getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Number of cores");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue(ol.getCores());
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Model");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue(ol.getLicensingModel());
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Coefficient");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue(ol.getCoefficient().getValue());
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Included options");
                                                    c1.setCellStyle(cs1);

                                                    String options = "";
                                                    for (String ss : ol.getProduct().getOracleOptions()) {
                                                        OracleOption op = getCurrentProposalForm().getProposal()
                                                                .getConfig().getOracleOptions().get(ss);
                                                        options += ", " + op.getShortName();
                                                    }

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    c2.setCellValue(
                                                            options.length() > 0 ? options.substring(1) : "");
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                /*{
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor(currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Support rate");
                                                    c1.setCellStyle(cs1);
                                                        
                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                                                    c2.setCellValue(ol.getOracleLicense().getSupportRate());
                                                    c2.setCellStyle(cs2);
                                                        
                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                        
                                                    i++;
                                                }*/
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Maximum discount");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);
                                                    cs2.setDataFormat(psS.getWorkbook().createDataFormat()
                                                            .getFormat("0%;-0%"));
                                                    c2.setCellValue(ol.getCPDiscount() / 100d);
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                                {
                                                    if (oS.getLastRowNum() >= oRowIndex + i) {
                                                        oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                    }
                                                    RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex
                                                            - oRowStyles.size() * Math.floor(
                                                                    currentRowIndex / oRowStyles.size())));
                                                    currentRowIndex++;
                                                    Row r = oS.createRow(oRowIndex + i);
                                                    for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }
                                                    Cell c1 = r.createCell(0 + oCellIndex);
                                                    CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs1.setWrapText(true);
                                                    c1.setCellValue("Shared");
                                                    c1.setCellStyle(cs1);

                                                    Cell c2 = r.createCell(1 + oCellIndex);
                                                    CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                            wb.createCellStyle());
                                                    cs2.setWrapText(true);

                                                    String sharedString = "Yes, with";
                                                    for (String ss : ol.getChildren()) {
                                                        sharedString += " " + ss + ",";
                                                    }

                                                    c2.setCellValue(
                                                            ol.isShared()
                                                                    ? sharedString.substring(0,
                                                                            sharedString.length() - 1)
                                                                    : "No");
                                                    c2.setCellStyle(cs2);

                                                    for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                        CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                        if (tcs != null) {
                                                            Cell tc = r.createCell(y);
                                                            tc.setCellStyle(tcs);
                                                        }
                                                    }

                                                    i++;
                                                }
                                            }
                                            {
                                                if (oS.getLastRowNum() >= oRowIndex + i) {
                                                    oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = oRowStyles
                                                        .get((int) (currentRowIndex - oRowStyles.size() * Math
                                                                .floor(currentRowIndex / oRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = oS.createRow(oRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + oCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);

                                                for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            /*{
                                                if (psS.getLastRowNum() >= psRowIndex + i) {
                                                    psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = psS.createRow(psRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + psCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("");
                                                c1.setCellStyle(cs1);
                                                    
                                                for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                i++;
                                            }
                                            */
                                            {
                                                if (oS.getLastRowNum() >= oRowIndex + i) {
                                                    oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1);
                                                }
                                                RowStyle rowStyle = oRowStyles
                                                        .get((int) (currentRowIndex - oRowStyles.size() * Math
                                                                .floor(currentRowIndex / oRowStyles.size())));
                                                currentRowIndex++;
                                                Row r = oS.createRow(oRowIndex + i);
                                                for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }
                                                Cell c1 = r.createCell(0 + oCellIndex);
                                                CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex,
                                                        wb.createCellStyle());
                                                cs1.setWrapText(true);
                                                c1.setCellValue("Total");
                                                c1.setCellStyle(cs1);

                                                Cell c2 = r.createCell(1 + oCellIndex);
                                                CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex,
                                                        wb.createCellStyle());
                                                cs2.setWrapText(true);
                                                c2.setCellValue("");
                                                c2.setCellStyle(cs2);

                                                Cell c3 = r.createCell(2 + oCellIndex);
                                                CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex,
                                                        wb.createCellStyle());
                                                c3.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? oq.getLicenseTotal() : 0);
                                                if (!fSameCurrency)
                                                    cs3.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c3.setCellStyle(cs3);

                                                Cell c4 = r.createCell(3 + oCellIndex);
                                                CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex,
                                                        wb.createCellStyle());
                                                c4.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? oq.getOptionsTotal() : 0);
                                                if (!fSameCurrency)
                                                    cs4.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c4.setCellStyle(cs4);

                                                Cell c5 = r.createCell(4 + oCellIndex);
                                                CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex,
                                                        wb.createCellStyle());
                                                c5.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? oq.getSupportTotal() : 0);
                                                if (!fSameCurrency)
                                                    cs5.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c5.setCellStyle(cs5);

                                                Cell c6 = r.createCell(5 + oCellIndex);
                                                CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex,
                                                        wb.createCellStyle());
                                                c6.setCellValue(!getCurrentProposalForm().getProposal()
                                                        .getConfig().isSalesSupport() ? oq.getTotalTotal() : 0);
                                                //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")");
                                                if (!fSameCurrency)
                                                    cs6.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c6.setCellStyle(cs6);

                                                Cell c7 = r.createCell(6 + oCellIndex);
                                                CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex,
                                                        wb.createCellStyle());
                                                //cs7.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                                                cs7.setWrapText(true);
                                                c7.setCellValue("");
                                                c7.setCellStyle(cs7);

                                                /*
                                                *
                                                String totCol = CellReference.convertNumToColString(5 + oCellIndex);
                                                String disCol = CellReference.convertNumToColString(6 + oCellIndex);
                                                String cpCol = CellReference.convertNumToColString(7 + oCellIndex);
                                                String custCol = CellReference.convertNumToColString(8 + oCellIndex);
                                                * */

                                                Cell c8 = r.createCell(7 + oCellIndex);
                                                CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex,
                                                        wb.createCellStyle());
                                                c8.setCellValue(!getCurrentProposalForm().getProposal()
                                                        .getConfig().isSalesSupport() ? oq.getCPTotal() : 0);
                                                if (!fSameCurrency)
                                                    cs8.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c8.setCellStyle(cs8);

                                                Cell c9 = r.createCell(8 + oCellIndex);
                                                CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex,
                                                        wb.createCellStyle());
                                                c9.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? oq.getCustomerTotal() : 0);
                                                //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)");
                                                if (!fSameCurrency)
                                                    cs9.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c9.setCellStyle(cs9);

                                                Cell c10 = r.createCell(9 + oCellIndex);
                                                CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex,
                                                        wb.createCellStyle());
                                                c10.setCellValue(
                                                        !getCurrentProposalForm().getProposal().getConfig()
                                                                .isSalesSupport() ? oq.getTotalMargin() : 0);
                                                if (!fSameCurrency)
                                                    cs10.setDataFormat(oS.getWorkbook().createDataFormat()
                                                            .getFormat(format));
                                                c10.setCellStyle(cs10);

                                                for (int y = 10 + oCellIndex; y <= rowStyle.getLast(); y++) {
                                                    CellStyle tcs = rowStyle.getCellStyle(y, null);
                                                    if (tcs != null) {
                                                        Cell tc = r.createCell(y);
                                                        tc.setCellStyle(tcs);
                                                    }
                                                }

                                                i++;
                                            }
                                        }

                                        ArrayList<Row> rowsToRemove = new ArrayList();
                                        ScriptEngineManager factory = new ScriptEngineManager();
                                        ScriptEngine engine = factory.getEngineByName("JavaScript");
                                        Bindings bindings = getBindings(getCurrentProposalForm().getProposal(),
                                                engine);
                                        int dec = 0;
                                        int psDec = 0;
                                        for (int si = 0; si < wb.getNumberOfSheets(); si++) {
                                            Sheet sis = wb.getSheetAt(si);
                                            Iterator<Row> riter = sis.rowIterator();
                                            while (riter.hasNext()) {
                                                Row row = riter.next();
                                                Iterator<Cell> citer = row.cellIterator();
                                                while (citer.hasNext()) {
                                                    Cell cell = citer.next();
                                                    if (analyzeCell(wb, sis, row, cell, engine, bindings)) {
                                                        if (sis.getSheetName() == s.getSheetName()
                                                                && row.getRowNum() < rowIndex) {
                                                            dec++;
                                                        }
                                                        if (sis.getSheetName() == psS.getSheetName()
                                                                && row.getRowNum() < psRowIndex) {
                                                            psDec++;
                                                        }
                                                        if (!rowsToRemove.contains(row)) {
                                                            rowsToRemove.add(row);
                                                        }
                                                    }
                                                }
                                            }
                                            for (Row ri : rowsToRemove) {
                                                removeRow(sis, ri.getRowNum(), wb);
                                            }

                                            rowsToRemove.clear();
                                        }
                                        rowIndex = rowIndex - dec;
                                        psRowIndex = psRowIndex - psDec;

                                        Row settingsRow = settingsSheet.getRow(0);
                                        if (settingsRow == null) {
                                            settingsRow = settingsSheet.createRow(0);
                                        }
                                        CellUtil.createCell(settingsRow, 0,
                                                getCurrentProposalForm().getProposal().toString());
                                        CellUtil.createCell(settingsRow, 1, new Integer(
                                                getCurrentProposalForm().getProposal().getProducts().size())
                                                        .toString());
                                        CellUtil.createCell(settingsRow, 2, cellIndex.toString());
                                        CellUtil.createCell(settingsRow, 3, rowIndex.toString());
                                        CellUtil.createCell(settingsRow, 4, s.getSheetName());

                                        //PS
                                        if (isPSQ) {
                                            CellUtil.createCell(settingsRow, 5, new Integer(j).toString());
                                            CellUtil.createCell(settingsRow, 6, psCellIndex.toString());
                                            CellUtil.createCell(settingsRow, 7, psRowIndex.toString());
                                            CellUtil.createCell(settingsRow, 8, psS.getSheetName());
                                        }

                                        //Oracle
                                        if (isOQ) {
                                            CellUtil.createCell(settingsRow, 9, new Integer(i).toString());
                                            CellUtil.createCell(settingsRow, 10, oCellIndex.toString());
                                            CellUtil.createCell(settingsRow, 11, oRowIndex.toString());
                                            CellUtil.createCell(settingsRow, 12, oS.getSheetName());
                                        }

                                        wb.setSheetHidden(wb.getSheetIndex(settingsSheet), true);
                                        OutputStream out = new FileOutputStream(sfile);
                                        wb.write(out);
                                        out.close();
                                        getCurrentProposalForm().setChanged(false);
                                        JOptionPane.showMessageDialog(getRoot(),
                                                "Proposal successfully exported", "Result",
                                                JOptionPane.INFORMATION_MESSAGE);
                                    } catch (Exception exception) {
                                        exception.printStackTrace();
                                        JOptionPane.showMessageDialog(getRoot(), "Proposal can't be exported",
                                                "Error", JOptionPane.ERROR_MESSAGE);
                                    }
                                } else if (value == JOptionPane.CANCEL_OPTION) {
                                    dialog.dispose();
                                }
                            }
                        } catch (Exception exception) {
                            optionPane.setValue(null);
                        }
                    }
                }
            }
        });
        dialog.pack();
        dialog.setLocationRelativeTo(getRoot());
        dialog.setVisible(true);
    }
}

From source file:com.dickimawbooks.datatooltk.io.DatatoolExcel.java

License:Open Source License

public DatatoolDb importData(File file) throws DatatoolImportException {
    DatatoolDb db = new DatatoolDb(settings);

    try {//from  w  w  w . j  a  va  2  s.  co  m
        if (!file.exists()) {
            throw new IOException(DatatoolTk.getLabelWithValue("error.io.file_not_found", "" + file));
        }

        if (file.getName().toLowerCase().endsWith(".xlsx")) {
            throw new IOException(DatatoolTk.getLabel("error.xlsx_not_supported"));
        }

        Workbook workBook = WorkbookFactory.create(file);
        Sheet sheet;

        String sheetRef = settings.getSheetRef();

        int sheetIdx = 0;
        String sheetName = null;

        try {
            sheetIdx = Integer.parseInt(sheetRef);
        } catch (NumberFormatException e) {
            sheetName = sheetRef;
        }

        if (sheetName == null) {
            sheet = workBook.getSheetAt(sheetIdx);
            db.setName(sheet.getSheetName());
        } else {
            sheet = workBook.getSheet(sheetName);
            db.setName(sheetName);
        }

        Iterator<Row> rowIter = sheet.rowIterator();
        int rowIdx = 0;

        if (!rowIter.hasNext()) {
            return db;
        }

        Row row = rowIter.next();

        if (settings.hasCSVHeader()) {
            // First row is header

            boolean empty = true;

            while (empty) {
                for (Cell cell : row) {
                    DatatoolHeader header = new DatatoolHeader(db, cell.toString());
                    db.addColumn(header);

                    empty = false;
                }

                if (empty) {
                    if (!rowIter.hasNext()) {
                        return db;
                    }

                    row = rowIter.next();
                }
            }
        } else {
            // First row of data

            int cellIdx = 0;

            for (Cell cell : row) {
                DatatoolHeader header = new DatatoolHeader(db,
                        DatatoolTk.getLabelWithValue("default.field", (cellIdx + 1)));
                db.addColumn(header);

                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }

        while (rowIter.hasNext()) {
            row = rowIter.next();

            int cellIdx = 0;

            for (Cell cell : row) {
                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }
    } catch (Exception e) {
        throw new DatatoolImportException(DatatoolTk.getLabelWithValue("error.import.failed", file.toString()),
                e);
    }

    return db;
}

From source file:com.dtec.validationgen.service.ValidationService.java

public String addContent() {
    try {/*from   ww  w.ja  va  2s  .c  o m*/
        XSSFWorkbook book = ioService.readExcel(templatePath);
        Map<String, String> mapFunction = new HashMap<String, String>();

        Sheet bookSheet = book.getSheetAt(0);
        mapFunction = ioService.readFunction("mapping.korn");
        Iterator<Row> rowIterator = bookSheet.rowIterator();
        String fieldName = "", validateString = "", resultCommand = "", comment = "", callfunction = "",
                keyFunction = "";
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getCell(0).getStringCellValue().trim() == null
                    || row.getCell(0).getStringCellValue().trim() == "") {
                break;
            }
            callfunction = "PROCEDURE P_validate";
            fieldName = row.getCell(1).getStringCellValue().trim();
            validateString = row.getCell(10).getStringCellValue();
            String[] validateStrings = validateString.split("\\n");
            List<String> params = new ArrayList<>();
            for (String keyValidation : validateStrings) {
                keyFunction = getKeyMap(mapFunction, keyValidation);
                callfunction += "_" + keyFunction;
                comment += "--" + keyValidation + "\n";
                if (keyFunction.equals("expectvalue")) {
                    params.add(keyValidation.substring(keyValidation.indexOf("'") + 1,
                            keyValidation.lastIndexOf("'")));
                } else if (keyFunction.equals("expectvalues")) {
                    params.add(keyValidation.substring(keyValidation.indexOf("'") + 1,
                            keyValidation.indexOf("'", keyValidation.indexOf("'") + 1)));
                    params.add(
                            keyValidation
                                    .substring(
                                            keyValidation
                                                    .indexOf("'",
                                                            keyValidation.indexOf("'",
                                                                    keyValidation.indexOf("'") + 1) + 1)
                                                    + 1,
                                            keyValidation.lastIndexOf("'")));
                } else if (keyFunction.equals("intemplate")) {
                    params.add(keyValidation.substring(keyValidation.indexOf("'") + 1,
                            keyValidation.lastIndexOf("'")));
                } else if (keyFunction.equals("length")) {
                    params.add(keyValidation.substring(keyValidation.indexOf("'") + 1,
                            keyValidation.lastIndexOf("'")));
                }
            }
            callfunction += "(v_cs1_rec." + fieldName + ",'" + fieldName + "'";
            if (params.size() <= 0) {
                callfunction += ");";
            } else if (params.size() == 1) {
                callfunction += ",'" + params.get(0) + "');";
            } else if (params.size() == 2) {
                callfunction += ",'" + params.get(0) + "'" + ",'" + params.get(1) + "');";
            }

            resultCommand += "--" + fieldName + "\n" + comment + "BEGIN\n" + callfunction + "\nEND;\n\n";
            comment = "";
        }

        return resultCommand + "\n\n";
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(parent, "Error Gen Content", "Can't update Content",
                JOptionPane.ERROR_MESSAGE);
        Logger.getLogger(ValidationService.class.getName()).log(Level.SEVERE, null, ex);
        return "{content}";
    }

}

From source file:com.dtec.validationgen.test.TestService.java

public static void main(String... args) throws IOException {
    System.out.println("agbc number(5)".split(" ")[1].toUpperCase().contains("R("));
    System.out.println("number".toUpperCase().contains("NsUMBER") ? "NUMBER" : "VARCHAR2");
    TestService f = new TestService();
    f.changeString(f.abc);/* w ww  .j av a  2  s  . com*/
    System.out.println(f.abc);

    XSSFWorkbook book = new IoService().readExcel("work around.xlsx");
    Map<String, String> mapFunction = new HashMap<String, String>();
    Sheet bookSheet = book.getSheetAt(0);
    Iterator<Row> rowIterator = bookSheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        row.getCell(0).getStringCellValue();
        String fieldName = row.getCell(1).getStringCellValue().trim();
        String validateString = row.getCell(10).getStringCellValue();
        for (String keyValidation : validateString.split("\\n")) {
            System.out.println(keyValidation.trim());
        }
    }
    String keyValidation = "'abc' or 'ddd'";
    System.out.println(
            keyValidation.indexOf("'") + 1 + " " + keyValidation.indexOf("'", keyValidation.indexOf("'") + 1));
    String[] param = { "", "", "" };
    param[0] = keyValidation.substring(keyValidation.indexOf("'") + 1,
            keyValidation.indexOf("'", keyValidation.indexOf("'") + 1));
    param[1] = keyValidation.substring(
            keyValidation.indexOf("'", keyValidation.indexOf("'", keyValidation.indexOf("'") + 1) + 1) + 1,
            keyValidation.lastIndexOf("'"));

    System.out.println(Arrays.asList(param));
}