C#实现将Excel测试用例转换成Testlink支持的xml方便导入

资源链接：http://download.csdn.net/detail/w565911788/4325414

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace ExcelToXml
{
    class Program
    {
        /// <summary>
        /// 返回Excel数据源
        /// </summary>
        /// <param name="filename">文件路径</param>
        /// <returns></returns>
        static public DataTable ExcelToDataSet(string filename)
        {
            DataTable ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Extended Properties=Excel 8.0;" +
                            "data source=" + filename;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [Sheet1$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataTable();
            myCommand.Fill(ds);
            myConn.Close();
            return ds;
        }

        static void Main(string[] args)
        {
            string filePath = @"F:\TDDOWNLOAD\TestlinkConvert_V67_20120317_TY\test-json-api-success-report.xls";           
            FileInfo fi = new FileInfo(filePath);
            string xmlPath = @"D:\" + fi.Name.Replace(".xlsx", "").Replace(".xls", "")+".xml";

            FileInfo xmlFi = new FileInfo(xmlPath);
            FileStream fs = xmlFi.Open(FileMode.OpenOrCreate, FileAccess.Write, FileShare.None);
            StreamWriter sw= new StreamWriter(fs);

            string content = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<testcases>\n";
            sw.WriteLine(content);

            DataTable dt = ExcelToDataSet(filePath);
            if (dt.Rows.Count > 1)
            {
                for (int i = 0; i < dt.Rows.Count-1; i+=2)
                {
                    content = "";
                    content += "<testcase name=\""+dt.Rows[i][0].ToString()+"\">";
                    content += "<summary><![CDATA[" + dt.Rows[i][1].ToString() + @"]]></summary><preconditions><![CDATA[correct token: ae873897a0a9a2afdb676eefe4e864e2b0e3abb07cd73cf0944e77d6c792b1aa7926f07dd201b6aa<br />]]></preconditions>
                                <execution_type><![CDATA[0]]></execution_type><importance><![CDATA[0]]></importance><steps>";
                    content += "<step><step_number><![CDATA[1]]></step_number><actions><![CDATA[" + dt.Rows[i][2].ToString() + "]]></actions>";
                    content += "<expectedresults><![CDATA[" + dt.Rows[i][3].ToString() + "]]></expectedresults><execution_type><![CDATA[1]]></execution_type></step>";
                    content += "<step><step_number><![CDATA[2]]></step_number><actions><![CDATA[" + dt.Rows[i+1][2].ToString() + "]]></actions>";
                    content += "<expectedresults><![CDATA[" + dt.Rows[i+1][3].ToString() + "]]></expectedresults><execution_type><![CDATA[1]]></execution_type></step>";
                    content += "</steps></testcase>";
                    sw.WriteLine(content);                   
                }
            }
            sw.WriteLine("</testcases>\n");
            sw.Close();
            fs.Close();
        }
    }
}