Ĉapitro 12

Ĉapitro 12 - Laborante kun Excel Spreadsheets

Subtenu la Aŭtoro: Aĉeti la libron sur Amazono aŭ
la libro / ebook pakaĵo rekte Neniu amelo Gazetaro .

Legi la aŭtora aliaj liberaj Python libroj:

Laborante kun Excel Spreadsheets

Excel estas populara kaj potenca spreadsheet apliko por Windows. La openpyxl modulo permesas via Python programoj por legi kaj modifi Excel kalkultabelo dosierojn. Ekzemple, vi povus havi la enuiga tasko de kopiado iuj datumoj de unu folio kaj alglui ĝin en alia. Aŭ eble vi devas iri tra miloj da vicoj kaj elpikis nur manpleno el ili fari malgrandajn redaktetojn bazita sur kelkaj kriterioj. Aŭ eble vi devas rigardi tra centoj de kalkultabeloj de fako buĝetoj, serĉante iu kiu estas en la ruĝa. Tiuj estas ĝuste la speco de enuiga, senhonta kalkultabelo taskoj ke Python povas fari por vi.

Kvankam Excel estas proprieta programaro de Microsoft, estas liberaj alternativoj kiuj kuras sur Vindozo, OS X, kaj Linukso. Ambaŭ LibreOffice Calc kaj OpenOffice Calc laboro kun Excel la .xlsx dosierformato por kalkultabeloj, kion signifas la openpyxl modulo povas labori sur kalkultabelojn el tiuj aplikoj ankaŭ. Vi povas elŝuti la programaron el https://www.libreoffice.org/ kaj http://www.openoffice.org/ , respektive. Eĉ se vi jam havas Excel instalita sur via komputilo, Vi povas trovi ĉi tiujn programojn facila uzi. La ekrankopioj en tiu ĉapitro, tamen, estas ĉiu el Excel 2010 en Windows 7.

excel Dokumentoj

Unue, ni iru super iu baza difinoj: An Excel kalkultabelo dokumenton nomiĝas workbook. Sola workbook estas savita en dosiero kun la .xlsx etendo. Ĉiu workbook povas enhavi multoblajn foliojn (ankaŭ nomita laborfolioj). La folio la uzanto nuntempe vidanta (aŭ lasta vidita antaŭ fermi Excel) nomas la aktivan tabelon.

Ĉiu folio havas kolumnoj (adresita per literoj ekde A) kaj vicoj (adresita per nombroj ekde 1). Skatolo en aparta kolumno kaj vico estas nomata kiel ĉelo. Ĉiu ĉelo povas enhavi kelkajn aŭ teksto valoro. La krado de ĉeloj kun datumoj konsistigas folio.

Instalante la openpyxl Modulo

Python ne venas kun OpenPyXL, do vi devos instali ĝin. Sekvi la instrukciojn por instali triaj moduloj en Apendico A; la nomo de la modulo estas openpyxl . Testi ĉu ĝi estas instalita korekte, tajpu la sekvajn en la interaga konko:

  >>> Import openpyxl 

Se la modulo estis korekte instalita, ĉi devus produkti neniun erarmesaĝoj. Memoru importi la openpyxl modulo antaux kurante la interaga ŝelo ekzemploj en ĉi tiu ĉapitro, aŭ vi ricevos NameError: name 'openpyxl' is not defined eraro.

Tiu libro kovras versio 2.3.3 de OpenPyXL, sed novaj versioj estas regule eldonita de la OpenPyXL teamo. Ne maltrankviliĝu, se: Novaj versioj devus resti malantaŭen kongrua kun la instrukcioj en ĉi tiu libro faras bonan tempon. Se vi havas pli novan version kaj volas vidi kion pliaj karakterizaĵoj povas esti disponeblaj por vi, vi povas kontroli la plenan dokumentaron por OpenPyXL ĉe http://openpyxl.readthedocs.org/ .

Legante Excel Dokumentoj

La ekzemploj en ĉi tiu ĉapitro uzos kalkultabelo nomita example.xlsx stokitaj en la radika dosierujo. Vi povas aŭ krei la kalkultabelo mem aŭ elŝutu ĝin de http://nostarch.com/automatestuff/ . Figuro 12-1 montras la langetoj por la tri defaŭlta littukojn nomita Sheet1, Sheet2 kaj Sheet3 ke Excel aŭtomate provizas novan ekzercokajeroj. (La nombro de defaŭlta littukojn kreita povas varii inter mastrumaj sistemoj kaj kalkultabelo programoj.)

Figuro 12-1. La langetoj por workbook la folioj estas en la malsupra-maldekstra angulo de Excel.

Folio 1 en la ekzemplo dosiero aspektu Tabelo 12-1 . (Se vi ne elŝuti example.xlsx de la retejo, vi devas eniri la datumojn en la folion mem.)

Tabelo 12-1. La example.xlsx kalkultabelo

	A	B	C
1	4/5/2015 1:34:02 PM	pomoj	73
2	4/5/2015 3:41:23 AM	ĉerizoj	85
3	4/6/2015 12:46:51 PM	piroj	14
4	4/8/2015 8:59:43 AM	oranĝoj	52
5	4/10/2015 2:07:00 AM	pomoj	152
6	4/10/2015 6:10:37 PM	bananoj	23
7	4/10/2015 2:40:46 AM	fragoj	98

Nun ke ni havas nian ekzemplon kalkultabelo, ni vidu kiel ni povas manipuli ĝin kun la openpyxl modulo.

Malfermante Excel Dokumentoj kun OpenPyXL

Kiam vi importis la openpyxl modulo, vi povos uzi la openpyxl.load_workbook() funkcio. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Tipo (WB)
 <Klaso openpyxl.workbook.workbook.Workbook '> 

La openpyxl.load_workbook() funkcio prenas en la dosiernomo kaj resendas valoron de la workbook datumtipo. Ĉi Workbook objekto reprezentas la Excel-dosiero, iom kiel kiel File objekto reprezentas malfermis tekstdosiero.

Memoru ke example.xlsx bezonas esti en la nuna laboranta dosierujo en ordo por vi labori kun ĝi. Vi povas trovi ekstere kion la nuna labordosierujon estas per importanta os kaj uzante os.getcwd() , kaj vi povas ŝanĝi la nunan labordosierujon uzante os.chdir() .

Getting Folioj el la Workbook

Vi povas akiri liston de ĉiuj folio nomoj en la workbook nomante la get_sheet_names() metodo. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Wb.get_sheet_names ()
 [ 'Sheet1', 'Sheet2', 'Sheet3']
 >>> Folio = wb.get_sheet_by_name ( 'Sheet3')
 >>> folio
 <Laborfolio "Sheet3">
 >>> Tipo (folio) <klaso openpyxl.worksheet.worksheet.Worksheet '>
 >>> sheet.title
 'Sheet3'
 >>> AnotherSheet = wb.active
 >>> anotherSheet
 <Laborfolio "Sheet1"> 

Ĉiu folio estas reprezentita de Worksheet objekto, kiun vi povas akiri per pasante la folio nomo ŝnuro al la get_sheet_by_name() workbook metodo. Fine, vi povas legi la active membro variablo de Workbook objekto akiri la workbook la aktivan tabelon. La aktivan tabelon estas la folio kiu estas sur supro kiam la workbook malfermigxas en Excel. Unufoje vi havas la Worksheet objekto, vi povas trovi ĝian nomon de la title atributo.

Getting Ĉeloj de la Folioj

Unufoje vi havas Worksheet objekto, vi povas konsenti Cell objekto de ĝia nomo. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Folio = wb.get_sheet_by_name ( 'Sheet1')
 >>> Folio [ 'Al1']
 <Cell Sheet1.A1>
 >>> Folio [ 'Al1']. Valoron
 datetime.datetime (2015, 4, 5, 13, 34, 2)
 >>> C = folio [ 'B1']
 >>> c.value
 'Apples'
 >>> 'Vico' + str (c.row) + ', Kolumno' + c.column + 'estas' + c.value
 'Linio 1, Kolumno B Pomoj'
 >>> 'Ĉelo' + c.coordinate + 'estas' + c.value
 'Ĉelo B1 estas Apples'
 >>> Folio [ 'C1']. Valoron
 73 

La Cell objekto havas value atributo kiu enhavas, antaŭvideble, la valoro stokita en tiu ĉelo. Cell objektoj ankaŭ havas row , column , kaj coordinate atributojn kiuj havigas lokon informon por la ĉelo.

Tie, alirante la value atributo de nia Cell objekto por ĉelo B1 donas al ni la kordo 'Apples' . La row atributo donas al ni la entjero 1 , la column atributon al ni 'B' , kaj la coordinate atributon al ni 'B1' .

OpenPyXL aŭtomate interpretas la datojn en kolumno A kaj redoni ilin kiel datetime valoroj anstataŭ kordoj. La datetime datumtipo estas klarigita plu en Ĉapitro 16 .

Preciziganta kolumnon per letero povas esti delikata al programo, speciale ĉar post kolumno Z, la kolumnoj komencas uzante du literoj: AA, AB, AC, kaj tiel plu. Kiel alternativo, vi povas ankaŭ preni ĉelon uzante la folio la cell() metodo kaj pasante entjeroj por ĝia row kaj column ŝlosilvorto argumentoj. La unua vico aŭ kolumno entjero estas 1 , ne 0 . Daŭrigi la interaga ŝelo ekzemplo enmetante la sekvaj:

  >>> Sheet.cell (vico = 1, kolumno = 2)
 <Cell Sheet1.B1>
 >>> Sheet.cell (vico = 1, kolumno = 2) .value
 'Apples'
 >>> Por i en gamo (1, 8, 2):
         print (i, sheet.cell (vico = i, kolumno = 2) .value)

 1 Pomoj
 3 Piroj
 5 Pomoj
 7 Fragoj 

Kiel vi povas vidi, uzante la folio la cell() metodo kaj pasante ĝin row=1 kaj column=2 akiras vin en Cell objekto por ĉelo B1 , ĝuste kiel preciziganta sheet['B1'] faris. Poste, uzante la cell() metodo kaj liaj ŝlosilvorto argumentojn, vi povas skribi for buklo presi la valorojn de serio de ĉeloj.

Diru vi deziras iri malsupren kolumno B kaj presi la valoro en ĉiu ĉelo kun nepara vico nombro. Pasante 2 por la range() funkcio estas "paŝo" parametro, vi povas akiri ĉelojn el ĉiu dua vico (en tiu kazo, ĉiuj neparajn vicoj). La for buklo estas i variablo estas pasita por la row ŝlosilvorto argumento por la cell() metodon, dum 2 estas ĉiam pasis por la column ŝlosilvorto argumento. Notu ke la entjero 2 , ne la kordo 'B' , trapasintan.

Vi povas determini la grandecon de la folio kun la Worksheet objekto max_row kaj max_column membro variabloj. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Folio = wb.get_sheet_by_name ( 'Sheet1')
 >>> sheet.max_row
 7
 >>> sheet.max_column
 3 

Notu ke la max_column metodo revenas entjero anstataŭ la leteron kiu aperas en Excel.

Konvertado Inter Kolumno Literoj kaj Nombroj

Konverti el leteroj al nombroj, invitu openpyxl.cell.column_index_from_string() funkcio. Konverti de nombroj al literoj, invitu openpyxl.cell.get_column_letter() funkcio. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> De openpyxl.cell importado get_column_letter, column_index_from_string
 >>> Get_column_letter (1)
 'A'
 >>> Get_column_letter (2)
 'B'
 >>> Get_column_letter (27)
 'AA'
 >>> Get_column_letter (900)
 'AHP'
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Folio = wb.get_sheet_by_name ( 'Sheet1')
 >>> Get_column_letter (sheet.max_column)
 'C'
 >>> Column_index_from_string ( 'A')
 1
 >>> Column_index_from_string ( 'AA')
 27 

Post vi importi tiujn du funkciojn de la openpyxl.cell modulo, vi povas voki get_column_letter() kaj fordoni entjero kiel 27 elkompreni kion la letero nomo de la 27-a kolumno estas. La funkcio column_index_string() faras la dorsflanko: Vi transpasxas la letero nomo de kolumno, kaj ĝi rakontas al vi kion nombro tiu kolumno estas. Vi ne bezonas havi workbook ŝarĝita uzi tiujn funkciojn. Se vi volas, vi povas ŝarĝi workbook, akiri Worksheet objekto, kaj voki Worksheet objekto metodo kiel max_column akiri entjero. Tiam, vi povas pasi ke entjero al get_column_letter() .

Getting Vicoj kaj kolumnoj de la Folioj

Vi povas tranĉi Worksheet objektoj akiri ĉiujn Cell objektoj en vico, kolumno aŭ rektangulan areon de la kalkultabelo. Tiam vi povas buklo super ĉiuj ĉeloj en la tranĉaĵo. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
    >>> WB = openpyxl.load_workbook ( 'example.xlsx')
    >>> Folio = wb.get_sheet_by_name ( 'Sheet1')
    >>> Opo (folio [ 'Al1': 'C3'])
    ((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>), (<Cell Sheet1.A2>,
    <Cell Sheet1.B2>, <Cell Sheet1.C2>), (<Cell Sheet1.A3>, <Cell Sheet1.B3>,
    <Cell Sheet1.C3>))
 ❶ >>> por rowOfCellObjects en folio [ 'Al1': 'C3']:
 ❷ por cellObj en rowOfCellObjects:
                print (cellObj.coordinate, cellObj.value)
            print ( '--- FINO DE VICO ---')
    A1 2015-04-05 13:34:02
    B1 Pomoj
    C1 73
    --- FINO DE VICO ---
    A2 2015-04-05 03:41:23
    B2 ĉerizoj
    C2 85
    --- FINO DE VICO ---
    A3 2015-04-06 12:46:51
    B3 Piroj
    C3 14
    --- FINO DE VICO --- 

Tie, ni specifas, ke ni deziras la Cell objektoj en la rektangula spaco de A1 ĝis C3, kaj ni preni Generator objekto enhavanta la Cell objektoj en tiu spaco. Helpi nin visualizar ĉi Generator objekto, ni povas uzi tuple() sur ĝi por montri lian Cell objektoj en opo.

Tiu opo enhavas tri opoj: unu por ĉiu vico, de la supro de la dezirata areo malsupre. Ĉiu de ĉi tiuj tri internan opoj enhavas la Cell objektoj en unu vico de nia dezirata areo, de la plej maldekstra ĉelo dekstre. Do ĝenerale, niaj tranĉaĵo de la folio enhavas ĉiujn Cell objektoj en la spaco de A1 ĝis C3, komencante de la supro-maldekstra ĉelo kaj finante per la malsupra-dekstra ĉelo.

Presi la valorojn de ĉiu ĉelo en la areo, ni uzos du for maŝojn. La ekstera for buklo iras super ĉiu vico en la tranĉaĵo ❶. Tiam, por ĉiu vico, la nestitaj for buklo iras tra ĉiu ĉelo en tiu vico ❷.

Aliri la valoroj de ĉeloj en aparta vico aŭ kolumno, vi povas ankaŭ uzi Worksheet objekto rows kaj columns atribui. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Folio = wb.active
 >>> Sheet.columns [1]
 (<Cell Sheet1.B1>, <Cell Sheet1.B2>, <Cell Sheet1.B3>, <Cell Sheet1.B4>,
 <Cell Sheet1.B5>, <Cell Sheet1.B6>, <Cell Sheet1.B7>)
 >>> Por cellObj en sheet.columns [1]:
         print (cellObj.value)

 pomoj
 ĉerizoj
 piroj
 oranĝoj
 pomoj
 bananoj
 fragoj 

Uzante la rows atribui iun Worksheet objekto donos vin opo de opoj. Ĉiu de ĉi tiuj internaj opoj reprezentas vicon, kaj enhavas la Cell objektoj en tiu vico. La columns atribui ankaŭ donas vin opo de opoj, kun ĉiu de la interna opoj enhavanta la Cell objektoj en specifa kolumno. Por example.xlsx, ĉar ekzistas 7 vicoj kaj 3 kolumnoj, rows al ni opo de 7 opoj (ĉiu enhavante 3 Cell objektoj), kaj columns donas ni opo de 3 opoj (ĉiu enhavante 7 Cell objektoj).

Aliri unu aparta opo, vi povas referi al ĝi per ĝia indekso en la pli granda opo. Ekzemple, por ricevi la opo kiuj reprezentas kolumno B, vi uzas sheet.columns[1] . Akiri la opo enhavanta la Cell objektoj en kolumno A, vi uzus sheet.columns[0] . Unufoje vi havas opo reprezentante unu vico aŭ kolumno, vi povas buklo tra lia Cell celoj kaj presi iliajn valorojn.

Workbooks, Folioj, Ĉeloj

Kiel rapida revizio, jen resumo de ĉiuj funkcioj, metodoj kaj datumtipoj implikita en leganta ĉelo el kalkultabelo dosiero:

1. Importi la openpyxl modulo.
2. Voku la openpyxl.load_workbook() funkcio.
3. Akiri Workbook objekto.
4. Legi la active membro variablo aŭ nomi la get_sheet_by_name() workbook metodo.
5. Akiri Worksheet objekto.
6. Uzu indeksado aŭ la cell() folio metodo kun row kaj column ŝlosilvorto argumentoj.
7. Akiri Cell objekto.
8. Legi la Cell objekto value atributo.

Projekto: Reading Datumoj de kalkultabelo

Diru vi havas kalkultabelo de datumoj de la 2010 Usona Censo kaj vi havas la enuiga tasko de iranta tra ĝia miloj da vicoj kalkuli ambaŭ la tuta loĝantaro kaj la nombro de censo terpecojn por ĉiu graflando. (Censo terpeco estas simple geografia areo difinita por la celoj de la censo.) Ĉiu linio reprezentas sola censo urinarias. Ni nomas la kalkultabelo dosiero censuspopdata.xlsx, kaj vi povas elŝuti ĝin el http://nostarch.com/automatestuff/ . Ĝian enhavon aspekti Figuro 12-2 .

Figuro 12-2. La censuspopdata.xlsx kalkultabelo

Kvankam Excel povas kalkuli la sumon de pluraj elektitaj ĉeloj, oni kredus ankoraŭ devi elekti la ĉeloj por ĉiu de la 3.000-plus graflandoj. Eĉ se ĝi prenas nur kelkaj sekundoj por kalkuli graflando populacio mane, ĉi prenus horojn fari por la tuta kalkultabelo.

En tiu projekto, vi skribas skripton kiu povas legi de la censo kalkultabelo dosiero kaj kalkuli statistikojn por ĉiu graflando en demando de duaj.

Jen kion via programo faras:

• Legas la datumojn de la Excel kalkultabelo.
• Rakontas la numeron de censo terpecojn en ĉiu graflando.
• Rakontas la totala loĝantaro de ĉiu distrikto.
• Presas la rezultojn.

Tio signifas via kodo devos fari la sekvan:

• Malfermita kaj legi la ĉeloj de Excel dokumenton kun la openpyxl modulo.
• Kalkuli ĉiujn terpeco kaj loĝantaro datumoj kaj stoki ĝin en datumstrukturo.
• Skribi la datumstrukturo por teksta dosiero kun la .py etendo uzante la pprint modulo.

Paŝo 1: Legu la kalkultabelo Datumoj

Ekzistas nur unu folio en la censuspopdata.xlsx kalkultabelo, nomis 'Population by Census Tract' , kaj ĉiu vico tenas la datumojn por sola censo urinarias. La kolumnoj estas la tracto nombro (A), la stato mallongigo (B), la graflando nomo (C), kaj la loĝantaro de la tracto (D).

Malfermi novan dosieron redaktanto fenestro kaj eniri la sekvan kodon. Konservu la dosieron kiel readCensusExcel.py.

  #!  python3
    # ReadCensusExcel.py - Tabulates populacio kaj nombro de censo terpecojn por
    # Ĉiu graflando.

 ❶ importado openpyxl, pprint
    print ( 'Opening workbook ...')
 ❷ WB = openpyxl.load_workbook ( 'censuspopdata.xlsx')
 ❸ folio = wb.get_sheet_by_name ( 'Loĝantaro por Census Tract')
    countyData = {}

    # TODO: Enskribu countyData kun ĉiu graflando la loĝantaro kaj terpecojn.
    print ( 'Reading vicoj ...')
 ❹ por vico en gamo (2, sheet.max_row + 1):
        # Ĉiu linio en la tabelo havas datumojn por unu censo urinarias.
        ŝtato = folio [ 'B' + str (vico)]. valoron
        graflando = folio [ 'C' + str (vico)]. valoron
        pop = folio [ 'D' + str (vico)]. valoron

    # TODO: Malfermu nova tekstdosiero skribi la enhavon de countyData al ĝi. 

Tiu kodo importas la openpyxl modulo, tiel kiel la pprint modulo vi uzos por presi la finan graflando datumoj ❶. Tiam malfermas la censuspopdata.xlsx dosiero ❷, ricevas la folio kun la censo datumoj ❸ kaj komencas ripetanta super liaj vicoj ❹.

Rimarku ke vi ankaŭ kreis variablon nomita countyData , kiu enhavos la loĝantaroj kaj kelkaj terpecoj vi kalkulas por ĉiu graflando. Antaŭ vi povas stoki ion en ĝi, Tamen, Vi devus determini precize kiom vi strukturi la datumoj ene ĝi.

Paŝo 2: popoli la Datumoj Strukturo

La datumstrukturo stokita en countyData Estos vortaro kun stato mallongigoj kiel ĝia klavoj. Ĉiu ŝtato mallongigo estos mapo por alia vortaro, kies ŝlosiloj estas kordoj de la graflando nomoj en tiu stato. Ĉiu graflando nomon volas siavice mapo al vortaro kun nur du klavoj, 'tracts' kaj 'pop' . Tiuj klavoj mapi la nombro de censo terpecojn kaj loĝantaro por la graflando. Ekzemple, la vortaro aspektos simila al tiu:

  { 'AK': { 'Aleutians East': { 'pop': 3141, 'terpecojn' 1},
         'Aleutians West': { 'pop': 5561, 'terpecojn': 2},
         'Anchorage': { 'pop': 291826: terpecojn '55},
         'Bethel': { 'pop': 17013, 'terpecojn': 3},
         'Bristol Bay': { 'pop': 997: terpecojn '1},
         - Snip - 

Se la antaŭa vortaro estis stokitaj en countyData , jenaj esprimoj taksus tiel:

  >>> CountyData [ 'AK'] [ 'Anchorage'] [ 'popo']
 291826
 >>> CountyData [ 'AK'] [ 'Anchorage'] [ 'terpecojn']
 55 

Pli ĝenerale, la countyData vortaro de klavoj aspektos tiel ĉi:

  countyData [stato abbrev] [graflando] [ 'terpecojn']
 countyData [stato abbrev] [graflando] [ 'popo'] 

Nun ke vi scias kiel countyData estos strukturita, vi povas skribi la kodon kiu plenigos ĝin per la graflando datumoj. Aldonu la sekvan kodon al la fundo de via programo:

  #!  python 3
    # ReadCensusExcel.py - Tabulates populacio kaj nombro de censo terpecojn por
    # Ĉiu graflando.

    - Snip -
    por vico en gamo (2, sheet.max_row + 1):
        # Ĉiu linio en la tabelo havas datumojn por unu censo urinarias.
        ŝtato = folio [ 'B' + str (vico)]. valoron
        graflando = folio [ 'C' + str (vico)]. valoron
        pop = folio [ 'D' + str (vico)]. valoron

        # Certiĝu la ŝlosilon por tiu stato ekzistas.
 ❶ countyData.setdefault (stato, {})
        # Certiĝu la ŝlosilon por tiu distrikto en tiu stato ekzistas.
 ❷ countyData [stato] .setdefault (graflando, { 'terpecojn' 0, 'pop': 0})

        # Ĉiu linio reprezentas censo urinarias, tiel pliigo de unu.
 ❸ countyData [stato] [graflando] [ 'terpecojn'] + = 1
        # Aldonu la graflando popo de la popo en tiu censo urinarias.
 ❹ countyData [stato] [graflando] [ 'popo'] + = int (popo)

    # TODO: Malfermu nova tekstdosiero skribi la enhavon de countyData al ĝi. 

La lastaj du linioj de kodo fari la fakta kalkulo laboron, pliigante la valoron por tracts ❸ kaj pliiganta la valoron por pop ❹ por la aktuala graflando sur ĉiu ripeto de la for buklo.

La aliaj poŝtkodo estas tie ĉar vi ne povas aldoni graflando vortaro kiel la valoro por ŝtato mallongigo ŝlosilo ĝis la ŝlosilo mem ekzistas en countyData . (Tio estas, countyData['AK']['Anchorage']['tracts'] += 1 kaŭzos eraro se la 'AK' klavo ne ekzistanta.) Por certigi la staton mallongigo ŝlosilo ekzistas en via datumstrukturo, vi devas alvoki la setdefault() metodo fiksi valoron se oni ne jam ekzistas por state ❶.

Tiel la countyData vortaro bezonas vortaron kiel la valoro por ĉiu stato mallongigo ŝlosilo, ĉiu de tiuj vortaroj bezonos lian propran vortaro kiel la valoro por ĉiu graflando ŝlosilo ❷. Kaj ĉiu el tiuj vortaroj siavice devos klavoj 'tracts' kaj 'pop' kiuj komencas kun la entjera valoro 0 . (Se vi iam perdis trakon de la vortaro strukturo, retrorigardas al la ekzemplo vortaro ĉe la komenco de ĉi tiu sekcio.)

Ekde setdefault() faros nenion, se la ŝlosilo jam ekzistas, vi povas nomi ĝin sur ĉiu ripeto de la for buklo sen problemo.

Paŝo 3: Skribu la rezultoj al Dosiero

Post la for buklo finis, la countyData vortaro enhavos tuta de la loĝantaro kaj tracto informo tajpas de graflando kaj stato. Ĉe tiu punkto, vi povus plani pli kodo skribi tion al tekstdosiero aŭ alia Excel kalkultabelo. Nuntempe, ni nur uzas la pprint.pformat() funkcion por skribi la countyData vortaro valoron kiel amasa ŝnuro al dosiero nomita census2010.py. Aldonu la sekvan kodon al la fundo de via programo (certigi teni ĝin unindented tiel ke ĝi restas ekster la for buklo):

  #!  python 3
 # ReadCensusExcel.py - Tabulates populacio kaj nombro de censo terpecojn por
 # Ĉiu county.get_active_sheet
 - Snip -

 por vico en gamo (2, sheet.max_row + 1):
 - Snip -

 # Malfermu nova tekstdosiero skribi la enhavon de countyData al ĝi.
 print ( 'Skribo rezultoj ...')
 resultFile = malfermita ( 'census2010.py', 'w')
 resultFile.write ( 'allData =' + pprint.pformat (countyData))
 resultFile.close ()
 print ( 'Farita.') 

La pprint.pformat() funkcio produktas ĉenon kiu mem estas formatita kiel valida Python kodo. Per elirigi gxin al tekstdosiero nomita census2010.py, vi generis Python programo de via Python programo! Tio povas ŝajni komplika, sed la avantaĝo estas ke vi povas nun importi census2010.py ĝuste kiel ajna alia Python modulo. En la interaga ŝelo, ŝanĝi la nunan laboras dosierujo la dosierujo kun via lastatempe kreita census2010.py dosiero (sur mia tekkomputilo, tio estas C: \ Python34), kaj poste importi ĝin:

  >>> Import os
 >>> Os.chdir ( 'C: \\ Python34')
 >>> Import census2010
 >>> Census2010.allData [ 'AK'] [ 'Anchorage']
 { 'Pop': 291826: terpecojn '55}
 >>> AnchoragePop = census2010.allData [ 'AK'] [ 'Anchorage'] [ 'popo']
 >>> Print ( 'La 2010 populacio de Anchorage estis' + str (anchoragePop))
 La 2010 populacio de Anchorage estis 291826 

La readCensusExcel.py programo estis throwaway kodo Unufoje vi liajn rezultojn savis al census2010.py, vi ne bezonas kuri la programon denove. Kiam ajn vi bezonos la graflando datumoj, vi povas simple kuri import census2010 .

Kalkulanta tiu datumo mane havus prenita horoj; ĉi programo faris en kelkaj sekundoj. Uzante OpenPyXL, vi havos neniun problemon ĉerpi informon kiu savas al Excel kalkultabelo kaj plenumante kalkuloj sur ĝi. Vi povas elŝuti la kompletan programon de http://nostarch.com/automatestuff/ .

Ideoj por Similaj Programoj

Multaj entreprenoj kaj oficejoj uzi Excel enteni diversajn tipojn de datumoj, kaj ĝi ne estas neofta por kalkultabeloj fariĝi granda kaj maloportunaj. Ajna programo kiu parses Excel spreadsheet havas similan strukturon: Ĝi ŝarĝas la kalkultabelo dosiero, Preps iuj variabloj aŭ datumstrukturoj, kaj tiam cikloj tra ĉiu el la vicoj en la kalkultabelo. Tia programo povus fari la sekvajn:

• Kompari datumojn trans multoblaj vicoj en folio de ŝtono.
• Malfermi plurajn Excel dosierojn kaj kompari datumojn inter kalkultabeloj.
• Kontrolu ĉu spreadsheet havas malplenan vicoj aŭ nevalida datumoj en ajna ĉeloj kaj alarmi la uzanton se ĝi faras.
• Legi datumojn de kalkultabelo kaj uzi ĝin kiel la enigo por via Python programoj.

Skribi Excel Dokumentoj

OpenPyXL ankaŭ disponigas manierojn por skribi datumojn, signifante ke viaj programoj povas krei kaj redakti kalkultabelo dosierojn. Kun Python, ĝi estas simpla por krei kalkultabelojn kun miloj de vicoj de datumoj.

Kreado kaj Savanta Excel Dokumentoj

Voku la openpyxl.Workbook() funkcio por krei novan, malplenan Workbook objekto. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.Workbook ()
 >>> Wb.get_sheet_names ()
 [ 'Folio']
 >>> Folio = wb.active
 >>> sheet.title
 'Folio'
 >>> Sheet.title = 'spamo Bacon Ovoj Folio'
 >>> Wb.get_sheet_names ()
 [ 'Spamo Bacon Ovoj Folio'] 

La workbook dividos kun sola folio nomita Folio. Vi povas ŝanĝi la nomon de la folio stokante novan ĉenon en ĝia title atributo.

Ajna tempo vi modifi la Workbook objekto aŭ ĝia littukojn kaj ĉeloj, la kalkultabelo dosiero ne konserviĝos ĝis kiam vi vokas la save() workbook metodo. Eniri la sekva en la interaga ŝelo (kun example.xlsx en la nuna labordosierujon):

  >>> Import openpyxl
 >>> WB = openpyxl.load_workbook ( 'example.xlsx')
 >>> Folio = wb.active
 >>> Sheet.title = 'spamo Spamo Spamo'
 >>> Wb.save ( 'example_copy.xlsx') 

Tie, ni ŝanĝas la nomo de nia folio. Savi niajn ŝanĝojn, ni pasas dosiernomo kiel linio al la save() metodo. Pasante malsama dosiernomo ol la originalo, kiel ekzemple 'example_copy.xlsx' , savas la ŝanĝoj al la teksto de la kalkultabelo.

Kiam vi redaktas spreadsheet vi ŝarĝita de dosiero, vi devus ĉiam konservi la nova, eldonita kalkultabelo al malsama dosiernomo ol la originalo. Tiamaniere, vi ankoraŭ havas la originalajn kalkultabelo dosiero labori kun en kazo cimon en via kodo kaŭzis la novan, savis dosiero havi malĝustan aŭ koruptaj datumoj.

Kreado kaj Forigado Folioj

Folioj povas esti aldonita kaj forigita el workbook la create_sheet() kaj remove_sheet() metodoj. Eniri la sekva en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.Workbook ()
 >>> Wb.get_sheet_names ()
 [ 'Folio']
 >>> Wb.create_sheet ()
 <Laborfolio "Sheet1">
 >>> Wb.get_sheet_names ()
 [ 'Folio', 'Sheet1']
 >>> Wb.create_sheet (indekso = 0, titolo = 'Unua Folio')
 <Laborfolio "Unua Folio">
 >>> Wb.get_sheet_names ()
 [ 'Unua Folio', 'Folio', 'Sheet1']
 >>> Wb.create_sheet (indekso = 2, titolo = Mezgrada Folio ')
 <Laborfolio "Meza Folio">
 >>> Wb.get_sheet_names ()
 [ 'Unua Folio', 'Folio', 'Middle Folio', 'Sheet1'] 

La create_sheet() metodo revenas nova Worksheet objekto nomita Sheet X , kiu defaŭlte estas metita esti la lasta folio de la workbook. Laŭvole, la indekso kaj nomo de la nova folio povas esti precizigita per la index kaj title ŝlosilvorto argumentoj.

Daŭrigi la antaŭan ekzemplon enmetante la sekvaj:

  >>> Wb.get_sheet_names ()
 [ 'Unua Folio', 'Folio', 'Middle Folio', 'Sheet1']
 >>> Wb.remove_sheet (wb.get_sheet_by_name (Mezgrada Folio '))
 >>> Wb.remove_sheet (wb.get_sheet_by_name ( 'Sheet1'))
 >>> Wb.get_sheet_names ()
 [ 'Unua Folio', 'Folio'] 

La remove_sheet() metodo prenas Worksheet objekto, ne ĉenon de la folio nomon, kiel ĝia argumento. Se vi scias nur la nomon de folio vi volas forigi, voki get_sheet_by_name() migru lia reveno valoro en remove_sheet() .

Memori por alvoki save() metodo por savi la ŝanĝojn post aldono littukojn aŭ forigado folioj de la workbook.

Skribi Valoroj por Ĉeloj

Skribi valorojn al ĉeloj estas tre kiel skribanta valorojn al ŝlosilojn en vortaro. Eniri ĉi en la interaga konko:

  >>> Import openpyxl
 >>> WB = openpyxl.Workbook ()
 >>> Folio = wb.get_sheet_by_name ( 'Folio')
 >>> Folio [ 'Al1'] = 'Saluton mondo!'
 >>> Folio [ 'Al1']. Valoron
 'Saluton mondo!' 

Se vi havas la ĉelo koordinato kiel linio, vi povas uzi ĝin nur kiel vortaro ŝlosilo sur la Worksheet objekto specifi kiu ĉelo skribi al.

Projekto: Ĝisdatigi kalkultabelo

En tiu projekto, vi skribi programon por ĝisdatigi ĉelojn en kalkultabelo de produktoj vendoj. Via programo aspektas tra la kalkultabelo, trovi specifajn specojn de produktoj, kaj ĝisdatigi iliajn prezojn. Elŝuti ĉi kalkultabelo el http://nostarch.com/automatestuff/ . Figuro 12-3 montras kion la kalkultabelo aspektas.

Figuro 12-3. A spreadsheet de produktoj vendoj

Ĉiu linio reprezentas individuan vendo. La kolumnoj estas la tipo de produktoj vendis (A), la kosto po funto de tiu produkto (B), la nombro da funtoj vendita (C), kaj la totala enspezo de la vendo (D). La TUTA kolumno estas agordita la Excel formulo = ROUND (B3 * C3, 2), kiu multiplikas la kosto po funto de la nombro da funtoj vendita kaj preterpasas la rezulton al la plej proksima cendo. Kun tiu formulo, la ĉeloj en la TUTA kolumno aŭtomate ĝisdatigi se estas ŝanĝo en kolumno B aŭ C.

Nun imagu ke la prezoj de ajlo, celerio, kaj citronoj estis eniris malĝuste, lasante vin kun la enuiga tasko de iranta tra miloj da vicoj en tiu kalkultabelo ĝisdatigi la kosto po funto por ajna ajlo, celerio, kaj citrono vicoj. Vi ne povas fari simplan trovi-kaj-anstataŭigi por la prezo ĉar povus esti aliaj aĵoj kun la sama prezo kiun vi ne volas erare "korekta." Dum miloj da vicoj, ĉi prenus horojn fari mane . Sed vi povas skribi programon kiu povas atingi tion en sekundoj.

Via programo faras la sekvajn:

• Masxojn sur ĉiuj vicoj.
• Se la vico estas por ajlo, celerio, aŭ citronoj, ŝanĝas la prezon.

Tio signifas via kodo devos fari la sekvan:

• Malfermi la kalkultabelo dosiero.
• Por ĉiu vico, kontrolu ĉu la valoro en kolumno A estas Celery , Garlic , aŭ Lemon .
• Tiukaze ĝisdatigi la prezo en kolumno B.
• Savi la kalkultabelo al nova dosiero (por ke vi ne perdas la malnovan kalkultabelo, ĉiaokaze).

Paŝo 1: Ŝanĝu Supren datumstrukturo per Ĝisdatigu Informoj

La prezoj kiujn vi devas ĝisdatigi estas jenaj:

celerio	1.19
ajlo	3.07
citrono	1.27

Vi povus skribi kodo kiel tiu:

  se produceName == 'celerio':
     cellObj = 1.19
 se produceName == 'ajlo':
     cellObj = 3.07
 se produceName == 'citrono':
     cellObj = 1,27 

Havante la produktoj kaj ĝisdatigita prezo datumoj hardcoded kiel ĉi estas iom inelegant. Se vi bezonas ĝisdatigi la kalkultabelo denove kun malsamaj prezoj aŭ malsamajn produktojn, vi devus ŝanĝi multon de la kodo. Ĉiufoje kiam vi ŝanĝas kodon, vi riskus enkonduki cimojn.

Pli fleksebla solvo estas stoki la korektis prezo informon en vortaro kaj skribi vian kodon por uzi tiun datumstrukturo. En nova dosiero redaktanto fenestro, tajpu la sekvan kodon:

  #!  python3
 # UpdateProduce.py - Korektas kostoj en produktoj vendoj kalkultabelo.

 importado openpyxl

 WB = openpyxl.load_workbook ( 'produceSales.xlsx')
 folio = wb.get_sheet_by_name ( 'Folio')

 # La produktajxoj tipoj kaj iliaj ĝisdatigita prezoj
 PRICE_UPDATES = { 'ajlo': 3,07,
                  'Celerio': 1,19,
                  'Citrono': 1,27}

 # TODO: Loop tra la vicoj kaj ĝisdatigi la prezoj. 

Savi tion kiel updateProduce.py. Se vi bezonas ĝisdatigi la kalkultabelo denove, vi devas ĝisdatigi nur la PRICE_UPDATES vortaro, ne iu alia kodo.

Paŝo 2: Kontrolu Ĉiuj Vicoj kaj Ĝisdatigi Neĝusta Prezoj

La sekva parto de la programo volo buklo tra ĉiuj vicoj en la kalkultabelo. Aldonu la sekvan kodon al la fundo de updateProduce.py:

  #!  python3
    # UpdateProduce.py - Korektas kostoj en produktoj vendoj kalkultabelo.

    - Snip -

    # Loop tra la vicoj kaj ĝisdatigi la prezoj.
 ❶ por rowNum en gamo (2, sheet.max_row): # salti la unua vico
 ❷ produceName = sheet.cell (vico = rowNum, kolumno = 1) .value
 ❸ se produceName en PRICE_UPDATES:
            sheet.cell (vico = rowNum, kolumno = 2) .value = PRICE_UPDATES [produceName]

 ❹ wb.save ( 'updatedProduceSales.xlsx') 

Ni buklo tra la vicoj ekde vico 2, ekde vico 1 estas simple la kaplinio ❶. La ĉelo en kolumno 1 (tio estas, kolumno A) estos stokitaj en la variablo produceName ❷. Se produceName ekzistas kiel ŝlosilo en la PRICE_UPDATES vortaro ❸, tiam vi scias ĉi estas vico ke sendube lia prezo ĝustigita. La ĝusta prezo estos en PRICE_UPDATES[produceName] .

Rimarku kiel pura uzante PRICE_UPDATESfaras la kodon. Nur unu ifdeklaron, anstataŭ kodo kiel if produceName == 'Garlic':estas necesa por ĉiu tipo de produktoj por ĝisdatigi. Kaj kiam la kodo uzas la PRICE_UPDATESvortaro anstataŭ hardcoding produktajxojn nomoj kaj ĝisdatigita kostoj en la forbuklo, vi modifas nur la PRICE_UPDATESvortaron kaj ne la kodon se la produkto vendoj kalkultabelo bezonas kromajn ŝanĝojn.

Ili trapasis la tutan kalkultabelo kaj farado ŝanĝoj, la kodo savas la Workbookobjekto al updatedProduceSales.xlsx ❹. Ne anstataŭigi la malnovan kalkultabelo ĉiaokaze ekzistas cimo en via programo kaj la ĝisdatigita kalkultabelo estas erara. Post kontroli ke la ĝisdatigita kalkultabelo aspektas ĝuste, vi povas forviŝi la malnovan kalkultabelo.

Vi povas elŝuti la kompletan fontkodon por tiu programo de http://nostarch.com/automatestuff/ .

Ideoj por Similaj Programoj

Ĉar multaj oficistoj uzi Excel Spreadsheets tutan tempon, programo kiu povas aŭtomate redakti kaj skribi Excel dosierojn povus esti vere utila. Tia programo povus fari la sekvajn:

• Legi datumojn de unu folio kaj skribi ĝin al partoj de aliaj folioj de ŝtono.
• Legi datumojn de retejoj, tekstaj dosieroj, aŭ la tondujo kaj skribi ĝin al kalkultabelo.
• Aŭtomate "purigi" datumojn en kalkultabeloj. Ekzemple, ĝi povus uzi regulajn esprimojn por legi multnombraj formatoj de telefonnumerojn kaj redakti ilin sola, norma formato.

Lancxi la Tiparo Stilo de Ĉeloj

Stiligante certaj ĉeloj, vicoj aŭ kolumnoj povas helpi vin reliefigi gravaj areoj en via kalkultabelo. Produktajxojn kalkultabelo, ekzemple, via programo povus apliki aŭdaca teksto al la terpomo, ajlo, kaj Pastinako vicoj. Aŭ eble vi volas italicize ĉiu vico kun kosto po funto granda ol $ 5. Stilo partoj de granda ŝtono mane estus teda, sed viaj programoj povas fari ĝin tuj.

Personecigi tiparo stiloj en ĉeloj, grava, importi la Font()funkcion de la openpyxl.stylesmodulo.

 el openpyxl.styles importado Tiparo 

Tio ebligas vin tajpi Font()anstataŭ openpyxl.styles.Font(). (Vidu Importante Moduloj revizii tiun stilon de importdeklaro.)

Jen ekzemplo kiu kreas novajn workbook kaj fiksas ĉelon A1 havi 24-punkta, kursivigitaj tiparo. Eniri la sekva en la interaga konko:

>>> Import openpyxl 
   >>> de openpyxl.styles importi Tiparo 
   >>> WB = openpyxl.Workbook () 
   >>> folio = wb.get_sheet_by_name ( 'Folio') 
❶ >>> italic24Font = Tiparo (grandeco = 24, kursivan = Vera) 
❷ >>> folio [ 'Al1']. tiparo = italic24Font 
   >>> folio [ 'Al1'] = 'Saluton mondo!' 
   >>> wb.save ( 'styled.xlsx') 

Ĉelo stilo povas agordi atribuante la Fontobjekton al la styleatributo.

En ĉi tiu ekzemplo, Font(size=24, italic=True)resendas Fontobjekton, kiu estas stokita en italic24Font❶. La ŝlosilvorto argumentoj Font(), sizekaj italic, agordi la Fontcelon. Kaj kiam fontObjestas atribuita al la ĉelo fontatributo ❷, cxiuj tiparo stilo informo akiras aplikita al ĉelo A1.

font Objektoj

Agordi tiparon stilo atributoj, sekvinberoj ŝlosilvorto argumentojn por Font(). Tabelo 12-2 montras eblajn ŝlosilvorto argumentojn por la Font()funkcio.

Tabelo 12-2. Temo Argumentoj por Tiparo styleAtributoj

ŝlosilvorto argumento	datumtipo	priskribo
name	ŝnuro	La tiparo, ekz 'Calibri'aŭ'Times New Roman'
size	entjero	La punkto grandeco
bold	bulea	True Pro aŭdaca tiparo
italic	bulea	True Cxar kursiva tiparo

Vi povas voki Font()krei Fontobjekton kaj vendejo tiu Fontobjekto en variablo. Vi tiam preterpasonta al Style(), stoki la rezultanta Styleobjekto en variablo, kaj asignos tiun variablon al Cellobjekto styleatributo. Ekzemple, tiun kodon kreas diversajn tiparo stiloj:

>>> Import openpyxl 
>>> de openpyxl.styles importi Tiparo 
>>> WB = openpyxl.Workbook () 
>>> folio = wb.get_sheet_by_name ( 'Folio')

>>> FontObj1 = Tiparo (nomo = 'Times New Roman', aŭdaca = Vera) 
>>> folio [ 'Al1']. Tiparo = fontObj1 
>>> folio [ 'Al1'] = 'Grasa Times New Roman'

>>> FontObj2 = Tiparo (grandeco = 24, kursivan = Vera) 
>>> folio [ 'B3']. Tiparo = fontObj2 
>>> folio [ 'B3'] = '24 pt Italic '

>>> Wb.save ( 'styles.xlsx') 

Tie, ni stoki Fontcelon fontObj1kaj tiam fiksita la Al1 Cellobjekto fontatributon al fontObj1. Ni ripetas la procezon kun alia Fontobjekto agordi la stilon de dua ĉelo. Post vi kuras tiun kodon, la stiloj de la Al1 kaj B3 ĉeloj en la kalkultabelo estos fiksita por kutimo tiparo stiloj, kiel montrita en Figuro 12-4 .

Figuro 12-4. A spreadsheet kun kutimo tiparo stiloj

Por ĉelo A1, ni starigis la tiparo nomon 'Times New Roman'kaj starigis boldal true, Do nia teksto aperas en aŭdaca Times New Roman. Ni ne specifi grandeco, tiel la openpyxldefaŭlta, 11, estas uzita. En ĉelo B3, nia teksto kursiva, kun grandeco de 24; ni ne specifis tiparon nomo, tial la openpyxldefaŭlta, Calibri, estas uzita.

formuloj

Formuloj, kiuj komencas kun egala signo, povas konfiguri ĉeloj enhavi valorojn kalkulitaj de aliaj ĉeloj. En tiu sekcio, vi uzos la openpyxlmodulon por programmatically aldoni formulojn al ĉeloj, same kiel ĉiu normala valoro. Ekzemple:

>>> Folio [ 'B9'] = '= SUM (B1: B8)' 

Ĉi konservos = SUM (B1: B8) kiel la valoro en ĉelo B9. Tio metas la B9 ĉelo al formulo kiu kalkulas la sumon de la valoroj en ĉeloj B1 al B8. Vi povas vidi tion en ago en Figuro 12-5 .

Figuro 12-5. Ĉelo B9 enhavas la formulon = SUM (B1: B8) , kiu aldonas la ĉeloj B1 al B8.

Formulo enkadriĝas ĝuste kiel ajna alia teksto valoron en ĉelo. Eniri la sekva en la interaga konko:

>>> Import openpyxl 
>>> WB = openpyxl.Workbook () 
>>> folio = wb.active 
>>> folio [ 'Al1'] = 200 
>>> folio [ 'Al2'] = 300 
>>> folio [ 'Al3'] = '= SUM (A1: A2)' 
>>> wb.save ( 'writeFormula.xlsx') 

La ĉeloj en Al1 kaj Al2 havas inklinon al 200 kaj 300, respektive. La valoron en ĉelo Al3 estas agordita formulo kiu resumas la valorojn en Al1 kaj Al2. Kiam la kalkultabelo malfermiĝas en Excel, Al3 montros lian valoron kiel 500.

Excel formuloj proponas nivelon de programabilidad por kalkultabeloj sed povas rapide fariĝis malobeema por komplikaj taskoj. Ekzemple, eĉ se vi estas profunde konas Excel formuloj, estas kapdoloro por provi deĉifri kion = IFERROR (TRIM (IF (LEN (VLOOKUP (F7, Sheet2! $ A $ 1: $ B $ 10000, 2, FALSA)) > 0, anstataŭanto (VLOOKUP (F7, Sheet2! $ A $ 1: $ B $ 10000, 2, FALSA), "", ""), "")), "") reale faras. Python kodo estas multe pli legebla.

Ĝustigante Vicoj kaj Kolumnoj

En Excel, ĝustigante la grandecoj de vicoj kaj kolumnoj estas tiel facila kiel klakante kaj trenante la randoj de vico aŭ kolumno header. Sed se vi bezonos agordi vico aŭ kolumno grandeco bazita sur liaj ĉeloj 'enhavo aŭ se vi volas agordi grandecojn en granda nombro de kalkultabelo dosierojn, ĝi estos multe pli rapide skribi Python programo por fari ĝin.

Vicoj kaj kolumnoj povas esti kaŝitaj el la okuloj. Aŭ ili povas esti "frostigita" en loko tiel ke ili estas ĉiam videbla sur la ekrano kaj aperas sur ĉiu paĝo, kiam la kalkultabelo estas presita (kiu estas oportuna por titolaj).

Opcio Vico Alteco kaj Kolumno Larĝo

Worksheetceloj havas row_dimensionskaj column_dimensionsatributoj kiuj kontrolas vicon altecoj kaj kolumno larĝaj. Eniri ĉi en la interaga konko:

>>> Import openpyxl 
>>> WB = openpyxl.Workbook () 
>>> folio = wb.active 
>>> folio [ 'Al1'] = 'Tall vico' 
>>> folio [ 'B2'] = 'Larĝa kolumno ' 
>>> sheet.row_dimensions [1] .height = 70 
>>> sheet.column_dimensions [' B ']. larĝeco = 20 
>>> wb.save (' dimensions.xlsx ') 

Al folio de row_dimensionskaj column_dimensionsestas vortaro-similaj valoroj; row_dimensionsenhavas RowDimensionobjektojn kaj column_dimensionsenhavas ColumnDimensionobjektojn. En row_dimensions, vi povas aliri unu el la objektoj uzante la nombro de la vico (en tiu kazo, 1 aŭ 2). En column_dimensions, vi povas aliri unu el la objektoj uzante la leteron de la kolumno (en tiu kazo, A aŭ B).

La dimensions.xlsx kalkultabelo aspektas kiel Figuro 12-6 .

Figuro 12-6. Vico 1 kaj kolumno B aron al granda altaĵoj kaj larĝoj

Unufoje vi havas la RowDimensioncelon, vi povas agordi la alton. Unufoje vi havas la ColumnDimensioncelon, vi povas agordi lia larĝa. La vico alteco povas esti aro al entjero aŭ kaleŝego valoro inter 0kaj 409. Tiu valoro reprezentas la alteco mezurita en punktoj , kie unu punkto egalas 1/72 de colo. La defaŭlta vico alteco estas 12,75. La kolumno larĝa povas esti aro al entjero aŭ kaleŝego valoro inter 0kaj 255. Tiu valoro reprezentas la nombron de karakteroj en la defaŭlta tiparo (11 punkto) kiu povas esti montrita en la ĉelo. La defaŭlta kolumno larĝa estas 8,43 karakteroj. Kolumnoj kun larĝaj de 0aŭ vicoj kun altecoj de 0estas kaŝitaj de la uzanto.

Kunfandante kaj Unmerging Ĉeloj

A rektangula areo de ĉeloj povas esti kunigitaj en sola ĉelo kun la merge_cells()folio metodo. Eniri la sekva en la interaga konko:

>>> Import openpyxl 
>>> WB = openpyxl.Workbook () 
>>> folio = wb.active 
>>> sheet.merge_cells ( 'Al1: D3') 
>>> folio [ 'Al1'] = 'Dekdu ĉeloj kunfandas kune. ' 
>>> sheet.merge_cells (' C5: D5 ') 
>>> folio [' C5 '] =' Du kunfandita ĉeloj. ' 
>>> wb.save (' merged.xlsx ') 

La argumento merge_cells()estas ununura ĉeno de la supro-maldekstra kaj malsupra-dekstra ĉeloj de la rektangula spaco al esti kunfandita: 'A1:D3'kunfandas 12 ĉeloj en sola ĉelo. Agordi la valoron de ĉi tiuj kunfandis ĉelojn, simple starigis la valoro de la supro-maldekstra ĉelo de la kunfandita grupo.

Kiam vi kuras tiun kodon, merged.xlsx similos Figuro 12-7 .

Figuro 12-7. Kunfandis ĉelojn en kalkultabelo

Al Malkunigi ĉeloj, invitu unmerge_cells()folio metodo. Eniri ĉi en la interaga ŝelo.

>>> Import openpyxl 
>>> WB = openpyxl.load_workbook ( 'merged.xlsx') 
>>> folio = wb.active 
>>> sheet.unmerge_cells ( 'Al1: D3') 
>>> sheet.unmerge_cells ( 'C5 : D5 ') 
>>> wb.save (' merged.xlsx ') 

Se vi konservos viajn ŝanĝojn kaj tiam rigardu la kalkultabelo, vi vidos ke la kunfandis ĉelojn iris reen al esti individuaj ĉeloj.

Freeze Panoj

Por kalkultabeloj tro granda por esti montrata samtempe, ĝi estas utila al "frostigi" kelkaj el la supro vicoj aŭ plej maldekstra kolumnoj surekrane. Glaciiĝinta kolumno aŭ vico titolaj, ekzemple, estas ĉiam videblaj al la uzanto kiel ili rulumu tra la kalkultabelo. Tiuj estas konataj kiel frostitan vitroj . En OpenPyXL, ĉiu Worksheetobjekto havas freeze_panesatributon kiu povas esti fiksita al Cellobjekto aŭ ŝnuro de ĉelo de koordinatoj. Notu ke ĉiuj vicoj supre kaj ĉiuj kolonoj de la maldekstra de ĉi tiu ĉelo estos frostigita, sed la vico kaj kolumno de la ĉelo mem ne frostigita.

Por revivigi ĉiuj vitroj, fiksita freeze_panesal Noneaŭ 'A1'. Tabelo 12-3 montras kio vicoj kaj kolumnoj estos frostigita por iuj ekzemple kadretojn freeze_panes.

Tabelo 12-3. Glaciiĝinta Pane Ekzemploj

freeze_panes fikso	Vicoj kaj kolumnoj frostigita
sheet.freeze_panes = 'A2'	vico 1
sheet.freeze_panes = 'B1'	kolumno A
sheet.freeze_panes = 'C1'	Kolumnoj A kaj B
sheet.freeze_panes = 'C2'	Vico 1 kaj kolumnoj A kaj B
sheet.freeze_panes = 'A1' aŭ sheet.freeze_panes = None	Neniu glaciiĝinta vitroj

Certiĝu ke vi havas la produkto vendoj kalkultabelo el http://nostarch.com/automatestuff/ . Tiam eniri la sekva en la interaga konko:

>>> Import openpyxl 
>>> WB = openpyxl.load_workbook ( 'produceSales.xlsx') 
>>> folio = wb.active 
>>> sheet.freeze_panes = 'Al2' 
>>> wb.save ( 'freezeExample.xlsx' ) 

Se vi starigis la freeze_panesatributon por 'A2', vico 1 ĉiam estos videbla, negrave kie la uzanto rulumas en la kalkultabelo. Vi povas vidi tion en Figuro 12-8 .

Figuro 12-8. Kun freeze_panesaro 'A2', vico 1 estas ĉiam videbla kiel la uzanto rulumas malsupren.

furorlisto

OpenPyXL subtenas krei trinkejo, linio, disĵeti, kaj kukaĵo lertaj uzanta la datumoj en folio ĉeloj. Fari diagramo, vi bezonas fari la sekvajn:

1. Krei Referenceobjekton de rektangula selektado de ĉeloj.
2. Krei Seriesobjekton de pasanta en la Referenceobjekto.
3. Krei Chartobjekton.
4. Postglui la Seriesobjekton al la Chartobjekto.
5. Aldonu la Chartobjekton al la Worksheetobjekto, laŭvole preciziganta kiu ĉelo la supro maldekstra angulo de la diagramo devus esti poziciigita ..

La Referenceobjekto postulas iun klarigon. ReferenceObjektoj estas kreitaj per nomante la openpyxl.chart.Reference()funkcio kaj pasi tri argumentojn:

1. La Worksheetobjekto enhavanta via abako datumoj.
2. Al opo de du entjeroj, reprezentante la supro-maldekstra ĉelo de la rektangula selektado de ĉeloj enhavantaj via abako datumoj: La unua entjero en la opo estas la vico, kaj la dua estas la kolumno. Notu ke 1estas la unua vico, ne 0.
3. Al opo de du entjeroj, reprezentante la malsupro-dekstra ĉelo de la rektangula selektado de ĉeloj enhavantaj via abako datumoj: La unua entjero en la opo estas la vico, kaj la dua estas la kolumno.

Figuro 12-9 montras iun specimenon koordinato argumentoj.

Figuro 12-9. De maldekstre dekstren: (1, 1), (10, 1); (3, 2), (6, 4);(5, 3), (5, 3)

Eniri ĉi interaga ŝelon ekzemple krei trinkejo abako kaj aldoni ĝin al la kalkultabelo:

>>> Import openpyxl 
>>> WB = openpyxl.Workbook () 
>>> folio = wb.active 
>>> por i en gamo (1, 11): # krei iujn datumojn en kolumno A 
        folio [ 'A' + str (i)] = i

>>> RefObj = openpyxl.chart.Reference (folio, min_col = 1, min_row = 1, max_col = 1, max_row = 10)

>>> SeriesObj = openpyxl.chart.Series (refObj, titolo = 'Unua serio)

>>> ChartObj = openpyxl.chart.BarChart () 
>>> chartObj.title = Mia Chart ' 
>>> chartObj.append (seriesObj) 
>>> sheet.add_chart (chartObj: C5') 
>>> WB. savi ( 'sampleChart.xlsx') 

Tio produktas kalkultabelo kiu similas Figuro 12-10 .

Figuro 12-10. A spreadsheet kun abako aldonitaj

Ni kreis trinkejo abako nomante openpyxl.chart.BarChart(). Vi povas ankaŭ krei linio lertaj, disjxetu lertaj kaj kukaĵo lertaj nomante openpyxl.chart.LineChart(), openpyxl.chart.ScatterChart()kaj openpyxl.chart.PieChart().

Bedaŭrinde, en la aktuala versio de OpenPyXL (2.3.3), la load_workbook()funkcio ne ŝarĝi lertaj en Excel dosierojn. Eĉ se la Excel dosiero havas lertaj, la ŝarĝita Workbookobjekto ne inkludi ilin. Se vi ŝarĝi Workbookobjekton kaj tuj savi ĝin al la sama .xlsx dosiernomo, vi efike forigi la lertaj de ĝi.

resumo

Ofte la malmola parto de procesante informo ne la prilaborado mem sed simple akiranta la datumoj en la ĝusta formato por via programo. Sed unufoje vi via kalkultabelo ŝarĝita en Pitono, vi povas ĉerpi kaj manipuli liajn datumojn multe pli rapide ol vi povus mane.

Vi povas ankaŭ generi kalkultabeloj kiel eligo de viaj programoj. Do se kolegoj bezonas vian tekstdosiero aŭ PDF da vendoj kontaktoj kopiitaj al kalkultabelo dosiero, vi ne devos tede kopii kaj almeti ĉion en Excel.

Ekipita kun la openpyxlmodulo kaj iuj programado scio, vi trovos prilaborado eĉ la plej grandaj kalkultabelojn peco de kuko.

praktiko Demandoj

Por la sekvaj demandoj: imagu vi havas Workbookcelon en la variablo wb, a Worksheetobjekto en sheet, a Cellobjekto en cell, a Commentobjekto en comm, kaj Imageobjekto en img.

Q:	1. Kion signifas la openpyxl.load_workbook()funkcion reveni?
Q:	2. Kion la get_sheet_names()workbook metodo revenos?
Q:	3. Kiel vi elsxuti la Worksheetcelon por folio nomita 'Sheet1'?
Q:	4. Kiel vi elsxuti la Worksheetcelon por la workbook la aktiva folio?
Q:	5. Kiel vi elsxuti la valoro en la ĉelo C5?
Q:	6. Kiel vi starigis la valoro en la ĉelo C5 al "Hello"?
Q:	7. Kiel vi elsxuti la ĉelo vico kaj kolumno kiel entjeroj?
Q:	8. Kion la max_columnkaj max_rowfolio metodoj reveni, kaj kio estas la datumtipo de tiuj reveno valoroj?
Q:	9. Se vi bezonas akiri la entjeran indekson por kolumno 'M', kio funkcio estus vi bezonas nomi?
Q:	10. Se vi bezonas akiri la kordo nomo por kolumno 14, kio funkcio estus vi bezonas nomi?
Q:	11. Kiel vi povas elsxuti opo de ĉiuj Cellobjektoj de A1 ĝis F1?
Q:	12. Kiel vi ŝpari la workbook la dosiernomo example.xlsx ?
Q:	13. Kiel vi starigis formulo en ĉelo?
Q:	15. Kiel vi starigis la alteco de vico 5 al 100?
Q:	16. Kiel vi kaŝos kolumno C?
Q:	17. Nomo kelkaj trajtoj kiujn OpenPyXL 2.3.3 ne ŝarĝi de kalkultabelo dosiero.
Q:	18. Kio estas frostitan vitron?
Q:	19. Kion kvin funkcioj kaj metodoj vi devas voki krei trinkejo abako?

praktiko Projektoj

Por praktiko, skribi programojn kiuj plenumas la sekvajn taskojn.

Multipliko Tabelo Kreinto

Krei programon multiplicationTable.py kiu prenas numeron N de la komandlinio kaj kreas N × N multipliko tablo en Excel kalkultabelo. Ekzemple, kiam la programo kuras tiel:

 py multiplicationTable.py 6 

... Ĝi devus krei kalkultabelo kiu similas Figuro 12-11 .

Figuro 12-11. A multipliko tablo generita en kalkultabelo

Vico 1 kaj kolumno A uziĝu por etiketoj kaj devus esti en aŭdaca.

Malplenan Vico inserter

Krei programon blankRowInserter.py kiu prenas du entjeroj kaj dosiernomo ĉeno kiel komandlinio argumentoj. Ni nomas la unuan entjeron N kaj la dua entjera M . Ekde vico N , la programo devus enmeti M malplenan vicoj en la kalkultabelo. Ekzemple, kiam la programo kuras tiel:

 python blankRowInserter.py 3 2 myProduce.xlsx 

... La "antaŭ" kaj "post" kalkultabelojn aspektu Figuro 12-12 .

Figuro 12-12. Antaŭ (maldekstre) kaj post (dekstra) la du malplenaj vicoj estas enmetitaj en vico 3

Vi povas skribi ĉi programo legante en la enhavo de la kalkultabelo. Tiam, kiam skribi la novan kalkultabelo, uzi forbuklo kopii la unuan N linioj. Por la ceteraj linioj, aldoni M al la vico nombro en la eligo kalkultabelo.

Kalkultabelo Cell Inverter

Skribi programon por inversigi la vico kaj kolumno de la ĉeloj en la kalkultabelo. Ekzemple, la valoro je vico 5, kolumno 3 estos en vico 3, kolumno 5 (kaj viceversa). Tio devus esti farita por ĉiuj ĉeloj en la kalkultabelo. Ekzemple, la "antaŭ" kaj "post" kalkultabelojn aspektus ion kiel Figuro 12-13 .

Figuro 12-13. La kalkultabelo antaŭe (pinto) kaj post (fundo) inversigo

Vi povas skribi ĉi programo uzante nestitaj formaŝojn legi en la kalkultabelo datumoj en listo de listoj datumstrukturo. Tiu datumstrukturo povus havi sheetData[x][y]por la ĉelo je kolumno xkaj vico y. Tiam, kiam skribi la novan kalkultabelo, uzi sheetData[y][x]por la ĉelo je kolumno xkaj vico y.

Teksto Dosieroj al kalkultabelo

Skribi programon por legi en la enhavon de pluraj tekstaj dosieroj (vi povas fari la tekston dosierojn mem) kaj enmeti tiujn enhavojn en kalkultabelo, kun unu linio de teksto por vico. La linioj de la unua teksto dosiero estos en la ĉeloj de kolumno A, la linioj de la dua teksto dosiero estos en la ĉeloj de kolumno B, kaj tiel plu.

Uzi la readlines() Fileobjekton metodo reveni listo de kordoj, unu cxeno por linio en la dosiero. Por la unua dosiero, eligo la unua linio al kolumno 1, remi 1. La dua linio devus esti skribita en kolumno 1, vico 2, kaj tiel plu. La sekva dosieron kiu legas kun readlines()estos skribita por kolumno 2, la proksima dosiero kolumno 3, kaj tiel plu.

Kalkultabelo al tekstdosieroj

Skribi programon kiu realigas la taskojn de la antaŭa programo en inversa ordo: La programo devus malfermi kalkultabelo kaj skribi la ĉeloj de kolumno A en unu tekstdosiero, la ĉeloj de kolumno B en alia teksto-dosiero, kaj tiel plu.

Subtenu la aŭtoro aĉetante la presaĵo & ebook pakaĵo de Neniu amelo Gazetaro aŭ aparte sur Amazono .

Legi la aŭtora aliaj Krea Komunaĵo publikigita Python libroj.