Apache Poi:可以访问工作表级别的自定义属性吗?

问题描述

是否也可以通过 POI 访问工作表级别的自定义属性(在 VBA 中作为 Worksheet.CustomProperties 访问)?

Can custom properties on worksheet level (in VBA accessed as Worksheet.CustomProperties) also be accessed via POI?

推荐答案

Jason - 我一直在为同样的问题而苦苦挣扎，并找到了使其工作的方法，但它远非最佳.无论如何都在这里，希望您或其他人可以想出更好的方法.

Jason - I've been struggling with the same issue and found a way to make it work, but it's far from optimal. Here it is anyway and hopefully you or someone else can come up with a better method.

package temp.temp;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomProperties;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomProperty;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

public class Temp2 {

    public static void main(String[] args) {
        File inputFile = new File("C:\\myspreadsheet.xlsx");
        try (BufferedInputStream fis = new BufferedInputStream(new FileInputStream(inputFile))) {
            XSSFWorkbook wb = new XSSFWorkbook(fis);

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                XSSFSheet sheet  = wb.getSheetAt(i);
                System.out.println("\nSheetName=" + sheet.getSheetName());

                CTWorksheet ctSheet = sheet.getCTWorksheet();
                CTCustomProperties props = ctSheet.getCustomProperties();

                if (props != null) {
                    List<CTCustomProperty> propList = props.getCustomPrList();
                    propList.stream().forEach((prop) -> {
                        POIXMLDocumentPart rel = sheet.getRelationById(prop.getId());
                        if (rel != null) {
                            try (InputStream inp  = rel.getPackagePart().getInputStream()) {
                                byte[] inBytes = inp.readAllBytes();
                                // By experimentation, byte array has two bytes per character with least
                                //  significant in first byte which is UTF-16LE encoding.  Don't know why!
                                String value = new String(inBytes, "UTF-16LE");
                                System.out.println("   " + prop.getName() + "=" + value);
                            } catch (IOException ioe) {
                                //Error
                            }
                        }
                    });
                }


            }
            wb.close();
        } catch (Exception e) {
            System.out.println(e);
        }
        System.out.println("End");
    }
}

请注意，CTWorksheet 来自 poi-ooxml-schemas-xx.jar 和 CustomProperties 来自 ooxml-schemas-yy.jar，因此两者都必须在类路径上.如果你正在使用模块(就像我一样)，这会带来很大的问题！祝你好运

Note that CTWorksheet comes from poi-ooxml-schemas-xx.jar and CustomProperties from ooxml-schemas-yy.jar, so both have to be on the classpath. If you're using modules (as I am), this gives big problems! Good Luck

这篇关于Apache Poi:可以访问工作表级别的自定义属性吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！