List of usage examples for org.apache.poi.ss.util CellReference convertNumToColString
public static String convertNumToColString(int col)
From source file:com.company.et.service.XlsService.java
private static void createReserveCell(Workbook wb, Sheet s, Row row, int column) { Cell cell = row.createCell(column);/*from w w w.jav a 2s. c o m*/ cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula(CellReference.convertNumToColString(column - 2) + (row.getRowNum() + 1) + "-" + CellReference.convertNumToColString(column - 1) + (row.getRowNum() + 1)); setCellStyle(wb, s, cell); }
From source file:com.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 . ja v a 2s. c o m { final ArrayList<String> sheets = new ArrayList<String>(0); for (int i = 0; i < wb.getNumberOfSheets(); i++) { if (!(wb.isSheetHidden(i) || wb.isSheetVeryHidden(i))) { sheets.add(wb.getSheetName(i)); } } if (sheets.size() == 0) { JOptionPane.showMessageDialog(getRoot(), "Selected excel workbook is empty", "Error", JOptionPane.ERROR_MESSAGE); return; } Integer rowsCountInt = null; Integer cellIndexInt = null; Integer rowIndexInt = null; String sheetIndexStr = null; Integer sheetIndexInt = null; Integer psRowsCountInt = null; Integer psCellIndexInt = null; Integer psRowIndexInt = null; String psSheetIndexStr = null; Integer psSheetIndexInt = null; Integer oRowsCountInt = null; Integer oCellIndexInt = null; Integer oRowIndexInt = null; String oSheetIndexStr = null; Integer oSheetIndexInt = null; Sheet settingsSheet = wb.getSheet("PCTSettings"); final List<RowStyle> rowStyles = new ArrayList<RowStyle>(); final List<RowStyle> psRowStyles = new ArrayList<RowStyle>(); final List<RowStyle> oRowStyles = new ArrayList<RowStyle>(); boolean sameCurrency = false; if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(1); Cell cellIndexCell = currentSettingsRow.getCell(2); Cell rowIndexCell = currentSettingsRow.getCell(3); Cell sheetIndexCell = currentSettingsRow.getCell(4); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { rowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); cellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); rowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); rowIndexInt++; cellIndexInt++; //sheetIndexInt = Integer.parseInt(sheetIndexCell.getStringCellValue()); sheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(sheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < rowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(sheetIndexStr).getRow(rowIndexInt - 1)); rowStyles.add(rowStyle); removeRow(wb.getSheet(sheetIndexStr), rowIndexInt - 1, wb); } } } catch (Exception ex) { } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(5); Cell cellIndexCell = currentSettingsRow.getCell(6); Cell rowIndexCell = currentSettingsRow.getCell(7); Cell sheetIndexCell = currentSettingsRow.getCell(8); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { psRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); psCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); psRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); psRowIndexInt++; psCellIndexInt++; psSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(psSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < psRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(psSheetIndexStr).getRow(psRowIndexInt - 1)); psRowStyles.add(rowStyle); removeRow(wb.getSheet(psSheetIndexStr), psRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } if (settingsSheet != null) { Row currentSettingsRow = settingsSheet.getRow(0); if (currentSettingsRow != null) { Cell oldProposalCell = currentSettingsRow.getCell(0); Cell rowsCountCell = currentSettingsRow.getCell(9); Cell cellIndexCell = currentSettingsRow.getCell(10); Cell rowIndexCell = currentSettingsRow.getCell(11); Cell sheetIndexCell = currentSettingsRow.getCell(12); try { Proposal oldProposal = new Proposal(config); String proposalString = oldProposalCell.getStringCellValue(); if (proposalString != null) { oldProposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString)); sameCurrency = getCurrentProposalForm().getProposal().getCurrency().getName() .equals(oldProposal.getCurrency().getName()); } } catch (Exception e) { } if (rowsCountCell != null && rowIndexCell != null && sheetIndexCell != null && cellIndexCell != null) { try { oRowsCountInt = Integer.parseInt(rowsCountCell.getStringCellValue()); oCellIndexInt = Integer.parseInt(cellIndexCell.getStringCellValue()); oRowIndexInt = Integer.parseInt(rowIndexCell.getStringCellValue()); oRowIndexInt++; oCellIndexInt++; oSheetIndexStr = sheetIndexCell.getStringCellValue(); if (wb.getSheet(oSheetIndexStr) != null) { //sheetIndexStr = wb.getSheetAt(sheetIndexInt).getSheetName(); for (int j = 0; j < oRowsCountInt; j++) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, wb.getSheet(oSheetIndexStr).getRow(oRowIndexInt - 1)); oRowStyles.add(rowStyle); removeRow(wb.getSheet(oSheetIndexStr), oRowIndexInt - 1, wb, true); } } } catch (Exception ex) { ex.printStackTrace(); } } } else { } } final boolean isPSQ = getCurrentProposalForm().getProposal().getPSQuote().enabled(); final boolean isOQ = !getCurrentProposalForm().getProposal().getConfig().isSalesSupport() && getCurrentProposalForm().getProposal().getOracleQuote().enabled() && getCurrentProposalForm().getProposal().getOracleQuote().getOracleLicenses().size() > 0; final JComboBox sheetIndexField = sheets.size() > 1 ? new JComboBox(sheets.toArray()) : null; if (sheetIndexField != null && sheetIndexStr != null) { for (String key : sheets) { if (key.equals(sheetIndexStr)) { sheetIndexField.setSelectedItem(key); break; } } } String selectedSheet1 = sheetIndexField != null ? (String) sheetIndexField.getSelectedItem() : null; final JComboBox psSheetIndexField = sheets.size() > 1 && isPSQ ? new JComboBox(sheets.toArray()) : null; if (psSheetIndexField != null && psSheetIndexStr != null) { for (String key : sheets) { if (key.equals(psSheetIndexStr)) { psSheetIndexField.setSelectedItem(key); break; } } } else if (psSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1)) { psSheetIndexField.setSelectedItem(key); break; } } } String selectedSheet2 = psSheetIndexField != null ? (String) psSheetIndexField.getSelectedItem() : null; final JComboBox oSheetIndexField = sheets.size() > 1 && isOQ ? new JComboBox(sheets.toArray()) : null; if (oSheetIndexField != null && oSheetIndexStr != null) { for (String key : sheets) { if (key.equals(oSheetIndexStr)) { oSheetIndexField.setSelectedItem(key); break; } } } else if (oSheetIndexField != null) { for (String key : sheets) { if (!key.equals(selectedSheet1) && !key.equals(selectedSheet2)) { oSheetIndexField.setSelectedItem(key); break; } } } final JTextField rowIndexField = new JTextField(rowIndexInt != null ? rowIndexInt.toString() : "1"); final JTextField cellIndexField = new JTextField(cellIndexInt != null ? cellIndexInt.toString() : "1"); final JTextField psRowIndexField = new JTextField( psRowIndexInt != null ? psRowIndexInt.toString() : "1"); final JTextField psCellIndexField = new JTextField( psCellIndexInt != null ? psCellIndexInt.toString() : "1"); final JTextField oRowIndexField = new JTextField(oRowIndexInt != null ? oRowIndexInt.toString() : "1"); final JTextField oCellIndexField = new JTextField( oCellIndexInt != null ? oCellIndexInt.toString() : "1"); final JOptionPane optionPane = new JOptionPane(new JComponent[] { sheets.size() > 1 ? new JLabel("Products sheet") : null, sheetIndexField, new JLabel("Products row index"), rowIndexField, new JLabel("Products cell index"), cellIndexField, (sheets.size() > 1 && isPSQ) ? new JLabel("Prof. services sheet") : null, isPSQ ? psSheetIndexField : null, isPSQ ? new JLabel("Prof. services row index") : null, isPSQ ? psRowIndexField : null, isPSQ ? new JLabel("Prof. services cell index") : null, isPSQ ? psCellIndexField : null, (sheets.size() > 1 && isOQ) ? new JLabel("Oracle calculation sheet") : null, isOQ ? oSheetIndexField : null, isOQ ? new JLabel("Oracle calculation row index") : null, isOQ ? oRowIndexField : null, isOQ ? new JLabel("Oracle calculation cell index") : null, isOQ ? oCellIndexField : null }, JOptionPane.QUESTION_MESSAGE, JOptionPane.OK_CANCEL_OPTION); final JDialog dialog = new JDialog(getFrame(), "Export position", true); dialog.setResizable(false); dialog.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent we) { dialog.dispose(); } }); dialog.setContentPane(optionPane); dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE); final boolean fSameCurrency = false;//sameCurrency; optionPane.addPropertyChangeListener(new PropertyChangeListener() { public void propertyChange(PropertyChangeEvent e) { if (optionPane.getValue() != null) { String prop = e.getPropertyName(); if (dialog.isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY))) { try { if (optionPane.getValue() instanceof Integer) { int value = (Integer) optionPane.getValue(); if (value == JOptionPane.OK_OPTION) { Sheet s = null; Sheet psS = null; Sheet oS = null; if (sheetIndexField != null) { try { s = wb.getSheet((String) sheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { s = wb.getSheet(sheets.get(0)); } //PS if (psSheetIndexField != null) { try { psS = wb.getSheet((String) psSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { psS = wb.getSheet(sheets.get(0)); } //Oracle if (oSheetIndexField != null) { try { oS = wb.getSheet((String) oSheetIndexField.getSelectedItem()); } catch (Exception exception) { } } else { oS = wb.getSheet(sheets.get(0)); } if (getCurrentProposalForm().getProposal().getPSQuote().enabled()) { if (s.equals(psS)) { JOptionPane.showMessageDialog(getRoot(), "Products and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (getCurrentProposalForm().getProposal().getPSQuote().enabled() && isOQ) { if (psS.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation and prof. services can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } if (isOQ) { if (s.equals(oS)) { JOptionPane.showMessageDialog(getRoot(), "Products and oracle calculation can't be exported on the same sheet", "Error", JOptionPane.ERROR_MESSAGE); throw new Exception(); } } Integer rowIndex = null; try { rowIndex = Integer.parseInt(rowIndexField.getText()); rowIndex--; } catch (Exception exception) { } if (rowIndex == null || rowIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); rowIndexField.requestFocus(); rowIndexField.selectAll(); throw new Exception(); } //PS Integer psRowIndex = null; try { psRowIndex = Integer.parseInt(psRowIndexField.getText()); psRowIndex--; } catch (Exception exception) { } if ((psRowIndex == null || psRowIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psRowIndexField.requestFocus(); psRowIndexField.selectAll(); throw new Exception(); } //Oracle Integer oRowIndex = null; try { oRowIndex = Integer.parseInt(oRowIndexField.getText()); oRowIndex--; } catch (Exception exception) { } if ((oRowIndex == null || oRowIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation row index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oRowIndexField.requestFocus(); oRowIndexField.selectAll(); throw new Exception(); } Integer cellIndex = null; try { cellIndex = Integer.parseInt(cellIndexField.getText()); cellIndex--; } catch (Exception exception) { } if (cellIndex == null || cellIndex < 0) { JOptionPane.showMessageDialog(getRoot(), "Products cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); cellIndexField.requestFocus(); cellIndexField.selectAll(); throw new Exception(); } Integer psCellIndex = null; try { psCellIndex = Integer.parseInt(psCellIndexField.getText()); psCellIndex--; } catch (Exception exception) { } if ((psCellIndex == null || psCellIndex < 0) && isPSQ) { JOptionPane.showMessageDialog(getRoot(), "Prof. services cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); psCellIndexField.requestFocus(); psCellIndexField.selectAll(); throw new Exception(); } Integer oCellIndex = null; try { oCellIndex = Integer.parseInt(oCellIndexField.getText()); oCellIndex--; } catch (Exception exception) { } if ((oCellIndex == null || oCellIndex < 0) && isOQ) { JOptionPane.showMessageDialog(getRoot(), "Oracle calculation cell index is not valid", "Error", JOptionPane.ERROR_MESSAGE); oCellIndexField.requestFocus(); oCellIndexField.selectAll(); throw new Exception(); } dialog.dispose(); try { int i = 0; Sheet settingsSheet = wb.getSheet("PCTSettings"); if (settingsSheet == null) { settingsSheet = wb.createSheet("PCTSettings"); } else { } if (rowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, s.getRow(rowIndex)); rowStyles.add(rowStyle); } //PS if (psRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, psS.getRow(psRowIndex)); psRowStyles.add(rowStyle); } //Oracle if (oRowStyles.size() == 0) { RowStyle rowStyle = new RowStyle(); rowStyle.init(wb, oS.getRow(oRowIndex)); oRowStyles.add(rowStyle); } String regPriceCol = CellReference.convertNumToColString(1 + cellIndex); String regPriceDiscount = CellReference .convertNumToColString(2 + cellIndex); String supPriceCol = CellReference.convertNumToColString(4 + cellIndex); String supPriceDiscount = CellReference .convertNumToColString(5 + cellIndex); int currentRowIndex = 0; for (Product p : getCurrentProposalForm().getProposal().getProducts() .values()) { if (s.getLastRowNum() >= rowIndex + i) { s.shiftRows(rowIndex + i, s.getLastRowNum(), 1); } RowStyle rowStyle = rowStyles .get((int) (currentRowIndex - rowStyles.size() * Math.floor(currentRowIndex / rowStyles.size()))); currentRowIndex++; Row r = s.createRow(rowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + cellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + cellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + cellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(p.getDescription()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + cellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + cellIndex, wb.createCellStyle()); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); if (!fSameCurrency) cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); c2.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getRegionPrice()); Cell c3 = r.createCell(2 + cellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + cellIndex, wb.createCellStyle()); cs3.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c3.setCellStyle(cs3); c3.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getDiscount()); Cell c4 = r.createCell(3 + cellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs4.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c4.setCellStyle(cs4); int rowIndexTotal = rowIndex + i + 1; c4.setCellFormula("CEILING(" + regPriceCol + rowIndexTotal + "*(1-" + regPriceDiscount + rowIndexTotal + "),1)"); Cell c5 = r.createCell(4 + cellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs5.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c5.setCellStyle(cs5); c5.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportPriceUndiscounted()); Cell c6 = r.createCell(5 + cellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + cellIndex, wb.createCellStyle()); cs6.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c6.setCellStyle(cs6); c6.setCellValue(p.getProposal().getConfig().isSalesSupport() ? 0 : p.getSupportDiscount()); Cell c7 = r.createCell(6 + cellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + cellIndex, wb.createCellStyle()); if (!fSameCurrency) cs7.setDataFormat( s.getWorkbook().createDataFormat().getFormat(format)); c7.setCellStyle(cs7); c7.setCellFormula("CEILING(" + supPriceCol + rowIndexTotal + "*(1-" + supPriceDiscount + rowIndexTotal + "),1)"); for (int y = 7 + cellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } PSQuote psq = getCurrentProposalForm().getProposal().getPSQuote(); //PS i = 0; currentRowIndex = 0; if (isPSQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + psCellIndex); String chargeCol = CellReference .convertNumToColString(2 + psCellIndex); String totalCol = CellReference .convertNumToColString(3 + psCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? getCurrentProposalForm().getProposal() .getRegion().getMDRate() * getCurrentProposalForm().getProposal() .getCurrency().getRate() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Man-day discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellStyle(cs2); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getMDDiscount() : 0); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite daily rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getOnsiteDailyCost() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Onsite trip rate:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?getCurrentProposalForm().getProposal().getRegion().getTripPrice() * getCurrentProposalForm().getProposal().getCurrency().getRate():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); for (int y = 2 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Professional Services Description:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("M/D:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Chargeable:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Onsite days:"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Onsite trips:"); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (ServicesGroup sg : getCurrentProposalForm().getProposal() .getConfig().getServicesRoot().getGroups()) { if (sg.notEmpty(psq)) { //vigrujaem gruppu { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; if (currentRowIndexFrom < 0) { currentRowIndexFrom = currentRowIndex; } Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(sg.getName()); c1.setCellStyle(cs1); if (psq.isExportable(sg.getKey())) { Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(sg.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(sg.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat().getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(sg.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(sg.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ } for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } if (!psq.isExportable(sg.getKey())) { for (Service srv : sg.getServices().values()) { if (srv.notEmpty(psq)) { //System.out.println("\n\nsg.getKey()="+sg.getKey()); //System.out.println("psq.isHidden(sg.getKey())="+psq.isHidden(sg.getKey())); if (psq.isExportable(srv.getKey())) { //vigrujaem service { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(srv.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(srv.getTotalMD(psq)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue( srv.getChargeableMD(psq)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(srv.getTotalOnsiteMD(psq)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(srv.getTotalOnsiteTrips(psq)); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } else { for (com.compassplus.proposalModel.Service inst : psq .getServices().values()) { if (inst.getService().getGroupKey() .equals(sg.getKey()) && inst.getService().getKey() .equals(srv.getKey())) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows( psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles .size() * Math.floor( currentRowIndex / psRowStyles .size()))); currentRowIndex++; Row r = psS.createRow( psRowIndex + i); for (int y = rowStyle .getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell( 0 + psCellIndex); CellStyle cs1 = rowStyle .getCellStyle( 0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(inst.getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell( 1 + psCellIndex); CellStyle cs2 = rowStyle .getCellStyle( 1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( inst.getTotalValue()); c2.setCellStyle(cs2); Cell c3 = r.createCell( 2 + psCellIndex); CellStyle cs3 = rowStyle .getCellStyle( 2 + psCellIndex, wb.createCellStyle()); c3.setCellValue(inst.getCharge() ? inst.getTotalValue() : 0); c3.setCellStyle(cs3); Cell c4 = r.createCell( 3 + psCellIndex); CellStyle cs4 = rowStyle .getCellStyle( 3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula(rCol + (psRowIndex + 1) + "*" + chargeCol + (psRowIndex + currentRowIndex) + "*(1-" + rCol + (psRowIndex + 2) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS .getWorkbook() .createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); /*Cell c5 = r.createCell(4 + psCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + psCellIndex, wb.createCellStyle()); c5.setCellValue(inst.getOnsiteTotalValue()); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + psCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + psCellIndex, wb.createCellStyle()); c6.setCellValue(inst.getTripTotalValue()); c6.setCellStyle(cs6);*/ for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle .getCellStyle(y, null); if (tcs != null) { Cell tc = r .createCell(y); tc.setCellStyle(tcs); } } i++; } } } } } } } } } currentRowIndexTo = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { sTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Services total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellFormula("SUM(" + rCol + (psRowIndex + currentRowIndexFrom) + ":" + rCol + (psRowIndex + currentRowIndexTo) + ")"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFrom) + ":" + totalCol + (psRowIndex + currentRowIndexTo) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } int currentRowIndexFromTC = -1; int currentRowIndexToTC = -1; if (psq.getTrainingCoursesCount() > 0) { { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training course:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue("Cost per person:"); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("Number of participants:"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("$$:"); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } for (TrainingCourse ttc : psq.getTrainingCourses().values()) { if (!ttc.getInclude()) { continue; } if (currentRowIndexFromTC < 0) { currentRowIndexFromTC = currentRowIndex + 1; } if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ttc.getTrainingCourse().getName()); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ttc.getPricePerAttendee() : 0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? ttc.getRegionalPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } currentRowIndexToTC = currentRowIndex; { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { tTotal = currentRowIndex; if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor( currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Training courses total:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("SUM(" + totalCol + (psRowIndex + currentRowIndexFromTC) + ":" + totalCol + (psRowIndex + currentRowIndexToTC) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles .get((int) (currentRowIndex - psRowStyles.size() * Math .floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total cost discount:"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? psq.getPSDiscount() : 0); cs2.setDataFormat( s.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(""); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + psCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + psCellIndex, wb.createCellStyle()); c4.setCellFormula("(1-" + rCol + (psRowIndex + currentRowIndex) + ")*(" + totalCol + (psRowIndex + sTotal + 1) + "+" + totalCol + (psRowIndex + tTotal + 1) + ")"); if (!fSameCurrency) cs4.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); for (int y = 4 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } com.compassplus.proposalModel.OracleQuote oq = getCurrentProposalForm() .getProposal().getOracleQuote(); //Oracle int j = i; i = 0; currentRowIndex = 0; if (isOQ) { int sTotal = -1; int tTotal = -1; int currentRowIndexFrom = -1; int currentRowIndexTo = -1; String rCol = CellReference.convertNumToColString(1 + oCellIndex); String licCol = CellReference.convertNumToColString(2 + oCellIndex); String supCol = CellReference.convertNumToColString(4 + oCellIndex); String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference .convertNumToColString(8 + oCellIndex); String format = (getCurrentProposalForm().getProposal() .getCurrency().getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal() .getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal() .getCurrency().getName() + "\"" : ""); boolean first = true; for (com.compassplus.proposalModel.OracleLicense ol : oq .getOracleLicenses().values()) { if (ol.isMemberOfAnotherBox()) { continue; } if (!first) { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } else { first = false; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(ol.getProductKey()); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue("License price"); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); cs4.setWrapText(true); c4.setCellValue("Options price"); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); cs5.setWrapText(true); c5.setCellValue("Support price"); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); cs6.setWrapText(true); c6.setCellValue("Total price"); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setWrapText(true); c7.setCellValue("Discount"); c7.setCellStyle(cs7); Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); cs8.setWrapText(true); c8.setCellValue("CP price (total)"); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); cs9.setWrapText(true); c9.setCellValue("Customer price"); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); cs10.setWrapText(true); c10.setCellValue("Margin"); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /* * Cell c2 = r.createCell(1 + psCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + psCellIndex, wb.createCellStyle()); c2.setCellValue(!getCurrentProposalForm().getProposal().getConfig().isSalesSupport()?ttc.getPricePerAttendee():0); if (!fSameCurrency) cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat(format)); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + psCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + psCellIndex, wb.createCellStyle()); cs3.setWrapText(true); c3.setCellValue(ttc.getAttendees()); c3.setCellStyle(cs3); * */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("License type"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getOracleLicense().getName()); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getLicensePrice() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOptionsPrice() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? ol.getOracleSupportPrice() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellFormula("SUM(" + licCol + (oRowIndex + currentRowIndex) + ":" + supCol + (oRowIndex + currentRowIndex) + ")"); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); cs7.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(ol.getDiscount()); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + rCol + (oRowIndex + currentRowIndex + 6) + "),1)"); //Customer Price if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellFormula("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); //Customer Price //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellFormula(custCol + (oRowIndex + currentRowIndex) + "-" + cpCol + (oRowIndex + currentRowIndex)); //Customer Price if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle .getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Number of cores"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCores()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Model"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getLicensingModel()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Coefficient"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(ol.getCoefficient().getValue()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Included options"); c1.setCellStyle(cs1); String options = ""; for (String ss : ol.getProduct().getOracleOptions()) { OracleOption op = getCurrentProposalForm().getProposal() .getConfig().getOracleOptions().get(ss); options += ", " + op.getShortName(); } Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue( options.length() > 0 ? options.substring(1) : ""); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Support rate"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); c2.setCellValue(ol.getOracleLicense().getSupportRate()); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; }*/ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Maximum discount"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); cs2.setDataFormat(psS.getWorkbook().createDataFormat() .getFormat("0%;-0%")); c2.setCellValue(ol.getCPDiscount() / 100d); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles.get((int) (currentRowIndex - oRowStyles.size() * Math.floor( currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Shared"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); String sharedString = "Yes, with"; for (String ss : ol.getChildren()) { sharedString += " " + ss + ","; } c2.setCellValue( ol.isShared() ? sharedString.substring(0, sharedString.length() - 1) : "No"); c2.setCellStyle(cs2); for (int y = 2 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } /*{ if (psS.getLastRowNum() >= psRowIndex + i) { psS.shiftRows(psRowIndex + i, psS.getLastRowNum(), 1); } RowStyle rowStyle = psRowStyles.get((int) (currentRowIndex - psRowStyles.size() * Math.floor(currentRowIndex / psRowStyles.size()))); currentRowIndex++; Row r = psS.createRow(psRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + psCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + psCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + psCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue(""); c1.setCellStyle(cs1); for (int y = 1 + psCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } */ { if (oS.getLastRowNum() >= oRowIndex + i) { oS.shiftRows(oRowIndex + i, oS.getLastRowNum(), 1); } RowStyle rowStyle = oRowStyles .get((int) (currentRowIndex - oRowStyles.size() * Math .floor(currentRowIndex / oRowStyles.size()))); currentRowIndex++; Row r = oS.createRow(oRowIndex + i); for (int y = rowStyle.getFirst(); y < 0 + oCellIndex; y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } Cell c1 = r.createCell(0 + oCellIndex); CellStyle cs1 = rowStyle.getCellStyle(0 + oCellIndex, wb.createCellStyle()); cs1.setWrapText(true); c1.setCellValue("Total"); c1.setCellStyle(cs1); Cell c2 = r.createCell(1 + oCellIndex); CellStyle cs2 = rowStyle.getCellStyle(1 + oCellIndex, wb.createCellStyle()); cs2.setWrapText(true); c2.setCellValue(""); c2.setCellStyle(cs2); Cell c3 = r.createCell(2 + oCellIndex); CellStyle cs3 = rowStyle.getCellStyle(2 + oCellIndex, wb.createCellStyle()); c3.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getLicenseTotal() : 0); if (!fSameCurrency) cs3.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c3.setCellStyle(cs3); Cell c4 = r.createCell(3 + oCellIndex); CellStyle cs4 = rowStyle.getCellStyle(3 + oCellIndex, wb.createCellStyle()); c4.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getOptionsTotal() : 0); if (!fSameCurrency) cs4.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c4.setCellStyle(cs4); Cell c5 = r.createCell(4 + oCellIndex); CellStyle cs5 = rowStyle.getCellStyle(4 + oCellIndex, wb.createCellStyle()); c5.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getSupportTotal() : 0); if (!fSameCurrency) cs5.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c5.setCellStyle(cs5); Cell c6 = r.createCell(5 + oCellIndex); CellStyle cs6 = rowStyle.getCellStyle(5 + oCellIndex, wb.createCellStyle()); c6.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getTotalTotal() : 0); //System.out.println("SUM(" + licCol + oRowIndex + ":" + supCol + oRowIndex + ")"); if (!fSameCurrency) cs6.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c6.setCellStyle(cs6); Cell c7 = r.createCell(6 + oCellIndex); CellStyle cs7 = rowStyle.getCellStyle(6 + oCellIndex, wb.createCellStyle()); //cs7.setDataFormat(psS.getWorkbook().createDataFormat().getFormat("0%;-0%")); cs7.setWrapText(true); c7.setCellValue(""); c7.setCellStyle(cs7); /* * String totCol = CellReference.convertNumToColString(5 + oCellIndex); String disCol = CellReference.convertNumToColString(6 + oCellIndex); String cpCol = CellReference.convertNumToColString(7 + oCellIndex); String custCol = CellReference.convertNumToColString(8 + oCellIndex); * */ Cell c8 = r.createCell(7 + oCellIndex); CellStyle cs8 = rowStyle.getCellStyle(7 + oCellIndex, wb.createCellStyle()); c8.setCellValue(!getCurrentProposalForm().getProposal() .getConfig().isSalesSupport() ? oq.getCPTotal() : 0); if (!fSameCurrency) cs8.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c8.setCellStyle(cs8); Cell c9 = r.createCell(8 + oCellIndex); CellStyle cs9 = rowStyle.getCellStyle(8 + oCellIndex, wb.createCellStyle()); c9.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getCustomerTotal() : 0); //System.out.println("CEILING(" + totCol + (oRowIndex + currentRowIndex) + "*(1-" + disCol + (oRowIndex + currentRowIndex) + "),1)"); if (!fSameCurrency) cs9.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c9.setCellStyle(cs9); Cell c10 = r.createCell(9 + oCellIndex); CellStyle cs10 = rowStyle.getCellStyle(9 + oCellIndex, wb.createCellStyle()); c10.setCellValue( !getCurrentProposalForm().getProposal().getConfig() .isSalesSupport() ? oq.getTotalMargin() : 0); if (!fSameCurrency) cs10.setDataFormat(oS.getWorkbook().createDataFormat() .getFormat(format)); c10.setCellStyle(cs10); for (int y = 10 + oCellIndex; y <= rowStyle.getLast(); y++) { CellStyle tcs = rowStyle.getCellStyle(y, null); if (tcs != null) { Cell tc = r.createCell(y); tc.setCellStyle(tcs); } } i++; } } ArrayList<Row> rowsToRemove = new ArrayList(); ScriptEngineManager factory = new ScriptEngineManager(); ScriptEngine engine = factory.getEngineByName("JavaScript"); Bindings bindings = getBindings(getCurrentProposalForm().getProposal(), engine); int dec = 0; int psDec = 0; for (int si = 0; si < wb.getNumberOfSheets(); si++) { Sheet sis = wb.getSheetAt(si); Iterator<Row> riter = sis.rowIterator(); while (riter.hasNext()) { Row row = riter.next(); Iterator<Cell> citer = row.cellIterator(); while (citer.hasNext()) { Cell cell = citer.next(); if (analyzeCell(wb, sis, row, cell, engine, bindings)) { if (sis.getSheetName() == s.getSheetName() && row.getRowNum() < rowIndex) { dec++; } if (sis.getSheetName() == psS.getSheetName() && row.getRowNum() < psRowIndex) { psDec++; } if (!rowsToRemove.contains(row)) { rowsToRemove.add(row); } } } } for (Row ri : rowsToRemove) { removeRow(sis, ri.getRowNum(), wb); } rowsToRemove.clear(); } rowIndex = rowIndex - dec; psRowIndex = psRowIndex - psDec; Row settingsRow = settingsSheet.getRow(0); if (settingsRow == null) { settingsRow = settingsSheet.createRow(0); } CellUtil.createCell(settingsRow, 0, getCurrentProposalForm().getProposal().toString()); CellUtil.createCell(settingsRow, 1, new Integer( getCurrentProposalForm().getProposal().getProducts().size()) .toString()); CellUtil.createCell(settingsRow, 2, cellIndex.toString()); CellUtil.createCell(settingsRow, 3, rowIndex.toString()); CellUtil.createCell(settingsRow, 4, s.getSheetName()); //PS if (isPSQ) { CellUtil.createCell(settingsRow, 5, new Integer(j).toString()); CellUtil.createCell(settingsRow, 6, psCellIndex.toString()); CellUtil.createCell(settingsRow, 7, psRowIndex.toString()); CellUtil.createCell(settingsRow, 8, psS.getSheetName()); } //Oracle if (isOQ) { CellUtil.createCell(settingsRow, 9, new Integer(i).toString()); CellUtil.createCell(settingsRow, 10, oCellIndex.toString()); CellUtil.createCell(settingsRow, 11, oRowIndex.toString()); CellUtil.createCell(settingsRow, 12, oS.getSheetName()); } wb.setSheetHidden(wb.getSheetIndex(settingsSheet), true); OutputStream out = new FileOutputStream(sfile); wb.write(out); out.close(); getCurrentProposalForm().setChanged(false); JOptionPane.showMessageDialog(getRoot(), "Proposal successfully exported", "Result", JOptionPane.INFORMATION_MESSAGE); } catch (Exception exception) { exception.printStackTrace(); JOptionPane.showMessageDialog(getRoot(), "Proposal can't be exported", "Error", JOptionPane.ERROR_MESSAGE); } } else if (value == JOptionPane.CANCEL_OPTION) { dialog.dispose(); } } } catch (Exception exception) { optionPane.setValue(null); } } } } }); dialog.pack(); dialog.setLocationRelativeTo(getRoot()); dialog.setVisible(true); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected void readColumnNames() { // read the schema from the first row or the named row. _headerRange = null;/*from w ww. j a va 2s . c o m*/ Row headerRow = null; if (StringUtilities.isNotNullOrEmpty(_columnNameAddress)) { _headerRange = new ExcelRange(_columnNameAddress); CellReference cr = _headerRange.getStartCell(); headerRow = _sheet.getRow(cr.getRow()); } else { Iterator<Row> iterator = _sheet.iterator(); headerRow = iterator.next(); } _columnCount = 0; int endColumnIndex = (_headerRange == null) ? -1 : _headerRange.getEndColumn(); String value = ""; _columnLabels = new ArrayList<String>(); _columnAddress = new ArrayList<String>(); List<String> usedLabels = new ArrayList<String>(); for (Cell cell : headerRow) { String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); if ((endColumnIndex != -1) && (cell.getColumnIndex() > endColumnIndex)) { break; } CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: value = cell.getStringCellValue(); if (usedLabels.contains(value.toLowerCase())) value = String.format("%s_%s%d", value, columnLetter, cell.getRowIndex() + 1); else usedLabels.add(value.toLowerCase()); _columnLabels.add(value); _columnAddress.add(columnLetter); break; case BOOLEAN: case NUMERIC: case FORMULA: case BLANK: _columnLabels.add(String.format("Column_%s", columnLetter)); _columnAddress.add(columnLetter); break; default: break; } } _columnCount = _columnLabels.size(); _dataSchema = new String[_columnCount][2]; _dataTypes = new DataType[_columnCount]; for (int i = 0; i < _columnCount; i++) { _dataSchema[i][0] = _columnLabels.get(i); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected void readRowSchema(Row dataRow, int endColumnIndex) { String cellAddress = ""; String schemaColumnType = null; String currentCellDataType = null; try {//from w w w .j a v a 2s . c o m for (Cell cell : dataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } currentCellDataType = "String"; CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: currentCellDataType = "String"; break; case BOOLEAN: currentCellDataType = "Boolean"; break; case NUMERIC: currentCellDataType = "Double"; break; case FORMULA: currentCellDataType = "Object"; break; case BLANK: currentCellDataType = "String"; break; default: break; } // Object, String, Numeric, Boolean schemaColumnType = _dataSchema[columnIndex][1]; if (schemaColumnType == null) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Object".equals(schemaColumnType)) { // no change } else if ("String".equals(schemaColumnType) && "Object".equals(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Double".equals(schemaColumnType) && "Object|String".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Boolean".equals(schemaColumnType) && "Object|String|Double".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } _dataTypes[columnIndex] = DataUtilities.dataTypeToEnum(_dataSchema[columnIndex][1]); columnIndex++; } } catch (Exception ex) { throw new PieException(String.format("Error while reading Excel cell %s for its data type (%s). %s", cellAddress, currentCellDataType, ex.getMessage()), ex); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected Object[] readExcelData(Row excelDataRow) { Object[] data = new Object[_columnCount]; String cellAddress = ""; int dataIndex = 0; try {/*from w ww .j a va 2 s . c o m*/ for (Cell cell : excelDataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: data[dataIndex] = cell.getStringCellValue(); break; case BOOLEAN: data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue()) : cell.getBooleanCellValue(); break; case NUMERIC: data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue()) : cell.getNumericCellValue(); break; default: data[dataIndex] = _allTypesStrings ? "" : null; break; } dataIndex++; } if (_addFilename) data[data.length - 1] = _filenameOnly; } catch (Exception ex) { throw new PieException( String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()), ex); } return data; }
From source file:com.fanniemae.ezpie.data.utilities.ExcelRange.java
License:Open Source License
public String getStartColumn() { if (_topLeftCell == null) return "A"; return CellReference.convertNumToColString(_topLeftCell.getCol()); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) { Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloAzul = workbook.createFont(); fontSubTituloAzul.setFontHeightInPoints((short) 8); fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloCaje = workbook.createFont(); fontSubTituloCaje.setFontHeightInPoints((short) 7); fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD); Font font7 = workbook.createFont(); font7.setFontHeightInPoints((short) 6); Font fontTerritorio = workbook.createFont(); fontTerritorio.setItalic(true);/* w w w . ja v a2 s. c o m*/ fontTerritorio.setFontHeightInPoints((short) 12); fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTerritorioTotal1 = workbook.createFont(); fontTerritorioTotal1.setItalic(true); fontTerritorioTotal1.setFontHeightInPoints((short) 8); fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTitulo9 = workbook.createFont(); fontTitulo9.setFontHeightInPoints((short) 8); Font fontTitulo = workbook.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); styleTitulo = workbook.createCellStyle(); styleTitulo.setFont(fontTitulo); styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleTerritorio = workbook.createCellStyle(); styleTerritorio.setFont(fontTerritorio); styleTerritorioTotal1 = workbook.createCellStyle(); styleTerritorioTotal1.setFont(fontTerritorioTotal1); styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitulo9 = workbook.createCellStyle(); styleTitulo9.setFont(fontTitulo9); styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSubTitulo = workbook.createCellStyle(); styleSubTitulo.setFont(fontSubTitulo); styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje = workbook.createCellStyle(); styleSubTituloCaje.setFont(fontSubTituloCaje); styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado = workbook.createCellStyle(); styleMergeCentrado.setFont(fontSubTituloAzul); styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleMergeCentrado.setWrapText(true); styleMergeTotal = workbook.createCellStyle(); styleMergeTotal.setFont(fontSubTitulo); styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND); styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = workbook.createDataFormat(); styleCantidadAzul = workbook.createCellStyle(); styleCantidadAzul.setFont(fontSubTituloAzul); styleCantidadAzul.setDataFormat(format.getFormat("#,##0")); styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCantidadNegrita = workbook.createCellStyle(); styleCantidadNegrita.setFont(fontSubTitulo); styleCantidadNegrita.setDataFormat(format.getFormat("#,##0")); styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris = workbook.createCellStyle(); styleCantidadGris.setFont(fontSubTitulo); styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris.setDataFormat(format.getFormat("#,##0")); styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidad = workbook.createCellStyle(); styleCantidad.setFont(fontTitulo9); styleCantidad.setDataFormat(format.getFormat("#,##0")); styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadGris1 = workbook.createCellStyle(); styleCantidadGris1.setFont(fontTitulo9); styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris1.setDataFormat(format.getFormat("#,##0")); styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadCaje = workbook.createCellStyle(); styleCantidadCaje.setFont(font7); styleCantidadCaje.setDataFormat(format.getFormat("#,##0")); styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFRow rowTitulo = sheet.createRow(0); HSSFCell cellTitulo = rowTitulo.createCell(18); cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS"); cellTitulo.setCellStyle(styleTitulo); HSSFRow rowFecha = sheet.createRow(++indexRow); HSSFCell cellFecha = rowFecha.createCell(18); // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py")); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py")); cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin)); //Calendar calender = Calendar.getInstance(); //cellFecha.setCellValue(calender.get(getInicio())); cellFecha.setCellStyle(styleTitulo); ++indexRow; HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow)); HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow)); HSSFRow rowCabecerauM = sheet.createRow((++indexRow)); rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell0 = rowCabecerauM.createCell(indexCol); cell0.setCellValue("Zona"); cell0.setCellStyle(styleSubTitulo); HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1); cell1.setCellValue("Vendedor"); cell1.setCellStyle(styleSubTitulo); HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2); cell2.setCellValue("Boletas"); cell2.setCellStyle(styleSubTitulo); HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3); HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4); cell3.setCellValue("Palermo Red Box 20"); cell3.setCellStyle(styleMergeCentrado); cell4.setCellValue(""); cell4.setCellStyle(styleMergeCentrado); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow)); HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3); cell3g.setCellValue("Grue"); cell3g.setCellStyle(styleMergeCentrado); HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4); cell3c.setCellValue("Caj"); cell3c.setCellStyle(styleSubTituloCaje); HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow)); cell5.setCellValue("Palermo Blue Box 20"); cell5.setCellStyle(styleMergeCentrado); HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5); cell5g.setCellValue("Grue"); cell5g.setCellStyle(styleMergeCentrado); HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6); cell5c.setCellValue("Caj"); cell5c.setCellStyle(styleSubTituloCaje); HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow)); cell7.setCellValue("Palermo Green Box 20"); cell7.setCellStyle(styleMergeCentrado); HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7); cell7g.setCellValue("Grue"); cell7g.setCellStyle(styleMergeCentrado); HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8); cell7c.setCellValue("Caj"); cell7c.setCellStyle(styleSubTituloCaje); HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow)); cell9.setCellValue("Palermo Tres Box 20"); cell9.setCellStyle(styleMergeCentrado); HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9); cell9g.setCellValue("Grue"); cell9g.setCellStyle(styleMergeCentrado); HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10); cell9c.setCellValue("Caj"); cell9c.setCellStyle(styleSubTituloCaje); HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow)); cell11.setCellValue("Palermo Duo Box 20"); cell11.setCellStyle(styleMergeCentrado); HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11); cell11g.setCellValue("Grue"); cell11g.setCellStyle(styleMergeCentrado); HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12); cell11c.setCellValue("Caj"); cell11c.setCellStyle(styleSubTituloCaje); HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow)); cell13.setCellValue("Palermo Red Box 10"); cell13.setCellStyle(styleMergeCentrado); HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13); cell13g.setCellValue("Grue"); cell13g.setCellStyle(styleMergeCentrado); HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14); cell13c.setCellValue("Caj"); cell13c.setCellStyle(styleSubTituloCaje); HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow)); cell15.setCellValue("Palermo Blue Box 10"); cell15.setCellStyle(styleMergeCentrado); HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15); cell15g.setCellValue("Grue"); cell15g.setCellStyle(styleMergeCentrado); HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16); cell15c.setCellValue("Caj"); cell15c.setCellStyle(styleSubTituloCaje); HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow)); cell17.setCellValue("Palermo Green Box 10"); cell17.setCellStyle(styleMergeCentrado); HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17); cell17g.setCellValue("Grue"); cell17g.setCellStyle(styleMergeCentrado); HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18); cell17c.setCellValue("Caj"); cell17c.setCellStyle(styleSubTituloCaje); HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow)); cell19.setCellValue("Palermo Tres Box 10"); cell19.setCellStyle(styleMergeCentrado); HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19); cell19g.setCellValue("Grue"); cell19g.setCellStyle(styleMergeCentrado); HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20); cell19c.setCellValue("Caj"); cell19c.setCellStyle(styleSubTituloCaje); HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow)); cell21.setCellValue("Palermo Duo Box 10"); cell21.setCellStyle(styleMergeCentrado); HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21); cell21g.setCellValue("Grue"); cell21g.setCellStyle(styleMergeCentrado); HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22); cell21c.setCellValue("Caj"); cell21c.setCellStyle(styleSubTituloCaje); HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow)); cell23.setCellValue("San Marino TYPE Box 20"); cell23.setCellStyle(styleMergeCentrado); HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23); cell23g.setCellValue("Grue"); cell23g.setCellStyle(styleMergeCentrado); HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24); cell23c.setCellValue("Caj"); cell23c.setCellStyle(styleSubTituloCaje); HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow)); cell25.setCellValue("San Marino TYPE Box 10"); cell25.setCellStyle(styleMergeCentrado); HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25); cell25g.setCellValue("Grue"); cell25g.setCellStyle(styleMergeCentrado); HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26); cell25c.setCellValue("Caj"); cell25c.setCellStyle(styleSubTituloCaje); HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow)); cell27.setCellValue("San Marino TYPE Soft"); cell27.setCellStyle(styleMergeCentrado); HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27); cell27g.setCellValue("Grue"); cell27g.setCellStyle(styleMergeCentrado); HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28); cell27c.setCellValue("Caj"); cell27c.setCellStyle(styleSubTituloCaje); HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow)); cell29.setCellValue("Kentucky Box 20"); cell29.setCellStyle(styleMergeCentrado); HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29); cell29g.setCellValue("Grue"); cell29g.setCellStyle(styleMergeCentrado); HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30); cell29c.setCellValue("Caj"); cell29c.setCellStyle(styleSubTituloCaje); HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow)); cell31.setCellValue("Kentucky Box 10"); cell31.setCellStyle(styleMergeCentrado); HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31); cell31g.setCellValue("Grue"); cell31g.setCellStyle(styleMergeCentrado); HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32); cell31c.setCellValue("Caj"); cell31c.setCellStyle(styleSubTituloCaje); HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow)); cell33.setCellValue("Kentucky Soft"); cell33.setCellStyle(styleMergeCentrado); HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33); cell33g.setCellValue("Grue"); cell33g.setCellStyle(styleMergeCentrado); HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34); cell33c.setCellValue("Caj"); cell33c.setCellStyle(styleSubTituloCaje); HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow)); cell35.setCellValue("Total"); cell35.setCellStyle(styleMergeTotal); HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36); cell38.setCellValue(""); cell38.setCellStyle(styleMergeTotal); HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35); cell35g.setCellValue("Gruesas"); cell35g.setCellStyle(styleSubTitulo); HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36); cell35c.setCellValue("Caje"); cell35c.setCellStyle(styleSubTitulo); HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37); cell37c.setCellValue("Cajas"); cell37c.setCellStyle(styleSubTitulo); HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38); cell38g.setCellValue("+gr"); cell38g.setCellStyle(styleSubTitulo); sheet.createFreezePane(2, 7); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal3(HSSFRow rowTotal3, int indexFinGrupo, CellStyle style, HSSFCell cellTerr, HSSFSheet sheet) {// w w w . j a va2 s . c o m HSSFCell cellTotal3Territorio = rowTotal3.createCell(indexCol); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow)); cellTotal3Territorio.setCellValue("Part. De Emb / " + cellTerr.getStringCellValue()); cellTotal3Territorio.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Territorio1 = rowTotal3.createCell(indexCol + 1); cellTotal3Territorio1.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Boletas = rowTotal3.createCell(indexCol + 2); cellTotal3Boletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Red20g = rowTotal3.createCell(indexCol + 3); cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Red20g.setCellStyle(style); HSSFCell cellTotal3Red20c = rowTotal3.createCell(indexCol + 4); cellTotal3Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Blue20g = rowTotal3.createCell(indexCol + 5); cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Blue20g.setCellStyle(style); HSSFCell cellTotal3Blue20c = rowTotal3.createCell(indexCol + 6); cellTotal3Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Green20g = rowTotal3.createCell(indexCol + 7); cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Green20g.setCellStyle(style); HSSFCell cellTotal3Green20c = rowTotal3.createCell(indexCol + 8); cellTotal3Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Tres20g = rowTotal3.createCell(indexCol + 9); cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Tres20g.setCellStyle(style); HSSFCell cellTotal3Tres20c = rowTotal3.createCell(indexCol + 10); cellTotal3Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Duo20g = rowTotal3.createCell(indexCol + 11); cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Duo20g.setCellStyle(style); HSSFCell cellTotal3Duo20c = rowTotal3.createCell(indexCol + 12); cellTotal3Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Red10g = rowTotal3.createCell(indexCol + 13); cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Red10g.setCellStyle(style); HSSFCell cellTotal3Red10c = rowTotal3.createCell(indexCol + 14); cellTotal3Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Blue10g = rowTotal3.createCell(indexCol + 15); cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Blue10g.setCellStyle(style); HSSFCell cellTotal3Blue10c = rowTotal3.createCell(indexCol + 16); cellTotal3Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Green10g = rowTotal3.createCell(indexCol + 17); cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Green10g.setCellStyle(style); HSSFCell cellTotal3Green10c = rowTotal3.createCell(indexCol + 18); cellTotal3Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Tres10g = rowTotal3.createCell(indexCol + 19); cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Tres10g.setCellStyle(style); HSSFCell cellTotal3Tres10c = rowTotal3.createCell(indexCol + 20); cellTotal3Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Duo10g = rowTotal3.createCell(indexCol + 21); cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Duo10g.setCellStyle(style); HSSFCell cellTotal3Duo10c = rowTotal3.createCell(indexCol + 22); cellTotal3Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SM20g = rowTotal3.createCell(indexCol + 23); cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SM20g.setCellStyle(style); HSSFCell cellTotal3SM20c = rowTotal3.createCell(indexCol + 24); cellTotal3SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SM10g = rowTotal3.createCell(indexCol + 25); cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SM10g.setCellStyle(style); HSSFCell cellTotal3SM10c = rowTotal3.createCell(indexCol + 26); cellTotal3SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SMSoftg = rowTotal3.createCell(indexCol + 27); cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SMSoftg.setCellStyle(style); HSSFCell cellTotal3SMSoftc = rowTotal3.createCell(indexCol + 28); cellTotal3SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3K20g = rowTotal3.createCell(indexCol + 29); cellTotal3K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3K20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3K20g.setCellStyle(style); HSSFCell cellTotal3K20c = rowTotal3.createCell(indexCol + 30); cellTotal3K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3K10g = rowTotal3.createCell(indexCol + 31); cellTotal3K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3K10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3K10g.setCellStyle(style); HSSFCell cellTotal3K10c = rowTotal3.createCell(indexCol + 32); cellTotal3K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3KSoftg = rowTotal3.createCell(indexCol + 33); cellTotal3KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3KSoftg.setCellStyle(style); HSSFCell cellTotal3KSoftc = rowTotal3.createCell(indexCol + 34); cellTotal3KSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3g = rowTotal3.createCell(indexCol + 35); cellTotal3g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3g.setCellFormula(CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3g.setCellStyle(style); HSSFCell cellTotal3c = rowTotal3.createCell(indexCol + 36); cellTotal3c.setCellStyle(styleCantidadGris); HSSFCell cellTotal3caja = rowTotal3.createCell(indexCol + 37); cellTotal3caja.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal3gr = rowTotal3.createCell(indexCol + 38); cellTotal3gr.setCellStyle(styleCantidadNegrita); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal2(HSSFRow rowTotal2, int indexFinGrupo, String[] aColsGrue, HSSFCell cellTerr) { HSSFCell cellTotal2Territorio = rowTotal2.createCell(indexCol); cellTotal2Territorio.setCellValue("Total " + cellTerr.getStringCellValue() + " - Cajas"); cellTotal2Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal2Boletas = rowTotal2.createCell(indexCol + 2); cellTotal2Boletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20g = rowTotal2.createCell(indexCol + 3); cellTotal2Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red20g/*from w w w . j av a2 s .c om*/ .setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/50"); cellTotal2Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20c = rowTotal2.createCell(indexCol + 4); cellTotal2Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20g = rowTotal2.createCell(indexCol + 5); cellTotal2Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue20g .setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20c = rowTotal2.createCell(indexCol + 6); cellTotal2Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20g = rowTotal2.createCell(indexCol + 7); cellTotal2Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green20g .setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/50"); cellTotal2Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20c = rowTotal2.createCell(indexCol + 8); cellTotal2Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20g = rowTotal2.createCell(indexCol + 9); cellTotal2Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres20g .setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20c = rowTotal2.createCell(indexCol + 10); cellTotal2Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20g = rowTotal2.createCell(indexCol + 11); cellTotal2Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo20g .setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20c = rowTotal2.createCell(indexCol + 12); cellTotal2Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10g = rowTotal2.createCell(indexCol + 13); cellTotal2Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red10g .setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/50"); cellTotal2Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10c = rowTotal2.createCell(indexCol + 14); cellTotal2Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10g = rowTotal2.createCell(indexCol + 15); cellTotal2Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue10g .setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10c = rowTotal2.createCell(indexCol + 16); cellTotal2Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10g = rowTotal2.createCell(indexCol + 17); cellTotal2Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green10g .setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/50"); cellTotal2Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10c = rowTotal2.createCell(indexCol + 18); cellTotal2Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10g = rowTotal2.createCell(indexCol + 19); cellTotal2Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres10g .setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10c = rowTotal2.createCell(indexCol + 20); cellTotal2Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10g = rowTotal2.createCell(indexCol + 21); cellTotal2Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo10g .setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10c = rowTotal2.createCell(indexCol + 22); cellTotal2Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20g = rowTotal2.createCell(indexCol + 23); cellTotal2SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM20g .setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/50"); cellTotal2SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20c = rowTotal2.createCell(indexCol + 24); cellTotal2SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10g = rowTotal2.createCell(indexCol + 25); cellTotal2SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM10g .setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/50"); cellTotal2SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10c = rowTotal2.createCell(indexCol + 26); cellTotal2SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftg = rowTotal2.createCell(indexCol + 27); cellTotal2SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SMSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/50"); cellTotal2SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftc = rowTotal2.createCell(indexCol + 28); cellTotal2SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20g = rowTotal2.createCell(indexCol + 29); cellTotal2K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K20g .setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/50"); cellTotal2K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20c = rowTotal2.createCell(indexCol + 30); cellTotal2K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10g = rowTotal2.createCell(indexCol + 31); cellTotal2K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K10g .setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/50"); cellTotal2K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10c = rowTotal2.createCell(indexCol + 32); cellTotal2K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2KSoftg = rowTotal2.createCell(indexCol + 33); cellTotal2KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2KSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/50"); cellTotal2KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal22KSoftc = rowTotal2.createCell(indexCol + 34); cellTotal22KSoftc.setCellStyle(styleCantidadAzul); String formTotal2Cajas = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal2Cajas += aColsGrue[i] + (indexFinGrupo + 2) + "+"; } formTotal2Cajas += "0"; HSSFCell cellTotal2g = rowTotal2.createCell(indexCol + 35); cellTotal2g.setCellStyle(styleCantidadGris); HSSFCell cellTotal2c = rowTotal2.createCell(indexCol + 36); cellTotal2c.setCellStyle(styleCantidadGris); HSSFCell cellTotal2caja = rowTotal2.createCell(indexCol + 37); cellTotal2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2caja.setCellFormula(formTotal2Cajas); cellTotal2caja.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal2gr = rowTotal2.createCell(indexCol + 38); cellTotal2gr.setCellStyle(styleCantidadNegrita); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal1(HSSFRow rowTotal1, int indexInicioGrupo, int indexFinGrupo, String[] aColsGrue, String[] aCols20, String[] aCols10, HSSFCell cellTerr) { HSSFCell cellTotal1Territorio = rowTotal1.createCell(indexCol); cellTotal1Territorio.setCellValue("Total " + cellTerr.getStringCellValue()); cellTotal1Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal1Boleta = rowTotal1.createCell(indexCol + 2); cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Boleta.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 2) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 2) + indexFinGrupo + ")"); cellTotal1Boleta.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20g = rowTotal1.createCell(indexCol + 3); cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 3) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 3) + indexFinGrupo + ")"); cellTotal1Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20c = rowTotal1.createCell(indexCol + 4); cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 4) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 4) + indexFinGrupo + ")"); cellTotal1Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20g = rowTotal1.createCell(indexCol + 5); cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 5) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 5) + indexFinGrupo + ")"); cellTotal1Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20c = rowTotal1.createCell(indexCol + 6); cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 6) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 6) + indexFinGrupo + ")"); cellTotal1Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20g = rowTotal1.createCell(indexCol + 7); cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 7) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 7) + indexFinGrupo + ")"); cellTotal1Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20c = rowTotal1.createCell(indexCol + 8); cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 8) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 8) + indexFinGrupo + ")"); cellTotal1Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20g = rowTotal1.createCell(indexCol + 9); cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 9) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 9) + indexFinGrupo + ")"); cellTotal1Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20c = rowTotal1.createCell(indexCol + 10); cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20c/*from ww w.j av a 2 s. c o m*/ .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 10) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 10) + indexFinGrupo + ")"); cellTotal1Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20g = rowTotal1.createCell(indexCol + 11); cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 11) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 11) + indexFinGrupo + ")"); cellTotal1Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20c = rowTotal1.createCell(indexCol + 12); cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 12) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 12) + indexFinGrupo + ")"); cellTotal1Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10g = rowTotal1.createCell(indexCol + 13); cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 13) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 13) + indexFinGrupo + ")"); cellTotal1Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10c = rowTotal1.createCell(indexCol + 14); cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 14) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 14) + indexFinGrupo + ")"); cellTotal1Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10g = rowTotal1.createCell(indexCol + 15); cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 15) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 15) + indexFinGrupo + ")"); cellTotal1Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10c = rowTotal1.createCell(indexCol + 16); cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 16) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 16) + indexFinGrupo + ")"); cellTotal1Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10g = rowTotal1.createCell(indexCol + 17); cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 17) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 17) + indexFinGrupo + ")"); cellTotal1Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10c = rowTotal1.createCell(indexCol + 18); cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 18) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 18) + indexFinGrupo + ")"); cellTotal1Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10g = rowTotal1.createCell(indexCol + 19); cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 19) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 19) + indexFinGrupo + ")"); cellTotal1Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10c = rowTotal1.createCell(indexCol + 20); cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 20) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 20) + indexFinGrupo + ")"); cellTotal1Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10g = rowTotal1.createCell(indexCol + 21); cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 21) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 21) + indexFinGrupo + ")"); cellTotal1Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10c = rowTotal1.createCell(indexCol + 22); cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 22) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 22) + indexFinGrupo + ")"); cellTotal1Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20g = rowTotal1.createCell(indexCol + 23); cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 23) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 23) + indexFinGrupo + ")"); cellTotal1SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20c = rowTotal1.createCell(indexCol + 24); cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 24) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 24) + indexFinGrupo + ")"); cellTotal1SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10g = rowTotal1.createCell(indexCol + 25); cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 25) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 25) + indexFinGrupo + ")"); cellTotal1SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10c = rowTotal1.createCell(indexCol + 26); cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 26) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 26) + indexFinGrupo + ")"); cellTotal1SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftg = rowTotal1.createCell(indexCol + 27); cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 27) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 27) + indexFinGrupo + ")"); cellTotal1SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftc = rowTotal1.createCell(indexCol + 28); cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 28) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 28) + indexFinGrupo + ")"); cellTotal1SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20g = rowTotal1.createCell(indexCol + 29); cellTotal1K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 29) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 29) + indexFinGrupo + ")"); cellTotal1K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20c = rowTotal1.createCell(indexCol + 30); cellTotal1K20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 30) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 30) + indexFinGrupo + ")"); cellTotal1K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10g = rowTotal1.createCell(indexCol + 31); cellTotal1K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 31) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 31) + indexFinGrupo + ")"); cellTotal1K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10c = rowTotal1.createCell(indexCol + 32); cellTotal1K10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 32) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 32) + indexFinGrupo + ")"); cellTotal1K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftg = rowTotal1.createCell(indexCol + 33); cellTotal1KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 33) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 33) + indexFinGrupo + ")"); cellTotal1KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftc = rowTotal1.createCell(indexCol + 34); cellTotal1KSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 34) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 34) + indexFinGrupo + ")"); cellTotal1KSoftc.setCellStyle(styleCantidadAzul); String formTotal1Grue = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal1Grue += aColsGrue[i] + (indexFinGrupo + 1) + "+"; } formTotal1Grue += "0"; String formTotal120 = ""; for (int i = 0; i < aCols20.length; i++) { formTotal120 += aCols20[i] + (indexFinGrupo + 1) + "+"; } formTotal120 += "0"; String formTotal110 = ""; for (int i = 0; i < aCols10.length; i++) { formTotal110 += aCols10[i] + (indexFinGrupo + 1) + "+"; } formTotal110 += "0"; HSSFCell cellTotal1g = rowTotal1.createCell(indexCol + 35); cellTotal1g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1g.setCellFormula( "(INT((" + formTotal120 + ")/10))+" + "(INT((" + formTotal110 + ")/20)) +(" + formTotal1Grue + ")"); cellTotal1g.setCellStyle(styleCantidadGris); HSSFCell cellTotal1c = rowTotal1.createCell(indexCol + 36); cellTotal1c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1c.setCellFormula("MOD((" + formTotal110 + "),20)+ MOD((" + formTotal120 + "),10)"); cellTotal1c.setCellStyle(styleCantidadGris); HSSFCell cellTotal1ca = rowTotal1.createCell(indexCol + 37); cellTotal1ca.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1ca.setCellFormula( "(INT(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/50))"); cellTotal1ca.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal1gr = rowTotal1.createCell(indexCol + 38); cellTotal1gr.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1gr.setCellFormula( "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + ",50))"); cellTotal1gr.setCellStyle(styleCantidadNegrita); }