org.apache.poi.poifs.filesystem.POIFSFileSystem Java Examples

protected static void cleanupAndSaveExcel97(POIFSFileSystem fs, OutputStream outputStream)
    throws IOException {
  Workbook wb = WorkbookFactory.create(fs);

  if (wb instanceof HSSFWorkbook) {
    HSSFWorkbook hwb = (HSSFWorkbook) wb;
    InternalWorkbook internal = hwb.getInternalWorkbook();
    if (internal != null) {
      LOGGER.trace("# of Records: {}", internal.getNumRecords());
      removeObProjRecord(internal.getRecords());
      LOGGER.trace("# of Records: {}", internal.getNumRecords());
    }
  }

  wb.write(outputStream);
}
 

public static final Workbook newInstance(String type, boolean isSave) throws Exception {
    File f = new File(type);
    if (isSave) {
        if (type.equals("xls")) {
            return new HSSFWorkbook();
        }
        return new XSSFWorkbook();
    }
    if (!f.isFile()) {
        throw new Exception("请填写正确路径");
    }
    type = type.substring(type.lastIndexOf(".") + 1);
    if (type.equals("xls")) {
        return new HSSFWorkbook(new POIFSFileSystem(f));
    }
    return new XSSFWorkbook(f);
}
 

@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 static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

/**
 * 读取非文档类的Ole对象
 * 
 * @param poifs
 * @return
 */
private WcOleObject readOle10Native(POIFSFileSystem poifs) {
	WcOleObject oleObject = new WcOleObject();
	try {
		Ole10Native ole10 = Ole10Native.createFromEmbeddedOleObject(poifs);
		oleObject.setFileName(FilenameUtils.getName(ole10.getFileName()));

		// byte[] data = IOUtils.toByteArray(packagePart.getInputStream());
		oleObject.setDataSize(ole10.getDataSize());
		oleObject.setData(ole10.getDataBuffer());
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
	}

	return oleObject;
}
 

private POIFSFileSystem openFileSystemAndEncrypt(File file) throws Exception {
    POIFSFileSystem fileSystem = new POIFSFileSystem();
    Encryptor encryptor = new EncryptionInfo(EncryptionMode.standard).getEncryptor();
    encryptor.confirmPassword(writeWorkbookHolder.getPassword());
    OPCPackage opcPackage = null;
    try {
        opcPackage = OPCPackage.open(file, PackageAccess.READ_WRITE);
        OutputStream outputStream = encryptor.getDataStream(fileSystem);
        opcPackage.save(outputStream);
    } finally {
        if (opcPackage != null) {
            opcPackage.close();
        }
    }
    return fileSystem;
}
 

/**
 * {@inheritDoc}
 */
@Override public ProjectFile read(InputStream is) throws MPXJException
{

   try
   {

      //
      // Open the file system
      //
      POIFSFileSystem fs = new POIFSFileSystem(is);

      return read(fs);

   }
   catch (IOException ex)
   {

      throw new MPXJException(MPXJException.READ_ERROR, ex);

   }
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( String fileName ) throws IOException {
	
	this.fs = new POIFSFileSystem( new FileInputStream(fileName) );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( String fileName ) throws IOException {
	
	this.fs = new POIFSFileSystem( new FileInputStream(fileName) );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( String fileName ) throws IOException {
	
	this.fs = new POIFSFileSystem( new FileInputStream(fileName) );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

public static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( String fileName ) throws IOException {
	
	this.fs = new POIFSFileSystem( new FileInputStream(fileName) );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process(String fileName ) throws IOException {
	
	this.fs = new POIFSFileSystem(new FileInputStream(fileName));
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
}
 

private String microsoftWordDocumentToString(InputStream inputStream) throws IOException {
    String strRet;

    try (InputStream wordStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(wordStream)) {
            WordExtractor wordExtractor = new WordExtractor(wordStream);
            strRet = wordExtractor.getText();
            wordExtractor.close();
        } else {
            XWPFWordExtractor wordXExtractor = new XWPFWordExtractor(new XWPFDocument(wordStream));
            strRet = wordXExtractor.getText();
            wordXExtractor.close();
        }
    }

    return strRet;
}
 

private List<Session> readAllSessions(InputStream is) {
    final List<Session> result = new ArrayList<Session>();

    try {
        final POIFSFileSystem fileSystem = new POIFSFileSystem(is);
        final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        final HSSFSheet sheet = workBook.getSheet("Alle Tage");

        int rows = sheet.getPhysicalNumberOfRows();
        // as the row is a header we start with the second one
        for (int r = 1; r < rows; r++) {
            final HSSFRow row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            final Session session = getSessionFromRow(row, r);
            if (session != null) {
                result.add(session);
            }
        }
    } catch (Exception e) {
        throw new RuntimeException("Error while reading sessions from file", e);
    }
    return result;
}
 

/**
 * 遍历excel下所有的sheet
 * @param fileKey 
 * @throws IOException
 */
public void process( InputStream is ) throws IOException {
	this.fs = new POIFSFileSystem( is );
	MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this);
	formatListener = new FormatTrackingHSSFListener(listener);
	HSSFEventFactory factory = new HSSFEventFactory();
	HSSFRequest request = new HSSFRequest();
	if ( outputFormulaValues ) {
		request.addListenerForAllRecords( formatListener );
	} else {
		workbookBuildingListener = new SheetRecordCollectingListener( formatListener);
		request.addListenerForAllRecords(workbookBuildingListener);
	}
	factory.processWorkbookEvents(request, fs);
	//数据读取完成
	
}
 

/**
 * Adds an OLE package manager object with the given POIFS to the sheet
 *
 * @param poiData an POIFS containing the embedded document, to be added
 * @param label the label of the payload
 * @param fileName the original filename
 * @param command the command to open the payload
 * @return the index of the added ole object
 * @throws IOException if the object can't be embedded
 */
public int addOlePackage(POIFSFileSystem poiData, String label, String fileName, String command)
throws IOException {
	DirectoryNode root = poiData.getRoot();
	Map<String,ClassID> olemap = getOleMap();
	for (Map.Entry<String,ClassID> entry : olemap.entrySet()) {
		if (root.hasEntry(entry.getKey())) {
			root.setStorageClsid(entry.getValue());
			break;
		}
	}

	ByteArrayOutputStream bos = new ByteArrayOutputStream();
	poiData.writeFilesystem(bos);
    return addOlePackage(bos.toByteArray(), label, fileName, command);
}
 

@Override
public void sanitize(InputStream inputStream, OutputStream outputStream, BleachSession session)
    throws BleachException {
  try (POIFSFileSystem fsIn = new POIFSFileSystem(inputStream);
      POIFSFileSystem fs = new POIFSFileSystem()) {
    // @TODO: Filter based on Storage Class ID - see issue #23
    sanitize(session, fsIn, fs);

    if (ClassID.EXCEL97.equals(fs.getRoot().getStorageClsid())) {
      ExcelRecordCleaner.cleanupAndSaveExcel97(fs, outputStream);
    } else {
      fs.writeFilesystem(outputStream);
    }
  } catch (IOException | IndexOutOfBoundsException e) {
    throw new BleachException(e);
  }
}
 

/**
 * 加密导出
 *
 * @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);
    }
}
 

public static void exportCsv ( HttpServletResponse response , String fileName ,
                               LinkedHashMap< String, String > titleMap , List< ? > dataList ) throws IOException {
    String content = convertCsv( titleMap , dataList );

    // 导出 csv
    setResponse( response , "csv" , fileName );
    response.getOutputStream().write( content.getBytes( StandardCharsets.UTF_8 ) );

    POIFSFileSystem poifsFileSystem = new POIFSFileSystem();
    poifsFileSystem.createDocument( new ByteArrayInputStream( content.getBytes( "GBK" ) ) , "WordDocument" );
    poifsFileSystem.writeFilesystem( response.getOutputStream() );
}
 

public static void reader(String filePath) {
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell((short) 0);
		int type = cell.getCellType();
		String msg = getCellStringValue(cell);
		System.out.println(type + ":" + msg);
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

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 void signEncryptedPackage(InputStream tmpFileInputStream, SignatureConfig sc, String password) throws IOException, InvalidFormatException, FormatNotUnderstoodException, XMLSignatureException, MarshalException {

	POIFSFileSystem poifsTemp = new POIFSFileSystem(tmpFileInputStream);
	EncryptionInfo info = new EncryptionInfo(poifsTemp);
	Decryptor d = Decryptor.getInstance(info);

	try {
		if (!d.verifyPassword(password)) {
			throw new FormatNotUnderstoodException("Error: Cannot decrypt new Excel file (.xlsx) for signing. Invalid password");
		}
		// signing
		OPCPackage pkg = OPCPackage.open(d.getDataStream(poifsTemp));
		sc.setOpcPackage(pkg);
		
		SignatureInfo si = new SignatureInfo();
		si.setSignatureConfig(sc);
		si.confirmSignature();
		// encrypt again
		Encryptor enc = info.getEncryptor();
		enc.confirmPassword(password);
		POIFSFileSystem poifs = new POIFSFileSystem();
		OutputStream os = enc.getDataStream(poifs);
		pkg.save(os);
		pkg.close();
		if (os!=null) {
			os.close();
		}
		poifs.writeFilesystem(this.finalOutputStream);
		if (poifs!=null) {
			poifs.close();
		}
		if (poifsTemp!=null) {
			poifsTemp.close();
		}
	} catch (GeneralSecurityException e) {
		
		LOG.error(e);
		throw new FormatNotUnderstoodException("Error: Cannot decrypt new Excel file (.xlsx)  for signing.");
	} 
}
 

/**
 * This method allows us to peek into the OLE compound document to extract the file format.
 * This allows the UniversalProjectReader to determine if this is an MPP file, or if
 * it is another type of OLE compound document.
 *
 * @param fs POIFSFileSystem instance
 * @return file format name
 * @throws IOException
 */
public static String getFileFormat(POIFSFileSystem fs) throws IOException
{
   String fileFormat = "";
   DirectoryEntry root = fs.getRoot();
   if (root.getEntryNames().contains("\1CompObj"))
   {
      CompObj compObj = new CompObj(new DocumentInputStream((DocumentEntry) root.getEntry("\1CompObj")));
      fileFormat = compObj.getFileFormat();
   }
   return fileFormat;
}
 

public HSSFSaxReadHandler(InputStream inputStream,
                          List<T> result,
                          SaxExcelReader.ReadConfig<T> readConfig) throws IOException {
    super(false, result, readConfig);
    this.fs = new POIFSFileSystem(inputStream);
    this.sheetIndexs = readConfig.getSheetIndexs();
}