Memanipulasi Spreadsheets Excel menggunakan Python

Microsoft Excel adalah perisian spreadsheet yang digunakan untuk menyimpan dan menguruskan data jadual. Selanjutnya, dengan Excel, pengiraan dapat dilakukan dengan menerapkan formula pada data, dan visualisasi data dapat dihasilkan.Banyak tugas yang dilakukan dalam spreadsheet, seperti operasi matematik, dapat dilakukan secara automatik melalui pengaturcaraan, dan banyak bahasa pengaturcaraan memiliki modul untuk memanipulasi spreadsheet Excel. Dalam tutorial ini, kami akan menunjukkan kepada anda cara menggunakan modul openpyxl Python untuk membaca dan mengubahsuai spreadsheet Excel.

Memasang openpyxl

Sebelum anda boleh memasang openpyxl, anda mesti memasang pip. Pip digunakan untuk memasang pakej Python. Jalankan arahan berikut di command prompt untuk melihat apakah pip dipasang.

C: \ Users \ windows> pip help

Sekiranya kandungan bantuan pip dikembalikan, maka pip dipasang; jika tidak, pergi ke pautan berikut dan muat turun get-pip.fail py:

https: // bootstrap.pypa.io / get-pip.py

Sekarang, jalankan arahan berikut untuk memasang pip:

C: \ Users \ windows> get-pip python.py

Setelah memasang pip, perintah berikut dapat digunakan untuk memasang openpyxl.

C: \ Users \ windows> pip install openpyxl

Membuat Dokumen Excel

Di bahagian ini, kami akan menggunakan modul openpyxl untuk membuat dokumen Excel. Pertama, buka command prompt dengan menaip 'cmd' di bar carian; kemudian, masukkan

C: \ Users \ windows> python

Untuk membuat buku kerja Excel, kami akan mengimport modul openpyxl dan kemudian menggunakan kaedah 'Workbook ()' untuk membuat buku kerja.

>>> # mengimport modul openpyxl
>>> import openpyxl
>>> # Memulakan Buku Kerja
>>> buku_ kerja = openpyxl.Buku Kerja ()
>>> # menyimpan buku kerja sebagai 'contoh.xlsx '
>>> buku kerja.simpan ('contoh.xlsx ')

Perintah di atas membuat dokumen Excel yang disebut contoh.xlsx. Seterusnya, kami akan memanipulasi dokumen Excel ini.

Memanipulasi Helaian dalam Dokumen Excel

Kami telah membuat dokumen Excel yang dipanggil contoh.xlsx. Sekarang, kita akan memanipulasi lembaran dokumen ini menggunakan Python. Modul openpyxl mempunyai kaedah 'create_sheet ()' yang dapat digunakan untuk membuat lembaran baru. Kaedah ini mengambil dua hujah: indeks dan tajuk. Indeks mendefinisikan penempatan lembaran menggunakan bilangan bulat bukan negatif (termasuk 0), dan tajuk adalah tajuk lembaran. Senarai semua helaian dalam objek buku kerja dapat ditunjukkan dengan memanggil senarai nama lembaran.

>>> # mengimport openpyxl
>>> import openpyxl
>>> # memuatkan Dokumen Excel yang ada ke Objek buku kerja
>>> buku_ kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # Membuat Helaian baru pada indeks ke-0
>>> buku kerja.create_sheet (indeks = 0, tajuk = 'Helaian Pertama')

>>> # Mendapatkan semua Helaian
>>> buku kerja.nama lembaran
['Lembaran Pertama', 'Lembaran']
>>> # Menyimpan Dokumen Excel
>>> buku kerja.simpan ('contoh.xlsx ')

Dalam kod di atas, kami membuat helaian bernama First Sheet dan meletakkannya di indeks ke-0. Lembaran yang sebelumnya terletak di indeks ke-0 dipindahkan ke indeks ke-1, seperti yang ditunjukkan dalam output. Sekarang, kita akan menukar nama helaian asal dari Helaian ke Helaian Kedua.

Atribut tajuk memegang nama helaian. Untuk menamakan semula helaian, pertama kita mesti menavigasi ke helaian seperti berikut.

>>> # Mendapatkan lembaran aktif dari Excel Document
>>> helaian = buku kerja.aktif
>>> # Mencetak Nama Helaian
>>> cetak (helaian.tajuk)
Helaian Pertama >>> # Menavigasi ke Helaian Kedua (pada indeks 1)
>>> buku kerja.aktif = 1
>>> # Mendapatkan Lembaran Aktif
>>> helaian = buku kerja.aktif
>>> # mencetak Nama Helaian
>>> cetak (helaian.tajuk)
Helaian >>> # Menukar Tajuk Lembaran
>>> helaian.tajuk = 'Helaian Kedua'
>>> # Tajuk Lembaran Percetakan
>>> cetak (helaian.tajuk)
Helaian Kedua

Begitu juga, kita boleh mengeluarkan helaian dari dokumen Excel. Modul openpyxl menawarkan kaedah remove () untuk mengeluarkan helaian. Kaedah ini mengambil nama helaian untuk dikeluarkan sebagai argumen dan kemudian membuang helaian itu. Kami boleh membuang Helaian Kedua seperti berikut:

>>> # mengeluarkan Helaian mengikut nama
>>> buku kerja.alih keluar (buku kerja ['Helaian Kedua'])
>>> # mendapatkan semua helaian
>>> buku kerja.nama lembaran
['Helaian Pertama']
>>> # menyimpan Dokumen Excel
>>> buku kerja.simpan ('contoh.xlsx ')

Menambah Data ke Sel

Sejauh ini, kami telah menunjukkan kepada anda cara membuat atau menghapus helaian dalam dokumen Excel. Sekarang, kita akan menambahkan data ke sel-sel lembaran yang berbeza. Dalam contoh ini, kami mempunyai satu helaian bernama First Sheet dalam dokumen kami, dan kami ingin membuat dua helaian lagi.

>>> # mengimport openpyxl
>>> import openpyxl
>>> # memuatkan buku kerja
>>> buku_ kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # Membuat Helaian baru pada indeks pertama
>>> buku kerja.create_sheet (indeks = 1, tajuk = 'Helaian Kedua')

>>> # membuat Helaian baru di indeks ke-2
>>> buku kerja.create_sheet (indeks = 2, tajuk = 'Lembaran Ketiga')

>>> # mendapatkan semua helaian
>>> buku kerja.nama lembaran
['Lembaran Pertama', 'Lembaran Kedua', 'Lembaran Ketiga']

Sekarang, kami mempunyai tiga helaian, dan kami akan menambahkan data ke sel lembaran ini.

>>> # Mendapatkan Helaian Pertama
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # Menambah Data ke Sel 'A1' Helaian Pertama
>>> helaian_1 ['A1'] = 'Nama'
>>> # Mendapatkan Helaian Kedua
>>> helaian_2 = buku kerja ['Helaian Kedua']
>>> # Menambah Data ke Sel 'A1' Helaian Kedua
>>> helaian_2 ['A1'] = 'ID'
>>> # Mendapatkan Helaian Ketiga
>>> lembaran_3 = buku kerja ['Lembaran Ketiga']
>>> # Menambah Data ke Sel 'A1' Helaian Ketiga
>>> helaian_3 ['A1'] = 'Gred'
>>> # Menyimpan Buku Kerja Excel
>>> buku kerja.simpan ('contoh.xlsx ')

Membaca Helaian Excel

Modul openpyxl menggunakan atribut nilai sel untuk menyimpan data sel tersebut. Kita dapat membaca data dalam sel dengan memanggil atribut nilai sel. Sekarang, kami mempunyai tiga helaian, dan setiap helaian mengandungi beberapa data. Kami dapat membaca data dengan menggunakan fungsi berikut di openpyxl:

>>> # mengimport openpyxl
>>> import openpyxl
>>> # memuatkan buku kerja
>>> buku_ kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # Mendapatkan Helaian Pertama
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # Mendapatkan Helaian Kedua
>>> helaian_2 = buku kerja ['Helaian Kedua']
>>> # Mendapatkan Helaian Ketiga
>>> helaian_3 = buku kerja ['Lembaran Ketiga']
>>> # mencetak data dari sel 'A1' Helaian Pertama
>>> cetak (helaian_1 ['A1'].nilai)
Nama
>>> # mencetak data dari sel 'A1' Helaian Kedua
>>> cetak (helaian_2 ['A1'].nilai)
ID
>>> # mencetak data dari sel 'A1' Helaian Ketiga
>>> cetak (helaian_3 ['A1'].nilai)
Gred

Menukar Fon dan Warna

Seterusnya, kami akan menunjukkan kepada anda cara mengubah fon sel dengan menggunakan fungsi Font (). Pertama, import openpyxl.objek gaya. Kaedah Font () mengambil senarai argumen, termasuk:

nama (rentetan): nama fon
saiz (int atau apungan): saiz fon
garis bawah (rentetan): jenis garis bawah
warna (tali): warna heksadesimal teks
miring (bool): adakah fon dicetak miring
berani (bool): adakah fonnya dicetak tebal

Untuk menerapkan gaya, kita mesti membuat objek terlebih dahulu dengan meneruskan semua parameter ke kaedah Font (). Kemudian, kami memilih helaian, dan di dalam helaian, kami memilih sel yang ingin kami terapkan gaya. Kemudian, kami menerapkan gaya ke sel yang dipilih.

>>> # mengimport openpyxl
>>> import openpyxl
>>> # mengimport kaedah Font dari openpyxl.gaya
>>> dari openpyxl.gaya mengimport Font
>>> # memuatkan buku kerja
>>> buku kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # Membuat objek gaya
>>> gaya = Font (nama = 'Consolas', ukuran = 13, tebal = Benar,
... miring = Salah)
>>> # Memilih Helaian dari Buku Kerja
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # Memilih sel yang ingin kita tambah gaya
>>> a1 = helaian_1 ['A1']
>>> # Menerapkan Gaya ke sel
>>> a1.fon = gaya
>>> # Menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Memohon Sempadan pada Sel

Kita boleh menerapkan batas ke sel dalam lembaran Excel menggunakan kaedah Border () dan Side () dari openpyxl.gaya.modul sempadan. Kita dapat menyampaikan fungsi yang berbeza sebagai parameter ke kaedah Border (). Berikut adalah beberapa fungsi yang diteruskan sebagai parameter ke kaedah Border () untuk menentukan dimensi sempadan.

dibiarkan: gunakan sempadan di sebelah kiri sel
betul: gunakan sempadan di sebelah kanan sel
bahagian atas: gunakan sempadan ke bahagian atas sel
bawah: gunakan sempadan ke bahagian bawah sel

Fungsi ini mengambil atribut gaya sebagai parameter. Atribut gaya menentukan gaya sempadan (e.g., padat, putus-putus). Parameter gaya boleh mempunyai salah satu nilai berikut.

berganda: sempadan garis dua
putus asa: sempadan putus-putus
kurus: sempadan nipis
sederhana: sempadan sederhana
mediumDashDot: sempadan putus-putus dengan berat sederhana
tebal: sempadan tebal
dashDot: sempadan putus-putus dan putus-putus
rambut: sempadan yang sangat nipis
bertitik tolak: sempadan putus-putus

Sekarang, kami akan menggunakan pelbagai jenis sempadan ke sel spreadsheet yang berlainan. Pertama, kita memilih sel, dan kemudian, kita menentukan gaya sempadan dan menerapkan gaya ini ke sel yang berbeza.

>>> # mengimport openpyxl
>>> import openpyxl
>>> # mengimport kelas Sempadan dan Sampingan
>>> dari openpyxl.gaya.sempadan import Border, Side
>>> # memuatkan buku kerja
>>> buku_ kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # Memilih Helaian
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # Memilih sel yang berbeza dari helaian
>>> sel_1 = helaian_1 ['A1']
>>> sel_2 = helaian_1 ['B2']
>>> sel_3 = helaian_1 ['C3']
>>> # Mendefinisikan gaya sempadan yang berbeza
>>> style_1 = Sempadan (bawah = Sisi (gaya = 'putus-putus'))
>>> style_2 = Border (kanan = Sisi (style = 'nipis'))
>>> style_3 = Border (atas = Sisi (style = 'dashDot'))
>>> # menerapkan gaya sempadan ke sel
>>> sel_1.sempadan = gaya_1
>>> sel_2.sempadan = gaya_2
>>> sel_3.sempadan = gaya_3
>>> # Menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Menyelaraskan Dimensi Baris dan Lajur

Ketinggian baris dan lebar lajur dokumen Excel juga dapat disesuaikan menggunakan Python. Modul openpyxl mempunyai dua kaedah terbina dalam yang boleh digunakan untuk melakukan tindakan ini. Pertama, kami memilih helaian yang ingin kami ubah lebar lajur atau tinggi baris. Kemudian, kami menerapkan kaedah pada baris atau lajur tertentu.

>>> # mengimport openpyxl
>>> import openpyxl
>>> # memuatkan buku kerja
>>> buku kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # memilih helaian
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # mengubah ketinggian baris pertama
>>> helaian_1.baris_dimensi [1].tinggi = 50
>>> # Menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Begitu juga, kita boleh mengubah lebar lajur menggunakan kod berikut

>>> # memilih helaian dari buku kerja excel
>>> helaian_2 = buku kerja ['Helaian Kedua']
>>> # mengubah lebar lajur A
>>> helaian_2.ruang_dimensi ['A'].lebar = 50
>>> # Menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Kod di atas akan mengubah ketinggian baris pertama menjadi 50 pts dan lebar lajur A hingga 50 pts.

Penggabungan dan Penggabungan Sel

Semasa bekerja dengan hamparan Excel, kita sering perlu menggabungkan dan mencabut sel. Untuk menggabungkan sel di Python, fungsi mudah berdasarkan openpyxl dapat digunakan. Modul openpyxl menawarkan kaedah merge_cells (), yang boleh digunakan untuk menggabungkan sel di Excel. Sel baru akan mengambil nama sel kiri atas. Sebagai contoh, jika kita ingin menggabungkan sel dari sel A1 ke sel B2, maka sel yang baru terbentuk akan disebut sebagai A1. Untuk menggabungkan sel menggunakan openpyxl, pertama kami memilih helaian, dan kemudian kami menerapkan kaedah merge_cells () ke helaian.

>>> # mengimport modul openpyxl
>>> import openpyxl
>>> # memuatkan buku kerja
>>> buku_ kerja = openpyxl.load_workbook ('contoh.xlsx ')
>>> # memilih helaian pertama dari buku kerja excel
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # menggabungkan sel dari A1 ke B2 di Helaian 1
>>> helaian_1.gabungan_cells ('A1: B2')
>>> # menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Begitu juga, kaedah unmerge_cells () dapat digunakan untuk mencabut sel dalam spreadsheet Excel. Kod berikut boleh digunakan untuk mencabut sel:

>>> # memilih helaian dari buku kerja
>>> helaian_1 = buku kerja ['Helaian Pertama']
>>> # mencabut sel dari A1 hingga B2
>>> helaian_1.unmerge_cells ('A1: B2')
>>> # menyimpan buku kerja
>>> buku kerja.simpan ('contoh.xlsx ')

Kesimpulannya

Spreadsheet Excel biasanya digunakan untuk manipulasi data. Walau bagaimanapun, tugas seperti itu boleh menjadi monoton. Oleh itu, dalam kes seperti itu, pengaturcaraan dapat digunakan untuk mengotomatisasi manipulasi spreadsheet.

Dalam artikel ini, kami membincangkan beberapa fungsi berguna modul openpyxl Python. Kami menunjukkan kepada anda cara membuat, membaca, menghapus dan mengubah hamparan Excel, cara mengubah gaya, menerapkan fon, sempadan, dan dimensi sel, dan cara menggabungkan dan mencabut sel. Dengan menerapkan fungsi ini, anda dapat mengotomatisasi banyak tugas manipulasi spreadsheet menggunakan Python.