Как создавать, прочитать, обновлять и поискать в файлах Excel с помощью Python
Эта статья подробно покажет, как работать с файлами Excel и как изменять конкретные данные с Python.
Сначала мы узнаем, как работать с файлами CSV, чтение, запись и обновление их. Затем мы посмотрим, как читать файлы, отфильтровать их по листам, искать строки/столбцы и обновлять ячейки файлов XLSX.
Начнем с самых простых формата электронной таблицы: CSV.
Часть 1 – файл CSV
Файл CSV – это файл значений, разделенных запятыми, где простые текстовые данные отображаются в табличном формате. Их можно использовать с любой программой электронной таблицы, такой как Microsoft Office Excel, Google Teversshies или LibreOffice Calc.
Файлы CSV не похожи на другие файлы электронных таблиц, потому что они не позволяют вам сохранять ячейки, столбцы, строки или формулы. Их ограничение состоит в том, что они также позволяют только один лист на файл. Мой план для этой первой части статьи – показать вам, как создавать файлы CSV, используя Python 3 и стандартный библиотечный модуль CSV.
Этот учебник закончится двумя репозиториями GitHUB и встроенное веб-приложение, которое фактически использует код второй части этого урока (но обновляется и модифицирован для определенной цели).
Написание файлов CSV
Сначала откройте новый файл Python и импортируйте модуль Python CSV.
CSV модуль
Модуль CSV включает в себя все необходимые методы, встроенные в. К ним относятся:
В этом руководстве мы собираемся сосредоточиться на методах писателя, DictWriter и Dictreader. Они позволяют редактировать, изменять и манипулировать данными, хранящимися в файле CSV.
На первом шаге нам нужно определить имя файла и сохранить его как переменную. Мы должны сделать то же самое с заголовком и информацией о данных.
filename = "imdb_top_4.csv" header = ("Rank", "Rating", "Title") data = [ (1, 9.2, "The Shawshank Redemption(1994)"), (2, 9.2, "The Godfather(1972)"), (3, 9, "The Godfather: Part II(1974)"), (4, 8.9, "Pulp Fiction(1994)") ] Теперь нам нужно создать функцию с именем писатель Это примет три параметра: Заголовок , данные и Имя файла Отказ
def writer(header, data, filename): pass
Следующий шаг – изменить писатель Функция, поэтому она создает файл, который содержит данные из Заголовок и данные Переменные. Это сделано путем написания первого ряда из Заголовок переменная, а затем писать четыре ряда из данные Переменная (есть четыре ряда, потому что в списке есть четыре кортежи).
def writer(header, data, filename): with open (filename, "w", newline = "") as csvfile: movies = csv.writer(csvfile) movies.writerow(header) for x in data: movies.writerow(x)
И VUILà! Вы создали свой первый файл CSV с именем IMDB_TOP_4.csv. Откройте этот файл с предпочтительным приложением электронной таблицы, и вы должны увидеть что-то вроде этого:
Результатом может быть написан так, если вы решите открыть файл в каком-то другом приложении:
Обновление файлов CSV
Чтобы обновить этот файл, вы должны создать новую функцию с именем Updater это займет всего один параметр под названием Имя файла Отказ
def updater(filename): with open(filename, newline= "") as file: readData = [row for row in csv.DictReader(file)] # print(readData) readData[0]['Rating'] = '9.4' # print(readData) readHeader = readData[0].keys() writer(readHeader, readData, filename, "update")
Эта функция сначала открывает файл, определенный в Имя файла Переменная, а затем сохраняет все данные, которые он читает из файла внутри переменной с именем ReadData Отказ Второй шаг – это сложный код новое значение и поместить его вместо старого в ReadData [0] [‘Рейтинг’] должность.
Последний шаг в функции – это позвонить писатель Функция, добавив новый параметр Обновить Это скажет функцию, которую вы выполняете обновление.
Для писатель Чтобы работать с новым параметром, вам нужно добавить новый параметр везде писатель определено. Вернитесь на место, где вы впервые назвали писатель Функция и добавить «Написать» как новый параметр:
writer(header, data, filename, "write")
Чуть ниже писательской функции позвоните Updater и пройти Имя файла Параметр в него:
writer(header, data, filename, "write") updater(filename)
Теперь вам нужно изменить писатель Функция, чтобы сделать новый параметр с именем Опция :
def writer(header, data, filename, option):
Отныне мы ожидаем получить два разных варианта для писатель Функция ( Написать и обновить ). Из-за этого мы должны добавить два приложения, чтобы поддержать эту новую функциональность. Первая часть Функция под «если вариант уже известна вам. Вам просто нужно добавить» Элиф Вариант: Раздел кода и еще Часть так же, как они написаны ниже:
def writer(header, data, filename, option): with open (filename, "w", newline = "") as csvfile: if option == "write": movies = csv.writer(csvfile) movies.writerow(header) for x in data: movies.writerow(x) elif option == "update": writer = csv.DictWriter(csvfile, fieldnames = header) writer.writeheader() writer.writerows(data) else: print("Option is not known") Браво! Вы готовы!
Теперь ваш код должен выглядеть что-то подобное:
Вы также можете найти код здесь:
В первой части этой статьи мы видели, как работать с файлами CSV. Мы создали и обновили один такой файл.
Часть 2 – файл XLSX
В течение нескольких выходных я работал над этим проектом. Я начал работать над этим, потому что в моем компании была необходима необходимость решения. Моя первая идея состояла в том, чтобы построить это решение непосредственно в системе моей компании, но тогда мне не было бы ничего писать, а?
Я создаю это решение с помощью Python 3 и openpyxl библиотека. Причина, почему я выбрал openpyxl Это потому, что он представляет собой полное решение для создания рабочих листов, загрузки, обновления, переименования и удаления их. Это также позволяет нам читать или записывать на строки и столбцы, слияние или сердечных ячеек или создавать графики Excel Python и т. Д.
Opternyxl терминология и основная информация
- Рабочая тетрадь – это имя для файла Excel в OutonPyXL.
- Рабочая тетрадь состоит из листов (по умолчанию 1 лист). Листы ссылаются на их имена.
- Лист состоит из строк (горизонтальные линии), начиная с номера 1 и колонн (вертикальные линии), начиная с буквы А.
- Ряды и колонны приводят к сетке и образуют клетки, которые могут содержать некоторые данные (числовое или строковое значение) или формулы.
Первый шаг – открыть свою среду Python и установить openpyxl В вашем терминале:
Далее импорт openpyxl в ваш проект, а затем загрузить рабочую книгу в Thefile Переменная.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') print(theFile.sheetnames) currentSheet = theFile['customers 1'] print(currentSheet['B4'].value) Как видите, этот код печатает все листы по их именам. Затем он выбирает лист, который называется «клиентами 1» и сохраняет его к CurrentSheet Переменная. В последней строке код печатает значение, которое находится в положении B4 «Клиенты 1» листа.
Этот код работает как следует, но это очень сложно закодировано. Чтобы сделать эту динамику, мы напишем код, который будет:
- Прочитайте файл
- Получить все имена листов
- Петли через все листы
- На последнем шаге код будет печатать значения, которые расположены в полях B4 каждого найденного листа внутри рабочей книги.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names <> " .format(theFile.sheetnames)) for x in allSheetNames: print("Current sheet name is <>" .format(x)) currentSheet = theFile[x] print(currentSheet['B4'].value) Это лучше, чем раньше, но он все еще является жестким кодированным решением, и он по-прежнему предполагает ценность, которую вы будете искать в камере B4, что просто глупо:)
Я ожидаю, что ваш проект должен будет найти все листы в файле Excel для конкретного значения. Для этого мы добавим еще один для цикла в диапазоне «ABCDEF», а затем просто печатайте имена ячейки и их значения.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names <> " .format(theFile.sheetnames)) for sheet in allSheetNames: print("Current sheet name is <>" .format(sheet)) currentSheet = theFile[sheet] # print(currentSheet['B4'].value) #print max numbers of wors and colums for each sheet #print(currentSheet.max_row) #print(currentSheet.max_column) for row in range(1, currentSheet.max_row + 1): #print(row) for column in "ABCDEF": # Here you can add or reduce the columns cell_name = "<><>".format(column, row) #print(cell_name) print("cell position <> has value <>".format(cell_name, currentSheet[cell_name].value)) Мы сделали это, введя « за ряд в ассортименте .. » петля. Диапазон циклов для цикла определен из ячейки в строке 1 к максимальному количеству листа или рядами. Второй для поиска петли в пределах предопределенных имен столбцов « abcdef ». Во второй цикле мы отобразим полное положение ячейки (имя столбца и номер строки) и значение.
Однако в этой статье моя задача – найти определенный столбец, который называется «Телефон», а затем пройти все строки этого столбца. Для этого нам нужно изменить код, как ниже.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names <> " .format(theFile.sheetnames)) def find_specific_cell(): for row in range(1, currentSheet.max_row + 1): for column in "ABCDEFGHIJKL": # Here you can add or reduce the columns cell_name = "<><>".format(column, row) if currentSheet[cell_name].value == "telephone": #print(" cell is located on " .format(cell_name, currentSheet[cell_name].value)) print("cell position <> has value <>".format(cell_name, currentSheet[cell_name].value)) return cell_name for sheet in allSheetNames: print("Current sheet name is <>" .format(sheet)) currentSheet = theFile[sheet] Этот модифицированный код проходит через все ячейки каждого листа, и, как до того, как перед динамическим диапазоном строки и диапазон колонны специфичен. Кодовые петли через ячейки и ищут клетку, которая удерживает текст «телефон». После того, как код найдет определенную ячейку, он уведомляет пользователя, в котором находится текст текста. Код делает это для каждой ячейки внутри всех листов, которые находятся в файле Excel.
Следующим шагом является пройти через все строки этого определенного столбца и значения печати.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names <> " .format(theFile.sheetnames)) def find_specific_cell(): for row in range(1, currentSheet.max_row + 1): for column in "ABCDEFGHIJKL": # Here you can add or reduce the columns cell_name = "<><>".format(column, row) if currentSheet[cell_name].value == "telephone": #print(" cell is located on " .format(cell_name, currentSheet[cell_name].value)) print("cell position <> has value <>".format(cell_name, currentSheet[cell_name].value)) return cell_name def get_column_letter(specificCellLetter): letter = specificCellLetter[0:-1] print(letter) return letter def get_all_values_by_cell_letter(letter): for row in range(1, currentSheet.max_row + 1): for column in letter: cell_name = "<><>".format(column, row) #print(cell_name) print("cell position <> has value <>".format(cell_name, currentSheet[cell_name].value)) for sheet in allSheetNames: print("Current sheet name is <>" .format(sheet)) currentSheet = theFile[sheet] specificCellLetter = (find_specific_cell()) letter = get_column_letter(specificCellLetter) get_all_values_by_cell_letter(letter) Это сделано путем добавления функции с именем get_column_letter Это находит букву столбца. После того, как буква столбца найдена, мы проходим все строки этого определенного столбца. Это делается с get_all_values_by_cell_letter Функция, которая будет распечатать все значения этих ячеек.
Обертывание
Бюстгальтер Gьорт! Там много, что вы можете сделать после этого. Мой план состоял в том, чтобы построить онлайн-приложение, которое стандартизирует все шведские телефонные номера, взятые из текстового поля и предлагают пользователям возможность просто скопировать результаты из того же текстового поля. Второй шаг моего плана состоял в том, чтобы расширить функциональность веб-приложения для поддержки загруженности файлов Excel, обработки телефонных номеров внутри этих файлов (стандартизация их в шведский формат) и предлагая обработанные файлы обратно к пользователям.
Я сделал оба этих задания, и вы можете увидеть их жить на странице инструментов моего Incodaq.com сайт:
Также код из второй части этой статьи доступен на GitHub:
Спасибо за чтение! Посмотрите дополнительные статьи, как это в моем носителе: https://medium.com/@goranaviani и другие забавные вещи, которые я строит на моей странице GitHub: https://github.com/goranaviani.