List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetIndex
@Override public int getSheetIndex(org.apache.poi.ss.usermodel.Sheet sheet)
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
/** * Reads the configuration parameters from the specified worksheet in the * Xcel spreadsheet. // ww w . j av a 2 s . c om * * @param arglist - the arguments of the spreadsheet to read, such as file * name, sheet, and columns to read. * @param forRegistrar - a flag stating whether to use the hashKey as the key * in the properties table or the value of the IP property * @param forRemoval - whether this is being added or removed from the * properties table. * * @return - returns the Network Element Label this properties were saved under or * null. */ @SuppressWarnings("deprecation") private Hashtable<String, Properties> readConfigParameters(String[] arglist) { POIFSFileSystem fs = null; HSSFWorkbook wb = null; int xlsSheetIndex = 0; ArrayList<Object> hashKeyList = new ArrayList<Object>(); String hashKey = null; String propertyKey = null; String propertyValue = null; Hashtable<String, Properties> table = null; // verify execution syntax - make sure proper number of parameters are passed in if (arglist.length != 5) { logger.trace(PC2LogCategory.Settings, subCat, "Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); //System.out.print("Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); return table; } String xlsFileName = arglist[0]; String xlsSheetName = arglist[1]; String xlsHashKeyColumn = arglist[2]; String xlsPropertyKeyColumn = arglist[3]; String xlsPropertyValueColumn = arglist[4]; logger.trace(PC2LogCategory.Settings, subCat, "Excel File Name is: " + xlsFileName + "\nExcel Sheet Name is: " + xlsSheetName + "\nExcel Key Column is: " + xlsHashKeyColumn + "\nExcel Field Name Column is: " + xlsPropertyKeyColumn + "\nExcel Value Column is: " + xlsPropertyValueColumn); // use POI to read the excel file try { fs = new POIFSFileSystem(new FileInputStream(xlsFileName)); logger.trace(PC2LogCategory.Settings, subCat, "FS= " + fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to read the file named " + xlsFileName); } ; // read the workbook try { wb = new HSSFWorkbook(fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to create a workbook"); } ; try { xlsSheetIndex = wb.getSheetIndex(xlsSheetName); HSSFSheet sheet = wb.getSheetAt(xlsSheetIndex); HSSFRow row = null; HSSFCell cell = null; boolean formatKnown = false; table = new Hashtable<String, Properties>(); int initialRow = 0; if (sheet.getRow(0) == null) //|| sheet.getRow(0).getCell(Short.decode("0")) != null) initialRow = 1; int rows = sheet.getLastRowNum(); for (int r = initialRow; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { if (xlsHashKeyColumn != null) { cell = row.getCell(Short.decode(xlsHashKeyColumn)); // Support the original format of the settings files where the network element is in column 1 instead of zero if (!formatKnown && cell == null && xlsHashKeyColumn.equals("0")) { xlsHashKeyColumn = "1"; xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; cell = row.getCell(Short.decode(xlsHashKeyColumn)); } formatKnown = true; } else if (!formatKnown) { hashKey = SettingConstants.DUT; cell = row.getCell(Short.decode("0")); // Support the original format of the settings files where the network element is in column 1 instead of zero cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); if (cell == null || propertyKey.startsWith("Step 1")) { xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; } cell = null; formatKnown = true; } if (cell != null) { hashKey = getXlsCellStringValue(cell); } if ((hashKey != null) && (hashKey != "")) { if (!hashKeyList.contains(hashKey)) { hashKeyList.add(hashKey); table.put(hashKey, new Properties()); } } Properties p = null; if (hashKey != null) p = table.get(hashKey); if (p != null) { cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); cell = row.getCell(Short.decode(xlsPropertyValueColumn)); propertyValue = getXlsCellStringValue(cell); if (propertyKey.equals("MAC Address")) { p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); } // PCPCSII-125 // Create a colon verions of each MAC Address in the configuration files if (propertyKey.contains("MAC Address")) { String value = createMACAddressWithColons(propertyValue); p.setProperty(propertyKey + " Colon", value); } // Before putting the key/value pair into the property class, // (except for the LinkLocalAddress property), // see if it is an IP property and if so determine if the // value is an IPv6 address using the compressed form. if (propertyKey.contains(SettingConstants.IP) && propertyValue.contains(SettingConstants.IP_COMP_FORM)) { try { propertyValue = Conversion.ipv6LongForm(propertyValue); } catch (IllegalArgumentException iae) { logger.error(PC2LogCategory.Settings, subCat, hashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue + ". Detected an invalid IPv6 address."); } } if (propertyKey != null && propertyValue != null && propertyKey != "" && propertyValue != "") p.setProperty(propertyKey, propertyValue); cell = null; logger.trace(PC2LogCategory.Settings, subCat, hashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); } } } for (int q = 0; q < hashKeyList.size(); q++) { String currentHashKey = hashKeyList.get(q).toString(); Properties p = table.get(currentHashKey); addDeviceType(currentHashKey, p); // key = currentHashKey; if (currentHashKey.equals("DUT")) { String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); if (deviceType != null) { table.put(deviceType + "0", p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + deviceType + "0 to system settings."); } } else if (currentHashKey.startsWith("UE")) { String sim = p.getProperty(SettingConstants.SIMULATED); if (sim != null && (sim.equalsIgnoreCase("false") || sim.equalsIgnoreCase("no") || sim.equalsIgnoreCase("disable"))) { String pui = p.getProperty(SettingConstants.PUI); String pui2 = p.getProperty(SettingConstants.PUI2); if (pui != null) { table.put(pui, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui + " to system settings."); } if (pui2 != null && !pui2.equals(pui)) { table.put(pui2, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui2 + " to system settings."); } } } } // table = new Hashtable<String, Properties>(); // // for (int q = 0; q < hashKeyList.size(); q++) { // // String currentHashKey = hashKeyList.get(q).toString(); // logger.trace(PC2LogCategory.Settings, subCat, // "****** OK hashKey(q) = " + hashKeyList.get(q) + " ******"); // logger.trace(PC2LogCategory.Settings, subCat, // "Loop " + q); // Properties p = new Properties(); // for (int r = 0; r <= rows; r++) { // row = sheet.getRow(r); // if (row != null) { // if (xlsHashKeyColumn != null) { // cell = row.getCell(Short.decode(xlsHashKeyColumn)); // } // if (cell != null) { // hashKey = getXlsCellStringValue(cell); // } // else { // hashKey = SettingConstants.DUT; // } // if (hashKey == hashKeyList.get(q)) { // cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); // propertyKey = getXlsCellStringValue(cell); // cell = row.getCell(Short.decode(xlsPropertyValueColumn)); // propertyValue = getXlsCellStringValue(cell); // // if (propertyKey.equals("MAC Address")) { // p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); // } // // PCPCSII-125 // // Create a colon verions of each MAC Address in the configuration files // if (propertyKey.contains("MAC Address")) { // String value = createMACAddressWithColons(propertyValue); // p.setProperty(propertyKey + " Colon", value); // } // // // Before putting the key/value pair into the property class, // // (except for the LinkLocalAddress property), // // see if it is an IP property and if so determine if the // // value is an IPv6 address using the compressed form. // if (propertyKey.contains(SettingConstants.IP) && // propertyValue.contains(SettingConstants.IP_COMP_FORM)) { // try { // propertyValue = Conversion.ipv6LongForm(propertyValue); // } // catch (IllegalArgumentException iae) { // logger.error(PC2LogCategory.Settings, subCat, // currentHashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue // + ". Detected an invalid IPv6 address."); // } // // } // // p.setProperty(propertyKey, propertyValue); // logger.trace(PC2LogCategory.Settings, subCat, // currentHashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); // } // // } // } // // if (!(currentHashKey.equals("Network Element Label") || // currentHashKey.equals("Label"))){ // addDeviceType(currentHashKey, p); // table.put(currentHashKey, p); // // key = currentHashKey; // if (currentHashKey.equals("DUT")) { // String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); // if (deviceType != null) { // table.put(deviceType+"0", p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + deviceType + "0 to system settings."); // // } // } // else if (currentHashKey.startsWith("UE")) { // String sim = p.getProperty(SettingConstants.SIMULATED); // if (sim != null && // (sim.equalsIgnoreCase("false") || // sim.equalsIgnoreCase("no")|| // sim.equalsIgnoreCase("disable"))) { // String pui = p.getProperty(SettingConstants.PUI); // String pui2 = p.getProperty(SettingConstants.PUI2); // if (pui != null) { // table.put(pui, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui + " to system settings."); // // } // if (pui2 != null && !pui2.equals(pui)) { // table.put(pui2, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui2 + " to system settings."); // } // } // } // } // // } } catch (Exception e) { logger.error(PC2LogCategory.Settings, subCat, "Check xls workbook name, sheet name, and column parameters."); e.printStackTrace(); } return table; }
From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java
License:Open Source License
public void actionPerformed(ActionEvent event) { String command = event.getActionCommand(); if (command.equals("UseHeaderRows")) //$NON-NLS-1$ {//from www . j a va 2 s .c o m if (tableModel != null) tableModel.setUseHeaderRow(headerRows.isSelected()); } else if (event.getSource() == sheetSelect) { if (sheetSelect.getSelectedItem() != null) { HSSFWorkbook wb = (HSSFWorkbook) state.getProperty("workbook"); //$NON-NLS-1$ HSSFSheet sheet = wb.getSheetAt(wb.getSheetIndex(sheetSelect.getSelectedItem().toString())); tableModel = new SheetTableModel(sheet); tableModel.setUseHeaderRow(headerRows.isSelected()); table.setModel(tableModel); } } table.sizeColumnsToFit(JTable.AUTO_RESIZE_ALL_COLUMNS); revalidate(); repaint(); }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) { int i, columnIndex; ArrayList<DictionaryDO> tempQcColumns; DictionaryDO dict;// w w w . jav a 2s . com HashSet<Integer> emptyColumns; Name rangeName; Row row; String rangeFormula; if (qcColumns != null && !qcColumns.isEmpty()) row = sheet.getRow(32); else row = sheet.getRow(3); emptyColumns = new HashSet<Integer>(); for (i = 0; i < row.getLastCellNum(); i++) { if (i >= maxChars.size() || maxChars.get(i) == 0) emptyColumns.add(i); } setHeaderCells(sheet, qcName, qcType, sheetName); if (qcColumns != null && !qcColumns.isEmpty()) { tempQcColumns = new ArrayList<DictionaryDO>(); tempQcColumns.addAll(qcColumns); for (i = tempQcColumns.size() - 1; i > -1; i--) { if (emptyColumns.contains(i + 5)) { tempQcColumns.remove(i); removeColumn(sheet, i + 5); maxChars.remove(i + 5); } } rangeName = getName(wb, sheet, "RowNumber"); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName("RowNumber"); } rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$" + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); /* * Create named ranges for the graph to be able to locate the appropriate * data */ columnIndex = 5; for (i = 0; i < tempQcColumns.size(); i++) { dict = tempQcColumns.get(i); if (!DataBaseUtil.isEmpty(dict.getCode())) { rangeName = getName(wb, sheet, dict.getCode()); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName(dict.getCode()); } rangeFormula = rangeName.getRefersToFormula(); if (rangeFormula != null && rangeFormula.length() > 0 && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1))) rangeFormula += ","; else rangeFormula = ""; rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex) + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); } columnIndex++; } /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 5; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) { /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } wb.setSheetName(wb.getSheetIndex(sheet), sheetName); sheet.setForceFormulaRecalculation(true); maxChars.clear(); }
From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java
public static void addToDestination(HSSFWorkbook source, HSSFWorkbook destination) { ArrayList<String> listOfSheet = getListOfSheets(destination); int numberOfSheet = source.getNumberOfSheets(); int j = 0;//from w w w. ja va 2 s .c o m int sheetDestinationIndex; for (int i = 0; i < numberOfSheet; i++) { Sheet sheetSource = source.getSheetAt(i); String newName = sheetSource.getSheetName(); sheetDestinationIndex = destination.getSheetIndex(newName); while (listOfSheet.contains(newName)) { newName = sheetSource.getSheetName() + "_" + j; newName = newName.substring(Math.max(newName.length() - 31, 0), Math.min(newName.length(), 31)); sheetDestinationIndex = destination.getSheetIndex(newName); j++; } listOfSheet.add(newName); Sheet sheetDestination = destination.createSheet(newName); copyContent(sheetSource, sheetDestination); } }