[IoT上層應用] Python資料分析與儲存 - 使用xlsxwriter模組

程式碼參考 :
https://github.com/kunhsien/PyExample/tree/master/PyXlsxWriter


範例的程式進入點 main 位於Battery_analysis.py


def main():
    sendThr = threading.Thread(target=parse_value)
    sendThr.start()


if __name__ == '__main__':
    main()


我們用了threading的模組 去重複執行parse_value


def parse_value():
 index_Volt = 6
 index_Cap = 2
 indwx_Temp = 10
    for i in range(1000):
        message = "[battery_check]BATT cap 99 cap_count 0 vol 3981000 vol_count 0 temp 229 temp_count 0 batt_no_count 0 \r\n'"
        s_toke = message
        s_token = re.split(' ', s_toke)
        print(str(s_token))
        Volt_value = s_token[index_Volt]
        Cap_value = s_token[index_Cap]
        Temp_value = s_token[indwx_Temp]
        print("[Battery_analysis] - Volt_value: " + str(Volt_value) + ", Cap_value: " + str(Cap_value) + ", Temp_value: " + str(Temp_value) +"\n")
        for key in range(0,15):
            xls_report.VOLTAGE_QUEUE[key].append(Volt_value)
            xls_report.CAPACITY_QUEUE[key].append(Cap_value)
            xls_report.TEMPERTURE_QUEUE[key].append(Temp_value)
    for key_print in range(0, 15):
        print("KEY" + str(key_print) +" temperture : " + str(xls_report.TEMPERTURE_QUEUE[key_print]) + ".\n")
        print("KEY" + str(key_print) + " capacity : " + str(xls_report.CAPACITY_QUEUE[key_print]) + ".\n")
        print("KEY" + str(key_print) + " voltage : " + str(xls_report.VOLTAGE_QUEUE[key_print]) + ".\n")
    xls_report.battery_queue_finish = True
    xls_report.write_to_excel()


在parse_value中我們使用for迴圈對15個key component塞了三個資料 電壓 溫度 容量
塞進去queue之後 我們塞了一個flag 告訴系統 我們完成塞queue的動作了
最後執行write_to_excel函式

def write_to_excel():
    #if (battery_queue_finish == True):
        WORKBOOK = xlsxwriter.Workbook("Battery_values.xlsx") # 新增excel檔案
        sheet_Capacity = WORKBOOK.add_worksheet("Capacity") #新增表單 命名Capacity
        sheet_Voltage = WORKBOOK.add_worksheet("Voltage") #新增表單 命名Voltage
        sheet_Temperture = WORKBOOK.add_worksheet("Temperture") #新增表單 命名Temperture
        for index_num in range(len(Title_ADDR)):
            sheet_Capacity.write(Title_ADDR[index_num], "Key" + str(index_num))
            sheet_Voltage.write(Title_ADDR[index_num], "Key" + str(index_num))
            sheet_Temperture.write(Title_ADDR[index_num], "Key" + str(index_num))
        print("[Xls_report] - Write_to_excel: Sheet -  \n")
        for key_index in range(0,15):
            print("Saving Battery information for Key(" +str(key_index)+")...\n")
            for TempQ_index in range(len(TEMPERTURE_QUEUE[key_index])):
                TempAddr = GetAndIncrease_Addr(key_index, 'Temperture')
                print(str(TempAddr))
                sheet_Temperture.write(TempAddr, TEMPERTURE_QUEUE[key_index][TempQ_index])
            for CapaQ_index in range(len(CAPACITY_QUEUE[key_index])):
                CapaAddr = GetAndIncrease_Addr(key_index, 'Capacity')
                sheet_Capacity.write(CapaAddr, CAPACITY_QUEUE[key_index][CapaQ_index])
            for VoltQ_index in range(len(VOLTAGE_QUEUE[key_index])):
                VoltAddr = GetAndIncrease_Addr(key_index, 'Voltage') #傳入key值回傳該key於該sheet的現在寫入位址並將位址地增加1
                sheet_Voltage.write(VoltAddr, VOLTAGE_QUEUE[key_index][VoltQ_index])
#寫入該值 到該key的該位址
        print("Excel file write end.\n")
    #else:
        #pass


write_to_excel()函式就是xlsxwriter的使用技巧，其中會呼叫自創函式GetAndIncrease_Addr 定義如下:




#這個函式就不多解釋了，純粹字串跟list的轉換跟應用
def GetAndIncrease_Addr(key, sheet_name):
    str_ADDR = ''
    NOW_ADDR = ''
    NEW_ADDR = ''
    if sheet_name == "Capacity":
        NOW_ADDR = CAPACITY_SHEET_ADDR[key]
    elif sheet_name == "Temperture":
        NOW_ADDR = TEMPERTURE_SHEET_ADDR[key]
    elif sheet_name == "Voltage":
        NOW_ADDR = VOLTAGE_SHEET_ADDR[key]
    ADDR_header_temp = NOW_ADDR[0]
    ADDR_header = ADDR_header_temp
    sNOW_ADDR = NOW_ADDR.strip(ADDR_header_temp)
    iNEW_ADDR = int(sNOW_ADDR) + 1
    if sheet_name == "Capacity":
        CAPACITY_SHEET_ADDR[key] = ADDR_header + str(iNEW_ADDR)
    elif sheet_name == "Temperture":
        TEMPERTURE_SHEET_ADDR[key] = ADDR_header + str(iNEW_ADDR)
    elif sheet_name == "Voltage":
        VOLTAGE_SHEET_ADDR[key] = ADDR_header + str(iNEW_ADDR)
    else:
        print("Can't mapping any sheet name.\n")
    print("NOW_ADDR: " + str(NOW_ADDR))
    return NOW_ADDR