社区首页 >问答首页 >Apache poi :使用apache poi在工作表级别设置自定义属性

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

Stack Overflow用户

提问于 2020-12-16 09:21:49

回答 2查看 437关注 0票数 0

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文件已损坏。

当我尝试打开excel文件时，我收到一条错误消息：“excel无法打开该文件，因为文件格式或文件扩展名无效。请确认该文件尚未损坏，并且文件扩展名与该文件的格式匹配。”

java

excel

apache-poi

回答 2

Stack Overflow用户

发布于 2020-12-17 07:26:30

只有在使用VBA时才能使用图纸自定义特性。它们存储在Excel文件中，但是值在二进制文档部分customProperty1.bin、customProperty2.bin、...到目前为止，这还不是apache poi提供的访问权限。

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

下面的完整示例显示了这一点。它使用当前的apache poi 4.1.2进行了测试和工作。它需要ooxml-schemas-1.4.jar在类路径中，因为默认的poi-ooxml-schemas-4.1.2.jar不包含所有需要的低级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);
  }
 }
}

票数 1

Stack Overflow用户

发布于 2020-12-16 09:39:14

我一直在努力解决同样的问题，并找到了一种方法来使其工作，但它远远不是最优的。不管怎样，这就是它，希望你或其他人能想出更好的方法。

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