List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:com.kcs.action.FrwImportAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {//from ww w . ja va2s . c om InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetfrw>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetfrw datasetfrw = new Datasetfrw(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetfrw.setOrgId(row.getCell(index++).toString()); datasetfrw.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetfrw.setCurrCode(row.getCell(index++).toString()); datasetfrw.setCommInLieuRateForDepsit(row.getCell(index++).toString()); datasetfrw.setMinCommInLieuForDepsit(row.getCell(index++).toString()); datasetfrw.setMaxCommInLieuForDepsit(row.getCell(index++).toString()); datasetfrw.setInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setMinInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setMaxInwTransfFeeForDepsit(row.getCell(index++).toString()); datasetfrw.setOthFeeDescForDepsit(row.getCell(index++).toString()); datasetfrw.setCommInLieuRateForWithdw(row.getCell(index++).toString()); datasetfrw.setMinCommInLieuRateFWithdw(row.getCell(index++).toString()); datasetfrw.setMaxCommInLieuRateFWithdw(row.getCell(index++).toString()); datasetfrw.setWithdwFeeForOthTransf(row.getCell(index++).toString()); datasetfrw.setEffectiveDate(convertDate(row.getCell(index++).toString())); datasetfrw.setEndDate(convertDate(row.getCell(index++).toString())); datasetfrw.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetfrw.setUpdBy(getCurrentLoginId()); datasetfrw.setUpdDate(DateUtil.getCurrentDateTime()); index++; index++; datasetfrw.setSysCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetfrw); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_FRW", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.ImportExcelDsDipAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {/*from ww w . j a v a2 s .com*/ InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetdip>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetdip datasetdip = new Datasetdip(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(3).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); logger.debug("dateFromJsp >>> " + dateFromJsp); logger.debug("dateFromExcel >>> " + dateFromExcel); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetdip.setOrgId(row.getCell(index++).toString()); datasetdip.setDataPvdrBrcNo(row.getCell(index++).toString()); datasetdip.setFiRptGrp(row.getCell(index++).toString()); datasetdip.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetdip.setItemType(row.getCell(index++).toString()); datasetdip.setItemDesc(row.getCell(index++).toString()); datasetdip.setCtryId(row.getCell(index++).toString()); datasetdip.setCurr(row.getCell(index++).toString()); datasetdip.setAmt(new BigDecimal(row.getCell(index++).toString())); datasetdip.setUpdDate(DateUtil.getCurrentDateTime()); datasetdip.setUpdBy(getCurrentLoginId()); index++; index++; datasetdip.setSysCode(row.getCell(index++).toString()); datasetdip.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetdip.setCustCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetdip); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_DIP", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.IrfImportAction.java
public void excelToList() throws Exception { String filePath = servletRequest.getSession().getServletContext().getRealPath("/"); try {// www .j av a2 s .c o m InputStream input = new BufferedInputStream(new FileInputStream(this.toBeUploaded)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); resultsFromExcel = new ArrayList<Datasetirf>(); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetirf datasetirf = new Datasetirf(); Date dateFromJsp = DateUtil.convertDateFromJsp(dataSetDate); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } datasetirf.setOrgId(row.getCell(0).toString()); datasetirf.setDataSetDate( DateUtil.parse(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); datasetirf.setArrgmentTye(row.getCell(2).toString()); datasetirf.setInvPartyTye(row.getCell(3).toString()); datasetirf.setCurrCode(row.getCell(4).toString()); datasetirf.setDepsitTerm(Long.valueOf(row.getCell(5).toString())); datasetirf.setDepsitTermUnt(row.getCell(6).toString()); datasetirf.setBalTierAmt(row.getCell(7).toString()); datasetirf .setInterestRate(NumberUtil.convertToBigDecimal(row.getCell(8).toString()).setScale(2)); datasetirf.setEffectiveDate( DateUtil.parse(row.getCell(9).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); if (!row.getCell(10).toString().equals("null") || row.getCell(10).toString().equals(null)) { datasetirf.setEndDate( DateUtil.parse(row.getCell(10).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX)); } else { datasetirf.setEndDate(null); } datasetirf.setSeq(Long.valueOf(row.getCell(11).toString())); datasetirf.setUpdBy(getCurrentLoginId()); datasetirf.setUpdDate(DateUtil.getCurrentDateTime()); datasetirf.setSysCode(row.getCell(14).toString()); resultsFromExcel.add(datasetirf); } checkColName++; } setResultsFromExcel(resultsFromExcel); session.put("EXCEL_TO_LIST", resultsFromExcel); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kcs.action.OffImportAction.java
public void excelToList() throws Exception { String filePath = getServletRequest().getSession().getServletContext().getRealPath("/"); try {/* w w w . j a va 2 s .co m*/ InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded())); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setResultsFromExcel(new ArrayList<Datasetoff>()); int checkColName = 0; setCheckDatasetDate("true"); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (checkColName != 0) { Datasetoff datasetoff = new Datasetoff(); Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate()); Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX); if (dateFromJsp.compareTo(dateFromExcel) != 0) { setCheckDatasetDate("false"); break; } int index = 0; datasetoff.setOrgId(row.getCell(index++).toString()); datasetoff.setDataSetDate(convertDate(row.getCell(index++).toString())); datasetoff.setArrgmentTye(row.getCell(index++).toString()); datasetoff.setCurrCode(row.getCell(index++).toString()); datasetoff.setMinimOpenAcctAmt(row.getCell(index++).toString()); datasetoff.setMinimBalForMaintenanceFee(row.getCell(index++).toString()); datasetoff.setMaintenanceFee(row.getCell(index++).toString()); datasetoff.setInactiveTerm(convertBigDecimal(row.getCell(index++).toString())); datasetoff.setInactiveTermUnt(row.getCell(index++).toString()); datasetoff.setInactiveFee(row.getCell(index++).toString()); datasetoff.setEffectiveDate(convertDate(row.getCell(index++).toString())); datasetoff.setEndDate(convertDate(row.getCell(index++).toString())); datasetoff.setSeq(Long.valueOf(row.getCell(index++).toString())); datasetoff.setUpdBy(getCurrentLoginId()); datasetoff.setUpdDate(DateUtil.getCurrentDateTime()); index++; index++; datasetoff.setSysCode(row.getCell(index++).toString()); getResultsFromExcel().add(datasetoff); } checkColName++; } setResultsFromExcel(getResultsFromExcel()); session.put("EXCEL_TO_LIST_FRF", getResultsFromExcel()); } catch (IOException ex) { ex.printStackTrace(); logger.error("Error >>> " + ex.getMessage()); } }
From source file:com.kongwu.insweb.utils.ReadExcel.java
License:Apache License
/** * @param filepath excel/*from w w w .jav a 2 s . com*/ * * * * @return ??,:list<usrid,query,biz,semantic> * : * : * @throws IOException */ public static List<List<String>> readTestset(String filepath) throws IOException { FileInputStream fileIn = null; List<List<String>> testsetList = new ArrayList<List<String>>(); /**?excel**/ try { fileIn = new FileInputStream(filepath); /**excel 2007**/ if (filepath.endsWith(".xlsx")) { Workbook wb = new XSSFWorkbook(fileIn); Sheet sheet = wb.getSheetAt(0); Row row = null; Cell cell = null; int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1 logger.info("?" + rowSize); if (rowSize < 2) return null; // ?, for (int i = 1; i < rowSize; i++) { row = sheet.getRow(i); if (row == null) continue; int cellSize = row.getLastCellNum(); logger.info("?" + cellSize); /** * ?cell */ List<String> list = new ArrayList<String>(); for (int j = 0; j < cellSize; j++) { cell = row.getCell(i); if (cell != null) { list.add(cell.toString()); } } testsetList.add(list); } } else { POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1 logger.info("?" + rowSize); if (rowSize < 2)// ??? return null; // ?, for (int i = 1; i < rowSize; i++) { row = sheet.getRow(i); if (row == null) continue; int cellSize = row.getLastCellNum(); logger.info("?" + cellSize); List<String> list = new ArrayList<String>(); for (int j = 0; j < cellSize; j++) { cell = row.getCell(j); if (cell != null) { list.add(cell.toString()); } } testsetList.add(list); } } } finally { if (fileIn != null) fileIn.close(); } return testsetList; }
From source file:com.krawler.esp.fileparser.word.DocxParser.java
License:Open Source License
public String extractTextDocx(String filepath) throws FileNotFoundException, IOException { StringBuilder sb = new StringBuilder(); try {//from ww w.j a v a 2s .c o m FileInputStream fis = new FileInputStream(filepath); POIOLE2TextExtractor poitex = ExtractorFactory.createExtractor(new POIFSFileSystem(fis)); sb.append(poitex.getText()); // XWPFDocument doc = new XWPFDocument(fis); // XWPFWordExtractor ex = new XWPFWordExtractor(doc); // sb.append(ex.getText()); } catch (Exception e) { logger.warn(e.getMessage(), e); } return sb.toString(); }
From source file:com.krawler.esp.fileparser.word.ExtractWordFile.java
License:Open Source License
public String extractText(String filepath) throws FastSavedException, IOException { InputStream iStream = new BufferedInputStream(new FileInputStream(filepath)); POIFSFileSystem fsys = new POIFSFileSystem(iStream); // load our POIFS document streams. DocumentEntry headerProps = (DocumentEntry) fsys.getRoot().getEntry("WordDocument"); DocumentInputStream din = fsys.createDocumentInputStream("WordDocument"); byte[] header = new byte[headerProps.getSize()]; din.read(header);//from w w w . j av a 2s .c om din.close(); int info = LittleEndian.getShort(header, 0xa); if ((info & 0x4) != 0) { throw new FastSavedException("Fast-saved files are unsupported at this time"); } if ((info & 0x100) != 0) { System.out.println("This document is password protected"); } // determine the version of Word this document came from. int nFib = LittleEndian.getShort(header, 0x2); switch (nFib) { case 101: case 102: case 103: case 104: // this is a Word 6.0 doc send it to the extractor for that version. Word6Extractor oldExtractor = new Word6Extractor(); return oldExtractor.extractText(header); } // Get the information we need from the header boolean useTable1 = (info & 0x200) != 0; // get the location of the piece table int complexOffset = LittleEndian.getInt(header, 0x1a2); // determine which table stream we must use. String tableName = null; if (useTable1) { tableName = "1Table"; } else { tableName = "0Table"; } DocumentEntry table = (DocumentEntry) fsys.getRoot().getEntry(tableName); byte[] tableStream = new byte[table.getSize()]; din = fsys.createDocumentInputStream(tableName); din.read(tableStream); din.close(); int chpOffset = LittleEndian.getInt(header, 0xfa); int chpSize = LittleEndian.getInt(header, 0xfe); int fcMin = LittleEndian.getInt(header, 0x18); // load our text pieces and our character runs ComplexFileTable cft = new ComplexFileTable(header, tableStream, complexOffset, fcMin); TextPieceTable tpt = cft.getTextPieceTable(); List textPieces = tpt.getTextPieces(); CHPBinTable cbt = new CHPBinTable(header, tableStream, chpOffset, chpSize, fcMin, tpt); // make the POIFS objects available for garbage collection din = null; fsys = null; table = null; headerProps = null; List textRuns = cbt.getTextRuns(); Iterator runIt = textRuns.iterator(); Iterator textIt = textPieces.iterator(); TextPiece currentPiece = (TextPiece) textIt.next(); int currentTextStart = currentPiece.getStart(); int currentTextEnd = currentPiece.getEnd(); WordTextBuffer finalTextBuf = new WordTextBuffer(); // iterate through all text runs extract the text only if they haven't // been // deleted while (runIt.hasNext()) { CHPX chpx = (CHPX) runIt.next(); boolean deleted = isDeleted(chpx.getGrpprl()); if (deleted) { continue; } int runStart = chpx.getStart(); int runEnd = chpx.getEnd(); while (runStart >= currentTextEnd) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); currentTextEnd = currentPiece.getEnd(); } if (runEnd < currentTextEnd) { String str = currentPiece.substring(runStart - currentTextStart, runEnd - currentTextStart); finalTextBuf.append(str); } else if (runEnd > currentTextEnd) { while (runEnd > currentTextEnd) { String str = currentPiece.substring(runStart - currentTextStart, currentTextEnd - currentTextStart); finalTextBuf.append(str); if (textIt.hasNext()) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); runStart = currentTextStart; currentTextEnd = currentPiece.getEnd(); } else { return finalTextBuf.toString(); } } String str = currentPiece.substring(0, runEnd - currentTextStart); finalTextBuf.append(str); } else { String str = currentPiece.substring(runStart - currentTextStart, runEnd - currentTextStart); if (textIt.hasNext()) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); currentTextEnd = currentPiece.getEnd(); } finalTextBuf.append(str); } } return finalTextBuf.toString(); }
From source file:com.krawler.esp.fileparser.wordparser.ExtractWordFile.java
License:Open Source License
public String extractText(String filepath) throws FastSavedException, IOException { InputStream iStream = new BufferedInputStream(new FileInputStream(filepath)); ArrayList text = new ArrayList(); POIFSFileSystem fsys = new POIFSFileSystem(iStream); // load our POIFS document streams. DocumentEntry headerProps = (DocumentEntry) fsys.getRoot().getEntry("WordDocument"); DocumentInputStream din = fsys.createDocumentInputStream("WordDocument"); byte[] header = new byte[headerProps.getSize()]; din.read(header);/*ww w .ja va2 s .c om*/ din.close(); int info = LittleEndian.getShort(header, 0xa); if ((info & 0x4) != 0) { throw new FastSavedException("Fast-saved files are unsupported at this time"); } if ((info & 0x100) != 0) { System.out.println("This document is password protected"); } // determine the version of Word this document came from. int nFib = LittleEndian.getShort(header, 0x2); // Get the information we need from the header boolean useTable1 = (info & 0x200) != 0; // get the location of the piece table int complexOffset = LittleEndian.getInt(header, 0x1a2); // determine which table stream we must use. String tableName = null; if (useTable1) { tableName = "1Table"; } else { tableName = "0Table"; } DocumentEntry table = (DocumentEntry) fsys.getRoot().getEntry(tableName); byte[] tableStream = new byte[table.getSize()]; din = fsys.createDocumentInputStream(tableName); din.read(tableStream); din.close(); int chpOffset = LittleEndian.getInt(header, 0xfa); int chpSize = LittleEndian.getInt(header, 0xfe); int fcMin = LittleEndian.getInt(header, 0x18); ComplexFileTable cft = new ComplexFileTable(header, tableStream, complexOffset, fcMin); TextPieceTable tpt = cft.getTextPieceTable(); switch (nFib) { case 101: case 102: case 103: case 104: // this is a Word 6.0 doc send it to the extractor for that version. Word6Extractor oldExtractor = new Word6Extractor(); return oldExtractor.extractText(header, tpt); } CHPBinTable cbt = new CHPBinTable(header, tableStream, chpOffset, chpSize, fcMin, tpt); // load our text pieces and our character runs List textPieces = tpt.getTextPieces(); // make the POIFS objects available for garbage collection din = null; fsys = null; table = null; headerProps = null; List textRuns = cbt.getTextRuns(); Iterator runIt = textRuns.iterator(); Iterator textIt = textPieces.iterator(); TextPiece currentPiece = (TextPiece) textIt.next(); int currentTextStart = currentPiece.getStart(); int currentTextEnd = currentPiece.getEnd(); WordTextBuffer finalTextBuf = new WordTextBuffer(); // iterate through all text runs extract the text only if they haven't // been // deleted while (runIt.hasNext()) { CHPX chpx = (CHPX) runIt.next(); boolean deleted = isDeleted(chpx.getGrpprl()); if (deleted) { continue; } int runStart = chpx.getStart(); int runEnd = chpx.getEnd(); while (runStart >= currentTextEnd) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); currentTextEnd = currentPiece.getEnd(); } if (runEnd < currentTextEnd) { String str = currentPiece.substring(runStart - currentTextStart, runEnd - currentTextStart); finalTextBuf.append(str); } else if (runEnd > currentTextEnd) { while (runEnd > currentTextEnd) { String str = currentPiece.substring(runStart - currentTextStart, currentTextEnd - currentTextStart); finalTextBuf.append(str); if (textIt.hasNext()) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); runStart = currentTextStart; currentTextEnd = currentPiece.getEnd(); } else { return finalTextBuf.toString(); } } String str = currentPiece.substring(0, runEnd - currentTextStart); finalTextBuf.append(str); } else { String str = currentPiece.substring(runStart - currentTextStart, runEnd - currentTextStart); if (textIt.hasNext()) { currentPiece = (TextPiece) textIt.next(); currentTextStart = currentPiece.getStart(); currentTextEnd = currentPiece.getEnd(); } finalTextBuf.append(str); } } return finalTextBuf.toString(); }
From source file:com.krawler.esp.servlets.fileUploadXLS.java
License:Open Source License
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * @param request servlet request/*from w w w . java 2 s .co m*/ * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); JSONObject jobj = new JSONObject(); try { jobj.put("success", true); FileItemFactory factory = new DiskFileItemFactory(4096, new File("/tmp")); ServletFileUpload upload = new ServletFileUpload(factory); upload.setSizeMax(10000000); List fileItems = upload.parseRequest(request); Iterator i = fileItems.iterator(); String destinationDirectory = StorageHandler.GetDocStorePath() + "xlsfiles"; String fileName = null; while (i.hasNext()) { FileItem fi = (FileItem) i.next(); if (fi.isFormField()) continue; fileName = fi.getName(); fi.write(new File(destinationDirectory, fileName)); } POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + fileName)); HSSFWorkbook wb = new HSSFWorkbook(fs); int count = wb.getNumberOfSheets(); JSONArray jArr = new JSONArray(); for (int x = 0; x < count; x++) { JSONObject obj = new JSONObject(); obj.put("name", wb.getSheetName(x)); obj.put("index", x); jArr.put(obj); } jobj.put("file", destinationDirectory + "/" + fileName); jobj.put("data", jArr); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); } catch (Exception e) { Logger.getLogger(fileUploadXLS.class.getName()).log(Level.SEVERE, null, e); try { jobj.put("msg", e.getMessage()); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception ex) { } } finally { out.println(jobj); } }
From source file:com.krawler.esp.servlets.XLSDataExtractor.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(); }/*from w ww .j a va2s .com*/ JSONArray jArr = new JSONArray(); for (int i = 0; i <= noOfRowsDisplayforSample; i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { jArr.put(obj); continue; } if (maxCol < row.getLastCellNum()) maxCol = row.getLastCellNum(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); if (cell == null) continue; String colHeader = new CellReference(i, j).getCellRefParts()[2]; String val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: val = Double.toString(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); String df_full = "yyyy-MM-dd"; DateFormat sdf = new SimpleDateFormat(df_full); val = sdf.format(df); } break; case Cell.CELL_TYPE_STRING: val = cell.getRichStringCellValue().getString(); break; } if (i == 0) { // List of Headers (Consider first row as Headers) jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); } obj.put(colHeader, val); } jArr.put(obj); } 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", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }