public NodeList queryAbstractElementDocElem(
String sheetName, String queryExpression, String requiredField) throws Exception {
if (!queryExpression.contains(Constants.QUERY_EQUALS_EXP)) {
throw new IllegalArgumentException("expression should contain =", null);
}
ArrayList<String> keyValSequence = new ArrayList<String>();
if (queryExpression.contains(Constants.QUERY_CONCAT_EXP)) {
String[] expressionArray_joined = queryExpression.split(Constants.QUERY_CONCAT_EXP);
for (String equalsExpression : expressionArray_joined) {
String[] expressionArray_equals = equalsExpression.split(Constants.QUERY_EQUALS_EXP);
keyValSequence.add(expressionArray_equals[0]);
keyValSequence.add(expressionArray_equals[1]);
}
} else {
String[] expressionArray_equals = queryExpression.split(Constants.QUERY_EQUALS_EXP);
keyValSequence.add(expressionArray_equals[0]);
keyValSequence.add(expressionArray_equals[1]);
}
String[] expressionArray1 = {};
String[] e = keyValSequence.toArray(expressionArray1);
this.sheetName = sheetName;
String toRet = "";
if (docNew == null) {
docNew = getSimpleXmlFromXls();
RobotCache.getXmlCache().put(cacheId, docNew);
docNew = RobotCache.getXmlCache().get(cacheId);
} else {
log.debug("Working with docNew from cache");
}
XPath xpath = XPathFactory.newInstance().newXPath();
// String query = "//Row/Record[(@key=\"" +expressionArray[0]+"\" and @value=\""
// +expressionArray[1]+"\" )" +
// " and (@key=\"" +expressionArray[2]+"\" and @value=\"" +expressionArray[3]+"\")"+
// "]/../Record[@key=\"" +requiredField+"\"]";
String query = "";
if (e.length > 2) {
query = "//Row[" + e[0] + "='" + e[1] + "' and " + e[2] + "='" + e[3] + "']";
} else {
query = "//Row[" + e[0] + "='" + e[1] + "']";
}
log.debug("Query is " + query);
XPathExpression expr = xpath.compile(query);
Object result = expr.evaluate(docNew, XPathConstants.NODESET);
NodeList tableRowNodes = (NodeList) result;
log.info("returning " + tableRowNodes);
return tableRowNodes;
}
public NodeList getHeaderRows() throws Exception {
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
// docFactory.setNamespaceAware(true);
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
Document doc = docBuilder.parse(file);
NodeList sheetLst = doc.getElementsByTagName("ss:Name");
XPath xpath = XPathFactory.newInstance().newXPath();
XPathExpression expr =
xpath.compile("//Worksheet[@Name=\"" + sheetName + "\"]/Table/Row[1]/Cell");
Object result = expr.evaluate(doc, XPathConstants.NODESET);
NodeList headerNodes = (NodeList) result;
Node toRem = (Node) headerNodes.item(0);
for (int i = 0; i < sheetLst.getLength(); i++) {
List lstFields = new ArrayList<Node>();
List lstFields_common = new ArrayList<Node>();
log.debug("======================start=======================");
Node testSuiteNode = sheetLst.item(i);
NamedNodeMap commonAttributesList = testSuiteNode.getAttributes();
}
return headerNodes;
}
public Document queryAbstractElementDocDocument(
String sheetName, String queryExpression, String requiredField) throws Exception {
NodeList tableRowNodes = queryAbstractElementDocElem(sheetName, queryExpression, requiredField);
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
Document doc = docBuilder.newDocument();
Element rootElement = docNew.createElement("Result");
log.info("returning " + tableRowNodes);
for (int resultSetIdx = 0; resultSetIdx < tableRowNodes.getLength(); resultSetIdx++) {
Element record = (Element) tableRowNodes.item(resultSetIdx);
rootElement.appendChild(record);
}
// doc.appendChild(rootElement);
return docNew;
}
public List<Map<String, String>> queryAbstractElementDoc_List(
String sheetName, String queryExpression, String requiredField) throws Exception {
List<Map<String, String>> lstResultMap = new ArrayList<Map<String, String>>();
Map<String, String> entryMap = new LinkedHashMap<String, String>();
NodeList tableRowNodes = queryAbstractElementDocElem(sheetName, queryExpression, requiredField);
log.debug("Result count is " + tableRowNodes.getLength());
Node recordNode = tableRowNodes.item(0);
for (int resultSetIdx = 0; resultSetIdx < tableRowNodes.getLength(); resultSetIdx++) {
Element record = (Element) tableRowNodes.item(resultSetIdx);
NodeList recordFldNodes = record.getChildNodes();
entryMap = new LinkedHashMap<String, String>();
for (int fldIdx = 0; fldIdx < recordFldNodes.getLength(); fldIdx++) {
Element recordField = (Element) recordFldNodes.item(fldIdx);
entryMap.put(recordField.getTagName(), recordField.getTextContent());
}
lstResultMap.add(entryMap);
}
return lstResultMap;
}
public String queryAbstractElementDoc(
String sheetName, String queryExpression, String requiredField) throws Exception {
String toRet = "";
NodeList tableRowNodes = queryAbstractElementDocElem(sheetName, queryExpression, requiredField);
log.debug("Result count is " + tableRowNodes.getLength());
Node recordNode = tableRowNodes.item(0);
for (int resultSetIdx = 0; resultSetIdx < tableRowNodes.getLength(); resultSetIdx++) {
Element record = (Element) tableRowNodes.item(resultSetIdx);
NodeList childNodes = record.getChildNodes();
for (int recordIdx = 0; recordIdx < childNodes.getLength(); recordIdx++) {
Element recordField = (Element) childNodes.item(recordIdx);
if (recordField.getTagName().equals(requiredField)) {
if (!toRet.isEmpty()) {
toRet = toRet + ",";
}
toRet = toRet + recordField.getTextContent();
}
}
}
return toRet;
}
public Document getSimpleXmlFromXls() throws Exception {
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
docNew = docBuilder.newDocument();
Element rootElement = docNew.createElement("Worksheet");
NodeList listHeaders = getHeaderRows();
NodeList listRecords = getRecordRows();
Element row = null;
Element record = null;
Node cell = null;
for (int rowIdx = 1; rowIdx < listRecords.getLength(); rowIdx++) {
int trueIdx = -1;
row = docNew.createElement("Row");
Node rows = listRecords.item(rowIdx);
log.debug("working with row " + listRecords.item(rowIdx).getTextContent());
NodeList cellNodeList = rows.getChildNodes(); // Cell
for (int cellIdxAsRecd = 0; cellIdxAsRecd < cellNodeList.getLength() - 1; cellIdxAsRecd++) {
cell = cellNodeList.item(cellIdxAsRecd);
trueIdx++;
if (cell instanceof DeferredTextImpl) {
cell = cell.getNextSibling();
cellIdxAsRecd++;
// continue;
}
log.trace("Working with cell " + cell.getTextContent());
Element elem = (Element) cell;
if (elem.hasAttribute("ss:Index")) {
// this means some previous cells have been skipped.
int idxActual = Integer.parseInt(elem.getAttribute("ss:Index"));
// do some empty runs before resuming the process.
// run only for the preceeding empty ones, the current one needs to proceed
int skippedIdxs = idxActual - trueIdx - 1;
// catchup the skipped idx, which automatically brings trueIdx up to date.(whihc is one
// lies than the actual)
int emtpyTodo = idxActual - trueIdx - 1;
for (int i = 0; i < skippedIdxs; i++) {
record = docNew.createElement(listHeaders.item(trueIdx).getTextContent());
log.trace(
"Empty Run"
+ ":::::"
+ "key cellIdx( "
+ (trueIdx)
+ ") "
+ listHeaders.item(trueIdx).getTextContent());
// record.setAttribute("key", listHeaders.item(trueIdx).getTextContent());
record.setTextContent("");
// setAttribute("value", "");
row.appendChild(record);
trueIdx++;
}
}
record = docNew.createElement("Record");
Node data = cell.getFirstChild();
if (data == null) continue;
if (trueIdx < listHeaders.getLength()) {
record = docNew.createElement(listHeaders.item(trueIdx).getTextContent());
log.trace(
"Data now is "
+ data.getTextContent()
+ "//"
+ "key from header cellIdx( "
+ trueIdx
+ ") "
+ listHeaders.item(trueIdx).getTextContent());
// record.setAttribute("key", listHeaders.item(trueIdx).getTextContent());
// record.setAttribute("value", data.getTextContent());
record.setTextContent(data.getTextContent());
row.appendChild(record);
} else {
break;
}
}
rootElement.appendChild(row);
}
docNew.appendChild(rootElement);
log.debug("Number of rows are " + rootElement.getChildNodes().getLength());
xmlToFile(docNew, "C:");
return docNew;
}
