@Test public void shouldBeAbleToCreateMetadataForExcel() throws Exception { InputStream inputStream = getClass().getClassLoader().getResourceAsStream("excel.xls"); ExcelMetadata excelMetadata = ExcelMetadataReader.instance(inputStream); ExcelSheetMetadata sheet2 = excelMetadata.getSheet("MySheet2"); assertThat(sheet2, is(notNullValue())); assertThat(excelMetadata.getSheet("Sheet1").getText().startsWith("This is a text"), is(true)); }
public static ExcelMetadata instance( InputStream stream ) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta); HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }
ExcelMetadataReader { public static ExcelMetadata instance( InputStream stream ) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta); HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; } }
ExcelMetadataReader { public static ExcelMetadata instance( InputStream stream ) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta); HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }  }
ExcelMetadataReader { public static ExcelMetadata instance( InputStream stream ) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta); HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }  static ExcelMetadata instance( InputStream stream ); }
ExcelMetadataReader { public static ExcelMetadata instance( InputStream stream ) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta); HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }  static ExcelMetadata instance( InputStream stream );  }