List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:uploads.uploadAllFacilities.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from ww w. j a v a 2 s. c om*/ dbConn conn = new dbConn(); checker_county = checker_district = ""; county_name = county_id = district_name = district_id = hf_name = hf_id = ""; checker_dist = checker_hf = mflcode = 0; file_source = new File("C:\\Users\\Geofrey Nyabuto\\Desktop\\hf\\supported_hf.xls"); System.out.println(" The file path is: " + file_source); FileInputStream fileInputStream = new FileInputStream(file_source); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Sheet2"); Iterator rowIterator = worksheet.iterator(); int i = 0, y = 0; while (rowIterator.hasNext()) { county_name = county_id = district_name = district_id = hf_name = hf_id = ""; mflcode = 0; HSSFRow rowi = worksheet.getRow(i); HSSFCell cell1 = rowi.getCell((short) 2); county_name = cell1.getStringCellValue(); HSSFCell cell2 = rowi.getCell((short) 3); district_name = cell2.getStringCellValue(); HSSFCell cell3 = rowi.getCell((short) 1); hf_name = cell3.getStringCellValue(); HSSFCell cell4 = rowi.getCell((short) 0); mflcode = (int) cell4.getNumericCellValue(); district_name = district_name.toUpperCase(); county_name = county_name.toUpperCase(); IdGenerator IG = new IdGenerator(); // hf_id=IG.sec+""+IG.micro; String county_che = "SELECT * FROM county WHERE county_name=?"; conn.pst = conn.conn.prepareStatement(county_che); conn.pst.setString(1, county_name); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } System.out.println("county : " + county_id); if (county_id.length() == 0) { county_id = IG.current_id(); String county_inserter = "INSERT INTO county (county_name) VALUES (?)"; conn.pst = conn.conn.prepareStatement(county_inserter); conn.pst.setString(1, county_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(county_id)FROM county"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } } String check_dist = "SELECT * FROM district WHERE district_name=? && county_id=?"; conn.pst = conn.conn.prepareStatement(check_dist); conn.pst.setString(1, district_name); conn.pst.setString(2, county_id); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } System.out.println("district : " + district_id); if (district_id.length() == 0) { district_id = IG.current_id(); String dist_inserter = "INSERT INTO district (county_id,district_name) VALUES (?,?)"; conn.pst = conn.conn.prepareStatement(dist_inserter); conn.pst.setString(1, county_id); conn.pst.setString(2, district_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(district_id)FROM district"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } } // if(checker_dist>0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String check_hf = "SELECT COUNT(hf_id) FROM health_facility WHERE hf_name=? && district_id=?"; conn.prest = conn.conn.prepareStatement(check_hf); conn.prest.setString(1, hf_name); conn.prest.setString(2, district_id); conn.rs = conn.prest.executeQuery(); if (conn.rs.next() == true) { checker_hf = conn.rs.getInt(1); } if (checker_hf == 0) { // ADD THE NEW HEALTH FACILITY TO THE SYSTEM......................... hf_id = IG.current_id(); String inserter = "INSERT INTO health_facility (hf_id,hf_name,mflcode,district_id) " + " VALUES(?,?,?,?)"; conn.prest = conn.conn.prepareStatement(inserter); conn.prest.setString(1, hf_id); conn.prest.setString(2, hf_name); conn.prest.setInt(3, mflcode); conn.prest.setString(4, district_id); conn.prest.executeUpdate(); System.out.println("" + i + " added : " + hf_name); } else { System.out.println("HEALTH FACILITY AT POSITION : " + i + " AL READY ADDED : " + hf_name); } // } // else{ // System.out.println("MISSING DISTRICT AT POSITION : "+i+" for the district "+district_name); // } i++; } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } }
From source file:utilesBD.servidoresDatos.JServerServidorDatosExcel.java
/** * Metodo que se encarga de leer los datos de un archivo en formato excel * @return Fila que contiene la informacion del archivo *//*www . ja v a 2 s . co m*/ public JFilaDatosDefecto leeLineaExcel(HSSFRow hssfRow) { JFilaDatosDefecto loLinea = new JFilaDatosDefecto(); //Me barro todos los elementos de una fila for (int i = hssfRow.getFirstCellNum(); i < hssfRow.getLastCellNum(); i++) { HSSFCell hssfCell = hssfRow.getCell(i); if (hssfCell != null) { switch (hssfCell.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: loLinea.addCampo(String.valueOf(hssfCell.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: try { loLinea.addCampo(hssfCell.getStringCellValue()); } catch (Exception e) { try { loLinea.addCampo(String.valueOf(hssfCell.getNumericCellValue())); } catch (Exception e1) { try { loLinea.addCampo(new JDateEdu(hssfCell.getDateCellValue()).toString() .replace("31/12/1899 ", "")); } catch (Exception e2) { try { loLinea.addCampo(String.valueOf(hssfCell.getBooleanCellValue())); } catch (Exception e3) { loLinea.addCampo(""); } } } } break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(hssfCell)) { loLinea.addCampo( new JDateEdu(hssfCell.getDateCellValue()).toString().replace("31/12/1899 ", "")); } else { double ldValor = hssfCell.getNumericCellValue(); loLinea.addCampo( JFormat.msFormatearDouble(ldValor, "############.#########").replace(',', '.')); } break; case HSSFCell.CELL_TYPE_STRING: loLinea.addCampo(hssfCell.toString()); break; default: loLinea.addCampo(""); } } else { loLinea.addCampo(""); } } return loLinea; }
From source file:utils.ReadWriteExcelFile.java
public static void readXLSFile(String aFile) throws IOException { InputStream ExcelFileToRead = new FileInputStream(aFile); HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); HSSFSheet sheet = wb.getSheetAt(0);/*from w w w .j av a2s. co m*/ HSSFRow row; HSSFCell cell; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { System.out.print(cell.getStringCellValue() + " "); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue() + " "); } else { //U Can Handel Boolean, Formula, Errors } } System.out.println(); } }
From source file:vone.HSSFReadWrite.java
License:Apache License
/** * Method main// w ww . jav a 2s . c o m * * Given 1 argument takes that as the filename, inputs it and dumps the cell * values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet see * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second an * output filename (not write), attempts to fully read in the spreadsheet * and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". * If you take the output from the write test, you'll have a valid scenario. */ @SuppressWarnings("deprecation") public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } // int cells = row.getPhysicalNumberOfCells(); int cells = row.getLastCellNum(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; if (cell == null) { System.out.println("CELL col=" + c + " VALUE= Empty"); continue; } switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ypcnv.views.impl.FileXLS.java
License:Open Source License
/** * Check whether or not the given row's cells content have all the expected * names of data fields. <br>/*from w w w .j a v a 2 s. c o m*/ * <br> * XLS workbooks generated by MS-Outlook while contacts export all ways have * fixed number of data fields.<br> * <br> * Obtain meta data concerning distribution of header names. * * @param headerRow * - row to be processed. * @throws FileViewException * in case check is not passed. */ private void validateHeaderNames(HSSFRow headerRow) throws FileViewException { startColumnIdx = (int) headerRow.getFirstCellNum(); /* * It's Apache POI feature - getLastCellNum gets the index of the last * cell contained in this row PLUS ONE. */ stopColumnIdx = -1 + (int) headerRow.getLastCellNum(); /* Whether quantity of fields is valid. */ int foundQuantityOfDataFields = 1 + stopColumnIdx - startColumnIdx; if (foundQuantityOfDataFields != Contact2k3.getDataFieldsQuantity() || foundQuantityOfDataFields != FileXLSMeta.NUMBER_OF_DATA_FIELDS) { String message = String.format(FileXLSMeta.ERR_MESSAGE_WRONG_FIELD_QUANTITY, ((File) address).getAbsoluteFile(), FileXLSMeta.NUMBER_OF_DATA_FIELDS); LOG.error(message); throw new FileViewException(null, "Contact2k3Xls.checkHeaderNames()", message); } /* Whether data fields names are valid. */ for (int idx = startColumnIdx; idx <= stopColumnIdx; idx++) { HSSFCell currentCell = headerRow.getCell(idx); String xlsFieldName = currentCell.getStringCellValue(); String modelDataFieldName = null; modelDataFieldName = FileXLSNames.CONTAINER_FIELD_NAMING_MAP.get(xlsFieldName); if (modelDataFieldName == null) { ArrayList<String> foreignNamesSearchResultList = new ArrayList<String>(); /* * Add one by one mappings of other localizations, or add * explicit locality flag support. */ foreignNamesSearchResultList .add(FileXLSNames.CONTAINER_FIELD_NAMING_MAP_RUS2ENGL.get(xlsFieldName)); Iterator<String> namesWereFoundListIterator = foreignNamesSearchResultList.iterator(); while (namesWereFoundListIterator.hasNext()) { String foundForeignName = namesWereFoundListIterator.next(); if (foundForeignName != null) { modelDataFieldName = FileXLSNames.CONTAINER_FIELD_NAMING_MAP.get(foundForeignName); } } } if (modelDataFieldName != null) { dataColumnsSequenceMap.put(modelDataFieldName, idx); } else { String message = String.format(FileXLSMeta.ERR_MESSAGE_WRONG_FIELD_NAME, ((File) address).getAbsoluteFile(), xlsFieldName); throw new FileViewException(null, "Contact2k3Xls.checkHeaderNames()", message); } } }
From source file:ypcnv.views.impl.FileXLS.java
License:Open Source License
/** * With respect to cell type get cell's content. * //from w w w. j a v a 2s . c om * @param cell * - cell to be processed. * @return Cell's content. */ private String getCellContent(HSSFCell cell) { int foundCellType = cell.getCellType(); String cellContent = null; switch (foundCellType) { case Cell.CELL_TYPE_STRING: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellContent = cell.getBooleanCellValue() ? "TRUE" : "FALSE"; break; case Cell.CELL_TYPE_ERROR: byte xlsErrorCode = cell.getErrorCellValue(); if (xlsErrorCode == 0) { cellContent = ""; } else { cellContent = "XLS error code '" + xlsErrorCode + "'."; String message = "XLS cell type is 'ERROR', the XLS error code is '" + xlsErrorCode + "'." + " The cell row=" + cell.getRowIndex() + ", col=" + cell.getColumnIndex() + "."; LOG.info(message); } break; case Cell.CELL_TYPE_NUMERIC: HSSFDataFormatter numericFormat = new HSSFDataFormatter(); cellContent = numericFormat.formatCellValue(cell); // cellContent = cell.getNumericCellValue(); break; default: cellContent = ""; break; } return cellContent; }