openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files

文檔

• https://openpyxl.readthedocs.io/en/stable/
• https://pypi.org/project/openpyxl/

安裝

pip install openpyxl

環(huán)境

$ python --version
Python 3.7.0

讀取文件示例：將Excel文件讀取為json數(shù)據(jù)

有如下一個(gè)文件 data.xlsx

實(shí)現(xiàn)代碼

# -*- coding: utf-8 -*-

import json

from openpyxl.reader.excel import load_workbook


def read_excel(filename):
    book = load_workbook(filename)
    worksheet = book.worksheets[0]

    row_num = 0
    titles = []
    lst = []

    for row in worksheet.rows:
        row_num += 1

        if row_num == 1:
            # 表頭
            for cell in row:
                # 移除空格
                value = cell.value.replace(' ', '')
                titles.append(value)
        else:
            # 內(nèi)容
            item = {}
            for key, cell in zip(titles, row):
                item[key] = cell.value

            lst.append(item)

    return lst


if __name__ == '__main__':
    data = read_excel('./data.xlsx')
    print(json.dumps(data, ensure_ascii=False, indent=2))

輸出讀取的json數(shù)據(jù)

[
  {
    "姓名": "曹操",
    "國(guó)家": "魏國(guó)"
  },
  {
    "姓名": "劉備",
    "國(guó)家": "蜀國(guó)"
  },
  {
    "姓名": "孫權(quán)",
    "國(guó)家": "吳國(guó)"
  }
]

讀寫(xiě)示例文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-607092.html

# -*- coding: utf-8 -*-
"""
@File    : excel_util.py
@Date    : 2023-11-16
"""
import re

from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
from openpyxl.utils import get_column_letter


def read_excel(filename):
    """
    讀取excel文件為python對(duì)象
    :param filename:
    :return: iterator
    """
    book = load_workbook(filename)
    worksheet = book.worksheets[0]

    titles = []
    row_num = 0

    for row in worksheet.rows:
        row_num += 1

        if row_num == 1:
            # 表頭
            titles = [cell.value.strip() for cell in row]
        else:
            # 內(nèi)容
            yield dict(zip(titles, [cell.value for cell in row]))

    book.close()


def write_excel(filename, rows):
    """
    將列表寫(xiě)入到文件
    :param filename:
    :param rows: list
    :return:
    """
    workbook = Workbook()
    worksheet = workbook.active


    # 表頭
    if len(rows) > 0:
        for i, key in enumerate(rows[0].keys()):
            worksheet.cell(row=1, column=i + 1, value=key)

    # 內(nèi)容
    for x, row in enumerate(rows):
        for y, value in enumerate(row.values()):
            worksheet.cell(row=x + 2, column=y + 1, value=value)

    # 調(diào)整列寬
    # 參考：https://blog.csdn.net/gongzairen/article/details/130819231
    width = 3  # 手動(dòng)加寬的數(shù)值
    # 單元格列寬處理
    dims = {}
    for row in worksheet.rows:
        for cell in row:
            if cell.value:
                cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
                dims[cell.column] = max((dims.get(cell.column, 0), cell_len))

        for col, value in dims.items():
            worksheet.column_dimensions[get_column_letter(col)].width = value + width

    workbook.save(filename)
    workbook.close()

到了這里，關(guān)于Python：使用openpyxl讀取Excel文件轉(zhuǎn)為json數(shù)據(jù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！