excel宏调用webservice使用存储过程同步excel数据的 -电脑资料

excel宏：

    随后更新

webservice：

    1.创建空应用程序

    2.添加web服务

    3.创建数据库访问类库DataHelper

sqlserver：

    创建数据同步的存储过程

    以下是一些需要的代码，比较杂乱，有空再整理整理，

excel宏调用webservice使用存储过程同步excel数据的

DataFactory.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;namespace DataHelper{public class DataFactory{////// 获取数据类型1为sqlserver,2为access///////// public static DbHelper GetHelper(){string ConnStr = ConfigurationManager.AppSettings["ConnStr"].ToString();int Dbtype = 1;Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);switch (Dbtype){case 1:return new SqlHelper(ConnStr);case 2:return new OledbHelper(ConnStr);default:return new SqlHelper(ConnStr);}}public static DbHelper GetHelper1(){string ConnStr = ConfigurationManager.AppSettings["ConnStr1"].ToString();int Dbtype = 1;Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);switch (Dbtype){case 1:return new SqlHelper(ConnStr);case 2:return new OledbHelper(ConnStr);default:return new SqlHelper(ConnStr);}}public static DbHelper GetYellowPageHelper(){string ConnStr = ConfigurationManager.AppSettings["ConnStrYP"].ToString();int Dbtype = 1;Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);switch (Dbtype){case 1:return new SqlHelper(ConnStr);case 2:return new OledbHelper(ConnStr);default:return new SqlHelper(ConnStr);}}////// Discuz数据库////// public static DbHelper GetYellowPageHelperDNT(){string ConnStr = ConfigurationManager.AppSettings["ConnStrDNT"].ToString();int Dbtype = 1;Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);switch (Dbtype){case 1:return new SqlHelper(ConnStr);case 2:return new OledbHelper(ConnStr);default:return new SqlHelper(ConnStr);}}}}

DbHelper.cs

using System;using System.Collections.Generic;using System.Data;using System.Data.Common;using System.Text;using System.Collections.Specialized;namespace DataHelper{public abstract class DbHelper{////// 得到数据库链接///public abstract IDbConnection Connection { get; }////// 打开数据库连接;///public abstract void Open();////// 关闭数据库链接;///public abstract void Close();////// 开始一个事务;///public abstract void BeginTrans();////// 提交一个事务;///public abstract void CommitTrans();////// 回滚一个事务;///public abstract void RollBackTrans();////// 执行sql语句,返回受影响集合数//////命令类型///命令字符串///命令参数/// public abstract int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars);////// 执行sql语句,返回IDataReader//////命令类型///命令字符串///命令参数/// public abstract DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars);////// 执行sql语句，返回结构的第一行，第一列的值//////命令类型///命令字符串///命令参数/// public abstract object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars);////// 执行sql语句，获得datatable//////命令类型///命令字符串///命令参数/// public abstract DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars);}}

OledbHelper.cs

using System;using System.Collections.Generic;using System.Collections.Specialized;using System.Text;using System.Data;using System.Data.Common;using System.Data.OleDb;namespace DataHelper{class OledbHelper : DbHelper{private OleDbConnection conn;private OleDbTransaction trans;private bool inTransaction = false; //指示当前是否正处于事务中////// 构造函数，初始OledbConnection对象//////public OledbHelper(string StrConnection){this.conn = new OleDbConnection(StrConnection);}////// 获取Conneciton///public override IDbConnection Connection{get { return this.conn; }}////// 打开数据库连接///public override void Open(){if (conn.State != ConnectionState.Open){this.conn.Open();}}////// 关闭数据库连接，释放资源///public override void Close(){if (this.trans != null){this.trans.Dispose();}if (conn.State != ConnectionState.Closed){conn.Close();}conn.Dispose();}public override void BeginTrans(){trans = conn.BeginTransaction();inTransaction = true;}public override void CommitTrans(){trans.Commit();inTransaction = false;}public override void RollBackTrans(){trans.Rollback();inTransaction = false;}////// 参数准备////////////public void PrepareCommand(OleDbCommand cmd, CommandType cmdType, string cmdText, NameValueCollection pars){if (this.trans != null){cmd.Transaction = this.trans;}cmd.Connection = conn;cmd.CommandType = cmdType;cmd.CommandText = cmdText;if (pars != null && pars.Count > 0){string[] keys = pars.AllKeys;for (int i = 0; i < pars.Count; i++){cmd.Parameters.AddWithValue(keys[i], pars[i]);}}}public override int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars){using (OleDbCommand cmd = new OleDbCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}}public override DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars){using (OleDbCommand cmd = new OleDbCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);DbDataReader dr = cmd.ExecuteReader();cmd.Parameters.Clear();return dr;}}public override object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars){using (OleDbCommand cmd = new OleDbCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}}public override DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars){using (OleDbCommand cmd = new OleDbCommand()){DataTable dt = new DataTable();this.PrepareCommand(cmd, cmdType, cmdText, pars);using (DbDataAdapter da = new OleDbDataAdapter()){da.SelectCommand = cmd;da.Fill(dt);cmd.Parameters.Clear();return dt;}}}}}

SqlHelper.cs

using System;using System.Collections.Generic;using System.Collections.Specialized;using System.Text;using System.Data;using System.Data.Common;using System.Data.SqlClient;namespace DataHelper{class SqlHelper : DbHelper{private SqlConnection conn;private SqlTransaction trans;private bool inTransaction = false; //指示当前是否正处于事务中////// 获取IDbConnection///public override IDbConnection Connection{get { return this.conn; }}////// 构造函数，初始SqlConnection对象//////public SqlHelper(string StrConnection){this.conn = new SqlConnection(StrConnection);}////// 打开数据库连接///public override void Open(){if (conn.State != ConnectionState.Open){this.conn.Open();}}////// 关闭数据库连接，释放资源///public override void Close(){if (this.trans != null){this.trans.Dispose();}if (conn.State != ConnectionState.Closed){conn.Close();}conn.Dispose();}////// 开始事务///public override void BeginTrans(){trans = conn.BeginTransaction();inTransaction = true;}////// 提交事务///public override void CommitTrans(){trans.Commit();inTransaction = false;}////// 回滚事务///public override void RollBackTrans(){trans.Rollback();inTransaction = false;}////// 参数准备////////////public void PrepareCommand(SqlCommand cmd, CommandType cmdType, string cmdText, NameValueCollection pars){if (this.trans != null){cmd.Transaction = this.trans;}cmd.Connection = conn;cmd.CommandType = cmdType;cmd.CommandText = cmdText;if (pars != null && pars.Count > 0){string[] keys = pars.AllKeys;for (int i = 0; i < pars.Count; i++){cmd.Parameters.AddWithValue(keys[i], pars[i]);}}}////// 执行sql命令，返回受影响行数//////命令类型///命令///参数组/// 受影响行数public override int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars){using (SqlCommand cmd = new SqlCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}}////// 执行sql命令，返回DbDataReader//////命令类型///命令///参数组/// DbDataReaderpublic override DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars){using (SqlCommand cmd = new SqlCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);DbDataReader dr = cmd.ExecuteReader();cmd.Parameters.Clear();return dr;}}////// 执行sql语句，返回第一行第一列/////////////// public override object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars){using (SqlCommand cmd = new SqlCommand()){this.PrepareCommand(cmd, cmdType, cmdText, pars);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}}////// 执行sql语句，返回DataTable//////命令类型///命令///参数组/// public override DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars){using (SqlCommand cmd = new SqlCommand()){DataTable dt = new DataTable();this.PrepareCommand(cmd, cmdType, cmdText, pars);using (DbDataAdapter da = new SqlDataAdapter()){da.SelectCommand = cmd;da.Fill(dt);cmd.Parameters.Clear();return dt;}}}}}

WebService.asmx

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Services;using System.Data.Common;using System.Data;using System.Collections.Specialized;using DataHelper;namespace myWebService{////// WebService 的摘要说明///[WebService(Namespace = "http://tempuri.org/")][WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)][System.ComponentModel.ToolboxItem(false)]// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释，
电脑资料
《excel宏调用webservice使用存储过程同步excel数据的》(https://www.unjs.com)。// [System.Web.Script.Services.ScriptService]public class WebService : System.Web.Services.WebService{[WebMethod]public string HelloWorld(){string uid = GetGuid();string test = "n7,5,20150701,10,20150701|n8,5,20150701,10,20150701|n9,5,20150701,9,20150701";string[] rows=test.Split('|');int rowsCount=rows.Length;int colsCount=rows[0].Length;string cmd = string.Empty;string floorno=string.Empty;NameValueCollection nvc=new NameValueCollection();string result = string.Empty;DataHelper.DbHelper dh = DataHelper.DataFactory.GetHelper();dh.Open();dh.BeginTrans();try{List sql = new List();for (int i = 0; i < rowsCount; i++){sql.Add("insert into TARGETHISTORY(EQNO,FLOORNO,RUNSTART,RUNTIME,UPDATETIME,UID) values(@EQNO,@FLOORNO,@RUNSTART,@RUNTIME,@UPDATETIME,@UID)");nvc = new NameValueCollection();nvc.Add("@EQNO", rows[i].Split(',')[0]);nvc.Add("@FLOORNO", rows[i].Split(',')[1]);nvc.Add("@RUNSTART", rows[i].Split(',')[2]);nvc.Add("@RUNTIME", rows[i].Split(',')[3]);nvc.Add("@UPDATETIME", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff"));nvc.Add("@UID", uid);floorno = rows[i].Split(',')[1];dh.ExecuteNonQuery(CommandType.Text, sql[i], nvc);}nvc = new NameValueCollection();nvc.Add("@UID", uid);nvc.Add("@FLOORNO", floorno);dh.ExecuteNonQuery(CommandType.StoredProcedure, "DeviceShareLoad", nvc);dh.CommitTrans();result= "数据导入成功！";}catch (Exception ex){dh.RollBackTrans();dh.Close();result = "数据导入失败！请联系 IT！ 错误原因："+ex.ToString();}finally{dh.Close();}return result;}private static string GetGuid(){System.Guid guid = new Guid();guid = Guid.NewGuid();return guid.ToString();}}}

USE [test]GO/****** Object: StoredProcedure [dbo].[DeviceShareLoad] Script. Date: 07/10/2015 02:49:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[DeviceShareLoad]@floorno nvarchar(50),@uid nvarchar(50)ASBEGINmerge into targettable as tusing (select * from TARGETHISTORY where uid=@uid) as son t.floorno=@floorno and s.floorno=@floorno and t.eqno=s.eqnowhen matchedthen update set t.eqno=s.eqno,t.floorno=s.floorno,t.runstart=s.runstart,t.runtime=s.runtime,t.updatetime=getdate()when not matched and s.floorno=@floornothen insert (eqno,floorno,runstart,runtime,updatetime) values(s.eqno,s.floorno,s.runstart,s.runtime,getdate())when not matched by source and t.floorno=@floornothen delete;SET NOCOUNT ON;END