python操作xlsx并生成json文件

from openpyxl import load_workbook
import os
#创建文件
os.system("touch Calibration.json")
#以写入格式打开所创建的文件
c=open("./Calibration.json",mode="w")

workbook = load_workbook(u'/root/8025.xlsx')    #找到需要xlsx文件的位置
booksheet = workbook.active                 #获取当前活跃的sheet,默认是第一个sheet

#如果想获取别的sheet页采取下面这种方式，先获取所有sheet页名，在通过指定那一页。
# sheets = workbook.get_sheet_names()  # 从名称获取sheet
# booksheet = workbook.get_sheet_by_name(sheets[0])

#获取sheet页的行数据
rows = booksheet.rows
#获取sheet页的列数据
columns = booksheet.columns
i = 23
a=0
js= []
#获取表格内的数据
for row in rows:
    if i<31:
        i=i+1
        a=a+1
        line = [col.value for col in row]
        x = booksheet.cell(row=i, column=5).value #获取第五列中第i行的数据
        y = booksheet.cell(row=i+1,column=5).value
        if i%2==0:
            m=14/(y-x)　　#将两行的数据根据公式进行计算得出所需要的数据
        else:
            y1= booksheet.cell(row=i-1,column=5).value
            m=1-y1*(14/(x-y1))
        js.append(m)
        # print("i:",i,"x：",x,"y:",y,"m:",m)

    elif i<39:　　　　#根据所在行的不同调整公式
        i = i + 1
        a = a + 1
        line = [col.value for col in row]
        x = booksheet.cell(row=i, column=5).value
        y = booksheet.cell(row=i + 1, column=5).value
        if i % 2 == 0:
            m = 16 / (y - x)
        else:
            y1 = booksheet.cell(row=i - 1, column=5).value
            m = 4 - (y1 * (16 / (x - y1)))
        js.append(m)
        # print("i:", i, "x：", x, "y:", y, "m:", m)

    elif i<47:
        i = i + 1
        a = a + 1
        line = [col.value for col in row]
        x = booksheet.cell(row=i, column=5).value
        y = booksheet.cell(row=i + 1, column=5).value
        if i % 2 == 0:
            m = 50 / (y - x)
        else:
            y1 = booksheet.cell(row=i - 1, column=5).value
            m = 100 - y1 * (50 / (x - y1))
        js.append(m)
        # print("i:", i, "x：", x, "y:", y, "m:", m)

    elif i==47:　　#截至在所需要的行数中断循环，可以考虑根据数值为Null来中断循环
        break
#将所得出的数据记录在相应json文本中
b=[
   {
      "ChannelNo" : 1,
      "Cur_Calibration" : 1.0,
      "Cur_Offset" : 0.0,
      "Tem_Calibration" : 1.0,
      "Tem_Offset" : 0.0,
      "Vol_Calibration" : js[0],
      "Vol_Offset" : js[1]
   },
   {
      "ChannelNo" : 2,
      "Cur_Calibration" : 1.0,
      "Cur_Offset" : 0.0,
      "Tem_Calibration" : 1.0,
      "Tem_Offset" : 0.0,
      "Vol_Calibration" : js[2],
      "Vol_Offset" : js[3]
   },
   {
      "ChannelNo" : 3,
      "Cur_Calibration" : 1.0,
      "Cur_Offset" : 0.0,
      "Tem_Calibration" : 1.0,
      "Tem_Offset" : 0.0,
      "Vol_Calibration" : js[4],
      "Vol_Offset" : js[5]
   },
   {
      "ChannelNo" : 4,
      "Cur_Calibration" : 1.0,
      "Cur_Offset" : 0.0,
      "Tem_Calibration" : 1.0,
      "Tem_Offset" : 0.0,
      "Vol_Calibration" : js[6],
      "Vol_Offset" : js[7]
   },
   {
      "ChannelNo" : 5,
      "Cur_Calibration" : js[8],
      "Cur_Offset" : js[9],
      "Tem_Calibration" : js[16],
      "Tem_Offset" : js[17],
      "Vol_Calibration" : 1.0,
      "Vol_Offset" : 1.0
   },
   {
      "ChannelNo" : 6,
      "Cur_Calibration" : js[10],
      "Cur_Offset" : js[11],
      "Tem_Calibration" : js[18],
      "Tem_Offset" : js[19],
      "Vol_Calibration" : 1.0,
      "Vol_Offset" : 0.0
   },
   {
      "ChannelNo" : 7,
      "Cur_Calibration" : js[12],
      "Cur_Offset" : js[13],
      "Tem_Calibration" : js[20],
      "Tem_Offset" : js[21],
      "Vol_Calibration" : 1.0,
      "Vol_Offset" : 0.0
   },
   {
      "ChannelNo" : 8,
      "Cur_Calibration" : js[14],
      "Cur_Offset" : js[15],
      "Tem_Calibration" : js[22],
      "Tem_Offset" : js[23],
      "Vol_Calibration" : 1.0,
      "Vol_Offset" : 0.0
   }
]
#将得出的文本写入文件中
c.write(b)
c.close()#关闭文件