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

用代码实现xml 文件/数据 转换为excel 文件。（java）-何潮背景：最近项目要做导出功能，但导出的数据对象类型实在太多了，一个个去实现；实在是没心情去做。于是-意义：快速实现数据导出为什么是xml to excel?因为项目中可以直接使用xml数据。所以就选择xml了。（直接数据库取数据转excel,也可以按同一思路实现）目标：从一个最多二级关联的数据结构，通过定义结构关系，实现数据自动转换成excel.特点：支持字典字段转换，可合并多关联字段，三种效果展示，有一定的可复用性 _(最多二级关联-当然，可自行扩展)转换效果：实现三种显示方式的转换方式1：一行显示一个完整对象（合并多个关联表）方式2：复合形展示方式3：一行显示一个完整对象（合并多个关联表）-支持不同对象不同表头复用实现一个简单导出要多少代码？xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = CM10_OBJECT;mainXmlStruct1.tableCName = 活动信息;mainXmlStruct1.tableCalssField = OBJ_SUBTYPE;mainXmlStruct1.tableCalssCName.put(200101, 院团大事);HashMap test1 = new HashMap();test1.put(0, 新建);test1.put(100, 完成);mainXmlStruct1.tableFilesClassValue.put(OBJ_STATE, test1);mainXmlStruct1.tableFiles = new String OBJ_NAME, 活动名称 , FIELD_1, 活动内容简介 , FIELD_2, 活动开始时间 , FIELD_3, 活动结束时间 , OBJ_STATE, 状态 ;xt.alltables.put(200101, mainXmlStruct1);思路？定义结构 定义数据关系过程循环取表，循环取表头，循环取内容-class xExcelType/* xls显示方式0：组合式1：单一列表式 */int exShowType = 0;String rootName = DataSource;String maintableName = CM10_OBJECT;HashMap alltables = new HashMap();/* 定义xml数据结构定义 */class xmlStructString tableName = ;/ 表名String tableCName = ;/ 表名中文说明/* 表的性质0：主表（默认）1：从表-这里暂处理1级主从，哈 */int tableType = 0;String tableCalssField = ;/ 如果有内容分类，分类标识字段String tableCalssFieldValue = ;HashMap tableCalssCName = new HashMap();/* 表的字典字段及对应值 */HashMapString, HashMap tableFilesClassValue = new HashMapString, HashMap();/* 表字段中文对应 */ HashMap tableFiles = new HashMap();String tableFiles;理论上通用，罪过。不多说，有可能用到的，下附件看看。不合理的地方请指出-下面是完整代码（一些特殊引用只是用作数据取数据，引用时可以去掉的-）package jetsennet.jmcc.business;import java.io.File;import java.io.IOException;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 mon.PathUtil;import jetsennet.contentmanage.CmDataAccess;import jetsennet.contentmanage.CmObjHelper;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 jetsennet.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.WritableCellFormat;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.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.getProperty(cmp_dbpwd);static jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.createFont(宋体), 10, WritableFont.BOLD, false);static WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(titleFont);private static ConnectionInfo mccConnectionString = new ConnectionInfo(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 celltitlenow = 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 = Workbook.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 catch 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(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;elseaddcelltitle = false;/ *主表/结构定义/ 200101xmlStruct mainXmlStruct1 = new xmlStruct();mainXmlStruct1.tableName = CM10_OBJECT;mainXmlStruct1.tableCName = 活动信息;mainXmlStruct1.tableCalssField = OBJ_SUBTYPE;mainXmlStruct1.tableCalssCName.put(200101, 院团大事);mainXmlStruct1.tableCalssCName.put(200102, 院团出访);mainXmlStruct1.tableCalssCName.put(200103, 院团专家交流);HashMap test1 = new HashMap();test1.put(0, 新建);test1.put(100, 完成);mainXmlStruct1.tableFilesClassValue.put(OBJ_STATE, test1);mainXmlStruct1.tableFiles = new String OBJ_NAME, 活动名称 , FIELD_1, 活动内容简介 , FIELD_2, 活动开始时间 , FIELD_3, 活动结束时间 ;xt.alltables.put(200101, mainXmlStruct1);xt.alltables.put(200102, mainXmlStruct1);xt.alltables.put(200103, mainXmlStruct1);/ 200104xmlStruct mainXmlStruct2 = new xmlStruct();mainXmlStruct2.tableName = CM10_OBJECT;mainXmlStruct2.tableCName = 活动信息;mainXmlStruct2.tableCalssField = OBJ_SUBTYPE;mainXmlStruct2.tableCalssCName.put(200104, 院团演出);mainXmlStruct2.tableFiles = new String OBJ_NAME, 活动名称 , FIELD_2, 活动开始时间 , FIELD_3, 活动结束时间 ;xt.alltables.put(200104, mainXmlStruct2);/ 20010401xmlStruct mainXmlStruct3 = new xmlStruct();mainXmlStruct3.tableName = CM10_OBJECT;mainXmlStruct3.tableCalssField = OBJ_SUBTYPE;mainXmlStruct3.tableCalssCName.put(20010401, 演出日志);/ HashMap showType_hm = getHMctroWord(20010401);/ mainXmlStruct1.tableFilesClassValue.put(FIELD_3, showType_hm);mainXmlStruct3.tableFiles = new String FIELD_1, 演出时间 , FIELD_2, 演出场次 , FIELD_3, 演出类型 , NUM_VAL2, 观众人数 ;xt.alltables.put(20010401, mainXmlStruct3);/ *从表/结构定义/xmlStruct son1Struct1 = new xmlStruct();son1Struct1.tableName = CM10_MULTIINFO;son1Struct1.tableCName = 动信息;son1Struct1.tableCalssField = INFO_TYPE;son1Struct1.tableCalssFieldValue = 1;/ HashMap test2 = new HashMap();/ test2.put(1, 新建1);/ test2.put(2, 完成2);/ test2.put(3, 完成3);/ xs2.tableFilesClassValue.put(INFO_TYPE, test2);son1Struct1.tableFiles = 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;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, son1Struct2);xt.alltables.put(200103-2, son1Struct2);/xmlStruct son1Struct3 = new xmlStruct();son1Struct3.tableName = CM10_MULTIINFO;son1Struct3.tableCName = 剧目信息;son1Struct3.tableCalssField = INFO_TYPE;son1Struct3.tableCalssFieldValue = 3;son1Struct3.tableFiles = 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;son2Struct1.tableCalssField = INFO_TYPE;son2Struct1.tableCalssFieldValue = 1;son2Struct1.tableFiles = new String FIELD_1, 节目单 ;xt.alltables.put(200104-1, son2Struct1);xmlStruct son2Struct2 = new xmlStruct();son2Struct2.tableName = CM10_MULTIINFO;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 = 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();son3Struct1.tableName = CM10_MULTIINFO;son3Struct1.tableCalssField = INFO_TYPE;son3Struct1.tableCalssFieldValue = 1;son3Struct1.tableFiles = new String FIELD_1, 演出单位 ;xt.alltables.put(20010401-1, son3Struct1);xmlStruct son3Struct2 = new xmlStruct();son3Struct2.tableName = CM10_MULTIINFO;son3Struct2.tableCalssField = INFO_TYPE;son3Struct2.tableCalssFieldValue = 2;son3Struct2.tableFiles = new String FIELD_1, 演剧目名称 ;xt.alltables.put(20010401-2, son3Struct2);xmlStruct son3Struct3 = new xmlStruct();son3Struct3.tableName = CM10_MULTIINFO;son3Struct3.tableCalssField = INFO_TYPE;son3Struct3.tableCalssFieldValue = 3;son3Struct3.tableFiles = new String FIELD_1, 演出地点 ;xt.alltables.put(20010401-3, son3Struct3);xmlStruct son3Struct4 = new xmlStruct();son3Struct4.tableName = CM10_MULTIINFO;son3Struct4.tableCalssField = INFO_TYPE;son3Struct4.tableCalssFieldValue = 4;/ son3Struct4.tableFilesClassValue.put(FIELD_2, getHMctroWord(200104011);son3Struct4.tableFiles = 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 objtype = idtidi;HashMap sonMainIdTypes = null;if (objtype.equals(200101) | objtype.equals(200102) | objtype.equals(200103)/ 院团活动 常用else if (objtype.equals(200104)/ 院团活动 演出sonMainIdTypes = getMainSonIDAndType(idsidi, 20010401);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)/ 优秀剧目展演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 = createcell(rowid, key.toString(), val.toString(), xt, sheet);private static HashMap getHMctroWord(String cw_type) throws SQLExceptionHashMap ctrlwordDic = new HashMap();SqlQuery sqlQuery = new SqlQuery();QueryTable queryTable = new QueryTable(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.Numeric);sqlQuery.conditions = condition.getSqlConditions().toArray(new SqlCondition );Document ds = sqlExecutor.fill(sqlQuery);List list = ds.getRootElement().selectNodes(Record);for (Element element : list)String cw_id = element.selectSingleNode(CW_ID).getText();String cw_name = element.selectSingleNode(CW_NAME).getText();ctrlwordDic.put(cw_id, cw_name);return ctrlwordDic;private static HashMap getMainSonIDAndType(String mainid, String suntype) throws ExceptionHashMap sonMainIdTypes = new HashMap();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(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.getSqlConditions().toArray(new SqlCondition );Document ds = sqlExecutor.fill(sqlQuery);List list = ds.getRootElement().selectNodes(Record);for (Element element : list)String cw_id = element.selectSingleNode(OBJ_ID).getText();String cw_name = element.selectSingleNode(OBJ_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 & !(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(cellid, 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+;celltitlenow = cellid;rowtitlenow = rowid;rowid+;SAXReader reader = new SAXReader();Document doc = cmpGetObjectData(operation, objid);System.out.println(doc.asXML();List datasorce = doc.selectNodes(/ + xt.rootName);cellid = 0;for (Element document : datasorce)/ 是否要表头if (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) + ) + 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;String 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;/