org.apache.poi.ss.usermodel.WorkbookFactory Java Examples

private List<String> getExcelResourceLines(String fileName) {
    List<String> expectedLines = new ArrayList<>();
    try {
        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        int index = 0;
        for (Row row : sheet) {
            String rowStr = "";
            for (Cell cell : row) {
                rowStr += df.formatCellValue(cell, evaluator);
            }
            expectedLines.add(index, rowStr);
            index++;
        }
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
    return expectedLines;
}
 

@Test
public void poi07() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xlsx");
    Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的
    Sheet sheet = workbook.getSheetAt(0);
    Row hssfRow = sheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormatString());
    isDate(hssfRow.getCell(0));
    isDate(hssfRow.getCell(1));
    isDate(hssfRow.getCell(2));
    isDate(hssfRow.getCell(3));

}
 

/**
 * 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();
    }
}
 

/**
 * 在指定的工作簿、行、列书写值。
 * 作者：朱志杰
 * Sep 19, 2012 3:25:01 PM
 * @param sheetOrder 工作簿序号，基于0.
 * @param colum 列 基于1
 * @param row 行 基于1
 * @param content 将要被书写的内容。
 * @throws Exception 书写后保存异常。
 */
public void write(int sheetOrder,int colum, int row, String content) throws Exception {
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	Row rows = sheet.createRow(row-1);
	Cell cell = rows.createCell(colum-1);
	cell.setCellValue(content);
	FileOutputStream fileOut = new FileOutputStream(path);
	workbook.write(fileOut);
	fileOut.close();

}
 

public ExcelReader(String base64Content) {
    byte[] decoded = Base64.getDecoder().decode(base64Content);
    InputStream input = new ByteArrayInputStream(decoded);

    try {
        Workbook workbook = WorkbookFactory.create(input);
        if (workbook.getNumberOfSheets() == 0) {
            workbook.createSheet();
        }

        sheet = workbook.getSheetAt(0);
        header = sheet.getPhysicalNumberOfRows() == 0 ? null : readRow(0);
    } catch (IOException | InvalidFormatException e) {
        throw new IllegalArgumentException(e);
    }
}
 

private List<Match> importFromInputStream(InputStream inp) throws IOException, InvalidFormatException {
	final List<Match> matches = new ArrayList<>();

	try (Workbook wb = WorkbookFactory.create(inp)) {
		Sheet sheet = wb.getSheetAt(0);
		for (Row row : sheet) {
			if (row.getRowNum() == 0) {
				continue;
			}
			Match match = convertRowToMatch(row);
			if (match != null) {
				matches.add(match);
			}
		}

		return matches;
	}
}
 

@Test
public void testParseHandlesStartingFromANonZeroOffset() throws IOException, InvalidFormatException, DataParserException {
  InputStream file = getFile("/excel/TestOffset.xlsx");
  Workbook workbook = WorkbookFactory.create(file);
  WorkbookParserSettings settings = WorkbookParserSettings.builder()
      .withHeader(ExcelHeader.IGNORE_HEADER)
      .build();

  WorkbookParser parser = new WorkbookParser(settings, getContext(), workbook, "Sheet2::2");

  Record firstContentRow = parser.parse();

  LinkedHashMap<String, Field> contentMap = new LinkedHashMap<>();
  for (int i = 0; i <= 2; i++) {
    contentMap.put(String.valueOf(i), Field.create(new BigDecimal(i + 4)));
  }
  Field expected = Field.createListMap(contentMap);

  assertEquals(expected, firstContentRow.get());
}
 

@Test
public void saveTest_HeadersOnly() throws InvalidFormatException, IOException {

	ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
	// save it
	Ssio.save(ITRecord.getHeaderMap(), null, outputStream);
	byte[] spreadsheet = outputStream.toByteArray();

	// do a save for human eye check
	FileUtils.writeByteArrayToFile(createFile("saveTest_HeadersOnly"), spreadsheet);

	// then parse it
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);

	// size
	Assert.assertEquals(0, sheet.getLastRowNum());
	Assert.assertEquals(ITRecord.getHeaderMap().size(), headerRow.getLastCellNum());

}
 

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 testAsExcelOutput() throws ResourceException, ReportProcessingException, IOException, SAXException,
  ParserConfigurationException, InvalidFormatException {
  final URL url = getClass().getResource( "Prd-3431.prpt" );
  assertNotNull( url );
  final ResourceManager resourceManager = new ResourceManager();
  resourceManager.registerDefaults();
  final Resource directly = resourceManager.createDirectly( url, MasterReport.class );
  final MasterReport report = (MasterReport) directly.getResource();
  final MemoryByteArrayOutputStream mbos = new MemoryByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, new NoCloseOutputStream( mbos ) );

  final ByteArrayInputStream bin = new ByteArrayInputStream( mbos.getRaw(), 0, mbos.getLength() );
  final Workbook workbook = WorkbookFactory.create( bin );
  assertEquals( 4, workbook.getNumberOfSheets() );
  assertEquals( "Summary", workbook.getSheetAt( 0 ).getSheetName() );
  assertEquals( "AuthorPublisher A", workbook.getSheetAt( 1 ).getSheetName() );
  assertEquals( "AuthorPublisher B", workbook.getSheetAt( 2 ).getSheetName() );
  assertEquals( "AuthorPublisher C", workbook.getSheetAt( 3 ).getSheetName() );
}
 

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() );
      }
    }

  }

}
 

@Test
public void saveMapsTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
	LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
	headerMap.put("primInt", "Primitive Int");
	headerMap.put("fake", "Not Real");

	Map<String, Object> record = new LinkedHashMap<>();
	record.put("primInt", 123);
	record.put("fake", "someValueAnyway");

	Collection<Map<String, Object>> records = Arrays.asList(record);
	File outputFile = createFile("saveMapsTest_IgnoringErrors_File");

	// save it
	Ssio.saveMaps(headerMap, records, outputFile);


	// then parse it
	byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);
	Row dataRow = sheet.getRow(1);

	Cell cell00 = headerRow.getCell(0);
	Cell cell01 = headerRow.getCell(1);
	Cell cell10 = dataRow.getCell(0);
	Cell cell11 = dataRow.getCell(1);


	// texts
	Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
	Assert.assertEquals("Not Real", cell01.getStringCellValue());
	Assert.assertEquals("123", cell10.getStringCellValue());
	Assert.assertEquals("someValueAnyway", cell11.getStringCellValue());

}
 

private static Workbook toWorkbook(InputStream inputStream)
		throws InvalidFormatException {
	try {
		return WorkbookFactory.create(inputStream);
	} catch (IOException e) {
		throw new RuntimeException(e);
	}
}
 

@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());
}
 

private void init(final InputStream inputStream) {
    if (inputStream == null) {
        throw new NullPointerException("inputStream cannot be null");
    }

    try {
        workbook = WorkbookFactory.create(inputStream);
    } catch (final Exception e) {
        throw new ExcelException(e);
    }
}
 

/**
 * 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;
	}
}
 

@Test
public void testExcelExport() throws Exception {
  URL resource = getClass().getResource( "Prd-4968.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, bout );
  Workbook workbook = WorkbookFactory.create( new ByteArrayInputStream( bout.toByteArray() ) );
  assertEquals( 34, workbook.getNumCellStyles() );
  assertEquals( 9, workbook.getNumberOfFonts() );
}
 

@Test
void iteratorHeaderCaseSensitive() throws IOException {
  String fileName = "/case-sensitivity.xlsx";
  try (InputStream inputStream = getClass().getResourceAsStream(fileName)) {
    Workbook workbook = WorkbookFactory.create(inputStream);
    ExcelRepository excelRepository =
        new ExcelRepository(
            workbook.getSheet("case-sensitivity"), entityTypeFactory, attrMetaFactory);
    Entity entity = excelRepository.iterator().next();
    assertEquals("Value #0", entity.get("Header"));
    assertNull(entity.get("hEADER"));
  }
}
 

public static int getNumberOfSheets(File file) {
  if (!isExcelFile(file.getName())) return -1;
  try (FileInputStream fis = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(fis)) {
    return workbook.getNumberOfSheets();
  } catch (Exception e) {
    throw new MolgenisDataException(e);
  }
}
 

/**
 * Load a specific page of the given Excel Spreadsheet
 * 
 * @param bytes
 *            Excel content as byte array
 * @param page
 *            page to use
 * @return true on success
 */
public boolean load(final InputStream inputStream, final int page){
	try {
		Workbook wb = WorkbookFactory.create(inputStream);
		sheet = wb.getSheetAt(page);
		return true;
	} catch (Exception ex) {
		return false;
	}
}
 

@Test
public void testSheetNames() 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() );
  Assert.assertEquals( "FIRST REPORT", workbook.getSheetName( 0 ) );
  Assert.assertEquals( "SECOND REPORT", workbook.getSheetName( 1 ) );
  Assert.assertEquals( "SECOND REPORT 2", workbook.getSheetName( 2 ) );
}
 

@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 ) );
}
 

@Override
public void parseInput() throws ModuleException {
	// Get workbook 
	Workbook wb;
	try {
		wb = WorkbookFactory.create(this.payload.getInputStream());
	} catch (Exception e) {
		throw new ModuleException(e.getMessage(), e);
	}
	// Get the sheet
	Sheet sheet = retrieveSheet(wb, this.sheetName, this.sheetIndex);	
	// Get the number of rows and columns
	if (this.columnCount == 0) { // this only happens if processFieldNames = fromFile
		this.columnCount = retrieveHeaderColumnCount(sheet);
	}

	this.noOfRows = sheet.getLastRowNum() + 1;

	// Get the column names from header
	if (this.processFieldNames.equalsIgnoreCase("fromFile")) {
		this.columnNames = retrieveColumnNamesFromFileHeader(sheet, this.columnCount);
	}

	// Get the cell contents of the sheet
	this.sheetContents = extractSheetContents(sheet, wb, 
			this.rowOffset, this.noOfRows, this.columnOffset, this.columnCount, 
			this.skipEmptyRows, this.evaluateFormulas, this.formatting,
			this.debug);
}
 

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;
	}
 

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

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);
	} 
}
 

/**
 * 得到一个工作区最后一条记录的序号，相当于这个工作簿共多少行数据。
 * @param sheetOrder 工作区序号
 * @return int 序号。
 * @throws IOException 根据excel路径加载excel异常。
 * @throws InvalidFormatException 
 */
public int getSheetLastRowNum(int sheetOrder) throws IOException, InvalidFormatException{
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	return sheet.getLastRowNum();
}
 

/**
 * 读取某个工作簿上的某个单元格的值。
 * 作者：朱志杰
 * Sep 19, 2012 3:27:08 PM
 * @param sheetOrder 工作簿序号,从0开始。
 * @param colum 列数 从1开始
 * @param row 行数 从1开始
 * @return 单元格的值。
 * @throws Exception 加载excel异常。
 */
public String read(int sheetOrder,int colum, int row) throws Exception {
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	Row rows = sheet.getRow(row-1);
	Cell cell = rows.getCell(colum-1);
	String content = cell.getStringCellValue();
	return content;
}
 

@BeforeEach
void beforeMethod() throws IOException {
  is = getClass().getResourceAsStream("/test.xls");
  workbook = WorkbookFactory.create(is);
  excelSheetReader =
      new ExcelRepository(workbook.getSheet("test"), entityTypeFactory, attrMetaFactory);
}
 

@Test
public void testExcel2007Export() throws Exception {
  URL resource = getClass().getResource( "Prd-4968.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLSX( report, bout );
  Workbook workbook = WorkbookFactory.create( new ByteArrayInputStream( bout.toByteArray() ) );
  assertEquals( 14, workbook.getNumCellStyles() );
  assertEquals( 6, workbook.getNumberOfFonts() );

  //    File testOutputFile = DebugReportRunner.createTestOutputFile();
  //    ExcelReportUtil.createXLSX(report, "test-output/Prd-4988.xlsx");

}