将Excel表中的数据 转换成XML 并对XML数据进行读取

EXCEL 转换成 XML 工具类

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.IO;
namespace xML
{
    class Program
    {

        static Dictionary<string,DataTable> DtList= new Dictionary<string,DataTable>();

        static Dictionary<string, XmlNode> xmlNodelist = new Dictionary<string, XmlNode>();//数据类型节点

        static  string basePath = AppDomain.CurrentDomain.BaseDirectory;

        static  string excelPath = basePath + @"\Excel";

        static string xmlPath = basePath + "Xml";

        static void Main(string[] args)
        {
            DirectoryInfo folder = new DirectoryInfo(excelPath);

            FileInfo[] file = folder.GetFiles("*.xlsx*",SearchOption.TopDirectoryOnly);

            foreach (var f in file)
            {
                Start(f.FullName);
            }
            Console.ReadKey();   
        }

        static void Start(string excelPath)
        {

            bool flag = ExcelToDS(excelPath);

            if (flag)
            {
                foreach (KeyValuePair<string, DataTable> pair in DtList)
                {
                    XmlDocument xmlDocument = new XmlDocument();
                    XmlNode declaration = xmlDocument.CreateXmlDeclaration("1.0", "utf-8", "");
                    xmlDocument.AppendChild(declaration);
                    XmlNode content = xmlDocument.CreateComment(pair.Key);
                    xmlDocument.AppendChild(content);
                    XmlNode Root = XmlOperationUtil.AppendElement(xmlDocument, "Root");

                    //标题 取得Excel表的第二行 作为属性值
                    List<string> titleList = new List<string>();
                    try
                    {
                        int rowLength = pair.Value.Rows.Count;
                        int columnLength = pair.Value.Columns.Count;
                        for (int i = 0; i < columnLength; i++)
                        {
                            titleList.Add(pair.Value.Rows[1][i].ToString());
                        }
                        for (int j = 2; j < rowLength; j++)
                        {
                            XmlNode node = XmlOperationUtil.AppendElement(Root, pair.Key);
                            for (int k = 0; k < columnLength; k++)
                            {
                                XmlOperationUtil.AddAttribute(node, titleList[k], pair.Value.Rows[j][k].ToString());
                            }
                        }
                        xmlDocument.Save(xmlPath+"\\"+pair.Key + ".xml");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }
        static public bool  ExcelToDS(string path)
        {
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';"; 

            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                string[] TableNames = new string[sheetsName.Rows.Count];
                try
                {
                    for (int k = 0; k < sheetsName.Rows.Count; ++k)
                    {
                        TableNames[k] = sheetsName.Rows[sheetsName.Rows.Count - k - 1]["TABLE_NAME"].ToString();
                       
                        string sql = string.Format("SELECT * FROM [{0}]", TableNames[k]); 
                        OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                        DataSet set = new DataSet();
                        ada.Fill(set);
                        string dataNmae = TableNames[k].Substring(0, TableNames[k].Length - 1);
                        if (set.Tables[0].Rows[0][0]!=DBNull.Value)
                        {
                            DtList.Add(dataNmae, set.Tables[0]);
                            Console.WriteLine(dataNmae);
                        }
                        
                    }
                    return true;
                }catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                    return false;
                }
            }
        }
    }
}


XML读取工具
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
/// <summary>
/// XML 读取工具
/// </summary>
public class XmlOperationUtil
{

    /// <summary>
    /// 创建节点
    /// </summary>
    /// <param name="node"></param>
    /// <param name="newElementName"></param>
    /// <returns></returns>
    public static XmlNode AppendElement(XmlNode node, string newElementName)
    {      
        return AppendElement(node, newElementName,null);
    }

    /// <summary>
    /// 创建节点 带有文本参数
    /// </summary>
    /// <param name="node"></param>
    /// <param name="newElementName"></param>
    /// <param name="innerValue"></param>
    /// <returns></returns>
    public static XmlNode AppendElement(XmlNode node, string newElementName, string innerValue)
    {

        if (newElementName == "")
            newElementName = "zero";

        XmlNode newNode=null;
        XmlNode tempNode=null;
        if (node is XmlDocument)
        {
            tempNode = ((XmlDocument)node).CreateElement(newElementName);
            newNode = node.AppendChild(tempNode);
        }
        else
        {
            tempNode = node.OwnerDocument.CreateElement(newElementName);
            newNode = node.AppendChild(tempNode);
        }
        if (innerValue != null&&newNode!=null)
        {
            newNode.InnerText = innerValue;
        }
        return newNode;
    }
    /// <summary>
    /// 获取节点的值
    /// </summary>
    /// <param name="parentNode"></param>
    /// <param name="nodeXPath"></param>
    /// <param name="defaultValue"></param>
    /// <returns></returns>
    public static string GetNodeValue(XmlNode parentNode, string nodeXPath, string defaultValue)
    {
        XmlNode node = parentNode.SelectSingleNode(nodeXPath);
        if (node.FirstChild != null)
        {
            return node.FirstChild.Value;
        }
        else if (node != null)
        {
            return node.Value;
        }
        else
        {
            return defaultValue;
        }
    }
    /// <summary>
    /// 根据XML文件路径获取XmlDocument对象
    /// </summary>
    /// <param name="xmlFilePath"></param>
    /// <returns></returns>
    public static XmlDocument GetXmlDocByFilePath(string xmlFilePath)
    {
        if (string.IsNullOrEmpty(xmlFilePath) || !File.Exists(xmlFilePath))
        {
            return null;
        }
        var xDoc = new XmlDocument();
        try
        {
            xDoc.Load(xmlFilePath);
        }
        catch
        {
            throw new Exception(string.Format("请确认该XML文件格式正确,路径为:{0}", xmlFilePath));
        }
        return xDoc;
    }
    /// <summary>
    /// 获取父节点的子节点列表
    /// </summary>
    /// <param name="parentXmlNode">父节点</param>
    /// <returns></returns>
    public static XmlNodeList GetChildNodes(XmlNode parentXmlNode)
    {
        return parentXmlNode == null ? null : parentXmlNode.ChildNodes;
    }
    /// <summary>
    /// 读取某个XML节点的属性值(根据属性名)
    /// </summary>
    /// <param name="xmlNode"></param>
    /// <param name="attrName"></param>
    /// <returns></returns>
    public static string ReadAttrValue(XmlNode xmlNode, string attrName)
    {
        var xmlElement = xmlNode as XmlElement;
        return xmlElement == null ? null : xmlElement.GetAttribute(attrName);
    }

    public static void AddAttribute(XmlNode node, string type, string name)
    {
        XmlElement element = (XmlElement)node;
        element.SetAttribute(type, name);
    }

工具已经上传,可以下载尝试

工具使用

EXCEL表 严格按照这个样式
在这里插入图片描述
生成的XML表格
在这里插入图片描述
接下来是读取 XML 数据 的过程

XML 实体类 接受

public class SocietyFuctionCfgData
{
    public string iconUrl;
    public string title;
    public int functionId;
    public void Init(XmlNode node)
    {
        functionId = int.Parse(node.Attributes["FunctionID"].Value);
        iconUrl = node.Attributes["Icon"].Value;
        title = node.Attributes["name"].Value;
    }
}

读取XML实体类数据

public class SocietyFuctionCfg
{
    private string m_resCfg = ResPathConst.s_cfgPath + "/SocietySingleMerberFuction.xml"; //这个是需要用户自己修改,XML文件的路径
    private Dictionary<int,SocietyFuctionCfgData> m_SocietyDic = new Dictionary<int,SocietyFuctionCfgData>();  //数据保存位置
    public void Init()     //调用Init 进行XML数据读取保存
    {
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.Load(m_resCfg);
        XmlNode root = xmlDocument.SelectSingleNode("Root");
        XmlNodeList nodeList = root.ChildNodes;
        for (int i = 0; i < nodeList.Count; i++)
        {
            SocietyFuctionCfgData data = new SocietyFuctionCfgData();
            data.Init(nodeList[i]);
            m_SocietyDic.Add(data.functionId,data);
        }
    }
}

补充下下载地址:https://download.csdn.net/download/qq_27032631/10693595

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页