基本信息

---

下载

   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

---

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：78630559

本次赞助数额为： 5 元　

支付宝支付

微信支付

   源码介绍

---

D:\ExcelToXML.zip

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Xml;
using System.Windows.Forms;
using System.IO;


namespace ExcelToTxt
{
    public partial class Form2 : Form
    {
        private DataTable dt;//存储EXCLE中的数据

        public Form2()
        {
            InitializeComponent();
            this.btnChange.Enabled = false; //初始化设置控件为不可用
        }



        /// <summary>
        /// 该方法打开一个Excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelect_Click(object sender, EventArgs e)
        {
            string excelFilePath = "";//存储打开的文件的路径

            OpenFileDialog selectFile = new OpenFileDialog();

            //选择打开的文件设置
            selectFile.Filter = "Excel(*.xls)|*.xls";
            selectFile.FilterIndex = 1;
            selectFile.DefaultExt = "xls";
            selectFile.AddExtension = true;
            selectFile.RestoreDirectory = true;
            selectFile.Multiselect = false;

            //选择文件
            if (selectFile.ShowDialog() == DialogResult.OK)
            {
                excelFilePath = selectFile.FileName;//获取选择的文件路径
            }
            else
            {
                return;
            }

            //得到控件的数据源
            dt = GetExcelData(excelFilePath);

            //在显示控件中显示数据
            ShowDataGridView();

            //设置转换格式的控件可用
            this.btnChange.Enabled = true;
        }

        /// <summary>
        ///该方法将选择的EXCEL文件转换成XML文档
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnChange_Click(object sender, EventArgs e)
        {

            string companyCode = "000002";//公司编码
            string year = comboBox1.Text; //获取年份
            string month = comboBox2.Text; //获取月份
            string areaid = "2000000"; //行政区域码
            string intervaltype = "";  //频度值

            int num = 0;//统计数据个数
            string[,] tempstr;//定义存储数据的二维数组
            int tRow = 0;//用来记录二维数据


            //对月份进行判断
            if (month.Equals("03") || month.Equals("09"))
            {
                intervaltype = "3";
            }
            else if(month.Equals("06")) 
            {
                intervaltype = "4";
            }
            else if (month.Equals("12"))
            {
                intervaltype = "5";
            }
            else
            {
                intervaltype = "6";
            }

            string xmlFilePath = "";//存储选择的XML文档的文件名
            SaveFileDialog saveXMLFile = new SaveFileDialog();

            //选择保存的文件设置
            saveXMLFile.Filter = "Xml(.xml)|*.xml";
            saveXMLFile.FilterIndex = 1;
            saveXMLFile.DefaultExt = "xml";
            saveXMLFile.AddExtension = true;
            saveXMLFile.RestoreDirectory = true;
            saveXMLFile.OverwritePrompt = true;

            //选择创建文件的文件夹
            if (saveXMLFile.ShowDialog() == DialogResult.OK)
            {
                xmlFilePath = saveXMLFile.FileName; //获取选择的文件路径
            }
            else
            {
                return;
            }

            //将DataTable中的文件写入到xml文档中
            Cursor.Current = Cursors.WaitCursor; //设置鼠标状态
            int dtcols = dt.Columns.Count;
            StringBuilder sbxmldata = new StringBuilder(); ;//临时存储从dt中读出的每一条数据


            //先创建一个新的XML文档
            FileStream fsXmlFile = new FileStream(xmlFilePath, FileMode.CreateNew, FileAccess.Write);
            //StreamWriter swXmlFile = new StreamWriter(fsXmlFile, Encoding.GetEncoding("gb2312"));

            //以下读取Excel数据
            if (intervaltype.Equals("6")) //获取年度记录
            {
                for (int rows = 0; rows < dt.Rows.Count; rows  )
                {
                    if ("年".Equals(dt.Rows[rows][4].ToString()))
                    {
                        num  ;
                    }
                }

                //存储数据
                tempstr = new string[num, dt.Columns.Count];//定义存储数据的二维数组
                //--------------------- 华丽的分割线，明天做 --------------------------------
                for (int rows = 0; rows < dt.Rows.Count; rows  )
                {
                    if ("年".Equals(dt.Rows[rows][4].ToString()))
                    {
                        for (int cols = 0; cols < dt.Columns.Count; cols  )
                        {
                            tempstr[tRow, cols] = dt.Rows[rows][cols].ToString();
                        }
                        tRow  ;
                    }
                }
            }
            else
            {
                for (int rows = 0; rows < dt.Rows.Count; rows  )
                {
                    if ("季".Equals(dt.Rows[rows][3].ToString()))
                    {
                        num  ;
                    }
                }

                //存储数据
                tempstr = new string[num, dt.Columns.Count];//定义存储数据的二维数组
                //--------------------- 华丽的分割线，明天做 --------------------------------
                for (int rows = 0; rows < dt.Rows.Count; rows  )
                {
                    if ("季".Equals(dt.Rows[rows][3].ToString()))
                    {
                        for (int cols = 0; cols < dt.Columns.Count; cols  )
                        {
                            tempstr[tRow, cols] = dt.Rows[rows][cols].ToString();
                        }
                        tRow  ;
                    }
                }
            }


            //以下代码为写入XML文档操作
            //string FileName = Application.StartupPath   "\\phone.xml";
            XmlTextWriter objXmlTextWriter = new XmlTextWriter(fsXmlFile, Encoding.Default);
            objXmlTextWriter.Formatting = Formatting.Indented;
            objXmlTextWriter.Indentation = 6;
            objXmlTextWriter.WriteStartDocument();
            objXmlTextWriter.WriteStartElement("", "DATA"   companyCode   year   month, "");//最大标签
            objXmlTextWriter.WriteStartElement("", "area", "");
            objXmlTextWriter.WriteStartElement("", "areaid", "");
            objXmlTextWriter.WriteString("00000"   areaid);
            objXmlTextWriter.WriteEndElement();

            for (int rows = 1; rows < num; rows  )
            {
                objXmlTextWriter.WriteStartElement("", "item", "");//开始item
                objXmlTextWriter.WriteStartElement("", "PK", "");//开始pk
                objXmlTextWriter.WriteStartElement("", "key", "");//开始key
                objXmlTextWriter.WriteString(tempstr[rows,0]);//key值
                objXmlTextWriter.WriteEndElement();//结束key
                objXmlTextWriter.WriteStartElement("", "intervaltype", "");//开始
                objXmlTextWriter.WriteString(intervaltype);//值
                objXmlTextWriter.WriteEndElement();//结束
                objXmlTextWriter.WriteEndElement();//结束PK
                objXmlTextWriter.WriteStartElement("", "value", "");//开始value
                objXmlTextWriter.WriteString(tempstr[rows,2]);//值
                objXmlTextWriter.WriteEndElement();//结束value
                objXmlTextWriter.WriteStartElement("", "remark", "");//开始remark
                objXmlTextWriter.WriteString(tempstr[rows, dt.Columns.Count-1]);//值   可替换成tempstr[rows,dt.Columns.Count]
                objXmlTextWriter.WriteEndElement();//结束remark
                objXmlTextWriter.WriteEndElement();//结束item
            }

            objXmlTextWriter.WriteEndElement();
            objXmlTextWriter.WriteEndDocument();//结束最大标签
            objXmlTextWriter.Flush();//停止写入
            objXmlTextWriter.Close();//关闭写操作

            //重新设置鼠标格式
            Cursor.Current = Cursors.Default;
            MessageBox.Show("文件转换成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }


        /// <summary>
        /// 获取Excel文件中的数据
        /// </summary>
        /// <param name="path">Excel文件的路径</param>
        /// <returns>DataTable:将Excel文件的数据加载到DataTable中</returns>
        private DataTable GetExcelData(string path)
        {
            //连接字符串确定
            string excelstr = "Provider = Microsoft.Jet.OLEDB.4.0;"   "Data Source= "   path   " ;"  " Extended Properties = Excel 8.0;";
            OleDbConnection excelConn = new OleDbConnection(excelstr);

            //打开数据源连接
            try
            {
                if (excelConn.State == ConnectionState.Closed)
                {
                    excelConn.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("打开数据源连接失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
            finally
            {
                if(excelConn.State == ConnectionState.Open)
                    excelConn.Close();
            }
            //设置查询命令
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [因子代码-产险公司$]", excelConn);
            DataSet ds = new DataSet();
            //执行该查询EXCEL表的命令
            try
            {
                myCommand.Fill(ds, "excelTable");
            }
            catch (Exception ex)
            {
                MessageBox.Show("该Excel文件的工作表的名字不是[因子代码-产险公司$]!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
            finally
            {
                if (excelConn.State == ConnectionState.Closed)
                {
                    excelConn.Close();
                }
            }
            //判断DataTable中是否有数据
            if (ds.Tables["excelTable"].Rows.Count > 0)
            {
                return ds.Tables["excelTable"];
            }
            else
            {
                MessageBox.Show("没有读到Excel表中的数据!", "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }
        /// <summary>
        /// 将选择的excel表中的数据显示在DataGridView中
        /// </summary>
        private void ShowDataGridView()
        {
            ////设置显示控件的样式
            //this.dgvShow.DefaultCellStyle.BackColor = Color.Beige;
            //this.dgvShow.DefaultCellStyle.Font = new Font("Tahoma", 12);
            //DataGridViewCellStyle highlightCellStyle = new DataGridViewCellStyle();
            //highlightCellStyle.BackColor = Color.Red;
            //DataGridViewCellStyle currencyCellStyle = new DataGridViewCellStyle();
            //currencyCellStyle.Format = "C";
            //currencyCellStyle.ForeColor = Color.Green;

            ////设置显示控件的数据源 
            //dgvShow.DataSource = dt;
        }

    }
}