Работа с Excel в Питоне.
Для работы с файлами Excel в Python создано несколько библиотек. Часть из них ориентирована на работу только с файлами типа XLS, а другие могут обрабатывать и файлы типа XLSX. Файлы первого типа двоичные и кодированные, файлы второго типа текстовые, и на самом деле в действительности состоят из ряда папок (директорий). Это можно увидеть, если изменить тип файла на неизвестный системе, например, XL,DO,PP.
При просмотре в Total Commander мы увидим только гипертекстовые файлы типа XML.
Я пока предпочитаю работать в старой системе 2003.
Для обработки файлов Excel необходимо сначала скачать необходимые библиотеки, но на первом этапе надо установить программы PIP или PIP3 – установщики пакетов. Эти программы типа EXE.
После установки ПИПОВ скачиваем сами библиотеки. Для начала это могут быть пакеты чтения файлов Excel, записи файлов Excel, утилит, обработки файлов Excel в формате близком к VBA.
Все скачиваемые пакеты будут иметь расширение WHL и длинное имя. Лучше имена укоротить до XLRD, XLWT, XLUTILS и PYWIN32. Все имена файлов скачиваются в строчном формате. Я их просто выделил.
Для установки пакетов вызываем командное окно системы. Я это делаю через Выполнить и cmd в окне. В начале лучше войти в папку, где находятся пакеты для установки. Я скопировал эти пакеты в директорию scripts папки Python 3_7 на диске С.
Поэтому набираю в командном окне
cd C:\ Python 3_7\scripts
Т.к. по умолчанию система ПК предполагает выбор директории Мои Документы или User в некоторых случаях, то в ней можно создать Bat файл с командами выше. Я это делаю. Файл можно назвать питон.bat или python.bat. Для Анаконда я создаю файл conda.bat.
После установки exe файлов pip.exe или pip3.exe можно начать устанавливать пипами выбранные пакеты.
pyp3 install xlrd, потом pyp3 install xlwt и т.д.
Установка библиотек для работы с файлами Excel происходит быстро. Если возникают ошибки, то они видны в окне CMD. О попытке загрузки уже установленной ранее библиотеки также появляется сообщение. Пакет может быть, хотя и установлен, но не работать по каким-то причинам. Так у меня в начале произошло с пакетом PANDAS. В этом случае для удаления сбойного пакета вызывается в том же окне CMD команда UNINSTALL. Между прочим у меня не пошла установка и пакета Anaconda3. Все правильно установилось только тогда, когда я снёс старый Питон и почистил реестр Windows методом тупого поиска ссылок. Хотя это можно сделать было и программно.
Пакет utils связующий между пакетами xlrd, xlwt.
Что я заметил при обработке файлов Excel?
Обработка файлов Excel в питоне с помощью COM32 (Win32) более понятна и близка по виду к работе с макросами. Но время обработки зато приближается к времени обработке в самом Excel или даже больше. Обработка файлов Excel командами библиотек XLRD и XLWT происходит быстро, очень быстро.
Второй момент. Модуль XLRD служит лишь для чтения и обработки считанного файла. Записать открытый файл, сохранить его нельзя. Это приводит к ошибке.
Обязательно надо сначала создать модулем XLWT новый файл Excel и добавить в него необходимое число листов, затем переписать в него данные со считанного ранее файла и записать новые сведения. Новый файл можно создать и методом копирования. Обработку данных надо вести в считанном по XLRD файле, формировать данные на листах выходного файла, а только потом записывать, перезаписывать или добавлять вниз. К листу можно обратиться или по имени, или по индексу.
Ниже один из примеров с полей Интернет
Пример просто на чтение и обработку:
import xlrd
wb_path = r’c:\Python 3_7\Работа с Excel\analiz.xls’
output_path = r’c:\Python 3_7\Работа с Excel\output.txt’
wb = xlrd.open(wb_path)
ws = wb.sheets()[0]# Это обращение к первому листу файла
# assuming you want to work with the first sheet in the workbook with open(output_path, ‘w’) as output_file:
for i in xrange(ws.nrows):
row = [cell.value for cell in ws.row(i)]
# … replace the following if statement with your own conditions …
if row[0] == u’interesting’:
output_file.write(‘\t’.join(row) + ‘\r\n’)
Пример на создание нового файла и запись в него данных
# тут важно поставить ‘utf-8’, если надо писать русские символы и так далее
import xlwt
wbk = xlwt.Workbook(‘utf-8’)
# добавляем лист
sheet = wbk.add_sheet(‘sheet 1’)
# пишем в первую строку и первый столбец
sheet.write(0, 0, ‘bla bla’)
print (2**3)
# сохраняем в файл
wbk.save(‘C:/Python 3_7/test.xls’)
Параметры команд каждого модуля можно просмотреть командой Dir, например, dir(xlrd), dir (xlwt). При работе с Питоном не стоит забывать, что он чувствителен к написанию команд и идентификаторов в строчном или заглавном формате, например, команда Print (…..) вызовет ошибку, т.к. она должна набираться как print.
P.S. Сегодня день ракетных войск и артиллерии 19 ноября, посвященный фактически началу зимнего контрнаступления под Сталинградом. Я служил когда-то в ракетных войсках, так что это и мой праздник.
А теперь к теме. Наконец-то я написал свой работающий модуль обработки Экселовского файла диагностических работ. В нем данные со 2 строки по 861. Шапка в первой строке. Файлы диагностики есть на моём сайте, поэтому я его не размещаю. В исходном файле данные почему-то по 107 столбец, хотя заполнено всего 27, а вернее 28. Питон ведь ведёт отсчёт с нуля. К этому тоже надо привыкать после VBA. В VBA проблема выделения столбцов решается переходом с метода UsedRange на метод XlEnd. В питоне пришлось действовать пока более грубо путём поиска пустого первого столбца.
В своём модуле я создавал много проверочных операторов print, чтобы отладить программу. Часть из них я до сих пор оставил по привычке делать программы более удобные для диагностики и обучения.
Какая ещё проблема возникла у меня? Очень простая, но я долго из-за неё искал ошибку. Это тип данных и их преобразование.
Вариант программы для обработки файлов Excel Анализ диагностических работ.
Напомню, что этот файл состоит из 7 основных полей –
| Муниципалитет | Общеобразовательная организация | Класс | Участник | Сумма баллов | Процент выполнения | Оценка |
Далее идут столбцы заданий. В моём проверочном файле было 20 заданий в столбцах 8-27. В питоне это range(7,27). По каждому выполненному заданию выставляется оценка-критерий 0,1,2 или X-латинское. Поиск школ – это лишнее в алгоритме, но использовалось для проверки. Была попытка использовать функцию для подсчета количества данных критериев по столбцу, что логично. Но это приводило к смещению выводимых данных на столбец вправо. Так и не сумел в этом разобраться. Поэтому использовал алгоритм подсчёта количества записей по данному критерию в данном столбце в данной школе в самом тексте программы. У меня есть аналогичная программа в макросах VBA. Но в питоне обработка ведётся в десятки раз быстрее, не смотря на операторы вывода, правда пока не сумел сохранить форматирование. В макросах я могу моделировать и функции Excel, а возможно ли это здесь? Пока не знаю.
Выходные данные работы программы
Sheet: Лист1
Число столбцов = 107
Число строк на листе – 861 А число найденных столбцов на листе – 107
Определяем пустую ячейку и колонку
Номер первого пустого столбца = 27
Диапазон поиска: строк= 861 столбцов 27
#Список 29 школ и числа учащихся в них опущен
spList= [0.0, 1.0, 2.0, ‘X’]
Список оценок заданий, отсортированный по убыванию – [‘X’, ‘2.0’, ‘1.0’, ‘0.0’]
Список оценок заданий, отсортированный по возрастанию – [‘0.0’, ‘1.0’, ‘2.0’, ‘X’]
#Выше видно, что уже изначально список был Питоном отсортирован по возрастанию, но типы данных разные
#Из-за этого сортировка давала бы ошибку, если бы не было приведению к одному типу
Число значений = 4
0.0
1.0
2.0
X
Число оценок = 4
X <class ‘str’>
s.nrows= 861
>>>
Текст программы
from xlrd import open_workbook
import xlrd
import xlwt
import xlutils
import sys
wb_path = r’c:\Python 3_7\Работа с Excel\analiz.xls’
output_path = r’c:\Python 3_7\Работа с Excel\output_school_kriterii_schools.xls’
wb = open_workbook(wb_path,formatting_info=True)
#Создаём новую рабочую книгу
wbk = xlwt.Workbook(‘utf-8’)
ws = wbk.add_sheet(‘A Test Sheet’)
#print (wb.sheet.name)
for s in wb.sheets():
print (‘Sheet:’,s.name)
# s=wb.sheets(0)
print(“Число столбцов =”,s.ncols)
#Определяем номер последней строки и последней колонки на листе WB
i=s.nrows
j=s.ncols
#Число колонок часто больше по UsedRange в VBA, чем заполненных
#Ищем последнюю колонку таблицы
print (“Число строк на листе -“,i,” А число найденных столбцов на листе -“,j)
print (“\nОпределяем пустую ячейку и колонку”)
for p in range(s.ncols):
if s.cell(1,p).value==””:
break
print (“\nНомер первого пустого столбца = “,p)
j=p
print (“\n\nДиапазон поиска: строк=”,i, ” столбцов”,j)
#Заводим значения типа Set для получения значений критериев и наименований школ
values = set()
schools = set()
#Получение данных о количестве и значении оценок заданий
for row in range(1,i):
n=row%100 # Ниже условие для подсчета числа обработанных строк
# if n==0:
#Вывод количества обработанных строк с шагом 100
# print (“Обработано строк-“,row)
#Заполнение наименований школ из колонки №2
school=s.cell(row,1).value
schools.add(school)
for col in range(7,j):
value = (s.cell(row,col).value)
# try : value = str(int(value))
# except : pass
values.add(value)
#Печать найденных школ после преобразования в список
spSchool=list(schools)
spSchool.sort(reverse=False)
#for s1 in spSchool:
# print (“\n”,s1)
# Предыдущий и этот циклы можно было объединить
#Подсчет числа записей в конкретной школе и вывод на печать для контроля
n=1
for sps in spSchool:
for i in range(2,s.nrows):
school=s.cell(i,1).value
if sps==school:
n+=1
print (sps,” -“, n)
n=0
#Вывод критериев – оценок задания диагностической работы
global spList
spList=list(values)
print(“spList=”,spList)
#Перевод всех значений в строковый вид, иначе будет ошибка ниже, т.к 1.0 – float. а Х – str
for k in range(len(spList)):
spList[k]=str(spList[k])
#Печать значений в прямой и обратной сортировке
spList.sort(reverse=True)
print (“Список оценок заданий, отсортированный по убыванию – “,spList)
spList.sort(reverse=False)
print (“Список оценок заданий, отсортированный по возрастанию – “,spList)
print (spList[3],type(spList[3]))
#Перезапись в новый файл первой строки шапки, запоминание текущей школы во второй строке
for j in range(p):
ws.write(0,j,s.cell(0,j).value)
#Начало вывода данных в файл и формирование сумм по критериям задания по каждой школе
school1=s.cell(1,1).value #Запоминание школы
stroka_end=0 #Конечная строка в текущей школе
stroka_begin=stroka_end #Начальная строка в текущей школе
stroka_ws=1 #Запоминание текущей строки вывода в выходной файл
for i in range(1,s.nrows): # Исправить на s.nrows
school2=s.cell(i,1).value
if school2==school1:
stroka_end+=1
#stroka_ws+=1
for j in range (p):
ws.write(stroka_ws,j,s.cell(i,j).value)
stroka_ws+=1
else:
#Школа2 не равна Школе1
school1=school2
stroka_ws+=1
stroka_end+=1
for j1 in range (7,p):
n1=n2=n3=n4=0
for i in range(stroka_begin,stroka_end):
value =str(s.cell(i,j1).value)
if value==spList[0]:
n1+=1
if value==spList[1]:
n2+=1
if value==spList[2]:
n3+=1
if value==spList[3]:
n4+=1
ws.write(stroka_ws+1,j1,n1)
ws.write(stroka_ws+2,j1,n2)
ws.write(stroka_ws+3,j1,n3)
ws.write(stroka_ws+4,j1,n4)
ws.write(stroka_ws+1,6,spList[0])
ws.write(stroka_ws+2,6,spList[1])
ws.write(stroka_ws+3,6,spList[2])
ws.write(stroka_ws+4,6,spList[3])
stroka_ws+=6
#Дополнительная корректировка вывода, т.к. иначе теряется первая строка новой школы
for j in range (p):
ws.write(stroka_ws,j,s.cell(i+1,j).value)
stroka_ws+=1
stroka_begin=stroka_end
print(“Школа1=”,school1,” Школа2=”,school2,n1,n2,n3,n4,stroka_begin,stroka_end,stroka_ws)
stroka_ws+=1
ws.write(stroka_ws+1,6,spList[0])
ws.write(stroka_ws+2,6,spList[1])
ws.write(stroka_ws+3,6,spList[2])
ws.write(stroka_ws+4,6,spList[3])
for j1 in range (7,p):
n1=n2=n3=n4=0 #Обнуляем значения
for i in range(stroka_begin,stroka_end):
value =str(s.cell(i,j1).value)
if value==spList[0]:
n1+=1
if value==spList[1]:
n2+=1
if value==spList[2]:
n3+=1
if value==spList[3]:
n4+=1
ws.write(stroka_ws+1,j1,n1)
ws.write(stroka_ws+2,j1,n2)
ws.write(stroka_ws+3,j1,n3)
ws.write(stroka_ws+4,j1,n4)
wbk.save(output_path)
#Выход из программы, если обработка больше не нужна
sys.exit([n])
Рекомендую установить Anaconda3. Среди её модулей есть прекрасное приложение JupiterLab. Это и прекрасный редактор и программная среда, сохраняющая и текст программы и результаты её работы.
Пример созданной в JupiterLab статьи на основе информации из Интернет
Пример подключения файла Excel для получения входных данных для круговой диаграммы
Эти статьи надо скачать и запустить на своём ПК, но можно вставлять непосредственно и на свой сайт.
Эти примеры созданы в JupiterLab и переведены в файлы HTML через File-> Export->HTML
Посмотрите новую заметку по консолидации