Как обвести ячейки в Openpyxl

Продолжаю бороться с построением отчетности ну Python при помощи Openpyxl. С ходу уперся в вопрос как нарисовать таблицу, а конкретнее как обвести ячейки в Openpyxl. Стандартных функций обвода диапазона ячеек фреймворк Openpyxl не содержит. Те, которые нашлись поиском, очевидно были прописаны под более ранние версии Openpyxl . В итоге продолжаю наполнять свой модуль excelz.py новыми функциями. Встречайте: set_border и set_border_range.

from openpyxl.styles import Side

def set_border(ws, c1, r1, c2, r2, _border_style='thin', _color='FF000000'):
side = Side(border_style=_border_style, color=_color)
for y in range(r1, r2+1):
for x in range(c1, c2+1):
cell = ws.cell(row = y, column=x)
border = cell.border.copy()
if x == c1:
border.left = side
if x == c2:
border.right = side
if y == r1:
border.top = side
if y == r2:
border.bottom = side
cell.border = border

def set_border_range(ws, cells, _border_style='thin', _color='FF000000'):
arr = cells.split(':')
r1 = ws[arr[0]].row
c1 = ws[arr[0]].col_idx
r2 = ws[arr[1]].row
c2 = ws[arr[1]].col_idx
set_border(ws, c1, r1, c2, r2, _border_style, _color)

Вставка строк в Openpyxl

Продолжаю цикл статей по теме Формирование документов Excel из Python. С чем влет столкнулся - как вставить строки в Openpyxl. То есть нет базовой функции для вставки строк документ. И это проблема для работы с шаблонами отчетов. Например накладную уже не сформируешь.

Поиск в интернете выдал 2 ссылки на примеры кода, как это сделать. По сути это перенос данных на определенное количество строк вниз. Но эти примеры переносили только содержание ячеек, не затрагивая стили и не переносили объединенные диапазоны. То есть оказались не пригодны для работы с шаблонами документа.

Порывшись в кишках Openpyxl я обнаружил модуль copier.py, который копирует страницы документа Excel, с переносом всех форматирований. Скрестив данный модуль с кодом найденных примеров я получил первую версию своего модуля excelz.py с функцией insert_rows для вставки строк в документ Excel. Сразу скажу, что данная реализация вставляет абсолютно не отформатированные строки и в ближайшем будущем придется модуль дорабатывать для вставки строк по шаблону из предыдущей строки документа.

excelz.py

import openpyxl

from copy import copy

import re

def insert_rows(sheet, startRow, nRows):

maxCol = sheet.max_column

maxRow = sheet.max_row

lastRow = maxRow + nRows

merged_cells = sheet._merged_cells

mcs = []

for m in merged_cells:

pair = m.split(':')

a = re.findall('(\d+)', pair[0])

if int(a[0]) >= startRow:

sheet.unmerge_cells(m)

mcs.append(m)

for i_row in range(maxRow, startRow - 1, -1):

sheet.row_dimensions[i_row + nRows].height = sheet.row_dimensions[i_row].height

for i_col in range(1, maxCol + 1, 1):

source_cell = sheet.cell(row = i_row, column=i_col)

target_cell = sheet.cell(row=i_row + nRows, column=i_col)

target_cell.value = None

target_cell._style = None

target_cell._hyperlink = None

target_cell.comment = None

target_cell._value = source_cell._value

target_cell.data_type = source_cell.data_type

if source_cell.has_style:

target_cell._style = copy(source_cell._style)

if source_cell.hyperlink:

target_cell._hyperlink = copy(source_cell.hyperlink)

if source_cell.comment:

target_cell.comment = Comment(source_cell.comment.text, source_cell.comment.author)

for i_row in range(startRow, startRow +nRows):

sheet.row_dimensions[i_row].height = None

for i_col in range(1, maxCol + 1, 1):

source_cell = sheet.cell(row = i_row, column=i_col)

source_cell.value = None

source_cell._style = None

source_cell._hyperlink = None

source_cell.comment = None

for m in mcs:

pair = m.split(':')

a = re.findall('(\d+)', pair[0])

if int(a[0]) >= startRow:

r1 = int(a[0]) + nRows

c1 = pair[0].split(a[0])[0]

a = re.findall('(\d+)', pair[1])

r2 = int(a[0]) + nRows

c2 = pair[1].split(a[0])[0]

newrange = '%s%d:%s%d' % (c1, r1, c2, r2)

sheet.merge_cells(newrange)

Формирование документов Excel из Python

Как я уже написал в статье Перевод системы на Delphi и Interbase на новую архитектуру работы по разработке новой Системы уперлось в выбор фреймворка для формирования отчетов в формате MS Excel. После долгих проб я остановился на Openpyxl. 

Итак особенности, с которыми сразу столкнулся:

• Openpyxl позволяет открывать документы MS Excel сформированные только начиная с 10-й версии офиса.
• Openpyxl не сохраняет в тот же документ. Для сохранения надо указывать другой файл.
• В Openpyxl реализованы не все необходимые функции. Об этом дальше.

Перевод системы на Delphi и Interbase на новую архитектуру

Вот и пришло время переводить мою старенькую систему на Delphi и Interbase (назовем ее Система) на новую архитектуру. Терпели и так сильно долго. Необходимость обновлять парк ПК и соответственно операционку уперлось в невозможность полноценно работать с BDE в Windows начиная с 7-й версии. По итогу было принято решение переписывать систему и по возможности оторвать ее от привязки к клиентской ОС (да-да, речь о кроссплатформенности) и от версий MS Office (формирование всех отчетов выполняется в MS Excel). Скажу, что это далеко не первая попытка. Была мысль уйти в чистый веб, но в Системе есть формы ввода которые опрерируют с вводом и корректировкой в экранной форме большим числом строк (порой больше 1000) и подсасывание по 20 строк с сервера сильно замедляло работу, а порой и ложило браузер.

Итак, новая архитектура.

Серверверная часть

Сервер баз данных

Ubuntu Server

PostgreSQL

Сервер печати

• Формирование отчетов с последующей выгрузкой их в файловую систему и внесением соответствующих данных в очередь отчетов.
• Для каждого пользования системы своя папка с отчетами.
• Автоочистка папок с отчетами старше определенной даты. Запуск из планировщика.
• Минимальный интерфейс управления очередью отчетов.
• В дальнейшем добавление функционала управления приложением.

Ubuntu Server

Веб-сервер

Apache

Фреймворк сервера печати

Flask

Язык программирования

Python 3

Формирование документов Excel

Openpyxl

Дополнительные модули

SQLAlchemy

Клиентская часть

Клиентское приложение

Lazarus 

Вот такую архитектуру я принял год назад. На текущий момент многое реализовано. Об этом в следующих статьях.

Автоподбор высоты объединенных ячеек в Excel

Пришлось недавно переделывать один документ в Excel, который формируется автоматически из программы написанной Delphi. Все бы ничего, но шаблон документа имеет объединенные ячейки в которые необходимо вписывать многострочный текст. И вот тут вылезла "особенность" Excel. Он не умеет автоматически подбирать высоту строки по многострочному тексту в объединенных ячейках. Приехали!!!

Время было позднее, а сроки давили на все части мозга. Погуглив тему нашел ряд примеров с громоздким кодом расчетов ширины-высоты-прочейлабуды. Но толкового ничего. Копаться в мегаскриптах не было ни сил ни желания. Да и код должен быть максимально быстрым. Документы формируются большими пачками и операция выполняется многократно, а всем известно что любой вывод Excel это не быстрая операция.

Итак решение "в лоб". Абсолютно не красивое но весьма эффективное:

- выбираем любой столбец листа Excel за пределами бланка документа и выставляем ширину столбца равной ширине объединенных ячеек (можно на глаз);

wb.Sheets[page_number].Range['DQ:DQ'].ColumnWidth := 27;

- формируем документ и при этом дублируем выводимый текст в эту колонку;

wb.Sheets[page].Range['DQ'+h].Value:=items[i].text;

- говорим "автоподобрать высоту";

wb.Sheets[page].Rows[h+':'+h].EntireRow.AutoFit;

- запоминаем высоту строки;

x:=wb.Sheets[page].Rows[h+':'+h].RowHeight;

- очищаем секретную ячейку;

wb.Sheets[page].Range['DQ'+h].Value:='';

- выставляем высоту строки;

wb.Sheets[page].Rows[h+':'+h].RowHeight := x;

- после формирования документа возвращаем ширину столбца в исходное состояние

wb.Sheets[page_number].Range['DQ:DQ'].ColumnWidth:=0.5;

Собственно все. Надеюсь не напугал. Код не вычищал, но никакой магии там нет. Все просто и тривиально.

Пользуйтесь...

Автоматическое архивирование логов ISA 2004. Новая версия

Произошло интересное совпадение. В работе скрипта начали случаться непонятніе сбои и только я успел с ними разобраться, как в теме Автоматическое архивирование логов ISA 2004 меня попросили перевыложить исходник скрипта. Мистика блин.

Теперь по порядку. После более, чем двухлетней безукоризненной работы скрипта (раз в несколько месяцев вспоминал посмотреть) начались чудеса. Часть логов перестала попадать в архивный файл. Тоесть детач баз и удаление файлов отрабатывали, а архиватор нет. Анализ показал, что размер лог-файлов увеличился и соответственно размер архивного файла начал превышать допустимый размер. Это явилось следствием увеличения пропускной способности канала.

Соответственно пришлось переделывать скрипт. Раз нельзя упаковать логи за весь месяц в один файл – значит каждый день будет паковаться в отдельный архив. Остальные принципы не поменялись.

Ну и собственно сама ссылка на архив со скриптом.

Как качать с depositfiles

Depositefiles один из самых распространенных файлообменников. В данном посте хочу рассказать как качать с depositfiles.com из под Linux. Данный процесс будет осуществляться при помощи скрипта, написанного на bash.
Итак:

download_depositfiles()
{
wget -O- -q --post-data 'gateway_result=1' "http://depositfiles.com/ru/files/$(basename ${1})" |
sed -n -r -e 's/.*

}

Скрипт надо сохранить в файл и вызывать: "download_depositfiles <ссылка на скачивание>"

Как это все работает:
- происходит переход по ссылке на страницу с выбором варианта скачивания ("платно"/"бесплатно")
- путём отправки –post-data с параметром "gateway_result=1" происходит "нажатие" на кнопку "бесплатно"
- выполняется поиск прямой ссылки на файл в исходном коде страницы
- начинается загрузка файла по найденной ссылке
- в случае необходимости скрипт переходит в режим "ожидание".