Java Code Examples for org.apache.poi.xssf.eventusermodel.XSSFReader#SheetIterator

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException
 * @throws OpenXML4JException
 * @throws ParserConfigurationException
 * @throws SAXException
 */
public void process() throws IOException, OpenXML4JException,
        ParserConfigurationException, SAXException {

    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
            this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
            .getSheetsData();
    //int index = 0;
    while (iter.hasNext()) {
        InputStream stream = iter.next();
        //String sheetName = iter.getSheetName();
        //this.output.println();
        //this.output.println(sheetName + " [index=" + index + "]:");
        processSheet(styles, strings, stream);
        stream.close();
        //++index;
    }
}
 

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException  If reading the data from the package fails.
 * @throws SAXException if parsing the XML data fails.
 */
private void process(OPCPackage xlsxPackage) throws IOException, OpenXML4JException, SAXException {
    long startTime = System.currentTimeMillis();
    StringsCache stringsCache = new StringsCache();
    try {
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage, stringsCache);
        XSSFReader xssfReader = new XSSFReader(xlsxPackage);
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        if (readConfig.readAllSheet) {
            while (iter.hasNext()) {
                try (InputStream stream = iter.next()) {
                    processSheet(strings, new XSSFSaxReadHandler<>(result, readConfig), stream);
                }
            }
        } else if (!readConfig.sheetNames.isEmpty()) {
            while (iter.hasNext()) {
                try (InputStream stream = iter.next()) {
                    if (readConfig.sheetNames.contains(iter.getSheetName())) {
                        processSheet(strings, new XSSFSaxReadHandler<>(result, readConfig), stream);
                    }
                }
            }
        } else {
            int index = 0;
            while (iter.hasNext()) {
                try (InputStream stream = iter.next()) {
                    if (readConfig.sheetIndexs.contains(index)) {
                        processSheet(strings, new XSSFSaxReadHandler<>(result, readConfig), stream);
                    }
                    ++index;
                }
            }
        }
    } finally {
        stringsCache.clearAll();
    }
    log.info("Sax import takes {} ms", System.currentTimeMillis() - startTime);
}
 

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException
 * @throws OpenXML4JException
 * @throws ParserConfigurationException
 * @throws SAXException
 */
public List<String> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    int index = 0;
    while (iter.hasNext()) {
        if (blankRowNum == 10) { break; }
        InputStream stream = iter.next();
        String sheetName = iter.getSheetName();
        results.add(ExcelValidator.SHEET_NAME_PREFIX + sheetName);
        processSheet(styles, strings, new SheetToCSV(), stream);
        stream.close();
        ++index;
    }

    return results;
}
 

public void process() throws IOException, OpenXML4JException,
        ParserConfigurationException, SAXException {

    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
            this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
            .getSheetsData();
    int index = 0;
    while (iter.hasNext()) {
        InputStream stream = iter.next();
        String sheetName = iter.getSheetName();
        this.output.println();
        this.output.println(sheetName + " [index=" + index + "]:");
        processSheet(styles, strings, stream);
        stream.close();
        ++index;
    }
    close();
}
 

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException
 * @throws OpenXML4JException
 * @throws ParserConfigurationException
 * @throws SAXException
 */
public List<String> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
	ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
	XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
	StylesTable styles = xssfReader.getStylesTable();
	XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
	int index = 0;
	while (iter.hasNext()) {
		if(blankRowNum == 10)break;
		InputStream stream = iter.next();
		String sheetName = iter.getSheetName();
		results.add(ExcelValidator.SHEET_NAME_PREFIX + sheetName);
		processSheet(styles, strings, new SheetToCSV(), stream);
		stream.close();
		++index;
	}

	return results;
}
 

public void parseXlsxFileAndWriteTmxBody(String fileName, AbstractWriter tmxWriter, IProgressMonitor monitor)
		throws ParserConfigurationException, SAXException, IOException, OpenXML4JException {
	this.tmxWriter = tmxWriter;
	this.monitor = monitor;
	File file = new File(fileName);
	long length = file.length();
	monitor.beginTask("", countTotal(length));
	OPCPackage p = OPCPackage.open(fileName, PackageAccess.READ);
	ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
	XSSFReader xssfReader = new XSSFReader(p);
	XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
	try {
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			parse(stream, strings, tmxWriter);
			stream.close();
			// 目前只处理第一个sheet
			break;
		}
	} finally {
		p.close();
	}

	monitor.done();
}
 

public void readRows(IProgressMonitor monitor) throws ParserConfigurationException, SAXException, IOException, OpenXML4JException {
	monitor.beginTask("", 10);
	monitor.worked(1);
	OPCPackage p = OPCPackage.open(xlsxFile, PackageAccess.READ);
	ReadOnlySharedStringsTable shareString = new ReadOnlySharedStringsTable(p);
	XSSFReader xssfReader = new XSSFReader(p);
	XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
	try {
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			readCells(stream, shareString, new SubProgressMonitor(monitor, 9));
			stream.close();
			// 目前只处理第一个sheet
			break;
		}
	} finally {
		p.close();
		monitor.done();
	}
}
 

public void parseXlsxFileAndWriteTmxBody(String fileName, AbstractWriter tmxWriter, IProgressMonitor monitor)
		throws ParserConfigurationException, SAXException, IOException, OpenXML4JException {
	this.tmxWriter = tmxWriter;
	this.monitor = monitor;
	File file = new File(fileName);
	long length = file.length();
	monitor.beginTask("", countTotal(length));
	OPCPackage p = OPCPackage.open(fileName, PackageAccess.READ);
	ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
	XSSFReader xssfReader = new XSSFReader(p);
	XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
	try {
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			parse(stream, strings, tmxWriter);
			stream.close();
			// 目前只处理第一个sheet
			break;
		}
	} finally {
		p.close();
	}

	monitor.done();
}
 

/**
 * Get the sheet that should be parsed
 * @param options The options
 * @param iter The sheet iterator
 * @return A sheet that matches the name if the sheet name is provided
 */
private InputStream getSheetForParsing(ExcelSourceOptions<R> options, XSSFReader.SheetIterator iter) {
    while(iter.hasNext()){
        InputStream inputStream = iter.next();
        final String sheetName = iter.getSheetName();
        if(options.getSheetName() == null){
            return inputStream;
        }else{
            if( sheetName.equals(options.getSheetName())){
                return inputStream;
            }
        }
    }
    throw new DataFrameException("No sheet found for that matched configured sheet " + options.getSheetName());
}
 

@Override
public void apply(XSSFContext context) throws Exception {
       OPCPackage xlsxPackage = OPCPackage.open(context.getInpuStream());
       XSSFReader xssfReader = new XSSFReader(xlsxPackage);
       context.setStyles(xssfReader.getStylesTable());
       context.setStrings(new ReadOnlySharedStringsTable(xlsxPackage));
       
       initContext(context);
       
       XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
               .getSheetsData();
       while (iter.hasNext()) {
           InputStream stream = iter.next();
           context.setInpuStream(stream);
           String sheetName = context.getImporterSolution().getExcelSheetName();
           if (StringUtils.isNotEmpty(sheetName)) {
			if (sheetName.equals(iter.getSheetName())) {
				sheetPolicy.apply(context);
				break;
			}
		} else {
            sheetPolicy.apply(context);
		}
           stream.close();
       }
       
       parseRecordPolicy.apply(context);
       
}
 

@Override
public void loadData(List<Tuple<String, File>> files, Importer importer) throws InvalidFileException, IOException {
  try {
    RowCellHandler rowCellHandler = makeRowCellHandler(importer);
    SAXParserFactory saxFactory = SAXParserFactory.newInstance();
    saxFactory.setNamespaceAware(true);
    for (Tuple<String, File> file : files) {
      OPCPackage pkg = OPCPackage.open(file.getRight().getPath());
      XSSFReader xssfReader = new XSSFReader(pkg);

      final SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
      XSSFReader.SheetIterator worksheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

      while (worksheets.hasNext()) {
        final InputStream sheet = worksheets.next();

        XMLReader sheetParser = saxFactory.newSAXParser().getXMLReader();
        sheetParser.setContentHandler(new SheetXmlParser(sharedStringsTable, rowCellHandler));

        rowCellHandler.start(worksheets.getSheetName());
        sheetParser.parse(new InputSource(sheet));
        rowCellHandler.finish();
      }
    }
  } catch (SAXException | OpenXML4JException | ParserConfigurationException e) {
    throw new InvalidFileException("Not a valid Excel file", e);
  }
}
 

public XlsxSaxAnalyser(XlsxReadContext xlsxReadContext, InputStream decryptedStream) throws Exception {
    this.xlsxReadContext = xlsxReadContext;
    // Initialize cache
    XlsxReadWorkbookHolder xlsxReadWorkbookHolder = xlsxReadContext.xlsxReadWorkbookHolder();

    OPCPackage pkg = readOpcPackage(xlsxReadWorkbookHolder, decryptedStream);
    xlsxReadWorkbookHolder.setOpcPackage(pkg);

    ArrayList<PackagePart> packageParts = pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());

    if (!CollectionUtils.isEmpty(packageParts)) {
        PackagePart sharedStringsTablePackagePart = packageParts.get(0);

        // Specify default cache
        defaultReadCache(xlsxReadWorkbookHolder, sharedStringsTablePackagePart);

        // Analysis sharedStringsTable.xml
        analysisSharedStringsTable(sharedStringsTablePackagePart.getInputStream(), xlsxReadWorkbookHolder);
    }

    XSSFReader xssfReader = new XSSFReader(pkg);
    analysisUse1904WindowDate(xssfReader, xlsxReadWorkbookHolder);

    xlsxReadWorkbookHolder.setStylesTable(xssfReader.getStylesTable());
    sheetList = new ArrayList<ReadSheet>();
    sheetMap = new HashMap<Integer, InputStream>();
    commentsTableMap = new HashMap<Integer, CommentsTable>();
    XSSFReader.SheetIterator ite = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
    int index = 0;
    if (!ite.hasNext()) {
        throw new ExcelAnalysisException("Can not find any sheet!");
    }
    while (ite.hasNext()) {
        InputStream inputStream = ite.next();
        sheetList.add(new ReadSheet(index, ite.getSheetName()));
        sheetMap.put(index, inputStream);
        if (xlsxReadContext.readWorkbookHolder().getExtraReadSet().contains(CellExtraTypeEnum.COMMENT)) {
            CommentsTable commentsTable = ite.getSheetComments();
            if (null != commentsTable) {
                commentsTableMap.put(index, commentsTable);
            }
        }
        index++;
    }
}
 

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