List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:edms.core.Config.java
License:Open Source License
public static void convertToXlsx(InputStream inStream, java.io.File outputFile) { // For storing data into CSV files StringBuffer cellValue = new StringBuffer(); try {/*w w w . j av a2s .co m*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(inStream); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellValue.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellValue.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellValue.append("" + ","); break; default: cellValue.append(cell + ","); } } } fos.write(cellValue.toString().getBytes()); fos.close(); } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } }
From source file:edu.jhu.cvrg.timeseriesstore.opentsdb.store.ExcelStorer.java
License:Apache License
@Override public ArrayList<IncomingDataPoint> extractTimePoints(InputStream inputStream, String[] channels, int samples, long epochTime) { ArrayList<IncomingDataPoint> dataPoints = new ArrayList<IncomingDataPoint>(); XSSFWorkbook subjectWorkbook = getWorkbook(inputStream); HashMap<String, String> tags = new HashMap<String, String>(); for (int i = 0; i < subjectWorkbook.getNumberOfSheets(); i++) { XSSFSheet sheetIn = subjectWorkbook.getSheetAt(i); for (int r = 1; r <= sheetIn.getLastRowNum(); r++) { long currentTime = epochTime; XSSFRow row = sheetIn.getRow(r); String channel = getChannelName(i, channels); dataPoints.add(new IncomingDataPoint("ecg.uv." + channel, currentTime, String.valueOf(row.getCell(1).getNumericCellValue()), tags)); tags.put("format", "excel"); currentTime++;//from ww w.j a v a 2 s . c om } } return dataPoints; }
From source file:edu.vt.cs.irwin.etdscraper.retriever.excel.ExcelEtdSource.java
License:Apache License
/** * {@inheritDoc}//from ww w . j av a 2s. c o m */ @Override public List<Etd> retrieveEtds() { List<Etd> etds = new ArrayList<>(); try { logger.info("Starting to load ETDs from Excel document"); File file = new File(configuration.getExcelFile()); InputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); Sheet sheet = wb.getSheetAt(0); int rowNumber = 0; for (Row row : sheet) { if (rowNumber++ == 0) continue; String name = getCellValue(row.getCell(0)); if (name.isEmpty()) { logger.info("Skipping row " + rowNumber + " because there is no name"); continue; } if (name.equals("count")) { logger.info("Skipping row " + rowNumber + " as it is a count row"); continue; } EtdEntity etd = new EtdEntity(); etd.addContributor(personFactory.createPerson(etd, name, Role.STUDENT)); etd.setDate(sdf.parse(getCellValue(row.getCell(1)))); String advisor = getCellValue(row.getCell(2)); if (!advisor.isEmpty()) { etd.addContributor(personFactory.createPerson(etd, advisor, Role.CHAIR)); } etd.setTitle(getCellValue(row.getCell(3))); etds.add(etd); } logger.info("Extracted " + etds.size() + " ETDs"); return etds; } catch (IOException | ParseException e) { throw new RuntimeException(e); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-2] ? : ? ? ? ? ?//from w w w .j a v a2 s . c o m */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new XSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cellx = row.createCell(cellnum); cellx.setCellType(XSSFCell.CELL_TYPE_STRING); cellx.setCellValue(new XSSFRichTextString(content)); cellx.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream outx = new FileOutputStream(sb.toString()); wb.write(outx); outx.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-7] : ? ?? /* w w w . j av a 2 s. co m*/ */ @Test public void testUseTemplate1() throws Exception { StringBuffer sb = new StringBuffer(); StringBuffer sbResult = new StringBuffer(); sb.append(fileLocation).append("/template/").append("template.xlsx"); sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx"); Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, }; try { XSSFWorkbook wb = null; wb = excelService.loadExcelTemplate(sb.toString(), wb); Sheet sheet = wb.getSheetAt(0); // set data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; Cell cell = row.getCell(j); if (sample_data[i][j] instanceof String) { cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j])); } else { cell.setCellValue((Double) sample_data[i][j]); } } } // ? sheet.setForceFormulaRecalculation(true); excelService.createWorkbook(wb, sbResult.toString()); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheetAt(0); for (int i = 0; i < sample_data.length; i++) { Row row = sheetT.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { Cell cell = row.getCell(j); LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]); if (sample_data[i][j] == null) { assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue())); } else { assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString()); } } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testUseTemplate end...."); } }
From source file:egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl.java
License:Apache License
/** * xlsx ?? ? DB upload ./*w w w.j a v a 2 s . co m*/ * * @param queryId * @param fileIn * @param start * @param commitCnt * @param wb * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, int start, long commitCnt, XSSFWorkbook wb) throws BaseException, Exception { wb = loadWorkbook(fileIn, wb); Sheet sheet = wb.getSheetAt(0); return uploadExcel(queryId, sheet, start, commitCnt); }
From source file:egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl.java
License:Apache License
/** * xlsx ?? DB? ?.<br/>/*w ww. j a v a 2s .c om*/ * ? . * * @param queryId * @param fileIn * @param sheetIndex * @param start (default : 0) * @param commitCnt (default : 0) * @param wb * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, short sheetIndex, int start, long commitCnt, XSSFWorkbook wb) throws BaseException, Exception { wb = loadWorkbook(fileIn, wb); Sheet sheet = wb.getSheetAt(sheetIndex); return uploadExcel(queryId, sheet, start, commitCnt); }
From source file:Ekon.PanelVypisFirem.java
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed JFileChooser fch = new JFileChooser(); FileNameExtensionFilter filter = new FileNameExtensionFilter("objects xls xlsx", "xls", "xlsx"); fch.setFileFilter(filter);//from w ww. j a va2 s . c o m int returnVal = fch.showOpenDialog(this); try { File nazevSouboru = fch.getSelectedFile(); ArrayList<Firma> list = new ArrayList<>(); FileInputStream file = new FileInputStream(nazevSouboru.getAbsoluteFile()); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbookV = new XSSFWorkbook(nazevSouboru.getAbsolutePath()); //Get first/desired sheet from the workbook XSSFSheet sheetV = workbookV.getSheetAt(0); Iterator<Row> rowIT = sheetV.iterator(); while (rowIT.hasNext()) { Row row = rowIT.next(); Iterator<Cell> cellIt = row.cellIterator(); ArrayList<String> listO = new ArrayList<>(); while (cellIt.hasNext()) { Cell cell = cellIt.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + " "); listO.add(String.valueOf((int) cell.getNumericCellValue())); break; case Cell.CELL_TYPE_STRING: listO.add(cell.getStringCellValue()); //System.out.print(cell.getStringCellValue() + " "); break; } } vytvorFirmy.pridejFirmu(new Firma(String.valueOf(listO.get(0)), String.valueOf(listO.get(1)), String.valueOf(listO.get(2)), String.valueOf(listO.get(3)), String.valueOf(listO.get(4)), Integer.valueOf(listO.get(5)), Integer.valueOf(listO.get(6)), String.valueOf(listO.get(7)), Integer.valueOf(listO.get(8)))); } file.close(); DefaultTableModel model = (DefaultTableModel) tableFirmy.getModel(); while (model.getRowCount() > 0) { model.removeRow(0); } Iterator it = vytvorFirmy.dejIterator(); Iterator itN = poleNazvu.iterator(); Firma f; try { for (Iterator it1 = it; it1.hasNext();) { f = (Firma) it1.next(); model.addRow(new Object[] { f.getNazevFirmy(), f.getMesto(), f.getUlice(), f.getKraj(), f.getPsc(), f.getKontakt(), f.getEmail(), f.getIco(), f.getDico() }); } } catch (NullPointerException e) { JOptionPane.showMessageDialog(this, "seznam firem je prazdny", "Chyba", 1); } } catch (Exception e) { e.printStackTrace(); } }
From source file:Ekon.PanelVypisZamestnancu.java
private void btnNactiXLSActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnNactiXLSActionPerformed JFileChooser fch = new JFileChooser(); FileNameExtensionFilter filter = new FileNameExtensionFilter("objects xls xlsx", "xls", "xlsx"); fch.setFileFilter(filter);/* w w w. j a va 2 s. co m*/ int returnVal = fch.showOpenDialog(this); try { File nazevSouboru = fch.getSelectedFile(); ArrayList<Zamestnanec> list = new ArrayList<>(); FileInputStream file = new FileInputStream(nazevSouboru.getAbsoluteFile()); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbookV = new XSSFWorkbook(nazevSouboru.getAbsolutePath()); //Get first/desired sheet from the workbook XSSFSheet sheetV = workbookV.getSheetAt(0); Iterator<Row> rowIT = sheetV.iterator(); rowIT.next(); while (rowIT.hasNext()) { Row row = rowIT.next(); Iterator<Cell> cellIt = row.cellIterator(); ArrayList<String> listO = new ArrayList<>(); while (cellIt.hasNext()) { Cell cell = cellIt.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + " "); listO.add(String.valueOf((int) cell.getNumericCellValue())); break; case Cell.CELL_TYPE_STRING: listO.add(cell.getStringCellValue()); // System.out.print(cell.getStringCellValue() + " "); break; } } aktualniFirma.pridejZamestnance(new Zamestnanec(listO.get(0), listO.get(1), listO.get(2), listO.get(3), Integer.valueOf(listO.get(4)), listO.get(5), listO.get(6), listO.get(7), listO.get(8), Integer.valueOf(listO.get(9)), Integer.valueOf(listO.get(10)), listO.get(11), listO.get(12))); } file.close(); DefaultTableModel model = (DefaultTableModel) tableZamestnanci.getModel(); while (model.getRowCount() > 0) { model.removeRow(0); } Iterator it = aktualniFirma.dejIteratorZamestnancu(); try { for (Iterator it1 = it; it1.hasNext();) { Zamestnanec z = (Zamestnanec) it1.next(); model.addRow(new Object[] { z.getJmeno(), z.getPrijmeni(), z.getTitul(), z.getMesto(), z.getUlice(), z.getPsc(), z.getKraj(), z.getDatumNarozeni(), z.getRodneCislo(), z.getEmail(), z.getTelefoniCislo(), z.getNarodnost(), z.getPozice() }); } } catch (Exception e) { JOptionPane.showMessageDialog(this, "seznam zamestnancu je prazdny", "Chyba", 1); } } catch (Exception e) { e.printStackTrace(); } Iterator itF = vytvorFirmy.dejIterator(); for (Iterator it1 = itF; it1.hasNext();) { Firma f = (Firma) it1.next(); if (aktualniFirma.getNazevFirmy().equals(f.getNazevFirmy())) { f.setListZamestanancu(aktualniFirma.getListZamestanancu()); } } }
From source file:es.SSII2.manager.ExcelManagerAccount.java
public void actualizarCuentas(ArrayList<String> cuentas, ArrayList<String> cuentasCorrectas, ArrayList<String> arrayIban, ArrayList<String> pos) throws IOException, ParseException { int row, col; String originalCuenta, nuevaCuenta, iban, posicion, entidad, oficina, dc, numCuenta; String[] a;/*from w w w . j a v a2 s . c o m*/ FileInputStream file; file = new FileInputStream(new File(excel)); FileOutputStream outFile; XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow rowIban; XSSFCell cellIban; Cell cellCuenta; //sacar las cuentas for (int i = 0; i < cuentas.size(); i++) { originalCuenta = cuentas.get(i);//se coje la cuenta original nuevaCuenta = cuentasCorrectas.get(i);//se coje la cuenta actualizada iban = arrayIban.get(i);// cojer el iban posicion = pos.get(i);//se coje la posicion a = posicion.split("-");//split del string "1-3" de las posiciones que estan el el arraylist row = Integer.parseInt(a[0]);//fila "1" col = Integer.parseInt(a[1]);//columna "3" //insertar el iban rowIban = sheet.getRow(row);//coje la fila cellIban = rowIban.createCell(1 + col);//crea la celda cellIban.setCellValue(iban); //2096 0056 16 3231500000 entidad = nuevaCuenta.substring(0, 4); oficina = nuevaCuenta.substring(4, 8); dc = nuevaCuenta.substring(8, 10); numCuenta = nuevaCuenta.substring(10); //actualizar la cuenta si esta mal el cc if (!originalCuenta.equals(nuevaCuenta)) { cellCuenta = sheet.getRow(row).getCell(col); //obtiene la fila y columna DecimalFormat df = new DecimalFormat("#"); Number cuenta = df.parse(nuevaCuenta); cellCuenta.setCellValue(cuenta.doubleValue()); System.out.println( "Cuenta actualizada: " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta); } else { System.out.println( "Cuenta correcta: " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta); } } //for outFile = new FileOutputStream(new File(excel)); //escribe en el excel workbook.write(outFile); outFile.close(); file.close();//cierra el archivo }