package org.apache.poi.xssf.extractor;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Vector;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ooxml.util.DocumentHelper;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFMap;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFTableColumn;
import org.apache.poi.xssf.usermodel.helpers.XSSFSingleXmlCell;
import org.apache.poi.xssf.usermodel.helpers.XSSFXmlColumnPr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
public class implements Comparator<String>{
private static final POILogger = POILogFactory.getLogger(XSSFExportToXml.class);
private XSSFMap ;
private final HashMap<String, Integer> = new HashMap<>();
public (XSSFMap map) {
this.map = map;
}
public void (OutputStream os, boolean validate) throws SAXException, TransformerException {
exportToXML(os, "UTF-8", validate);
}
public void (OutputStream os, String encoding, boolean validate) throws SAXException, TransformerException{
List<XSSFSingleXmlCell> singleXMLCells = map.getRelatedSingleXMLCell();
List<XSSFTable> tables = map.getRelatedTables();
String rootElement = map.getCtMap().getRootElement();
Document doc = DocumentHelper.createDocument();
final Element root;
if (isNamespaceDeclared()) {
root = doc.createElementNS(getNamespace(),rootElement);
} else {
root = doc.createElementNS("", rootElement);
}
doc.appendChild(root);
List<String> xpaths = new Vector<>();
Map<String,XSSFSingleXmlCell> singleXmlCellsMappings = new HashMap<>();
Map<String,XSSFTable> tableMappings = new HashMap<>();
for(XSSFSingleXmlCell simpleXmlCell : singleXMLCells) {
xpaths.add(simpleXmlCell.getXpath());
singleXmlCellsMappings.put(simpleXmlCell.getXpath(), simpleXmlCell);
}
for(XSSFTable table : tables) {
String commonXPath = table.getCommonXpath();
xpaths.add(commonXPath);
tableMappings.put(commonXPath, table);
}
indexMap.clear();
xpaths.sort(this);
indexMap.clear();
for(String xpath : xpaths) {
XSSFSingleXmlCell simpleXmlCell = singleXmlCellsMappings.get(xpath);
XSSFTable table = tableMappings.get(xpath);
if (!xpath.matches(".*\\[.*")) {
if (simpleXmlCell!=null) {
XSSFCell cell = simpleXmlCell.getReferencedCell();
if (cell!=null) {
Node currentNode = getNodeByXPath(xpath,doc.getFirstChild(),doc,false);
mapCellOnNode(cell,currentNode);
if ("".equals(currentNode.getTextContent()) && currentNode.getParentNode() != null) {
currentNode.getParentNode().removeChild(currentNode);
}
}
}
if (table!=null) {
List<XSSFTableColumn> tableColumns = table.getColumns();
XSSFSheet sheet = table.getXSSFSheet();
int startRow = table.getStartCellReference().getRow() + table.getHeaderRowCount();
int endRow = table.getEndCellReference().getRow();
for(int i = startRow; i<= endRow; i++) {
XSSFRow row = sheet.getRow(i);
Node tableRootNode = getNodeByXPath(table.getCommonXpath(), doc.getFirstChild(), doc, true);
short startColumnIndex = table.getStartCellReference().getCol();
for (XSSFTableColumn tableColumn : tableColumns) {
XSSFCell cell = row.getCell(startColumnIndex + tableColumn.getColumnIndex());
if (cell != null) {
XSSFXmlColumnPr xmlColumnPr = tableColumn.getXmlColumnPr();
if (xmlColumnPr != null) {
String localXPath = xmlColumnPr.getLocalXPath();
Node currentNode = getNodeByXPath(localXPath,tableRootNode,doc,false);
mapCellOnNode(cell, currentNode);
}
}
}
}
}
}
}
boolean isValid = true;
if (validate) {
isValid =isValid(doc);
}
if (isValid) {
TransformerFactory transfac = TransformerFactory.newInstance();
Transformer trans = transfac.newTransformer();
trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
trans.setOutputProperty(OutputKeys.INDENT, "yes");
trans.setOutputProperty(OutputKeys.ENCODING, encoding);
StreamResult result = new StreamResult(os);
DOMSource source = new DOMSource(doc);
trans.transform(source, result);
}
}
private boolean (Document xml) throws SAXException{
try{
String language = "http://www.w3.org/2001/XMLSchema";
SchemaFactory factory = SchemaFactory.newInstance(language);
Source source = new DOMSource(map.getSchema());
Schema schema = factory.newSchema(source);
Validator validator = schema.newValidator();
validator.validate(new DOMSource(xml));
return true;
} catch(IOException e) {
LOG.log(POILogger.ERROR, "document is not valid", e);
}
return false;
}
private void (XSSFCell cell, Node node) {
String value ="";
switch (cell.getCellType()) {
case STRING: value = cell.getStringCellValue(); break;
case BOOLEAN: value += cell.getBooleanCellValue(); break;
case ERROR: value = cell.getErrorCellString(); break;
case FORMULA:
if (cell.getCachedFormulaResultType() == CellType.STRING) {
value = cell.getStringCellValue();
} else {
if (DateUtil.isCellDateFormatted(cell)) {
value = getFormattedDate(cell);
} else {
value += cell.getNumericCellValue();
}
}
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = getFormattedDate(cell);
} else {
value += cell.getRawValue();
}
break;
default:
}
if (node instanceof Element) {
Element currentElement = (Element) node;
currentElement.setTextContent(value);
} else {
node.setNodeValue(value);
}
}
private String (String elementName) {
return elementName.matches(".*:.*")?elementName.split(":")[1]:elementName;
}
private String (XSSFCell cell) {
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", Locale.ROOT);
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
return sdf.format(cell.getDateCellValue());
}
private Node (String xpath,Node rootNode,Document doc,boolean createMultipleInstances) {
String[] xpathTokens = xpath.split("/");
Node currentNode =rootNode;
for(int i =2; i<xpathTokens.length;i++) {
String axisName = removeNamespace(xpathTokens[i]);
if (!axisName.startsWith("@")) {
NodeList list =currentNode.getChildNodes();
Node selectedNode = null;
if (!(createMultipleInstances && i==xpathTokens.length-1) ) {
selectedNode = selectNode(axisName, list);
}
if (selectedNode==null) {
selectedNode = createElement(doc, currentNode, axisName);
}
currentNode = selectedNode;
} else {
currentNode = createAttribute(doc, currentNode, axisName);
}
}
return currentNode;
}
private Node (Document doc, Node currentNode, String axisName) {
String attributeName = axisName.substring(1);
NamedNodeMap attributesMap = currentNode.getAttributes();
Node attribute = attributesMap.getNamedItem(attributeName);
if (attribute==null) {
attribute = doc.createAttributeNS("", attributeName);
attributesMap.setNamedItem(attribute);
}
return attribute;
}
private Node (Document doc, Node currentNode, String axisName) {
Node selectedNode;
if (isNamespaceDeclared()) {
selectedNode =doc.createElementNS(getNamespace(),axisName);
} else {
selectedNode = doc.createElementNS("", axisName);
}
currentNode.appendChild(selectedNode);
return selectedNode;
}
private Node (String axisName, NodeList list) {
Node selectedNode = null;
for(int j=0;j<list.getLength();j++) {
Node node = list.item(j);
if (node.getNodeName().equals(axisName)) {
selectedNode=node;
break;
}
}
return selectedNode;
}
private boolean () {
String schemaNamespace = getNamespace();
return schemaNamespace!=null && !schemaNamespace.isEmpty();
}
private String () {
return map.getCTSchema().getNamespace();
}
@Override
public int (String leftXpath, String rightXpath) {
Node xmlSchema = map.getSchema();
String[] leftTokens = leftXpath.split("/");
String[] rightTokens = rightXpath.split("/");
String samePath = "";
int minLength = leftTokens.length< rightTokens.length? leftTokens.length : rightTokens.length;
Node localComplexTypeRootNode = xmlSchema;
for(int i =1;i <minLength; i++) {
String leftElementName = leftTokens[i];
String rightElementName = rightTokens[i];
if (leftElementName.equals(rightElementName)) {
samePath += "/" + leftElementName;
localComplexTypeRootNode = getComplexTypeForElement(leftElementName, xmlSchema, localComplexTypeRootNode);
} else {
return indexOfElementInComplexType(samePath, leftElementName, rightElementName,localComplexTypeRootNode);
}
}
return 0;
}
private int (String samePath,String leftElementName,String rightElementName,Node complexType) {
if(complexType == null) {
return 0;
}
int i = 0;
Node node = complexType.getFirstChild();
final String leftWithoutNamespace = removeNamespace(leftElementName);
int leftIndexOf = getAndStoreIndex(samePath, leftWithoutNamespace);
final String rightWithoutNamespace = removeNamespace(rightElementName);
int rightIndexOf = getAndStoreIndex(samePath, rightWithoutNamespace);
while (node != null && (rightIndexOf==-1||leftIndexOf==-1)) {
if (node instanceof Element && "element".equals(node.getLocalName())) {
String elementValue = getNameOrRefElement(node).getNodeValue();
if (elementValue.equals(leftWithoutNamespace)) {
leftIndexOf = i;
indexMap.put(samePath+"/"+leftWithoutNamespace, leftIndexOf);
}
if (elementValue.equals(rightWithoutNamespace)) {
rightIndexOf = i;
indexMap.put(samePath+"/"+rightWithoutNamespace, rightIndexOf);
}
}
i++;
node = node.getNextSibling();
}
if(leftIndexOf == -1 || rightIndexOf == -1) {
return 0;
}
return Integer.compare(leftIndexOf, rightIndexOf);
}
private int getAndStoreIndex(String samePath,String withoutNamespace) {
String withPath = samePath+"/"+withoutNamespace;
return indexMap.getOrDefault(withPath, -1);
}
private Node (Node node) {
Node returnNode = node.getAttributes().getNamedItem("ref");
if(returnNode != null) {
return returnNode;
}
return node.getAttributes().getNamedItem("name");
}
private Node (String elementName,Node xmlSchema,Node localComplexTypeRootNode) {
String elementNameWithoutNamespace = removeNamespace(elementName);
String complexTypeName = getComplexTypeNameFromChildren(localComplexTypeRootNode, elementNameWithoutNamespace);
Node complexTypeNode = null;
if (!"".equals(complexTypeName)) {
complexTypeNode = getComplexTypeNodeFromSchemaChildren(xmlSchema, null, complexTypeName);
}
return complexTypeNode;
}
private String (Node localComplexTypeRootNode,
String elementNameWithoutNamespace) {
if(localComplexTypeRootNode == null) {
return "";
}
Node node = localComplexTypeRootNode.getFirstChild();
String complexTypeName = "";
while (node != null) {
if ( node instanceof Element && "element".equals(node.getLocalName())) {
Node nameAttribute = getNameOrRefElement(node);
if (nameAttribute.getNodeValue().equals(elementNameWithoutNamespace)) {
Node complexTypeAttribute = node.getAttributes().getNamedItem("type");
if (complexTypeAttribute!=null) {
complexTypeName = complexTypeAttribute.getNodeValue();
break;
}
}
}
node = node.getNextSibling();
}
return complexTypeName;
}
private Node (Node xmlSchema, Node complexTypeNode,
String complexTypeName) {
Node node = xmlSchema.getFirstChild();
while (node != null) {
if ( node instanceof Element) {
if ("complexType".equals(node.getLocalName())) {
Node nameAttribute = getNameOrRefElement(node);
if (nameAttribute.getNodeValue().equals(complexTypeName)) {
Node sequence = node.getFirstChild();
while(sequence != null) {
if ( sequence instanceof Element) {
final String localName = sequence.getLocalName();
if ("sequence".equals(localName) || "all".equals(localName)) {
complexTypeNode = sequence;
break;
}
}
sequence = sequence.getNextSibling();
}
if (complexTypeNode!=null) {
break;
}
}
}
}
node = node.getNextSibling();
}
return complexTypeNode;
}
}