将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);
}
}
}