openpyxl获取excel范围内的数据并返回二维数组

#!/usr/bin/env python
#coding:utf-8
import sys
reload(sys)
sys.setdefaultencoding(‘utf-8’)
def get_range_data(data_range,save_path,sheetName):
‘’’
獲取excel範圍內的數據，並返回二維數組
:param data_range: 需要獲取的數據的範圍： “B11:H34”
:param save_path: excel的文件路徑，不存在報錯並退出
:param sheetName:sheet頁的名字，不存在會報錯並退出
:return:返回範圍內的二維數組
‘’’
import openpyxl
from openpyxl.utils import cell as openpyxl_tools
from openpyxl.utils import get_column_letter, column_index_from_string
try:
book = openpyxl.load_workbook(save_path)
except Exception as e:
print “{0}:excel文件不存在”.format(save_path)
sys.exit(1)
if sheetName in book.sheetnames:
sheet = book[sheetName]
else:
print “sheet{0}不存在”.format(sheetName)
sys.exit(1)
location_range = data_range.split("😊
A_location = location_range[0]
B_location = location_range[1]
colnumber_alp1,row_number1 = openpyxl_tools.coordinate_from_string(A_location)
col_number1 = column_index_from_string(colnumber_alp1)
colnumber_alp2,row_number2 = openpyxl_tools.coordinate_from_string(B_location)
col_number2 = column_index_from_string(colnumber_alp2)
total_list=[]
for r in range(row_number1,row_number2+1):
row_list = []
for c in range(col_number1,col_number2+1):
v = sheet.cell(r,c).value
row_list.append(v)
total_list.append(row_list)
return total_list
if name == “main” :
data_list = get_range_data(“B11:H34”,‘test.xlsx’,‘sss’)
import json
with open(‘openxly_test.json’,‘w’) as f:
json.dump(data_list,f,indent=4,ensure_ascii=False)