List of usage examples for org.apache.poi.ss.usermodel Workbook isSheetVeryHidden
boolean isSheetVeryHidden(int sheetIx);
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();//www. j a va2s. com { final ArrayList<String> sheets = new ArrayList<String>(0); for (int i = 0; i < wb.getNumberOfSheets(); i++) { if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) { sheets.add(wb.getSheetName(i)); } } if (sheets.size() == 0) { JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error", JOptionPane.ERROR_MESSAGE); return; } Integer rowsCountInt = null; Integer cellIndexInt = null; Integer rowIndexInt = null; String sheetIndexStr = null; Integer sheetIndexInt = null; Integer psRowsCountInt = null; Integer psCellIndexInt = null; Integer psRowIndexInt = null; String psSheetIndexStr = null; Integer psSheetIndexInt = null; Integer oRowsCountInt = null; Integer oCellIndexInt = null; Integer oRowIndexInt = null; String oSheetIndexStr = null; Integer oSheetIndexInt = null; Sheet settingsSheet = wb.getSheet("PCTSettings"); final List<RowStyle> rowStyles = new ArrayList<RowStyle>(); final List<RowStyle> psRowStyles = new ArrayList<RowStyle>(); final List<RowStyle> oRowStyles = new ArrayList<RowStyle>(); boolean sameCurrency = false; if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(1); Cell cellIndexCell = currentSettingsRow.getCell(2); Cell rowIndexCell = currentSettingsRow.getCell(3); Cell sheetIndexCell = currentSettingsRow.getCell(4); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { rowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); cellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); rowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); rowIndexInt++; cellIndexInt++; //sheetIndexInt = Integer.parseInt(sheetIndexCell.getStringCellValue()); sheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(sheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < rowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(sheetIndexStr).getRow(rowIndexInt - 1)); rowStyles.add(rowStyle); removeRow(wb.getSheet(sheetIndexStr), rowIndexInt - 1, wb); } } } catch (Exception ex) { } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(5); Cell cellIndexCell = currentSettingsRow.getCell(6); Cell rowIndexCell = currentSettingsRow.getCell(7); Cell sheetIndexCell = currentSettingsRow.getCell(8); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { psRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); psCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); psRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); psRowIndexInt++; psCellIndexInt++; psSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(psSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < psRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(psSheetIndexStr).getRow(psRowIndexInt - 1)); psRowStyles.add(rowStyle); removeRow(wb.getSheet(psSheetIndexStr), psRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(9); Cell cellIndexCell = currentSettingsRow.getCell(10); Cell rowIndexCell = currentSettingsRow.getCell(11); Cell sheetIndexCell = currentSettingsRow.getCell(12); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { oRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); oCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); oRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); oRowIndexInt++; oCellIndexInt++; oSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(oSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < oRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(oSheetIndexStr).getRow(oRowIndexInt - 1)); oRowStyles.add(rowStyle); removeRow(wb.getSheet(oSheetIndexStr), oRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } final boolean isPSQ = getCurrentProposalForm().getProposal().getPSQuote().enabled(); final boolean isOQ = !getCurrentProposalForm().getProposal().getConfig().isSalesSupport() && getCurrentProposalForm().getProposal().getOracleQuote().enabled() && getCurrentProposalForm().getProposal().getOracleQuote().getOracleLicenses().size() > 0; final JComboBox sheetIndexField = sheets.size() > 1 ? new JComboBox(sheets.toArray()) : null; if (sheetIndexField != null && sheetIndexStr != null) { for (String key : sheets) { if (key.equals(sheetIndexStr)) { sheetIndexField.setSelectedItem(key); break; } } } String selectedSheet1 = sheetIndexField != null ? (String) sheetIndexField.getSelectedItem() : null; final JComboBox psSheetIndexField = sheets.size() > 1 && isPSQ ? new JComboBox(sheets.toArray()) : null; if (psSheetIndexField != null && psSheetIndexStr != null) { for (String key : sheets) { if (key.equals(psSheetIndexStr)) { psSheetIndexField.setSelectedItem(key); break; } } } else if (psSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1)) { psSheetIndexField.setSelectedItem(key); break; } } } String selectedSheet2 = psSheetIndexField != null ? (String) psSheetIndexField.getSelectedItem() : null; final JComboBox oSheetIndexField = sheets.size() > 1 && isOQ ? new JComboBox(sheets.toArray()) : null; if (oSheetIndexField != null && oSheetIndexStr != null) { for (String key : sheets) { if (key.equals(oSheetIndexStr)) { oSheetIndexField.setSelectedItem(key); break; } } } else if (oSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1) && !key.equals(selectedSheet2)) { oSheetIndexField.setSelectedItem(key); break; } } } final JTextField rowIndexField = new JTextField(rowIndexInt != null ? rowIndexInt.toString() : "1"); final JTextField cellIndexField = new JTextField(cellIndexInt != null ? cellIndexInt.toString() : "1"); final JTextField psRowIndexField = new JTextField( psRowIndexInt != null ? psRowIndexInt.toString() : "1"); final JTextField psCellIndexField = new JTextField( psCellIndexInt != null ? psCellIndexInt.toString() : "1"); final JTextField oRowIndexField = new JTextField(oRowIndexInt != null ? oRowIndexInt.toString() : "1"); final JTextField oCellIndexField = new JTextField( oCellIndexInt != null ? oCellIndexInt.toString() : "1"); final JOptionPane optionPane = new JOptionPane(new JComponent[] { sheets.size() > 1 ? new JLabel("Products sheet") : null, sheetIndexField, new JLabel("Products row index"), rowIndexField, new JLabel("Products cell index"), cellIndexField, (sheets.size() > 1 && isPSQ) ? new JLabel("Prof. services sheet") : null, isPSQ ? psSheetIndexField : null, isPSQ ? new JLabel("Prof. services row index") : null, isPSQ ? psRowIndexField : null, isPSQ ? new JLabel("Prof. services cell index") : null, isPSQ ? psCellIndexField : null, (sheets.size() > 1 && isOQ) ? new JLabel("Oracle calculation sheet") : null, isOQ ? oSheetIndexField : null, isOQ ? new JLabel("Oracle calculation row index") : null, isOQ ? oRowIndexField : null, isOQ ? new JLabel("Oracle calculation cell index") : null, isOQ ? oCellIndexField : null }, JOptionPane.QUESTION_MESSAGE, JOptionPane.OK_CANCEL_OPTION); final JDialog dialog = new JDialog(getFrame(), "Export position", true); dialog.setResizable(false); dialog.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent we) { dialog.dispose(); } }); dialog.setContentPane(optionPane); dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE); final boolean fSameCurrency = false;//sameCurrency; optionPane.addPropertyChangeListener(new PropertyChangeListener() { public void propertyChange(PropertyChangeEvent e) { if (optionPane.getValue() != null) { String prop = e.getPropertyName(); if (dialog.isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY))) { try { if (optionPane.getValue() instanceof Integer) { int value = (Integer) optionPane.getValue(); if (value == JOptionPane.OK_OPTION) { Sheet s = null; Sheet psS = null; Sheet oS = null; if (sheetIndexField != null) { try { s = wb.getSheet((String) sheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { s = wb.getSheet(sheets.get(0)); } //PS if (psSheetIndexField != null) { try { psS = wb.getSheet((String) psSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { psS = wb.getSheet(sheets.get(0)); } //Oracle if (oSheetIndexField != null) { try { oS = wb.getSheet((String) oSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { oS = wb.getSheet(sheets.get(0)); } if (getCurrentProposalForm().getProposal().getPSQuote().enabled()) { if (s.equals(psS)) { JOptionPane.showMessageDialog(getRoot(), "Products and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (getCurrentProposalForm().getProposal().getPSQuote().enabled() && isOQ) { if (psS.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (isOQ) { if (s.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Products and oracle calculation can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } Integer rowIndex = null; try { rowIndex = Integer.parseInt(rowIndexField.getText()); rowIndex--; } catch (Exception exception) { } if (rowIndex == null || rowIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); rowIndexField.requestFocus(); rowIndexField.selectAll(); throw new Exception(); } //PS Integer psRowIndex = null; try { psRowIndex = Integer.parseInt(psRowIndexField.getText()); psRowIndex--; } catch (Exception exception) { } if ((psRowIndex == null || psRowIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psRowIndexField.requestFocus(); psRowIndexField.selectAll(); throw new Exception(); } //Oracle Integer oRowIndex = null; try { oRowIndex = Integer.parseInt(oRowIndexField.getText()); oRowIndex--; } catch (Exception exception) { } if ((oRowIndex == null || oRowIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oRowIndexField.requestFocus(); oRowIndexField.selectAll(); throw new Exception(); } Integer cellIndex = null; try { cellIndex = Integer.parseInt(cellIndexField.getText()); cellIndex--; } catch (Exception exception) { } if (cellIndex == null || cellIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); cellIndexField.requestFocus(); cellIndexField.selectAll(); throw new Exception(); } Integer psCellIndex = null; try { psCellIndex = Integer.parseInt(psCellIndexField.getText()); psCellIndex--; } catch (Exception exception) { } if ((psCellIndex == null || psCellIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psCellIndexField.requestFocus(); psCellIndexField.selectAll(); throw new Exception(); } Integer oCellIndex = null; try { oCellIndex = Integer.parseInt(oCellIndexField.getText()); oCellIndex--; } catch (Exception exception) { } if ((oCellIndex == null || oCellIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oCellIndexField.requestFocus(); oCellIndexField.selectAll(); throw new Exception(); } dialog.dispose(); try { int i = 0; Sheet settingsSheet = wb.getSheet("PCTSettings"); if (settingsSheet == null) { settingsSheet = wb.createSheet("PCTSettings"); } else { } if (rowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, s.getRow(rowIndex)); rowStyles.add(rowStyle); } //PS if (psRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, psS.getRow(psRowIndex)); psRowStyles.add(rowStyle); } //Oracle if (oRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, oS.getRow(oRowIndex)); oRowStyles.add(rowStyle); } String regPriceCol = CellReference.convertNumToColString(1 + cellIndex); String regPriceDiscount = CellReference .convertNumToColString(2 + cellIndex); String supPriceCol = CellReference.convertNumToColString(4 + cellIndex); String supPriceDiscount = CellReference .convertNumToColString(5 + cellIndex); int currentRowIndex = 0; for (Product p : getCurrentProposalForm().getProposal().getProducts() .values()) { if (s.getLastRowNum() >= rowIndex + i) { s.shiftRows(rowIndex + i, s.getLastRowNum(), 1); } RowStyle rowStyle = rowStyles .get((int) (currentRowIndex - rowStyles.size() * Math.floor(currentRowIndex / rowStyles.size()))); currentRowIndex++; Row r = s.createRow(rowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + cellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + cellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + cellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(p.getDescription()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + cellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + cellIndex, wb.createCellStyle()); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); if (!fSameCurrency) cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); c2.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getRegionPrice()); Cell c3 = r.createCell(2 + cellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + cellIndex, wb.createCellStyle()); cs3.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c3.setCellStyle(cs3); c3.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getDiscount()); Cell c4 = r.createCell(3 + cellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs4.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c4.setCellStyle(cs4); int rowIndexTotal = rowIndex + i + 1; c4.setCellFormula("CEILING(" + regPriceCol + rowIndexTotal + "*(1-" + regPriceDiscount + rowIndexTotal + "),1)"); Cell c5 = r.createCell(4 + cellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs5.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c5.setCellStyle(cs5); c5.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportPriceUndiscounted()); Cell c6 = r.createCell(5 + cellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + cellIndex, wb.createCellStyle()); cs6.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c6.setCellStyle(cs6); c6.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportDiscount()); Cell c7 = r.createCell(6 + cellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs7.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c7.setCellStyle(cs7); c7.setCellFormula("CEILING(" + supPriceCol + rowIndexTotal + "*(1-" + supPriceDiscount + rowIndexTotal + "),1)"); for (int y = 7 + cellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } PSQuote psq = getCurrentProposalForm().getProposal().getPSQuote(); //PS i = 0; currentRowIndex = 0; if (isPSQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + psCellIndex); String chargeCol = CellReference .convertNumToColString(2 + psCellIndex); String totalCol = CellReference .convertNumToColString(3 + psCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? getCurrentProposalForm().getProposal() .getRegion().getMDRate() * getCurrentProposalForm().getProposal() .getCurrency().getRate() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellStyle(cs2); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getMDDiscount() : 0); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite daily rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getOnsiteDailyCost() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite trip rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getTripPrice() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Professional Services Description:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("M/D:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Chargeable:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Onsite days:"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Onsite trips:"); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (ServicesGroup sg : getCurrentProposalForm().getProposal() .getConfig().getServicesRoot().getGroups()) { if (sg.notEmpty(psq)) { //vigrujaem gruppu { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; if (currentRowIndexFrom < 0) { currentRowIndexFrom = currentRowIndex; } Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(sg.getName()); c1.setCellStyle(cs1); if (psq.isExportable(sg.getKey())) { Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(sg.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(sg.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat().getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(sg.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(sg.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ } for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } if (!psq.isExportable(sg.getKey())) { for (Service srv : sg.getServices().values()) { if (srv.notEmpty(psq)) { //System.out.println("\n\nsg.getKey()="+sg.getKey()); //System.out.println("psq.isHidden(sg.getKey())="+psq.isHidden(sg.getKey())); if (psq.isExportable(srv.getKey())) { //vigrujaem service { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(srv.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(srv.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue( srv.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(srv.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(srv.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } else { for (com.compassplus.proposalModel.Service inst : psq .getServices().values()) { if (inst.getService().getGroupKey() .equals(sg.getKey()) && inst.getService().getKey() .equals(srv.getKey())) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows( psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles .size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow( psRowIndex + i); for (int y = rowStyle .getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell( 0 + psCellIndex); CellStyle cs1 = rowStyle .getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(inst.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell( 1 + psCellIndex); CellStyle cs2 = rowStyle .getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( inst.getTotalValue()); c2.setCellStyle(cs2); Cell c3 = r.createCell( 2 + psCellIndex); CellStyle cs3 = rowStyle .getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(inst.getCharge() ? inst.getTotalValue() : 0); c3.setCellStyle(cs3); Cell c4 = r.createCell( 3 + psCellIndex); CellStyle cs4 = rowStyle .getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS .getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(inst.getOnsiteTotalValue()); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(inst.getTripTotalValue()); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } i++; } } } } } } } } } currentRowIndexTo = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { sTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Services total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellFormula("SUM(" + rCol + (psRowIndex + currentRowIndexFrom) + ":" + rCol + (psRowIndex + currentRowIndexTo) + ")"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFrom) + ":" + totalCol + (psRowIndex + currentRowIndexTo) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } int currentRowIndexFromTC = -1; int currentRowIndexToTC = -1; if (psq.getTrainingCoursesCount() > 0) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training course:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("Cost per person:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Number of participants:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (TrainingCourse ttc : psq.getTrainingCourses().values()) { if (!ttc.getInclude()) { continue; } if (currentRowIndexFromTC < 0) { currentRowIndexFromTC = currentRowIndex + 1; } if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ttc.getTrainingCourse().getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ttc.getPricePerAttendee() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? ttc.getRegionalPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } currentRowIndexToTC = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { tTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training courses total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFromTC) + ":" + totalCol + (psRowIndex + currentRowIndexToTC) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total cost discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getPSDiscount() : 0); cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("(1-" + rCol + (psRowIndex + currentRowIndex) + ")*(" + totalCol + (psRowIndex + sTotal + 1) + "+" + totalCol + (psRowIndex + tTotal + 1) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } com.compassplus.proposalModel.OracleQuote oq = getCurrentProposalForm() .getProposal().getOracleQuote(); //Oracle int j = i; i = 0; currentRowIndex = 0; if (isOQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + oCellIndex); String licCol = CellReference.convertNumToColString(2 + oCellIndex); String supCol = CellReference.convertNumToColString(4 + oCellIndex); String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference .convertNumToColString(8 + oCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); boolean first = true; for (com.compassplus.proposalModel.OracleLicense ol : oq .getOracleLicenses().values()) { if (ol.isMemberOfAnotherBox()) { continue; } if (!first) { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } else { first = false; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ol.getProductKey()); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("License price"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("Options price"); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Support price"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Total price"); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setWrapText(true); c7.setCellValue("Discount"); c7.setCellStyle(cs7); Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); cs8.setWrapText(true); c8.setCellValue("CP price (total)"); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); cs9.setWrapText(true); c9.setCellValue("Customer price"); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); cs10.setWrapText(true); c10.setCellValue("Margin"); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /* * Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?ttc.getPricePerAttendee():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); * */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("License type"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getOracleLicense().getName()); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getLicensePrice() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOptionsPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOracleSupportPrice() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellFormula("SUM(" + licCol + (oRowIndex + currentRowIndex) + ":" + supCol + (oRowIndex + currentRowIndex) + ")"); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(ol.getDiscount()); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + rCol + (oRowIndex + currentRowIndex + 6) + "),1)"); //Customer Price if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); //Customer Price //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellFormula(custCol + (oRowIndex + currentRowIndex) + "-" + cpCol + (oRowIndex + currentRowIndex)); //Customer Price if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Number of cores"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCores()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Model"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getLicensingModel()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Coefficient"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCoefficient().getValue()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Included options"); c1.setCellStyle(cs1); String options = ""; for (String ss : ol.getProduct().getOracleOptions()) { OracleOption op = getCurrentProposalForm().getProposal() .getConfig().getOracleOptions().get(ss); options += ", " + op.getShortName(); } Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue( options.length() > 0 ? options.substring(1) : ""); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Support rate"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellValue(ol.getOracleLicense().getSupportRate()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Maximum discount"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellValue(ol.getCPDiscount() / 100d); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Shared"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); String sharedString = "Yes, with"; for (String ss : ol.getChildren()) { sharedString += " " + ss + ","; } c2.setCellValue( ol.isShared() ? sharedString.substring(0, sharedString.length() - 1) : "No"); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getLicenseTotal() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getOptionsTotal() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getSupportTotal() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getTotalTotal() : 0); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); //cs7.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(""); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getCPTotal() : 0); if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getCustomerTotal() : 0); //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getTotalMargin() : 0); if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } ArrayList<Row> rowsToRemove = new ArrayList(); ScriptEngineManager factory = new ScriptEngineManager(); ScriptEngine engine = factory.getEngineByName("JavaScript"); Bindings bindings = getBindings(getCurrentProposalForm().getProposal(), engine); int dec = 0; int psDec = 0; for (int si = 0; si < wb.getNumberOfSheets(); si++) { Sheet sis = wb.getSheetAt(si); Iterator<Row> riter = sis.rowIterator(); while (riter.hasNext()) { Row row = riter.next(); Iterator<Cell> citer = row.cellIterator(); while (citer.hasNext()) { Cell cell = citer.next(); if (analyzeCell(wb, sis, row, cell, engine, bindings)) { if (sis.getSheetName() == s.getSheetName() && row.getRowNum() < rowIndex) { dec++; } if (sis.getSheetName() == psS.getSheetName() && row.getRowNum() < psRowIndex) { psDec++; } if (!rowsToRemove.contains(row)) { rowsToRemove.add(row); } } } } for (Row ri : rowsToRemove) { removeRow(sis, ri.getRowNum(), wb); } rowsToRemove.clear(); } rowIndex = rowIndex - dec; psRowIndex = psRowIndex - psDec; Row settingsRow = settingsSheet.getRow(0); if (settingsRow == null) { settingsRow = settingsSheet.createRow(0); } CellUtil.createCell(settingsRow, 0, getCurrentProposalForm().getProposal().toString()); CellUtil.createCell(settingsRow, 1, new Integer( getCurrentProposalForm().getProposal().getProducts().size()) .toString()); CellUtil.createCell(settingsRow, 2, cellIndex.toString()); CellUtil.createCell(settingsRow, 3, rowIndex.toString()); CellUtil.createCell(settingsRow, 4, s.getSheetName()); //PS if (isPSQ) { CellUtil.createCell(settingsRow, 5, new Integer(j).toString()); CellUtil.createCell(settingsRow, 6, psCellIndex.toString()); CellUtil.createCell(settingsRow, 7, psRowIndex.toString()); CellUtil.createCell(settingsRow, 8, psS.getSheetName()); } //Oracle if (isOQ) { CellUtil.createCell(settingsRow, 9, new Integer(i).toString()); CellUtil.createCell(settingsRow, 10, oCellIndex.toString()); CellUtil.createCell(settingsRow, 11, oRowIndex.toString()); CellUtil.createCell(settingsRow, 12, oS.getSheetName()); } wb.setSheetHidden(wb.getSheetIndex(settingsSheet), true); OutputStream out = new FileOutputStream(sfile); wb.write(out); out.close(); getCurrentProposalForm().setChanged(false); JOptionPane.showMessageDialog(getRoot(), "Proposal successfully exported", "Result", JOptionPane.INFORMATION_MESSAGE); } catch (Exception exception) { exception.printStackTrace(); JOptionPane.showMessageDialog(getRoot(), "Proposal can't be exported", "Error", JOptionPane.ERROR_MESSAGE); } } else if (value == JOptionPane.CANCEL_OPTION) { dialog.dispose(); } } } catch (Exception exception) { optionPane.setValue(null); } } } } }); dialog.pack(); dialog.setLocationRelativeTo(getRoot()); dialog.setVisible(true); } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Clears the given Spreadsheet and loads the given Workbook into it. * * @param spreadsheet/*from w ww . j av a 2 s . c o m*/ * Target Spreadsheet * @param workbook * Workbook to load or null to generate a new workbook with one * sheet. * @param rowCount * Number of rows to generate in the first sheet. Only applies * when the workbook parameter is null. * @param columnCount * Number of columns to generate in the first sheet. Only applies * when the workbook parameter is null. */ static void loadSpreadsheetWith(Spreadsheet spreadsheet, Workbook workbook, int rowCount, int columnCount) { spreadsheet.clearSheetServerSide(); final Sheet sheet; if (workbook == null) { workbook = new XSSFWorkbook(); sheet = createNewSheet(workbook); spreadsheet.setInternalWorkbook(workbook); generateNewSpreadsheet(spreadsheet, sheet, rowCount, columnCount); } else { int activeSheetIndex = workbook.getActiveSheetIndex(); if (workbook.isSheetHidden(activeSheetIndex) || workbook.isSheetVeryHidden(activeSheetIndex)) { workbook.setActiveSheet(SpreadsheetUtil.getFirstVisibleSheetPOIIndex(workbook)); } sheet = workbook.getSheetAt(activeSheetIndex); spreadsheet.setInternalWorkbook(workbook); reloadSpreadsheetData(spreadsheet, sheet); } loadWorkbookStyles(spreadsheet); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
/** * Returns the POI index of the first visible sheet (not hidden or very * hidden). If no sheets are visible, returns 0. This is not be possible at * least in Excel, but unfortunately POI allows it. * /* www . j a v a 2s . c om*/ * @param workbook * Workbook to get the sheets from * @return Index of the first visible sheet, 0-based */ public static int getFirstVisibleSheetPOIIndex(Workbook workbook) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (!(workbook.isSheetHidden(i) && workbook.isSheetVeryHidden(i))) { return i; } } return 0; }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
/** * Returns the number of visible sheets (not hidden or very hidden) in the * given Workbook./*from ww w .j a va2 s .co m*/ * * @param workbook * Workbook to get the sheets from * @return Number of visible sheets */ public static int getNumberOfVisibleSheets(Workbook workbook) { int result = 0; for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (!(workbook.isSheetHidden(i) || workbook.isSheetVeryHidden(i))) { result++; } } return result; }