嗨，我需要使用Flask应用程序读取excel电子表格数据，并在应用程序中使用python代码来读取电子表格数据并将其作为CSV文件保存到某个位置。
要从本地Excel执行此操作，我使用以下VBScript将HTTP响应发送到烧瓶应用程序URL
Sub example()
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = "http://192.168.100.8:5000/"
objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
End Sub
我从链接中引用了上面的代码：如何使用VBA从Excel向服务器发送HTTP POST请求？
我正在使用以下烧瓶应用程序代码从Excel 打印出HTTP响应
import logging
from flask import Flask, request
#Creating an object
log=logging.getLogger()
format = "%(asctime)s %(message)s"
logging.basicConfig(format=format, level=logging.INFO, filename='Job_history_logs.log')
app = Flask(__name__)
#@app.route('/', methods=['GET'])
#def hello():
# return "hello world"
@app.route('/', methods=['POST'])
def write():
content = request.json
print(content)
return content
if __name__ == '__main__':
app.run(host='0.0.0.0')
以下是尝试访问地址时的错误日志
2020-03-17 18:58:19,122 192.168.100.8 - - [17/Mar/2020 18:58:19] "POST / HTTP/1.1" 500 -
2020-03-17 18:58:38,976 Exception on / [POST]
Traceback (most recent call last):
File "C:Usersprasanna.kommuriAppDataLocalContinuumanaconda3libsite-packagesflaskapp.py", line 2446, in wsgi_app
response = self.full_dispatch_request()
File "C:Usersprasanna.kommuriAppDataLocalContinuumanaconda3libsite-packagesflaskapp.py", line 1952, in full_dispatch_request
return self.finalize_request(rv)
File "C:Usersprasanna.kommuriAppDataLocalContinuumanaconda3libsite-packagesflaskapp.py", line 1967, in finalize_request
response = self.make_response(rv)
File "C:Usersprasanna.kommuriAppDataLocalContinuumanaconda3libsite-packagesflaskapp.py", line 2097, in make_response
"The view function did not return a valid response. The"
TypeError: The view function did not return a valid response. The function either returned None or ended without a return statement.
2020-03-17 18:58:38,978 192.168.100.8 - - [17/Mar/2020 18:58:38] "POST / HTTP/1.1" 500 -
这也是spyder IDE中python控制台中的响应(我正在运行flask应用程序(
* Serving Flask app "sampleapp2" (lazy loading)
* Environment: production
WARNING: This is a development server. Do not use it in a production deployment.
Use a production WSGI server instead.
* Debug mode: off
None
1( Excel中的数据未到达url2( 如何将整个电子表格数据包装到关闭的电子表格中的HTTPObject是另一项要求。
有人能帮我哪里出错了吗？或者有什么建议吗
我能够解决这个
以下VBA代码将从电子表格中获取数据，而不显式提及任何单元格范围，并最终在Json格式的parseData((方法中的名为result的变量中捕获数据。
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
最后，为了在Flask中捕获这一点，我使用了以下代码，它将从Excel中捕获Json主体，并将其保存到csv格式的文件
# -*- 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()