List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java
public void readNewTaxonExcel() { try (FileInputStream file = new FileInputStream(new File(NEW_TAXAN_EXCEL_FILE_PATH))) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(6); int numOfRows = sheet.getLastRowNum(); IntStream.range(3, numOfRows + 1).forEach(nbr -> { XSSFRow row = sheet.getRow(nbr); String scientificName = row.getCell(0).getStringCellValue(); String rank = row.getCell(1).getStringCellValue(); String author = row.getCell(2).getStringCellValue(); String parent = row.getCell(3).getStringCellValue(); String source = row.getCell(4).getStringCellValue(); String agent = row.getCell(5).getStringCellValue(); String isManuscript = row.getCell(7).getStringCellValue(); System.out.println("isManuscript : " + Boolean.valueOf(isManuscript) + isManuscript); if (agent.contains(" ")) { int index = agent.indexOf(" "); System.out.println("last name : " + agent.substring(index + 1)); System.out.println("first name : " + agent.split(" ")[0]); }/*from w ww . j a v a2s . c om*/ }); } catch (IOException ex) { logger.error(ex.getMessage()); } }
From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java
public List<TaxonVO> read() { logger.info("read excel"); List<TaxonVO> list = new ArrayList(); try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); int rowEndNumber = sheet.getLastRowNum(); IntStream.range(1, rowEndNumber + 1).forEach(nbr -> { XSSFRow row = sheet.getRow(nbr); String rank = row.getCell(1).getStringCellValue(); if (rank.equals("Genus") || rank.equals("Subgenus")) { currentParent = row.getCell(2).getStringCellValue(); currentRank = rank;/* w w w . j a v a2s .c o m*/ } else if (rank.equals("Species")) { taxonName = row.getCell(2).getStringCellValue(); TaxonVO vo = new TaxonVO(taxonName, currentParent, currentRank); System.out.println(taxonName + " --- " + currentParent + " --- " + currentRank); list.add(vo); } }); System.out.println("row number : " + rowEndNumber); } catch (IOException ex) { System.out.println("error: " + ex.getMessage()); } return list; }
From source file:Search.IDSSearchFunctionRunner.java
@Test public void searchAction() throws FileNotFoundException, IOException, InterruptedException { FileInputStream file = new FileInputStream(new File("D:\\Book13.xlsx")); workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); CellStyle style = workbook.createCellStyle(); Cell cell;//from w ww . ja v a 2 s . co m Row row; navigateToLogin(); LoginAction loginAction = new LoginAction(driver); loginAction.enterUserName("maxval"); loginAction.enterPassword("Qcom2015*"); TermsAndCondition termsAndConditions = loginAction.loginSubmit(); Dashboard dashboard = termsAndConditions.Accept(); System.out.println("abcd"); System.out.println("abcde"); int rowStart = Math.min(15, sheet.getFirstRowNum()); int rowEnd = Math.max(1400, sheet.getLastRowNum()); for (int rowNum = rowStart + 1; rowNum < rowEnd; rowNum++) { row = sheet.getRow(rowNum); if (row != null) { int columnNumber = 0; cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL); try { if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); RichTextString fullTextQuery = cell.getRichStringCellValue(); String myQuery = fullTextQuery.toString(); System.out.println(myQuery); SearchList searchList = new SearchList(driver); if (rowNum == 1) { dashboard.enterFullTextSearchQuery(myQuery); searchList = dashboard.submitFullTextSearchQueryFromDashboard(); } else { searchList.enterFullTextSearchQuery(myQuery); searchList.submitFullTextSearchQueryFromSearchList(); } String searchRecordList = searchList.getRecordIdInSearchList(); cell = row.createCell(2); cell.setCellType(Cell.CELL_TYPE_STRING); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(searchRecordList); cell = row.getCell(1); String expectedResult = cell.getStringCellValue(); if (expectedResult.equals(searchRecordList)) { cell = row.createCell(3); cell.setCellValue("Seach result matched with Expected"); } else { cell = row.createCell(3); cell.setCellValue("Seach result NOT matched with Expected"); } try (FileOutputStream fileOut = new FileOutputStream("D:\\result.xlsx")) { workbook.write(fileOut); } } } catch (AssertionError Ae) { } } } }
From source file:Servelt.ExcelReader.java
private void readSheet(XSSFSheet sheet, int Max_Col, ComponentSet comSet) throws Exception { String data = null;/*from w ww . j a v a 2 s.co m*/ Component com = null; for (int i = 1; i <= sheet.getLastRowNum(); i++) { com = comSet.newComponent(); for (int j = 0; j < Max_Col; j++) { Cell cell = sheet.getRow(i).getCell(j); if (cell != null) { data = cellToString(cell); } else { data = ""; } System.out.printf("| %2d-%2d%30s |", i, j, data); com.setComponentByColumn(j, data); } System.out.println(); comSet.addComponent(com); } return; }
From source file:Servelt.ExcelWriter.java
public ExcelWriter(String pjname, String id) { logger = Logger.getLogger(ExcelWriter.class.getName()); String cate = null;/*from w w w .j a va2s . c o m*/ Map<String, List<String>> map = new TreeMap<String, List<String>>(); List<String> typ = new ArrayList<String>(); List<String> des = new ArrayList<String>(); Map<String, List<String[]>> emap = new TreeMap<String, List<String[]>>(); String[] stra; try (Connection conn = DriverManager.getConnection(DB2url, DB2user, DB2password)) { Statement stm = conn.createStatement(); ResultSet result = MySQLSyntax.getResultSet(stm, db2.project_Table.TABLE_NAME, db2.project_Table.ProjectName, pjname); if (result.next()) { cate = result.getString(db2.project_Table.Category); } result = MySQLSyntax.getResultSet(stm, db2.description_Table.TABLE_NAME, db2.description_Table.ProjectName, pjname); while (result.next()) { stra = new String[2]; stra[0] = result.getString(db2.description_Table.Type); stra[1] = result.getString(db2.description_Table.Description); if (!emap.containsKey("Others")) { emap.put("Others", new ArrayList<String[]>()); } emap.get("Others").add(stra); } ProjectResultMap(stm, map, pjname, Antennas.TableName, db2.project_antennas_Table.TABLE_NAME, Antennas.MainKey); ProjectResultMap(stm, map, pjname, Battery.TableName, db2.project_battery_Table.TABLE_NAME, Battery.MainKey); ProjectResultMap(stm, map, pjname, Button.TableName, db2.project_button_Table.TABLE_NAME, Button.MainKey); ProjectResultMap(stm, map, pjname, Camera.TableName, db2.project_camera_Table.TABLE_NAME, Camera.MainKey); ProjectResultMap(stm, map, pjname, CardReader.TableName, db2.project_card_reader_Table.TABLE_NAME, CardReader.MainKey); ProjectResultMap(stm, map, pjname, Charger.TableName, db2.project_charger_Table.TABLE_NAME, Charger.MainKey); ProjectResultMap(stm, map, pjname, ClickPad.TableName, db2.project_click_pad_Table.TABLE_NAME, ClickPad.MainKey); ProjectResultMap(stm, map, pjname, CPU.TableName, db2.project_cpu_Table.TABLE_NAME, CPU.MainKey); ProjectResultMap(stm, map, pjname, Ethernet.TableName, db2.project_ethernet_Table.TABLE_NAME, Ethernet.MainKey); ProjectResultMap(stm, map, pjname, KBCEBC.TableName, db2.project_kbc_ebc_Table.TABLE_NAME, KBCEBC.MainKey); ProjectResultMap(stm, map, pjname, Keyboard.TableName, db2.project_keyboard_Table.TABLE_NAME, Keyboard.MainKey); ProjectResultMap(stm, map, pjname, LcdPanel.TableName, db2.project_lcd_panel_Table.TABLE_NAME, LcdPanel.MainKey); ProjectResultMap(stm, map, pjname, Measurement.TableName, db2.project_measurement_Table.TABLE_NAME, Measurement.MainKey); ProjectResultMap(stm, map, pjname, Memory.TableName, db2.project_memory_Table.TABLE_NAME, Memory.MainKey); ProjectResultMap(stm, map, pjname, Sensor.TableName, db2.project_sensor_Table.TABLE_NAME, Sensor.MainKey); ProjectResultMap(stm, map, pjname, Storage.TableName, db2.project_storage_Table.TABLE_NAME, Storage.MainKey); ProjectResultMap(stm, map, pjname, TouchPanel.TableName, db2.project_touch_panel_Table.TABLE_NAME, TouchPanel.MainKey); ProjectResultMap(stm, map, pjname, WLAN.TableName, db2.project_wlan_Table.TABLE_NAME, WLAN.MainKey); ProjectResultMap(stm, map, pjname, WWAN.TableName, db2.project_wwan_Table.TABLE_NAME, WWAN.MainKey); ProjectResultMap(stm, map, pjname, Graphic.TableName, db2.project_graphic_Table.TABLE_NAME, Graphic.MainKey); ProjectResultMap(stm, map, pjname, AudioCodec.TableName, db2.project_audio_codec_Table.TABLE_NAME, AudioCodec.MainKey); ProjectResultMap(stm, map, pjname, PanelInterfaceBridge.TableName, db2.project_panel_interface_bridge_Table.TABLE_NAME, PanelInterfaceBridge.MainKey); ProjectResultMap(stm, map, pjname, ExternalStorageCard.TableName, db2.project_external_storage_card_Table.TABLE_NAME, ExternalStorageCard.MainKey); ProjectResultMap(stm, map, pjname, ODD.TableName, db2.project_odd_Table.TABLE_NAME, ODD.MainKey); ProjectResultMap(stm, map, pjname, Speaker.TableName, db2.project_speaker_Table.TABLE_NAME, Speaker.MainKey); ProjectResultMap(stm, map, pjname, Mic.TableName, db2.project_mic_Table.TABLE_NAME, Mic.MainKey); ProjectResultMap(stm, map, pjname, IoPort.TableName, db2.project_io_port_Table.TABLE_NAME, IoPort.MainKey); ProjectResultMap(stm, map, pjname, OS.TableName, db2.project_os_Table.TABLE_NAME, OS.MainKey); } catch (SQLException ex) { logger.log(Level.WARNING, "In SQLException (getSearchProjectResultPage) : " + ex.toString()); ex.printStackTrace(); } List<String[]> list; String str; try (Connection conn = DriverManager.getConnection(DBurl, DBuser, DBpassword)) { Statement stm = conn.createStatement(); ResultSet result = null; for (Entry<String, List<String>> entry : map.entrySet()) { switch (entry.getKey()) { case db.antennas_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.antennas_Table.TABLE_NAME, Antennas.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.antennas_Table.Type); stra[1] = result.getString(db.antennas_Table.Description); if (!emap.containsKey("Antenna")) emap.put("Antenna", new ArrayList<String[]>()); emap.get("Antenna").add(stra); } } break; } case db.audio_codec_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.audio_codec_Table.TABLE_NAME, AudioCodec.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.audio_codec_Table.Vendor); stra[1] = result.getString(db.audio_codec_Table.Model) + "\n" + result.getString(db.audio_codec_Table.Description); if (!emap.containsKey("Audio Codec")) emap.put("Audio Codec", new ArrayList<String[]>()); emap.get("Audio Codec").add(stra); } } break; } case db.battery_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.battery_Table.TABLE_NAME, Battery.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.battery_Table.Vendor); stra[1] = result.getString(db.battery_Table.ModelName) + " " + result.getString(db.battery_Table.Capacity) + " " + result.getString(db.battery_Table.Cell) + "\n" + result.getString(db.battery_Table.Description); if (!emap.containsKey("Battery Pack")) emap.put("Battery Pack", new ArrayList<String[]>()); emap.get("Battery Pack").add(stra); } } break; } case db.button_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.button_Table.TABLE_NAME, Button.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.button_Table.Type); stra[0] = result.getString(db.button_Table.Description); if (!emap.containsKey("Button")) emap.put("Button", new ArrayList<String[]>()); emap.get("Button").add(stra); } } break; } case db.camera_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.camera_Table.TABLE_NAME, Camera.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.camera_Table.Vendor); stra[1] = result.getString(db.camera_Table.Model) + " " + result.getString(db.camera_Table.Sensor_IC) + " " + result.getString(db.camera_Table.Pixel_Mega) + " " + result.getString(db.camera_Table.AF_FF) + " " + result.getString(db.camera_Table.Lens) + "\n" + result.getString(db.camera_Table.Description); if (!emap.containsKey("Web CAM")) emap.put("Web CAM", new ArrayList<String[]>()); emap.get("Web CAM").add(stra); } } break; } case db.card_reader_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.card_reader_Table.TABLE_NAME, CardReader.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.card_reader_Table.Vendor); stra[1] = result.getString(db.card_reader_Table.Model) + "\n" + result.getString(db.card_reader_Table.Description); if (!emap.containsKey("Card reader")) emap.put("Card reader", new ArrayList<String[]>()); emap.get("Card reader").add(stra); } } break; } case db.charger_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.charger_Table.TABLE_NAME, Charger.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.charger_Table.Vendor); stra[1] = result.getString(db.charger_Table.ModelName) + " " + result.getString(db.charger_Table.Type) + " " + result.getString(db.charger_Table.OUTPUT) + " " + result.getString(db.charger_Table.INPUT) + "\n" + result.getString(db.charger_Table.Description); if (!emap.containsKey("Adaptor")) emap.put("Adaptor", new ArrayList<String[]>()); emap.get("Adaptor").add(stra); } } break; } case db.click_pad_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.click_pad_Table.TABLE_NAME, ClickPad.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.click_pad_Table.Vendor); stra[1] = result.getString(db.click_pad_Table.ModelName) + "\n" + result.getString(db.click_pad_Table.Description); if (!emap.containsKey("Pointing Device")) emap.put("Pointing Device", new ArrayList<String[]>()); emap.get("Pointing Device").add(stra); } } break; } case db.cpu_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.cpu_Table.TABLE_NAME, CPU.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.cpu_Table.Vendor) + "/" + result.getString(db.cpu_Table.CodeName); stra[1] = result.getString(db.cpu_Table.ModelName) + " " + result.getString(db.cpu_Table.MaxCoreFreq) + " " + result.getString(db.cpu_Table.CoreThreats) + " " + result.getString(db.cpu_Table.Gfx) + " " + result.getString(db.cpu_Table.TDP); if (!emap.containsKey("CPU/SoC")) emap.put("CPU/SoC", new ArrayList<String[]>()); emap.get("CPU/SoC").add(stra); } } break; } case db.ethernet_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.ethernet_Table.TABLE_NAME, Ethernet.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.ethernet_Table.Vendor); stra[1] = result.getString(db.ethernet_Table.ModelName) + "\n" + result.getString(db.ethernet_Table.Description); if (!emap.containsKey("LAN Controller")) emap.put("LAN Controller", new ArrayList<String[]>()); emap.get("LAN Controller").add(stra); } } break; } case db.external_storage_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.external_storage_Table.TABLE_NAME, ExternalStorageCard.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.external_storage_Table.Vendor); stra[1] = result.getString(db.external_storage_Table.Type) + " " + result.getString(db.external_storage_Table.Model) + " " + result.getString(db.external_storage_Table.Dimension) + " " + result.getString(db.external_storage_Table.Interface) + " " + result.getString(db.external_storage_Table.Speed) + " " + result.getString(db.external_storage_Table.Capacity); if (!emap.containsKey("Extra storage card")) emap.put("Extra storage card", new ArrayList<String[]>()); emap.get("Extra storage card").add(stra); } } break; } case db.graphic_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.graphic_Table.TABLE_NAME, Graphic.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.graphic_Table.Vendor); stra[1] = result.getString(db.graphic_Table.ModelName) + " " + result.getString(db.graphic_Table.MemoryType) + " " + result.getString(db.graphic_Table.vRAM_size) + "\n" + result.getString(db.graphic_Table.Description); if (!emap.containsKey("Graphic")) emap.put("Graphic", new ArrayList<String[]>()); emap.get("Graphic").add(stra); } } break; } case db.io_port_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.io_port_Table.TABLE_NAME, IoPort.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = ""; stra[1] = result.getString(db.io_port_Table.Standard) + " " + result.getString(db.io_port_Table.ConnectorType) + "\n" + result.getString(db.io_port_Table.Description); if (!emap.containsKey("I/O Port")) emap.put("I/O Port", new ArrayList<String[]>()); emap.get("I/O Port").add(stra); } } break; } case db.kbc_ebc_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.kbc_ebc_Table.TABLE_NAME, KBCEBC.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.kbc_ebc_Table.Vendor); stra[1] = result.getString(db.kbc_ebc_Table.Model) + "\n" + result.getString(db.kbc_ebc_Table.Description); if (!emap.containsKey("KBC/EBC")) emap.put("KBC/EBC", new ArrayList<String[]>()); emap.get("KBC/EBC").add(stra); } } break; } case db.keyboard_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.keyboard_Table.TABLE_NAME, Keyboard.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.keyboard_Table.Vendor); stra[1] = result.getString(db.keyboard_Table.ModelName) + " " + result.getString(db.keyboard_Table.Type) + " " + result.getString(db.keyboard_Table.OS) + " " + result.getString(db.keyboard_Table.Layout) + "\n" + result.getString(db.keyboard_Table.Description); if (!emap.containsKey("Keyboard")) emap.put("Keyboard", new ArrayList<String[]>()); emap.get("Keyboard").add(stra); } } break; } case db.lcd_panel_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.lcd_panel_Table.TABLE_NAME, LcdPanel.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.lcd_panel_Table.Vendor); stra[1] = result.getString(db.lcd_panel_Table.ModelName) + " " + result.getString(db.lcd_panel_Table.Size) + " " + result.getString(db.lcd_panel_Table.Resolution) + " " + result.getString(db.lcd_panel_Table.Type) + " " + result.getString(db.lcd_panel_Table.Nits) + " " + result.getString(db.lcd_panel_Table.Touch) + " " + result.getString(db.lcd_panel_Table.Interface); if (!emap.containsKey("LCD")) emap.put("LCD", new ArrayList<String[]>()); emap.get("LCD").add(stra); } } break; } case db.measurement_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.measurement_Table.TABLE_NAME, Measurement.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.measurement_Table.Type); stra[1] = result.getString(db.measurement_Table.Dimension) + " " + result.getString(db.measurement_Table.Weight); if (!emap.containsKey("Physical Outline")) emap.put("Physical Outline", new ArrayList<String[]>()); emap.get("Physical Outline").add(stra); } } break; } case db.memory_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.memory_Table.TABLE_NAME, Memory.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.memory_Table.Vendor); stra[1] = result.getString(db.memory_Table.Type) + " " + result.getString(db.memory_Table.Frequency) + " " + result.getString(db.memory_Table.Capacity) + "\n" + result.getString(db.memory_Table.Description); if (!emap.containsKey("Memory")) emap.put("Memory", new ArrayList<String[]>()); emap.get("Memory").add(stra); } } break; } case db.mic_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.mic_Table.TABLE_NAME, Mic.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = "Mic"; stra[1] = result.getString(db.mic_Table.ModelName) + "\n" + result.getString(db.mic_Table.Description); if (!emap.containsKey("Audio")) emap.put("Audio", new ArrayList<String[]>()); emap.get("Audio").add(stra); } } break; } case db.speaker_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.speaker_Table.TABLE_NAME, Speaker.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = "Speaker"; stra[1] = result.getString(db.speaker_Table.ModelName) + "\n" + result.getString(db.mic_Table.Description); if (!emap.containsKey("Audio")) emap.put("Audio", new ArrayList<String[]>()); emap.get("Audio").add(stra); } } break; } case db.odd_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.odd_Table.TABLE_NAME, ODD.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.odd_Table.Vendor); stra[1] = result.getString(db.odd_Table.Model) + " " + result.getString(db.odd_Table.Interface) + " " + result.getString(db.odd_Table.zHigh) + "\n" + result.getString(db.odd_Table.Description); if (!emap.containsKey("ODD")) emap.put("ODD", new ArrayList<String[]>()); emap.get("ODD").add(stra); } } break; } case db.os_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.os_Table.TABLE_NAME, OS.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.os_Table.Type); stra[1] = result.getString(db.os_Table.Description); if (!emap.containsKey("OS")) emap.put("OS", new ArrayList<String[]>()); emap.get("OS").add(stra); } } break; } case db.panel_interface_bridge_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.panel_interface_bridge_Table.TABLE_NAME, PanelInterfaceBridge.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.panel_interface_bridge_Table.Vendor); stra[1] = result.getString(db.panel_interface_bridge_Table.ModelName) + "\n" + result.getString(db.panel_interface_bridge_Table.Description); if (!emap.containsKey("Bridge IC")) emap.put("Bridge IC", new ArrayList<String[]>()); emap.get("Bridge IC").add(stra); } } break; } case db.sensor_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.sensor_Table.TABLE_NAME, Sensor.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.sensor_Table.Type); stra[1] = result.getString(db.sensor_Table.Vendor) + " " + result.getString(db.sensor_Table.ModelName) + "\n" + result.getString(db.sensor_Table.Description); if (!emap.containsKey("Sensor")) emap.put("Sensor", new ArrayList<String[]>()); emap.get("Sensor").add(stra); } } break; } case db.storage_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.storage_Table.TABLE_NAME, Storage.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.storage_Table.Vendor); stra[1] = result.getString(db.storage_Table.Type) + " " + result.getString(db.storage_Table.Model) + " " + result.getString(db.storage_Table.Capacity) + " " + result.getString(db.storage_Table.Interface) + " " + result.getString(db.storage_Table.zHight) + " " + result.getString(db.storage_Table.Rpm) + " " + result.getString(db.storage_Table.Speed); if (!emap.containsKey("Storage")) emap.put("Storage", new ArrayList<String[]>()); emap.get("Storage").add(stra); } } break; } case db.touch_panel_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.touch_panel_Table.TABLE_NAME, TouchPanel.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.touch_panel_Table.Vendor); stra[1] = result.getString(db.touch_panel_Table.ModelName) + " " + result.getString(db.touch_panel_Table.IC) + " " + result.getString(db.touch_panel_Table.Type) + " " + result.getString(db.touch_panel_Table.Multi_Touch) + " " + result.getString(db.touch_panel_Table.Stylus); if (!emap.containsKey("Touch solution")) emap.put("Touch solution", new ArrayList<String[]>()); emap.get("Touch solution").add(stra); } } break; } case db.wlan_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.wlan_Table.TABLE_NAME, WLAN.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.wlan_Table.Vendor); stra[1] = result.getString(db.wlan_Table.ModelName) + " " + result.getString(db.wlan_Table.Protocal) + " " + result.getString(db.wlan_Table.nTnR) + " " + result.getString(db.wlan_Table.Band) + " " + result.getString(db.wlan_Table.Bluetooth) + " " + result.getString(db.wlan_Table.Max_Speed); if (!emap.containsKey("Wireless LAN and Bluetooth Combo")) emap.put("Wireless LAN and Bluetooth Combo", new ArrayList<String[]>()); emap.get("Wireless LAN and Bluetooth Combo").add(stra); } } break; } case db.wwan_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.wwan_Table.TABLE_NAME, WWAN.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.wwan_Table.Vendor); stra[1] = result.getString(db.wwan_Table.ModelName) + " " + result.getString(db.wwan_Table.Type) + "\n" + result.getString(db.wwan_Table.Description); if (!emap.containsKey("WWAN")) emap.put("WWAN", new ArrayList<String[]>()); emap.get("WWAN").add(stra); } } break; } } } } catch (SQLException ex) { logger.log(Level.WARNING, "In SQLException (ExcelWriter) : " + ex.toString()); ex.printStackTrace(); } try { workbook = new XSSFWorkbook(new File(filepath + template)); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row; XSSFCell cell; int y, k; y = 45; List<Entry<String, List<String[]>>> elist = new ArrayList<Entry<String, List<String[]>>>(); if (emap.containsKey("CPU/SoC")) elist.add(new SimpleEntry<String, List<String[]>>("CPU/SoC", emap.get("CPU/SoC"))); if (emap.containsKey("Graphic")) elist.add(new SimpleEntry<String, List<String[]>>("Graphic", emap.get("Graphic"))); if (emap.containsKey("Memory")) elist.add(new SimpleEntry<String, List<String[]>>("Memory", emap.get("Memory"))); if (emap.containsKey("Audio Codec")) elist.add(new SimpleEntry<String, List<String[]>>("Audio Codec", emap.get("Audio Codec"))); if (emap.containsKey("KBC/EBC")) elist.add(new SimpleEntry<String, List<String[]>>("KBC/EBC", emap.get("KBC/EBC"))); if (emap.containsKey("LAN Controller")) elist.add(new SimpleEntry<String, List<String[]>>("LAN Controller", emap.get("LAN Controller"))); if (emap.containsKey("Wireless LAN and Bluetooth Combo")) elist.add(new SimpleEntry<String, List<String[]>>("Wireless LAN and Bluetooth Combo", emap.get("Wireless LAN and Bluetooth Combo"))); if (emap.containsKey("WWAN")) elist.add(new SimpleEntry<String, List<String[]>>("WWAN", emap.get("WWAN"))); if (emap.containsKey("Antenna")) elist.add(new SimpleEntry<String, List<String[]>>("Antenna", emap.get("Antenna"))); if (emap.containsKey("Pointing Device")) elist.add(new SimpleEntry<String, List<String[]>>("Pointing Device", emap.get("Pointing Device"))); if (emap.containsKey("LCD")) elist.add(new SimpleEntry<String, List<String[]>>("LCD", emap.get("LCD"))); if (emap.containsKey("Touch solution")) elist.add(new SimpleEntry<String, List<String[]>>("Touch solution", emap.get("Touch solution"))); if (emap.containsKey("Bridge IC")) elist.add(new SimpleEntry<String, List<String[]>>("Bridge IC", emap.get("Bridge IC"))); if (emap.containsKey("Storage")) elist.add(new SimpleEntry<String, List<String[]>>("Storage", emap.get("Storage"))); if (emap.containsKey("Extra storage card")) elist.add(new SimpleEntry<String, List<String[]>>("Extra storage card", emap.get("Extra storage card"))); if (emap.containsKey("ODD")) elist.add(new SimpleEntry<String, List<String[]>>("ODD", emap.get("ODD"))); if (emap.containsKey("Card reader")) elist.add(new SimpleEntry<String, List<String[]>>("Card reader", emap.get("Card reader"))); if (emap.containsKey("Audio")) elist.add(new SimpleEntry<String, List<String[]>>("Audio", emap.get("Audio"))); if (emap.containsKey("Web CAM")) elist.add(new SimpleEntry<String, List<String[]>>("Web CAM", emap.get("Web CAM"))); if (emap.containsKey("Sensor")) elist.add(new SimpleEntry<String, List<String[]>>("Sensor", emap.get("Sensor"))); if (emap.containsKey("Battery Pack")) elist.add(new SimpleEntry<String, List<String[]>>("Battery Pack", emap.get("Battery Pack"))); if (emap.containsKey("Adaptor")) elist.add(new SimpleEntry<String, List<String[]>>("Adaptor", emap.get("Adaptor"))); if (emap.containsKey("I/O Port")) elist.add(new SimpleEntry<String, List<String[]>>("I/O Port", emap.get("I/O Port"))); if (emap.containsKey("Keyboard")) elist.add(new SimpleEntry<String, List<String[]>>("Keyboard", emap.get("Keyboard"))); if (emap.containsKey("Button")) elist.add(new SimpleEntry<String, List<String[]>>("Button", emap.get("Button"))); if (emap.containsKey("OS")) elist.add(new SimpleEntry<String, List<String[]>>("OS", emap.get("OS"))); if (emap.containsKey("Physical Outline")) elist.add( new SimpleEntry<String, List<String[]>>("Physical Outline", emap.get("Physical Outline"))); if (emap.containsKey("Others")) elist.add(new SimpleEntry<String, List<String[]>>("Others", emap.get("Others"))); for (Entry<String, List<String[]>> entry : elist) { for (int i = 0; i < entry.getValue().size(); i++) { sheet.createRow(y + i); } //System.out.println(entry.getValue().size()); row = sheet.getRow(y); cell = row.createCell(0); cell.setCellValue(entry.getKey()); sheet.addMergedRegion(new CellRangeAddress(y, y + entry.getValue().size() - 1, 0, 1)); k = 0; for (String[] s : entry.getValue()) { //System.out.println(s[0] + s[1]); row = sheet.getRow(y + k); cell = row.createCell(2); cell.setCellValue(s[0]); sheet.addMergedRegion(new CellRangeAddress(y + k, y + k, 2, 3)); cell = row.createCell(4); cell.setCellValue(s[1]); sheet.addMergedRegion(new CellRangeAddress(y + k, y + k, 4, 10)); k++; } y = y + entry.getValue().size(); } filename = pjname + ".xlsx"; try (FileOutputStream out = new FileOutputStream(filepath + filename)) { workbook.write(out); } } catch (Exception ex) { logger.log(Level.WARNING, "In Exception (ExcelWriter) : " + ex.toString()); ex.printStackTrace(); } }
From source file:Servlets.UploadList.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); String file_name = ""; String extension = ""; String cellData = ""; InputStream input = null;//w ww . j a va2 s . c om ArrayList<String[]> list = new ArrayList<String[]>(); String[] arr; FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List items = null; try { items = upload.parseRequest(request); } catch (Exception e) { e.printStackTrace(); } try { Iterator itr = items.iterator(); while (itr.hasNext()) { FileItem item = (FileItem) itr.next(); file_name = item.getName(); input = item.getInputStream(); } if (!file_name.equals("")) { extension = file_name.substring(file_name.indexOf(".")); if (extension.equalsIgnoreCase(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(input); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row, header; int r = sheet.getLastRowNum(); int headerCnt = 0; header = sheet.getRow(3); String[] headerText = new String[10]; for (int i = 0; i < 10; i++) { try { headerText[i] = header.getCell(i).getStringCellValue(); headerCnt++; } catch (Exception e) { } } arr = new String[headerCnt]; System.arraycopy(headerText, 0, arr, 0, headerCnt); list.add(arr); for (int i = 4; i <= r; i++) { arr = new String[headerCnt]; row = sheet.getRow(i); for (int j = 0; j < headerCnt; j++) { try { cellData = row.getCell(j).getStringCellValue(); arr[j] = cellData; } catch (Exception e) { } } list.add(arr); } InfosDao dao = new InfosDao(); ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo(); request.setAttribute("collegeList", collegeList); request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.'))); request.setAttribute("itemList", list); RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp"); dispatcher.forward(request, response); } else if (extension.equalsIgnoreCase(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(input); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Row row, header; int r = sheet.getLastRowNum(); int headerCnt = 0; header = sheet.getRow(3); String[] headerText = new String[10]; for (int i = 0; i < 10; i++) { try { headerText[i] = header.getCell(i).getStringCellValue(); headerCnt++; } catch (Exception e) { } } arr = new String[headerCnt]; System.arraycopy(headerText, 0, arr, 0, headerCnt); list.add(arr); for (int i = 4; i <= r; i++) { arr = new String[headerCnt]; row = sheet.getRow(i); for (int j = 0; j < headerCnt; j++) { try { cellData = row.getCell(j).getStringCellValue(); arr[j] = cellData; } catch (Exception e) { } } list.add(arr); } InfosDao dao = new InfosDao(); ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo(); request.setAttribute("collegeList", collegeList); request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.'))); request.setAttribute("itemList", list); RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp"); dispatcher.forward(request, response); } else { request.setAttribute("temp", "File Is Not In Proper Format !!!"); RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp"); dispatcher.forward(request, response); } } else { request.setAttribute("temp", "Please Select File !!!"); RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp"); dispatcher.forward(request, response); } } catch (Exception e) { request.setAttribute("temp", "Error Loading File !!! Check contents and format of file."); RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp"); dispatcher.forward(request, response); } }
From source file:singleton.StaticClass.java
public synchronized void copyFromExcel(String fileName) throws IOException { //System.out.println("start"); Data temp;//w ww .j a v a 2 s .c o m String value = ""; File inFile = new File(fileName); FileInputStream file = new FileInputStream(inFile); XSSFWorkbook wb = new XSSFWorkbook(file); XSSFSheet sheet = wb.getSheetAt(4); // Build Image Analysis page is at // the 4th sheet of Open source // license excel file. int rows = sheet.getPhysicalNumberOfRows(); for (int i = 2; i < rows; ++i) { // start index should be 2 since the // 1st row is used for titles. XSSFRow row = sheet.getRow(i); if (row != null) { int cells = row.getPhysicalNumberOfCells(); // Number of cells // at each row. temp = new Data(); for (int colIndex = 1; colIndex <= cells; colIndex++) { XSSFCell cell = row.getCell(colIndex); if (colIndex == 1) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setBinary(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setBinary(cell.getStringCellValue()); break; } } else if (colIndex == 2) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setPath(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setPath(cell.getStringCellValue()); break; } } else if (colIndex == 3) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setOnok(""); break; case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); temp.setOnok(value); break; } } else if (colIndex == 4) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setOssComponent(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setOssComponent(cell.getStringCellValue()); break; } } else if (colIndex == 6) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: temp.setLicense(""); break; case XSSFCell.CELL_TYPE_STRING: temp.setLicense(cell.getStringCellValue()); break; } } else { continue; } } if (temp != null) { if (value.equalsIgnoreCase("nok")) { nokList.add(temp); //System.out.println("nok count : " + nokList.size()); } else if (value.equalsIgnoreCase("ok")) { okList.add(temp); //System.out.println("ok count : " + okList.size()); } else if (value.equalsIgnoreCase("nok(na)")) { nokNaList.add(temp); //System.out.println("nok(na) count : " + nokNaList.size()); } else { blankList.add(temp); //System.out.println("blank count : " + blankList.size()); } System.out.println(temp.getBinary() + "\t" + temp.getPath() + "\t\t" + temp.getOnok() + "\t\t" + temp.getLicense()); } } } }
From source file:standarapp.algorithm.Lecture.java
private void fixXLSX(String nameIn, String nameOut, int sheet, int columnas[]) { XSSFWorkbook xwb = lectureXLSX(nameIn); XSSFSheet xsheet = xwb.getSheetAt(sheet); for (Row row : xsheet) { //xsheet.createRow(row.getRowNum()); for (Cell cell : row) { try { if (columnas.length == 0 || containsInColumns(columnas, cell.getColumnIndex())) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String contenido = cell.getStringCellValue(); if (!cell.getStringCellValue().equals("")) { contenido = fixWords(contenido); xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()) .setCellValue(contenido); } else { xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).setCellValue(""); }/* w ww .j ava2s . c o m*/ break; case Cell.CELL_TYPE_NUMERIC: double contenido_Numerico = cell.getNumericCellValue(); xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex()) .setCellValue(contenido_Numerico); break; default: System.err.print(cell + "\t\t"); xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex()) .setCTCell((CTCell) cell); break; } } } catch (Exception e) { } } } try (FileOutputStream outputStream = new FileOutputStream(nameOut)) { xwb.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:steffen.haertlein.file.FileObject.java
License:Apache License
private void readExcelDocument() { try {/*from w w w .j a va 2 s .c om*/ FileInputStream fs = new FileInputStream(f); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sh; String text = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sh = wb.getSheetAt(i); for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) { XSSFRow currRow = sh.getRow(j); if (currRow == null || currRow.getFirstCellNum() == -1) { continue; } else { for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) { if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) { continue; } else { text += currRow.getCell(k) + "; "; } } text += System.lineSeparator(); } } } fs.close(); wb.close(); String[] xlsxLines = text.split(System.lineSeparator()); for (String line : xlsxLines) { lines.add(line); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE); e.printStackTrace(); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;//from w w w . ja va 2 s . co m String ruthPath = null; try { if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) { // Se crea el libro XSSFWorkbook libro = new XSSFWorkbook(); // Se crea una hoja dentro del libro XSSFSheet sheetD = libro.createSheet(); //Obtener lista de alumnos del curso List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso()); //Leer datos y colocarlos en la hoja int f = 0; //Guardar datos en celda for (SieniAlumno alumno : alumnosEval) { // Se crea una fila dentro de la hoja XSSFRow fila = sheetD.createRow(f); f++; // Se crea las celdas dentro de la fila XSSFCell celdaCarnet = fila.createCell((short) 0); XSSFCell celdaAlumno = fila.createCell((short) 1); XSSFCell celdaNota = fila.createCell((short) 2); //Colocar valor en celda celdaCarnet.setCellValue(alumno.getAlCarnet()); celdaAlumno.setCellValue(alumno.getNombreCompleto()); celdaNota.setCellValue((double) 0.00); } //Encabezados desde plantilla InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx"); StreamedContent plantillaXLS = new DefaultStreamedContent(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx"); XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream()); XSSFSheet sheetP = plantilla.getSheetAt(0); //Filas que ocupa el encabezado de plantilla int encabezado = 3; //Quitar encabezado y desplazar Datos sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado); //Copiar contenido de plantilla a la hoja del reporte int inicio = 0; for (int row = 0; row < encabezado; row++) { copyRow(sheetP, sheetD, row, inicio); inicio++; } //Combinar las columnas al igual que la plantilla for (int m = 0; m < sheetP.getNumMergedRegions(); m++) { CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy(); sheetD.addMergedRegion(cellRangeAddress); } //Evaluacion XSSFCell celdaEval = sheetD.getRow(0).getCell(1); celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre()); // Se salva el libro. FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx"); libro.write(elFichero); elFichero.close(); //Leer libro para descarga FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx")); filePlantilla = new DefaultStreamedContent(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx"); } else { new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR); } } catch (Exception exc) { new ValidationPojo().printMsj( "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath, FacesMessage.SEVERITY_ERROR); } return filePlantilla; }