每30秒连接一次服务器,连接成功单元格变绿色,连接失败变红色,状态单元格为17行,2列
1,打开excel文件,进入vba编辑器,新建一个modules模块,在里面先写一个每30秒执行一次ConnServer宏
Sub StartTimer() Application.OnTime Now + TimeValue("00:00:30"), "StartTimer" ConnServer End Sub
2,再编写一个访问服务器代码的宏
Client.BaseUrl = "http://localhost:56112"填的是本地项目
Request.Resource = "/test/CheckConnServer" 访问testController下的CheckConnServer方法
Sub ConnServer() Dim Client As New WebClient, Request As New WebRequest, Response As WebResponse, ResponseString As String, Body As Object On Error GoTo MyErr Client.BaseUrl = "http://localhost:56112" Client.TimeoutMs = 100000 Request.Resource = "/chhk/CheckConnServer" Request.Method = WebMethod.HttpPost Set Body = New Dictionary Body.Add "fileName", ThisWorkbook.Name Set Request.Body = Body Set Response = Client.Execute(Request) ResponseString = Response.Content ' 判断访问状态 If ResponseString = "OK" Then ThisWorkbook.Sheets("Sales Portal").Cells(17, 2).Interior.Color = RGB(0, 176, 80) Else ThisWorkbook.Sheets("Sales Portal").Cells(17, 2).Interior.Color = RGB(255, 0, 0) End If Exit Sub MyErr: ThisWorkbook.Sheets("Sales Portal").Cells(17, 2).Interior.Color = RGB(255, 0, 0)
' 访问代码报错时单元格变红色 End Sub
3,MVC控制器中的方法
[HttpPost] [AllowAnonymous] public string CheckConnServer(string fileName) { return new TxtConnServer().TryConnServer(fileName); }
4,MVC项目中添加访问时间,工作蒲名称的类
AppDomain.CurrentDomain.BaseDirectory获取基目录,没有存在则生成一个CheckConnServer.txt文件
public class TxtConnServer { public string TryConnServer(string requestConnBody) { try { //写入连接服务器信息 string file = AppDomain.CurrentDomain.BaseDirectory + "CheckConnServer.txt"; if (!File.Exists(file)) File.Create(file).Close(); FileStream fs = new FileStream(file, FileMode.Append); StreamWriter sw = new StreamWriter(fs); sw.WriteLine(" "); sw.WriteLine(requestConnBody + "-----------" + DateTime.Now.ToChinaTime().ToString("dd-MM-yyyy HH-mm-ss"));//开始写入值 sw.Close(); fs.Close(); return "OK"; } catch (Exception ex) { return "Write error"; } } }