• csharp read execl and write xml


    /*
    
    编写:围城(solq)
    
    日期:2012-5-8
    
    blog:http://www.cnblogs.com/solq/
    说明:读取 execl2007 注意,要安装驱动,否则不能识别2007,保存xml 时,注意编码问题。。不然会出错
    
    
    
    表格格式:
    
    行1:不重要的标题,,
    
    行2:对应行1的英文名,,,程序自动对这个名来生成 xml 节点属性
    
    行3+:。。。。xxxxxxx数据
    
    */
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.Xml;
    
    
    namespace readexecl
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();            
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.InitialDirectory = "c:";
                openFileDialog1.Filter = "*.xlsx|*.xlsx";
                openFileDialog1.FilterIndex = 2;
                openFileDialog1.RestoreDirectory = true;
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    this.textBox1.Text = openFileDialog1.FileName;
                    Connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1\";", this.textBox1.Text); 
                }
    
            }
    
            private string save_file = "";
            private string table = "";
            private void button2_Click(object sender, EventArgs e)
            {
                if (textBox2.Text == "")
                {
                    MessageBox.Show("请输入 输出的表格。。。。");
                    return;
                }
                if (textBox1.Text == "")
                {
                    MessageBox.Show("请选择XML文件。。。。");
                    return;
                }
                SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                //设置文件类型  
                saveFileDialog1.Filter = "All files(*.*)|*.*|xml(*.xml)|*.xml";
    
                //设置默认文件类型显示顺序  
                saveFileDialog1.FilterIndex = 2;
    
                //保存对话框是否记忆上次打开的目录  
                saveFileDialog1.RestoreDirectory = true;
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    save_file = saveFileDialog1.FileName;
                    readExecl();
                }
            }
    
            /////////////////////execl////////////////////////
    
            string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:/test.xlsx;Extended Properties=\"Excel 12.0;HDR=no;IMEX=1\";";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;" +  "Extended Properties='Excel 8.0'";
            private void readExecl()
            {
    
                OleDbConnection con = new OleDbConnection(Connection);
    
                #region
    
                /*
                    获取多少个表。。。。。
                 */
                con.Open();
                DataTable sheetsName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
    
                //Console.WriteLine(sheetsName.Rows.Count);
    
                bool check = false;
                for (int t = 0; t < sheetsName.Rows.Count; t++)
                {
                    if (sheetsName.Rows[t][2].ToString().Equals(this.textBox2.Text + "$"))
                    {
                        check = true;
                        break;
                    }
                    Console.WriteLine(sheetsName.Rows[t][2].ToString()); // get table name
                }
    
                if (!check)
                {
                    MessageBox.Show("没有表格名!!! " + this.textBox2.Text);
                    return;
                }
    
                #endregion
                /*
                //读取方式一      
                OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", con);
    
                DataTable dt = new DataTable();
                myCommand.Fill(dt);
    
                DataSet set = new DataSet();
                myCommand.Fill(set);
               
                foreach (DataRow r in dt.Rows)
                {
                   
                    String str = r[0].ToString();
                    ////Console.WriteLine(str);
                }*/
    
                /*
                    如果只想读取前两列可以用:select * from [Sheet1$A:B]
                    如果只想读取A1到B2的内容,就用:select * from [Sheet1$A1:B2]
                 */
                //读取方式二
                string sql = string.Format("SELECT * FROM [{0}$] ", textBox2.Text);
                OleDbCommand myOleDbCommand = new OleDbCommand(sql, con);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
    
                List<string> fields = new List<string>();
    
                if (myDataReader.Read())
                {
                    //读取英文头表,字段
                    for (int i = 0; i < myDataReader.FieldCount; i++)
                    {
                        string c = Convert.ToString(myDataReader.GetValue(i)).Trim();
                        //Console.Write(c + "\t");
    
                        fields.Add(c);
                    }
                }
    
                ////////////////////////////////////xml////////////////////////////////////////////
                XmlDocument doc = new XmlDocument();
                XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
                doc.AppendChild(dec);
    
                //创建一个根节点(一级)  
                XmlElement root = doc.CreateElement("datas"); 
    
                doc.AppendChild(root);
    
                //读取主体
                while (myDataReader.Read())
                {
                    XmlElement element = doc.CreateElement("data");
                    for (int i = 0; i < myDataReader.FieldCount; i++)
                    {
                        string value = Convert.ToString(myDataReader.GetValue(i)).Trim();
                        Console.Write(value + "\t");
    
                        element.SetAttribute(fields[i].ToString(), value);
    
                    }
                    root.AppendChild(element);
                    //Console.WriteLine("");
                }
    
                try
                {
                    doc.Save(save_file);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
    
                myDataReader.Dispose();
                con.Close();
            }
    
            private void readExecl2()
            {
    
                OleDbConnection con = new OleDbConnection(Connection);
                con.Open();
                DataTable sheetsName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); 
    
                for (int t = 0; t < sheetsName.Rows.Count; t++)
                {
                    string sql = string.Format("SELECT * FROM [{0}] ", sheetsName.Rows[t][2].ToString());
                    OleDbCommand myOleDbCommand = new OleDbCommand(sql, con);
                    OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
    
                    List<string> fields = new List<string>();
    
                    if (myDataReader.Read())
                    {
                        //读取英文头表,字段
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                            string c = Convert.ToString(myDataReader.GetValue(i)).Trim();
                            fields.Add(c);
                        }
                    }
    
                    ////////////////////////////////////xml////////////////////////////////////////////
                    XmlDocument doc = new XmlDocument();
                    XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
                    doc.AppendChild(dec);
    
                    //创建一个根节点(一级)  
                    XmlElement root = doc.CreateElement("datas");
    
                    doc.AppendChild(root);
    
                    //读取主体
                    while (myDataReader.Read())
                    {
                        XmlElement element = doc.CreateElement("data");
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                            string value = Convert.ToString(myDataReader.GetValue(i)).Trim();
                            Console.Write(value + "\t");
    
                            element.SetAttribute(fields[i].ToString(), value);
    
                        }
                        root.AppendChild(element);
                        //Console.WriteLine("");
                    }
    
                    string file=sheetsName.Rows[t][2].ToString().Replace("$",".xml");
                    file = this.textBox3.Text +"/"+file;
                    try
                    {
                        doc.Save(file);
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    myDataReader.Dispose();
                } 
    
                
                con.Close();
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("请选择XML文件。。。。");
                    return;
                }
                if (textBox3.Text == "")
                {
                    MessageBox.Show("请输入保存目录。。。。");
                    return;
                }
                readExecl2();            
            }
          
        }
    }
  • 相关阅读:
    linux tcp中time_wait
    linux查看系统信息
    运行程序出错无法找到库文件
    安装keepalived
    python 深拷贝与浅拷贝
    python import eventlet包时提示ImportError: cannot import name eventlet
    [原创] 用两个queue实现stack的功能
    [原创] 用两个stack实现queue的功能
    [原创] 编写函数,实现对链表元素的排序与分类
    python 装饰器
  • 原文地址:https://www.cnblogs.com/solq/p/2494162.html
Copyright © 2020-2023  润新知