代码快速 实现xml 转换为 Excel（xml转excel通用类-java-完成代码可作工具使用）

1、用代码实现xml 文件/数据 转换为excel 文件。（java）-何潮背景：最近项目要做导出功能，但导出的数据对象类型实在太多了，一个个去实现；实在是没心情去做。于是-意义：快速实现数据导出为什么是xml to excel?因为项目中可以直接使用xml数据。所以就选择xml了。（直接数据库取数据转excel,也可以按同一思路实现）目标：从一个最多二级关联的数据结构，通过定义结构关系，实现数据自动转换成excel.特点：支持字典字段转换，可合并多关联字段，三种效果展示，有一定的可复用性 _(最多二级关联-当然，可自行扩展)转换效果：实现三种显示方式的转换方式1：一行显示一个完整对象（合并多个关

2、联表）方式2：复合形展示方式3：一行显示一个完整对象（合并多个关联表）-支持不同对象不同表头复用实现一个简单导出要多少代码？xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = "CM10_OBJECT"mainXmlStruct1.tableCName = "活动信息"mainXmlStruct1.tableCalssField = "OBJ_SUBTYPE"mainXmlStruct1.tableCalssCName.put("2001

3、01", "院团大事");HashMap<String, String> test1 = new HashMap<String, String>();test1.put("0", "新建");test1.put("100", "完成");mainXmlStruct1.tableFilesClassValue.put("OBJ_STATE", test1);mainXmlStruct1.tableFiles = new String "

4、;OBJ_NAME", "活动名称" , "FIELD_1", "活动内容简介" , "FIELD_2", "活动开始时间" , "FIELD_3", "活动结束时间" , "OBJ_STATE", "状态" ;xt.alltables.put("200101", mainXmlStruct1);思路？定义结构 定义数据关系过程循环取表，循环取表头，循环取内容-class xExcelT

5、ype/* xls显示方式0：组合式1：单一列表式 */int exShowType = 0;String rootName = "DataSource"String maintableName = "CM10_OBJECT"HashMap<String, xmlStruct> alltables = new HashMap<String, xmlStruct>();/* 定义xml数据结构定义 */class xmlStructString tableName = ""/ 表名String tableCNam

6、e = ""/ 表名中文说明/* 表的性质0：主表（默认）1：从表-这里暂处理1级主从，哈 */int tableType = 0;String tableCalssField = ""/ 如果有内容分类，分类标识字段String tableCalssFieldValue = ""HashMap<String, String> tableCalssCName = new HashMap<String, String>();/* 表的字典字段及对应值 */HashMap<String, HashMap<

7、;String, String>> tableFilesClassValue = new HashMap<String, HashMap<String, String>>();/* 表字段中文对应 */ HashMap<String, String> tableFiles = new HashMap<String, String>();String tableFiles;理论上通用，罪过。不多说，有可能用到的，下附件看看。不合理的地方请指出-下面是完整代码（一些特殊引用只是用作数据取数据，引用时可以去掉的-）package jetse

8、nnet.jmcc.business;import java.io.File;n;import java.sql.SQLException;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import javax.jws.WebParam;import jetsennet mon.PathUtil;import jetsennet.contentmanage.CmDataAccess;import jetsennet.contentmanage.CmObj

9、Helper;import .UserAuthHeader;import .WSResult;import jetsennet.sqlclient.ConnectionInfo;import jetsennet.sqlclient.DbConfig;import jetsennet.sqlclient.ISqlExecutor;import jetsennet.sqlclient.QueryTable;import jetsennet.sqlclient.SqlClientObjFactory;import jetsennet.sqlclient.SqlCondition;import jet

10、sennet.sqlclient.SqlLogicType;import jetsennet.sqlclient.SqlParamType;import jetsennet.sqlclient.SqlQuery;import jetsennet.sqlclient.SqlRelationType;import jetsennet.sqlclient.TableJoinType;import jetsennet.util.StringUtil;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableCellForma

11、t;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import org.dom4j.Document;import org.dom4j.Element;import org.dom4j.io.SAXReader;public class xmlToExcelprivate static jetsennet.logger.ILog logger = jetsennet.logger.LogManager.getLogger("JetsenNet

12、.JCMP");private static String XSD_FILE_PATH = "jcmp/schemafiles"private static ConnectionInfo cmpConnectionString = new ConnectionInfo(DbConfig.getProperty("cmp_driver"), DbConfig.getProperty("cmp_dburl"),DbConfig.getProperty("cmp_dbuser"), DbConfig.getPr

13、operty("cmp_dbpwd");leFont titleFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false);static WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(titleFont);private static ConnectionInfo mccConnectionString = new ConnectionInf

14、o(DbConfig.getProperty("mcc_driver"), DbConfig.getProperty("mcc_dburl"),DbConfig.getProperty("mcc_dbuser"), DbConfig.getProperty("mcc_dbpwd");private static ISqlExecutor sqlExecutor = SqlClientObjFactory.createSqlExecutor(mccConnectionString);static int cellti

15、tlenow = 0;/ 当前列表头列号static int cellvaluenow = 0;/ 当前内容列号static int rowtitlenow = 0;/ 当前列表头行号static boolean addcelltitle = true;static String OldObjtype = ""public static void main(String args)File file = new File("C:/temp/test.xls");WritableWorkbook workbook;tryworkbook = Workboo

16、k.createWorkbook(file);/processActivity(workbook, "4023,4025,4007,4024,4026", "200101,200103,200101,200102,200104");processActivity(workbook, "4023,4025,4007", "200101,200101,200101");workbook.write();workbook.close();catch (Exception e)/ TODO Auto-generated c

17、atch blocke.printStackTrace();System.out.println("OK?");public static void processActivity(WritableWorkbook workBook, String obj_id, String obj_subtype) throws ExceptionOldObjtype = ""celltitlenow = 0;addcelltitle = true;rowtitlenow =0;WritableSheet sheet = workBook.createSheet(&

18、quot;sheet1", 0);/*/* 导出ID */String ids = obj_id.split(",");/* 导出类型 */String idt = obj_subtype.split(",");/* 导出个数 */int idsl = ids.length;/* 定义列宽 */int cellwihth = 20;xExcelType xt = new xExcelType();xt.exShowType = 1;if (xt.exShowType = 1)addcelltitle = true;elseaddcelltitl

19、e = false;/ *主表/结构定义/ 200101xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = "CM10_OBJECT"mainXmlStruct1.tableCName = "活动信息"mainXmlStruct1.tableCalssField = "OBJ_SUBTYPE"mainXmlStruct1.tableCalssCName.put("200101", "院团大事");mainXm

20、lStruct1.tableCalssCName.put("200102", "院团出访");mainXmlStruct1.tableCalssCName.put("200103", "院团专家交流");HashMap<String, String> test1 = new HashMap<String, String>();test1.put("0", "新建");test1.put("100", "完成");

21、mainXmlStruct1.tableFilesClassValue.put("OBJ_STATE", test1);mainXmlStruct1.tableFiles = new String "OBJ_NAME", "活动名称" , "FIELD_1", "活动内容简介" , "FIELD_2", "活动开始时间" , "FIELD_3", "活动结束时间" ;xt.alltables.put("20

22、0101", mainXmlStruct1);xt.alltables.put("200102", mainXmlStruct1);xt.alltables.put("200103", mainXmlStruct1);/ 200104xmlStruct mainXmlStruct2 = new xmlStruct();mainXmlStruct2.tableName = "CM10_OBJECT"mainXmlStruct2.tableCName = "活动信息"mainXmlStruct2.tableC

23、alssField = "OBJ_SUBTYPE"mainXmlStruct2.tableCalssCName.put("200104", "院团演出");mainXmlStruct2.tableFiles = new String "OBJ_NAME", "活动名称" , "FIELD_2", "活动开始时间" , "FIELD_3", "活动结束时间" ;xt.alltables.put("200104

24、", mainXmlStruct2);/ 20010401xmlStruct mainXmlStruct3 = new xmlStruct();mainXmlStruct3.tableName = "CM10_OBJECT"mainXmlStruct3.tableCalssField = "OBJ_SUBTYPE"mainXmlStruct3.tableCalssCName.put("20010401", "演出日志");/ HashMap<String, String> showType_h

25、m = getHMctroWord("20010401");/ mainXmlStruct1.tableFilesClassValue.put("FIELD_3", showType_hm);mainXmlStruct3.tableFiles = new String "FIELD_1", "演出时间" , "FIELD_2", "演出场次" , "FIELD_3", "演出类型" , "NUM_VAL2", &qu

26、ot;观众人数" ;xt.alltables.put("20010401", mainXmlStruct3);/ *从表/结构定义/xmlStruct son1Struct1 = new xmlStruct();son1Struct1.tableName = "CM10_MULTIINFO"son1Struct1.tableCName = "动信息"son1Struct1.tableCalssField = "INFO_TYPE"son1Struct1.tableCalssFieldValue = &qu

27、ot;1"/ HashMap<String, String> test2 = new HashMap<String, String>();/ test2.put("1", "新建1");/ test2.put("2", "完成2");/ test2.put("3", "完成3");/ xs2.tableFilesClassValue.put("INFO_TYPE", test2);son1Struct1.tableFile

28、s = new String "FIELD_1", "活动地点" ;xt.alltables.put("200101-1", son1Struct1);xt.alltables.put("200102-1", son1Struct1);xt.alltables.put("200103-1", son1Struct1);/xmlStruct son1Struct2 = new xmlStruct();son1Struct2.tableName = "CM10_MULTIINFO"

29、;son1Struct2.tableCName = "人员信息"son1Struct2.tableCalssField = "INFO_TYPE"son1Struct2.tableCalssFieldValue = "2"son1Struct2.tableFiles = new String "FIELD_1", "参与人员" ;xt.alltables.put("200101-2", son1Struct2);xt.alltables.put("200102-2&

30、quot;, son1Struct2);xt.alltables.put("200103-2", son1Struct2);/xmlStruct son1Struct3 = new xmlStruct();son1Struct3.tableName = "CM10_MULTIINFO"son1Struct3.tableCName = "剧目信息"d = "INFO_TYPE"son1Struct3.tableCalssFieldValue = "3"son1Struct3.tableFiles

31、= new String "FIELD_1", "剧目名称" ;xt.alltables.put("200101-3", son1Struct3);xt.alltables.put("200102-3", son1Struct3);xt.alltables.put("200103-3", son1Struct3);xmlStruct son2Struct1 = new xmlStruct();son2Struct1.tableName = "CM10_MULTIINFO"so

32、n2Struct1.tableCalssField = "INFO_TYPE"son2Struct1.tableCalssFieldValue = "1"son2Struct1.tableFiles = new String "FIELD_1", "节目单" ;lltables.put("200104-1", son2Struct1);xmlStruct son2Struct2 = new xmlStruct();son2Struct2.tableName = "CM10_MULTII

33、NFO"son2Struct2.tableCalssField = "INFO_TYPE"son2Struct2.tableCalssFieldValue = "2"son2Struct2.tableFiles = new String "FIELD_1", "剪报" ;xt.alltables.put("200104-2", son2Struct2);xmlStruct son2Struct3 = new xmlStruct();son2Struct3.tableName = &qu

34、ot;CM10_MULTIINFO"son2Struct3.tableCalssField = "INFO_TYPE"son2Struct3.tableCalssFieldValue = "3"son2Struct3.tableFiles = new String "FIELD_1", "海报" ;xt.alltables.put("200104-3", son2Struct3);/ 演出的日志多关联xmlStruct son3Struct1 = new xmlStruct();son

35、3Struct1.tableName = "CM10_MULTIINFO"son3Struct1.tableCalssField = "INFO_TYPE"son3Struct1.tableCalssFieldValue = "1"son3Struct1.tableFiles = new String "FIELD_1", "演出单位" ;xt.alltables.put("20010401-1", son3Struct1);xmlStruct son3Struct2 = n

36、ew xmlStruct();son3Struct2.tableName = "CM10_MULTIINFO"lssField = "INFO_TYPE"son3Struct2.tableCalssFieldValue = "2"son3Struct2.tableFiles = new String "FIELD_1", "演剧目名称" ;xt.alltables.put("20010401-2", son3Struct2);xmlStruct son3Struct3 = n

37、ew xmlStruct();son3Struct3.tableName = "CM10_MULTIINFO"son3Struct3.tableCalssField = "INFO_TYPE"son3Struct3.tableCalssFieldValue = "3"son3Struct3.tableFiles = new String "FIELD_1", "演出地点" ;xt.alltables.put("20010401-3", son3Struct3);xmlStru

38、ct son3Struct4 = new xmlStruct();son3Struct4.tableName = "CM10_MULTIINFO"son3Struct4.tableCalssField = "INFO_TYPE"son3Struct4.tableCalssFieldValue = "4"/ son3Struct4.tableFilesClassValue.put("FIELD_2", getHMctroWord("200104011");son3Struct4.tableFile

39、s = new String "FIELD_1", "参演人员" , "FIELD_2", "责任方式" ;xt.alltables.put("20010401-4", son3Struct4);/ /下面是统一生成处理int rowid = 0;int cellid = 0;for (int idi = 0; idi < idsl; idi+)rowid = createcell(rowid, idsidi, idtidi, xt, sheet);/ 额外加入xml结构String ob

40、jtype = idtidi;HashMap<String, String> sonMainIdTypes = null;if (objtype.equals("200101") | objtype.equals("200102") | objtype.equals("200103")/ 院团活动 常用else if (objtype.equals("200104")/ 院团活动 演出sonMainIdTypes = getMainSonIDAndType(idsidi, "20010401&

41、quot;);else if (objtype.equals("300201")/ 比赛声乐else if (objtype.equals("400101") | objtype.equals("400201") | objtype.equals("400301") | objtype.equals("400401")| objtype.equals("400501")/ 通用比赛else if (objtype.equals("500301")/ 优秀剧

42、目展演else if (objtype.equals("500101") | objtype.equals("500201")if (sonMainIdTypes != null)Iterator iter = sonMainIdTypes.entrySet().iterator();while (iter.hasNext()Map.Entry entry = (Map.Entry) iter.next();Object key = entry.getKey();Object val = entry.getValue();rowid = createce

43、ll(rowid, key.toString(), val.toString(), xt, sheet);private static HashMap<String, String> getHMctroWord(String cw_type) throws SQLExceptionHashMap<String, String> ctrlwordDic = new HashMap<String, String>();SqlQuery sqlQuery = new SqlQuery();QueryTable queryTable = new QueryTable

44、("CMP_CTRLWORD", "T");sqlQuery.queryTable = queryTable;sqlQuery.resultFields = "CW_ID,CW_NAME"SqlCondition condition = new SqlCondition();condition.getSqlConditions().add(new SqlCondition("CW_TYPE", cw_type, SqlLogicType.And, SqlRelationType.In, SqlParamType.N

45、umeric);sqlQuery.conditions = condition.getSqlConditions().toArray(new SqlCondition );Document ds = sqlExecutor.fill(sqlQuery);List<Element> list = ds.getRootElement().selectNodes("Record");for (Element element : list)String cw_id = element.selectSingleNode("CW_ID").getText

46、();String cw_name = element.selectSingleNode("CW_NAME").getText();ctrlwordDic.put(cw_id, cw_name);return ctrlwordDic;private static HashMap<String, String> getMainSonIDAndType(String mainid, String suntype) throws ExceptionHashMap<String, String> sonMainIdTypes = new HashMap<

47、;String, String>();SqlQuery sqlQuery = new SqlQuery();QueryTable queryTable = new QueryTable("CM10_OBJECT", "T");sqlQuery.queryTable = queryTable;sqlQuery.resultFields = "OBJ_ID,OBJ_SUBTYPE"SqlCondition condition = new SqlCondition();condition.getSqlConditions().add(

48、new SqlCondition("NUM_VAL1", mainid, SqlLogicType.And, SqlRelationType.Equal, SqlParamType.Numeric);condition.getSqlConditions().add(new SqlCondition("OBJ_SUBTYPE", suntype, SqlLogicType.And, SqlRelationType.In, SqlParamType.Numeric);sqlQuery.conditions = condition.getSqlConditio

49、ns().toArray(new SqlCondition );Document ds = sqlExecutor.fill(sqlQuery);List<Element> list = ds.getRootElement().selectNodes("Record");for (Element element : list)String cw_id = element.selectSingleNode("OBJ_ID").getText();String cw_name = element.selectSingleNode("OB

50、J_SUBTYPE").getText();sonMainIdTypes.put(cw_id, cw_name);return sonMainIdTypes;public static int createcell(int rowid, String objid, String objtype, xExcelType xt, WritableSheet sheet)System.out.println("_" + rowid);int cellid = 0;int cellwihth = 20;tryif (xt.exShowType = 1 &&

51、 !(OldObjtype.equals(objtype)/ 不同类型时也加入表头addcelltitle = true;xmlStruct xs = xt.alltables.get(objtype);/ 获取主表if (xs = null)return rowid;if (xt.exShowType = 1 && rowid = 0) | addcelltitle)cellid = 0;/ 循环取主表字段表头int tl = xs.tableFiles.length;for (int i = 0; i < tl; i+)sheet.setColumnView(cell

52、id, cellwihth);if (i = 0 && xs.tableCalssCName.get(objtype) != null)sheet.addCell(new Label(cellid, rowid, "(" + xs.tableCalssCName.get(objtype) + ")" + xs.tableFilesi1, titleFormat);elsesheet.addCell(new Label(cellid, rowid, xs.tableFilesi1, titleFormat);cellid+;celltitl

53、enow = cellid;rowtitlenow = rowid;rowid+;SAXReader reader = new SAXReader();Document doc = cmpGetObjectData("operation", objid);System.out.println(doc.asXML();List<Element> datasorce = doc.selectNodes("/" + xt.rootName);cellid = 0;for (Element document : datasorce)/ 是否要表头if

54、 (xt.exShowType = 0)cellid = 0;/ 循环取字段表头int tl = xs.tableFiles.length;for (int i = 0; i < tl; i+)sheet.setColumnView(cellid, cellwihth);if (i = 0 && xs.tableCalssCName.get(objtype) != null)sheet.addCell(new Label(cellid, rowid, "(" + xs.tableCalssCName.get(objtype) + ")&quo

55、t; + xs.tableFilesi1, titleFormat);elsesheet.addCell(new Label(cellid, rowid, xs.tableFilesi1, titleFormat);cellid+;rowid+;/ 表内容cellid = 0;Element tableBaseNode = (Element) document.selectSingleNode(xs.tableName);/ System.out.println(tableBaseNode.asXML();/ 循环取字段内容int tl = xs.tableFiles.length;Strin

56、g filevalue = ""for (int i = 0; i < tl; i+)String filename = xs.tableFilesi0;filevalue = tableBaseNode.selectSingleNode(filename) = null ? "" : tableBaseNode.selectSingleNode(filename).getText();/ 分析是否为字典if (xs.tableFilesClassValue.containsKey(filename)filevalue = xs.tableFilesClassValue.get(filename).get(filevalue);if (filevalue = null)filevalue = ""sheet.addCell(new Label(cellid, rowid, filevalue);cellid+;if (xt.exShowType = 0)rowid+;cellvaluenow = cellid;boolean testc = true;/ * 分析从表内容if (testc)int tablec = 0;while (true)tablec+;if (xt.allta