List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
public void printStyles() { ensureOut();//from ww w . j a v a 2 s.c o m // First, copy the base css BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css"))); String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) return;// w w w . j a v a2 s.c o m Iterator<Row> iter = sheet.rowIterator(); firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0); endColumn = 0; while (iter.hasNext()) { Row row = iter.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } gotBounds = true; }
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();/*from w w w .j a v a 2 s . com*/ out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:com.abixen.platform.service.businessintelligence.multivisualisation.application.service.file.reader.ExcelReaderService.java
License:Open Source License
private DataFileDto parseWorkbook(final Sheet sheet, final FileParserMessage<DataFileColumn> msg, final Boolean readFirstColumnAsColumnName) { final DataFileDto dataFileDto = new DataFileDto(); dataFileDto.setRowTypes(buildRowTypes(sheet.getRow(readFirstColumnAsColumnName ? 1 : 0))); final Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { dataFileDto.addRow(readRowAsRowInMemory(rowIterator.next(), dataFileDto.getRowTypes())); }/*from w w w .j av a 2 s .c om*/ return dataFileDto; }
From source file:com.aurel.track.lucene.util.poi.XLSTextStripper.java
License:Open Source License
public XLSTextStripper(FileInputStream fis, String fileExtension) { try {/*from w w w . j ava 2 s . c o m*/ StringBuffer sb = new StringBuffer(); Workbook workbook = null; if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) { workbook = new HSSFWorkbook(fis); } else { if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) { workbook = new XSSFWorkbook(fis); } } if (workbook != null) { int numOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStringValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { boolean booleanValue = cell.getBooleanCellValue(); cellStringValue = Boolean.toString(booleanValue); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double doubleValue = cell.getNumericCellValue(); cellStringValue = Double.toString(doubleValue); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); } if (cellStringValue != null) { sb.append(cellStringValue); sb.append("\t"); } } sb.append("\n"); } } } _text = sb.toString(); } catch (Exception e) { LOGGER.error(ExceptionUtils.getStackTrace(e)); } }
From source file:com.common.report.util.html.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { // printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next();/* ww w . j ava 2 s .c o m*/ out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:com.compassplus.gui.MainForm.java
private void save(String file) throws IOException, InvalidFormatException { final String sfile = file; FileInputStream inp = new FileInputStream(sfile); final Workbook wb = WorkbookFactory.create(inp); inp.close();/*from w w w. j a v a 2 s .co m*/ { final ArrayList<String> sheets = new ArrayList<String>(0); for (int i = 0; i < wb.getNumberOfSheets(); i++) { if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) { sheets.add(wb.getSheetName(i)); } } if (sheets.size() == 0) { JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error", JOptionPane.ERROR_MESSAGE); return; } Integer rowsCountInt = null; Integer cellIndexInt = null; Integer rowIndexInt = null; String sheetIndexStr = null; Integer sheetIndexInt = null; Integer psRowsCountInt = null; Integer psCellIndexInt = null; Integer psRowIndexInt = null; String psSheetIndexStr = null; Integer psSheetIndexInt = null; Integer oRowsCountInt = null; Integer oCellIndexInt = null; Integer oRowIndexInt = null; String oSheetIndexStr = null; Integer oSheetIndexInt = null; Sheet settingsSheet = wb.getSheet("PCTSettings"); final List<RowStyle> rowStyles = new ArrayList<RowStyle>(); final List<RowStyle> psRowStyles = new ArrayList<RowStyle>(); final List<RowStyle> oRowStyles = new ArrayList<RowStyle>(); boolean sameCurrency = false; if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(1); Cell cellIndexCell = currentSettingsRow.getCell(2); Cell rowIndexCell = currentSettingsRow.getCell(3); Cell sheetIndexCell = currentSettingsRow.getCell(4); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { rowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); cellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); rowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); rowIndexInt++; cellIndexInt++; //sheetIndexInt = Integer.parseInt(sheetIndexCell.getStringCellValue()); sheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(sheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < rowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(sheetIndexStr).getRow(rowIndexInt - 1)); rowStyles.add(rowStyle); removeRow(wb.getSheet(sheetIndexStr), rowIndexInt - 1, wb); } } } catch (Exception ex) { } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(5); Cell cellIndexCell = currentSettingsRow.getCell(6); Cell rowIndexCell = currentSettingsRow.getCell(7); Cell sheetIndexCell = currentSettingsRow.getCell(8); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { psRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); psCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); psRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); psRowIndexInt++; psCellIndexInt++; psSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(psSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < psRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(psSheetIndexStr).getRow(psRowIndexInt - 1)); psRowStyles.add(rowStyle); removeRow(wb.getSheet(psSheetIndexStr), psRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(9); Cell cellIndexCell = currentSettingsRow.getCell(10); Cell rowIndexCell = currentSettingsRow.getCell(11); Cell sheetIndexCell = currentSettingsRow.getCell(12); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { oRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); oCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); oRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); oRowIndexInt++; oCellIndexInt++; oSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(oSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < oRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(oSheetIndexStr).getRow(oRowIndexInt - 1)); oRowStyles.add(rowStyle); removeRow(wb.getSheet(oSheetIndexStr), oRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } final boolean isPSQ = getCurrentProposalForm().getProposal().getPSQuote().enabled(); final boolean isOQ = !getCurrentProposalForm().getProposal().getConfig().isSalesSupport() && getCurrentProposalForm().getProposal().getOracleQuote().enabled() && getCurrentProposalForm().getProposal().getOracleQuote().getOracleLicenses().size() > 0; final JComboBox sheetIndexField = sheets.size() > 1 ? new JComboBox(sheets.toArray()) : null; if (sheetIndexField != null && sheetIndexStr != null) { for (String key : sheets) { if (key.equals(sheetIndexStr)) { sheetIndexField.setSelectedItem(key); break; } } } String selectedSheet1 = sheetIndexField != null ? (String) sheetIndexField.getSelectedItem() : null; final JComboBox psSheetIndexField = sheets.size() > 1 && isPSQ ? new JComboBox(sheets.toArray()) : null; if (psSheetIndexField != null && psSheetIndexStr != null) { for (String key : sheets) { if (key.equals(psSheetIndexStr)) { psSheetIndexField.setSelectedItem(key); break; } } } else if (psSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1)) { psSheetIndexField.setSelectedItem(key); break; } } } String selectedSheet2 = psSheetIndexField != null ? (String) psSheetIndexField.getSelectedItem() : null; final JComboBox oSheetIndexField = sheets.size() > 1 && isOQ ? new JComboBox(sheets.toArray()) : null; if (oSheetIndexField != null && oSheetIndexStr != null) { for (String key : sheets) { if (key.equals(oSheetIndexStr)) { oSheetIndexField.setSelectedItem(key); break; } } } else if (oSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1) && !key.equals(selectedSheet2)) { oSheetIndexField.setSelectedItem(key); break; } } } final JTextField rowIndexField = new JTextField(rowIndexInt != null ? rowIndexInt.toString() : "1"); final JTextField cellIndexField = new JTextField(cellIndexInt != null ? cellIndexInt.toString() : "1"); final JTextField psRowIndexField = new JTextField( psRowIndexInt != null ? psRowIndexInt.toString() : "1"); final JTextField psCellIndexField = new JTextField( psCellIndexInt != null ? psCellIndexInt.toString() : "1"); final JTextField oRowIndexField = new JTextField(oRowIndexInt != null ? oRowIndexInt.toString() : "1"); final JTextField oCellIndexField = new JTextField( oCellIndexInt != null ? oCellIndexInt.toString() : "1"); final JOptionPane optionPane = new JOptionPane(new JComponent[] { sheets.size() > 1 ? new JLabel("Products sheet") : null, sheetIndexField, new JLabel("Products row index"), rowIndexField, new JLabel("Products cell index"), cellIndexField, (sheets.size() > 1 && isPSQ) ? new JLabel("Prof. services sheet") : null, isPSQ ? psSheetIndexField : null, isPSQ ? new JLabel("Prof. services row index") : null, isPSQ ? psRowIndexField : null, isPSQ ? new JLabel("Prof. services cell index") : null, isPSQ ? psCellIndexField : null, (sheets.size() > 1 && isOQ) ? new JLabel("Oracle calculation sheet") : null, isOQ ? oSheetIndexField : null, isOQ ? new JLabel("Oracle calculation row index") : null, isOQ ? oRowIndexField : null, isOQ ? new JLabel("Oracle calculation cell index") : null, isOQ ? oCellIndexField : null }, JOptionPane.QUESTION_MESSAGE, JOptionPane.OK_CANCEL_OPTION); final JDialog dialog = new JDialog(getFrame(), "Export position", true); dialog.setResizable(false); dialog.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent we) { dialog.dispose(); } }); dialog.setContentPane(optionPane); dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE); final boolean fSameCurrency = false;//sameCurrency; optionPane.addPropertyChangeListener(new PropertyChangeListener() { public void propertyChange(PropertyChangeEvent e) { if (optionPane.getValue() != null) { String prop = e.getPropertyName(); if (dialog.isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY))) { try { if (optionPane.getValue() instanceof Integer) { int value = (Integer) optionPane.getValue(); if (value == JOptionPane.OK_OPTION) { Sheet s = null; Sheet psS = null; Sheet oS = null; if (sheetIndexField != null) { try { s = wb.getSheet((String) sheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { s = wb.getSheet(sheets.get(0)); } //PS if (psSheetIndexField != null) { try { psS = wb.getSheet((String) psSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { psS = wb.getSheet(sheets.get(0)); } //Oracle if (oSheetIndexField != null) { try { oS = wb.getSheet((String) oSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { oS = wb.getSheet(sheets.get(0)); } if (getCurrentProposalForm().getProposal().getPSQuote().enabled()) { if (s.equals(psS)) { JOptionPane.showMessageDialog(getRoot(), "Products and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (getCurrentProposalForm().getProposal().getPSQuote().enabled() && isOQ) { if (psS.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (isOQ) { if (s.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Products and oracle calculation can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } Integer rowIndex = null; try { rowIndex = Integer.parseInt(rowIndexField.getText()); rowIndex--; } catch (Exception exception) { } if (rowIndex == null || rowIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); rowIndexField.requestFocus(); rowIndexField.selectAll(); throw new Exception(); } //PS Integer psRowIndex = null; try { psRowIndex = Integer.parseInt(psRowIndexField.getText()); psRowIndex--; } catch (Exception exception) { } if ((psRowIndex == null || psRowIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psRowIndexField.requestFocus(); psRowIndexField.selectAll(); throw new Exception(); } //Oracle Integer oRowIndex = null; try { oRowIndex = Integer.parseInt(oRowIndexField.getText()); oRowIndex--; } catch (Exception exception) { } if ((oRowIndex == null || oRowIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oRowIndexField.requestFocus(); oRowIndexField.selectAll(); throw new Exception(); } Integer cellIndex = null; try { cellIndex = Integer.parseInt(cellIndexField.getText()); cellIndex--; } catch (Exception exception) { } if (cellIndex == null || cellIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); cellIndexField.requestFocus(); cellIndexField.selectAll(); throw new Exception(); } Integer psCellIndex = null; try { psCellIndex = Integer.parseInt(psCellIndexField.getText()); psCellIndex--; } catch (Exception exception) { } if ((psCellIndex == null || psCellIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psCellIndexField.requestFocus(); psCellIndexField.selectAll(); throw new Exception(); } Integer oCellIndex = null; try { oCellIndex = Integer.parseInt(oCellIndexField.getText()); oCellIndex--; } catch (Exception exception) { } if ((oCellIndex == null || oCellIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oCellIndexField.requestFocus(); oCellIndexField.selectAll(); throw new Exception(); } dialog.dispose(); try { int i = 0; Sheet settingsSheet = wb.getSheet("PCTSettings"); if (settingsSheet == null) { settingsSheet = wb.createSheet("PCTSettings"); } else { } if (rowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, s.getRow(rowIndex)); rowStyles.add(rowStyle); } //PS if (psRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, psS.getRow(psRowIndex)); psRowStyles.add(rowStyle); } //Oracle if (oRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, oS.getRow(oRowIndex)); oRowStyles.add(rowStyle); } String regPriceCol = CellReference.convertNumToColString(1 + cellIndex); String regPriceDiscount = CellReference .convertNumToColString(2 + cellIndex); String supPriceCol = CellReference.convertNumToColString(4 + cellIndex); String supPriceDiscount = CellReference .convertNumToColString(5 + cellIndex); int currentRowIndex = 0; for (Product p : getCurrentProposalForm().getProposal().getProducts() .values()) { if (s.getLastRowNum() >= rowIndex + i) { s.shiftRows(rowIndex + i, s.getLastRowNum(), 1); } RowStyle rowStyle = rowStyles .get((int) (currentRowIndex - rowStyles.size() * Math.floor(currentRowIndex / rowStyles.size()))); currentRowIndex++; Row r = s.createRow(rowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + cellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + cellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + cellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(p.getDescription()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + cellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + cellIndex, wb.createCellStyle()); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); if (!fSameCurrency) cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); c2.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getRegionPrice()); Cell c3 = r.createCell(2 + cellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + cellIndex, wb.createCellStyle()); cs3.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c3.setCellStyle(cs3); c3.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getDiscount()); Cell c4 = r.createCell(3 + cellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs4.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c4.setCellStyle(cs4); int rowIndexTotal = rowIndex + i + 1; c4.setCellFormula("CEILING(" + regPriceCol + rowIndexTotal + "*(1-" + regPriceDiscount + rowIndexTotal + "),1)"); Cell c5 = r.createCell(4 + cellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs5.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c5.setCellStyle(cs5); c5.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportPriceUndiscounted()); Cell c6 = r.createCell(5 + cellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + cellIndex, wb.createCellStyle()); cs6.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c6.setCellStyle(cs6); c6.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportDiscount()); Cell c7 = r.createCell(6 + cellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs7.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c7.setCellStyle(cs7); c7.setCellFormula("CEILING(" + supPriceCol + rowIndexTotal + "*(1-" + supPriceDiscount + rowIndexTotal + "),1)"); for (int y = 7 + cellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } PSQuote psq = getCurrentProposalForm().getProposal().getPSQuote(); //PS i = 0; currentRowIndex = 0; if (isPSQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + psCellIndex); String chargeCol = CellReference .convertNumToColString(2 + psCellIndex); String totalCol = CellReference .convertNumToColString(3 + psCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? getCurrentProposalForm().getProposal() .getRegion().getMDRate() * getCurrentProposalForm().getProposal() .getCurrency().getRate() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellStyle(cs2); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getMDDiscount() : 0); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite daily rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getOnsiteDailyCost() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite trip rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getTripPrice() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Professional Services Description:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("M/D:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Chargeable:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Onsite days:"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Onsite trips:"); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (ServicesGroup sg : getCurrentProposalForm().getProposal() .getConfig().getServicesRoot().getGroups()) { if (sg.notEmpty(psq)) { //vigrujaem gruppu { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; if (currentRowIndexFrom < 0) { currentRowIndexFrom = currentRowIndex; } Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(sg.getName()); c1.setCellStyle(cs1); if (psq.isExportable(sg.getKey())) { Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(sg.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(sg.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat().getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(sg.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(sg.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ } for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } if (!psq.isExportable(sg.getKey())) { for (Service srv : sg.getServices().values()) { if (srv.notEmpty(psq)) { //System.out.println("\n\nsg.getKey()="+sg.getKey()); //System.out.println("psq.isHidden(sg.getKey())="+psq.isHidden(sg.getKey())); if (psq.isExportable(srv.getKey())) { //vigrujaem service { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(srv.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(srv.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue( srv.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(srv.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(srv.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } else { for (com.compassplus.proposalModel.Service inst : psq .getServices().values()) { if (inst.getService().getGroupKey() .equals(sg.getKey()) && inst.getService().getKey() .equals(srv.getKey())) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows( psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles .size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow( psRowIndex + i); for (int y = rowStyle .getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell( 0 + psCellIndex); CellStyle cs1 = rowStyle .getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(inst.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell( 1 + psCellIndex); CellStyle cs2 = rowStyle .getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( inst.getTotalValue()); c2.setCellStyle(cs2); Cell c3 = r.createCell( 2 + psCellIndex); CellStyle cs3 = rowStyle .getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(inst.getCharge() ? inst.getTotalValue() : 0); c3.setCellStyle(cs3); Cell c4 = r.createCell( 3 + psCellIndex); CellStyle cs4 = rowStyle .getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS .getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(inst.getOnsiteTotalValue()); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(inst.getTripTotalValue()); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } i++; } } } } } } } } } currentRowIndexTo = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { sTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Services total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellFormula("SUM(" + rCol + (psRowIndex + currentRowIndexFrom) + ":" + rCol + (psRowIndex + currentRowIndexTo) + ")"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFrom) + ":" + totalCol + (psRowIndex + currentRowIndexTo) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } int currentRowIndexFromTC = -1; int currentRowIndexToTC = -1; if (psq.getTrainingCoursesCount() > 0) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training course:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("Cost per person:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Number of participants:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (TrainingCourse ttc : psq.getTrainingCourses().values()) { if (!ttc.getInclude()) { continue; } if (currentRowIndexFromTC < 0) { currentRowIndexFromTC = currentRowIndex + 1; } if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ttc.getTrainingCourse().getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ttc.getPricePerAttendee() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? ttc.getRegionalPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } currentRowIndexToTC = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { tTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training courses total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFromTC) + ":" + totalCol + (psRowIndex + currentRowIndexToTC) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total cost discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getPSDiscount() : 0); cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("(1-" + rCol + (psRowIndex + currentRowIndex) + ")*(" + totalCol + (psRowIndex + sTotal + 1) + "+" + totalCol + (psRowIndex + tTotal + 1) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } com.compassplus.proposalModel.OracleQuote oq = getCurrentProposalForm() .getProposal().getOracleQuote(); //Oracle int j = i; i = 0; currentRowIndex = 0; if (isOQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + oCellIndex); String licCol = CellReference.convertNumToColString(2 + oCellIndex); String supCol = CellReference.convertNumToColString(4 + oCellIndex); String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference .convertNumToColString(8 + oCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); boolean first = true; for (com.compassplus.proposalModel.OracleLicense ol : oq .getOracleLicenses().values()) { if (ol.isMemberOfAnotherBox()) { continue; } if (!first) { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } else { first = false; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ol.getProductKey()); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("License price"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("Options price"); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Support price"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Total price"); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setWrapText(true); c7.setCellValue("Discount"); c7.setCellStyle(cs7); Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); cs8.setWrapText(true); c8.setCellValue("CP price (total)"); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); cs9.setWrapText(true); c9.setCellValue("Customer price"); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); cs10.setWrapText(true); c10.setCellValue("Margin"); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /* * Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?ttc.getPricePerAttendee():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); * */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("License type"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getOracleLicense().getName()); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getLicensePrice() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOptionsPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOracleSupportPrice() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellFormula("SUM(" + licCol + (oRowIndex + currentRowIndex) + ":" + supCol + (oRowIndex + currentRowIndex) + ")"); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(ol.getDiscount()); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + rCol + (oRowIndex + currentRowIndex + 6) + "),1)"); //Customer Price if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); //Customer Price //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellFormula(custCol + (oRowIndex + currentRowIndex) + "-" + cpCol + (oRowIndex + currentRowIndex)); //Customer Price if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Number of cores"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCores()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Model"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getLicensingModel()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Coefficient"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCoefficient().getValue()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Included options"); c1.setCellStyle(cs1); String options = ""; for (String ss : ol.getProduct().getOracleOptions()) { OracleOption op = getCurrentProposalForm().getProposal() .getConfig().getOracleOptions().get(ss); options += ", " + op.getShortName(); } Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue( options.length() > 0 ? options.substring(1) : ""); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Support rate"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellValue(ol.getOracleLicense().getSupportRate()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Maximum discount"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellValue(ol.getCPDiscount() / 100d); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Shared"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); String sharedString = "Yes, with"; for (String ss : ol.getChildren()) { sharedString += " " + ss + ","; } c2.setCellValue( ol.isShared() ? sharedString.substring(0, sharedString.length() - 1) : "No"); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getLicenseTotal() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getOptionsTotal() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getSupportTotal() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getTotalTotal() : 0); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); //cs7.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(""); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getCPTotal() : 0); if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getCustomerTotal() : 0); //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getTotalMargin() : 0); if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } ArrayList<Row> rowsToRemove = new ArrayList(); ScriptEngineManager factory = new ScriptEngineManager(); ScriptEngine engine = factory.getEngineByName("JavaScript"); Bindings bindings = getBindings(getCurrentProposalForm().getProposal(), engine); int dec = 0; int psDec = 0; for (int si = 0; si < wb.getNumberOfSheets(); si++) { Sheet sis = wb.getSheetAt(si); Iterator<Row> riter = sis.rowIterator(); while (riter.hasNext()) { Row row = riter.next(); Iterator<Cell> citer = row.cellIterator(); while (citer.hasNext()) { Cell cell = citer.next(); if (analyzeCell(wb, sis, row, cell, engine, bindings)) { if (sis.getSheetName() == s.getSheetName() && row.getRowNum() < rowIndex) { dec++; } if (sis.getSheetName() == psS.getSheetName() && row.getRowNum() < psRowIndex) { psDec++; } if (!rowsToRemove.contains(row)) { rowsToRemove.add(row); } } } } for (Row ri : rowsToRemove) { removeRow(sis, ri.getRowNum(), wb); } rowsToRemove.clear(); } rowIndex = rowIndex - dec; psRowIndex = psRowIndex - psDec; Row settingsRow = settingsSheet.getRow(0); if (settingsRow == null) { settingsRow = settingsSheet.createRow(0); } CellUtil.createCell(settingsRow, 0, getCurrentProposalForm().getProposal().toString()); CellUtil.createCell(settingsRow, 1, new Integer( getCurrentProposalForm().getProposal().getProducts().size()) .toString()); CellUtil.createCell(settingsRow, 2, cellIndex.toString()); CellUtil.createCell(settingsRow, 3, rowIndex.toString()); CellUtil.createCell(settingsRow, 4, s.getSheetName()); //PS if (isPSQ) { CellUtil.createCell(settingsRow, 5, new Integer(j).toString()); CellUtil.createCell(settingsRow, 6, psCellIndex.toString()); CellUtil.createCell(settingsRow, 7, psRowIndex.toString()); CellUtil.createCell(settingsRow, 8, psS.getSheetName()); } //Oracle if (isOQ) { CellUtil.createCell(settingsRow, 9, new Integer(i).toString()); CellUtil.createCell(settingsRow, 10, oCellIndex.toString()); CellUtil.createCell(settingsRow, 11, oRowIndex.toString()); CellUtil.createCell(settingsRow, 12, oS.getSheetName()); } wb.setSheetHidden(wb.getSheetIndex(settingsSheet), true); OutputStream out = new FileOutputStream(sfile); wb.write(out); out.close(); getCurrentProposalForm().setChanged(false); JOptionPane.showMessageDialog(getRoot(), "Proposal successfully exported", "Result", JOptionPane.INFORMATION_MESSAGE); } catch (Exception exception) { exception.printStackTrace(); JOptionPane.showMessageDialog(getRoot(), "Proposal can't be exported", "Error", JOptionPane.ERROR_MESSAGE); } } else if (value == JOptionPane.CANCEL_OPTION) { dialog.dispose(); } } } catch (Exception exception) { optionPane.setValue(null); } } } } }); dialog.pack(); dialog.setLocationRelativeTo(getRoot()); dialog.setVisible(true); } }
From source file:com.dickimawbooks.datatooltk.io.DatatoolExcel.java
License:Open Source License
public DatatoolDb importData(File file) throws DatatoolImportException { DatatoolDb db = new DatatoolDb(settings); try {//from w w w . j a va 2 s. co m if (!file.exists()) { throw new IOException(DatatoolTk.getLabelWithValue("error.io.file_not_found", "" + file)); } if (file.getName().toLowerCase().endsWith(".xlsx")) { throw new IOException(DatatoolTk.getLabel("error.xlsx_not_supported")); } Workbook workBook = WorkbookFactory.create(file); Sheet sheet; String sheetRef = settings.getSheetRef(); int sheetIdx = 0; String sheetName = null; try { sheetIdx = Integer.parseInt(sheetRef); } catch (NumberFormatException e) { sheetName = sheetRef; } if (sheetName == null) { sheet = workBook.getSheetAt(sheetIdx); db.setName(sheet.getSheetName()); } else { sheet = workBook.getSheet(sheetName); db.setName(sheetName); } Iterator<Row> rowIter = sheet.rowIterator(); int rowIdx = 0; if (!rowIter.hasNext()) { return db; } Row row = rowIter.next(); if (settings.hasCSVHeader()) { // First row is header boolean empty = true; while (empty) { for (Cell cell : row) { DatatoolHeader header = new DatatoolHeader(db, cell.toString()); db.addColumn(header); empty = false; } if (empty) { if (!rowIter.hasNext()) { return db; } row = rowIter.next(); } } } else { // First row of data int cellIdx = 0; for (Cell cell : row) { DatatoolHeader header = new DatatoolHeader(db, DatatoolTk.getLabelWithValue("default.field", (cellIdx + 1))); db.addColumn(header); db.addCell(rowIdx, cellIdx, getCellValue(cell)); cellIdx++; } if (cellIdx > 0) { rowIdx++; } } while (rowIter.hasNext()) { row = rowIter.next(); int cellIdx = 0; for (Cell cell : row) { db.addCell(rowIdx, cellIdx, getCellValue(cell)); cellIdx++; } if (cellIdx > 0) { rowIdx++; } } } catch (Exception e) { throw new DatatoolImportException(DatatoolTk.getLabelWithValue("error.import.failed", file.toString()), e); } return db; }
From source file:com.dtec.validationgen.service.ValidationService.java
public String addContent() { try {/*from ww w.ja va 2s .c o m*/ XSSFWorkbook book = ioService.readExcel(templatePath); Map<String, String> mapFunction = new HashMap<String, String>(); Sheet bookSheet = book.getSheetAt(0); mapFunction = ioService.readFunction("mapping.korn"); Iterator<Row> rowIterator = bookSheet.rowIterator(); String fieldName = "", validateString = "", resultCommand = "", comment = "", callfunction = "", keyFunction = ""; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getCell(0).getStringCellValue().trim() == null || row.getCell(0).getStringCellValue().trim() == "") { break; } callfunction = "PROCEDURE P_validate"; fieldName = row.getCell(1).getStringCellValue().trim(); validateString = row.getCell(10).getStringCellValue(); String[] validateStrings = validateString.split("\\n"); List<String> params = new ArrayList<>(); for (String keyValidation : validateStrings) { keyFunction = getKeyMap(mapFunction, keyValidation); callfunction += "_" + keyFunction; comment += "--" + keyValidation + "\n"; if (keyFunction.equals("expectvalue")) { params.add(keyValidation.substring(keyValidation.indexOf("'") + 1, keyValidation.lastIndexOf("'"))); } else if (keyFunction.equals("expectvalues")) { params.add(keyValidation.substring(keyValidation.indexOf("'") + 1, keyValidation.indexOf("'", keyValidation.indexOf("'") + 1))); params.add( keyValidation .substring( keyValidation .indexOf("'", keyValidation.indexOf("'", keyValidation.indexOf("'") + 1) + 1) + 1, keyValidation.lastIndexOf("'"))); } else if (keyFunction.equals("intemplate")) { params.add(keyValidation.substring(keyValidation.indexOf("'") + 1, keyValidation.lastIndexOf("'"))); } else if (keyFunction.equals("length")) { params.add(keyValidation.substring(keyValidation.indexOf("'") + 1, keyValidation.lastIndexOf("'"))); } } callfunction += "(v_cs1_rec." + fieldName + ",'" + fieldName + "'"; if (params.size() <= 0) { callfunction += ");"; } else if (params.size() == 1) { callfunction += ",'" + params.get(0) + "');"; } else if (params.size() == 2) { callfunction += ",'" + params.get(0) + "'" + ",'" + params.get(1) + "');"; } resultCommand += "--" + fieldName + "\n" + comment + "BEGIN\n" + callfunction + "\nEND;\n\n"; comment = ""; } return resultCommand + "\n\n"; } catch (IOException ex) { JOptionPane.showMessageDialog(parent, "Error Gen Content", "Can't update Content", JOptionPane.ERROR_MESSAGE); Logger.getLogger(ValidationService.class.getName()).log(Level.SEVERE, null, ex); return "{content}"; } }
From source file:com.dtec.validationgen.test.TestService.java
public static void main(String... args) throws IOException { System.out.println("agbc number(5)".split(" ")[1].toUpperCase().contains("R(")); System.out.println("number".toUpperCase().contains("NsUMBER") ? "NUMBER" : "VARCHAR2"); TestService f = new TestService(); f.changeString(f.abc);/* w ww .j av a 2 s . com*/ System.out.println(f.abc); XSSFWorkbook book = new IoService().readExcel("work around.xlsx"); Map<String, String> mapFunction = new HashMap<String, String>(); Sheet bookSheet = book.getSheetAt(0); Iterator<Row> rowIterator = bookSheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); row.getCell(0).getStringCellValue(); String fieldName = row.getCell(1).getStringCellValue().trim(); String validateString = row.getCell(10).getStringCellValue(); for (String keyValidation : validateString.split("\\n")) { System.out.println(keyValidation.trim()); } } String keyValidation = "'abc' or 'ddd'"; System.out.println( keyValidation.indexOf("'") + 1 + " " + keyValidation.indexOf("'", keyValidation.indexOf("'") + 1)); String[] param = { "", "", "" }; param[0] = keyValidation.substring(keyValidation.indexOf("'") + 1, keyValidation.indexOf("'", keyValidation.indexOf("'") + 1)); param[1] = keyValidation.substring( keyValidation.indexOf("'", keyValidation.indexOf("'", keyValidation.indexOf("'") + 1) + 1) + 1, keyValidation.lastIndexOf("'")); System.out.println(Arrays.asList(param)); }