current position:Home>Methods of reading and writing excel (Python)

Methods of reading and writing excel (Python)

2022-06-24 08:23:16go_ flush

  • Reading and writing excel There are many bags , When I use it here xlrd3 and xlsxwriter These two bags
  • The code is encapsulated as follows
    read excel
import xlrd3
import xlsxwriter
def read_excel_data(filename, data_type=None):
    """ :param filename:  file name  :param data_type:  Return data type , The default is list, If data_type yes True The return is dict The type of data  :return: """
    wb = xlrd3.open_workbook(filename)
    sheet_01 = wb.sheet_by_index(0)  #  Choose the first one sheet
    res_list = []
    if data_type:
        start_index = 0
        title = [ele.value for ele in sheet_01.row(start_index)]
    for index in range(1, sheet_01.nrows):  #  Read from the first line , Title unread 
        row_list = sheet_01.row(index)
        if data_type:
            row_data = {
    title[k_index]: ele.value for k_index, ele in enumerate(row_list)}
        else:
            row_data = [ele.value for ele in row_list]
        res_list.append(row_data)
    return res_list

Write excle_
The code is as follows

def write_to_excel_data(data_list, file_name, title=None, sheet_name="sheet1"):
    """ :param data_list:  Write data [{}]  perhaps  [[]] :param title:  title  :param file_name:  file name  :param sheet_name:  Workbooks  :return: """
    workfile = xlsxwriter.Workbook(file_name)
    worksheet = workfile.add_worksheet(sheet_name)
    if not isinstance(title, (tuple, list)):
        return " Incorrect header data format "
    row_index, col_index = 0, 0
    start_index = 0
    #  Set background color 
    if not sheet_name:
        format = workfile.add_format()
        format.set_bg_color("green")

    if title:
        worksheet.write_row(row_index, col_index, title)
        start_index = 1
    # dict  Data writing 
    if isinstance(data_list[0], dict):
        for index, item in enumerate(data_list, start_index):
            ele = [item.get(key) for key in title]
            worksheet.write_row(index, col_index, ele)
        pass
    # list  Data writing 
    else:
        for index, item in enumerate(data_list, start_index):
            worksheet.write_row(index, col_index, item)
    workfile.close()

copyright notice
author[go_ flush],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/175/202206240437270611.html

Random recommended