Hogyan lehet létrehozni, olvasni, frissíteni és keresni az Excel fájlokban a Python használatával

Ez a cikk részletesen bemutatja, hogyan kell működni az Excel fájlokkal, és hogyan lehet módosítani a konkrét adatokat a Python segítségével.

Először megtanuljuk, hogyan kell CSV-fájlokkal dolgozni olvasásukkal, írásukkal és frissítésükkel. Ezután megvizsgáljuk a fájlok olvasását, szűrését lapok alapján, sorok / oszlopok keresését és az xlsx fájlok celláinak frissítését.

Kezdjük a legegyszerűbb táblázatformátummal: CSV.

1. rész - A CSV fájl

A CSV fájl vesszővel elválasztott értékfájl, ahol az egyszerű szöveges adatok táblázatos formában jelennek meg. Bármely táblázatkezelő programmal használhatók, például a Microsoft Office Excel, a Google Spreadsheets vagy a LibreOffice Calc alkalmazással.

A CSV-fájlok azonban nem olyanok, mint a többi táblázatfájl, mert nem teszik lehetővé cellák, oszlopok, sorok vagy képletek mentését. Korlátozásuk az, hogy fájlonként csak egy lapot engednek meg. A cikk első részének terve az, hogy bemutassam, hogyan hozhat létre CSV fájlokat a Python 3 és a szabványos CSV könyvtár modul segítségével.

Ez az oktatóanyag két GitHub-tárral és egy élő webalkalmazással zárul, amely ténylegesen használja az oktatóanyag második részének kódját (még frissítve és módosítva, hogy meghatározott célra legyen).

Írás CSV fájlokba

Először nyisson meg egy új Python fájlt, és importálja a Python CSV modult.

import csv

CSV modul

A CSV modul tartalmazza az összes beépített szükséges módszert. Ezek a következők:

  • csv.olvasó
  • csv.író
  • csv.DictReader
  • csv.DictWriter
  • és mások

Ebben az útmutatóban az író, a DictWriter és a DictReader módszerekre fogunk koncentrálni. Ezek lehetővé teszik a CSV-fájlban tárolt adatok szerkesztését, módosítását és manipulálását.

Az első lépésben meg kell határoznunk a fájl nevét, és változóként kell mentenünk. Ugyanezt kell tennünk a fejléccel és az adatokkal is.

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

Most létre kell hoznunk egy író nevű függvényt , amely három paramétert vesz fel: fejléc , adat és fájlnév .

def writer(header, data, filename): pass

A következő lépés az író függvény módosítása, így létrehoz egy fájlt, amely a fejlécből és az adatváltozókból származó adatokat tárolja . Ez úgy történik, hogy megírja az első sort a fejléc változóból, majd négy sort ír az adat változóból (négy sor van, mert a listán belül négy sorrend van).

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)
A hivatalos Python dokumentáció leírja a csv.writer módszer működését. Erősen javaslom, hogy szánjon egy percet az elolvasására.

És voilà! Létrehozta az első CSV fájlt, amelynek neve imdb_top_4.csv. Nyissa meg ezt a fájlt a kívánt táblázatkezelő alkalmazással, és valami ilyesmit kell látnia:

Az eredmény így írható, ha a fájlt valamilyen más alkalmazásban nyitja meg:

A CSV-fájlok frissítése

A fájl frissítéséhez létre kell hoznia egy frissítő nevű függvényt , amely csak egy fájlnév nevű paramétert vesz fel .

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

Ez a függvény először megnyitja a fájlnév változóban definiált fájlt , majd a readData nevű változó belsejébe menti az összes fájlt, amelyet a fájlból olvas . A második lépés az új érték kemény kódolása és a régi helyett a readData [0] ['Értékelés'] pozícióba helyezése.

Az utolsó lépés a funkciója az, hogy hívja a író funkció hozzáadásával egy új paramétert frissítés , amely megmondja a funkciót, hogy csinálsz egy frissítést.

A csv.DictReader részletesebben az itt található hivatalos Python dokumentációban található.

Mert író , hogy a munka egy új paramétert, amire szükség van, hogy egy új paramétert mindenhol író meg van határozva. Térjen vissza arra a helyre, ahol először meghívta az író függvényt, és adja hozzá az „írást” új paraméterként:

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

Közvetlenül az író függvény alatt hívja meg az frissítőt, és adja át a fájlnév paramétert:

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

Most akkor módosítani kell a író funkció, hogy egy új paraméter nevű opció :

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

Mostantól azt várjuk, hogy két különböző opciót kapunk az író funkcióhoz ( írás és frissítés ). Emiatt hozzá kell adnunk két if-utasítást az új funkció támogatásához. A függvény első része az „if option ==“ write: ” alatt már ismert. Csak hozzá kell adnia a kód „ elif option ==„ update ”:” szakaszát és a másik részt, amint azok a következők:

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

Bravó! Kész!

Most a kódjának ilyennek kell kinéznie:

A kódot itt is megtalálja:

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

A cikk első részében láthattuk, hogyan kell működni a CSV fájlokkal. Hoztunk létre és frissítettünk egy ilyen fájlt.

2. rész - Az xlsx fájl

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.
  • A lap sorokból (vízszintes vonalak) áll, amelyek az 1-es számtól kezdődnek, és oszlopokból (függőleges vonalak), amelyek az A betűtől kezdődnek.
  • A sorok és oszlopok rácsot eredményeznek, és cellákat alkotnak, amelyek tartalmazhatnak bizonyos adatokat (numerikus vagy karakterlánc-értéket) vagy képleteket.
Az Openpyxl szépen dokumentálva van, és azt tanácsolom, hogy nézzen meg itt.

Az első lépés a Python-környezet megnyitása és az openpyxl telepítése a terminálon belül:

pip install openpyxl

Ezután importálja az openpyxl fájlt a projektjébe, majd töltsön be egy munkafüzetet a theFile változóba.

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

Bra gjort! Ezek után sok mindent megtehet. Az volt a tervem, hogy létrehozok egy online alkalmazást, amely egységesíti a szövegmezőből vett összes svéd telefonszámot, és lehetőséget kínál a felhasználóknak arra, hogy egyszerűen másolják le az eredményeket ugyanabból a mezőből. A tervem második lépése az volt, hogy kibővítsem a webalkalmazás funkcionalitását az Excel fájlok feltöltésének támogatása, a telefonszámok feldolgozása ezeken a fájlokon belül (svéd formátumba standardizálásuk) és a feldolgozott fájlok visszajuttatása a felhasználóknak.

Mindkét feladatot elvégeztem, és élőben láthatja őket az Incodaq.com webhelyem Eszközök oldalán :

//tools.incodaq.com/

A cikk második részéből származó kód is elérhető a GitHubon:

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