org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler Java Examples

/**
 * Parses and shows the content of one sheet using the specified styles and
 * shared-strings tables.
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
                         InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}
 

/**
 * Parses and shows the content of one sheet using the specified styles and
 * shared-strings tables.
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
		InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
	DataFormatter formatter = new DataFormatter();
	InputSource sheetSource = new InputSource(sheetInputStream);
	try {
		XMLReader sheetParser = SAXHelper.newXMLReader();
		ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
		sheetParser.setContentHandler(handler);
		sheetParser.parse(sheetSource);
	} catch (ParserConfigurationException e) {
		throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
	}
}
 

/**
 * Parses the content of one sheet using the specified styles and shared-strings tables.
 * 
 * @param styles a {@link StylesTable} object
 * @param sharedStringsTable a {@link ReadOnlySharedStringsTable} object
 * @param sheetInputStream a {@link InputStream} object
 * @throws IOException
 * @throws ParserConfigurationException
 * @throws SAXException
 */
private void readSheet(StylesTable styles, ReadOnlySharedStringsTable sharedStringsTable,
    InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {

  SAXParserFactory saxFactory = SAXParserFactory.newInstance();
  XMLReader sheetParser = saxFactory.newSAXParser().getXMLReader();

  ContentHandler handler =
      new XSSFSheetXMLHandler(styles, sharedStringsTable, sheetContentsHandler, true);

  sheetParser.setContentHandler(handler);
  sheetParser.parse(new InputSource(sheetInputStream));
}
 

/**
 * Processes data from an Excel file contained in the OPCPackage using the
 * reader implementation
 * <p>
 * Please note that the process will read data from the first sheet in the
 * File when if sheet name is not specified
 * (i.e. the sheet name defaults to the {@link EntityHandler.DEFAULT_SHEET})
 * </p>
 * @param opcPackage the OpenXML OPC Package
 * @param sheetName The sheet name
 * @param reader the reader implementation that handles the entity mapping
 */
private static void process(OPCPackage opcPackage, String sheetName, ZeroCellReader reader) {
    try {
        DataFormatter dataFormatter = new DataFormatter();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
        XSSFReader xssfReader = new XSSFReader(opcPackage);
        StylesTable stylesTable = xssfReader.getStylesTable();
        InputStream sheetInputStream = null;
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheets.hasNext()) {
            sheetInputStream = sheets.next();

            if (EntityHandler.DEFAULT_SHEET.equalsIgnoreCase(sheetName)) {
                break;
            }
            if (sheets.getSheetName().equalsIgnoreCase(sheetName)) {
                break;
            } else {
                sheetInputStream = null;
            }
        }

        if (Objects.isNull(sheetInputStream)) {
            throw new SheetNotFoundException(sheetName);
        }

        XMLReader xmlReader = SAXHelper.newXMLReader();
        xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, strings, reader, dataFormatter, false));
        xmlReader.parse(new InputSource(sheetInputStream));
        sheetInputStream.close();
        xmlReader = null;
        sheetInputStream = null;
        stylesTable = null;
        strings = null;
        xssfReader = null;
    } catch(InvalidFormatException | EmptyFileException | NotOfficeXmlFileException ife) {
        throw new ZeroCellException(ERROR_NOT_OPENXML);
    } catch(SheetNotFoundException ex) {
        throw new ZeroCellException(ex.getMessage());
    } catch (ZeroCellException ze) {
        throw ze; // Rethrow the Exception
    } catch (Exception e) {
        throw new ZeroCellException("Failed to process file", e);
    }
}
 

/**
 * Handles an individual Excel sheet from the entire Excel document. Each sheet will result in an individual flowfile.
 *
 * @param session
 *  The NiFi ProcessSession instance for the current invocation.
 */
private void handleExcelSheet(ProcessSession session, FlowFile originalParentFF, final InputStream sheetInputStream, ExcelSheetReadConfig readConfig,
                              CSVFormat csvFormat) throws IOException {

    FlowFile ff = session.create(originalParentFF);
    try {
        final DataFormatter formatter = new DataFormatter();
        final InputSource sheetSource = new InputSource(sheetInputStream);

        final SheetToCSV sheetHandler = new SheetToCSV(readConfig, csvFormat);

        final XMLReader parser = SAXHelper.newXMLReader();

        //If Value Formatting is set to false then don't pass in the styles table.
        // This will cause the XSSF Handler to return the raw value instead of the formatted one.
        final StylesTable sst = readConfig.getFormatValues()?readConfig.getStyles():null;

        final XSSFSheetXMLHandler handler = new XSSFSheetXMLHandler(
                sst, null, readConfig.getSharedStringsTable(), sheetHandler, formatter, false);

        parser.setContentHandler(handler);

        ff = session.write(ff, new OutputStreamCallback() {
            @Override
            public void process(OutputStream out) throws IOException {
                PrintStream outPrint = new PrintStream(out);
                sheetHandler.setOutput(outPrint);

                try {
                    parser.parse(sheetSource);

                    sheetInputStream.close();

                    sheetHandler.close();
                    outPrint.close();
                } catch (SAXException se) {
                    getLogger().error("Error occurred while processing Excel sheet {}", new Object[]{readConfig.getSheetName()}, se);
                }
            }
        });

        ff = session.putAttribute(ff, SHEET_NAME, readConfig.getSheetName());
        ff = session.putAttribute(ff, ROW_NUM, new Long(sheetHandler.getRowCount()).toString());

        if (StringUtils.isNotEmpty(originalParentFF.getAttribute(CoreAttributes.FILENAME.key()))) {
            ff = session.putAttribute(ff, SOURCE_FILE_NAME, originalParentFF.getAttribute(CoreAttributes.FILENAME.key()));
        } else {
            ff = session.putAttribute(ff, SOURCE_FILE_NAME, UNKNOWN_SHEET_NAME);
        }

        //Update the CoreAttributes.FILENAME to have the .csv extension now. Also update MIME.TYPE
        ff = session.putAttribute(ff, CoreAttributes.FILENAME.key(), updateFilenameToCSVExtension(ff.getAttribute(CoreAttributes.UUID.key()),
                ff.getAttribute(CoreAttributes.FILENAME.key()), readConfig.getSheetName()));
        ff = session.putAttribute(ff, CoreAttributes.MIME_TYPE.key(), CSV_MIME_TYPE);

        session.transfer(ff, SUCCESS);

    } catch (SAXException | ParserConfigurationException saxE) {
        getLogger().error("Failed to create instance of Parser.", saxE);
        ff = session.putAttribute(ff,
                ConvertExcelToCSVProcessor.class.getName() + ".error", saxE.getMessage());
        session.transfer(ff, FAILURE);
    } finally {
        sheetInputStream.close();
    }
}