I was able to resolve this
Following VBA code will take the data from spreadsheet without explicitly mentioning of any cell ranges and finally capturing it in a variable called result in parseData() method in Json format .
Function toJSON(rangeToParse As Range, parseAsArrays As Boolean) As String
Dim rowCounter As Integer
Dim columnCounter As Integer
Dim parsedData As String: parsedData = "["
Dim temp As String
If parseAsArrays Then ' Check to see if we need to make our JSON an array; if not, we'll make it an object
For rowCounter = 1 To rangeToParse.Rows.Count ' Loop through each row
temp = "" ' Reset temp's value
For columnCounter = 1 To rangeToParse.Columns.Count ' Loop through each column
temp = temp & """" & rangeToParse.Cells(rowCounter, columnCounter) & """" & ","
Next
temp = "[" & Left(temp, Len(temp) - 1) & "]," ' Remove extra comma from after last object
parsedData = parsedData & temp ' Add temp to the data we've already parsed
Next
Else
For rowCounter = 2 To rangeToParse.Rows.Count ' Loop through each row starting with the second row so we don't include the header
temp = "" ' Reset temp's value
For columnCounter = 1 To rangeToParse.Columns.Count ' Loop through each column
temp = temp & """" & rangeToParse.Cells(1, columnCounter) & """" & ":" & """" & rangeToParse.Cells(rowCounter, columnCounter) & """" & ","
Next
temp = "{" & Left(temp, Len(temp) - 1) & "}," ' Remove extra comma from after last object
parsedData = parsedData & temp ' Add temp to the data we've already parsed
Next
End If
parsedData = Left(parsedData, Len(parsedData) - 1) & "]" ' Remove extra comma and add the closing bracket for the JSON array
toJSON = parsedData ' Return the JSON data
End Function
Function getValuesRange(sheet As String) As Range
' Row variables
Dim usedRows As Integer: usedRows = 0
Dim rowCounter As Integer: rowCounter = 1
Dim rowsToCount As Integer: rowsToCount = 100
' Column variables
Dim usedColumns As Integer: usedColumns = 0
Dim columnCounter As Integer: columnCounter = 1
Dim columnsToCount As Integer: columnsToCount = 50
Do While rowCounter <= rowsToCount ' Loop through each row
Do While columnCounter <= columnsToCount ' Loop through each column
If Worksheets(sheet).Cells(rowCounter, columnCounter) <> "" Then ' Check to see if the cell has a value
usedRows = rowCounter ' Since the current row has a cell with a value in it, set usedRows to the current row
If columnCounter > usedColumns Then
usedColumns = columnCounter ' If the current column is greater than usedColumns, set usedColumns to the current column
End If
If usedRows = rowsToCount Then
rowsToCount = rowsToCount + 100 ' If the value of usedRows reaches the rowsToCount limit, then extend the rowsToCount limit by 100
End If
If usedColumns = columnsToCount Then
columnsToCount = columnsToCount + 50 ' If the value of usedColumns reaches the columnsToCount limit, then extend the columnsToCount limit by 100
End If
End If
columnCounter = columnCounter + 1 ' Increment columnCounter
Loop
rowCounter = rowCounter + 1 ' Increment rowCounter
columnCounter = 1 ' Reset the columnCounter to 1 so we're always checking the first column every time we loop
Loop
Set getValuesRange = Worksheets(sheet).Range("a1", Worksheets(sheet).Cells(usedRows, usedColumns).Address) ' Return the range of cells that have values
End Function
Sub parseData()
result = toJSON(getValuesRange("Sheet1"), False) ' Set cell B1's value to our JSON data
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = "http://127.0.0.1:5555/write"
objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-Type", "application/json"
objHTTP.send (result)
End Sub
Finally to capture this in Flask i have used the following code which will take the Json body captured from Excel and save it to a file in csv format
# -*- coding: utf-8 -*-
import csv
import logging
import datetime
import cherrypy
from paste.translogger import TransLogger
from flask import Flask, request, jsonify
#Creating an object
log=logging.getLogger()
format = "%(asctime)s %(message)s"
logging.basicConfig(format=format, level=logging.INFO, filename='Job_history_logs_vb.log')
app = Flask(__name__)
class FotsTransLogger(TransLogger):
def write_log(self, environ, method, req_uri, start, status, bytes):
""" We'll override the write_log function to remove the time offset so
that the output aligns nicely with CherryPy's web server logging
i.e.
[08/Jan/2013:23:50:03] ENGINE Serving on 0.0.0.0:5000
[08/Jan/2013:23:50:03] ENGINE Bus STARTED
[08/Jan/2013:23:50:45 +1100] REQUES GET 200 / (192.168.172.1) 830
becomes
[08/Jan/2013:23:50:03] ENGINE Serving on 0.0.0.0:5000
[08/Jan/2013:23:50:03] ENGINE Bus STARTED
[08/Jan/2013:23:50:45] REQUES GET 200 / (192.168.172.1) 830
"""
if bytes is None:
bytes = '-'
remote_addr = '-'
if environ.get('HTTP_X_FORWARDED_FOR'):
remote_addr = environ['HTTP_X_FORWARDED_FOR']
elif environ.get('REMOTE_ADDR'):
remote_addr = environ['REMOTE_ADDR']
now = datetime.now() # current date and time
d = {
'REMOTE_ADDR': remote_addr,
'REMOTE_USER': environ.get('REMOTE_USER') or '-',
'REQUEST_METHOD': method,
'REQUEST_URI': req_uri,
'HTTP_VERSION': environ.get('SERVER_PROTOCOL'),
'time': now.strftime('%d/%b/%Y:%H:%M:%S', start),
'status': status.split(None, 1)[0],
'bytes': bytes,
'HTTP_REFERER': environ.get('HTTP_REFERER', '-'),
'HTTP_USER_AGENT': environ.get('HTTP_USER_AGENT', '-'),
}
message = self.format % d
self.logger.log(self.logging_level, message)
def convert_json_to_csv(data):
# now we will open a file for writing
data_file = open('data_file.csv', 'w')
# create the csv writer object
csv_writer = csv.writer(data_file)
# Counter variable used for writing
# headers to the CSV file
count = 0
for i in data:
if count == 0:
# Writing headers of CSV file
header = i.keys()
csv_writer.writerow(header)
count += 1
# Writing data of CSV file
csv_writer.writerow(i.values())
#closing the file
data_file.close()
return None
@app.route('/write', methods=['POST'])
def write():
global a
a = request.json
log.info(a)
convert_json_to_csv(a)
print(a)
return jsonify(a)
@app.route('/read', methods=['GET'])
def hello():
return jsonify(a)
def run_server():
# Enable WSGI access logging via Paste
app_logged = TransLogger(app)
# Mount the WSGI callable object (app) on the root directory
cherrypy.tree.graft(app_logged, '/')
# Set the configuration of the web server
cherrypy.config.update({
'engine.autoreload_on': True,
'log.screen': True,
'server.socket_port': 5555,
'server.socket_host': '127.0.0.1'
})
# Start the CherryPy WSGI web server
cherrypy.engine.start()
cherrypy.engine.block()
if __name__ == '__main__':
# app.run(host='0.0.0.0', debug=False)
run_server()
