Apache poi:使用 apache poi 在工作表级别设置自定义属性

问题描述

public static void main(String[] args) {
  try {
    FileInputStream file = new FileInputStream(new File("D://New Microsoft Excel Worksheet.xlsx"));
    XSSFWorkbook wb = new XSSFWorkbook(file);
    XSSFSheet sheet = wb.createSheet("newsheet5");
    CTWorksheet ctSheet = sheet.getCTWorksheet();

    CTCustomProperties props = ctSheet.addNewCustomProperties();
    props.addNewCustomPr().setId("APACHE POI");
    props.addNewCustomPr().setName("Tender no = 48");
    props.addNewCustomPr().setId("APACHE POI 2");
    props.addNewCustomPr().setName("tender no = 58");
    ctSheet.setCustomProperties(props);

    FileOutputStream out = new FileOutputStream("D://New Microsoft Excel Worksheet.xlsx");
    wb.write(out);
    out.close();
    wb.close();
  } catch (Exception e) {
    e.printStackTrace();
  }
}

在工作表级别写入自定义属性后 Xlsx 文件已损坏.

Xlsx file is corrupted after writing custom properties at sheet level.

我收到一条错误消息，因为excel 无法打开文件，因为文件格式或文件扩展名无效".尝试打开excel文件时确认文件没有损坏并且文件扩展名与文件格式匹配.

I'm getting an error message as 'excel cannot open the file because the file format or file extension is not valid . Vefiry that the file has not been corrupted and the file extension matches the format of the file' when tried open the excel file.

推荐答案

只有使用 VBA 才能使用图纸自定义属性.它们存储在 Excel 文件中，但值位于二进制文档部分 customProperty1.bin、customProperty2.bin、...这没什么到目前为止，apache poi 提供了哪些访问权限.

Sheet custom properties only are useable using VBA. They are stored in the Excel file but the values are within binary document parts customProperty1.bin, customProperty2.bin, ... This is nothing what apache poi provides access to until now.

使用 XSSF 需要创建二进制文档部分，然后获取与该二进制文档部分的关系 Id.然后设置CTCustomProperties - CTCustomProperty.Id 指向包含值的二进制文档部分，名称是属性名称.

Using XSSF one needs creating the binary document part, then getting the relation Id to that binary document part. Then set CTCustomProperties - CTCustomProperty. There the Id points to the binary document part containing the value and the name is the property name.

以下完整示例显示了这一点.它使用当前的 apache poi 4.1.2 进行了测试和工作.它需要 ooxml-schemas-1.4.jar 在类路径中，因为默认 poi-ooxml-schemas-4.1.2.jar 不包含所有需要的低级 CT*-类.

Following complete example shows this. It is tested and works using current apache poi 4.1.2. It needs ooxml-schemas-1.4.jar being in class path because default poi-ooxml-schemas-4.1.2.jar does not contain all needed low level CT*-classes.

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.*;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;

import java.nio.charset.StandardCharsets;

class CreateExcelSheetCustomProperties {

 static void setSheetCustomProperty(XSSFSheet sheet, String customPropertyName, String customPropertyValue) throws Exception {

  OPCPackage opcpackage = sheet.getWorkbook().getPackage();
  int i = opcpackage.getUnusedPartIndex("/customProperty#.bin");
  PackagePartName partname = PackagingURIHelper.createPartName("/customProperty" + i + ".bin");
  PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.customProperty");
  POIXMLDocumentPart customProperty = new POIXMLDocumentPart(part) {
   @Override
   protected void commit() throws IOException {
    PackagePart part = getPackagePart();
    OutputStream out = part.getOutputStream();
    try {
     out.write(customPropertyValue.getBytes(StandardCharsets.UTF_16LE));
     out.close();
    } catch (Exception ex) {
     ex.printStackTrace();
    };
   }
  };

  String rId = sheet.addRelation(null, XSSFRelation.CUSTOM_PROPERTIES, customProperty).getRelationship().getId();

  CTWorksheet ctSheet = sheet.getCTWorksheet();
  CTCustomProperties props = ctSheet.getCustomProperties();
  if (props == null) props = ctSheet.addNewCustomProperties();
  CTCustomProperty prop = props.addNewCustomPr();
  prop.setId(rId);
  prop.setName(customPropertyName);
 }

 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {

   XSSFSheet sheet = workbook.createSheet();

   setSheetCustomProperty(sheet, "APACHE POI", "Tender no = 48");
   setSheetCustomProperty(sheet, "APACHE POI 2", "tender no = 58");

   workbook.write(fileout);
  }
 }
}

这篇关于Apache poi:使用 apache poi 在工作表级别设置自定义属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！