List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.bizosys.dataservice.dao.WriteToXls.java
License:Apache License
public void write(List<Object[]> records) throws Exception { Workbook workbook = getWorkbook();/*from w ww . j a v a 2 s . co m*/ Sheet sheet = workbook.createSheet(); if (null != templateFile) { File templateFileObject = new File(templateFile); if (templateFileObject.exists()) { Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject)); Sheet templatesheet = templateWorkbook.getSheetAt(0); Iterator<Row> rowIterator = templatesheet.iterator(); while (rowIterator.hasNext()) { Row templateRow = rowIterator.next(); Row row = sheet.createRow(startRowIndex++); Iterator<Cell> cellIterator = templateRow.cellIterator(); while (cellIterator.hasNext()) { Cell templateCell = cellIterator.next(); Cell cell = row.createCell(templateCell.getColumnIndex()); cell.setCellType(templateCell.getCellType()); switch (templateCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(templateCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cell.setCellValue(templateCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellValue(templateCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(templateCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(templateCell.getStringCellValue()); break; } } } } else { System.err.println("Can not read " + templateFileObject.getAbsolutePath()); } } for (Object[] cols : records) { createRecord(cols, sheet); } workbook.write(out); }
From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java
License:Apache License
public String getFileContentString(File f) { int i, j, j2, k; short k2;//from w ww . j a v a 2 s . co m HSSFWorkbook wb = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; InputStream is = null; String cell_string = ""; try { is = new FileInputStream(f); POIFSFileSystem fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); for (i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); j2 = 0; for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { while ((row = sheet.getRow(j2)) == null) { j2++; } j2++; k2 = 0; for (k = 0; k < row.getPhysicalNumberOfCells(); k++) { while ((cell = row.getCell(k2)) == null) { k2++; } k2++; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: cell_string = cell_string + cell.getNumericCellValue() + "\t"; break; case HSSFCell.CELL_TYPE_STRING: cell_string = cell_string + cell.getStringCellValue() + "\t"; break; default: } } cell_string = cell_string + "\n"; } cell_string = cell_string + "\n\n\n"; } is.close(); } catch (Exception fe) { cell_string = null; if (is != null) { try { is.close(); } catch (Exception ise) { } } } return cell_string; }
From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java
public /*static*/ void leXLS(String filename) throws IOException { System.out.println("Mtodo leXLS chamado!"); FileInputStream fileInputStream = new FileInputStream(filename); try {/* w w w. j a v a 2 s. co m*/ //Obtem acesso pasta de trabalho wb = new HSSFWorkbook(fileInputStream); //Obtem acesso planilha Plan1 HSSFSheet s = wb.getSheet("Plan1"); Iterator<Row> rowIterator = s.rowIterator(); while (rowIterator.hasNext()) { System.out.println("Nova linha!"); //Obtem acesso a cada linha de Plan1 Row linha = rowIterator.next(); Iterator<Cell> cellIterator = linha.cellIterator(); while (cellIterator.hasNext()) { //Obtem acesso a cada clula de cada linha de Plan1 Cell celula = cellIterator.next(); //System.out.println(celula.getStringCellValue()); //Adiciona o valor de cada clula ao ArrayList que ser passado a DAO dados.add(celula.getStringCellValue()); // } //Chamada ao mtodo do BD que recebe o ArrayList (Deve estar em DAO) new TesteInsereBD().insereLinha(dados, dados.size()); //Limpa o ArrayLista para preench-lo novamente dados.clear(); } //Corrigir este catch com algo mais eficiente } catch (IOException ex) { System.out.println("Teste"); } }
From source file:com.bright.json.PGS.java
License:Open Source License
public static void main(String[] args) throws FileNotFoundException { String fileBasename = null;//from ww w .j a v a2s. c o m JFileChooser chooser = new JFileChooser(); try { FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel Spreadsheets", "xls", "xlsx"); chooser.setFileFilter(filter); chooser.setCurrentDirectory(new java.io.File(System.getProperty("user.home"))); chooser.setDialogTitle("Select the Excel file"); chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) { System.out.println("getCurrentDirectory(): " + chooser.getCurrentDirectory()); System.out.println("getSelectedFile() : " + chooser.getSelectedFile()); // String fileBasename = // chooser.getSelectedFile().toString().substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator)+1,chooser.getSelectedFile().toString().lastIndexOf(".")); fileBasename = chooser.getSelectedFile().toString() .substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator) + 1); System.out.println("Base name: " + fileBasename); } else { System.out.println("No Selection "); } } catch (Exception e) { System.out.println(e.toString()); } String fileName = chooser.getSelectedFile().toString(); InputStream inp = new FileInputStream(fileName); Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { try { workbook = new XSSFWorkbook(inp); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } else if (fileName.toLowerCase().endsWith("xls")) { try { workbook = new HSSFWorkbook(inp); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } Sheet nodeSheet = workbook.getSheet("Devices"); Sheet interfaceSheet = workbook.getSheet("Interfaces"); System.out.println("Read nodes sheet."); // Row nodeRow = nodeSheet.getRow(1); // System.out.println(row.getCell(0).toString()); // System.exit(0); JTextField uiHost = new JTextField("demo.brightcomputing.com"); // TextPrompt puiHost = new // TextPrompt("demo.brightcomputing.com",uiHost); JTextField uiUser = new JTextField("root"); // TextPrompt puiUser = new TextPrompt("root", uiUser); JTextField uiPass = new JPasswordField(""); // TextPrompt puiPass = new TextPrompt("x5deix5dei", uiPass); JPanel myPanel = new JPanel(new GridLayout(5, 1)); myPanel.add(new JLabel("Bright HeadNode hostname:")); myPanel.add(uiHost); // myPanel.add(Box.createHorizontalStrut(1)); // a spacer myPanel.add(new JLabel("Username:")); myPanel.add(uiUser); myPanel.add(new JLabel("Password:")); myPanel.add(uiPass); int result = JOptionPane.showConfirmDialog(null, myPanel, "Please fill in all the fields.", JOptionPane.OK_CANCEL_OPTION); if (result == JOptionPane.OK_OPTION) { System.out.println("Input received."); } String rhost = uiHost.getText(); String ruser = uiUser.getText(); String rpass = uiPass.getText(); String cmURL = "https://" + rhost + ":8081/json"; List<Cookie> cookies = doLogin(ruser, rpass, cmURL); chkVersion(cmURL, cookies); Map<String, Long> categories = UniqueKeyMap(cmURL, "cmdevice", "getCategories", cookies); Map<String, Long> networks = UniqueKeyMap(cmURL, "cmnet", "getNetworks", cookies); Map<String, Long> partitions = UniqueKeyMap(cmURL, "cmpart", "getPartitions", cookies); Map<String, Long> racks = UniqueKeyMap(cmURL, "cmpart", "getRacks", cookies); Map<String, Long> switches = UniqueKeyMap(cmURL, "cmdevice", "getEthernetSwitches", cookies); // System.out.println(switches.get("switch01")); // System.out.println("Size of the map: "+ switches.size()); // System.exit(0); cmDevice newnode = new cmDevice(); cmDevice.deviceObject devObj = new cmDevice.deviceObject(); cmDevice.switchObject switchObj = new cmDevice.switchObject(); // cmDevice.netObject netObj = new cmDevice.netObject(); List<String> emptyslist = new ArrayList<String>(); // Row nodeRow = nodeSheet.getRow(1); // Row ifRow = interfaceSheet.getRow(1); // System.out.println(nodeRow.getCell(0).toString()); // nodeRow.getCell(3).getStringCellValue() Map<String, ArrayList<cmDevice.netObject>> ifmap = new HashMap<String, ArrayList<cmDevice.netObject>>(); // Map<String,netObject> helperMap = new HashMap<String,netObject>(); // Iterator<Row> rows = interfaceSheet.rowIterator (); // while (rows. < interfaceSheet.getPhysicalNumberOfRows()) { // List<netObject> netList = new ArrayList<netObject>(); for (int i = 0; i < interfaceSheet.getPhysicalNumberOfRows(); i++) { Row ifRow = interfaceSheet.getRow(i); if (ifRow.getRowNum() == 0) { continue; // just skip the rows if row number is 0 } System.out.println("Row nr: " + ifRow.getRowNum()); cmDevice.netObject netObj = new cmDevice.netObject(); ArrayList<cmDevice.netObject> helperList = new ArrayList<cmDevice.netObject>(); netObj.setBaseType("NetworkInterface"); netObj.setCardType(ifRow.getCell(3).getStringCellValue()); netObj.setChildType(ifRow.getCell(4).getStringCellValue()); netObj.setDhcp((ifRow.getCell(5).getNumericCellValue() > 0.1) ? true : false); netObj.setIp(ifRow.getCell(7).getStringCellValue()); // netObj.setMac(ifRow.getCell(0).toString()); //netObj.setModified(true); netObj.setName(ifRow.getCell(1).getStringCellValue()); netObj.setNetwork(networks.get(ifRow.getCell(6).getStringCellValue())); //netObj.setOldLocalUniqueKey(0L); netObj.setRevision(""); netObj.setSpeed(ifRow.getCell(8, Row.CREATE_NULL_AS_BLANK).getStringCellValue()); netObj.setStartIf("ALWAYS"); netObj.setToBeRemoved(false); netObj.setUniqueKey((long) ifRow.getCell(2).getNumericCellValue()); //netObj.setAdditionalHostnames(new ArrayList<String>(Arrays.asList(ifRow.getCell(9, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*")))); //netObj.setMac(ifRow.getCell(10, Row.CREATE_NULL_AS_BLANK).getStringCellValue()); netObj.setMode((int) ifRow.getCell(11, Row.CREATE_NULL_AS_BLANK).getNumericCellValue()); netObj.setOptions(ifRow.getCell(12, Row.CREATE_NULL_AS_BLANK).getStringCellValue()); netObj.setMembers(new ArrayList<String>(Arrays .asList(ifRow.getCell(13, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*")))); // ifmap.put(ifRow.getCell(0).getStringCellValue(), new // HashMap<String, cmDevice.netObject>()); // helperMap.put(ifRow.getCell(1).getStringCellValue(), netObj) ; // ifmap.get(ifRow.getCell(0).getStringCellValue()).putIfAbsent(ifRow.getCell(1).getStringCellValue(), // netObj); // ifmap.put(ifRow.getCell(0).getStringCellValue(), helperMap); if (!ifmap.containsKey(ifRow.getCell(0).getStringCellValue())) { ifmap.put(ifRow.getCell(0).getStringCellValue(), new ArrayList<cmDevice.netObject>()); } helperList = ifmap.get(ifRow.getCell(0).getStringCellValue()); helperList.add(netObj); ifmap.put(ifRow.getCell(0).getStringCellValue(), helperList); continue; } for (int i = 0; i < nodeSheet.getPhysicalNumberOfRows(); i++) { Row nodeRow = nodeSheet.getRow(i); if (nodeRow.getRowNum() == 0) { continue; // just skip the rows if row number is 0 } newnode.setService("cmdevice"); newnode.setCall("addDevice"); Map<String, Long> ifmap2 = new HashMap<String, Long>(); for (cmDevice.netObject j : ifmap.get(nodeRow.getCell(0).getStringCellValue())) ifmap2.put(j.getName(), j.getUniqueKey()); switchObj.setEthernetSwitch(switches.get(nodeRow.getCell(8).getStringCellValue())); System.out.println(nodeRow.getCell(8).getStringCellValue()); System.out.println(switches.get(nodeRow.getCell(8).getStringCellValue())); switchObj.setPrt((int) nodeRow.getCell(9).getNumericCellValue()); switchObj.setBaseType("SwitchPort"); devObj.setBaseType("Device"); // devObj.setCreationTime(0L); devObj.setCustomPingScript(""); devObj.setCustomPingScriptArgument(""); devObj.setCustomPowerScript(""); devObj.setCustomPowerScriptArgument(""); devObj.setCustomRemoteConsoleScript(""); devObj.setCustomRemoteConsoleScriptArgument(""); devObj.setDisksetup(""); devObj.setBmcPowerResetDelay(0L); devObj.setBurning(false); devObj.setEthernetSwitch(switchObj); devObj.setExcludeListFull(""); devObj.setExcludeListGrab(""); devObj.setExcludeListGrabnew(""); devObj.setExcludeListManipulateScript(""); devObj.setExcludeListSync(""); devObj.setExcludeListUpdate(""); devObj.setFinalize(""); devObj.setRack(racks.get(nodeRow.getCell(10).getStringCellValue())); devObj.setRackHeight((long) nodeRow.getCell(11).getNumericCellValue()); devObj.setRackPosition((long) nodeRow.getCell(12).getNumericCellValue()); devObj.setIndexInsideContainer(0L); devObj.setInitialize(""); devObj.setInstallBootRecord(false); devObj.setInstallMode(""); devObj.setIoScheduler(""); devObj.setLastProvisioningNode(0L); devObj.setMac(nodeRow.getCell(6).getStringCellValue()); devObj.setModified(true); devObj.setCategory(categories.get(nodeRow.getCell(1).getStringCellValue())); devObj.setChildType("PhysicalNode"); //devObj.setDatanode((nodeRow.getCell(5).getNumericCellValue() > 0.1) ? true // : false); devObj.setHostname(nodeRow.getCell(0).getStringCellValue()); devObj.setModified(true); devObj.setPartition(partitions.get(nodeRow.getCell(2).getStringCellValue())); devObj.setUseExclusivelyFor("Category"); devObj.setNextBootInstallMode(""); devObj.setNotes(""); devObj.setOldLocalUniqueKey(0L); devObj.setPowerControl(nodeRow.getCell(7).getStringCellValue()); // System.out.println(ifmap.get("excelnode001").size()); // System.out.println(ifmap.get(nodeRow.getCell(0).getStringCellValue()).get(nodeRow.getCell(3).getStringCellValue()).getUniqueKey()); devObj.setManagementNetwork(networks.get(nodeRow.getCell(3).getStringCellValue())); devObj.setProvisioningNetwork(ifmap2.get(nodeRow.getCell(4).getStringCellValue())); devObj.setProvisioningTransport("RSYNCDAEMON"); devObj.setPxelabel(""); // "rack": 90194313218, // "rackHeight": 1, // "rackPosition": 4, devObj.setRaidconf(""); devObj.setRevision(""); devObj.setSoftwareImageProxy(null); devObj.setStartNewBurn(false); devObj.setTag("00000000a000"); devObj.setToBeRemoved(false); // devObj.setUcsInfoConfigured(null); // devObj.setUniqueKey(12345L); devObj.setUserdefined1(""); devObj.setUserdefined2(""); ArrayList<Object> mylist = new ArrayList<Object>(); ArrayList<cmDevice.netObject> mylist2 = new ArrayList<cmDevice.netObject>(); ArrayList<Object> emptylist = new ArrayList<Object>(); devObj.setFsexports(emptylist); devObj.setFsmounts(emptylist); devObj.setGpuSettings(emptylist); devObj.setFspartAssociations(emptylist); devObj.setPowerDistributionUnits(emptyslist); devObj.setRoles(emptylist); devObj.setServices(emptylist); devObj.setStaticRoutes(emptylist); mylist2 = ifmap.get(nodeRow.getCell(0).getStringCellValue()); devObj.setNetworks(mylist2); mylist.add(devObj); mylist.add(1); newnode.setArgs(mylist); GsonBuilder builder = new GsonBuilder(); builder.enableComplexMapKeySerialization(); // Gson g = new Gson(); Gson g = builder.create(); String json2 = g.toJson(newnode); // To be used from a real console and not Eclipse String message = JSonRequestor.doRequest(json2, cmURL, cookies); continue; } JOptionPane optionPaneF = new JOptionPane("The nodes have been added!"); JDialog myDialogF = optionPaneF.createDialog(null, "Complete: "); myDialogF.setModal(false); myDialogF.setVisible(true); doLogout(cmURL, cookies); // System.exit(0); }
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
/** * Reads the configuration parameters from the specified worksheet in the * Xcel spreadsheet. /*from ww w. j a v 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.camel.action.location.CityAction.java
public void handleFileUpload(FileUploadEvent event) { try {/* ww w .j ava2 s .c om*/ List<City> citiesList = new ArrayList<City>(); //Create the input stream from the xlsx/xls file String fileName = event.getFile().getFileName(); String cityCode = ""; String cityName = ""; String countryCode = ""; String countryName = ""; InputStream fis = event.getFile().getInputstream(); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row = null; Iterator<Cell> cellIterator = null; Cell cell = null; City city = null; while (rowIterator.hasNext()) { cityCode = ""; cityName = ""; countryCode = ""; countryName = ""; row = rowIterator.next(); cellIterator = row.cellIterator(); if (row.getRowNum() == 0) continue; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cityCode.equalsIgnoreCase("")) { cityCode = cell.getStringCellValue().trim(); } else if (cityName.equalsIgnoreCase("")) { cityName = cell.getStringCellValue().trim(); } else if (countryCode.equalsIgnoreCase("")) { countryCode = cell.getStringCellValue().trim(); } break; } } //end of cell iterator if (countryCode.equals("#N/A")) continue; country = findCountry(countryCode); if (country != null) { city = new City(); city.setCityCode(cityCode); city.setCityName(cityName); city.setCountry(country); citiesList.add(city); } } //end of rows iterator //close file input stream fis.close(); for (City c : citiesList) { super.setInstance(c); super.save(); } } catch (IOException e) { e.printStackTrace(); } FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); country = null; }
From source file:com.camel.action.location.CountryAction.java
public void handleFileUpload(FileUploadEvent event) { String errorMessage = ""; try {/*from w w w .j av a 2 s . co m*/ List<Country> countriesList = new ArrayList<Country>(); //Create the input stream from the xlsx/xls file String fileName = event.getFile().getFileName(); String name = ""; String shortCode = ""; String cont = ""; InputStream fis = event.getFile().getInputstream(); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row = null; Iterator<Cell> cellIterator = null; Cell cell = null; Country country = null; while (rowIterator.hasNext()) { name = ""; shortCode = ""; cont = ""; row = rowIterator.next(); cellIterator = row.cellIterator(); if (row.getRowNum() == 0) { continue; } while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (shortCode.equalsIgnoreCase("")) { shortCode = cell.getStringCellValue().trim(); } else if (name.equalsIgnoreCase("")) { name = cell.getStringCellValue().trim(); } else if (cont.equalsIgnoreCase("")) { cont = cell.getStringCellValue().trim(); } break; } } //end of cell iterator if (cont != null && cont.length() > 3) { country = new Country(); country.setContinet(Continent.valueOf(cont)); country.setCountryCode(shortCode); country.setCountryName(name); countriesList.add(country); } } //end of rows iterator fis.close(); for (Country c : countriesList) { super.setInstance(c); super.save(); } } catch (IOException e) { errorMessage = e.getMessage(); } System.out.println("eerrromessage..:" + errorMessage); FacesMessage message = null; if (errorMessage != null && errorMessage.length() > 3) { message = new FacesMessage("ERROR..:", "Country dosn't uploaded![" + errorMessage + "]"); } else { message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); } FacesContext.getCurrentInstance().addMessage(null, message); }
From source file:com.camel.action.location.PortAction.java
public void handleFileUpload(FileUploadEvent event) { try {// w ww . j a va2s. com List<Port> portsList = new ArrayList<Port>(); //Create the input stream from the xlsx/xls file String fileName = event.getFile().getFileName(); String portCode = ""; String portName = ""; String cityCode = ""; String cityName = ""; String countryCode = ""; String countryName = ""; InputStream fis = event.getFile().getInputstream(); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row = null; Iterator<Cell> cellIterator = null; Cell cell = null; City city = null; Port port = null; while (rowIterator.hasNext()) { portCode = ""; portName = ""; cityCode = ""; cityName = ""; countryCode = ""; countryName = ""; row = rowIterator.next(); cellIterator = row.cellIterator(); if (row.getRowNum() == 0) continue; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (portCode.equalsIgnoreCase("")) { portCode = cell.getStringCellValue().trim(); } else if (portName.equalsIgnoreCase("")) { portName = cell.getStringCellValue().trim(); } else if (cityCode.equalsIgnoreCase("")) { cityCode = cell.getStringCellValue().trim(); } else if (cityName.equalsIgnoreCase("")) { cityName = cell.getStringCellValue().trim(); } else if (countryCode.equalsIgnoreCase("")) { countryCode = cell.getStringCellValue().trim(); } else if (countryName.equalsIgnoreCase("")) { countryName = cell.getStringCellValue().trim(); } break; } } //end of cell iterator if (countryCode.equals("#N/A")) continue; country = findCountry(countryCode); city = findCity(cityCode); if (country != null && city != null) { port = new Port(); port.setPortType(PortType.SEAPORT); port.setPortCode(portCode); port.setPortName(portName); port.setCity(city); port.setCountry(country); portsList.add(port); } } //end of rows iterator //close file input stream fis.close(); for (Port c : portsList) { super.setInstance(c); super.save(); } } catch (IOException e) { e.printStackTrace(); } FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); country = null; }
From source file:com.canoo.webtest.plugins.exceltest.AbstractExcelStep.java
License:Open Source License
/** * Creates an Workbook for the page. This method should not be used directly, * use {@link #getExcelWorkbook()} instead. * @param currentPage the page containing the Excel document * @return the analyzer//from w ww. jav a2 s . co m */ private Workbook createWorkbook(final Page currentPage) { InputStream is = null; try { getContext().put(KEY_CURRENT_SHEET, null); try { is = currentPage.getWebResponse().getContentAsStream(); final POIFSFileSystem excelFile = new RetryWithCapsPOIFSFileSystem(is); return new HSSFWorkbook(excelFile); } catch (Exception e) { is = currentPage.getWebResponse().getContentAsStream(); return new XSSFWorkbook(is); } } catch (final Exception e) { final String message = "Could not open Excel file."; LOG.debug(message, e); throw new StepExecutionException(message, this, e); } finally { IOUtils.closeQuietly(is); } }
From source file:com.cdf.objects.ExcelData.java
public void loadData(File file) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);/*from w ww.j a v a 2 s . c om*/ HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int physicalCellCount; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { physicalCellCount = sheet.getRow(i).getPhysicalNumberOfCells(); if (physicalCellCount > cols) { cols = physicalCellCount; } } } System.out.println("Row count : " + rows); System.out.println("Coloumn count: " + cols); }