testlink中excel转XML格式

什么也不说了，直接上代码。后面我会把程序发上来。

源码下载地址http://download.youkuaiyun.com/detail/q317379184/4536047

 

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.Xml;
namespace TestLink
{
    public partial class Testlink : Form
    {
        public Testlink()
        {
            InitializeComponent();
        }

        private void btn_chooseFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog opd = new OpenFileDialog();
            opd.Filter = "Excel2010(*.xlsx)|*.xlsx|Excel2007(*.xlsx)|*.xlsx|Excel2003(*.xls)|*.xls";
            opd.ShowDialog();
            inputpath.Text = opd.FileName;
        }
        //获取EXCEL数据
        private DataSet xsldata(string filepath, string tablename)
        {
            string strCon = "";
            string filemode = filepath.Substring(filepath.Length - 4, 4);
            if (filemode == ".xls")
            {
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 8.0;IMEX=1'";
            }
            else if (filemode == "xlsx")
            {
                strCon = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 12.0;IMEX=1'";
            }
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM [" + tablename + "$]";
            Conn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();
            DataGridView dataGridView = new DataGridView();
            myCommand.Fill(ds, "[" + tablename + "$]");
            dataGridView.DataSource = ds.Tables[0];
            Conn.Close();
            return ds;
        }
        string title;
        string summary;
        string preconditions;
        int testway_value;
        int Execution_value;
        string[][] step2;
        string[][] result2;
        string[][] Execution2;
        string[][] requirement2;
        int k;

        string testWay;
        int testImportance_value = 0;
        string stepAction;
        string[] step1;
        string stepResult;
        string[] result1;
        string[] keywordname1;
        string[] requirement1;
        string Execution;
        string testsuit;
        string keywordname;
        string requirement;
        string endsuit;
        string lastsuit = "";
        bool ifend = false;
        string rquire = "";
        int i;

        private void BTN_addto_Click(object sender, EventArgs e)
        {
            if (inputpath.Text == "" || inputpath == null)
            {
                MessageBox.Show("请选择Excel文档");
                return;
            }
            DataSet ds = new DataSet();
            ds = xsldata(@inputpath.Text, "Sheet1");//取得数据集,调用上面的函数
            // 创建XmlTextWriter类的实例对象
            string[] filename = inputpath.Text.Split('.');
            string file = filename[0] + ".xml";
            XmlTextWriter textWriter = new XmlTextWriter(file, Encoding.UTF8);
            textWriter.Formatting = Formatting.Indented;

            // 开始写过程，调用WriteStartDocument方法
            textWriter.WriteStartDocument();

            // 写入说明
            textWriter.WriteComment("此XML文档由excel文档转换而来");
            textWriter.WriteComment("testLink.xml in root dir");
            //textWriter.WriteStartElement("testcases");
            try
            {
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    rquire = Convert.ToString(ds.Tables[0].Rows[0][3].ToString().Trim());
                    endsuit = Convert.ToString(ds.Tables[0].Rows[i][0].ToString().Trim());
                    testsuit = Convert.ToString(ds.Tables[0].Rows[i][1].ToString().Trim());
                    if (testsuit != "")
                    {
                        if (endsuit == lastsuit)
                        {
                            ifend = true;
                        }
                        lastsuit = endsuit;
                        if (ifend)
                        {
                            textWriter.WriteEndElement();//结束testsuit
                            ifend = false;
                        }
                        textWriter.WriteStartElement("testsuite");
                        textWriter.WriteAttributeString("name", testsuit);
                        textWriter.WriteStartElement("details");
                        //textWriter.WriteCData("<p>" + summary + "</p>");
                        textWriter.WriteCData("");
                        textWriter.WriteEndElement();//结束details
                    }
                    title = Convert.ToString(ds.Tables[0].Rows[i][2].ToString().Trim());

                    if (title == null || title == "")
                    {

                        continue;
                    }
                    summary = Convert.ToString(ds.Tables[0].Rows[i][5].ToString().Trim());
                    preconditions = Convert.ToString(ds.Tables[0].Rows[i][6].ToString().Trim());
                    testway_value = 0;
                    testWay = Convert.ToString(ds.Tables[0].Rows[i][7].ToString().Trim());
                    if (testWay == "手工")
                    {
                        testway_value = 1;
                    }
                    else if (testWay == "自动")
                    {
                        testway_value = 2;
                    }
                    string testImportance = Convert.ToString(ds.Tables[0].Rows[i][8].ToString().Trim());
                    if (testImportance == "高")
                    {
                        testImportance_value = 3;
                    }
                    else if (testImportance == "中")
                    {
                        testImportance_value = 2;
                    }
                    else if (testImportance == "低")
                    {
                        testImportance_value = 1;
                    }
                    stepAction = Convert.ToString(ds.Tables[0].Rows[i][9].ToString().Trim());
                    step1 = stepAction.Split('；');
                    step2 = new string[step1.Length][];
                    for (k = 0; k < step1.Length; k++)
                    {
                        step1[k] = step1[k].Replace("。", "</p><p>");
                    }
                    stepResult = Convert.ToString(ds.Tables[0].Rows[i][10].ToString().Trim());
                    result1 = stepResult.Split('；');
                    result2 = new string[result1.Length][];
                    for (k = 0; k < result1.Length; k++)
                    {
                        result1[k] = result1[k].Replace("。", "</p><p>");
                    }
                    Execution = Convert.ToString(ds.Tables[0].Rows[i][11].ToString().Trim());
                    //string[] Execution1 = Execution.Split('；');
                    //Execution2 = new string[result1.Length][];
                    //for (k = 0; k < Execution1.Length; k++)
                    //{
                    //    Execution1[k] = Execution1[k].Replace(".", "</p><p>");
                    //}
                    if (Execution == "手工")
                    {
                        Execution_value = 1;
                    }
                    else if (Execution == "自动")
                    {
                        Execution_value = 2;
                    }
                    keywordname = Convert.ToString(ds.Tables[0].Rows[i][4].ToString().Trim());
                    keywordname1 = keywordname.Split('#');
                    requirement = Convert.ToString(ds.Tables[0].Rows[i][3].ToString().Trim());
                    requirement1 = requirement.Split('#');
                    requirement2 = new string[requirement1.Length][];
                    for (k = 0; k < requirement1.Length; k++)
                    {
                        requirement2[k] = requirement1[k].Split('：');
                    }

                    textWriter.WriteStartElement("testcase");
                    textWriter.WriteAttributeString("name", title);
                    textWriter.WriteStartElement("summary");
                    textWriter.WriteCData("<p>" + summary + "</p>");
                    textWriter.WriteEndElement();
                    textWriter.WriteStartElement("preconditions");
                    textWriter.WriteCData("<p>" + preconditions + "</p>");
                    textWriter.WriteEndElement();
                    textWriter.WriteStartElement("execution_type");
                    textWriter.WriteCData(testway_value.ToString());
                    textWriter.WriteEndElement();
                    textWriter.WriteStartElement("importance");
                    textWriter.WriteCData(testImportance_value.ToString());
                    textWriter.WriteEndElement();
                    textWriter.WriteStartElement("steps");
                    for (k = 0; k < step1.Length; k++)
                    {
                        textWriter.WriteStartElement("step");//写步骤
                        textWriter.WriteStartElement("step_number");
                        textWriter.WriteCData((k + 1).ToString());
                        textWriter.WriteEndElement();
                        textWriter.WriteStartElement("actions");
                        textWriter.WriteCData("<p>" + step1[k] + "</p>");
                        textWriter.WriteEndElement();
                        textWriter.WriteStartElement("expectedresults");
                        textWriter.WriteCData("<p>" + result1[k] + "</p>");
                        textWriter.WriteEndElement();
                        textWriter.WriteStartElement("execution_type");
                        textWriter.WriteCData(Execution_value.ToString());
                        textWriter.WriteEndElement();
                        textWriter.WriteEndElement();
                    }
                    textWriter.WriteEndElement();//结束steps
                    if (keywordname != "")
                    {
                        textWriter.WriteStartElement("keywords");
                        for (k = 0; k < keywordname1.Length; k++)
                        {
                            textWriter.WriteStartElement("keyword");//写关键字
                            textWriter.WriteAttributeString("name", keywordname1[k]);
                            textWriter.WriteStartElement("notes");
                            textWriter.WriteCData(" ");
                            textWriter.WriteEndElement();//结束notes
                            textWriter.WriteEndElement();//结束keyword
                        }
                        textWriter.WriteEndElement();//结束keywords
                    }
                    if (requirement != "")
                    {
                        textWriter.WriteStartElement("requirements");
                        for (k = 0; k < requirement1.Length; k++)
                        {
                            textWriter.WriteStartElement("requirement");//写需求
                            textWriter.WriteStartElement("req_spec_title");
                            textWriter.WriteCData(rquire);//写需求的套件名称
                            textWriter.WriteEndElement();//结束req_spec_title
                            textWriter.WriteStartElement("doc_id");
                            textWriter.WriteCData(requirement2[k][0]);
                            textWriter.WriteEndElement();//结束doc_id
                            textWriter.WriteStartElement("title");
                            textWriter.WriteCData(requirement2[k][1]);
                            textWriter.WriteEndElement();//结束title
                            textWriter.WriteEndElement();//结束requirement
                        }
                        textWriter.WriteEndElement();//结束requirements
                    }
                    textWriter.WriteEndElement();//结束testcase
                }
                // 写文档结束，调用WriteEndDocument方法
                textWriter.WriteEndDocument();

                // 关闭textWriter
                textWriter.Close();
                MessageBox.Show("转换成功！");
            }
            catch
            {
                // 写文档结束，调用WriteEndDocument方法
                textWriter.WriteEndDocument();

                // 关闭textWriter
                textWriter.Close();
                MessageBox.Show("错误行数为"+(i+2).ToString());
            }

        }

        private void button1_Click(object sender, EventArgs e)
        {

            // 创建XmlTextWriter类的实例对象
            XmlTextWriter textWriter = new XmlTextWriter("w3sky.xml", Encoding.UTF8);
            textWriter.Formatting = Formatting.Indented;

            // 开始写过程，调用WriteStartDocument方法
            textWriter.WriteStartDocument();

            // 写入说明
            textWriter.WriteComment("First Comment XmlTextWriter Sample Example");
            textWriter.WriteComment("w3sky.xml in root dir");

            //创建一个节点
            textWriter.WriteStartElement("LicenseCertificate");
            textWriter.WriteElementString("AccountNumber", "aaaaaaaaaaaaaa");
            textWriter.WriteElementString("Signature","aaaaaaaaaaaaaaaaaaa");
            textWriter.WriteEndElement();
            // 写文档结束，调用WriteEndDocument方法
            textWriter.WriteEndDocument();
            // 关闭textWriter
            textWriter.Close();

            XmlTextReader textReader = new XmlTextReader("w3sky.xml");
            string Applycode = "";
            string AuthorizeCode = "";
            while(textReader.Read())
            {
                if (textReader.Name == "AccountNumber")
                    Applycode = textReader.ReadElementString();
                if (textReader.Name == "Signature")
                    AuthorizeCode = textReader.ReadElementString();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            MessageBox.Show("");
        }
    }

}