List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:bo.com.offercruzmail.LectorBandejaCorreo.java
private Multipart procesarPorAdjunto(Message mensaje, Integer idUsuario) throws MessagingException { adjunto = null;//w w w .ja v a2s . c o m try { //No se pudo procesar por asunto, leer el adjunto si tiene adjunto = UtilitariosMensajes.bajarPrimerAdjunto(mensaje); } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); return FormadorMensajes.getMensajeUsuarioAyuda(); } if (adjunto == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } FileInputStream fis = null; try { Workbook libro; fis = new FileInputStream(adjunto); libro = WorkbookFactory.create(fis); Sheet hoja = libro.getSheetAt(0); Row fila = hoja.getRow(0); if (fila == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } Cell celda = fila.getCell(0); if (celda == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } String nombreEntidad = HojaExcelHelper.getValorCelda(celda).toLowerCase(); interprete = InterpretadorMensajeGenerico.getMapaObjetos().get(nombreEntidad); if (interprete == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } interprete.setLectorBandejaCorreo(this); interprete.setIdUsuario(idUsuario); interprete.setNombreEntidad(nombreEntidad); return interprete.interpretarHojaExcel(hoja); } catch (IOException | InvalidFormatException ex) { LOG.log(Level.SEVERE, "Error Leyendo adjunto", ex); return FormadorMensajes.getMensajeUsuarioAyuda(); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(LectorBandejaCorreo.class.getName()).log(Level.SEVERE, null, ex); } } } }
From source file:bouttime.fileinput.ExcelFileInput.java
License:Open Source License
/** * Input wrestlers from a MS Excel formatted file. * * @param file File to read data from/*from w ww . j a v a 2s . com*/ * @param config Map of config parameters for column indexes, start and stop * row indexes, and the sheet number * @param dao Data access object * * @return A FileInputResult object with the results of the input operation */ private FileInputResult addWrestlersFromFile(File file, Map config, Dao dao) { Integer recordsProcessed = Integer.valueOf(0); Integer recordsAccepted = Integer.valueOf(0); Integer recordsRejected = Integer.valueOf(0); List<String> rejects = new ArrayList<String>(); try { int sheetNumber = Integer.parseInt((String) config.get("sheet")) - 1; int startRow = Integer.parseInt((String) config.get("startRow")) - 1; int endRow = Integer.parseInt((String) config.get("endRow")); int fNameCol = Integer.parseInt((String) config.get("firstName")) - 1; int lNameCol = Integer.parseInt((String) config.get("lastName")) - 1; int tNameCol = Integer.parseInt((String) config.get("teamName")) - 1; int gNameCol = Integer.parseInt((String) config.get("geo")) - 1; int classCol = Integer.parseInt((String) config.get("classification")) - 1; int divCol = Integer.parseInt((String) config.get("division")) - 1; int wtClassCol = Integer.parseInt((String) config.get("weightClass")) - 1; int actWtCol = Integer.parseInt((String) config.get("actualWeight")) - 1; int levelCol = Integer.parseInt((String) config.get("level")) - 1; int idCol = Integer.parseInt((String) config.get("serialNumber")) - 1; InputStream inp = new FileInputStream(file); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(sheetNumber); logger.info("Excel File Input configuration :" + "\n sheet=" + sheetNumber + "\n startRow=" + startRow + "\n endRow=" + endRow + "\n first=" + fNameCol + "\n last=" + lNameCol + "\n team=" + tNameCol + "\n geo=" + gNameCol + "\n class=" + classCol + "\n div=" + divCol + "\n" + "\n wtClass=" + wtClassCol + "\n actWt=" + actWtCol + "\n level=" + levelCol + "\n id=" + idCol); int i = startRow; while (i < endRow) { Wrestler w = new Wrestler(); Row row = sheet.getRow(i); if (row == null) { logger.warn("Row is null : " + i); i++; continue; } if (fNameCol >= 0) { String fName = row.getCell(fNameCol).getRichStringCellValue().getString(); // Should always have a first name, so this is a // reasonable check for the end of data. if (fName.isEmpty()) break; // found end of data w.setFirstName(fName.trim()); } if (lNameCol >= 0) { String lName = row.getCell(lNameCol).getRichStringCellValue().getString(); // Should always have a last name, so this is a // reasonable check for the end of data. if (lName.isEmpty()) break; // found end of data w.setLastName(lName.trim()); } if (divCol >= 0) { String div; if (row.getCell(divCol).getCellType() == Cell.CELL_TYPE_STRING) { div = row.getCell(divCol).getRichStringCellValue().getString(); } else { div = Long.valueOf(Double.valueOf(row.getCell(divCol).getNumericCellValue()).longValue()) .toString(); } w.setAgeDivision(div.trim()); } if (wtClassCol >= 0) { String wtClass; if (row.getCell(wtClassCol).getCellType() == Cell.CELL_TYPE_STRING) { wtClass = row.getCell(wtClassCol).getRichStringCellValue().getString(); } else { wtClass = Long .valueOf(Double.valueOf(row.getCell(wtClassCol).getNumericCellValue()).longValue()) .toString(); } w.setWeightClass(wtClass.trim()); } if (actWtCol >= 0) { String actWt; if (row.getCell(actWtCol).getCellType() == Cell.CELL_TYPE_STRING) { actWt = row.getCell(actWtCol).getRichStringCellValue().getString(); } else { actWt = Long .valueOf(Double.valueOf(row.getCell(actWtCol).getNumericCellValue()).longValue()) .toString(); } w.setActualWeight(actWt.trim()); } if (classCol >= 0) { String classification; if (row.getCell(classCol).getCellType() == Cell.CELL_TYPE_STRING) { classification = row.getCell(classCol).getRichStringCellValue().getString(); } else { classification = Long .valueOf(Double.valueOf(row.getCell(classCol).getNumericCellValue()).longValue()) .toString(); } w.setClassification(classification.trim()); } if (tNameCol >= 0) { String tName; if (row.getCell(tNameCol).getCellType() == Cell.CELL_TYPE_STRING) { tName = row.getCell(tNameCol).getRichStringCellValue().getString(); } else { tName = Long .valueOf(Double.valueOf(row.getCell(tNameCol).getNumericCellValue()).longValue()) .toString(); } w.setTeamName(tName.trim()); } if (gNameCol >= 0) { String gName; if (row.getCell(gNameCol).getCellType() == Cell.CELL_TYPE_STRING) { gName = row.getCell(gNameCol).getRichStringCellValue().getString(); } else { gName = Long .valueOf(Double.valueOf(row.getCell(gNameCol).getNumericCellValue()).longValue()) .toString(); } w.setGeo(gName.trim()); } if (idCol >= 0) { String id; if (row.getCell(idCol).getCellType() == Cell.CELL_TYPE_STRING) { id = row.getCell(idCol).getRichStringCellValue().getString(); } else { id = Long.valueOf(Double.valueOf(row.getCell(idCol).getNumericCellValue()).longValue()) .toString(); } w.setSerialNumber(id.trim()); } if (levelCol >= 0) { String level; if (row.getCell(levelCol).getCellType() == Cell.CELL_TYPE_STRING) { level = row.getCell(levelCol).getRichStringCellValue().getString(); } else { level = Long .valueOf(Double.valueOf(row.getCell(levelCol).getNumericCellValue()).longValue()) .toString(); } w.setLevel(level.trim()); } recordsProcessed++; if (dao.addWrestler(w)) { recordsAccepted++; logger.debug("Added wrestler : " + w); } else { recordsRejected++; rejects.add(String.format("%s %s", w.getFirstName(), w.getLastName())); logger.warn("Duplicate: " + w.getFirstName() + " " + w.getLastName()); } i++; } } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException ife) { JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame(); JOptionPane.showMessageDialog(mainFrame, "Error while handling the spreadsheet file.\n\n" + "This is not a file in an Excel file.", "Spreadsheet file error", JOptionPane.ERROR_MESSAGE); logger.error(ife.getLocalizedMessage() + "\n" + Arrays.toString(ife.getStackTrace())); } catch (Exception e) { JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame(); JOptionPane.showMessageDialog(mainFrame, "Error while handling the spreadsheet file.\n\n" + e, "Spreadsheet file error", JOptionPane.ERROR_MESSAGE); logger.error(e.getLocalizedMessage() + "\n" + Arrays.toString(e.getStackTrace())); } return (new FileInputResult(recordsProcessed, recordsAccepted, recordsRejected, rejects)); }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
private String workbook2xml(org.apache.poi.ss.usermodel.Workbook workbook) { String result = null;//from w w w . j a v a 2 s.c o m StringBuffer sb = null; Sheet sheet = null; if (workbook != null && workbook.getSheetAt(0) != null) { String newLine = System.getProperty("line.separator"); sb = new StringBuffer(); sb.append("<?xml version=\"1.0\" ?>"); sb.append(newLine); sb.append("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">"); sb.append(newLine); sb.append(newLine); sb.append("<workbook>"); sb.append(newLine); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { sheet = workbook.getSheetAt(i); if (sheet != null && sheet.rowIterator().hasNext()) { sb.append("\t"); sb.append("<sheet>"); sb.append(newLine); sb.append("\t\t"); sb.append("<name><![CDATA[" + sheet.getSheetName() + "]]></name>"); sb.append(newLine); int j = 0; for (Iterator<Row> iterator = sheet.rowIterator(); iterator.hasNext();) { Row row = (Row) iterator.next(); int k = 0; if (row.getCell(0) != null && row.getCell(0).getStringCellValue() != null && row.getCell(0).getStringCellValue().trim().length() > 0) { sb.append("\t\t"); sb.append("<row number=\"" + j + "\">"); sb.append(newLine); for (Cell cell : row) { sb.append("\t\t\t"); sb.append("<col number=\"" + k + "\">"); sb.append("<![CDATA[" + cellToString(cell) + "]]>"); sb.append("</col>"); sb.append(newLine); k++; } sb.append("\t\t"); sb.append("</row>"); sb.append(newLine); } j++; } sb.append("\t"); sb.append("</sheet>"); sb.append(newLine); } } sb.append("</workbook>"); sb.append(newLine); result = sb.toString(); } return result; }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException { XPath xPath = XPathFactory.newInstance().newXPath(); NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET); NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET); Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE); Sheet sheet = workbook.getSheetAt(0); for (int i = 0; i < cellValueList.getLength(); i++) { Node cellValue = cellValueList.item(i); String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent(); String type = cellValue.getAttributes().getNamedItem("type").getTextContent(); String value = cellValue.getTextContent(); CellReference cellRef = new CellReference(cellName); Row row = sheet.getRow(cellRef.getRow()); Cell cell = row.getCell(cellRef.getCol()); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellFormula(value);//from ww w. ja va2 s . c o m } else { cell.setCellValue(value); } } if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) { CellReference startCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("startRef").getTextContent()); CellReference endCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("endRef").getTextContent()); int startRowIndex = startCellRef.getRow(); int startColIndex = startCellRef.getCol(); int endColIndex = endCellRef.getCol(); CellStyle[] cellStyles = new CellStyle[endColIndex + 1]; Row firstRow = sheet.getRow(startRowIndex); for (int i = startColIndex; i <= endColIndex; i++) { cellStyles[i] = firstRow.getCell(i).getCellStyle(); } for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) { Row templeteRow = sheet.getRow(i); if (templeteRow != null) { sheet.removeRow(templeteRow); } } int rowNodeIndex = 0; for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) { Row row = sheet.createRow(i); int cellNodeIndex = 0; Node rowNode = rowNodeList.item(rowNodeIndex); NodeList rowValueNodeList = rowNode.getChildNodes(); ArrayList<Node> nodes = new ArrayList<Node>(); for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) { Node currentNode = rowValueNodeList.item(idx); if (currentNode.getNodeType() == Node.ELEMENT_NODE) { nodes.add(currentNode); } } for (int j = startColIndex; j <= endColIndex; j++) { Cell cell = row.createCell(j); Node cellNode = nodes.get(cellNodeIndex); String type = cellNode.getAttributes().getNamedItem("type").getTextContent(); String value = cellNode.getTextContent(); CellStyle cellStyle = cellStyles[j]; cell.setCellStyle(cellStyle); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula(value); } else if ("string".equals(type)) { if (value != null && value.length() > 0) { cell.setCellValue(value); } else { cell.setCellValue(""); } } else { cell.setCellValue(""); } cellNodeIndex++; } rowNodeIndex++; } } return workbook; }
From source file:br.com.gartech.nfse.integrador.view.ImportarView.java
public List<FaturamentoImport> readFaturamentoExcelFile(String excelFilePath) throws IOException { List<FaturamentoImport> listaFaturamento = new ArrayList<>(); FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next();/*www . j ava 2 s. c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); FaturamentoImport aFaturamentoImport = new FaturamentoImport(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: if (nextRow.getRowNum() == 0) { if (!getCellValue(nextCell).equals("CAMPUS")) { } } else { aFaturamentoImport.setCampus((String) getCellValue(nextCell)); } break; case 1: aFaturamentoImport.setCampus((String) getCellValue(nextCell)); break; case 2: aFaturamentoImport.setNumero((String) getCellValue(nextCell)); break; // case 3: // aFaturamentoImport.setTipo_fatura((String) getCellValue(nextCell)); // break; // case 4: // aFaturamentoImport.setAnocompetencia((Integer) getCellValue(nextCell)); // break; // case 5: // aFaturamentoImport.setMescompetencia((int) getCellValue(nextCell)); // break; // case 6: // aFaturamentoImport.setCodaluno((int) getCellValue(nextCell)); // break; // case 7: // aFaturamentoImport.setAlunome((String) getCellValue(nextCell)); // break; // case 8: // aFaturamentoImport.setCodcurso((int) getCellValue(nextCell)); // break; // case 9: // aFaturamentoImport.setCurso((String) getCellValue(nextCell)); // break; // case 10: // aFaturamentoImport.setTipocurso((String) getCellValue(nextCell)); // break; // case 11: // aFaturamentoImport.setAluendereco((String) getCellValue(nextCell)); // break; // case 12: // aFaturamentoImport.setAlubairro((String) getCellValue(nextCell)); // break; // case 13: // aFaturamentoImport.setAlucep((String) getCellValue(nextCell)); // break; // case 14: // aFaturamentoImport.setCiddesc((String) getCellValue(nextCell)); // break; // case 15: // aFaturamentoImport.setCiduf((String) getCellValue(nextCell)); // break; // case 16: // aFaturamentoImport.setAlutelefone((String) getCellValue(nextCell)); // break; // case 17: // aFaturamentoImport.setAlutelefone2((String) getCellValue(nextCell)); // break; // case 18: // aFaturamentoImport.setAluemail((String) getCellValue(nextCell)); // break; // case 19: // aFaturamentoImport.setAlucpf((String) getCellValue(nextCell)); // break; case 20: aFaturamentoImport.setValor((float) getCellValue(nextCell)); break; } } listaFaturamento.add(aFaturamentoImport); } //workbook.close; inputStream.close(); return listaFaturamento; }
From source file:br.com.tecsinapse.dataio.test.TableTest.java
License:LGPL
@Test public void testAutoSizeColumn_ComprovaErroMetodoAutoSizeColumn_de_Sheet_QuandoUltimasLinhasSaoVazias() { final int tamanhoIncorretoColuna = 236; final int tamanhoCorretoColuna = 2048; Table t = new Table(); //aps escrever certo nmero de linhas com o contedo vazio, //a coluna no ajustada para o tamanho equivalente a linha //com maior nmero de caracteres for (int i = 1; i < 130; i++) { t.addNewRow();//from www .j a va2 s. c o m if (i < 20) { t.add("Teste erro " + i); } else { t.add(" "); } } Workbook wb = t.toWorkBook(new SXSSFWorkbook()); Sheet sheet = wb.getSheetAt(0); // alterado o modo de validao devido a difernea de plataformas(Windows, Linux, Mac) esses valores podem mudar, porm devem respeitar o mnimo. Por isso usado assertTrue e no assertEquals Assert.assertTrue(sheet.getColumnWidth(0) >= tamanhoIncorretoColuna); Assert.assertTrue(sheet.getColumnWidth(1) >= tamanhoCorretoColuna); }
From source file:br.com.tecsinapse.dataio.test.TableTest.java
License:LGPL
@Test public void testAutoSizeColumn_GeraTamanhoConformeMaiorQuantidadeCaracteresColuna() { final int tamanhoUmCaracter = 256; final int tamanhoDefaultColuna = 2048; final int maiorNumeroCaracteresColuna = 13; Table t = new Table() { @Override/* w w w . ja v a 2 s .c om*/ public boolean isAutoSizeColumnSheet() { return false; } }; for (int i = 1; i < 130; i++) { t.addNewRow(); if (i < 20) { t.add("Teste erro " + i); } else { t.add(" "); } } Workbook wb = t.toWorkBook(new SXSSFWorkbook()); Sheet sheet = wb.getSheetAt(0); Assert.assertEquals(sheet.getColumnWidth(0), maiorNumeroCaracteresColuna * tamanhoUmCaracter); Assert.assertEquals(sheet.getColumnWidth(1), tamanhoDefaultColuna); }
From source file:br.sp.telesul.service.ExportServiceImpl.java
@Override public List<Funcionario> readExcelDocument() { try {/*from w w w . jav a 2 s .co m*/ List<Funcionario> funcionariosExcel = new ArrayList<>(); ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); // FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx")); Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx")); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int row = nextRow.getRowNum(); // System.out.println("Row start" + row); Iterator<Cell> cellIterator = nextRow.cellIterator(); Funcionario f = new Funcionario(); Formacao fm = new Formacao(); Idioma id = new Idioma(); int column = 0; while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); column = columnIndex; // System.out.println("Valor" + getCellValue(nextCell)); // System.out.println("Index: " + columnIndex); if (row > 0) { switch (columnIndex) { case 1: f.setArea((String) getCellValue(nextCell)); break; case 2: Date dt = new Date(); if (!getCellValue(nextCell).toString().isEmpty()) { try { dt = DateUtil.getJavaDate((Double) getCellValue(nextCell)); } catch (ClassCastException cce) { SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy"); dt = formatter.parse((String) getCellValue((nextCell))); } ; } f.setDtAdmissao(dt); break; case 3: f.setCargo((String) getCellValue(nextCell)); break; case 4: f.setNome((String) getCellValue(nextCell)); break; case 5: f.setGestor((String) getCellValue(nextCell)); break; case 9: fm.setNivel((String) getCellValue(nextCell)); break; case 10: fm.setCurso((String) getCellValue(nextCell)); break; case 11: fm.setInstituicaoo((String) getCellValue(nextCell)); break; case 12: String typeEnum = (String) getCellValue(nextCell); if (!typeEnum.isEmpty()) { id.setNome(Language.valueOf(typeEnum.trim())); } break; case 13: String typeEnumNivel = (String) getCellValue(nextCell); if (!typeEnumNivel.isEmpty()) { id.setNivel(Nivel.valueOf(typeEnumNivel.trim())); } break; } } } List<Formacao> listFm = new ArrayList<>(); listFm.add(fm); f.setFormacoes(listFm); List<Idioma> listId = new ArrayList<>(); listId.add(id); f.setIdiomas(listId); if (row > 0) { funcionariosExcel.add(f); } } wb.close(); // fl.close(); // for (Funcionario fc : funcionariosExcel) { // System.out.println(fc.getNome()); // } return funcionariosExcel; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook;/*from www . j a va 2 s.c om*/ if (!outputFile.exists()) { outputFile.createNewFile(); outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
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 .com 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; }