[Python] 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

後記 :
format1 = work.add_format() #新增一個字形格式
format1.set_font_size(SIZE) #設定字形格式的大小為SIZE
format1.set_font_color("red") #設定字形格式的顏色
format1.set_bold() #設定字形格式為粗體
format1.set_italic() #設定字形格式為斜體
format1.set_font_strikeout() #設定字形格式為刪節號
format1.set_font_underline() #設定字形格式為底線
format1.set_align(LOCATE) #設定字形格式的對齊 ..
                           # center, right, fill, justify, center_across
format1.set_text_wrap()  #設定字形格式為符合寬度換行
format1.set_bg_color()  #設定字形格式的背景顏色

worksheet.write()