org.apache.poi.openxml4j.exceptions.OpenXML4JException Java Examples

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

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

@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 static String Parse07(String FilePath) throws IOException, XmlException, OpenXML4JException{
    String text2007=null;
    try{
        OPCPackage opcPackage = POIXMLDocument.openPackage(FilePath);
        POIXMLTextExtractor extractor = new XWPFWordExtractor(opcPackage);
        text2007 = extractor.getText();

    } catch (Exception e) {
        e.printStackTrace();
    }
    return text2007;
}
 

private String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}
 

private static void listEmbeds(XWPFDocument doc) throws OpenXML4JException {
	List<PackagePart> embeddedDocs = doc.getAllEmbedds();
	if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
		Iterator<PackagePart> pIter = embeddedDocs.iterator();
		while (pIter.hasNext()) {
			PackagePart pPart = pIter.next();
			System.out.print(pPart.getPartName() + ", ");

			System.out.print(pPart.getContentType() + ", ");
			System.out.println();
		}
	}
}
 

<T> void listOfEncryptedItems(Class<T> type, Consumer<? super T> consumer, POIFSFileSystem fs) throws IOException {
    InputStream stream = DocumentFactoryHelper.getDecryptedStream(fs, options.getPassword());

    try (OPCPackage open = OPCPackage.open(stream)) {
        unmarshal0(type, consumer, open);

    } catch (ParserConfigurationException | SAXException | IOException | OpenXML4JException e) {
        IOUtils.closeQuietly(fs);
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

@Override
public <T> T returnFromExcelFile(Class<T> type) {
    try (OPCPackage open = OPCPackage.open(file, PackageAccess.READ)) {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(open);
        PropertyHandler propertyHandler = new PropertyHandler();
        return propertyHandler.unmarshal(type, xssfWorkbook.getProperties());
    } catch (IOException | OpenXML4JException e) {
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

@Override
public <T> void returnFromExcelFile(Class<T> type, Consumer<? super T> consumer) {

    try (OPCPackage open = OPCPackage.open(poijiInputStream.stream())) {

        unmarshal0(type, consumer, open);

    } catch (ParserConfigurationException | SAXException | IOException | OpenXML4JException e) {
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

@Override
public <T> T returnFromExcelFile(Class<T> type) {
    try (OPCPackage open = OPCPackage.open(inputStream)) {
        PropertyHandler propertyHandler = new PropertyHandler();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(open);
        return propertyHandler.unmarshal(type, xssfWorkbook.getProperties());
    } catch (IOException | OpenXML4JException e) {
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

@Override
public <T> void returnFromExcelFile(Class<T> type, Consumer<? super T> consumer) {

    try (OPCPackage open = OPCPackage.open(poijiFile.file(), PackageAccess.READ)) {

        unmarshal0(type, consumer, open);

    } catch (ParserConfigurationException | SAXException | IOException | OpenXML4JException e) {
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

/**
 * 遍历工作簿中所有的电子表格
 * 
 * @param filename
 * @throws IOException
 * @throws OpenXML4JException
 * @throws SAXException
 * @throws Exception
 */
public void process(String filename) throws IOException, OpenXML4JException, SAXException {
    OPCPackage pkg = null;
    try {
	    pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);
        Iterator<InputStream> sheets = xssfReader.getSheetsData();
   		while (sheets.hasNext()) {
   			currRowIndex = 0;
   			sheetIndex++;
   			InputStream sheet = null;
   			try {
   			    sheet = sheets.next();
   			    InputSource sheetSource = new InputSource(sheet);
   			    parser.parse(sheetSource);
   			} finally {
   			    sheet.close();
               }
   		}
   		while (!context.fileEnd());
	} finally {
           pkg.close();
       }
}
 

private static String getTextFromExcel(String fileName)
		throws InvalidFormatException, IOException, OpenXML4JException, XmlException {
	File inputFile = new File(fileName);
	POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
	return extractor.getText();
}
 

public XlsxFileReader(InputStream fis) throws IOException,
		OpenXML4JException {
	OPCPackage pkg = OPCPackage.open(fis);
	reader = new XSSFReader(pkg);
}