java读取Excel文件以及转为XML文件格式

java读取Excel文件以及转为XML文件格式

1. 导入需要的jar包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2. 读取excel文件

    /**
     * 读取Excel文件的内容
     *
     * @param inputStream excel文件，以InputStream的形式传入
     * @param sheetName   sheet名字
     * @return 以List返回excel中内容
     */
    public static List<Map<String, String>> readExcel(InputStream inputStream, String sheetName) {
        //定义工作簿
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            System.out.println("Excel data file cannot be found!");
        }
        //定义工作表
        XSSFSheet xssfSheet;
        if ("".equals(sheetName)) {
            // 默认取第一个子表
            assert xssfWorkbook != null;
            xssfSheet = xssfWorkbook.getSheetAt(0);
        } else {
            assert xssfWorkbook != null;
            xssfSheet = xssfWorkbook.getSheet(sheetName);
        }
        List<Map<String, String>> list = new ArrayList<>();
		int maxRow = xssfSheet.getLastRowNum();
        //默认第一行为标题行，index = 0
        XSSFRow titleRow = xssfSheet.getRow(0);
//        System.out.println("总行数为：" + maxRow);
        for (int row = 1; row <= maxRow; row++) {
            XSSFRow xss = xssfSheet.getRow(row);
            if (xss != null) {
                int maxRol = xssfSheet.getRow(row).getLastCellNum();
//                System.out.println("--------第" + row + "行的数据如下--------");
                Map<String, String> map = new LinkedHashMap<String, String>();
                for (int rol = 0; rol < maxRol; rol++) {
                    XSSFCell cell = xssfSheet.getRow(row).getCell(rol);

                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        cell.setCellType(CellType.STRING);
                        if (titleRow.getCell(rol) != null && cell.getCellType() != CellType.BLANK) {
                            map.put(titleRow.getCell(rol).getStringCellValue(), cell.getStringCellValue());
                        }
                    }
                }

                list.add(map);
            }
        }

        int i = 1;
        for (Map<String, String> map1 : list) {
            System.out.print(i+" ");
            for (Object key : map1.keySet()) {
                System.out.print(" "+key+":"+map1.get(key));
            }
            System.out.println();
            i++;
        }
        return list;
    }

3. 生成xml文件

    private static void CreateXML(Map<String, String> map) throws IOException {

       StringBuffer sb = new StringBuffer();
       sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
       sb.append("<assessmentItem adaptive=\"false\" identifier=\n" +
               "  timeDependent=\"false\" title=\"Unattended Luggage\"\n" +
               "  xmlns=\"http://www.imsglobal.org/xsd/imsqti_v2p2\"\n" +
               "  xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.imsglobal.org/xsd/imsqti_v2p2  http://www.imsglobal.org/xsd/qti/qtiv2p2/imsqti_v2p2p2.xsd\">").append("\n");

       sb.append("  <responseDeclaration baseType=\"string\" cardinality=\"single\" identifier=\"RESPONSE\">\n" +
                    "    <correctResponse>\n" +
                    "      <value>" + map.get("正确答案") + "</value>\n" +
                    "    </correctResponse>\n" +
                    "  </responseDeclaration>").append("\">\n")
                    .append("  <outcomeDeclaration baseType=\"float\" cardinality=\"single\" identifier=\"SCORE\">\n" +
                    "    <defaultValue>\n" +
                    "      <value>0</value>\n" +
                    "    </defaultValue>\n" +
                    "  </outcomeDeclaration>").append("\">\n");

        sb.append("  <itemBody>\n" +
                "    <blockquote>\n" +
                "      <img src=\"" + map.get("图片地址") + "\"/>\n" +
                "    </blockquote>\n" +
                "    <choiceInteraction maxChoices=\"1\" responseIdentifier=\"RESPONSE\" shuffle=\"false\">\n" +
                "      <prompt>" + map.get("题干") + "\n" +
                "      <simpleChoice identifier=\"A\">" + map.get("A") + "</simpleChoice>\n" +
                "      <simpleChoice identifier=\"B\">" + map.get("B") + "</simpleChoice>\n" +
                "      <simpleChoice identifier=\"C\">" + map.get("C") + "</simpleChoice>\n" +
                "    </choiceInteraction>\n" +
                "    <actionInteraction/>\n" +
                "  </itemBody>").append("\n");

        sb.append("  <responseProcessing>\n" +
                "    <responseCondition>\n" +
                "      <responseIf>\n" +
                "        <match>\n" +
                "          <variable identifier=\"RESPONSE\"/>\n" +
                "          <correct identifier=\"RESPONSE\"/>\n" +
                "        </match>\n" +
                "        <setOutcomeValue identifier=\"SCORE\">\n" +
                "          <baseValue baseType=\"float\">1</baseValue>\n" +
                "        </setOutcomeValue>\n" +
                "      </responseIf>\n" +
                "      <responseElse>\n" +
                "        <setOutcomeValue identifier=\"SCORE\">\n" +
                "          <baseValue baseType=\"float\">0</baseValue>\n" +
                "        </setOutcomeValue>\n" +
                "      </responseElse>\n" +
                "    </responseCondition>\n" +
                "  </responseProcessing>").append("\n");
        sb.append("</assessmentItem>");
        System.out.println(sb.toString());
//            生成xml文件
        File file = new File("D:\\XML\\"  + resourceId + ".xml");
        if (!file.exists()) {
            file.createNewFile();
        }
//        OutputFormat format = OutputFormat.createPrettyPrint();
//        format.setIndent(true);// 设置缩进
//        format.setIndent("  ");// 空行方式缩进
//        format.setNewlines(true);// 设置换行
        XMLWriter writer = new XMLWriter(new FileOutputStream(file));
        // 设置是否转义，默认使用转义字符
        writer.setEscapeText(false);
        writer.write(sb.toString());
        writer.close();
        System.out.println("生成.xml成功");
    }

4.主函数，执行

    public static void main(String[] args) {
        try {
            InputStream inp = new FileInputStream(new File("D:\\0721-LXH.xlsx"));
            String sheetName = "sheet1";
            List<Map<String, String>> list = readExcel(inp, sheetName);
           
            for (Map<String, String> map : list) {
                ktxz(map,resourceId);
            }

        } catch (Exception e) {
            System.out.println("生成.xml失败");
        }
    }

读取excel内容可用模板，生成的xml文件内容和格式根据需求自行拼接即可