java - 如何使用 Java 中的 Apache POI 从/在 excel 中读取/写入 XML 映射？

要从现有工作簿中读取 XML 映射，可以使用一些API方法。

有XSSFWorkbook.getMapInfo获取MapInfo。还有XSSFWorkbook.getCustomXMLMappings获得List所有XSSFMap。所以阅读应该不是问题。

但到目前为止，还没有什么可以创建新的MapInfo和/或在其中添加其他模式和/或映射的MapInfo. 因此，有必要使用低级底层对象创建具有 XML 映射的新工作簿。

以下完整示例显示了这一点。它提供了创建MapInfo和添加模式和映射的方法。它使用以下class.xsd文件作为架构定义：

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="class">
  <xs:complexType>
   <xs:sequence>
    <xs:element maxOccurs="unbounded" name="student">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="rollno" type="xs:string"/>
       <xs:element name="firstname" type="xs:string"/>
       <xs:element name="lastname" type="xs:string"/>
       <xs:element name="nickname" type="xs:string"/>
       <xs:element name="marks" type="xs:string"/>
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

它创建一个MapInfo，添加架构和地图并创建一个XSSFMap. 然后它会创建一个XSSFTable引用地图的 in first scheet。因此，可以在该表中收集数据以导出XML。

代码：

import java.io.InputStream;
import java.io.OutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ooxml.POIXMLRelation;
import static org.apache.poi.ooxml.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;

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

import org.apache.xmlbeans.XmlOptions;

import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.SpreadsheetVersion;

import org.apache.poi.xssf.model.MapInfo;
import org.apache.poi.xssf.usermodel.*;

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

import javax.xml.namespace.QName;

import java.util.List;

public class CreateExcelWithXmlMap {

 private static CTMap addMap(MapInfo mapInfo, String mapName, String rootElement, String schemaId) {
  CTMapInfo cTMapInfo = mapInfo.getCTMapInfo();
  long id = 1;
  for (CTMap map : cTMapInfo.getMapList()) {
   if (map.getID() >= id) id = map.getID() + 1;
  }
  CTMap map = cTMapInfo.addNewMap();
  map.setID(id);
  map.setName(mapName);
  map.setRootElement(rootElement);
  map.setSchemaID(schemaId);
  map.setAutoFit(true);
  map.setAppend(false);
  map.setPreserveSortAFLayout(true);
  map.setPreserveFormat(true);
  return map;
 }

 private static int addSchema(MapInfo mapInfo, InputStream schemaIn, String schemaIdPrefix) throws Exception {
  CTMapInfo cTMapInfo = mapInfo.getCTMapInfo();
  List<CTSchema> schemas = cTMapInfo.getSchemaList();
  CTSchema[] schemaArray = new CTSchema[schemas.size()+1];
  int i = 0;
  int id = 1;
  for (CTSchema schema : schemas) {
   schemaArray[i++] = schema;
   if (schema.getID().startsWith(schemaIdPrefix)) id++;
  }
  CTSchema schema = CTSchema.Factory.parse(schemaIn);
  schema.setID(schemaIdPrefix + id);
  schemaArray[i] = schema;
  cTMapInfo.setSchemaArray(schemaArray);
  return id;
 }

 private static void writeMapInfoMinContent(PackagePart part) throws Exception {
  CTMapInfo cTMapInfo = MapInfoDocument.Factory.newInstance().addNewMapInfo();
  cTMapInfo.setSelectionNamespaces("");
  XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
  xmlOptions.setSaveSyntheticDocumentElement(new QName(CTMapInfo.type.getName().getNamespaceURI(), "MapInfo"));
  OutputStream out = part.getOutputStream();
  cTMapInfo.save(out, xmlOptions);
  out.close();
 }

 private static MapInfo createMapInfo(XSSFWorkbook workbook) throws Exception {
  MapInfo mapInfo = workbook.getMapInfo();
  if (mapInfo != null) {
   return mapInfo;
  } else {
   OPCPackage oPCPackage = workbook.getPackage();
   PackagePartName partName = PackagingURIHelper.createPartName("/xl/xmlMaps.xml");
   PackagePart part = oPCPackage.createPart(partName, "application/xml");
   writeMapInfoMinContent(part);
   mapInfo = new MapInfo(part);
   String rId = workbook.addRelation(null, new XSSFXmlMapsRelation(), mapInfo).getRelationship().getId();
  }
  return mapInfo;
 }

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

  String schemaFilePath = "./class.xsd";
  String workbookFilePath = "./ExcelWithXMLMap.xlsx";

  XSSFWorkbook workbook = new XSSFWorkbook();

  MapInfo mapInfo = createMapInfo(workbook);
  InputStream schemaIn = new FileInputStream(schemaFilePath);
  int schemaId = addSchema(mapInfo, schemaIn, "Schema");
  CTMap cTMap = addMap(mapInfo, "class_Map", "class", "Schema"+schemaId);
  XSSFMap xssfMap = new XSSFMap(cTMap, mapInfo);
  //ToDo: update private Map<Integer, XSSFMap> maps in MapInfo 

  String[] headers = new String[]{"rollno", "firstname", "lastname", "nickname", "marks"};
  //ToDo: get headers from schema

  XSSFSheet sheet = workbook.createSheet();
  XSSFTable table = sheet.createTable(new AreaReference("A1:E2", SpreadsheetVersion.EXCEL2007));
  table.setDisplayName("class");
  table.getCTTable().addNewTableStyleInfo();
  XSSFTableStyleInfo style = (XSSFTableStyleInfo)table.getStyle();
  style.setName("TableStyleMedium2");
  style.setShowColumnStripes(false);
  style.setShowRowStripes(true);
  table.getCTTable().setTableType(STTableType.XML);
  int i = 0;
  for (CTTableColumn ctTableColumn : table.getCTTable().getTableColumns().getTableColumnList()) {
   ctTableColumn.setUniqueName(headers[i]);
   CTXmlColumnPr xmlColumnPr = ctTableColumn.addNewXmlColumnPr();
   xmlColumnPr.setXmlDataType(STXmlDataType.STRING);
   xmlColumnPr.setXpath("/class/student/" + headers[i++]);
   xmlColumnPr.setMapId(xssfMap.getCtMap().getID());
  }

  XSSFRow row = sheet.createRow(0);
  int c = 0;
  for (String header : headers) {
   row.createCell(c++).setCellValue(header);
  }

  FileOutputStream out = new FileOutputStream(workbookFilePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }


 //the XSSFRelation for /xl/xmlMaps.xml
 private final static class XSSFXmlMapsRelation extends POIXMLRelation {
  private XSSFXmlMapsRelation() {
   super(
    "application/xml",
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/xmlMaps",
    "/xl/xmlMaps.xml",
    MapInfo.class);
  }
 }
}

java - 如何使用 Java 中的 Apache POI 从/在 excel 中读取/写入 XML 映射？

问题描述

解决方案

推荐阅读