栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

Java 实现Excle表转成XML格式 如何读取和写入xml文件?

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Java 实现Excle表转成XML格式 如何读取和写入xml文件?

 输入sheet表名

package com.brains.crm.i18;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.ofbiz.base.util.Debug;
import org.apache.ofbiz.base.util.UtilValidate;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;


public class ExcelToXml {
    public static void main(String[] args) {
        try {
            InputStream inp = new FileInputStream(new File("D:\CRM-中英文-王金强.xlsx"));
            String sheetName = "order";
            List> list = readExcel(inp, sheetName);
//        dealList(list);
            StringBuffer sb = new StringBuffer();
            sb.append("n");
            sb.append("n");

            for (Map map : list) {
                if (UtilValidate.isNotEmpty(map.get("KEY"))) {
                    sb.append("     n");
                } else if (UtilValidate.isNotEmpty(map.get("英文"))) {
                    sb.append("     n");
                } else {
                    sb.append("     n");
                }
                sb.append("         ").append(map.get("中文")).append("n");
                sb.append("         ").append(map.get("英文")).append("n");
                sb.append("         ").append(map.get("日文")).append("n");
                sb.append("     n");
            }
            sb.append("n");
            System.out.println(sb.toString());
//            生成xml文件
            File file = new File("E:\workspace\svn\scsk_pj\SccmPlatform\04Code\crm\trunk\plugins\crm\config\CrmUitLabels-" + sheetName + ".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), format);
            // 设置是否转义,默认使用转义字符
            writer.setEscapeText(false);
            writer.write(sb.toString());
            writer.close();
            System.out.println("生成.xml成功");
        } catch (Exception e) {
            Debug.logError(e, "生成.xml文件失败");
            System.out.println("生成.xml失败");
        }
    }

    
    public static List> 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> 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 map = new LinkedHashMap();
                for (int rol = 1; rol < maxRol; rol++) {
                    XSSFCell cell = xssfSheet.getRow(row).getCell(rol);
                    if (UtilValidate.isNotEmpty(cell)) {
                        cell.setCellType(CellType.STRING);
                        //System.out.print(cell.getStringCellValue());
                        if (UtilValidate.isNotEmpty(titleRow.getCell(rol))) {
                            map.put(titleRow.getCell(rol).getStringCellValue(), cell.getStringCellValue());
                        }
                    }
                }
                //System.out.println();
                list.add(map);
            }
        }
        //System.out.println();
        int i = 1;
        for (Map map1 : list) {
            //System.out.print(i+" ");
            for (Object key : map1.keySet()) {
                //System.out.print(" "+key+":"+map1.get(key));
            }
            //System.out.println();
            i++;
        }
        return list;
    }

}

直接整个Excle表

package com.brains.crm.i18;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.ofbiz.base.util.Debug;
import org.apache.ofbiz.base.util.UtilValidate;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;


public class ExcelToXmlList {
    public static void main(String[] args) throws FileNotFoundException {
        InputStream inp = new FileInputStream(new File("D:\CRM-中英文-partygroup.xlsx"));
        List> list = readExcel(inp);
    }

    
    public static List> readExcel(InputStream inputStream) {
        //定义工作簿
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            System.out.println("Excel data file cannot be found!");
        }
        int numberOfSheets = xssfWorkbook.getNumberOfSheets();
        List> list = new ArrayList>();
        for (int i = 0; i < numberOfSheets; i++) {
            List> sheetList = new ArrayList>();
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(i);
            //定义工作表
            String sheetName = xssfWorkbook.getSheetName(i);
            int maxRow = sheetAt.getLastRowNum();
            //默认第一行为标题行,index = 0
            XSSFRow titleRow = sheetAt.getRow(0);

            for (int row = 1; row <= maxRow; row++) {
                XSSFRow xss = sheetAt.getRow(row);
                if (xss != null) {
                    int maxRol = sheetAt.getRow(row).getLastCellNum();
                    Map map = new LinkedHashMap();
                    for (int rol = 1; rol < maxRol; rol++) {
                        XSSFCell cell = sheetAt.getRow(row).getCell(rol);
                        if (UtilValidate.isNotEmpty(cell)) {
                            cell.setCellType(CellType.STRING);
                            //System.out.print(cell.getStringCellValue());
                            if (UtilValidate.isNotEmpty(titleRow.getCell(rol))) {
                                map.put(titleRow.getCell(rol).getStringCellValue(), cell.getStringCellValue());
                            }
                        }
                    }
                    sheetList.add(map);
                }
            }
            StringBuffer xmlDetails  = new StringBuffer();
            xmlDetails.append("n");
            xmlDetails.append("n");
            for (Map map : sheetList) {
                if (UtilValidate.isNotEmpty(map.get("KEY"))) {
                    xmlDetails.append("     n");
                } else if (UtilValidate.isNotEmpty(map.get("EN"))) {
                    xmlDetails.append("     n");
                } else {
                    xmlDetails.append("     n");
                }
                xmlDetails.append("         ").append(map.get("ZH")).append("n");
                xmlDetails.append("         ").append(map.get("EN")).append("n");
                xmlDetails.append("         ").append(map.get("JA")).append("n");
                xmlDetails.append("     n");
            }
            xmlDetails.append("n");
            try {
            //            生成xml文件
            File file = new File("E:\workspace\svn\scsk_pj\SccmPlatform\04Code\crm\trunk\plugins\crm\config\CrmUitLabels-" + sheetName + ".xml");
            if (!file.exists()) {
                file.createNewFile();
            }
            OutputFormat format = OutputFormat.createPrettyPrint();
            XMLWriter writer = new XMLWriter(new FileOutputStream(file), format);
            // 设置是否转义,默认使用转义字符
            writer.setEscapeText(false);
            writer.write(xmlDetails.toString());
            writer.close();
            System.out.println("CrmUitLabels-" + sheetName + ".xml成功");
            } catch (Exception e) {
                Debug.logError(e, "CrmUitLabels-" + sheetName + ".xml失败");
                System.out.println("CrmUitLabels-" + sheetName + ".xml失败");
            }
        }
        return list;
    }
}

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/937326.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号