List of usage examples for org.apache.poi.ss.usermodel Sheet getFirstRowNum
int getFirstRowNum();
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/*** * Get contents of a sheet into text rows and columns * //from w ww .jav a 2 s .c o m * @param sheet * @return */ private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) { // let us get a normalized rows/columns out of this sheet. int firstRowIdx = sheet.getFirstRowNum(); Row firstRow = sheet.getRow(firstRowIdx); int firstCellIdx = firstRow.getFirstCellNum(); int lastCellAt = firstRow.getLastCellNum(); int nbrCells = lastCellAt - firstCellIdx; int lastRow = sheet.getLastRowNum(); List<String[]> rawData = new ArrayList<String[]>(); for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) { Row row = sheet.getRow(rowNbr); if (row == null || row.getPhysicalNumberOfCells() == 0) { Spit.out( "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this."); continue; } String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells); if (rowData == null) { continue; } if (expectValueInFirstColumn) { String firstData = rowData[0]; if (firstData == null || firstData.length() == 0) { Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored"); continue; } } rawData.add(rowData); } if (rawData.size() > 0) { return rawData.toArray(new String[0][0]); } return null; }
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/*** * get data types of column based on actual values in the sheet * //from w ww. j a v a 2s . c o m * @param sheet * @param nbrCells * @param rowStart * @param rowEnd * @return */ private DataValueType[] getExilityTypes(Sheet sheet, int nbrCells) { DataValueType[] types = new DataValueType[nbrCells]; // though NULL is default (as of now that is the first one in ENUM) let // us explicitly populate it for (int i = 0; i < nbrCells; i++) { types[i] = DataValueType.NULL; } int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); int nbrFound = 0; // which cell to start? We will go by the first cell of the first // physucal row Row firstRow = sheet.getRow(sheet.getFirstRowNum()); int startingCellIdx = firstRow.getFirstCellNum(); int endCellIdx = startingCellIdx + nbrCells; for (int i = rowStart; i <= rowEnd; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } for (int j = startingCellIdx; j < endCellIdx; j++) { // do we already know this type? if (types[j] != DataValueType.NULL) { continue; } Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL); if (cell == null) { continue; } types[j] = this.getExilityType(cell); nbrFound++; if (nbrFound == nbrCells) { return types; } } } // we will treat unknown ones as text for (int i = 0; i < nbrCells; i++) { if (types[i] == DataValueType.NULL) { types[i] = DataValueType.TEXT; } } return types; }
From source file:com.google.gdt.handler.impl.ExcelHandler.java
License:Open Source License
/** * /*from www. j ava 2 s.c o m*/ * @param inputFile * @throws IOException * @throws InvalidFormatException */ @Override public void handle(String inputFile, ProgressLevel pLevel) throws IOException, InvalidFormatException { String outPutFile = getOuputFileName(inputFile); OutputStream outputStream = new FileOutputStream(outPutFile); InputStream is = new FileInputStream(inputFile); Workbook wb = WorkbookFactory.create(is); List<Sheet> sheets = getSheets(wb); pLevel.setTrFileName(outPutFile); //iterate over sheet for (int index = 0; index < sheets.size(); index++) { Sheet sheet = sheets.get(index); if (sheets.size() > 1) { pLevel.setString("Translating sheet " + (index + 1) + "/" + sheets.size()); } int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); int rowCount = lastRowNum - firstRowNum; // check for empty sheet, don't perform any operation if (rowCount == 0) { continue; } pLevel.setValue(0); pLevel.setMaxValue(rowCount); pLevel.setStringPainted(true); int pBarUpdate = 0; //iterate over row for (Row row : sheet) { //iterate over cells for (Cell cell : row) { if (isInterrupted) { outputStream.close(); new File(outPutFile).delete(); pLevel.setString("cancelled"); return; } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String inputText = cell.getStringCellValue(); String translatedTxt = inputText; try { translatedTxt = translator.translate(inputText); cell.setCellValue(translatedTxt); } catch (Exception e) { logger.log(Level.SEVERE, "Input File : " + inputFile + " cannot translate the text : " + inputText, e); continue; } } } //cell iteration ends pBarUpdate++; pLevel.setValue(pBarUpdate); } //row iteration ends pLevel.setValue(rowCount); } pLevel.setString("done"); wb.write(outputStream); outputStream.close(); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void clearSheet(int sheetIndex) { Sheet sheet = getSheet(sheetIndex); int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = lastRow; i >= firstRow; i--) { Row r = sheet.getRow(i);//from w ww .j a va 2s.c o m if (r != null) sheet.removeRow(r); } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public int[] getBoundingBox(int sheetIndex, int startRow, int startCol, int endRow, int endCol, boolean autofitRow, boolean autofitCol) { Sheet sheet = workbook.getSheetAt(sheetIndex); final int mark = Integer.MAX_VALUE - 1; if (startRow < 0) { startRow = sheet.getFirstRowNum(); if (sheet.getRow(startRow) == null) { // There is no row in this sheet startRow = -1;/*from w ww .j a v a 2s .c o m*/ } } if (endRow < 0) { endRow = sheet.getLastRowNum(); if (sheet.getRow(endRow) == null) { // There is no row in this sheet endRow = -1; } } int minRow = startRow; int maxRow = endRow; int minCol = startCol; int maxCol = endCol < 0 ? mark : endCol; startCol = startCol < 0 ? mark : startCol; endCol = endCol < 0 ? -1 : endCol; Cell topLeft = null, bottomRight = null; boolean anyCell = false; for (int i = minRow; i > -1 && i <= maxRow; i++) { Row r = sheet.getRow(i); if (r != null) { // Determine column boundaries int start = Math.max(minCol, r.getFirstCellNum()); int end = Math.min(maxCol + 1, r.getLastCellNum()); // NOTE: getLastCellNum is 1-based! boolean anyNonBlank = false; for (int j = start; j > -1 && j < end; j++) { Cell c = r.getCell(j); if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { anyCell = true; anyNonBlank = true; if ((autofitCol || minCol < 0) && (topLeft == null || j < startCol)) { startCol = j; topLeft = c; } if ((autofitCol || maxCol == mark) && (bottomRight == null || j > endCol)) { endCol = j; bottomRight = c; } } } if (autofitRow && anyNonBlank) { endRow = i; if (sheet.getRow(startRow) == null) { startRow = i; } } } } if ((autofitRow || startRow < 0) && !anyCell) { startRow = endRow = -1; } if ((autofitCol || startCol == mark) && !anyCell) { startCol = endCol = -1; } return new int[] { startRow, startCol, endRow, endCol }; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>/*w w w.j av a 2s . c om*/ * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ) * </pre> * * @method Name : readExcel * @param strFullFilePath, serviceType * @return List<Map<String, Object>> * @throws Exception * */ @SuppressWarnings("deprecation") public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */ /* Sheet ? , ? ?? */ /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */ for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1 ? */ Sheet sheet = wb.getSheetAt(sheetIdx); /* ?? ? ?, */ /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new HashMap<String, Object>(); if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; // WBS ? ? ?? if (serviceType.equals("order")) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); value = formatter.format(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: ; } } else { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } } map.put("colName" + cellIdx, value); } else { map.put("colName" + cellIdx, ""); } } list.add(map); } } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>/*w w w .j av a 2 s. c o m*/ * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ), sheet ? * </pre> * * @method Name : readExcelMulti * @param strFullFilePath, serviceType, sheetNo * @return * @throws * */ @SuppressWarnings("deprecation") public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType, int sheetNo) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>(); LinkedHashMap<String, Object> map = null; try { if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= file path : {} =", strFullFilePath); log.debug("= tmp file : {} =", tmpFile); log.debug( "=========================================================================================="); } file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ Sheet sheet = wb.getSheetAt(sheetNo); /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new LinkedHashMap<String, Object>(); if (rowIdx == 0) { if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= sheet no : {} =", sheetNo); log.debug("= row count : {} =", sheet.getLastRowNum()); log.debug("= col count : {} =", cellCount); log.debug( "=================================================================================="); } } if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } map.put("item" + String.valueOf(cellIdx), value); } else { map.put("item" + String.valueOf(cellIdx), ""); } } if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= map : {} =", map); log.debug( "=================================================================================="); } list.add(map); } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.ocs.dynamo.importer.impl.BaseXlsImporter.java
License:Apache License
@Override public int countRows(byte[] bytes, int row, int column) { Workbook wb = createWorkbook(bytes); if (wb.getNumberOfSheets() == 0) { return 0; } else {//from w w w .ja va 2 s . c o m Sheet sheet = wb.getSheetAt(0); return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1; } }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
/** * Schema table can contain multiple tables... * @param sheet/*www . j ava 2 s . co m*/ */ private static SchemaSheetInformation importSchemaTables(Sheet sheet, ExecutionReport report) { List<ODLTableReadOnly> tables = new ArrayList<>(); // schema tables are separated by empty rows int lastRow = sheet.getLastRowNum(); int firstRow = sheet.getFirstRowNum(); int firstNonEmptyRow = -1; int nbCols = 0; for (int x = firstRow; x <= lastRow; x++) { // check for completely empty row Row row = sheet.getRow(x); boolean isEmptyRow = true; for (int y = 0; row != null && y <= row.getLastCellNum(); y++) { if (isEmptyCell(row, y) == false) { isEmptyRow = false; } } if (isEmptyRow || x == lastRow) { // dump table if row was empty or on last row, but we previously had a non empty row if (firstNonEmptyRow != -1) { ODLDatastoreAlterable<ODLTableAlterable> tmpDs = ODLDatastoreImpl.alterableFactory.create(); ODLTableAlterable table = tmpDs.createTable(sheet.getSheetName(), -1); importSheetSubset(table, sheet, null, true, firstNonEmptyRow, isEmptyRow ? x - 1 : x, nbCols); tables.add(table); } firstNonEmptyRow = -1; } else if (firstNonEmptyRow == -1) { // initialise table if we've just found the first non empty row firstNonEmptyRow = x; nbCols = 0; for (int y = 0; y <= row.getLastCellNum(); y++) { if (isEmptyCell(row, y)) { break; } else { nbCols = y + 1; } } } } return readSchemaFromODLTables(tables, report); }
From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java
License:Open Source License
/** * Load S&P GICS code mappings from an Apace POI HSSFWorkbook * @param workbook HSSFWorkbook to parse S&P GCIS Excel * @param gicsMap Map to add mappings to *//*ww w. ja v a 2 s .c o m*/ static void processGICSExcelWorkbook(Workbook workbook, Map<String, String> gicsMap) { //Assume 1 sheet Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { return; } for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum); if (row == null) { continue; } for (int cellNum = 0; cellNum < row.getPhysicalNumberOfCells(); cellNum++) { Cell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK); if (isNumeric(cell)) { //worst case if the Excel file is in an incorrect (or updated) format // is that number -> random or empty string mappings will be created gicsMap.put(getGICSCellValue(cell), getGICSCellValue(row, cellNum + 1)); } } } }