Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:com.cmcti.cmts.domain.service.impl.CustomerMacMappingLocalServiceImpl.java

License:Open Source License

private String getStringCellValue(Cell cell) {
    String value = null;//from ww w. j  a  v  a  2 s .com
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        break;
    case Cell.CELL_TYPE_FORMULA:
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        double doubleValue = cell.getNumericCellValue();
        if (doubleValue == (int) doubleValue) {
            value = String.format("%d", (int) doubleValue);
        } else {
            value = String.format("%s", doubleValue);
        }

        break;
    }

    return value;
}

From source file:com.cmcti.cmts.domain.service.impl.MerchantScopeLocalServiceImpl.java

License:Open Source License

private List<MerchantScope> getMerchants(Iterator<Row> rowIterator, int startRowIdx,
        ServiceContext serviceContext) throws PortalException, SystemException {

    List<MerchantScope> merchants = new ArrayList<MerchantScope>();

    if (startRowIdx > 0) {
        for (int i = 0; i < startRowIdx; i++) {
            if (rowIterator.hasNext())
                rowIterator.next();/*from   w w  w  .ja va  2 s . c om*/
        }
    }

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        long merchantScopeId = counterLocalService.increment(MerchantScope.class.getName());
        MerchantScope merchantScope = merchantScopePersistence.create(merchantScopeId);

        // Meta data
        merchantScope.setUserId(serviceContext.getUserId());
        merchantScope.setGroupId(serviceContext.getScopeGroupId());
        merchantScope.setCompanyId(serviceContext.getCompanyId());
        merchantScope.setCreateDate(serviceContext.getCreateDate());
        merchantScope.setModifiedDate(serviceContext.getModifiedDate());

        // CMTSID
        Cell cmtsIdCell = row.getCell(0);
        merchantScope.setCmtsId(GetterUtil.getLong(cmtsIdCell.getStringCellValue()));

        // IfIndex
        Cell ifIndexCell = row.getCell(1);
        merchantScope.setIfIndex(GetterUtil.getInteger(ifIndexCell.getStringCellValue()));

        // Merchat Code
        Cell merchantCodeCell = row.getCell(2);
        merchantScope.setMerchantCode(merchantCodeCell.getStringCellValue());

        merchants.add(merchantScope);
    }

    return merchants;
}

From source file:com.cms.utils.ExcelReader.java

public static List importExcel(File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack)
        throws FileNotFoundException {
    List lst = new ArrayList();
    FileInputStream flieInput = new FileInputStream(file);
    HSSFWorkbook workbook;//from   w  w w.j av a  2  s .  c  om
    try {
        workbook = new HSSFWorkbook(flieInput);
        HSSFSheet worksheet = workbook.getSheetAt(iSheet);
        int irowBack = 0;
        for (int i = iBeginRow; i <= worksheet.getLastRowNum(); i++) {
            Object[] obj = new Object[iToCol - iFromCol + 1];
            Row row = worksheet.getRow(i);
            if (row != null) {
                int iCount = 0;
                int check = 0;
                for (int j = iFromCol; j <= iToCol; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            obj[iCount] = cell.getStringCellValue().trim();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                obj[iCount] = DateTimeUtils.convertDateToString(date, "dd/MM/yyyy");
                            } else {
                                Double doubleValue = (Double) cell.getNumericCellValue();
                                //String.format("%.0f", doubleValue);
                                List<String> lstValue = DataUtil.splitDot(String.valueOf(doubleValue));
                                if (lstValue.get(1).matches("[0]+")) {
                                    obj[iCount] = lstValue.get(0);
                                } else {
                                    obj[iCount] = String.format("%.2f", doubleValue).trim();
                                }
                            }

                            break;
                        case Cell.CELL_TYPE_BLANK:
                            check++;
                            break;
                        }
                    } else {
                        obj[iCount] = null;
                    }
                    iCount += 1;
                }
                if (check != (iToCol - iFromCol + 1)) {
                    lst.add(obj);
                }

            } else {
                irowBack += 1;
            }
            if (irowBack == rowBack) {
                break;
            }
        }
    } catch (IOException ex) {
        lst = null;
    }
    return lst;
}

From source file:com.cn.controller.CommonController.java

/**
 * ?/*from  w ww.  j  a  v  a 2  s . c o  m*/
 *
 * @param beanPackage
 * @param className
 * @param fileName
 * @return
 * @throws Exception
 */
public ArrayList<Object> importData(String beanPackage, String className, String fileName) throws Exception {
    //???
    Class objClass = Class.forName(beanPackage + className);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream = null;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        //json = Units.objectToJson(-1, "???, ?, ???", null);
        return null;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        if (fieldDes.indexOf(fieldName) != -1) {
            templateDataIndex[fieldDes.indexOf(fieldName)] = i;
        } else {
            return null;
        }
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, "");
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }

            }
        }

        result.add(object);
    }

    return result;
}

From source file:com.cn.controller.InterfaceController.java

/**
 * Excel?/* w ww . j  a va 2 s  .com*/
 * @param beanPackage
 * @param tableName
 * @param fileName
 * @param conn
 * @return
 * @throws Exception 
 */
public String importData(String beanPackage, String tableName, String fileName, String conn) throws Exception {
    String json;
    CommonController commonController = new CommonController();
    //???
    Class objClass = Class.forName(beanPackage + tableName);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream = null;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //        System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        json = Units.objectToJson(-1,
                "???, ?, ???", null);
        return json;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        templateDataIndex[fieldDes.indexOf(fieldName)] = i;
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            //                String fieldType = field.getGenericType().toString();

            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }
            }
        }

        result.add(object);
    }

    ArrayList<Integer> addResult = commonController.dataBaseOperate(
            JSONObject.toJSONString(result, Units.features), beanPackage, tableName, "add", conn);
    //        System.out.println("import res:" + Arrays.toString(addResult.toArray()));
    if (addResult.get(0) == 0) {
        json = Units.objectToJson(0, "?" + (addResult.size() - 1) + "??!", null);
    } else if (addResult.get(0) == 2) {
        json = Units.objectToJson(2, "?!", null);
    } else {
        int successNum = 0, failedNum = 0;
        String failedMsg = ":";
        for (int i = 1; i < addResult.size(); i++) {
            int res = addResult.get(i);
            if (res == 1) {
                successNum++;
            } else {
                failedNum++;
                failedMsg += (i + 1) + ",";
            }
        }
        json = Units.objectToJson(-1,
                "?" + successNum + "??, " + failedNum + "??!" + failedMsg,
                null);
    }
    return json;
}

From source file:com.cn.servlet.InInterface.java

private ArrayList<Object> importDetailData(String detail, String beanPackage, String tableName, String fileName)
        throws Exception {
    //???//  w  w  w .  j a va2  s .c om
    Class objClass = Class.forName(beanPackage + tableName);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        //            json = Units.objectToJson(-1, "???, ?, ???", null);
        return null;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        templateDataIndex[fieldDes.indexOf(fieldName)] = i;
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        //??object(, ??  )
        JSONObject jSONObject = JSONObject.parseObject(detail);
        Iterator<String> keysIterator = jSONObject.keySet().iterator();
        while (keysIterator.hasNext()) {
            String key = keysIterator.next();
            for (Field field : fields) {
                //                    System.out.println("fieldName:" + field.getName() + ",key:" + key);
                if (field.getName().compareToIgnoreCase(key) == 0) {
                    //                        System.out.println("fieldName:" + field.getName() + ",key:" + key + ",key value:" + jSONObject.getString(key));
                    field.setAccessible(true);
                    if (!Units.strIsEmpty(jSONObject.getString(key))) {
                        field.set(object, jSONObject.getString(key));
                    }
                }
            }
        }

        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            //                String fieldType = field.getGenericType().toString();

            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Integer.valueOf(Units.subZeroAndDot(Units.getCellValue(cell))));

                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    field.set(object, Float.valueOf(Units.getCellValue(cell)));

                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getCellValue(cell)));

                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getCellValue(cell));
                }
            }
        }

        result.add(object);
    }
    return result;
}

From source file:com.cn.util.Units.java

/**
 * ?Excel?//from  www .  j  a  va2s. c o m
 *
 * @param cell
 * @return
 */
public static String getCellValue(Cell cell) {
    String result = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = String.valueOf(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    default:
        result = cell.getStringCellValue();
        break;
    }
    return result.trim();
}

From source file:com.cn.util.Units.java

/**
 * ????/*  w ww . j a v a 2s . c  om*/
 *
 * @param cell Excel?
 * @return String ??
 */
public static String getStringCellValue(Cell cell) {
    String strCell = "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        strCell = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        cell.setCellType(Cell.CELL_TYPE_STRING);
        strCell = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        strCell = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        strCell = "";
        break;
    default:
        strCell = "";
        break;
    }
    if (strCell.equals("") || strCell == null) {
        return "";
    }
    if (cell == null) {
        return "";
    }
    return strCell.trim();
}

From source file:com.codellect.util.ExcelReader.java

private boolean notEmpty(Cell cell) {
    return cell != null && !cell.getStringCellValue().equals("");
}

From source file:com.compassplus.gui.MainForm.java

private void save(String file) throws IOException, InvalidFormatException {
    final String sfile = file;
    FileInputStream inp = new FileInputStream(sfile);
    final Workbook wb = WorkbookFactory.create(inp);
    inp.close();/* ww w.  j  a va2s .c o m*/
    {
        final ArrayList<String> sheets = new ArrayList<String>(0);
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) {
                sheets.add(wb.getSheetName(i));
            }
        }
        if (sheets.size() == 0) {
            JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }

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

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

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

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

                }

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

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

                }

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

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

                }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                                i++;
                                            }

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

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

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

                                                i++;
                                            }

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

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

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

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

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

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

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

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

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

                                                i++;
                                            }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                                i++;
                                            }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                                    i++;
                                                }
                                            }

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

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

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

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

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

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

                                                i++;
                                            }
                                        }

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

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

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

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

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

                                                    i++;
                                                }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                                i++;
                                            }
                                        }

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

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

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

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

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

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