package com.citi.dashboard.excel.xml;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import com.citi.dashboard.excel.ExcelReader;
import com.citi.dashboard.excel.ReflectClass;
import com.citi.dashboard.excel.chart.Chart;
/**
* @author xq38442
*/
public class ParseXML {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private ExcelReader excelReader = new ExcelReader();
/**
* parse xml and if the element text likes [row,column], then retrive data from relative excel file
* @param root root element
* @param ownerObj object which the element represents if the element has attribute 'class'.
* We can use reflect to get the data from xml element text
* @return return the assembled object when the element has 'class' attribute
* @throws Exception
*/
public Object parse(Element root, Object ownerObj) throws Exception{
if(root == null)
return null;
// top level,
if(ownerObj == null) {
String className = root.attributeValue("class");
Class<?> clazz = Class.forName(className);
ownerObj = clazz.newInstance();
}
String rootElementName = root.getName();
String rootFieldName = rootElementName;
// init POIFSFileSystem, HSSFWorkbook etc
if(fs == null) {
String rootExcelFilePath = root.attributeValue("filePath");
InputStream is = null;
if(rootExcelFilePath != null) {
try {
// absolute path
is = new FileInputStream(rootExcelFilePath);
} catch (Exception e) {
// relative path
String userDir = System.getProperty("user.dir");
StringBuilder sb = new StringBuilder(userDir);
sb.append("/").append(rootExcelFilePath);
is = new FileInputStream(rootExcelFilePath);
}
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
String sheetName = root.attributeValue("sheetName");
if(sheetName != null && !sheetName.equals("")) {
try {
int sheetIndex = Integer.parseInt(sheetName);
sheet = wb.getSheetAt(sheetIndex);
} catch (Exception e) {
sheet = wb.getSheet(sheetName);
}
} else {
sheet = wb.getSheetAt(0);
}
}
}
String rootTypeName = root.attributeValue("type");
/*
* type is list, primary type: byte,short,int,long,float,double, bool
*/
if(rootTypeName != null) {
if(rootTypeName.toLowerCase().equals("list")) {
List list = new ArrayList();
// iterate through child elements of root
for ( Iterator i = root.elementIterator(); i.hasNext(); ) {
// qinxd 需要判断属性more是否为true,未完待续
String more = root.attributeValue("more");
if(more != null && more.equals("true")) {
Object object = parseMoreAttribute(root, ownerObj);
if(object instanceof List) {
List objList = (List)object;
list.addAll(objList);
}
continue;
}
Element childElement = (Element) i.next();
String elementName = childElement.getName();
// owner object's field name should be equals to the childElement name
String fieldName = elementName;
// is there class attribute
String className = childElement.attributeValue("class");
if(className != null) {
Class<?> childClass = Class.forName(className);
Object childOwnerObj = childClass.newInstance();
Object childObj = parse(childElement, childOwnerObj);
if(childObj instanceof List) {
List childList = (List)childObj;
list.addAll(childList);
} else {
list.add(childObj);
}
} else {
// primary type or String, List, Map, Set
// default is String type
String typeName = childElement.attributeValue("type");
String text = childElement.getTextTrim();
// // qinxd 待测试
// String[] texts = getTextFromExcel(text);
// if(texts == null || te)
// return ownerObj;
if(typeName != null) {
if(typeName.toLowerCase().equals("int")
|| typeName.toLowerCase().equals("integer")) {
list.add(new Integer(text));
} else if(typeName.toLowerCase().equals("boolean")) {
list.add(new Boolean(text));
} else if(typeName.toLowerCase().equals("double")) {
list.add(new Double(text));
} else if(typeName.toLowerCase().equals("long")) {
list.add(new Long(text));
} else if(typeName.toLowerCase().equals("short")) {
list.add(new Short(text));
} else if(typeName.toLowerCase().equals("float")) {
list.add(new Float(text));
} else if(typeName.toLowerCase().equals("short")) {
list.add(new Short(text));
} else if(typeName.toLowerCase().equals("byte")) {
list.add(new Byte(text));
} // no char type
else {
// deafult type is String
list.add(text);
}
}
}
}
ReflectClass.setField(rootFieldName, ownerObj, list);
} else {
// type is not a list
String rootText = root.getTextTrim();
String[] rootTexts = getTextFromExcel(rootText);
if(rootTexts != null && rootTexts.length == 1) {
rootText = rootTexts[0];
if(rootTypeName.toLowerCase().equals("int")
|| rootTypeName.toLowerCase().equals("integer")) {
ReflectClass.setField(rootFieldName, ownerObj, new Integer(rootText));
} else if(rootTypeName.toLowerCase().equals("boolean")) {
ReflectClass.setField(rootFieldName, ownerObj, new Boolean(rootText));
} else if(rootTypeName.toLowerCase().equals("double")) {
ReflectClass.setField(rootFieldName, ownerObj, new Double(rootText));
} else if(rootTypeName.toLowerCase().equals("long")) {
ReflectClass.setField(rootFieldName, ownerObj, new Long(rootText));
} else if(rootTypeName.toLowerCase().equals("float")) {
ReflectClass.setField(rootFieldName, ownerObj, new Float(rootText));
} else if(rootTypeName.toLowerCase().equals("short")) {
ReflectClass.setField(rootFieldName, ownerObj, new Short(rootText));
} else if(rootTypeName.toLowerCase().equals("byte")) {
ReflectClass.setField(rootFieldName, ownerObj, new Byte(rootText));
} else if(rootTypeName.toLowerCase().equals("string")) {
ReflectClass.setField(rootFieldName, ownerObj, rootText);
} // no char type
}
}
return ownerObj;
}
// type is null, but text is not null, which means type is default: String
String rootText = root.getTextTrim();
if(rootText != null && !rootText.equals("")) {
String[] rootTexts = getTextFromExcel(rootText);
if(rootTexts != null && rootTexts.length == 1) {
rootText = rootTexts[0];
ReflectClass.setField(rootFieldName, ownerObj, rootText);
return ownerObj;
}
}
// check more attribute, which means the data are elements of a list
// qinxd
String more = root.attributeValue("more");
if(more != null && more.equals("true")) {
return parseMoreAttribute(root, ownerObj);
}
// otherwise, iterate through child elements of root
for ( Iterator i = root.elementIterator(); i.hasNext(); ) {
Element childElement = (Element) i.next();
parse(childElement, ownerObj);
}
return ownerObj;
}
/**
*
* @param coordinates [1,2]-[5,20]
* @return an array contains all excel cell data
* @throws Exception
*/
public String[] getTextFromExcel(String coordinates) throws Exception{
if(coordinates == null)
return null;
coordinates = coordinates.trim();
String[] texts = new String[]{coordinates};
if(!(coordinates.startsWith("[") && coordinates.endsWith("]")))
return texts;
texts = coordinates.split("-");
if(texts != null && texts.length > 0) {
for(int i=0;i<texts.length;++i) {
String[] coordArray = texts[i].split(",");
if(coordArray != null && coordArray.length == 2) {
String row = coordArray[0].trim().substring(1);
String column = coordArray[1].trim();
column = column.substring(0, column.length()-1);
String excelData = getSingleTextFromExcel(row, column);
texts[i] = excelData;
}
}
}
return texts;
}
public String getSingleTextFromExcel(String rowNum, String columnNum) {
if(sheet != null) {
HSSFRow row = sheet.getRow(Integer.parseInt(rowNum));
HSSFCell cell = row.getCell(Integer.parseInt(columnNum));
String cellData = excelReader.getCellFormatValue(cell);
return cellData;
}
return "";
}
public String getSingleTextFromExcel(Coord coord) {
if(sheet != null && coord != null) {
HSSFRow row = sheet.getRow(coord.row);
HSSFCell cell = row.getCell(coord.column);
String cellData = excelReader.getCellFormatValue(cell);
return cellData;
}
return "";
}
public static void main(String[] args) throws Exception{
// ParseXML px = new ParseXML();
// Document document = px.parseDocument("C:/Users/XQ38442/workspace/DashBoard-Chrome/html/excel/xml/test.xml");
// if(document != null) {
// Element root = document.getRootElement();
// for ( Iterator i = root.elementIterator(); i.hasNext(); ) {
// Element childElement = (Element) i.next();
// String imageType = childElement.attributeValue("imageType");
// if(imageType!= null && imageType.equals("pie")) {
// Object object = px.parse(childElement, null);
// if(object instanceof Chart) {
// Chart graph = (Chart) object;
// graph.createGraph(null);
// }
// System.out.println(object);
// }
// }
//
// }
}
public static List<String> getExcelImageUrl() throws Exception{
List<String> imageUrls = new ArrayList<String>();
// relative path
String userDir = System.getProperty("user.dir");
StringBuilder sb = new StringBuilder(userDir);
sb.append("/html/excel/xml");
File file = new File(sb.toString());
List<String> xmlFiles = new ArrayList<String>();
getAllExcelXmlFiles(file, xmlFiles);
for(String xmlFilePath: xmlFiles) {
List<String> singleXmlImageUrls = getExcelImageUrlFromSingleXml(xmlFilePath);
imageUrls.addAll(singleXmlImageUrls);
}
return imageUrls;
}
public static Map<XMLFile, List<String>> getAllExcelImageUrls() throws Exception {
// relative path
String userDir = System.getProperty("user.dir");
StringBuilder sb = new StringBuilder(userDir);
sb.append("/html/excel/xml");
File file = new File(sb.toString());
List<XMLFile> xmlFiles = new ArrayList<XMLFile>();
getAllXmlFiles(file, xmlFiles);
Map<XMLFile, List<String>> map = new HashMap<XMLFile, List<String>>();
for(XMLFile xmlfile: xmlFiles) {
List<String> imageUrls = new ArrayList<String>();
List<String> singleXmlImageUrls = getExcelImageUrlFromSingleXml(xmlfile.getAbsolutePath());
imageUrls.addAll(singleXmlImageUrls);
map.put(xmlfile, imageUrls);
}
return map;
}
/**
* @param dir
* @param xmlFiles list of absolute path of xml files
*/
private static void getAllExcelXmlFiles(File dir, List<String> xmlFiles) {
File[] files = dir.listFiles();
for(File file : files)
{
if(file.isDirectory())
{
getAllExcelXmlFiles(file, xmlFiles);
}
else
{
Pattern pattern = Pattern.compile("[(\\s\\S)*]+[\\.](xml|XML)$");
Matcher matcher = pattern.matcher(file.getName());
boolean flag = matcher.matches();
if(flag == true){
String xmlAbsolutePath = file.getAbsolutePath();
xmlFiles.add(xmlAbsolutePath.replaceAll("\\\\", "/"));
}
}
}
}
/**
* @param dir
* @param xmlFiles list of absolute path of xml files
*/
private static void getAllXmlFiles(File dir, List<XMLFile> xmlFiles) {
File[] files = dir.listFiles();
for(File file : files)
{
if(file.isDirectory())
{
getAllXmlFiles(file, xmlFiles);
}
else
{
Pattern pattern = Pattern.compile("[(\\s\\S)*]+[\\.](xml|XML)$");
Matcher matcher = pattern.matcher(file.getName());
boolean flag = matcher.matches();
if(flag == true){
String xmlAbsolutePath = file.getAbsolutePath();
XMLFile xmlfile = new XMLFile();
xmlfile.setAbsolutePath(xmlAbsolutePath.replaceAll("\\\\", "/"));
xmlfile.setLastModified(file.lastModified());
xmlFiles.add(xmlfile);
}
}
}
}
public static List<String> getExcelImageUrlFromSingleXml(String xmlPath) throws Exception{
List<String> imageUrlList = new ArrayList<String>();
ParseXML px = new ParseXML();
Document document = null;
try {
// absolute path
document = px.parseDocument(xmlPath);
} catch (Exception e) {
// relative path
String userDir = System.getProperty("user.dir");
StringBuilder sb = new StringBuilder(userDir);
sb.append("/").append(xmlPath);
document = px.parseDocument(xmlPath);
}
if(document != null) {
Element root = document.getRootElement();
for ( Iterator i = root.elementIterator(); i.hasNext(); ) {
Element childElement = (Element) i.next();
Object object = px.parse(childElement, null);
if(object instanceof Chart) {
Chart chart = (Chart) object;
String imageUrl = chart.createGraph(null);
imageUrlList.add(imageUrl);
}
}
}
return imageUrlList;
}
public Document parseDocument(String fileName) throws DocumentException {
SAXReader reader = new SAXReader();
Document document = reader.read(new File(fileName));
return document;
}
/**
* you can get parsed Chart object from single XML file
* @param xmlPath
* @return
* @throws Exception
*/
public static List<Chart> getGraphObjectFromSingleXml(String xmlPath, String chartType) throws Exception{
ParseXML px = new ParseXML();
Document document = null;
try {
// absolute path
document = px.parseDocument(xmlPath);
} catch (Exception e) {
// relative path
String userDir = System.getProperty("user.dir");
StringBuilder sb = new StringBuilder(userDir);
sb.append("/").append(xmlPath);
document = px.parseDocument(xmlPath);
}
List<Chart> chartList = new ArrayList<Chart>();
if(document != null) {
Element root = document.getRootElement();
for ( Iterator i = root.elementIterator(); i.hasNext(); ) {
Element childElement = (Element) i.next();
if(childElement == null)
continue;
String chartTypeFromXml = childElement.attributeValue("chartType");
if(chartTypeFromXml.toLowerCase().equals(chartType.toLowerCase())) {
Object object = px.parse(childElement, null);
if(object instanceof Chart) {
Chart chart = (Chart) object;
chartList.add(chart);
}
}
}
}
return chartList;
}
public Object parseMoreAttribute(Element root, Object ownerObj) throws Exception{
if(root == null)
return ownerObj;
String more = root.attributeValue("more");
if(more == null)
return ownerObj;
if(more.trim().equals("true")){
String numberStr = root.attributeValue("number");
if(numberStr == null)
return ownerObj;
int number = Integer.parseInt(numberStr);
List objList = new ArrayList();
String className = root.attributeValue("class");
if(className != null) {
Class<?> childClass = Class.forName(className);
for(int i=0;i<number;++i) {
Object childObj = childClass.newInstance();
objList.add(childObj);
}
}
for (Iterator i = root.elementIterator(); i.hasNext(); ) {
Element childElement = (Element) i.next();
if(childElement == null)
continue;
String elementName = childElement.getName();
String childFieldName = elementName;
String childTypeName = childElement.attributeValue("type");
if(childTypeName != null) {
String childText = childElement.getTextTrim();
if(childText != null && childText != "") {
if(childText.startsWith("[") && childText.endsWith("]")){
Coord[] coords = parseMoreData(childText);
if(coords == null || coords.length != number){
throw new Exception("number attribute doesn't equal to the excel columns or rows");
}
for(int k=0;k<coords.length;++k) {
String excelText = getSingleTextFromExcel(coords[k]);
if(childTypeName.toLowerCase().equals("int")
|| childTypeName.toLowerCase().equals("integer")) {
ReflectClass.setField(childFieldName, objList.get(k), new Integer(excelText));
} else if(childTypeName.toLowerCase().equals("boolean")) {
ReflectClass.setField(childFieldName, objList.get(k), new Boolean(excelText));
} else if(childTypeName.toLowerCase().equals("double")) {
ReflectClass.setField(childFieldName, objList.get(k), new Double(excelText));
} else if(childTypeName.toLowerCase().equals("long")) {
ReflectClass.setField(childFieldName, objList.get(k), new Long(excelText));
} else if(childTypeName.toLowerCase().equals("float")) {
ReflectClass.setField(childFieldName, objList.get(k), new Float(excelText));
} else if(childTypeName.toLowerCase().equals("short")) {
ReflectClass.setField(childFieldName, objList.get(k), new Short(excelText));
} else if(childTypeName.toLowerCase().equals("byte")) {
ReflectClass.setField(childFieldName, objList.get(k), new Byte(excelText));
} else if(childTypeName.toLowerCase().equals("string")) {
ReflectClass.setField(childFieldName, objList.get(k), excelText);
} // no char type
}
} else {
for(int k=0;k < objList.size();++k) {
parse(childElement, objList.get(k));
}
}
}
}
}
return objList;
}
return null;
}
/**
* @param moreData likes [1,2]-[5,20]
* @return
*/
public Coord[] parseMoreData(String moreData) {
Coord[] coords = new Coord[0];
if(moreData == null)
return coords;
String[] ranges = moreData.split("-");
if(ranges == null || ranges.length != 2)
return coords;
//[1,2]
String[] startDatas = ranges[0].trim().split(",");
if(startDatas == null || startDatas.length != 2)
return coords;
Coord startCoord = new Coord(Integer.parseInt(startDatas[0].substring(1)), Integer.parseInt(startDatas[1].substring(0, startDatas[1].length()-1)));
//[1,2]
String[] endDatas = ranges[1].trim().split(",");
if(startDatas == null || startDatas.length != 2)
return coords;
Coord endCoord = new Coord(Integer.parseInt(endDatas[0].substring(1)), Integer.parseInt(endDatas[1].substring(0, startDatas[1].length()-1)));
return Coord.getAllCoords(startCoord, endCoord);
}
final static class Coord {
int row;
int column;
public Coord(int row, int column) {
this.row = row;
this.column = column;
}
public static Coord[] getAllCoords (Coord start, Coord end) {
List<Coord> list = new ArrayList<Coord>();
if(start.row == end.row) {
int minY = start.column >= end.column ? end.column : start.column;
int maxY = start.column < end.column ? end.column : start.column;
for(int i=minY;i<=maxY;++i) {
Coord coord = new Coord(start.row, i);
list.add(coord);
}
} else if(start.column == end.column) {
int minX = start.row >= end.row ? end.row : start.row;
int maxX = start.row < end.row ? end.row : start.row;
for(int i=minX;i<=maxX;++i) {
Coord coord = new Coord(i, start.column);
list.add(coord);
}
}
Coord[] coords = new Coord[0];
coords = list.toArray(coords);
return coords;
}
}
}
ParseXML.java
最新推荐文章于 2024-01-25 17:01:37 发布