Excel vba中访问ASP.NET MVC项目，记录访问时间，文件名称

每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";
            }

        }
    }