Master Python Excel I/O with Six Handy Functions for XLS/XLSX

Compared with other programming languages, Python’s simplicity and rich third‑party libraries make it a popular choice for data analysis, and Microsoft Excel is a common format for data storage.

2. Preparation

2.1. Introduction to Excel

Microsoft Excel is a spreadsheet application for Windows and macOS.

2.2. Format differences

xls is a binary format; xlsx is XML‑based and compressed, resulting in smaller files.

xls is used by Excel 2003 and earlier; xlsx is used by Excel 2007 and later.

xlsx is backward compatible with xls.

2.3. Libraries

Python’s standard library includes xlrd and xlwt for handling xls files, while the third‑party openpyxl library is required for xlsx files.

2.4. Overall idea

Although the libraries expose Excel concepts as Python objects, each cell operation typically requires nested loops, which can be repetitive; therefore six utility functions are provided to simplify common read/write/append tasks.

3. Code demonstration

3.1. xls format

3.1.1. Read xls

def read_xls_excel(url,index):
    '''
    Read xls file
    Parameters:
        url: file path
        index: sheet number (starting from 1)
    Returns:
        data: sheet data
    '''
    workbook = xlrd.open_workbook(url)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[index-1])
    data = []
    for i in range(0, worksheet.nrows):
        da = []
        for j in range(0, worksheet.ncols):
            da.append(worksheet.cell_value(i, j))
        data.append(da)
    return data

3.1.2. Write xls

def write_xls_excel(url,sheet_name,two_dimensional_data):
    '''
    Write xls file
    Parameters:
        url: file path
        sheet_name: sheet name
        two_dimensional_data: data to write (list of lists)
    '''
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(sheet_name)
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            sheet.write(i, j, two_dimensional_data[i][j])
    workbook.save(url)
    print('写入成功')

3.1.3. Append xls

def write_xls_excel_add(url, two_dimensional_data, index):
    '''
    Append to xls file
    Parameters:
        url: file path
        two_dimensional_data: data to append (list of lists)
        index: sheet number (starting from 1)
    '''
    workbook = xlrd.open_workbook(url)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[index-1])
    rows_old = worksheet.nrows
    new_workbook = copy(workbook)
    new_worksheet = new_workbook.get_sheet(index-1)
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            new_worksheet.write(i + rows_old, j, two_dimensional_data[i][j])
    new_workbook.save(url)
    print('追加写入成功')

3.2. xlsx format

3.2.1. Read xlsx

def read_xlsx_excel(url, sheet_name):
    '''
    Read xlsx file
    Parameters:
        url: file path
        sheet_name: sheet name
    Returns:
        data: sheet data
    '''
    workbook = openpyxl.load_workbook(url)
    sheet = workbook[sheet_name]
    data = []
    for row in sheet.rows:
        da = []
        for cell in row:
            da.append(cell.value)
        data.append(da)
    return data

3.2.2. Write xlsx

def write_xlsx_excel(url, sheet_name, two_dimensional_data):
    '''
    Write xlsx file
    Parameters:
        url: file path
        sheet_name: sheet name
        two_dimensional_data: data to write
    '''
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            sheet.cell(row=i+1, column=j+1, value=str(two_dimensional_data[i][j]))
    workbook.save(url)
    print('写入成功')

3.2.3. Append xlsx

def write_xlsx_excel_add(url, sheet_name, two_dimensional_data):
    '''
    Append to xlsx file
    Parameters:
        url: file path
        sheet_name: sheet name
        two_dimensional_data: data to append (list of lists)
    '''
    workbook = openpyxl.load_workbook(url)
    sheet = workbook[sheet_name]
    for tdd in two_dimensional_data:
        sheet.append(tdd)
    workbook.save(url)
    print('追加写入成功')

4. Result testing

4.1. Read test

Two Excel files are prepared (as shown), then the read functions are executed; the printed output confirms that data is read correctly.

4.2. Write test

Running the write functions overwrites the original content, as demonstrated by the resulting screenshots.

4.3. Append test

Appending data after a write operation succeeds without data loss, confirming the utility of the append functions.