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.
Top comments (0)