Java Code Examples for org.apache.poi.ss.usermodel.WorkbookFactory#create()

private void validateExcelSheet( final ByteArrayOutputStream boutSlow, final TableModel data ) throws IOException,
  InvalidFormatException {
  Workbook workbook = WorkbookFactory.create( new ByteArrayInputStream( boutSlow.toByteArray() ) );
  Sheet sheet = workbook.getSheetAt( 0 );
  Assert.assertEquals( 0, sheet.getFirstRowNum() );
  Assert.assertEquals( data.getRowCount() - 1, sheet.getLastRowNum() );

  for ( int r = 0; r < data.getRowCount(); r += 1 ) {
    Row row = sheet.getRow( r );
    for ( int c = 0; c < data.getColumnCount(); c += 1 ) {
      Cell cell = row.getCell( c );

      Object valueAt = data.getValueAt( r, c );
      if ( valueAt == null ) {
        if ( cell != null ) {
          // excel cells never return null
          Assert.assertEquals( "", cell.getStringCellValue() );
        }
      } else {
        Assert.assertEquals( valueAt, cell.getStringCellValue() );
      }
    }

  }

}
 

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String.
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    try (Workbook wb = WorkbookFactory.create(input)) {
        // Only support single sheet files for now
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        JSONArray rowList = new JSONArray();
        String[] columns;
        for (Row row : sheet) {
            if (row.getRowNum() <= numRows) {
                //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't shift cells
                columns = new String[row.getLastCellNum()];
                for (int i = 0; i < row.getLastCellNum(); i++ ) {
                    columns[i] = df.formatCellValue(row.getCell(i), evaluator);
                }
                rowList.add(columns);
            }
        }
        return rowList.toString();
    }
}
 

private Map<String, List<List<String>>> readExcel(File tmpFile)
    throws IOException, InvalidFormatException {
  Map<String, List<List<String>>> actual = newHashMap();
  try (Workbook workbook = WorkbookFactory.create(tmpFile)) {
    List<List<String>> sheetResult = newArrayList();
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
      Sheet sheet = workbook.getSheetAt(sheetNum);
      for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
        List<String> rowResult = newArrayList();
        Row row = sheet.getRow(rowNum);
        for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
          rowResult.add(row.getCell(colNum, CREATE_NULL_AS_BLANK).getStringCellValue());
        }
        sheetResult.add(rowResult);
      }
      actual.put(sheet.getSheetName(), sheetResult);
    }
  }
  return actual;
}
 

public void importExcel(final String ds, final String collection, final String pathOrUrl) throws OperationException {
    final String filepath = pathOrUrl.startsWith("http:") ? downloadFile(pathOrUrl) : pathOrUrl;

    try {
        Workbook workbook = WorkbookFactory.create(new File(filepath));

    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
        throw new OperationException(ErrorCode.EXCEL_DATA_READ_ERR, "Unable to read file at: " + filepath);
    }

}
 

public static Workbook createWorkbook(InputStream in){
		Workbook workbook = null;
		try {
//			br.mark(1024*10);
			workbook = WorkbookFactory.create(in);
		} catch (Exception e) {
			throw wrapAsUnCheckedException("read excel inputstream error : " + in, e);
		}finally{
			IOUtils.closeQuietly(in);
		}
		return workbook;
	}
 

@Test
void renameSheetTest() throws IOException, InvalidFormatException {
  File file = ResourceUtils.getFile(getClass(), "/test.xls");
  File temp = File.createTempFile("unittest_", ".xls");
  FileUtils.copyFile(file, temp);
  ExcelUtils.renameSheet("unittest", temp, 0);
  Workbook workbook = WorkbookFactory.create(new FileInputStream(temp));
  assertEquals("unittest", workbook.getSheetAt(0).getSheetName());
}
 

@Test
public void poi0702() throws Exception {
    Workbook workbook = WorkbookFactory.create(new FileInputStream("D:\\test\\t2.xlsx"));
    workbook = WorkbookFactory.create(new File("D:\\test\\t2.xlsx"));
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    System.out.println(row.getCell(0).getNumericCellValue());
}
 

@Test
public void poi0701() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\f1.xlsx");
    Workbook workbook = WorkbookFactory.create(is);
    Sheet sheet = workbook.getSheetAt(0);
    print(sheet.getRow(0).getCell(0));
    print(sheet.getRow(1).getCell(0));
    print(sheet.getRow(2).getCell(0));
    print(sheet.getRow(3).getCell(0));
}
 

@SuppressWarnings("deprecation")
@Test
void iteratorDuplicateSheetHeader() throws IOException {
  String fileName = "/duplicate-sheet-header.xlsx";
  try (InputStream inputStream = getClass().getResourceAsStream(fileName)) {
    Workbook workbook = WorkbookFactory.create(inputStream);
    ExcelRepository excelRepository =
        new ExcelRepository(workbook.getSheet("attributes"), entityTypeFactory, attrMetaFactory);
    Exception exception = assertThrows(MolgenisDataException.class, excelRepository::iterator);
    assertThat(exception.getMessage())
        .containsPattern("Duplicate column header 'entity' in sheet 'attributes' not allowed");
  }
}
 

public ExcelDocReader(InputStream inputStream) {
  try {
    this.workbook = WorkbookFactory.create(inputStream);
    this.inputStream = inputStream;
  } catch (IOException | InvalidFormatException e) {
    throw new DocumentException(e);
  }

}
 

@Test
public void testSheetContent() throws Exception {
  MasterReport report = createReport();
  Assert.assertFalse( new ReportStructureValidator().isValidForFastProcessing( report ) );

  ByteArrayOutputStream boutFast = new ByteArrayOutputStream();
  FastExcelReportUtil.processXlsx( report, boutFast );

  Workbook workbook = WorkbookFactory.create( new ByteArrayInputStream( boutFast.toByteArray() ) );
  Assert.assertEquals( 3, workbook.getNumberOfSheets() );
  assertSheetNotEmpty( workbook.getSheetAt( 0 ) );
  assertSheetNotEmpty( workbook.getSheetAt( 1 ) );
  assertSheetNotEmpty( workbook.getSheetAt( 2 ) );
}
 

/**
 * Load a specific page of the given Excel Spreadsheet
 * 
 * @param file
 *            filename of the Excel file
 * @param page
 *            page to use
 * @return true on success
 * @deprecated use load(InputStream) instead
 */
@Deprecated
public boolean load(final String file, final int page){
	try {
		Workbook wb = WorkbookFactory.create(new FileInputStream(file));
		sheet = wb.getSheetAt(page);
		return true;
	} catch (Exception ex) {
		return false;
	}
}
 

public static Workbook readWorkbook(InputStream inp){
	try {
		return WorkbookFactory.create(inp);
	} catch (Exception e) {
		throw new ExcelException("read workbook error by inputStream : " + e.getMessage(), e);
	} 
}
 

public ExcelSheetsExtractor(byte[] bytes) throws WorkbookParsingException
{
    try (Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(bytes)))
    {
        sheets = getAllSheetsFromWorkbook(wb);
    }
    catch (EncryptedDocumentException | IOException e)
    {
        throw new WorkbookParsingException(e);
    }
}
 

/**
 * Please have a look at <a href="https://jira.talendforge.org/browse/TDP-4571">TDP-4571</a>.
 */
@Test
public void export_bugfix() throws Exception {
    // given
    SchemaParser.Request request = createSchemaParser("export_bug_fix_xlsx.json");

    Workbook workbook = WorkbookFactory.create(request.getContent());
    assertThat(workbook).isNotNull();
    assertThat(workbook.getNumberOfSheets()).isEqualTo(1);

    Sheet sheet = workbook.getSheetAt(0);
    assertThat(sheet).isNotNull().isNotEmpty();
    assertThat(sheet.getFirstRowNum()).isEqualTo(0);
    assertThat(sheet.getLastRowNum()).isEqualTo(6);

    // assert header content
    Row row = sheet.getRow(0);
    /*
     * "columns": [ { "id": "id", "type": "string" }, { "id": "firstname", "type": "string" }, { "id": "lastname",
     * "type": "string" }, { "id": "age", "type": "integer" }, { "id": "date-of-birth", "type": "date" }, { "id":
     * "alive", "type": "boolean" }, { "id": "city", "type": "string" }, { "id": "7", "type": "float" } ]
     */
    assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("id");
    assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("firstname");
    assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("lastname");
    assertThat(row.getCell(3).getRichStringCellValue().getString()).isEqualTo("age");
    assertThat(row.getCell(4).getRichStringCellValue().getString()).isEqualTo("date-of-birth");
    assertThat(row.getCell(5).getRichStringCellValue().getString()).isEqualTo("alive");
    assertThat(row.getCell(6).getRichStringCellValue().getString()).isEqualTo("city");
    assertThat(row.getCell(7).getRichStringCellValue().getString()).isEqualTo("phone-number");

    // assert first content
    row = sheet.getRow(1);
    /*
     * { "id" : "1", "firstname" : "Clark", "lastname" : "Kent", "age" : "42", "date-of-birth" : "10/09/1940",
     * "alive" : "false", "city" : "", "phone-number" : "" }
     */

    assertRowValues(row, 1, "Clark", "Kent", 42, "10/09/1940", //
            false, "Smallville", "");

    // assert second row content
    row = sheet.getRow(2);
    /*
     * { "id" : "2", "firstname" : "Bruce", "lastname" : "Wayne", "age" : "50", "date-of-birth" : "01/01/1947",
     * "alive" : "true", "city" : "Gotham city", "phone-number" : "null" }
     */
    assertRowValues(row, 2, "Bruce", "Wayne", 50, "01/01/1947", //
            true, "Gotham city", "null");

    // assert third row content
    row = sheet.getRow(3);
    /*
     * { "id" : "3", "firstname" : "Barry", "lastname" : "Allen", "age" : "67", "date-of-birth" : "01/02/1948",
     * "alive" : "true", "city" : "Central city", "phone-number" : "+33 6 89 46 55 34" }
     */
    assertRowValues(row, 3, "Barry", "Allen", 67, "01/02/1948", //
            true, "Central city", "+33 6 89 46 55 34");

    // assert last content
    row = sheet.getRow(sheet.getLastRowNum());
    /*
     * { "id" : "6", "firstname" : "Ray", "lastname" : "Palmer", "age" : "93", "date-of-birth" : "01/05/1951",
     * "alive" : "true", "city" : "Star city" }
     */
    assertRowValues(row, 6, "Ray", "Palmer", 93, "01/05/1951", //
            true, "Star city", "+33-6-89-46-55-34");
}
 

private Workbook createWorkbook(String filePath) throws IOException, InvalidFormatException {
  return WorkbookFactory.create(getFile(filePath));
}
 

public Workbook getWorkbook(File file) throws IOException, InvalidFormatException {
	return WorkbookFactory.create(file);
}
 

/**
 * 通过数据流操作excel
 *
 * @param inputStream excel数据流
 * @param outFilePath 输出的excel文件路径
 * @throws IOException            IO流异常
 * @throws InvalidFormatException 非法的格式异常
 */
public ExcelReader(InputStream inputStream, String outFilePath) throws IOException, InvalidFormatException {
    this.startRow = 0;
    this.sheetName = "Sheet1";
    this.excelFilePath = outFilePath;
    this.workbook = WorkbookFactory.create(inputStream);
}
 

/**
 * 读取Excel内容
 *
 * @param file
 * @param sheetIndex
 * @return
 * @throws InvalidFormatException
 * @throws IOException
 */
public static ExcelHelper readExcel(File file, int sheetIndex) throws InvalidFormatException, IOException {
    // 读取Excel工作薄
    Workbook wb = WorkbookFactory.create(file);
    return _readExcel(wb, sheetIndex);
}
 

/**
 * Opens the specific file, and the active workbook,
 * 
 * @param data reader
 * @throws IOException            if any problem reading the file
 * @throws InvalidFormatException if any issue is encountered during parsing
 */
public ExcelReader(InputStream data) throws IOException, InvalidFormatException {
	workbook = WorkbookFactory.create(data);
	activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
	rowIterator = activesheet.rowIterator();
}