如何将工作表中的文件（通过 VBA 宏）上传到 Laravel 应用程序？

How to upload a file from a sheet (via VBA macro) to a laravel application?

提问by Dennis Braga

I'm trying to make a spreedsheet "upload itself" to a RESTful web service made in Laravel 4.

我正在尝试将电子表格“自行上传”到 Laravel 4 中制作的 RESTful Web 服务。

I have a web form who does the same work, but I need to, instead of make the user go to the web application and manually upload the file, make the sheet capable of upload itself from a click of a button (using macros). I have a method that receives the Input::file('filename')and open the file to read and stuff. I'm using the Microsoft.XMLHTTPVBA object to send the request to the WS. Saddly, I ain't seem to be able to upload the god damm file! I'm sending the path (absolute path) in the post method, but isn't working.

我有一个执行相同工作的 Web 表单，但我需要让用户通过单击按钮（使用宏）来使工作表能够自行上传，而不是让用户转到 Web 应用程序并手动上传文件。我有一种方法可以接收Input::file('filename')并打开文件以进行读取和操作。我正在使用Microsoft.XMLHTTPVBA 对象将请求发送到 WS。可悲的是，我似乎无法上传该死的文件！我在 post 方法中发送路径（绝对路径），但不起作用。

The problem is: How I do this within a VBA code? How do I upload a file to the server through a VBA code? And, if it is possible, how to made that compatible with a laravel application?

问题是：我如何在 VBA 代码中做到这一点？如何通过VBA代码将文件上传到服务器？而且，如果可能的话，如何使其与 Laravel 应用程序兼容？

EDIT

编辑

To proper answer to @Andreyco's question, I'm making this edit.

为了正确回答@Andreyco 的问题，我正在进行此编辑。

That's what I receive in the VBA Debug Tool when I return the dump of Input::all()

当我返回转储时，这就是我在 VBA 调试工具中收到的 Input::all()

Array
(
    [spreedsheet] => C:\Users\Android\Desktop\tarifa.xls
)

...but, when I receive the response from the web form, it looks like this.

...但是，当我收到来自 Web 表单的响应时，它看起来像这样。

Array
(
    [_token] => rvtkLep6rwvkvvXc3u0WoO6nyldylp9xI36n6gb2
    [spreedsheet] => Symfony\Component\HttpFoundation\File\UploadedFile Object
    (
        [test:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 
        [originalName:Symfony\Component\HttpFoundation\File\UploadedFile:private] => tarifa.xls
        [mimeType:Symfony\Component\HttpFoundation\File\UploadedFile:private] => application/vnd.ms-excel
        [size:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 43520
        [error:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 0
        [pathName:SplFileInfo:private] => /tmp/phpRsX5bf
        [fileName:SplFileInfo:private] => phpRsX5bf
    )
)

... because of Laravel structure and stuff. Hope it will be useful.

...因为 Laravel 结构和东西。希望它会很有用。

采纳答案by Dennis Braga

I just figure it out in the follow IT Blogand it served me "like a glove"! Just two simple VBA functions/methods that did the job so f**king well! I Just needed to pass the file and the URL and it was done!

我只是在关注IT 博客中弄清楚了， 它“像手套一样”为我服务！只需两个简单的 VBA 函数/方法就可以很好地完成这项工作！我只需要传递文件和 URL 就完成了！

Thanks @Andreyco for your help! ;)

感谢@Andreyco 的帮助！;)

回答by Hyman Miller

Here a complete, working example. If you do not need the "Please Wait" dialog just use the first code snippet and delete UploadThisFileMainthereof. Also note the server PHP test script at the very end.

这是一个完整的工作示例。如果您不需要“请稍候”对话框，只需使用第一个代码片段并将其删除UploadThisFileMain。还要注意最后的服务器 PHP 测试脚本。

Sub UploadThisFileMain()
   If ActiveWorkbook.Saved = False Then
       MsgBox "This workbook contains unsaved changes. Please save first."
       Exit Sub
   End If
   Dim ret
   ret = StartProcessing("File uploading, Please Wait...", "UploadThisFile")
   If (ret = True) Then
       MsgBox "Upload successful!"
    Else
       MsgBox "Upload failed: " & ret
   End If
End Sub

Private Function UploadThisFile()
    Dim bound As String
    bound = "A0AD2346-9849-4EF0-9A93-ACFE17910734"

    Dim url  As String
    url = "https://<YourServer>/index.php?id={" & bound & "}"

    Dim path As String
    path = ThisWorkbook.path & "\" & ThisWorkbook.Name

    sMultipart = pvGetFileAsMultipart(path, bound)

    On Error Resume Next

    Dim r
    r = pvPostMultipart(url, sMultipart, bound)

    If Err.Number <> 0 Then
      UploadThisFile = Err.Description
      Err.Clear
    Else
      UploadThisFile = True
    End If
End Function

'sends multipart/form-data To the URL using WinHttprequest/XMLHTTP
'FormData - binary (VT_UI1 | VT_ARRAY) multipart form data
Private Function pvPostMultipart(url, FormData, Boundary)
  Dim http 'As New MSXML2.XMLHTTP

  'Create XMLHTTP/ServerXMLHTTP/WinHttprequest object
  'You can use any of these three objects.
  'Set http = CreateObject("WinHttp.WinHttprequest.5")
  'Set http = CreateObject("MSXML2.XMLHTTP")
  Set http = CreateObject("MSXML2.ServerXMLHTTP")

  'Open URL As POST request
  http.Open "POST", url, False

  'Set Content-Type header
  http.setRequestHeader "Content-Type", "multipart/form-data; boundary=" + Boundary

  'Send the form data To URL As POST binary request
  http.send FormData

  'Get a result of the script which has received upload
  pvPostMultipart = http.responseText
End Function

Private Function pvGetFileAsMultipart(sFileName As String, Boundary As String) As Byte()
    Dim nFile           As Integer
    Dim sPostData       As String
    '--- read file
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        sPostData = StrConv(baBuffer, vbUnicode)
    End If
    Close nFile
    '--- prepare body
    sPostData = "--" & Boundary & vbCrLf & _
        "Content-Disposition: form-data; name=""uploadfile""; filename=""" & Mid$(sFileName, InStrRev(sFileName, "\") + 1) & """" & vbCrLf & _
        "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & _
        sPostData & vbCrLf & _
        "--" & Boundary & "--"
    '--- post
    pvGetFileAsMultipart = pvToByteArray(sPostData)
End Function

Private Function pvToByteArray(sText As String) As Byte()
    pvToByteArray = StrConv(sText, vbFromUnicode)
End Function

Create a new module Processing_Code:

创建一个新模块Processing_Code：

Public Processing_Message As String
Public Macro_to_Process As String
Public Return_Value As String

Function StartProcessing(msg As String, code As String)

   Processing_Message = msg    'Set the message that is displayed
                               'in the dialog box

   Macro_to_Process = code     'Set the macro that is run after the
                               'dialog box is active

   Processing_Dialog.Show      'Show the Dialog box

   StartProcessing = Return_Value
End Function

Create a form Processing_Dialog. Set StartUpPositionto 2 - CenterScreen. Add code:

创建一个表单Processing_Dialog。设置StartUpPosition为2 - CenterScreen。添加代码：

Private Sub UserForm_Initialize()

   lblMessage.Caption = Processing_Message  'Change the Label
                                            'Caption

End Sub

Private Sub UserForm_Activate()

   Me.Repaint                                        'Refresh the UserForm
   Return_Value = Application.Run(Macro_to_Process)  'Run the macro
   Unload Me                                         'Unload the UserForm

End Sub

Now add a button to your Worksheet (If there is no "Developer" tab, go to "Options" -> "Customize Ribbon" -> enable checkbox "Developer") and assign macro UploadThisFileMain.

现在向您的工作表添加一个按钮（如果没有“开发人员”选项卡，请转到“选项”->“自定义功能区”-> 启用复选框“开发人员”）并分配宏UploadThisFileMain。

For the server part use this PHP test script:

对于服务器部分，请使用此 PHP 测试脚本：

<?php
foreach (getallheaders() as $name => $value) {
    echo "$name: $value\n";
}

echo "POST:";
print_r($_POST);
echo "GET:";
print_r($_GET);
echo "FILES:";
print_r($_FILES);

$entityBody = file_get_contents('php://input');
        echo "Body:$entityBody";

exit;
$base_dir = dirname( __FILE__ ) . '/upload/';
if(!is_dir($base_dir))
    mkdir($base_dir, 0777);
move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $base_dir . '/' . $_FILES["uploadfile"]["name"]);
?>

Sources:

资料来源：

• https://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/
• http://www.motobit.com/tips/detpg_post-binary-data-url/
• http://fm4dd.com/programming/shell/microsoft-vbs-https-upload.htm
• https://support.microsoft.com/en-us/kb/162257

• https://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/
• http://www.motobit.com/tips/detpg_post-binary-data-url/
• http://fm4dd.com/programming/shell/microsoft-vbs-https-upload.htm
• https://support.microsoft.com/en-us/kb/162257

回答by Hyman Miller

Simply posting file directly as binary body:

只需将文件直接作为二进制体发布：

Sub UploadThisFile()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "LOADING..."

    Dim url  As String
    url = "https://<YourServer>/index.php"

    Dim path As String
    path = ThisWorkbook.path & "\" & ThisWorkbook.Name

    sData = pvGetFileAsData(path)
    mimeType = "application/vnd.ms-excel.sheet.macroEnabled.12"

    On Error Resume Next

    Dim r
    r = pvPost(url, mimeType, sData)

    Range("A1").Select
    If Err.Number <> 0 Then
      ActiveCell.FormulaR1C1 = "Upload failed: " & Err.Description
      Err.Clear
    Else
      ActiveCell.FormulaR1C1 = r
    End If
End Sub

Private Function pvPost(url, mimeType, body)
  Dim http 'As New MSXML2.XMLHTTP
  Set http = CreateObject("MSXML2.ServerXMLHTTP")
  http.Open "POST", url, False
  http.setRequestHeader "Content-Type", mimeType
  http.send body
  pvPost = http.responseText
End Function

Private Function pvGetFileAsData(sFileName As String) As Byte()
    Dim nFile           As Integer
    Dim sPostData       As String
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        pvGetFileAsData = baBuffer
    End If
    Close nFile
End Function

Store file on server side via PHP script:

通过 PHP 脚本在服务器端存储文件：

$entityBody = file_get_contents('php://input');
file_put_contents('file.xlsm', $entityBody);