代碼快速 實(shí)現(xiàn)xml 轉(zhuǎn)換為 Excel（xml轉(zhuǎn)excel通用類-java-完成代碼可作工具使用）

1、用代碼實(shí)現(xiàn)xml 文件/數(shù)據(jù) 轉(zhuǎn)換為excel 文件。（java）-何潮背景：最近項(xiàng)目要做導(dǎo)出功能，但導(dǎo)出的數(shù)據(jù)對象類型實(shí)在太多了，一個個去實(shí)現(xiàn)；實(shí)在是沒心情去做。于是-意義：快速實(shí)現(xiàn)數(shù)據(jù)導(dǎo)出為什么是xml to excel?因?yàn)轫?xiàng)目中可以直接使用xml數(shù)據(jù)。所以就選擇xml了。（直接數(shù)據(jù)庫取數(shù)據(jù)轉(zhuǎn)excel,也可以按同一思路實(shí)現(xiàn)）目標(biāo)：從一個最多二級關(guān)聯(lián)的數(shù)據(jù)結(jié)構(gòu)，通過定義結(jié)構(gòu)關(guān)系，實(shí)現(xiàn)數(shù)據(jù)自動轉(zhuǎn)換成excel.特點(diǎn)：支持字典字段轉(zhuǎn)換，可合并多關(guān)聯(lián)字段，三種效果展示，有一定的可復(fù)用性 _(最多二級關(guān)聯(lián)-當(dāng)然，可自行擴(kuò)展)轉(zhuǎn)換效果：實(shí)現(xiàn)三種顯示方式的轉(zhuǎn)換方式1：一行顯示一個完整對象（合并多個關(guān)

2、聯(lián)表）方式2：復(fù)合形展示方式3：一行顯示一個完整對象（合并多個關(guān)聯(lián)表）-支持不同對象不同表頭復(fù)用實(shí)現(xiàn)一個簡單導(dǎo)出要多少代碼？xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = "CM10_OBJECT"mainXmlStruct1.tableCName = "活動信息"mainXmlStruct1.tableCalssField = "OBJ_SUBTYPE"mainXmlStruct1.tableCalssCName.put("2001

3、01", "院團(tuán)大事");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", "活動內(nèi)容簡介" , "FIELD_2", "活動開始時間" , "FIELD_3", "活動結(jié)束時間" , "OBJ_STATE", "狀態(tài)" ;xt.alltables.put("200101", mainXmlStruct1);思路？定義結(jié)構(gòu) 定義數(shù)據(jù)關(guān)系過程循環(huán)取表，循環(huán)取表頭，循環(huán)取內(nèi)容-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數(shù)據(jù)結(jié)構(gòu)定義 */class xmlStructString tableName = ""/ 表名String tableCNam

6、e = ""/ 表名中文說明/* 表的性質(zhì)0：主表（默認(rèn)）1：從表-這里暫處理1級主從，哈 */int tableType = 0;String tableCalssField = ""/ 如果有內(nèi)容分類，分類標(biāo)識字段String tableCalssFieldValue = ""HashMap<String, String> tableCalssCName = new HashMap<String, String>();/* 表的字典字段及對應(yīng)值 */HashMap<String, HashMap<

7、;String, String>> tableFilesClassValue = new HashMap<String, HashMap<String, String>>();/* 表字段中文對應(yīng) */ HashMap<String, String> tableFiles = new HashMap<String, String>();String tableFiles;理論上通用，罪過。不多說，有可能用到的，下附件看看。不合理的地方請指出-下面是完整代碼（一些特殊引用只是用作數(shù)據(jù)取數(shù)據(jù)，引用時可以去掉的-）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;/ 當(dāng)前列表頭列號static int cellvaluenow = 0;/ 當(dāng)前內(nèi)容列號static int rowtitlenow = 0;/ 當(dāng)前列表頭行號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);/*/* 導(dǎo)出ID */String ids = obj_id.split(",");/* 導(dǎo)出類型 */String idt = obj_subtype.split(",");/* 導(dǎo)出個數(shù) */int idsl = ids.length;/* 定義列寬 */int cellwihth = 20;xExcelType xt = new xExcelType();xt.exShowType = 1;if (xt.exShowType = 1)addcelltitle = true;elseaddcelltitl

19、e = false;/ *主表/結(jié)構(gòu)定義/ 200101xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = "CM10_OBJECT"mainXmlStruct1.tableCName = "活動信息"mainXmlStruct1.tableCalssField = "OBJ_SUBTYPE"mainXmlStruct1.tableCalssCName.put("200101", "院團(tuán)大事");mainXm

20、lStruct1.tableCalssCName.put("200102", "院團(tuán)出訪");mainXmlStruct1.tableCalssCName.put("200103", "院團(tuán)專家交流");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", "活動內(nèi)容簡介" , "FIELD_2", "活動開始時間" , "FIELD_3", "活動結(jié)束時間" ;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", "院團(tuán)演出");mainXmlStruct2.tableFiles = new String "OBJ_NAME", "活動名稱" , "FIELD_2", "活動開始時間" , "FIELD_3", "活動結(jié)束時間" ;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;觀眾人數(shù)" ;xt.alltables.put("20010401", mainXmlStruct3);/ *從表/結(jié)構(gòu)定義/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", "活動地點(diǎn)" ;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", "節(jié)目單" ;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);/ 演出的日志多關(guān)聯(lián)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", "演出地點(diǎn)" ;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", "責(zé)任方式" ;xt.alltables.put("20010401-4", son3Struct4);/ /下面是統(tǒng)一生成處理int rowid = 0;int cellid = 0;for (int idi = 0; idi < idsl; idi+)rowid = createcell(rowid, idsidi, idtidi, xt, sheet);/ 額外加入xml結(jié)構(gòu)String ob

40、jtype = idtidi;HashMap<String, String> sonMainIdTypes = null;if (objtype.equals("200101") | objtype.equals("200102") | objtype.equals("200103")/ 院團(tuán)活動 常用else if (objtype.equals("200104")/ 院團(tuán)活動 演出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")/ 優(yōu)秀劇

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;/ 循環(huán)取主表字段表頭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;/ 循環(huán)取字段表頭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+;/ 表內(nèi)容cellid = 0;Element tableBaseNode = (Element) document.selectSingleNode(xs.tableName);/ System.out.println(tableBaseNode.asXML();/ 循環(huán)取字段內(nèi)容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;/ * 分析從表內(nèi)容if (testc)int tablec = 0;while (true)tablec+;if (xt.allta