【自动化测试】C#开发自动化测试平台（3）——操作Excel

最新推荐文章于 2023-08-11 18:39:50 发布

Bill Micolar

最新推荐文章于 2023-08-11 18:39:50 发布

阅读量274

点赞数

分类专栏：自动化测试平台C# 文章标签： excel c# microsoft

本文链接：https://blog.csdn.net/weixin_49082594/article/details/130743130

版权

本文介绍如何使用C#开发自动化测试平台，并结合Excel进行数据操作，利用Excel作为测试数据源，实现测试脚本与Excel的紧密结合。

摘要由CSDN通过智能技术生成

对Excel进行基本操作，来实现将其用于简单数据源和测试脚本。

using System;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Collections.Generic;

namespace Utility
{
   
    public static class ExcelOp
    {
        public static void InsertIntoExcel(string filePath,List<string> lt)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + filePath;
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "Insert into [Sheet1$] Values("+ "'"+lt[0]+"'," + "'" + lt[2] + "'," + "'" + lt[3] + "'" + ")";
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public static void InsertRowIntoExcel(string filePath, string st)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + filePath;
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "Insert into [Sheet1$] Values('" + st + "');";
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public static void InsertIntoExcelByUser(string filePath, List<string> lt)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + filePath;
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "Insert into [Sheet1$] Values(" + "'" + lt[0] + "'," + "'" + lt[1] + "'," + "'" + lt[2] + "'," + "'" + lt[3] + "'," + "'" + lt[4] + "'"+")";
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public static void UpdateExcel(string filePath,string OldName, List<string> lt)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + filePath;
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "Update [Sheet1$] set Name = '"+lt[0]+"',XPath = '"+lt[2]+"',ImgPath = '"+lt[3]+"' where Name = '"+ OldName + "'";
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public static void UpdateImgInExcel(string filePath,string ControlName, string IMG)