23 июля 2010 в 13:33

Обработка Excel файлов с использованием Python

image
По работе пришлось столкнуться с задачей обработки xls файлов средствами python. Немного по гуглив, я натолкнулся на несколько библиотек, с помощью которых можно работать с файлами excel.

Библиотеки:
— xlrd – дает возможность читать файлы Excel
— xlwt – создание и заполнение файлов Excel
— xlutils – набор утилит для расширения возможности предыдущих двух библиотек
— pyExcelerator – также дает возможность работать с файлами Excel, но давно не обновлялась.

Для своей задачи я использовал первые три библиотеки.
Задача была разбита на несколько частей: чтение файла с расширением xls; создание нового и заполнение его; создание копии файла на основе входного файла; удаление необходимых строк в выходном файле.

Чтение входного файла


Эта задача не отличается высокой сложностью. Документация и примеры, идущие в комплекте с xlrd, помогли быстро решить ее.
Пример кода:
import xlrd
rb = xlrd.open_workbook('d:/final.xls',formatting_info=True)
sheet = rb.sheet_by_index(0)
for rownum in range(sheet.nrows):
row = sheet.row_values(rownum)
for c_el in row:
print c_el


Создание нового файла и заполнение его


Эта задача оказалась не сложнее предыдущей. Документация и примеры помогли.
Пример кода:
import xlwt
from datetime import datetime

font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.bold = True

style0 = xlwt.XFStyle()
style0.font = font0

style1 = xlwt.XFStyle()
style1.num_format_str = 'D-MMM-YY'

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(00'Test', style0)
ws.write(10, datetime.now(), style1)
ws.write(201)
ws.write(211)
ws.write(22, xlwt.Formula("A3+B3"))

wb.save('example.xls')


Создание копии файла на основе входного файла


Эта задача может решаться двумя путями. Вариант первый: открываем на чтение входной файл, создаем новый файл и по циклу переписываем все данные с одного файла в другой. Такое решение не сложно реализовать, поэтому пример кода выкладывать нет смысла. Вариант второй: воспользоваться библиотекой xlutils. В данной библиотеке есть много чего интересного и полезного, но для нашей задачи будет интересен именно xlutils.copy.
И так, пример кода по созданию файла на основании входного с использованием xlutils.copy:
import xlrd
import xlwt
from xlutils.copy import copy

rb = open_workbook('final.xls',on_demand=True,formatting_info=True)
wb = copy(rb)
wb.save("final_complete.xls")


Вот такой вот небольшой код получился. Для того чтобы он работал, обязательно должен стоять флаг on_demand=True. Благодаря использованию флага formatting_info выходной файл получается с такими же стилями оформления, как и входной. Для моей задачи это оказалась нужная опция.

Удаление строк по заданному условию


Для решения данной задачи было решено использовать фильтр. Один из вариантов — это переписывание из одного файла в другой, исключая те варианты, которые не выполняют заданное условие. Но тут есть одна загвоздка, если необходимо сохранить стиль оформление документа, то этот подход не подойдет (Если конечно вы заранее не знаете стиль оформления и можете задать его программно). Решение поставленной задачи было достигнуто посредством использования xlutils.filter. Задача: оставить в выходном Excel файле только те записи, которые содержатся в передаваемом списке.
Код, который решает данную задачу:
from xlutils.filter import GlobReader,BaseFilter,DirectoryWriter,process

myfile='final2.xls'
mydir='d:/' 

class MyFilter(BaseFilter): 

    goodlist = None
    
    def __init__(self,elist): 
        self.goodlist = goodlist
        self.wtw = 0
        self.wtc = 0
         

    def workbook(self, rdbook, wtbook_name): 
        self.next.workbook(rdbook, 'filtered_'+wtbook_name) 

    def row(self, rdrowx, wtrowx):
        pass

    def cell(self, rdrowx, rdcolx, wtrowx, wtcolx): 
        value = self.rdsheet.cell(rdrowx,rdcolx).value
        if value in self.goodlist:
            self.wtc=self.wtc+1 
            self.next.row(rdrowx,wtrowx)
        else:
            return
        self.next.cell(rdrowx,rdcolx,self.wtc,wtcolx)
        
        
data = """somedata1
somedata2
somedata3
somedata4
somedata5
"""


goodlist = data.split("\n")

process(GlobReader(os.path.join(mydir,myfile)),MyFilter(goodlist),DirectoryWriter(mydir))


Заключение


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

Ссылки


sourceforge.net/projects/pyexcelerator/
www.python-excel.org/ — на три первых библиотеки.
groups.google.com/group/python-excel — группа, в которой обсуждают использование библиотек xlrd, xlwt и xlutils.

P.S. Думаю было бы неплохо перенести данный пост в тематический блог.
+45
28157
214
atlete 8,5

Комментарии (54)

–1
barbuza, #
выступлю в роли грамарнаци.
«Обрабываем Excel файлы, используя Python» или «Обработка Excel файлов с использованием Python»
0
atlete, #
Благодарю за подсказку — исправил.
+3
barbuza, #
еще хорошо бы сделать подсветку кода. есть известный на хабре syntax highlighter, есть мой paste.ly
0
atlete, #
Спасибо за сервис.
Так пост смотрится значительно лучше.
0
helm2004, #
Спасибо, пригодилось — только что поставили мне такую задачу — чтение и запись Excel файлов.
–8
Frodo, #
Вам была поставлена задача сделать это именно питоном?
ИМХО с помощью ВБА это все делается быстрее, легче и правильнее.
+11
atlete, #
У меня стояла задача работать с excel файлами под ОС FreeBSD.
Это кусочек серверной части.
+11
Malamut, #
ИМХО использовать VB* хоть для чего-то само по себе извращение, какое поискать ещё надо.
0
KReal, #
Для макросов в офисе и .vbs скриптов — вполне.
+7
Malamut, #
Хм, сомнительное удовольствие после perl и bash использовать VBS. Да и просто сомнительное удовольствие использовать VBS.
0
KReal, #
Вполне допускаю, ибо перл и баш никогда не использовал. Просто поддерживая один из проектов столкнулся с .vbs скриптами, запускаемыми через SQL Agent (я думаю, линуксоиды дружно сплюнули). И ничего, через месяц даже стал находить в этом какой-то смысл!
+1
stansult, #
имхо любой инструмент имеет право на существование,
и для любого инструмента найдутся задачи, которые им удобно решать
+2
xrd, #
А почему нельзя было просто файл скопировать?
+2
atlete, #
потому что надо было из файла удалить те строки, которые не удовлетворяют заданное условие.
+1
Averrin, #
А есть что-нить хорошее, для вордовских файлов? Гос учреждения требують...=(
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
–8
System32, #
Есть Microsoft Office WebApps, которые интегрируются с SharePoint — и на портале можно прямо через браузер и смотреть и редактировать и ворд, и ексель, и даже аксесс. Это IMHO интереснее, чем простая конвертация в HTML.
+3
atlete, #
У Microsoft все продукты между собой взаимосвязаны. Но что делать если на сервере стоит не Windows Server. А поднимать Mono и разворачивать SharePoint для задачи совсем не актуально.
–2
System32, #
<sarcasm&bt;Поднять Windows Server на виртуалке!</sarcasm&bt;
На самом деле тогда действительно остается только конвертить. Хотя использовать для этого тяжелющий OpenOffice — это IMHO как из пушки по воробьям. Уж лучще какие-нибудь более легкие решения использовать — вот типа как библиотеки из топика.
+2
LIAL, #
>Поднять Windows Server на виртуалке!… тогда действительно остается только конвертить.

Это у вас получается из пушки по воробьям, а человек просто решил поставленную задачу
–1
System32, #
Задачу-то он решил, но вот как бы сервак не «просел» под такой нагрузкой.
НЛО прилетело и опубликовало эту надпись здесь
+1
lexun, #
Начиная с офиса 2007 Мелкомягкие продавили новый формат документа OpenXML, точнее не то что бы он был новый есть замечательный формат ODF который теже мелкомягкие проигнорировали, но суть не в этом, OpenXML это по сути zip архив в котором лежат обычные xml файлы, теперь не нужно корячится чтобы создать/изменить документ офиса, можно воспользоваться всем функционалом презентаций, електронных таблиц и документов просто изменив xml внутри архива, а как это сделать читаем стандарт OpenXML на 4тысячи страниц). Благо Микрософт вставил туда уйму примеров…
0
atlete, #
К сожалению не все еще пользователи перешли на офис 2007 и выше. Поэтому задачи по созданию xls файлов еще актуальны.
0
lexun, #
Да, но и он уже довольно распространен, и писать под него уже нужно, и самое важное — гораздо приятнее.
0
atlete, #
Думаю это будет следующей задачей. Конечно же нужно идти в ногу со временем.
НЛО прилетело и опубликовало эту надпись здесь
+1
abushev, #
У Джоэла в послелдей книге есть цела глава о том почему не стоит программно писать в файлы формата xls, doc и т.д. — почитайте любопытно.
Вам действительно не подходил стандартный svc формат?
В целом стать хорошая, спасибо — либа может оказаться полезной многим.
0
Monca, #
угу, cvs + awk — никаких xls
+8
habamax, #
Ну раз пошла такая пьянка: 'svc', 'cvs' — Comma Separated Values — csv.
+2
atlete, #
Проблема в том что пользователи отправляют на вход файлы разных форматов.
Один из форматов, которые необходимо поддерживать как раз Excel.
Система также понимает txt и csv формат.
И на выходе пользователи хотят видеть данные в том формате,
в котором они подали их на вход. Но уже обработанные.
0
abushev, #
Проблема в том, что при выходе новой версии ms office нужно будет обновлять соответствующие пакеты на сервере. Не факт, что либа к тому времени будет еще поддерживаться или же обновления придется ждать значительное время. А сказать, что «мы не поддерживаем версию ms office выше N» сродни тому чтобы сказать «мы не поддерживаем xls». Ну и говоря, что вы поддерживаете формат xls — вы тем самым подписываетесь под тем, что весь функционал из исходного документа будет перенесен в результирующий. Это так? Макросы, диаграммы и.т.д?
0
atlete, #
Вероятность наличия макросов и диаграмм в исходном документе мала.
Данное решение не поддерживает перенос всего этого.
Скорее формат xls используется пользователями, как привычный формат
формирования данных в таблицы. Пользователи не заботятся о том, в каком формате они сохранили данные. Это решение было призвано облегчить им немного жизнь.
Не могу с Вами не согласится по поводу поддержки формата xls.
+1
laQie, #
в новых же версиях он уже не xls, а xlsx.
0
atlete, #
За наводку на книгу спасибо, почитаю.
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
0
Regis, #
Читайте туториал по ссылке выше. Там всё просто и понятно разобрано.
0
Deepwalker, #
Главное не надо приходить на #python и #pythonua, если у вас там что-то не работает в symbian. Очень просим.
0
akral, #
0
baxtep, #
Моногоуважаемые, подскажите пожалуйста простой способ превращать xls в csv utf-8 средствами питона?
+1
atlete, #
Как то так, но работу скрипта не проверил:
import xlrd
fp = open("my.csv","wb")
rb = xlrd.open_workbook('my.xls')
sheet = rb.sheet_by_index(0)
for rownum in range(sheet.nrows):
    row = sheet.row_values(rownum)
    c_row=[]
    for c_el in row:
        c_row.append(c_el)
    fp.write(";".join(c_row))
fp.close()
+2
seriyPS, #
Все же для записи CSV лучше использовать docs.python.org/library/csv.html т.к. всякие экранирования символов и т.п. нужны на случай если в ячейке вдруг попадется точка с запятой/запятая
0
nailyangazov, #
Как раз вовремя — решаю подобную задачу. Спасибо.
0
gigimon, #
А еще была библиотека pyExcelerator, правда ее автор забросил
+1
atlete, #
По этой причине я и выбрал использование библиотек xlrd, xlwt и xlutils. И если я не ошибаюсь, то xlwt вышла из pyExcelerator. Только исправили все баги.
0
prefer, #
В прошлом году стояла подобная задача: есть некий excelевский шаблон со всевозможными стилями, картинками и формулами. Его нужно использовать как основу для отчетов.

Выявились проблемы:
1) xlrd не умеет понимать формулы и картинки.
2) xwlt не может менять документы.
Таким образом, чтобы постороить отчет его необходимо полностю написать в объектах xwlt, но так как xlrd не умеет полностью прочитать шаблон, эта задача становится невозможной.

В итоге поставленную задачу решили по средсвам Apache POI

Прошу меня поправить, если я не прав и сейчас поведение библиотеки изменилось.
0
atlete, #
Насчет формул сказать толком ничего не могу.
Потому что не попадались. Но вопросы по формулам читал в процессе поиска информации.
Насчет картинок так и есть, после создание копии посредством xlutils.copy картинки пропадают.
0
alekam, #
формулы так и не сделали (
0
rhangelxs, #
Так и не смог понять, по какому там принципу округление работает. У меня вместо n-значного числа его нормализованное представление. Буду признателен за советы.
0
alekam, #
округление зависит от формата ячейки.
операции над ячейками осуществляются до применения форматирования.
0
Leo5700, #
Atlete, спасибо за статью, готовое решение сэкономило час-другой времени.
0
atlete, #
Рад что вам помогло. Есть похожее решение для работы с xlsx файлами. Правда я не уверен что это стоит отдельной статьи.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.