xmlhelper and excelhelper

做了个从excel中读数据然后写入xml的东西，把东西放在这以备以后直接拿过来用。

excelhelper.cs

代码

using System;
using System.Data;
using System.Data.OleDb;

namespace ReadExecelAndWriteXml
{
    public class ExcelHelper
    {
        public static DataTable GetList(string path, string sheetName)
        {
            var conn =
                string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties=Excel 8.0;", path);
            using (var oleDbConnection = new OleDbConnection(conn))
            {
                //sheetName为excel中表的名字,如：sheet1
                var sql = string.Format("select * from [{0}$]", sheetName);
                var cmd = new OleDbCommand(sql, oleDbConnection);
                var ad = new OleDbDataAdapter(cmd);
                var ds = new DataSet();
                try
                {
                    ad.Fill(ds);
                    return ds.Tables[0];
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return null;
                }
            }
        }
    }
}

xmlhelper.cs

代码

using System;
using System.Data;
using System.IO;
using System.Xml;

namespace ReadExecelAndWriteXml
{
    public class XmlHelper
    {
        /* <root>
         * <item>
         * <o>123<o>
         * <k>111123</k>
         * </item>
         * </root>
         */
        private const string Path = @"E:\Study\App\ReadExecelAndWriteXml\ReadExecelAndWriteXml\Data\ids.xml";

        /// <summary>
        /// 读取xml的数据
        /// </summary>
        public static void ReadXml()
        {
            var document = new XmlDocument();
            document.Load(Path);
            var list =document.GetElementsByTagName("item");
            foreach (XmlNode node in list)
            {
                Console.WriteLine("o:{0},k:{1}", node.ChildNodes[0].InnerText, node.ChildNodes[1].InnerText);
            }
        }
        /// <summary>
        /// xpath中selectsingleNode的使用
        /// </summary>
        /// <param name="oId"></param>
        public static void SelectSingleNode(int oId)
        {
            var document = new XmlDocument();
            document.Load(Path);
            XmlNode node= document.SelectSingleNode(string.Format("//root/item[o='{0}']/k",oId));
            if(node!=null)
            {
                Console.WriteLine(node.InnerText);
            }
        }

        /// <summary>
        /// 根据dataTable中的数据写xml,符合预订规范的
        /// </summary>
        /// <param name="dt"></param>
        public static void Write(DataTable dt)
        {
            var document = new XmlDocument();
            if (File.Exists(Path))
            {
                File.Delete(Path);
            }
            var root = document.CreateElement("root");
            foreach (DataRow row in dt.Rows)
            {
                var item = document.CreateElement("item");
                var o = document.CreateElement("o");
                var k = document.CreateElement("k");
                var oId = Convert.ToInt32(row["column1"]);
                var kId = Convert.ToInt32(row["column2"]);
                o.InnerText = oId.ToString();
                k.InnerText = kId.ToString();
                item.AppendChild(o);
                item.AppendChild(k);
                root.AppendChild(item);
            }
            document.AppendChild(root);
            document.Save(Path);
        }
    }
}