Data exchange between Excel and a LAMP server

Premise

Since 2011 the data collected in my company is stored and managed in an Excel spreadsheet. The company has grown up and now the data in exchanged in three offices in different parts of the Nation.

Since 2013 was adopted a solution based on a VPN.

Since 2017 the Excel macros have been transformed in a VB NET application.

Not they asked to realize a web application to manage the data.

How to feed the beast (the web application)

Optimal solution

The final solution will be a web service that exchanges the data from the web application that collects the client's data and the new application that store and manage the data.

Call to a web service for every insert/update in the Excel file

What to do in the meantime while the integration is not ready?

The VB NET application calls a web service to insert and update the data.

It's an easy trick creating a REST web service.

        Dim md5 As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create()
        Dim request As System.Net.HttpWebRequest = System.Net.WebRequest.Create(Me.gatUrl & "?token=" & Me.GetMd5Hash(md5, token))
        request.AutomaticDecompression = System.Net.DecompressionMethods.Deflate Or Net.DecompressionMethods.GZip
        request.Method = "POST"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Timeout = 5000


        Dim dataStream As IO.Stream = request.GetRequestStream()
        Dim postData As String = Newtonsoft.Json.JsonConvert.SerializeObject(rigaRichiesta)
        Dim enc As System.Text.UTF8Encoding = New System.Text.UTF8Encoding()
        Dim byteArray As Byte() = enc.GetBytes(postData)
        Using stream = request.GetRequestStream()
            stream.Write(byteArray, 0, byteArray.Length)
        End Using
        Dim responseString As String
        Try
            Dim result As System.Net.HttpWebResponse = request.GetResponse()
            Dim reader As New System.IO.StreamReader(result.GetResponseStream())
            responseString = reader.ReadToEnd()
            result.Close()
        Catch ex As Exception
            Me.Log.Text = "Errore nella chimata a " + Me.gatUrl + " " + ex.Message
            Me.Log.BackColor = Drawing.Color.Red

            Exit Sub
        End Try

The solution is slow: 10 minutes for 4k rows.

Call to a web service at Excel close passing all the data

Another solution is:

• Save the Excel data as CSV;
• Pass the CSV file as content of a REST call.

        Dim currentWorkbook As String = Me.workbook.FullName
        Dim currentFormat As Long = Me.workbook.FileFormat

        Dim nomeFile = currentWorkbook + ".csv"
        Me.foglioRichieste.SaveAs(nomeFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
        Me.foglioRichieste.SaveAs(currentWorkbook, currentFormat)

        Dim contenuto As String = System.IO.File.ReadAllText(nomeFile)

        Dim dt As DateTime = DateTime.Now
        Dim token As String = Me.token & dt.Year & dt.Month.ToString.PadLeft(2, "0") & dt.Day.ToString.PadLeft(2, "0")

        Dim md5 As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create()
        Dim request As System.Net.HttpWebRequest = System.Net.WebRequest.Create(Me.gatUrl & "?azione=file&token=" & Me.GetMd5Hash(md5, token))
        request.AutomaticDecompression = System.Net.DecompressionMethods.Deflate Or Net.DecompressionMethods.GZip
        request.Method = "POST"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Timeout = 50000

        Dim dataStream As IO.Stream = request.GetRequestStream()

        Dim enc As System.Text.UTF8Encoding = New System.Text.UTF8Encoding()
        Dim byteArray As Byte() = enc.GetBytes(contenuto)
        Using stream = request.GetRequestStream()
            stream.Write(byteArray, 0, byteArray.Length)
        End Using
        Dim responseString As String
        Try
            Dim result As System.Net.HttpWebResponse = request.GetResponse()
            Dim reader As New System.IO.StreamReader(result.GetResponseStream())
            responseString = reader.ReadToEnd()
            result.Close()
        Catch ex As Exception
            Me.Log.Text = "Errore nella chimata a " + Me.gatUrl + " " + ex.Message
            Me.Log.BackColor = Drawing.Color.Red

            Exit Sub
End Try

In 2 seconds the data has been updated in the server.

Bonus trick: saving the Excel file as CSV using Visual Basic produce a file of good quality, UTF8 encoded and that use coma as separator.