List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/*from w w w .ja va2 s.co m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/*ww w . j av a 2s .co m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:ambit.io.IteratingXLSReader.java
License:Open Source License
public Object next() { Molecule mol = null;//from w ww. j a v a2s .com try { HSSFRow row = (HSSFRow) iterator.next(); Iterator cols = row.cellIterator(); Hashtable properties = new Hashtable(); while (cols.hasNext()) { HSSFCell cell = (HSSFCell) cols.next(); Object value = cell.toString(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { /* try { HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: value = cellValue.getBooleanValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = cellValue.getNumberValue(); break; case HSSFCell.CELL_TYPE_STRING: value = cellValue.toString(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_ERROR: value = ""; break; // CELL_TYPE_FORMULA will never happen case HSSFCell.CELL_TYPE_FORMULA: break; } } catch (Exception x) { x.printStackTrace(); value = ""; } */ } if (smilesIndex == cell.getCellNum()) { try { mol = sp.parseSmiles(value.toString()); properties.put(AmbitCONSTANTS.SMILES, value.toString()); } catch (InvalidSmilesException x) { logger.warn("Invalid SMILES!\t" + value); properties.put(AmbitCONSTANTS.SMILES, "Invalid SMILES"); } } else properties.put(header.get(cell.getCellNum()).toString(), value); } if (mol == null) mol = new Molecule(); mol.setProperties(properties); } catch (Exception x) { logger.error(x); } return mol; }
From source file:ambit.io.IteratingXLSReader.java
License:Open Source License
protected void processHeader(HSSFRow row) { Iterator cols = row.cellIterator(); TreeMap columns = new TreeMap(); while (cols.hasNext()) { HSSFCell cell = (HSSFCell) cols.next(); String value = cell.getStringCellValue(); /*/*from w w w .j a v a 2 s . co m*/ System.out.print(cell.getCellNum()); System.out.print("\t"); System.out.println(value); */ if (value.equals(defaultSMILESHeader)) smilesIndex = cell.getCellNum(); columns.put(new Integer(cell.getCellNum()), value); } Iterator i = columns.keySet().iterator(); while (i.hasNext()) { Integer key = (Integer) i.next(); header.ensureCapacity(key); while (key.intValue() >= header.size()) header.add(""); header.set(key, columns.get(key)); } }
From source file:ambit2.io.IteratingXLSReader.java
License:Open Source License
public Object next() { IMolecule mol = null;/*from w ww .j a v a2 s. co m*/ try { HSSFRow row = (HSSFRow) iterator.next(); Iterator cols = row.cellIterator(); Hashtable properties = new Hashtable(); while (cols.hasNext()) { HSSFCell cell = (HSSFCell) cols.next(); Object value = cell.toString(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { /* try { HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: value = cellValue.getBooleanValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = cellValue.getNumberValue(); break; case HSSFCell.CELL_TYPE_STRING: value = cellValue.toString(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_ERROR: value = ""; break; // CELL_TYPE_FORMULA will never happen case HSSFCell.CELL_TYPE_FORMULA: break; } } catch (Exception x) { x.printStackTrace(); value = ""; } */ } if (smilesIndex == cell.getCellNum()) { try { mol = sp.parseSmiles(value.toString()); properties.put(AmbitCONSTANTS.SMILES, value.toString()); } catch (InvalidSmilesException x) { logger.warn("Invalid SMILES!\t" + value); properties.put(AmbitCONSTANTS.SMILES, "Invalid SMILES"); } } else properties.put(header.get(cell.getCellNum()).toString(), value); } if (mol == null) mol = new Molecule(); mol.setProperties(properties); } catch (Exception x) { logger.error(x); } return mol; }
From source file:axiom.util.TextExtractor.java
License:Open Source License
public static String msExcelExtractor(InputStream is) throws Exception { POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); StringBuffer sb = new StringBuffer(); final int numSheets = wb.getNumberOfSheets(); for (int k = 0; k < numSheets; k++) { HSSFSheet sheet = wb.getSheetAt(k); Iterator rIt = sheet.rowIterator(); while (rIt.hasNext()) { HSSFRow row = (HSSFRow) rIt.next(); Iterator cIt = row.cellIterator(); while (cIt.hasNext()) { HSSFCell cell = (HSSFCell) cIt.next(); sb.append(cell.toString()).append(" "); }// ww w. j a va2 s . c om } } return sb.toString(); }
From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java
private void formatHeaderRow(HSSFWorkbook workbook, HSSFRow row) { HSSFFont headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Arial"); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setBoldweight((short) 800); headerFont.setItalic(false);/*from w ww.j ava 2 s . co m*/ HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex()); headerStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex()); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); row.cellIterator().forEachRemaining((cell) -> { cell.setCellStyle(headerStyle); }); }
From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java
private void formatRow(HSSFWorkbook workbook, HSSFRow row) { HSSFCellStyle styleMid = workbook.createCellStyle(); styleMid.setBorderTop(HSSFCellStyle.BORDER_THIN); styleMid.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleInit = workbook.createCellStyle(); styleInit.cloneStyleFrom(styleMid);/*from ww w. j a v a2s.c om*/ styleInit.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleFinal = workbook.createCellStyle(); styleFinal.cloneStyleFrom(styleMid); styleFinal.setBorderRight(HSSFCellStyle.BORDER_THIN); row.cellIterator().forEachRemaining((cell) -> { int index = cell.getColumnIndex(); int numOfCols = row.getPhysicalNumberOfCells(); if (index == 0) { cell.setCellStyle(styleInit); } else if (index == (numOfCols - 1)) { cell.setCellStyle(styleFinal); } else { cell.setCellStyle(styleMid); } }); }
From source file:br.eti.rogerioaguilar.minhasclasses.util.excel.leitor.LeitorExcel.java
License:Open Source License
/** * Realiza o processamento de leitura seguindo o fluxo: * /*from www .ja v a2 s. com*/ * 1 - O leitor abre a planilha <br> * 2 - o leitor comea a ler as linhas e colunas da planilha <br> * 3 - Para cada linha encontrada: <br> * 3.1 - Caso a linha esteja na lista de linhas que devem ser processadas passada ao construtor: <br> * 3.1.1 - O leitor monta uma lista de vos contendos os dados das colunas para a linha atual que <br> * estejam dentro do padro de leitura passado ao construtor <br> * 3.1.2 - O leitor chama o mtodo LinhaListener.lendoLinha passando o mapa com os dados da linha <br> * e aguarda o final do processamento. <br> * 3.1.2.1 - Caso o mtodo do listener retorne true, o leitor continua o processamento da <br> * da planilha. Caso contrrio, o processamento da planilha interrompido <br> * Se o processamento da planilha continuar (de acordo com o parmetro de retorno <br> * do listener para a linha descrito anteriormente), o leitor chama o listener para <br> * a coluna para cada coluna da linha atual. O comportamento deste listener o mesmo <br> * do listener para a linha, ou seja, se o listener retornar false o processamento da <br> * planilha interrompido. * * @throws ParseException * @throws PlanilhaNaoEncontradaException caso o ndice da planilha no seja encontrado no arquivo * @throws FileNotFoundException caso o arquivo passado como parmetro no exista * @throws ListenerException caso ocorra algum erro na chamada de algum dos listeners * @throws IOException caso ocorra algum problema de io * */ public void processarLeituraPlanilha() throws ParseException, PlanilhaNaoEncontradaException, FileNotFoundException, IOException, ListenerException { try { log.debug("Inicializando o processamento da leitura do arquivo..."); log.debug("Dados para o processamento --> " + this); POIFSFileSystem fs = null; if (this.streamArquivo != null) { fs = new POIFSFileSystem(streamArquivo); } else if (this.caminhoArquivoExcel != null) { fs = new POIFSFileSystem(new FileInputStream(this.caminhoArquivoExcel)); } else { throw new IllegalArgumentException( "No foi definido um stream para o arquivo nem um caminho para o arquivo!"); } log.debug("Processando a string de entrada --> " + this.strPadraoLeitura); Map mapaLinhasAProcessar = LeitorExcelReader .getMapaEntradas(new ByteArrayInputStream(this.strPadraoLeitura.getBytes())); log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); log.debug("Mapa retornado --> " + mapaLinhasAProcessar); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet planilha = wb.getSheetAt(this.indicePlanilha - 1); if (planilha == null) { log.error("Planilha no encontrada no ndice -->" + this.indicePlanilha); throw new PlanilhaNaoEncontradaException( "Planilha no encontrada no ndice -->" + this.indicePlanilha); } else { log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); boolean processarTodasAsLinhas = (mapaLinhasAProcessar.get("*") != null); boolean processarTodasAsColunas = false; boolean continuarProcessamentoLinha = true; Map propriedadesListenerLinha = new HashMap(); Map propriedadesListenerColuna = new HashMap(); List listaColunas = null; List listaVosColunas = new LinkedList(); if (processarTodasAsLinhas) { log.debug("Processando todas as linhas..."); } Iterator itLinhas = planilha.rowIterator(); while (itLinhas.hasNext() && continuarProcessamentoLinha) { HSSFRow linha = (HSSFRow) itLinhas.next(); propriedadesListenerLinha.clear(); listaVosColunas.clear(); propriedadesListenerLinha.put(LinhaListener.CHAVE_LINHA_OBJETO_ORIGINAL_POI, linha); int intLinhaAtual = linha.getRowNum() + 1; log.debug("Processando linha --> " + intLinhaAtual); if (!processarTodasAsLinhas) { listaColunas = getListaColunasLinha("" + intLinhaAtual, mapaLinhasAProcessar); } else { listaColunas = getListaColunasLinha("*", mapaLinhasAProcessar); } boolean processarLinhaAtual = processarTodasAsLinhas || (listaColunas != null); if (processarLinhaAtual) { Iterator itColunas = linha.cellIterator(); processarTodasAsColunas = (listaColunas != null) && (listaColunas.size() > 0) && ("" + listaColunas.get(0)).equals("*"); while (itColunas.hasNext()) { HSSFCell celula = (HSSFCell) itColunas.next(); int intCelulaAtual = celula.getCellNum() + 1; boolean processarColunaAtual = processarTodasAsColunas || ((listaColunas != null) && (listaColunas.size() > 0) && listaColunas.indexOf(new Long(intCelulaAtual)) != -1); LinhaColunaListenerVo linhaColunaListenerVo = new LinhaColunaListenerVo(); linhaColunaListenerVo.setLinha(intLinhaAtual); linhaColunaListenerVo.setColuna(intCelulaAtual); linhaColunaListenerVo.setCelulaAtual(celula); if (processarColunaAtual) { if (celula != null) { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " deve ser processada..."); switch (celula.getCellType()) { case HSSFCell.CELL_TYPE_STRING: linhaColunaListenerVo.setValorStr(celula.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: linhaColunaListenerVo .setValorNumerico(new Double(celula.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: linhaColunaListenerVo.setCelulaFormula(true); linhaColunaListenerVo.setValorStrFormula(celula.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: linhaColunaListenerVo.setCelulaContemErro(true); linhaColunaListenerVo.setErro(new Byte(celula.getErrorCellValue())); break; case HSSFCell.CELL_TYPE_BOOLEAN: linhaColunaListenerVo .setValorBoolean(new Boolean(celula.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_BLANK: linhaColunaListenerVo.setCelulaBranca(true); linhaColunaListenerVo.setValorStr(""); break; } } else { log.warn("Clula nula!"); linhaColunaListenerVo.setCelulaNula(true); } listaVosColunas.add(linhaColunaListenerVo); } else { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " no deve ser processada..."); } } if (this.linhaListener != null) { log.debug("Chamando o listener para a linha --> " + intLinhaAtual); Collections.sort(listaVosColunas, new Comparator() { public int compare(Object arg0, Object arg1) { int colunaUm = ((LinhaColunaListenerVo) arg0).getColuna(); int colunaDois = ((LinhaColunaListenerVo) arg1).getColuna(); if (colunaUm < colunaDois) { return -1; } else if (colunaUm > colunaDois) { return 1; } return 0; } }); propriedadesListenerLinha.put(LinhaListener.LISTA_VOS_LINHA, listaVosColunas); continuarProcessamentoLinha = this.linhaListener.lendoLinha(intLinhaAtual, propriedadesListenerLinha); if (!continuarProcessamentoLinha) { log.debug( "Listener retornou boolean false indicando que o processamento deve ser interrompido!"); } } else { log.debug("Listener no configurado para a linha --> " + intLinhaAtual); } if (this.colunaListener != null) { Iterator itColunasVoListener = listaVosColunas.iterator(); boolean continuarProcessamentoColunasnaLinha = true; while (itColunasVoListener.hasNext() && continuarProcessamentoColunasnaLinha) { propriedadesListenerColuna.clear(); LinhaColunaListenerVo voAtual = (LinhaColunaListenerVo) itColunasVoListener.next(); propriedadesListenerColuna.put(ColunaListener.CHAVE_VO_COLUNA, voAtual); propriedadesListenerColuna.put(ColunaListener.CHAVE_COLUNA_OBJETO_ORIGINAL_POI, voAtual.getCelulaAtual()); log.debug("Chamando o listener para a coluna --> " + voAtual.getColuna() + " na linha " + voAtual.getLinha()); continuarProcessamentoColunasnaLinha = this.colunaListener.lendoColuna( voAtual.getLinha(), voAtual.getColuna(), propriedadesListenerColuna); if (!continuarProcessamentoColunasnaLinha) { log.debug( "Listener de coluna retornou boolean false indicando que o processamento das colunas na linha " + voAtual.getLinha() + " deve ser interrompido!"); } } } else { log.debug("Listener no configurado para processamento das colunas"); } } else { log.debug("Linha --> " + intLinhaAtual + " no ser processada!"); } } } log.debug("Processamento da planilha realizado com sucesso!"); } catch (ParseException e) { e.printStackTrace(); log.error("Erro ao processar a string de entrada ", e); throw e; } catch (FileNotFoundException e) { e.printStackTrace(); log.error("Arquivo " + this.caminhoArquivoExcel + " no encontrado", e); throw e; } catch (IOException e) { e.printStackTrace(); log.error("Erro ao abrir o arquivo " + this.caminhoArquivoExcel, e); throw e; } catch (ListenerException e) { e.printStackTrace(); log.error("Erro ao processar o listener ", e); throw e; } }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsSpreadsheet(File spreadsheet) { /*//from ww w. j av a 2 s . co m Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { HSSFRow row; currentSpreadsheetFile = new HSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row = (HSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellTypeEnum()) { default: // cellValue = cell.getCellFormula(); cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); case BLANK: break; case STRING: break; } if (!cell.getStringCellValue().isEmpty()) { values.add(cell.getStringCellValue()); values.add(","); // System.out.println("HOLD IT"); } else { values.add("0"); values.add(","); // System.out.println("OBJECTION!!"); } //System.out.print(cell.getStringCellValue() + " \t\t " ); } //System.out.println(); values.add(";"); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } //System.out.println(values.get(0)); return values; }