List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
From source file:edu.gatech.pmase.capstone.awesome.impl.database.AbstractDatabaseDriver.java
License:Open Source License
/** * Reads the options from the workbook.//w w w . j av a 2s . co m * * @param workbook the workbook to read from * * @return the List of options in the workbook */ private List<T> readOptionsFromWorkbook(final Workbook workbook) { final List<T> options = new ArrayList<>(); // get first sheet final Sheet sheet = workbook.getSheetAt(0); // max rows int maxRows = sheet.getPhysicalNumberOfRows(); if (maxRows > 1) { for (int rowIter = 1; rowIter < maxRows; rowIter++) { final Row row = sheet.getRow(rowIter); if (null != row) { final T opt = this.getOptionFromRow(row); if (null != opt) { options.add(opt); } else { LOGGER.trace("Could not make option from row " + rowIter); } } else { LOGGER.debug("Loaded Invalid Row: " + rowIter); } } } else { LOGGER.error("Database does not have the expected number of rows. Must have more than one row."); } return options; }
From source file:edu.isi.karma.imp.excel.ToCSV.java
License:Apache License
/** * Called to convert the contents of the currently opened workbook into a * CSV file./*from www. j av a2s . c o m*/ * * @param destination * @param excelFileName * @throws IOException * @throws FileNotFoundException */ private void convertToCSV(File destination, String excelFileName) throws FileNotFoundException, IOException { Sheet sheet; Row row; int lastRowNum; this.csvData = new ArrayList<>(); logger.info("Converting files contents to CSV format."); // Discover how many sheets there are in the workbook.... int numSheets = this.workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < numSheets; i++) { // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } // Save to CSV String sheetName = sheet.getSheetName(); String csvFileName = excelFileName.substring(0, excelFileName.lastIndexOf(".")) + "_" + sheetName; File csvFile = new File(destination, csvFileName); this.saveCSVFile(csvFile); csvFiles.add(csvFile); csvData.clear(); } } }
From source file:edu.isi.karma.imp.excel.ToCSV.java
License:Apache License
private void convertWorksheetToCSV(int wsIdx, Writer writer) throws IOException { Sheet sheet = this.workbook.getSheetAt(wsIdx); Row row;//from www . ja va 2s . co m int lastRowNum; this.csvData = new ArrayList<>(); this.maxRowWidth = -1; if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } } StringBuffer buffer; ArrayList<String> line; String csvLineElement; BufferedWriter bw = new BufferedWriter(writer); for (int i = 0; i < this.csvData.size(); i++) { buffer = new StringBuffer(); line = this.csvData.get(i); for (int j = 0; j < this.maxRowWidth; j++) { if (line.size() > j) { csvLineElement = line.get(j); if (csvLineElement != null) { buffer.append(this.escapeEmbeddedCharacters(csvLineElement)); } } if (j < (this.maxRowWidth - 1)) { buffer.append(this.separator); } } // Once the line is built, write it away to the CSV file. bw.write(buffer.toString().trim()); // Condition the inclusion of new line characters so as to // avoid an additional, superfluous, new line at the end of // the file. if (i < (this.csvData.size() - 1)) { bw.newLine(); } } bw.flush(); }
From source file:edu.si.services.beans.excel.ExcelToCSV.java
License:Apache License
/** * Called to convert the contents of the currently opened workbook into * a CSV file.//from www . j a v a2 s . c om */ private void convertToCSV() { Sheet sheet = null; Row row = null; int lastRowNum = 0; this.csvData = new ArrayList<ArrayList<String>>(); logger.debug("Converting stream content to CSV format."); // Discover how many sheets there are in the workbook.... int numSheets = this.workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < numSheets; i++) { // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } } } }
From source file:egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl.java
License:Apache License
/** * ?? DB? ?.<br/>/* w w w .ja v a2 s . c o m*/ * ? .<br/> * commit .<br/> * sqlSessionTemplate(mybatis), sqlMapClient(ibatis) . * * @param queryId * @param sheet * @param start (default : 0) * @param commitCnt (default :0) * @return * @throws Exception */ public Integer uploadExcel(String queryId, Sheet sheet, int start, long commitCnt) throws BaseException, Exception { LOGGER.debug("sheet.getPhysicalNumberOfRows() : {}", sheet.getPhysicalNumberOfRows()); Integer rowsAffected = 0; try { long rowCnt = sheet.getPhysicalNumberOfRows(); long cnt = (commitCnt == 0) ? rowCnt : commitCnt; LOGGER.debug("Runtime.getRuntime().totalMemory() : {}", Runtime.getRuntime().totalMemory()); LOGGER.debug("Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); long startTime = System.currentTimeMillis(); for (int idx = start, i = start; idx < rowCnt; idx = i) { List<Object> list = new ArrayList<Object>(); LOGGER.debug("before Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); EgovExcelMapping mapping = null; if (mapBeanName != null) { mapping = (EgovExcelMapping) applicationContext.getBean(mapBeanName); } else if (mapClass != null) { mapping = (EgovExcelMapping) EgovObjectUtil.instantiate(mapClass); } else { throw new RuntimeException( getMessageSource().getMessage("error.excel.property.error", null, Locale.getDefault())); } for (i = idx; i < rowCnt && i < (cnt + idx); i++) { Row row = sheet.getRow(i); list.add(mapping.mappingColumn(row)); } // insert // spring ibatis? batch ? ? exception ?? rollback ? ?. // ibatis ? batch sqlMapClient.startTransaction() ?? ? ? ! if (sqlSessionTemplate != null) { rowsAffected += excelBatchMapper.batchInsert(queryId, list); } else if (sqlMapClient != null) { rowsAffected += dao.batchInsert(queryId, list); } else { throw new RuntimeException(getMessageSource().getMessage("error.excel.persistence.error", null, Locale.getDefault())); } LOGGER.debug("after Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); LOGGER.debug("rowsAffected : {}", rowsAffected); } LOGGER.debug("batchInsert time is {}", (System.currentTimeMillis() - startTime)); } catch (Exception e) { throw new Exception(e); } LOGGER.debug("uploadExcel result count is {}", rowsAffected); return rowsAffected; }
From source file:es.us.isa.jdataset.loader.ExcelToCSV.java
License:Apache License
/** * Called to convert the contents of the currently opened workbook into * a CSV file.//from ww w. j a v a 2 s . c om */ private void convertToCSV() { Sheet sheet = null; Row row = null; int lastRowNum = 0; this.csvData = new ArrayList<ArrayList>(); System.out.println("Converting files contents to CSV format."); // Discover how many sheets there are in the workbook.... int numSheets = this.getWorkbook().getNumberOfSheets(); // and then iterate through them. int i = 0; // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.getWorkbook().getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } } }
From source file:eu.esdihumboldt.hale.io.xls.reader.DefaultXLSLookupTableReader.java
License:Open Source License
/** * Reads a xls/xlsx lookup table workbook (from apache POI). The selected * columns specified by parameters keyColumn and valueColumn are mapped * together./*from ww w.j a va 2 s .c o m*/ * * @param workbook the workbook to read * @param skipFirst true, if first row should be skipped * @param keyColumn source column of the lookup table * @param valueColumn target column of the lookup table * @return the lookup table as map */ public Map<Value, Value> read(Workbook workbook, boolean skipFirst, int keyColumn, int valueColumn) { Map<Value, Value> map = new LinkedHashMap<Value, Value>(); Sheet sheet = workbook.getSheetAt(0); int row = 0; if (skipFirst) row++; for (; row < sheet.getPhysicalNumberOfRows(); row++) { Row currentRow = sheet.getRow(row); map.put(Value.of(currentRow.getCell(keyColumn).getStringCellValue()), Value.of(currentRow.getCell(valueColumn).getStringCellValue())); } return map; }
From source file:eu.esdihumboldt.hale.io.xls.writer.XLSInstanceWriter.java
License:Open Source License
private void setCellStyle(Sheet sheet, int rowSize) { int rowNum = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rowNum; i++) { Row row = sheet.getRow(i);// www.j a v a2s. c om for (int k = 0; k < rowSize; k++) { Cell cell = row.getCell(k); if (cell != null) cell.setCellStyle(cellStyle); else row.createCell(k).setCellStyle(cellStyle); } } }
From source file:ExcelFx.ParseAndWrite.Parser.java
/** * ? ? ? ?? ??/*w w w.ja v a2 s . co m*/ * * @param patchName xls/xlsx * @param pageNumber ? ? ? * @return ? Excel * @throws NullPointerException * @throws FileNotFoundException * @throws IOException */ public RowList parseInitalData(String patchName, Integer pageNumber) throws NullPointerException, IOException { System.out.println("parse inital data in " + patchName + " at page " + pageNumber); File file = new File(patchName); if (!file.exists()) { System.out.println("Error file on patch not exists"); } FileInputStream in = new FileInputStream(file); switch (patchName.substring(patchName.lastIndexOf("."), patchName.length())) { case ".xls": { this.wb = new HSSFWorkbook(in); break; } case ".xlsx": { this.wb = new XSSFWorkbook(in); break; } default: { System.out.println("error wrong file format"); } } Sheet sheet = this.wb.getSheetAt(pageNumber); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { int cellType; this.list.add(new ExcelRow()); Row row = sheet.getRow(i); int jMax = sheet.getRow(0).getPhysicalNumberOfCells(); try { for (int j = 0; j < jMax; j++) { Cell cell = row.getCell(j); try { cellType = cell.getCellType(); } catch (NullPointerException e) { this.list.get(i).add("null"); continue; } switch (cellType) { case Cell.CELL_TYPE_STRING: this.list.get(i).add(cell.getStringCellValue().trim().toLowerCase()); //System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: this.list.get(i).add(String.valueOf(cell.getNumericCellValue()).trim().toLowerCase()); //System.out.println(cell.getNumericCellValue()); break; default: this.list.get(i).add("null"); //System.out.println("null"); break; } } } catch (NullPointerException e) { System.err.println(e + " in " + i + " row"); } } System.out.println("parse complited just read " + this.list.size() + " row "); System.out.println("-----------------------------------------------------------"); return this.list; }
From source file:ExcelFx.ParseAndWrite.Parser.java
/** * ? //from www. j a v a2 s. co m * * @param patchName xls/xlsx * @param sheetNumber ? ? ? * @return ?? ? (?) * ? * @throws FileNotFoundException * @throws IOException */ public HashMap<String, String> parseBigGroupe(String patchName, Integer sheetNumber) throws FileNotFoundException, IOException { HashMap<String, String> codeListData = new HashMap<>(); String nameBigGr = null; InputStream in; in = new FileInputStream(patchName); if (".xls".equals(patchName.substring(patchName.lastIndexOf("."), patchName.length()))) { wb = new HSSFWorkbook(in); } else { wb = new XSSFWorkbook(in); } Sheet sheet = wb.getSheetAt(sheetNumber); int jMax = sheet.getRow(0).getPhysicalNumberOfCells(); for (int i = 3; i < sheet.getPhysicalNumberOfRows(); i++) { int cellBlankVal = 0; int cellType; HashMap<String, String> hm = new HashMap<>(); Row row = sheet.getRow(i); Cell cell; for (int j = 0; j < jMax; j++) { cell = row.getCell(j); cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { cellBlankVal++; } } switch (cellBlankVal) { case 0: { cell = row.getCell(0); cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_STRING: codeListData.put(cell.getStringCellValue(), nameBigGr); break; case Cell.CELL_TYPE_NUMERIC: codeListData.put(cell.getStringCellValue(), nameBigGr); } } break; case 2: { for (int j = 0; j < jMax; j++) { cell = row.getCell(j); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { nameBigGr = cell.getStringCellValue(); } } } break; } } return codeListData; }