Приветствую, хабросообщество.
Решил написать немного про работу python с xlsx.
Да, я знаю, что про это написано много, но тем не менее, я не смог найти готовое решение для своей задачи.
Нужно заполнять несколько десятков ячеек у готового файла xls.
Все бы ничего, но файл содержал макросы, картинки, объекты media-art, внедреные объекты и много чего еще.
Перебрав популярные решения (openpyxl, xlutils.copy и пару других), я понял, что они немного портят файл.
А задача сводилась именно к точечной правке ячеек и чтоб все остальное осталось как было.
Предыстория
Неожиданно оказалось, что у одного банка в одной стране нет информационной системы
для принятия решения о выдаче кредита.
Ее заменяет файл excel, который передавался по наследству из незопамятных времен
и прошел через руки «чуть больше чем много» «программистов на Excel».
И нужно как-то передавать в банк заявки на кредит.
То есть, нужно взять этот файл, вбить в него немного данных и отправить по почте.
Я подумал «не я первый, ни я последний» и пошел искать готовые решения.
Тут все оказалось не так просто.
openpyxl:
почему-то не полностью сохранял обводку ячеек, которые я даже не трогал (оставлял границу только нижнего левого угла)
Даже при использовании keep_vba=True, получались «битые» файлы xlsm (которые могут содержать макросы)
xlutils:
Терял картинки и объекты media-art и нужно было работать с xls, вместо xlsm
Попробовав еще пару вариантов, я решил вооружится напильником, гаечными ключами и приступить к написанию своего велосипеда с более удобной формой седла, который не норовит избавиться от пары лишних деталей в процессе употребления по назначению.
На самом деле, задача предельно проста,
потому что это просто zip-архив с кучкой XML-ек.
Нам нужно просто внести чуть-чуть изменений и запаковать все это обратно, оставив все остальное как было.
Итак, поехали
Нам для этого потребуется ZipFile, lxml и больше ничего.
import os import re from datetime import datetime, date from StringIO import StringIO from zipfile import ZipFile, ZIP_DEFLATED from lxml import etree Конструктор.
Сразу узнаем, где лежат наши листы, вызвав self._get_sheet_locations()
компилируем регулярку для поиска номера строки из индекса ячейки
def __init__(self, zip_folder): u""" @param zip_folder: Путь к директории, где лежит распакованный исходный файл xlsx То есть файл '[Content_Types].xml' должен находится в этой директории """ self._zip_folder = zip_folder self._data = {} self._zip_stream = StringIO() self._row_finder = re.compile(r'\d+$') self._namespaces = { 'ws': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'rel': 'http://schemas.openxmlformats.org/package/2006/relationships' } self._sheet_paths = self._get_sheet_locations() # Словарь строк self._shared_strings = None self._shared_strings_root = None self._shared_strings_index = None Делаем функцию, которая достает нам объекты lxml etree
по пути к файлу xml, чтоб не делать одинаковые действия
def _get_xml(self, file_path): u""" Вытащить XML-объект из папки по пути @param file_path: Путь к файлу относительно директории шаблона """ return etree.parse(os.path.join(self._zip_folder, file_path)) Строки в xlsx хранятся в отдельном файле.
В ячейках (узлах xml) физически храняться индексы на записи в словаре.
Это немного похоже на архиватор.
Инициализируем словарь.
Преобразуем его объект xml, удобный для добавления новых значений.
Это не очень правильно, но мы не будем искать, есть ли строка в словаре и использовать ее повторно.
Так как объем вносимых изменений небольшой (по сравнению с кол-вом строк, которые в файле уже есть),
мы просто добавляем в словарь еще одно значение.
Нам потребуется
_shared_strings (потом из него мы будем сохранять изменный словарь)
_shared_strings_root — в него мы будем добавлять новые строки
_shared_strings_index — текущий индекс (для отслеживания счетчика добавленой строки)
def _init_shared_strings(self): u""" Ленивым образом инициализируем работу со словарем строк. Лениво - потому что работа со строками может не понадобиться. Вызывается при первом вызове self._add_shared_string """ self._shared_strings = self._get_xml('xl/sharedStrings.xml') self._shared_strings_root = self._shared_strings.xpath('/ws:sst', namespaces=self._namespaces)[0] self._shared_strings_index = int(self._shared_strings_root.attrib['uniqueCount']) Добавим строку в словарь и вернем ее индекс.
Это будет нужно для внесения изменений строковых значений
def _add_shared_string(self, value): u""" Добавить строку в словарь sharedStrings Не учитывает тот момент, что строка уже может тут быть. Но из-за малого кол-ва модификаций пофигу на раздувание словаря. uniqueCount и Count не модифицирует (и без этого все работает) @param value: Строка для добавления в словарь @return: Индекс в словаре sharedStrings """ if self._shared_strings is None: self._init_shared_strings() node_t = etree.Element('t') node_t.text = value node_si = etree.Element('si') node_si.append(node_t) self._shared_strings_root.append(node_si) self._shared_strings_index += 1 return (self._shared_strings_index - 1) Соберем данные о том, где какой лист находится в архиве.
Эти данные распределены по 2-м XML-файлам:
— xl/workbook.xml
где хранится информация о том, что в книге есть
— xl/_rels/workbook.xml.rels
где лежит информация о том, где все это лежит
def _get_sheet_locations(self): u""" Узнаем где хранятся листы @return: Словарь. {название_листа: путь_к_xml} """ # Книги sheets_id = {} workbook_xml = self._get_xml('xl/workbook.xml') for sheet_xml in workbook_xml.xpath('/ws:workbook/ws:sheets/ws:sheet', namespaces=self._namespaces): sheet_name = sheet_xml.attrib['name'] sheet_rid = sheet_xml.attrib['{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id'] sheets_id[sheet_rid] = sheet_name # Названия файлов paths = {} xml = self._get_xml('xl/_rels/workbook.xml.rels') for node in xml.xpath('/rel:Relationships/rel:Relationship', namespaces=self._namespaces): r_id = node.attrib['Id'] path = os.path.join('xl', node.attrib['Target']) if r_id in sheets_id: sheet_label = sheets_id[r_id] paths[sheet_label] = path return paths Создаем zip-архив, собирая в него все файлы, которые не менялись (без exclude_files)
def _create_base_zip(self, exclude_files): u""" Создать базовый объект на основе шаблона в папке zip_folder для модификации. В него не входят листы с изменными ячейками. Они будут добавлены туда в методе _add_changes @param exclude_files: Список исключенных файлов @return: объект ZipFile """ zip_file = ZipFile(self._zip_stream, mode='a', compression=ZIP_DEFLATED) for path, dirs, files in os.walk(self._zip_folder): rel_path = path[len(self._zip_folder):] for file_name in files: if rel_path == '': zip_name = file_name else: zip_name = os.path.join(rel_path, file_name) if zip_name not in exclude_files: zip_file.write(os.path.join(path, file_name), zip_name) return zip_file Добавим измененные листы в zip-файл, переданый в виде аргумента.
Смотрим все накопленные данные по листам, запрашиваем измененные листы и добавляем их в архив
def _add_changes(self, zip_file): u""" Применить изменения. Открываем и модифицируем файлы и заливаем их в zip поверх файлов по умолчанию @param zip_file: объект ZipFile без листов, на которых есть измененные ячейки """ # Обходим листы и модифицируем for sheet_name, data in self._data.items(): sheet_file = self._sheet_paths[sheet_name] sheet_content = self._get_changed_sheet(sheet_file=sheet_file, data=data) zip_file.writestr(sheet_file, sheet_content) Вносим изменения в лист и возвращаем строку XML с внесенными изменениями
def _get_changed_sheet(self, sheet_file, data): u""" Возвращает отредактированный файл с данными для записи в ZIP-архив @param sheet_file: Путь к xml-файлу с листом @param data: Словарь с изменениями {cell: value} @return: xml-строка с измененным листом """ xml = etree.parse(os.path.join(self._zip_folder, sheet_file)) for cell, value in data.items(): self._change_cell(xml, cell, value) return etree.tostring(xml, xml_declaration=True, encoding="UTF-8", standalone="yes") Редактируем отдельную ячейку, принимая на вход лист, адрес ячейки и значение.
Тут очень важный момент.
Ячейка в XML должна быть (потому что рассматриваем именно изменение значений в ячейках).
Эта ячейка, как правило, отформатирована и раскрашена.
Данное решение не рассматривает отсутствие узла с ячейкой.
Вы всегда можете самостоятельно добавить обработку наличия ячейки в строке
или самой строке в книге.
def _change_cell(self, xml, cell, value): u""" Изменить параметр ячейки в листе xml Мутабельный метод по отношению к параметру xml @param xml: Объект lxml @param cell: Индекс ячейки в формате "C2" @param value: Значение """ row_index = self._row_finder.search(cell).group() value_type = type(value) pattern_params = {'row_index': row_index, 'cell': cell} pattern = '/ws:worksheet/ws:sheetData/ws:row[@r="%(row_index)s"]/ws:c[@r="%(cell)s"]' % pattern_params node_c = xml.xpath(pattern, namespaces=self._namespaces)[0] node_v = node_c.find('ws:v', namespaces=self._namespaces) # В шаблоне было пусто - добавим туда значение if node_v is None: node_v = etree.Element('v') node_c.append(node_v) # Пусто if value == None: node_c.remove(node_v) if node_c.attrib.get('t') == 's': del node_c.attrib['t'] # Расшареная строка elif value_type in (unicode, str): value = str(self._add_shared_string(value)) node_c.attrib['t'] = 's' # Числовые или приравненные к ним данные else: if node_c.attrib.get('t') == 's': del node_c.attrib['t'] if value_type == datetime: value = value.date() if value_type == date: value = (value - date(1899, 12, 30)).days node_v.text = unicode(value) Публичных методов всего два.
Собираем изменения в словарь. На этом этапе не вносим изменений.
Сюда можно передавать только простые типы данных:
— None
— Число (int, float)
— Строка (str, unicode)
— Дата
def write(self, sheet, cell, value): u""" Установить значение для ячейки на листе Набирает все изменения в память. Они применяться только в момент сборки файл-архива xlsx @param sheet: Название листа @param cell: Название ячейки (например C4) @param value: Значение для записи в ячейку """ if value is not None and type(value) not in (int, float, str, unicode): raise TypeError(u'Только None, int, float, str, unicode') if sheet not in self._data: self._data[sheet] = {} self._data[sheet][cell] = value Получить контент архива.
Не стал делать это в виде файла, zip-архива, потому что контент более универсален
и его удобно отдавать через HttpResponse
def get_content(self): u""" Получить контент файл xlsx с изменениями. Листы, которые будут вставлены с изменениями, не включаем """ exclude_files = ['/%s' % e[1] for e in self._sheet_paths.items() if e[0] in self._data.keys()] exclude_files.append('/xl/sharedStrings.xml') zip_file = self._create_base_zip(exclude_files=exclude_files) self._add_changes(zip_file) zip_file.writestr('xl/sharedStrings.xml', etree.tostring(self._shared_strings, xml_declaration=True, encoding="UTF-8", standalone="yes")) zip_file.close() return self._zip_stream.getvalue() Вроде, и все.
Пользоваться этим вот так:
xlsx = XLSXEdit('path_to_unzip_folder') xlsx.write('Sheet1', 'A1', 333) xlsx.write('Sheet1', 'A2', 44444) xlsx.write('Sheet1', 'A3', datetime.now()) xlsx.write('Sheet1', 'A4', u'Строка') with open('/Users/dibrovsd/Desktop/out.xlsx', 'w') as zip_file: zip_file.write(xlsx.get_content()) Я думаю, что самое большое преимущество решения в том, что тут понятно, что происходит
и тут ничего сложного нет. При необходимости, вы можете доработать решение,
если в текущем виде оно вас не устраивает.
import os import re from datetime import datetime, date from StringIO import StringIO from zipfile import ZipFile, ZIP_DEFLATED from lxml import etree class XLSXEdit(object): u""" Редактирование файла xlsx через прямую модификацию файлов xml Зачем: Это бываем нужным, когда есть большой с кучей формул и картинок, макросов, объектов медиа-арт, разных внедренных объектов, MS query настроек (кто-то знал excel) и нужно просто в пару ячеек вставить значения Особенности: Мы аккуратно правим отдельные куски XML внутри и не трогаем все остальное """ def __init__(self, zip_folder): u""" @param zip_folder: Путь к директории, где лежит распакованный исходный файл xlsx То есть файл '[Content_Types].xml' должен находится в этой директории """ self._zip_folder = zip_folder self._data = {} self._zip_stream = StringIO() self._row_finder = re.compile(r'\d+$') self._namespaces = { 'ws': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'rel': 'http://schemas.openxmlformats.org/package/2006/relationships' } self._sheet_paths = self._get_sheet_locations() # Словарь строк self._shared_strings = None self._shared_strings_root = None self._shared_strings_index = None def write(self, sheet, cell, value): u""" Установить значение для ячейки на листе Набирает все изменения в память. Они применяться только в момент сборки файл-архива xlsx @param sheet: Название листа @param cell: Название ячейки (например C4) @param value: Значение для записи в ячейку """ if value is not None and type(value) not in (int, float, str, unicode, date, datetime): raise TypeError(u'Только None, int, float, str, unicode') if sheet not in self._data: self._data[sheet] = {} self._data[sheet][cell] = value def get_content(self): u""" Получить контент файл xlsx с изменениями. Листы, которые будут вставлены с изменениями, не включаем """ exclude_files = ['/%s' % e[1] for e in self._sheet_paths.items() if e[0] in self._data.keys()] exclude_files.append('/xl/sharedStrings.xml') zip_file = self._create_base_zip(exclude_files=exclude_files) self._add_changes(zip_file) zip_file.writestr('xl/sharedStrings.xml', etree.tostring(self._shared_strings, xml_declaration=True, encoding="UTF-8", standalone="yes")) zip_file.close() return self._zip_stream.getvalue() def _get_xml(self, file_path): u""" Вытащить XML-объект из папки по пути @param file_path: Путь к файлу относительно директории шаблона """ return etree.parse(os.path.join(self._zip_folder, file_path)) def _init_shared_strings(self): u""" Ленивым образом инициализируем работу со словарем строк. Лениво - потому что работа со строками может не понадобиться. Вызывается при первом вызове self._add_shared_string """ self._shared_strings = self._get_xml('xl/sharedStrings.xml') self._shared_strings_root = self._shared_strings.xpath('/ws:sst', namespaces=self._namespaces)[0] self._shared_strings_index = int(self._shared_strings_root.attrib['uniqueCount']) def _add_shared_string(self, value): u""" Добавить строку в словарь sharedStrings Не учитывает тот момент, что строка уже может тут быть. Но из-за малого кол-ва модификаций пофигу на раздувание словаря. uniqueCount и Count не модифицирует (и без этого все работает) @param value: Строка для добавления в словарь @return: Индекс в словаре sharedStrings """ if self._shared_strings is None: self._init_shared_strings() node_t = etree.Element('t') node_t.text = value node_si = etree.Element('si') node_si.append(node_t) self._shared_strings_root.append(node_si) self._shared_strings_index += 1 return (self._shared_strings_index - 1) def _get_sheet_locations(self): u""" Узнаем где хранятся листы @return: Словарь. {название_листа: путь_к_xml} """ # Книги sheets_id = {} workbook_xml = self._get_xml('xl/workbook.xml') for sheet_xml in workbook_xml.xpath('/ws:workbook/ws:sheets/ws:sheet', namespaces=self._namespaces): sheet_name = sheet_xml.attrib['name'] sheet_rid = sheet_xml.attrib['{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id'] sheets_id[sheet_rid] = sheet_name # Названия файлов paths = {} xml = self._get_xml('xl/_rels/workbook.xml.rels') for node in xml.xpath('/rel:Relationships/rel:Relationship', namespaces=self._namespaces): r_id = node.attrib['Id'] path = os.path.join('xl', node.attrib['Target']) if r_id in sheets_id: sheet_label = sheets_id[r_id] paths[sheet_label] = path return paths def _create_base_zip(self, exclude_files): u""" Создать базовый объект на основе шаблона в папке zip_folder для модификации. В него не входят листы с изменными ячейками. Они будут добавлены туда в методе _add_changes @param exclude_files: Список исключенных файлов @return: объект ZipFile """ zip_file = ZipFile(self._zip_stream, mode='a', compression=ZIP_DEFLATED) for path, dirs, files in os.walk(self._zip_folder): rel_path = path[len(self._zip_folder):] for file_name in files: if rel_path == '': zip_name = file_name else: zip_name = os.path.join(rel_path, file_name) if zip_name not in exclude_files: zip_file.write(os.path.join(path, file_name), zip_name) return zip_file def _add_changes(self, zip_file): u""" Применить изменения. Открываем и модифицируем файлы и заливаем их в zip поверх файлов по умолчанию @param zip_file: объект ZipFile без листов, на которых есть измененные ячейки """ # Обходим листы и модифицируем for sheet_name, data in self._data.items(): sheet_file = self._sheet_paths[sheet_name] sheet_content = self._get_changed_sheet(sheet_file=sheet_file, data=data) zip_file.writestr(sheet_file, sheet_content) def _get_changed_sheet(self, sheet_file, data): u""" Возвращает отредактированный файл с данными для записи в ZIP-архив @param sheet_file: Путь к xml-файлу с листом @param data: Словарь с изменениями {cell: value} @return: xml-строка с измененным листом """ xml = etree.parse(os.path.join(self._zip_folder, sheet_file)) for cell, value in data.items(): self._change_cell(xml, cell, value) return etree.tostring(xml, xml_declaration=True, encoding="UTF-8", standalone="yes") def _change_cell(self, xml, cell, value): u""" Изменить параметр ячейки в листе xml Мутабельный метод по отношению к параметру xml @param xml: Объект lxml @param cell: Индекс ячейки в формате "C2" @param value: Значение """ row_index = self._row_finder.search(cell).group() value_type = type(value) pattern_params = {'row_index': row_index, 'cell': cell} pattern = '/ws:worksheet/ws:sheetData/ws:row[@r="%(row_index)s"]/ws:c[@r="%(cell)s"]' % pattern_params node_c = xml.xpath(pattern, namespaces=self._namespaces)[0] node_v = node_c.find('ws:v', namespaces=self._namespaces) # В шаблоне было пусто - добавим туда значение if node_v is None: node_v = etree.Element('v') node_c.append(node_v) # Пусто if value == None: node_c.remove(node_v) if node_c.attrib.get('t') == 's': del node_c.attrib['t'] # Расшареная строка elif value_type in (unicode, str): value = str(self._add_shared_string(value)) node_c.attrib['t'] = 's' # Числовые или приравненные к ним данные else: if node_c.attrib.get('t') == 's': del node_c.attrib['t'] if value_type == datetime: value = value.date() if value_type == date: value = (value - date(1899, 12, 30)).days node_v.text = unicode(value)
Спасибо за внимание, если вы таки, дочитали до конца.
ссылка на оригинал статьи http://habrahabr.ru/post/254167/
Добавить комментарий