POI读取excel转换成XML
来源：互联网 发布：网络cv设备 编辑：程序博客网 时间：2024/05/18 19:42
前期准备
-eclipse
-poi-3.16 jar包
-jdom-2.0.6 jar包
-xmlbeans-2.3.0 jar包
业务需要
测试系统时遇到一个小问题时，系统采用xml导入批量数据，且xml模板中存在一对一（a和b）和一对多（a和c）关系，同时在系统业务中，存在随机抽取的流程操作，xml模板导入的部分数据a需要从oracle中提取，本人通过excel模板编辑数据a和b，通过部分字段来控制变量便于测试，以下代码把excel转换成xml。
注意：这是本人第一次写导入excel代码，勿喷，欢迎交流
注意：本文仅适用于与c的数目相同的转换
注意：此代码只依据第一个listEnt来获取C对应的n（即item的数目）
EXCEL模板
读取的listEnt则是由A+B+C*n组成
代码
-引用的jar包
import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.dom4j.Document;import org.dom4j.DocumentHelper;import org.dom4j.Element;import org.dom4j.io.OutputFormat;import org.dom4j.io.XMLWriter;-主代码
public class excelToXML { public static void main(String[] args) { //仅适用于一个三级产品目录一个产品分类，若存在多个产品分类，请转换成XML后手动增加 try { excel2XML("D:\\18_FOR_TEST\\input.xlsx");// 读取excel的路径 System.out.println("XML啦啦啦啦"); } catch (IOException e) { e.printStackTrace(); System.out.println("读取excel失败"); } } @SuppressWarnings("resource") public static void excel2XML(String filename) throws IOException { InputStream input = new FileInputStream(filename); Workbook wb = new XSSFWorkbook(input); Sheet sheet = wb.getSheetAt(0); // 获得第一个表单 int rowCount = sheet.getPhysicalNumberOfRows();// 获取总行数 List<String> listEach = new ArrayList<String>(); // 获取ItemNum并赋值 int it = 0, itemNum = 0; for (int r = 0; r < rowCount; r++) { // 判断是否两行的数据是否相同 if (r < 1) continue; if (sheet.getRow(r - 1).getCell(1).toString().equals(sheet.getRow(r).getCell(1).toString())) { it++; } else { itemNum = it + 1; break; } } List<List<String>> listAllRow = new ArrayList<List<String>>();// 中转-存放所有row的数据 for (int r = 0; r < rowCount; r++) { Row row = sheet.getRow(r); int rowNum = row.getRowNum(); if (rowNum % itemNum == 0) { listEach = saveAllCell(row); } else { listEach = saveItemCell(row); } listAllRow.add(listEach); } // 切割listAllRow 把每个企业的A、B和C数据放在一个listEnt里面 List<List<String>> listEnt = new ArrayList<List<String>>();// 中转-存放单个企业的数据 List<List<List<String>>> listAll = new ArrayList<List<List<String>>>();// 存放所有row的数据 for (int i = 0; i < rowCount / itemNum; i++) { listEnt = listAllRow.subList(itemNum * i, itemNum * (i + 1)); listAll.add(listEnt); } // 开始创建XML Document document = DocumentHelper.createDocument(); Element datas = document.addElement("datas");// 创建根节点 // 逐级创建节点 for (int i = 0; i < listAll.size(); i++) { Element data = datas.addElement("data");// 创建data节点 for (int j = 0; j < itemNum; j++) { // 用集合的长度判断是 allCell数据（59）还是item数据（9） if (listAll.get(i).get(j).size() > 50) { createSonNode(data, listAll.get(i).get(j)); } else { createItemNode(data, listAll.get(i).get(j)); } } } OutputFormat format = OutputFormat.createPrettyPrint(); format.setEncoding("UTF-8");//gb2312 File file = new File("D:\\18_FOR_TEST\\OUTPUT.xml");// 输出xml的路径 XMLWriter writer; try { writer = new XMLWriter(new FileOutputStream(file), format); // 设置是否转义，默认是true，代表转义 writer.setEscapeText(false); writer.write(document); writer.close(); } catch (IOException e) { e.printStackTrace(); } } // 创建A和B结点 public static void createSonNode(Element data, List<String> listEachRow) { Element JDCC_TEMCYDB = data.addElement("JDCC_TEMCYDB");// A节点 Element CYDBH = JDCC_TEMCYDB.addElement("CYDBH");// A编号 CYDBH.setText("<![CDATA[" + listEachRow.get(0) + "]]>"); Element SJDWMC = JDCC_TEMCYDB.addElement("SJDWMC");// SJDWMC.setText("<![CDATA[" + listEachRow.get(1) + "]]>"); Element SJDWDZ = JDCC_TEMCYDB.addElement("SJDWDZ");// SJDWDZ.setText("<![CDATA[" + listEachRow.get(2) + "]]>"); Element SJDWFRDB = JDCC_TEMCYDB.addElement("SJDWFRDB");// SJDWFRDB.setText("<![CDATA[" + listEachRow.get(3) + "]]>"); Element SJDWLXR = JDCC_TEMCYDB.addElement("SJDWLXR");// SJDWLXR.setText("<![CDATA[" + listEachRow.get(4) + "]]>"); Element SJDWLXRDH = JDCC_TEMCYDB.addElement("SJDWLXRDH");// SJDWLXRDH.setText("<![CDATA[" + listEachRow.get(5) + "]]>"); Element SJDWYYZZ = JDCC_TEMCYDB.addElement("SJDWYYZZ");// SJDWYYZZ.setText("<![CDATA[" + listEachRow.get(6) + "]]>"); Element SJDWJGDM = JDCC_TEMCYDB.addElement("SJDWJGDM");// SJDWJGDM.setText("<![CDATA[" + listEachRow.get(7) + "]]>"); Element SJDWYZBM = JDCC_TEMCYDB.addElement("SJDWYZBM");// SJDWYZBM.setText("<![CDATA[" + listEachRow.get(8) + "]]>"); Element SCDWMC = JDCC_TEMCYDB.addElement("SCDWMC");// SCDWMC.setText("<![CDATA[" + listEachRow.get(9) + "]]>"); Element SCDWDZ = JDCC_TEMCYDB.addElement("SCDWDZ");// SCDWDZ.setText("<![CDATA[" + listEachRow.get(10) + "]]>"); Element SCDWLXDH = JDCC_TEMCYDB.addElement("SCDWLXDH");// SCDWLXDH.setText("<![CDATA[" + listEachRow.get(11) + "]]>"); Element SCDWYZBM = JDCC_TEMCYDB.addElement("SCDWYZBM");// SCDWYZBM.setText("<![CDATA[" + listEachRow.get(12) + "]]>"); Element SCDWFRDB = JDCC_TEMCYDB.addElement("SCDWFRDB");// SCDWFRDB.setText("<![CDATA[" + listEachRow.get(13) + "]]>"); Element SCDWLXR = JDCC_TEMCYDB.addElement("SCDWLXR");// SCDWLXR.setText("<![CDATA[" + listEachRow.get(14) + "]]>"); Element SCDWYYZZ = JDCC_TEMCYDB.addElement("SCDWYYZZ");// SCDWYYZZ.setText("<![CDATA[" + listEachRow.get(15) + "]]>"); Element SCDWJGDM = JDCC_TEMCYDB.addElement("SCDWJGDM");// SCDWJGDM.setText("<![CDATA[" + listEachRow.get(16) + "]]>"); Element QYGM = JDCC_TEMCYDB.addElement("QYGM"); QYGM.setText("<![CDATA[" + listEachRow.get(17) + "]]>"); Element JJLX = JDCC_TEMCYDB.addElement("JJLX"); JJLX.setText("<![CDATA[" + listEachRow.get(18) + "]]>"); Element ZSBH = JDCC_TEMCYDB.addElement("ZSBH"); ZSBH.setText("<![CDATA[" + listEachRow.get(19) + "]]>"); Element CPMC = JDCC_TEMCYDB.addElement("CPMC"); CPMC.setText("<![CDATA[" + listEachRow.get(20) + "]]>"); Element CPGGXH = JDCC_TEMCYDB.addElement("CPGGXH"); CPGGXH.setText("<![CDATA[" + listEachRow.get(21) + "]]>"); Element SCRQ = JDCC_TEMCYDB.addElement("SCRQ"); SCRQ.setText("<![CDATA[" + listEachRow.get(22) + "]]>"); Element SCPH = JDCC_TEMCYDB.addElement("SCPH"); SCPH.setText("<![CDATA[" + listEachRow.get(23) + "]]>"); Element SB = JDCC_TEMCYDB.addElement("SB"); SB.setText("<![CDATA[" + listEachRow.get(24) + "]]>"); Element CYSL = JDCC_TEMCYDB.addElement("CYSL"); CYSL.setText("<![CDATA[" + listEachRow.get(25) + "]]>"); Element CPDJ = JDCC_TEMCYDB.addElement("CPDJ"); CPDJ.setText("<![CDATA[" + listEachRow.get(26) + "]]>"); Element CYJS = JDCC_TEMCYDB.addElement("CYJS"); CYJS.setText("<![CDATA[" + listEachRow.get(27) + "]]>"); Element BZZXBZ = JDCC_TEMCYDB.addElement("BZZXBZ"); BZZXBZ.setText("<![CDATA[" + listEachRow.get(28) + "]]>"); Element FYZT = JDCC_TEMCYDB.addElement("FYZT"); FYZT.setText("<![CDATA[" + listEachRow.get(29) + "]]>"); Element BYLJFCDD = JDCC_TEMCYDB.addElement("BYLJFCDD"); BYLJFCDD.setText("<![CDATA[" + listEachRow.get(30) + "]]>"); Element JSYDD = JDCC_TEMCYDB.addElement("JSYDD"); JSYDD.setText("<![CDATA[" + listEachRow.get(31) + "]]>"); Element JSYJZRQ = JDCC_TEMCYDB.addElement("JSYJZRQ"); JSYJZRQ.setText("<![CDATA[" + listEachRow.get(32) + "]]>"); Element SCPL = JDCC_TEMCYDB.addElement("SCPL"); SCPL.setText("<![CDATA[" + listEachRow.get(33) + "]]>"); Element CPDJWY = JDCC_TEMCYDB.addElement("CPDJWY"); CPDJWY.setText("<![CDATA[" + listEachRow.get(34) + "]]>"); Element HGDXCP = JDCC_TEMCYDB.addElement("HGDXCP"); HGDXCP.setText("<![CDATA[" + listEachRow.get(35) + "]]>"); Element PLZHZWY = JDCC_TEMCYDB.addElement("PLZHZWY"); PLZHZWY.setText("<![CDATA[" + listEachRow.get(36) + "]]>"); Element QTXX = JDCC_TEMCYDB.addElement("QTXX"); QTXX.setText("<![CDATA[" + listEachRow.get(37) + "]]>"); Element BZ = JDCC_TEMCYDB.addElement("BZ"); BZ.setText("<![CDATA[" + listEachRow.get(38) + "]]>"); Element SJDWYJ = JDCC_TEMCYDB.addElement("SJDWYJ"); SJDWYJ.setText("<![CDATA[" + listEachRow.get(39) + "]]>"); Element CYDWYJ = JDCC_TEMCYDB.addElement("CYDWYJ"); CYDWYJ.setText("<![CDATA[" + listEachRow.get(40) + "]]>"); Element CYDD = JDCC_TEMCYDB.addElement("CYDD"); CYDD.setText("<![CDATA[" + listEachRow.get(41) + "]]>"); Element JDCC_TEMJYBGB = data.addElement("JDCC_TEMJYBGB");// B节点 Element JYBGBH = JDCC_TEMJYBGB.addElement("JYBGBH");// B编号 JYBGBH.setText("<![CDATA[" + listEachRow.get(42) + "]]>"); Element CPBH = JDCC_TEMJYBGB.addElement("CPBH"); CPBH.setText("<![CDATA[" + listEachRow.get(43) + "]]>"); Element CYRQ = JDCC_TEMJYBGB.addElement("CYRQ"); CYRQ.setText("<![CDATA[" + listEachRow.get(44) + "]]>"); Element CYRY = JDCC_TEMJYBGB.addElement("CYRY"); CYRY.setText("<![CDATA[" + listEachRow.get(45) + "]]>"); Element YPDDRQ = JDCC_TEMJYBGB.addElement("YPDDRQ"); YPDDRQ.setText("<![CDATA[" + listEachRow.get(46) + "]]>"); Element JYFYRY = JDCC_TEMJYBGB.addElement("JYFYRY"); JYFYRY.setText("<![CDATA[" + listEachRow.get(47) + "]]>"); Element YPDJ = JDCC_TEMJYBGB.addElement("YPDJ"); YPDJ.setText("<![CDATA[" + listEachRow.get(48) + "]]>"); Element FYZT1 = JDCC_TEMJYBGB.addElement("FYZT"); FYZT1.setText("<![CDATA[" + listEachRow.get(49) + "]]>"); Element YQRQ = JDCC_TEMJYBGB.addElement("YQRQ"); YQRQ.setText("<![CDATA[" + listEachRow.get(50) + "]]>"); Element SFHG = JDCC_TEMJYBGB.addElement("SFHG"); SFHG.setText("<![CDATA[" + listEachRow.get(51) + "]]>"); Element JYYJ = JDCC_TEMJYBGB.addElement("JYYJ"); JYYJ.setText("<![CDATA[" + listEachRow.get(52) + "]]>"); Element JYJL = JDCC_TEMJYBGB.addElement("JYJL"); JYJL.setText("<![CDATA[" + listEachRow.get(53) + "]]>"); Element BZ1 = JDCC_TEMJYBGB.addElement("BZ"); BZ1.setText("<![CDATA[" + listEachRow.get(54) + "]]>"); Element PZR = JDCC_TEMJYBGB.addElement("PZR"); PZR.setText("<![CDATA[" + listEachRow.get(55) + "]]>"); Element SHR = JDCC_TEMJYBGB.addElement("SHR"); SHR.setText("<![CDATA[" + listEachRow.get(56) + "]]>"); Element ZJR = JDCC_TEMJYBGB.addElement("ZJR"); ZJR.setText("<![CDATA[" + listEachRow.get(57) + "]]>"); // 移除已遍历的A和B数据 for (int i = 0; i < 58; i++) { listEachRow.remove(0); } createItemNode(data, listEachRow); } // 创建C结点 public static void createItemNode(Element data, List<String> listEachRow) { Element JDCC_TEMJYXMB = data.addElement("JDCC_TEMJYXMB");// C节点 Element MC = JDCC_TEMJYXMB.addElement("MC"); MC.setText("<![CDATA[" + listEachRow.get(0) + "]]>"); Element BZZ = JDCC_TEMJYXMB.addElement("BZZ"); BZZ.setText("<![CDATA[" + listEachRow.get(1) + "]]>"); Element JCZ = JDCC_TEMJYXMB.addElement("JCZ"); JCZ.setText("<![CDATA[" + listEachRow.get(2) + "]]>"); Element DXPD = JDCC_TEMJYXMB.addElement("DXPD"); DXPD.setText("<![CDATA[" + listEachRow.get(3) + "]]>"); Element DW = JDCC_TEMJYXMB.addElement("DW"); DW.setText("<![CDATA[" + listEachRow.get(4) + "]]>"); Element ZHPD = JDCC_TEMJYXMB.addElement("ZHPD"); ZHPD.setText("<![CDATA[" + listEachRow.get(5) + "]]>"); Element ZYCD = JDCC_TEMJYXMB.addElement("ZYCD"); ZYCD.setText("<![CDATA[" + listEachRow.get(6) + "]]>"); Element SFSY = JDCC_TEMJYXMB.addElement("SFSY"); SFSY.setText("<![CDATA[" + listEachRow.get(7) + "]]>"); } // 获取C的值 public static List<String> saveItemCell(Row row) { List<String> listPart = new ArrayList<String>(); // c获取改行的所有cell数据 for (int c = 0; c < 66; c++) { Cell cell = row.getCell(c); //在allCell的数据 只遍历 不取值 if (cell.getColumnIndex() < 58) { continue; } String a = getCellValue(cell); listPart.add(a); } return listPart; } // 获取整行的值 public static List<String> saveAllCell(Row row) { List<String> listAll = new ArrayList<String>(); // c获取改行的所有cell数据 for (int c = 0; c < 66; c++) { Cell cell = row.getCell(c); String a = getCellValue(cell); listAll.add(a); } return listAll; } // 判断cell类型 取值 public static String getCellValue(Cell cell) { if (cell.getCellTypeEnum() == CellType.BLANK) {// 为空则返回空格 //if (cell==null || cell.toString().trim().equals("")) { //cell.setCellValue("空白辅助测试"); return " "; } if (cell.getCellTypeEnum() == CellType.NUMERIC) { if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString()) || "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString()) || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString()) || "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())) { String cal = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue()); return cal; } else { cell.setCellType(CellType.STRING); return cell.toString(); } } if (cell.getCellTypeEnum() == CellType.STRING) { return cell.toString().replaceAll("[\\t\\n\\r]", ""); } return "没有判断就返回"; }附上参考的blog文地址
阅读全文
1 0
- POI读取excel转换成XML
- 使用 apache poi 读取excel 把 数字转换成 字符
- 读取excel（POI）【转换为html】
- POI读取EXCEL，转换HTML，图片，附件
- Java 使用最新POI Lib 将Excel转换成Txt读取Excel内容工具类
- 使用Apache POI读取Excel文件，将读取的数据转换成json写入文本文件
- POI-----POI读取Excel实例
- 使用POI读取Excel时如何把数字转换成字符串
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换。
- POI读取Excel 各种特殊数字和类型的转换
- POI读取Excel 各种特殊数字和类型的转换。
- POI读取EXCEL
- POI读取EXCEL教程
- POI读取EXCEL
- poi 读取excel
- 通过POI读取Excel
- json详解
- QWT编译、配置、使用
- 关于xlsx转成json的工具（记录后续使用）
- vue 的全局API
- [Python]cannot import name IntEnum
- POI读取excel转换成XML
- MySQL 第五天
- object转化成json,json格式字符串转字典,数组或字典转为json串
- usb转串口异步读取数据
- 背包DP合辑
- 某外企C++面试题
- Android BLE 开发资料汇总
- Spark机器学习之协同过滤算法使用-Java篇
- vue-router（1）
