Sådan oprettes, læses, opdateres og søges gennem Excel-filer ved hjælp af Python

Denne artikel viser detaljeret, hvordan man arbejder med Excel-filer, og hvordan man ændrer specifikke data med Python.

Først lærer vi at arbejde med CSV-filer ved at læse, skrive og opdatere dem. Derefter tager vi et kig på, hvordan man læser filer, filtrerer dem efter ark, søger efter rækker / kolonner og opdaterer celler i xlsx-filer.

Lad os starte med det enkleste regnearksformat: CSV.

Del 1 - CSV-filen

En CSV-fil er en kommasepareret værdifil, hvor almindelig tekstdata vises i tabelformat. De kan bruges med ethvert regnearksprogram, såsom Microsoft Office Excel, Google Spreadsheets eller LibreOffice Calc.

CSV-filer er dog ikke som andre regnearkfiler, fordi de ikke giver dig mulighed for at gemme celler, kolonner, rækker eller formler. Deres begrænsning er, at de kun tillader kun et ark pr. Fil. Min plan for denne første del af artiklen er at vise dig, hvordan du opretter CSV-filer ved hjælp af Python 3 og standardbiblioteksmodulet CSV.

Denne tutorial afsluttes med to GitHub-arkiver og en live webapplikation, der faktisk bruger koden i anden del af denne tutorial (endnu opdateret og ændret til at være til et specifikt formål).

Skrivning til CSV-filer

Først skal du åbne en ny Python-fil og importere Python CSV-modulet.

import csv

CSV-modul

CSV-modulet indeholder alle de nødvendige indbyggede metoder. Disse inkluderer:

  • csv.læser
  • csv.writer
  • csv.DictReader
  • csv.DictWriter
  • og andre

I denne vejledning vil vi fokusere på metoderne forfatter, DictWriter og DictReader. Disse giver dig mulighed for at redigere, ændre og manipulere de data, der er gemt i en CSV-fil.

I det første trin skal vi definere navnet på filen og gemme den som en variabel. Vi skal gøre det samme med header og dataoplysninger.

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

Nu skal vi oprette en funktion, der hedder forfatter , der tager tre parametre: overskrift , data og filnavn .

def writer(header, data, filename): pass

Det næste skridt er at ændre forfatteren funktion, så det skaber en fil, der indeholder data fra header og data variabler. Dette gøres ved at skrive den første række fra header variabel og derefter skrive fire rækker fra data variabel (der er fire rækker, fordi der er fire tupler inde i listen).

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)
Den officielle Python-dokumentation beskriver, hvordan csv.writer-metoden fungerer. Jeg vil kraftigt anbefale, at du tager et øjeblik på at læse det.

Og voilà! Du oprettede din første CSV-fil ved navn imdb_top_4.csv. Åbn denne fil med dit foretrukne regnearksprogram, og du skal se noget som dette:

Resultatet kan blive skrevet sådan, hvis du vælger at åbne filen i et andet program:

Opdatering af CSV-filer

For at opdatere denne fil skal du oprette en ny funktion ved navn updater, der kun tager en parameter kaldet filnavn .

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

Denne funktion åbner først filen er defineret i filnavnet variabel og gemmer alle de data, den læser fra filen indersiden af en variabel med navnet derefter readData . Det andet trin er at hårdkode den nye værdi og placere den i stedet for den gamle i readData [0] ['Rating'] position.

Det sidste trin i den funktion er at kalde forfatteren funktionen ved at tilføje en ny parameter opdatering , der vil fortælle den funktion, at du gør en opdatering.

csv.DictReader forklares mere i den officielle Python-dokumentation her.

For at forfatter skal arbejde med en ny parameter, skal du tilføje en ny parameter overalt, hvor forfatter er defineret. Gå tilbage til det sted, hvor du først ringede til forfatterfunktionen , og tilføj "skriv" som en ny parameter:

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

Lige under forfatterfunktionen skal du ringe til opdatereren og sende filnavnparameteren til den:

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

Nu skal du ændre skribentfunktionen for at tage en ny parameter med navnet option :

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

Fra nu af forventer vi at modtage to forskellige muligheder for forfatterfunktionen ( skriv og opdater ). På grund af dette skal vi tilføje to if-udsagn til støtte for denne nye funktionalitet. Første del af funktionen under "hvis option ==" skriv: " er allerede kendt for dig. Du skal bare tilføje “ elif option ==“ update ”: sektionen af ​​koden og den anden del, ligesom de er skrevet nedenfor:

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

Bravo! Du er færdig!

Nu skal din kode se sådan ud:

Du kan også finde koden her:

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

I den første del af denne artikel har vi set, hvordan man arbejder med CSV-filer. Vi har oprettet og opdateret en sådan fil.

Del 2 - Xlsx-filen

I flere weekender har jeg arbejdet med dette projekt. Jeg er begyndt at arbejde på det, fordi der var behov for denne form for løsning i min virksomhed. Min første idé var at bygge denne løsning direkte i min virksomheds system, men så ville jeg ikke have noget at skrive om, ikke?

Jeg bygger denne løsning ved hjælp af Python 3 og openpyxl- biblioteket. Årsagen til, at jeg har valgt openpyxl, er, at det repræsenterer en komplet løsning til oprettelse af regneark, indlæsning, opdatering, omdøbning og sletning af dem. Det giver os også mulighed for at læse eller skrive til rækker og kolonner, flette eller fjerne fletning af celler eller oprette Python excel-diagrammer osv.

Openpyxl-terminologi og grundlæggende info

  • Arbejdsmappe er navnet på en Excel-fil i Openpyxl.
  • En projektmappe består af ark (standard er 1 ark). Ark refereres til ved deres navne.
  • Et ark består af rækker (vandrette linjer) startende fra nummer 1 og kolonner (lodrette linjer) startende fra bogstavet A.
  • Rækker og kolonner resulterer i et gitter og danner celler, der kan indeholde nogle data (numerisk værdi eller strengværdi) eller formler.
Openpyxl er pænt dokumenteret, og jeg vil råde dig til at se her.

Det første trin er at åbne dit Python-miljø og installere openpyxl i din terminal:

pip install openpyxl

Derefter skal du importere openpyxl til dit projekt og derefter indlæse en projektmappe i theFile- variablen.

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! Der er mange ting, du kan gøre efter dette. Min plan var at opbygge en online-app, der vil standardisere alle svenske telefonnumre taget fra en tekstboks og give brugerne mulighed for blot at kopiere resultaterne fra den samme tekstboks. Det andet trin i min plan var at udvide funktionaliteten i webappen til at understøtte upload af Excel-filer, behandling af telefonnumre inde i disse filer (standardisere dem til svensk format) og tilbyde de behandlede filer tilbage til brugerne.

Jeg har udført begge disse opgaver, og du kan se dem live på siden Værktøjer på mit Incodaq.com- websted:

//tools.incodaq.com/

Koden fra anden del af denne artikel er også tilgængelig på GitHub:

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

Tak fordi du læste! Tjek flere artikler som denne på min Medium-profil: //medium.com/@goranaviani og andre sjove ting, jeg bygger på min GitHub-side: //github.com/GoranAviani