List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java
License:Open Source License
public String getFormatedJSON(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); JSONObject jobj = new JSONObject(); HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return ("\"data\":[]"); }/*from w w w . j a v a 2 s . co m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); JSONObject temp = new JSONObject(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { temp.put("cell" + cell.getCellNum(), ""); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); temp.put("cell" + cell.getCellNum(), d.toString()); } } } catch (Exception ex) { } } jobj.append("data", temp); temp = null; } } if (input != null) { input.close(); } return jobj.toString(); }
From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java
License:Open Source License
public String extractText(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); String resultText = ""; HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return resultText; }/*from ww w . ja va2 s . c om*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { /* * if(HSSFDateUtil.isCellDateFormatted(cell) == * true) { resultText += * cell.getDateCellValue().toString() + " "; } else */ if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { resultText += " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { resultText += cell.getRichStringCellValue().toString() + " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); resultText += d.toString() + " "; } /* * else if(cell.getCellType() == * HSSFCell.CELL_TYPE_FORMULA){ resultText += * cell.getCellFormula() + " "; } */ } } catch (Exception ex) { } } resultText += "\n"; } } if (input != null) { input.close(); } return resultText; }
From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java
License:Open Source License
public String getFormatedJSON(String filepath) throws Exception { InputStream input = new BufferedInputStream(new FileInputStream(filepath)); JSONObject jobj = new JSONObject(); HSSFWorkbook wb = new HSSFWorkbook(input); if (wb == null) { return ("\"data\":[]"); }/* w w w . j ava 2 s .com*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); JSONObject temp = new JSONObject(); for (int k = 0; k < cNum; k++) { try { if ((cell = row.getCell((short) k)) != null) { if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { temp.put("cell" + cell.getCellNum(), ""); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); temp.put("cell" + cell.getCellNum(), d.toString()); } } } catch (Exception ex) { } } jobj.append("data", temp); temp = null; } } if (input != null) { input.close(); } return jobj.toString(); }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }/* ww w . jav a 2s . c o m*/ JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS1(String filename, int sheetNo, int startindex) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); ArrayList<String> arr = new ArrayList<String>(); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; JSONArray jArr = new JSONArray(); try {/*from w w w.ja va 2 s .c o m*/ for (int i = startindex; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); } for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { arr.add(val); continue; } ; String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } if (i == startindex) { // List of Headers (consider startindex row as a headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); obj.put(colHeader, val); } arr.add(val); } else { if (arr.get(j) != null) obj.put(arr.get(j), val); } } if (obj.length() > 0) { jArr.put(obj); } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public int dumpXLSFileData(String filename, int sheetNo, int startindex) throws ServiceException { int dumpedRows = 0; try {/* ww w.ja va 2s .c om*/ String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(sheetNo); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); for (int i = startindex; i <= maxRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df);//(df_full); //BUG:16085 val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) dumpedRows += importDao.dumpFileRow(tableName, dataArray.toArray()); } } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } catch (Exception ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } return dumpedRows; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid//w w w. ja v a 2s . c o m * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int maxSheetColCnt = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } int firstValidRec = 0; JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header firstValidRec = i; // if(i==0) { maxCol = row.getLastCellNum(); if (maxSheetColCnt < maxCol) // get max column count maxSheetColCnt = maxCol; // } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = cell.toString();//Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == firstValidRec) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxSheetColCnt); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * @param filename/*w ww . j a v a 2s . com*/ * @param sheetNo * @param startindex * @param importDao * @return * @throws ServiceException */ public static void dumpXLSFileData(String filename, int sheetNo, int startindex, ImportDAO importDao, HibernateTransactionManager txnManager) throws ServiceException { boolean commitedEx = false; DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("import_Tx"); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txnManager.getTransaction(def); Session session = txnManager.getSessionFactory().getCurrentSession(); try { String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df_full); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); int flushCounter = 0; for (int i = startindex; i <= maxRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) importDao.dumpFileRow(tableName, dataArray.toArray()); if (flushCounter % 30 == 0) { session.flush(); session.clear(); } flushCounter++; } } try { txnManager.commit(status); } catch (Exception ex) { commitedEx = true; throw ex; } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } catch (Exception ex) { if (!commitedEx) { //if exception occurs during commit then dont call rollback txnManager.rollback(status); } throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main// www . ja v a 2 s.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. */ 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) { try (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; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != null) { 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; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.mebigfatguy.yank.YankTask.java
License:Apache License
private Map<ColumnType, Integer> getColumnInfo(HSSFSheet sheet) { int firstRow = sheet.getFirstRowNum(); HSSFRow row = sheet.getRow(firstRow); Map<ColumnType, Integer> columnHeaders = new EnumMap<ColumnType, Integer>(ColumnType.class); for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); ++i) { HSSFCell cell = row.getCell(i);// w ww . j a v a2 s. c o m if (cell != null) { String value = cell.getStringCellValue(); if (value != null) { value = value.trim().toLowerCase(); if (value.startsWith("group")) { columnHeaders.put(ColumnType.GROUP_COLUMN, i); } else if (value.startsWith("artifact")) { columnHeaders.put(ColumnType.ARTIFACT_COLUMN, i); } else if (value.startsWith("type")) { columnHeaders.put(ColumnType.TYPE_COLUMN, i); } else if (value.startsWith("version")) { columnHeaders.put(ColumnType.VERSION_COLUMN, i); } else if (value.startsWith("classifier") || value.startsWith("alternate")) { columnHeaders.put(ColumnType.CLASSIFIER_COLUMN, i); } if (columnHeaders.size() == 4) { return columnHeaders; } } } } if (columnHeaders.size() >= 3) return columnHeaders; throw new BuildException( "Input yank xls file (" + xlsFile + ") does not contains GroupId, ArtifactId, or Version columns"); }