python обрабатывает файл excel

Насколько легко работать с документами Excel? Посмотрите, как с этим справляется Python.

1. Введение

В последнее время мне нужно часто читать и записывать файлы Excel. Я хочу автоматизировать обработку файлов Excel с помощью программ. Я обнаружил, что использование библиотеки Python openpyxl для чтения и записи файлов Excel очень удобно, с четкой структурой и простым управлением. В этой статье обобщается использование openpyxl, в основном включая следующее:

• Введение в openpyxl и описание файловой структуры excel
• Чтение и запись обработки рабочих листов
• Чтение и запись обработки строк и столбцов
• Обработка чтения и записи ячеек

2 файловая структура openpyxl и excel

openpyxl — это параxlsx/xlsm/xltx/xltmБиблиотека Python для чтения и записи документов Excel 2010 в формате . ЭтоОфициальный сайтДоступна подробная документация. Перед использованием его необходимо установить и импортировать.

# 安装
pip install openpyxl
# 引入openpyxl 模块
import openpyxl

Перед выполнением операции Excel кратко ознакомьтесь со структурой файла Excel, чтобы ознакомиться с последующими операциями. Как показано ниже:

Файл Excel, содержимое которого иерархически разделено на工作簿(文件) -> 工作表(sheet) -> 行列 -> 单元格, соответствующий приведенному выше рисунку, весь файл Excel представляет собой рабочую книгу; в рабочей книге может быть несколько рабочих листов (Лист1/тест1 на рисунке и т. д.); рабочий лист представляет собой соответствующие данные таблицы, разделенные на строки и столбцы, строки представлены порядковыми номерами, а столбцы — прописными буквами (или порядковыми номерами); пересечение строки и столбца — это каждая ячейка, в которой хранятся данные. Таким образом, мы читаем и пишем таблицы Excel, в основном действуя в соответствии с этим уровнем мышления: читаем в файлах, находим рабочие листы, просматриваем строки и столбцы, находим ячейки, читаем и записываем ячейки. Следовательно, будут задействованы операции чтения и записи листов, строк и столбцов, а также ячеек. Перед этими операциями необходимо сначала загрузить файл.Excel-файл — это рабочая книга.Операция загрузки выглядит следующим образом (excel-файл в примере — text.xlsx):

# 加载excel文件
file_path = "E:/pythontest/test.xlsx"
workbook = openpyxl.load_workbook(file_path)

3 Обработка рабочего листа

3.1 Чтение рабочего листа

Будет несколько рабочих листов ( лист ), и все рабочие листы могут быть прочитаны При чтении одного листа его можно прочитать по имени листа или по индексу (индекс начинается с 0).

• Все объекты рабочего листа:workbook.worksheets
• Все имена рабочих листов:workbook.sheetnames
• Получить лист по имени (имя_листа):workbook[sheet_name]
• Получить рабочие листы по индексу (я начинается с 0):workbook.worksheets[i]
• Получить лист в использовании:workbook.active
• Получите свойства рабочего листа (например, имя рабочего листа, максимальное количество строк и столбцов и т. д.):sheet.title,sheet.max_row,sheet.max_column

следующее:

# 全部sheet对象
>>> workbook.worksheets
[<Worksheet "Sheet1">, <Worksheet "test1">, <Worksheet "test2">]
# 全部sheet名称
>>> workbook.sheetnames
['Sheet1', 'test1', 'test2']
# 按名称读取sheet
>>> workbook["Sheet1"]
<Worksheet "Sheet1">
# 按下标读取
>>> workbook.worksheets[0]
<Worksheet "Sheet1">
# 获取当前正在使用的sheet
>>> workbook.active
<Worksheet "Sheet1">
# 获取sheet的属性
>>> sheet_active.title
Sheet1
>>> sheet_active.max_row
6
>>> sheet_active.max_column
3

3.2 Добавить рабочий лист

Если вам нужно добавить новый рабочий лист, следуйте процессу операции, сначала добавьте рабочий лист, а затем сохраните файл. Созданcreate_sheetПосле завершения сохраните файл после создания, и дополнение вступит в силу.

• Создайте рабочие листы, переименуйте их автоматически, если они имеют одинаковое имя:workbook.create_sheet("test3")
• Создайте рабочий лист по указанному индексу:workbook.create_sheet("test4",1)
• Сохраните файл. Если путь к файлу совпадает с путем к открытому файлу, он будет перезаписан, если он отличается, исходный файл будет скопирован и сохранен (эквивалентно сохранению как):workbook.save(file_path)

3.3 Модификация рабочего листа

Чтобы изменить имя рабочего листа, вы можете напрямую установить заголовок рабочего листа.Вам также необходимо сохранить файл после изменения.

# 修改工作表名称
>>> sheet1 = workbook['test1']
>>> sheet1.title = 'test11'
# 保存文件
>>> workbook.save(file_path)

3.4 Удаление рабочего листа

Чтобы удалить рабочий лист, вам нужно сначала получить объект листа, а затем удалить его. Есть два способа удалить, один из них - использоватьworkbookкоторый предоставилremoveметод, вы также можете напрямую использовать pythondelудалить. После операции удаления также необходимо сохранить файл:

# remove删除工作表
sheet = workbook["test-1"]
workbook.remove(sheet)
# del操作删除
del workbook["test2"]
# 保存文件
workbook.save(file_path)

обработка 4 строк и столбцов

После получения объекта листа вы можете позже выполнять операции со строками и столбцами, включая чтение строк и столбцов, добавление, удаление и т. д.

4.1 Чтение рангов

• Получите все строки и столбцы, затем вы можете повторить:sheet.rows,sheet.columns
• Прочитать часть строк и столбцов: прочитать строкуsheet[1], прочитать несколько строкsheet[2:3], читать столбецsheet['A'], прочитать несколько столбцовsheet['B:C']

# 遍历全部行
>>> for row in sheet.rows:
...    print(row)
...
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>)
....
# 读取部分行列
>>> sheet[1]
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
>>> sheet["A:B"]
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>), (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>))

4.2 Добавление строк и столбцов

Добавьте строку и столбец, вы можете указать позицию для добавления одной строки и столбца или нескольких строк и столбцов.

• Добавьте данные строки непосредственно на лист:sheet.append(rowdata)
• Добавьте строку и столбец по указанному индексу (считая с 1):sheet.insert_rows,sheet.insert_cols

# 在第4行插入1行空行
>>> sheet.insert_rows(4)
# 在第2行插入2行空行
>>> sheet.insert_rows(idx=2,amount=2)
# 添加一行数据到表
>>> row_data = ["tom", 15, "tom@test.com"]
>>> sheet.append(row_data)
# 保存修改内容
>>> workbook.save(file_path)

4.3 Удалить строку и столбец

Операция удаления аналогична операции вставки строки и столбца, используйтеdelete_rowsиdelete_colsметод.

# 删除行
>>> sheet.delete_rows(2,2)
>>> workbook.save(file_path)

5 Обработка ячеек

Наши данные в конечном итоге хранятся в каждой ячейке (Cell). Поэтому, в конце концов, данные, которыми мы манипулируем, на самом деле являются данными в ячейке. В ячейке openpyxl использует объект Cell. При обходе данных строки и столбца ранее вы можете увидеть вывод<Cell 'Sheet1'.A1>Содержимое этого соответствующего объекта ячейки. Работа ячейки описана ниже.

5.1 Получить значение данных ячейки и значение атрибута

Есть два способа найти ячейку:

• Укажите имена строк и столбцов напрямую:sheet[A1]
• использоватьcellФункция (индексы строк и столбцов начинаются с 1):sheet.cell(row=2,column=1)

# 指定行列坐标获取单元格
>>> sheet["A1"]
<Cell 'Sheet1'.A1>
# cell函数获取单元格
>>> sheet.cell(row=1, column=1)
<Cell 'Sheet1'.A1>

После получения объекта ячейки вы можете получить значение данных и его свойства, включая количество строк и столбцов, в которых оно находится, координаты, значения и т. д.

>>> cell = sheet["A2"]
>>> cell.value
'张三'
>>> cell.coordinate
'A2'
>>> cell.column
1
>>> cell.row
2

5.2 Перемещение ячеек

Перемещая диапазон ячеек вверх, вниз, влево и вправо, используяmove_range(range,rows,cols), где строки и столбцы — это целые числа, положительные целые числа означают вниз или вправо, а отрицательные целые числа — вверх или влево.

# 移动数据区域(向上移动2行，向右移动3列)，正整数为向下或向右，负整数为向上或向左
sheet.move_range("A3:C3", rows=-2, cols=3)
wb.save(file_path)

5.3 Объединение разделенных ячеек

Для охвата строк и столбцов ячейки необходимо объединить, используяmerge_cells(range_string, start_row, start_column, end_row, end_column). Если во всех объединяемых ячейках есть данные, будут сохранены только данные в верхнем левом углу, а остальные будут удалены. Слияние и разделение можно выполнять по координатам строк и столбцов (например, A1) или индексам строк и столбцов (например, 1,2).

# 单元格合并，使用范围坐标
sheet.merge_cells("A2:B3")
# 单元格合并，指定行列下标（下标从1开始）
sheet.merge_cells(start_row=5, start_column=3, end_row=7, end_column=4)
wb.save(file_path)
# 拆分单元格
sheet.unmerge_cells("A2:B3")
sheet.unmerge_cells(start_row=5, start_column=3, end_row=7, end_column=4)
# 保存文件
wb.save(file_path)

5.4 Запись в ячейки

Измените и запишите значения ячеек непосредственно вcell.valueПросто назначьте его. Здесь следует отметить, что вы можете написать формулу excel, и конкретная формула такая же, как и формула, используемая в excel.Кроме того, если формула написана, значение, полученное при чтении, также является формулой, а не значением формулы .

# 写入值
cell.value = "张三"
# 写入公式(求平均值)
cell.value = "=AVERAGE(B2:B6)"

5.5 Форматирование ячеек

Формат ячейки включает высоту строки, ширину столбца, шрифт, границу, выравнивание, цвет заливки и т. д. Все это находится в модуле стилей openpyxl.

• Высота строки/ширина столбца:row_dimensions[row_num].height = xx,sheet.column_dimensions[col_name].width = xx
• Шрифт (объект шрифта): включая имя поля, размер, полужирный, курсив, цвет и т. д.,Font(name="微软雅黑", size=20, bold=True, italic=True, color="000000")
• Граница (объект границы и объект стороны): размер/цвет формата каждой стороны границыSide(style="thin", color="000000"), создайте граничный объект из ребер:Border(left=side, right=side, top=side, bottom=side)
• Выравнивание (объект выравнивания): направление выравнивания по вертикали и горизонтали, автоматический перенос.Alignment(horizontal="center", vertical="center", wrap_text=True)
• Цвет заливки, разделенный на заливку обычным цветом и заливку градиентным цветом:PatternFill(fill_type="solid", fgColor="FF0000")иGradientFill(stop=("FF0000", "FD1111", "000000"))

# 设置行高和列宽
sheet.row_dimensions[1].height = 50
sheet.column_dimensions["A"].width = 20
# 设置单元格字体
cell = sheet["A1"]
current_font = cell.font
font = Font(name="微软雅黑", size=20, bold=True, italic=True, color="000000")
cell.font = font
# 设置边框(细边，黑色)
side_style = Side(style="thin", color="000000")
border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)
cell.border = border
# 居中对齐，自动换行
cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
cell.alignment = cell_alignment
# 填充颜色（红色填充，和红色到黑色渐变填充）
p_fill = PatternFill(fill_type="solid", fgColor="FF0000")
g_fill = GradientFill(stop=("FF0000", "FD1111", "000000"))
cell.fill = p_fill
sheet["B1"].fill = g_fill

Последнее, что следует отметить, это то, что эти операции модификации, наконец, должны быть сохранены с помощью операции сохранения (wb.save(file_path)) вступить в силу.

6 Резюме

Благодаря приведенному выше объяснению, чтобы понять, как использовать библиотеку python openpyxl для обработки документов Excel, вы можете обнаружить, что логика ее работы довольно ясна и проста, что соответствует нашей привычке использовать Excel. Поток обработки в основном заключается в загрузке файла и поиске рабочего листа, строки, столбца и ячейки для обработки. Читать, писать, изменять формат и т.д. на них. Поэтому, если есть необходимость автоматизировать обработку файлов excel, используйте openpyxl, но он ограничен обработкой документов excel в формате 2010. Для старого формата ( xls ) рекомендуется перейти на новый формат и далее работать , или вы также можете использовать операции модуля xlrd и xlwt.

использованная литература

• официальная документация openpyxl: https://openpyxl.readthedocs.io/
• Серия Python Automation Office Python Operation Excel

Прошлые статьи

• Основные операции python — файлы, каталоги и пути
• Распределенное развертывание MinIO
• Используйте MinIO для простого создания сервисов статических ресурсов
• Получить несколько источников данных SpringBoot (3): параметризованный источник изменений
• Получить несколько источников данных SpringBoot (2): источники динамических данных
• Получить несколько источников данных SpringBoot (1): стратегии с несколькими источниками
• Знания, необходимые для разработки Java: динамический прокси
• Лучшие книги для чтения в 2019 году

Мой официальный аккаунт (поискMason技术记录) для более технических записей: