Java Code Examples for org.apache.poi.openxml4j.opc.OPCPackage#open()

@Test
public void testAccessEmptyIterator() throws InvalidFormatException, IOException {
    try (FileInputStream is = new FileInputStream("resources/document/notEmpty/notEmpty-template.docx");
            OPCPackage oPackage = OPCPackage.open(is);
            XWPFDocument document = new XWPFDocument(oPackage);) {
        TokenIterator iterator = new TokenIterator(document);
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertNotNull(iterator.next());
        assertFalse(iterator.hasNext());
        boolean hasException = false;
        try {
            iterator.next();
        } catch (NoSuchElementException e) {
            assertTrue(e instanceof NoSuchElementException);
            hasException = true;
        }
        assertTrue(hasException);
    }
}
 

/**
 * 遍历工作簿中所有的电子表格
 * @param filename
 * @param fileKey 
 * @throws Exception
 */
public void process( InputStream is ) throws Exception {
	OPCPackage pkg = OPCPackage.open( is );
	XSSFReader r = new XSSFReader(pkg);
	SharedStringsTable sst = r.getSharedStringsTable();
	XMLReader parser = fetchSheetParser(sst);
	Iterator<InputStream> sheets = r.getSheetsData();
	while (sheets.hasNext()) {
		curRow = 0;
		sheetIndex++;
		InputStream sheet = sheets.next();
		InputSource sheetSource = new InputSource(sheet);
		parser.parse( sheetSource );
		sheet.close();
	}
}
 

@Test
public void testLookaheadEmptyIterator() throws InvalidFormatException, IOException {
    try (FileInputStream is = new FileInputStream("resources/document/notEmpty/notEmpty-template.docx");
            OPCPackage oPackage = OPCPackage.open(is);
            XWPFDocument document = new XWPFDocument(oPackage);) {
        TokenProvider iterator = new TokenProvider(document);
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        iterator.next().getRun();
        assertNull(iterator.lookAhead(1));
    }
}
 

/**
 * 遍历工作簿中所有的电子表格
 * @param filename
 * @throws Exception
 */
public void process(String filename) throws Exception {
	OPCPackage pkg = OPCPackage.open(filename);
	XSSFReader r = new XSSFReader(pkg);
	SharedStringsTable sst = r.getSharedStringsTable();
	XMLReader parser = fetchSheetParser(sst);
	Iterator<InputStream> sheets = r.getSheetsData();
	while (sheets.hasNext()) {
		curRow = 0;
		sheetIndex++;
		InputStream sheet = sheets.next();
		InputSource sheetSource = new InputSource(sheet);
		parser.parse(sheetSource);
		sheet.close();
	}
}
 

@Test
public void testNonEmptyDoc() throws InvalidFormatException, IOException {
    try (FileInputStream is = new FileInputStream("resources/document/notEmpty/notEmpty-template.docx");
            OPCPackage oPackage = OPCPackage.open(is);
            XWPFDocument document = new XWPFDocument(oPackage);) {
        TokenIterator iterator = new TokenIterator(document);
        XWPFRun run = iterator.next().getRun();
        assertEquals("P1Run1 ", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals("P1Run2", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals(" P1Run3", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals("P2Run1 ", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals("P2Run2", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals(" ", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals("P2Run3", run.getText(run.getTextPosition()));
        run = iterator.next().getRun();
        assertEquals("", run.getText(run.getTextPosition()));
        assertTrue(!iterator.hasNext());
    }
}
 

@Test
public void Encryption() throws Exception {
    String file = TestFileUtil.getPath() + "large" + File.separator + "large07.xlsx";
    POIFSFileSystem fs = new POIFSFileSystem();
    EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
    Encryptor enc = info.getEncryptor();
    enc.confirmPassword("foobaa");
    OPCPackage opc = OPCPackage.open(new File(file), PackageAccess.READ_WRITE);
    OutputStream os = enc.getDataStream(fs);
    opc.save(os);
    opc.close();

    // Write out the encrypted version
    FileOutputStream fos = new FileOutputStream("D:\\test\\99999999999.xlsx");
    fs.writeFilesystem(fos);
    fos.close();
    fs.close();

}
 

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

/**
 * 遍历工作簿中所有的电子表格
 * @param filename
 * @param fileKey 
 * @throws Exception
 */
public void process( String filename ) throws Exception {
	OPCPackage pkg = OPCPackage.open(filename);
	XSSFReader r = new XSSFReader(pkg);
	SharedStringsTable sst = r.getSharedStringsTable();
	XMLReader parser = fetchSheetParser(sst);
	Iterator<InputStream> sheets = r.getSheetsData();
	while (sheets.hasNext()) {
		curRow = 0;
		sheetIndex++;
		InputStream sheet = sheets.next();
		InputSource sheetSource = new InputSource(sheet);
		parser.parse( sheetSource );
		sheet.close();
	}
	//数据读取完成
}
 

/**
 * Get OPCPackage from template file.
 * 
 * @param uriConverter
 *            the {@link URIConverter uri converter} to use.
 * @param templateURI
 *            the template {@link URI}
 * @return OPCPackage
 * @throws IOException
 *             IOException
 */
private OPCPackage getOPCPackage(URIConverter uriConverter, URI templateURI) throws IOException {
    OPCPackage oPackage;
    try (InputStream is = uriConverter.createInputStream(templateURI)) {
        try {
            oPackage = OPCPackage.open(is);

        } catch (InvalidFormatException e) {
            throw new IllegalArgumentException("Couldn't open template file", e);
        }
    }
    return oPackage;
}
 

private static OPCPackage getOPCPackage(File file) throws Exception {
  if (null == file || !file.canRead()) {
    throw new Exception("File object is null or cannot have read permission");
  }

  return OPCPackage.open(file, PackageAccess.READ);
}
 

/**
 * Parses a document for {@link UserContent} and returns the {@link DocumentTemplate} resulting from
 * this parsing.
 * 
 * @param uriConverter
 *            the {@link URIConverter uri converter} to use.
 * @param documentURI
 *            URI for the document
 * @param queryEnvironment
 *            the {@link IQueryEnvironment}
 * @return the {@link DocumentTemplate} resulting from parsing the specified
 *         document
 * @throws DocumentParserException
 *             if a problem occurs while parsing the document.
 */
@SuppressWarnings("resource")
public static DocumentTemplate parseUserContent(URIConverter uriConverter, URI documentURI,
        IQueryEnvironment queryEnvironment) throws DocumentParserException {
    final DocumentTemplate result = (DocumentTemplate) EcoreUtil.create(TemplatePackage.Literals.DOCUMENT_TEMPLATE);
    final ResourceImpl r = new ResourceImpl(documentURI);

    try {
        // resources are closed in DocumentTemplate.close()
        final InputStream is = uriConverter.createInputStream(documentURI);
        final OPCPackage oPackage = OPCPackage.open(is);
        final XWPFDocument document = new XWPFDocument(oPackage);
        r.getContents().add(result);
        final BodyGeneratedParser parser = new BodyGeneratedParser(document, queryEnvironment);
        result.setBody(parser.parseBlock(null));
        result.setInputStream(is);
        result.setOpcPackage(oPackage);
        result.setDocument(document);
        for (XWPFFooter footer : document.getFooterList()) {
            final BodyGeneratedParser footerParser = new BodyGeneratedParser(footer, queryEnvironment);
            result.getFooters().add(footerParser.parseBlock(null));
        }
        for (XWPFHeader header : document.getHeaderList()) {
            final BodyGeneratedParser headerParser = new BodyGeneratedParser(header, queryEnvironment);
            result.getHeaders().add(headerParser.parseBlock(null));
        }

    } catch (IOException e) {
        throw new DocumentParserException("Unable to open " + documentURI, e);
    } catch (InvalidFormatException e1) {
        throw new DocumentParserException("Invalid .docx format " + documentURI, e1);
    }

    return result;
}
 

/**
 * Create instance
 * @param inputStream sheet input stream
 * @param pluginConfig config options
 * @param writer {@link VectorContainerWriter} for writing values into vectors.
 * @param managedBuf Workspace buffer.
 * @param maxCellSize maximum allowable size of variable length cells
 */
public StAXBasedParser(final InputStream inputStream, final ExcelFormatPluginConfig pluginConfig,
                       final VectorContainerWriter writer, final ArrowBuf managedBuf,
                       final HashSet<String> columnsToProject, final int maxCellSize) throws Exception {
  pkgInputStream = OPCPackage.open(inputStream);
  this.writer = writer.rootAsStruct();
  this.managedBuf = managedBuf;
  this.maxCellSize = maxCellSize;

  final XSSFReader xssfReader = new XSSFReader(pkgInputStream);

  // Find the sheet id of the given sheet name in workbook
  try (final InputStream wbInputStream = xssfReader.getWorkbookData()) {
    final String sheetId = ExcelUtil.getSheetId(wbInputStream, pluginConfig.sheet);
    if (sheetId == null) {
      throw new SheetNotFoundException();
    }

    // Open the InputStream for sheet
    sheetInputStream = xssfReader.getSheet(sheetId);
  }


  // WARNING: XSSFReader can actually return null instances of sst and styles
  sst = xssfReader.getSharedStringsTable();
  styles = checkNotNull(xssfReader.getStylesTable(), "Expected a valid styles table instance");

  init(pluginConfig.extractHeader, pluginConfig.hasMergedCells);

  this.columnsToProject = columnsToProject;
}
 

/**只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3
 * @param filename
 * @param sheetId
 * @throws Exception
 */
public void processOneSheet(String filename,int sheetId) throws Exception {
	OPCPackage pkg = OPCPackage.open(filename);
	XSSFReader r = new XSSFReader(pkg);
	SharedStringsTable sst = r.getSharedStringsTable();
	XMLReader parser = fetchSheetParser(sst);
	
	// 根据 rId# 或 rSheet# 查找sheet
	InputStream sheet2 = r.getSheet( "rId"+sheetId);
	sheetIndex++;
	InputSource sheetSource = new InputSource(sheet2);
	parser.parse(sheetSource);
	sheet2.close();
}
 

public <T> List<T> readExcel(InputStream inputstream, Class<?> pojoClass, ImportParams params, ISaxRowRead rowRead, IExcelReadRowHanlder hanlder) {
	try {
		OPCPackage opcPackage = OPCPackage.open(inputstream);
		return readExcel(opcPackage, pojoClass, params, rowRead, hanlder);
	} catch (Exception e) {
		LOGGER.error(e.getMessage(), e);
		throw new ExcelImportException(e.getMessage());
	}
}
 

/**
 * 加密导出
 *
 * @param workbook workbook
 * @param fileName fileName
 * @param response response
 * @param password password
 */
public static void encryptExport(final Workbook workbook, String fileName, HttpServletResponse response, final String password) {
    if (workbook instanceof HSSFWorkbook) {
        throw new IllegalArgumentException("Document encryption for.xls is not supported");
    }
    Path path = null;
    try {
        String suffix = Constants.XLSX;
        path = TempFileOperator.createTempFile("encrypt_temp", suffix);
        workbook.write(Files.newOutputStream(path));

        final POIFSFileSystem fs = new POIFSFileSystem();
        final EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
        final Encryptor enc = info.getEncryptor();
        enc.confirmPassword(password);

        try (OPCPackage opc = OPCPackage.open(path.toFile(), PackageAccess.READ_WRITE);
             OutputStream os = enc.getDataStream(fs)) {
            opc.save(os);
        }
        if (!fileName.endsWith(suffix)) {
            fileName += suffix;
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        setAttachmentConfig(fileName, response);
        fs.writeFilesystem(response.getOutputStream());
    } catch (IOException | InvalidFormatException | GeneralSecurityException e) {
        throw new RuntimeException(e);
    } finally {
        clear(workbook);
        TempFileOperator.deleteTempFile(path);
    }
}
 

/**
 * Verifies a bug where BufferedStringsTable was dropping text enclosed in formatting
 * instructions.
 */
@Test
public void testStringsWrappedInFormatting() throws Exception {
  File file = new File("src/test/resources/shared_styled_string.xlsx");
  File sstCache = File.createTempFile("cache", ".sst");
  sstCache.deleteOnExit();
  try (OPCPackage pkg = OPCPackage.open(file, PackageAccess.READ);
       BufferedStringsTable sst = BufferedStringsTable.getSharedStringsTable(sstCache, 1000, pkg)) {
    assertNotNull(sst);
    assertEquals("shared styled string", sst.getItemAt(0).getString());
  }
}
 

/**
 * 遍历工作簿中所有的电子表格
 * 
 * @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();
       }
}
 

@Override
protected void transformInternal(ContentReader reader,
                                 ContentWriter writer,
                                 TransformationOptions options) throws Exception
{
    final String sourceMimetype = reader.getMimetype();
    final String sourceExtension = getMimetypeService().getExtension(sourceMimetype);
    final String targetMimetype = writer.getMimetype();
    
    
    if (log.isDebugEnabled())
    {
        StringBuilder msg = new StringBuilder();
        msg.append("Transforming from ").append(sourceMimetype)
           .append(" to ").append(targetMimetype);
        log.debug(msg.toString());
    }
    
    
    OPCPackage pkg = null;
    try 
    {
        File ooxmlTempFile = TempFileProvider.createTempFile(this.getClass().getSimpleName() + "_ooxml", sourceExtension);
        reader.getContent(ooxmlTempFile);
        
        // Load the file
        pkg = OPCPackage.open(ooxmlTempFile.getPath());
        
        // Does it have a thumbnail?
        PackageRelationshipCollection rels = 
            pkg.getRelationshipsByType(PackageRelationshipTypes.THUMBNAIL);
        if (rels.size() > 0)
        {
            // Get the thumbnail part
            PackageRelationship tRel = rels.getRelationship(0);
            PackagePart tPart = pkg.getPart(tRel);
            
            // Write it to the target
            InputStream tStream = tPart.getInputStream();
            writer.putContent( tStream );
            tStream.close();
        }
        else
        {
            log.debug("No thumbnail present in " + reader.toString());
            throw new UnimportantTransformException(NO_THUMBNAIL_PRESENT_IN_FILE + targetMimetype);
        }
    } 
    catch (IOException e) 
    {
       throw new AlfrescoRuntimeException("Unable to transform " + sourceExtension + " file.", e);
    }
    finally
    {
        if (pkg != null)
        {
            pkg.close();
        }
    }
}
 

public XLSX2CSV(String inputFilePath, String outputFilePath) throws Exception {
    xlsxPackage = OPCPackage.open(inputFilePath, PackageAccess.READ);
    output = new PrintStream(outputFilePath, OUTPUT_CHARSET);
    minColumns = -1;
}
 

/**
 * @param args
 * @throws Exception
 */
public static void main(String[] args) throws Exception {
  String SAMPLE_PERSON_DATA_FILE_PATH = "src/test/resources/Sample-Person-Data.xlsx";

  // Input File initialize
  File file = new File(SAMPLE_PERSON_DATA_FILE_PATH);
  InputStream inputStream = new FileInputStream(file);

  // Excel Cell Mapping
  Map<String, String> cellMapping = new HashMap<String, String>();
  cellMapping.put("HEADER", "Person Id,Name,Height,Email Address,DOB,Salary");
  cellMapping.put("A", "personId");
  cellMapping.put("B", "name");
  cellMapping.put("C", "height");
  cellMapping.put("D", "emailId");
  cellMapping.put("E", "dob");
  cellMapping.put("F", "salary");

  // The package open is instantaneous, as it should be.
  OPCPackage pkg = null;
  try {

    ExcelWorkSheetHandler<PersonVO> workSheetHandler =
        new ExcelWorkSheetHandler<PersonVO>(PersonVO.class, cellMapping);

    pkg = OPCPackage.open(inputStream);

    ExcelSheetCallback sheetCallback = new ExcelSheetCallback() {
      private int sheetNumber = 0;

      @Override
      public void startSheet(int sheetNum, String sheetName) {
        this.sheetNumber = sheetNum;
        System.out.println("Started processing sheet number=" + sheetNumber
            + " and Sheet Name is '" + sheetName + "'");
      }

      @Override
      public void endSheet() {
        System.out.println("Processing completed for sheet number=" + sheetNumber);
      }
    };

    System.out.println("Constructor: pkg, workSheetHandler, sheetCallback");
    ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback);
    example1.process();

    if (workSheetHandler.getValueList().isEmpty()) {
      // No data present
      LOG.error("sHandler.getValueList() is empty");
    } else {

      LOG.info(workSheetHandler.getValueList().size()
          + " no. of records read from given excel worksheet successfully.");

      // Displaying data ead from Excel file
      displayPersonList(workSheetHandler.getValueList());
    }

    System.out.println("\nConstructor: filePath, workSheetHandler, sheetCallback");
    ExcelReader example2 =
        new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, workSheetHandler, sheetCallback);
    example2.process();

    System.out.println("\nConstructor: file, workSheetHandler, sheetCallback");
    ExcelReader example3 = new ExcelReader(file, workSheetHandler, null);
    example3.process();

  } catch (RuntimeException are) {
    LOG.error(are.getMessage(), are.getCause());
  } catch (InvalidFormatException ife) {
    LOG.error(ife.getMessage(), ife.getCause());
  } catch (IOException ioe) {
    LOG.error(ioe.getMessage(), ioe.getCause());
  } finally {
    IOUtils.closeQuietly(inputStream);
    try {
      if (null != pkg) {
        pkg.close();
      }
    } catch (IOException e) {
      // just ignore IO exception
    }
  }
}