从Excel VBA将庞大的数据集发布到Web服务 [英] Posting huge datasets to a webservice from excel VBA

问题描述

您好，Excel vba编码员

Hello fellow Excel vba coders

我的excel工作表中有一个很棒的宏，我在该宏中根据用户输入的行编译XML-之后，它将xml发布到Web服务.

I have this great macro in my excel sheet, where i compile XML based on the rows the user puts in - after this it post the xml to a webservice.

您可以在下面查看我的代码-这很简单:

You can have a look at my code below - It is fairly simple:

Set XMLHttpRequest = New MSXML2.XMLHTTP    
With XMLHttpRequest
    .Open "POST", URL, False
    .setRequestHeader "Content-Type", "text/xml; encoding='utf-8'"
    .setRequestHeader "Content-Length", strLength
    .send strXML
End With

现在，当行数少于200时，它可以很好地工作，但是当行数超过1000行时，它就会超时.我发布的XML字符串确实很大，而且我很确定这就是它超时的原因.

Right now it works great when there is less than 200 rows, yet it times out when the row number gets above 1000 rows. The string of XML I post is really big, and i'm quite sure that's the reason it times out.

现在我的问题是，如何将超过1.000行，甚至超过20.000行的庞大数据集发布到Web服务?

Now my problem is, how do i post this huge dataset, that exceed 1.000 rows, maybe even above 20.000 rows, to a webservice?

到目前为止，我已经花了很多时间在网上寻找可能的解决方案，但是还没有找到解决这个问题的方法.到目前为止，我有以下想法可以解决该问题:

So far i have spend a lot of time to look for a possible solution around the web, but have yet to find a way to handle this. So far i have the following ideas to solve the problem:

• 将工作表复制到新工作簿中，获取新工作簿的位置，然后将文件转换为Base64字符串，然后将整个文件发布到新的.asmx服务中，并以C#代码处理工作簿".
• 将巨大的字符串转换为某种字节数组，然后将其发布到新的.asmx Web服务并处理C#代码.

我真的希望你们中的一个能指出我正确的方向并帮助我解决这个问题吗?

I really hope one of you guys can point me in the right direction and help me solve this problem?

推荐答案

好-我找到了解决问题的方法，这似乎是解决此问题的最佳方法.

Ok - I found a solution to my problem, and it seems like the best way to handle this problem.

我使用以下功能制作工作簿的副本:

I use the following function to make a copy of the workbook:

Private Function saveAS(Path As String)
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    ActiveWorkbook.Sheets.Copy
    ActiveWorkbook.saveAS Path, FileFormat:=51
    ActiveWorkbook.Close savechanges:=True

    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Function

然后我将文件编码为base64字符串，如下所示:

Then i encode the file into a base64string, like so:

Private Function EncodeFileBase64(Filename As String) As String
    Dim arrData() As Byte
    Dim fileNum As Integer

    Filename = Filename + ".xlsx"
    fileNum = FreeFile
    Open Filename For Binary As fileNum
    ReDim arrData(LOF(fileNum) - 1)
    Get fileNum, , arrData
    Close fileNum

    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement

    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeFileBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing
End Function

然后我将编码后的字符串发送到我自己的.asmx Webservice并在C#中使用它.webmethod看起来像这样:

And then i send the encoded string to my own .asmx webservice and work with it in C# The webmethod looks like this:

    [WebMethod]
    [ScriptMethod(UseHttpGet = true)]
    public string UploadXML(string base64string)
    {
        try
        {
            byte[] bytes = Convert.FromBase64String(base64string);

            using (MemoryStream ms = new MemoryStream(bytes))
            {
                using (var package = new ExcelPackage(ms))
                {
                    ExcelWorkbook workBook = package.Workbook;
                    ExcelWorksheet settings = workBook.Worksheets.SingleOrDefault(w => w.Name == "sheet1");
                    ExcelWorksheet data = workBook.Worksheets.SingleOrDefault(w => w.Name == "sheet2");

                    //Getting data
                    string SS1 = (string)settings.Cells[8, 3].Value;
                    string ss2 = (string)settings.Cells[7, 3].Value;
                }
            }
            return "success";

        }
        catch (Exception ee)
        {
            return ee.Message;
        }
    }

我只需要找到一种很好的方法来提取智能算法中的所有数据，我根本不认为这是个问题:)

I just need to find a good way to pull out all the data in a smart algorithm, i dont think that will be a problem at all :)

这篇关于从Excel VBA将庞大的数据集发布到Web服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！