Предисловие

Сегодня мы представляем Python для работы с Excel для создания отчетов. Нам понадобится библиотека openpyxl. Основные функции, которые мы используем, следующие:

• Вставка и запрос данных
• Статистика секретных данных
• визуализация данных
• Изменение цвета шрифта

Основные операции

Исходные данные таблицы такие, как показано ниже:

Вставьте сюда описание изображения

Давайте сначала познакомимся с библиотекой openpyxl, чтобы облегчить ее дальнейшее более расширенное использование.

1. Среда установки Нам нужно установить библиотеку openpyxl. Установка очень проста. Вы также можете установить ее в pycharm. Я не буду здесь вдаваться в подробности. openpyxl
2. Импортировать набор данных Excel, код следующий# Модуль импорта from openpyxl import Workbook, load_workbook # Импортировать набор данных Excel wb = load_workbook(r"sales.xlsx") # Получить рабочий лист sheet = wb.active # Имя рабочего листа называется print(sheet) ----------- # выход <Worksheet "auto_sales">
3. Распечатать значения с рабочего листа Мы печатаем значение, передавая позицию ячейки следующим образом: print(sheet["A1"].value) print(sheet["A2"].value) print(sheet["B3"].value) ---------------- # выход Brand Vw Japansheet["C10"] =75 # держать wb.save(r"sales.xlsx") после держать,Давайте посмотрим, было ли успешно изменено значение ячейки.,Как показано ниже:
4. Изменить значение ячейки

Вставьте сюда описание изображения

Мы видим, что значение ячейки успешно изменено.

1. Добавить рабочий лист Мы можем добавить лист в существующий файл Excel следующим образом: # Добавить новый лист wb.create_sheet("new_energy_cars_sales") # Возвращает список с именами листов print(wb.sheetnames) # выход ['auto_sales', 'new_energy_cars_sales']Примечание. Мы не сохраняли его здесь, поэтому новый лист в файле не появится.
2. Вставить данные Мы создаем новый файл Excel с помощью кода и записываем в него некоторые данные.

from openpyxl import Workbook, load_workbook

new_wb = Workbook()
ws = new_wb.active
# Переименуйте имя листа
ws.title = "contry"
# Вставить данные на новый лист
ws.append(["PRC","is","a","great","contry"])
new_wb.save("contry.xlsx")

Давайте посмотрим на конечный результат, как показано на рисунке ниже:

Вставьте сюда описание изображения

Давайте попробуем вставить еще несколько фрагментов данных. Код выглядит следующим образом:

# Вставьте больше данных
ws.append(["USA","Is","a","dirty", "contry"])
ws.append(["UK","is","a","deep", "contry"])
ws.append(["End"])
# держать
new_wb.save("contry.xlsx")

Результат следующий:

Вставьте сюда описание изображения

1. Вставка и удаление строк Чтобы вставить определенную строку, вызывается метод Insert_rows(). Подробности следующие: #. Как и в предыдущих шагах, импортируйте книгу и данные. ws.insert_rows(1) Результат new_wb.save("contry.xlsx") показан ниже.

Вставьте сюда описание изображения

Точно так же, если вы хотите удалить определенную строку данных, вызывается метод delete_rows(). Конкретный код выглядит следующим образом:

ws.delete_rows(1)
# держать
new_wb.save("contry.xlsx")

Посмотрев на результаты, вы увидите, что вставленная строка удалена.

Вставьте сюда описание изображения

1. Вставка и удаление столбцов Операция вставки и удаления столбцов аналогична операции вставки и удаления строк. Для вставки столбцов используется метод Insert_cols(), как показано ниже#. Вставить новый столбец ws.insert_cols(6) Метод удаления столбцов — delete_cols().

ws.delete_cols(6)

Анализ и визуализация данных

openpyxl — очень мощная сторонняя библиотека. Сочетая Python и Excel, python+openpyxl может выполнять те же операции. Теперь мы будем использовать немного более сложные функции для анализа и визуализации данных.

1. Подготовка данных

Поскольку у меня нет под рукой реальных данных, я могу сейчас сгенерировать только пакет данных. Это не строго, поэтому, пожалуйста, простите меня.

Данные следующие:

brand_data = {
    "Фольксваген":{"Класс А":1200000, «Класс Б»:500000, «С-уровень»: 400000, «Уровень D»: 30000, "SUV":480000},
    "Тойота":{"А-Класс":900000, «Класс Б»: 450000, «С-уровень»: 240000, «Д-Класс»: 2160, "SUV":300000},
    "Хонда": {"Класс А": 600000, «Класс Б»:240000, «С-уровень»: 160, «Уровень D»: 0, "SUV":240000},
    "BYD":{"Класс А":1080000, «Класс Б»:280000, «С-уровень»: 0, «Уровень D»: 0, "SUV":300000},
    "Бенц":{"А-Класс":0, «Класс Б»:180000, «С-уровень»: 216000, «Уровень D»:60000, "SUV": 120000 }
}

Для записи данных в Excel код выглядит следующим образом:

# Создать новую книгу
sales_wb = Workbook()
ws = sales_wb.active
# Переименуйте имя листа
ws.title = "Sales"
# Создайте имена столбцов
column_names = ["Brand Name"] + список(brand_data["Volkswagen"].keys())
ws.append(column_names)
# Поместите ряд значений на рабочий лист
for product in brand_data:
    sales = list(brand_data[product].values())
    ws.append([product] + sales)
sales_wb.save("brand_data.xlsx")

Таблица выглядит так, как показано на рисунке:

Вставьте сюда описание изображения

1. Реализация расчета среднего значения Укажем определенный столбец. Например, мы хотим посчитать средние продажи автомобилей B-класса каждой марки следующим образом: ws['C7'] = '=AVERAGE(C2:C6)' sales_wb.save("brand_data.xlsx")Результат такой, как показано ниже:
   

   Вставьте сюда описание изображения
   实现 我们现существовать Подсчитайте общий объем продаж каждого бренда,Как достичь,код следующий

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column

for row in range(min_row+1,max_row+1):
    key=ws.cell(row=row,column=max_col+1).coordinate
    #Начальный адрес ячейки суммирования
    start = ws.cell(row=row,column=min_col+1).coordinate
    #Адрес конечной ячейки суммирования
    end = ws.cell(row=row,column=max_col-1).coordinate
    ws[key]=f'=SUM({start}:{end})'

sales_wb.save('brand_data.xlsx')

Результат такой, как показано ниже:

Вставьте сюда описание изображения

Нарисуйте гистограмму

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

from openpyxl.chart import BarChart, Reference
# Создайте новый экземпляр гистограммы
barchart = BarChart()
# Определите объем данных
data = Reference(ws, min_col=ws.min_column+1, max_col=ws.max_column, min_row=ws.min_row, max_row=ws.max_row)
categories = Reference(ws, min_col=ws.min_column, max_col=ws.min_column, min_row=ws.min_row+1, max_row=ws.max_row)
# Добавьте данные и категории
barchart.add_data(data, titles_from_data=True)
barchart.set_categories(categories)
# Где разместить построенные данные
ws.add_chart(barchart, "A10")
# Добавить заголовок
barchart.title = «Данные о продажах продукции по уровням для каждого бренда»
# Тип диаграммы
barchart.style = sales_wb.save("brand_data.xlsx")

Результат показан на рисунке:

Вставьте сюда описание изображения

Подвести итог

Эта статья представляет собой лишь частичную демонстрацию мощных функций openpyxl. Если вы хотите изучить другие методы, оставьте сообщение в области комментариев!