Как да създавате, четете, актуализирате и търсите в Excel файлове с помощта на Python

Тази статия ще покаже подробно как да работите с файлове на Excel и как да модифицирате конкретни данни с Python.

Първо ще се научим как да работим с CSV файлове, като ги четем, пишем и актуализираме. След това ще разгледаме как да четем файлове, да ги филтрираме по листове, да търсим редове / колони и да актуализираме клетки на xlsx файлове.

Нека започнем с най-простия формат на електронна таблица: CSV.

Част 1 - CSV файлът

CSV файлът е файл със стойности, разделени със запетая, където данните с обикновен текст се показват в табличен формат. Те могат да се използват с всяка програма за електронни таблици, като Microsoft Office Excel, Google Spreadsheets или LibreOffice Calc.

CSV файловете обаче не са като другите електронни таблици, защото не ви позволяват да запазвате клетки, колони, редове или формули. Тяхното ограничение е, че те също позволяват само един лист на файл. Моят план за тази първа част на статията е да ви покажа как да създавате CSV файлове с помощта на Python 3 и стандартния библиотечен модул CSV.

Този урок ще завърши с две хранилища на GitHub и уеб приложение на живо, което всъщност използва кода на втората част на този урок (но актуализиран и модифициран, за да бъде с определена цел).

Запис в CSV файлове

Първо отворете нов файл на Python и импортирайте модула CSV на Python.

import csv

CSV модул

Модулът CSV включва всички необходими вградени методи. Те включват:

  • csv.reader
  • csv.writer
  • csv.DictReader
  • csv.DictWriter
  • и други

В това ръководство ще се съсредоточим върху методите за писане, 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)
Официалната документация на Python описва как работи методът csv.writer. Силно ви препоръчвам да отделите минута, за да го прочетете.

И voilà! Създадохте първия си 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] ['Оценка'] .

Последната стъпка в функцията е да се обадите на писател функция чрез добавяне на нов параметър актуализация , която ще каже на функцията, че правите актуализация.

csv.DictReader е обяснено повече в официалната документация на Python тук.

За да работи писателят с нов параметър, трябва да добавите нов параметър навсякъде, където е дефиниран писателят . Върнете се към мястото, където за първи път се нарича писател функцията и добави "запис" като нов параметър:

writer(header, data, filename, "write")

Точно под функцията писател извикайте актуализатора и предайте параметъра на името на файла в него:

writer(header, data, filename, "write") updater(filename)

Сега трябва да се модифицира писател функцията да вземе нов параметър наречен опция :

def writer(header, data, filename, option):

От сега нататък очакваме да получим два различни варианта за писател функция ( за запис и актуализация ). Поради това трябва да добавим две инструкции if, за да поддържаме тази нова функционалност. Първата част от функцията под „if option ==“ write: ” вече ви е известна. Трябва само да добавите раздела “ elif option ==“ update ”: на кода и частта else, точно както са написани по-долу:

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")

Браво! Свършихте!

Сега вашият код трябва да изглежда по следния начин:

Можете също да намерите кода тук:

//github.com/GoranAviani/CSV-Viewer-and-Editor

В първата част на тази статия видяхме как да работим с CSV файлове. Създадохме и актуализирахме един такъв файл.

Част 2 - xlsx файлът

For several weekends I have worked on this project. I have started working on it because there was a need for this kind of solution in my company. My first idea was to build this solution directly in my company’s system, but then I wouldn’t have anything to write about, eh?

I build this solution using Python 3 and openpyxl library. The reason why I have chosen openpyxl is because it represents a complete solution for creating worksheets, loading, updating, renaming and deleting them. It also allows us to read or write to rows and columns, merge or un-merge cells or create Python excel charts etc.

Openpyxl terminology and basic info

  • Workbook is the name for an Excel file in Openpyxl.
  • A workbook consists of sheets (default is 1 sheet). Sheets are referenced by their names.
  • Листът се състои от редове (хоризонтални линии), започващи от числото 1, и колони (вертикални линии), започващи от буквата А.
  • Редовете и колоните водят до мрежа и формират клетки, които могат да съдържат някои данни (числова или низова стойност) или формули.
Openpyxl в добре документиран и бих ви посъветвал да погледнете тук.

Първата стъпка е да отворите вашата среда на Python и да инсталирате openpyxl във вашия терминал:

pip install openpyxl

След това импортирайте openpyxl във вашия проект и след това, за да заредите работна книга в променливата theFile .

import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') print(theFile.sheetnames) currentSheet = theFile['customers 1'] print(currentSheet['B4'].value)

As you can see, this code prints all sheets by their names. It then selects the sheet that is named “customers 1” and saves it to a currentSheet variable. In the last line, the code prints the value that is located in the B4 position of the “customers 1” sheet.

This code works as it should but it is very hard coded. To make this more dynamic we will write code that will:

  • Read the file
  • Get all sheet names
  • Loop through all sheets
  • In the last step, the code will print values that are located in B4 fields of each found sheet inside the workbook.
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)

This is better than before, but it is still a hard coded solution and it still assumes the value you will be looking for is in the B4 cell, which is just silly :)

I expect your project will need to search inside all sheets in the Excel file for a specific value. To do this we will add one more for loop in the “ABCDEF” range and then simply print cell names and their values.

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))

We did this by introducing the “for row in range..” loop. The range of the for loop is defined from the cell in row 1 to the sheet’s maximum number or rows. The second for loop searches within predefined column names “ABCDEF”. In the second loop we will display the full position of the cell (column name and row number) and a value.

However, in this article my task is to find a specific column that is named “telephone” and then go through all the rows of that column. To do that we need to modify the code like below.

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("{1} cell is located on {0}" .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]

This modified code goes through all cells of every sheet, and just like before the row range is dynamic and the column range is specific. The code loops through cells and looks for a cell that holds a text “telephone”. Once the code finds the specific cell it notifies the user in which cell the text is located. The code does this for every cell inside of all sheets that are in the Excel file.

The next step is to go through all rows of that specific column and print values.

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("{1} cell is located on {0}" .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) 

This is done by adding a function named get_column_letter that finds a letter of a column. After the letter of the column is found we loop through all rows of that specific column. This is done with the get_all_values_by_cell_letter function which will print all values of those cells.

Wrapping up

Сутиен gjort! Има много неща, които можете да направите след това. Планът ми беше да създам онлайн приложение, което да стандартизира всички шведски телефонни номера, взети от текстово поле, и да предложи на потребителите възможност просто да копират резултатите от същото текстово поле. Втората стъпка от плана ми беше да разширя функционалността на уеб приложението, за да подкрепя качването на файлове на Excel, обработката на телефонни номера вътре в тези файлове (стандартизирането им в шведски формат) и предлагането на обработените файлове обратно на потребителите.

Изпълних и двете задачи и можете да ги видите на живо в страницата с инструменти на моя сайт Incodaq.com :

//tools.incodaq.com/

Също така кодът от втората част на тази статия е достъпен на GitHub:

//github.com/GoranAviani/Manipulate-Excel-spreadsheets

Thank you for reading! Check out more articles like this on my Medium profile: //medium.com/@goranaviani and other fun stuff I build on my GitHub page: //github.com/GoranAviani