Memanipulasi Spreadsheet Excel menggunakan Python

Microsoft Excel adalah perangkat lunak spreadsheet yang digunakan untuk menyimpan dan mengelola data tabular. Selanjutnya, dengan Excel, perhitungan dapat dilakukan dengan menerapkan rumus pada data, dan visualisasi data dapat dihasilkan.Banyak tugas yang dilakukan dalam spreadsheet, seperti operasi matematika, dapat diotomatisasi melalui pemrograman, dan banyak bahasa pemrograman memiliki modul untuk memanipulasi spreadsheet Excel. Dalam tutorial ini, kami akan menunjukkan cara menggunakan modul openpyxl Python untuk membaca dan memodifikasi spreadsheet Excel.

Menginstal openpyxl

Sebelum Anda dapat menginstal openpyxl, Anda harus menginstal pip. Pip digunakan untuk menginstal paket Python. Jalankan perintah berikut di command prompt untuk melihat apakah pip diinstal.

C:\Users\windows> bantuan pip

Jika konten bantuan pip dikembalikan, maka pip diinstal; jika tidak, buka tautan berikut dan unduh get-pip.file .py:

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

Sekarang, jalankan perintah berikut untuk menginstal pip:

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

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

C:\Users\windows> pip install openpyxl

Membuat Dokumen Excel

Di bagian ini, kita akan menggunakan modul openpyxl untuk membuat dokumen Excel. Pertama, buka command prompt dengan mengetik 'cmd' di bilah pencarian; lalu, masuk

C:\Users\windows> python

Untuk membuat workbook Excel, kita akan mengimpor modul openpyxl dan kemudian menggunakan metode 'Workbook()' untuk membuat workbook.

>>> # mengimpor modul openpyxl
>>> impor openpyxl
>>> # Menginisialisasi Buku Kerja
>>> work_book = openpyxl.buku kerja()
>>> # menyimpan buku kerja sebagai 'contoh.xlsx'
>>> buku_kerja.simpan('contoh.xlsx')

Perintah di atas membuat dokumen Excel yang disebut contoh.xlsx. Selanjutnya, kita akan memanipulasi dokumen Excel ini.

Memanipulasi Lembar dalam Dokumen Excel

Kami telah membuat dokumen Excel yang disebut contoh.xlsx. Sekarang, kita akan memanipulasi lembar dokumen ini menggunakan Python. Modul openpyxl memiliki metode 'create_sheet()' yang dapat digunakan untuk membuat lembar baru. Metode ini membutuhkan dua argumen: indeks dan judul. Indeks mendefinisikan penempatan lembar menggunakan bilangan bulat non-negatif (termasuk 0), dan judul adalah judul lembar. Daftar semua lembar dalam objek work_book dapat ditampilkan dengan memanggil daftar nama lembar.

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # memuat Dokumen Excel yang ada ke dalam Obyek work_book
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # Membuat Lembar baru pada indeks ke-0
>>> buku_kerja.create_sheet(indeks=0, judul='Lembar Pertama')

>>> # Mendapatkan semua Lembar
>>> buku_kerja.nama sheet
['Lembar Pertama', 'Lembar']
>>> # Menyimpan Dokumen Excel
>>> buku_kerja.simpan('contoh.xlsx')

Dalam kode di atas, kami membuat lembar bernama Lembar Pertama dan menempatkannya di indeks 0. Lembar yang sebelumnya terletak di indeks ke-0 dipindahkan ke indeks ke-1, seperti yang ditunjukkan pada output. Sekarang, kita akan mengubah nama sheet asli dari Sheet menjadi Second Sheet.

Atribut title memegang nama sheet. Untuk mengganti nama sheet, pertama-tama kita harus menavigasi ke sheet itu sebagai berikut:.

>>> # Mendapatkan lembar aktif dari Dokumen Excel
>>> lembar = work_book.aktif
>>> # Nama Lembar Cetak
>>> cetak (lembar).judul)
Lembar Pertama >>> # Menavigasi ke Lembar Kedua (pada indeks 1)
>>> buku_kerja.aktif = 1
>>> # Mendapatkan Lembar Aktif
>>> lembar = work_book.aktif
>>> # mencetak Nama Lembar
>>> cetak (lembar).judul)
Lembar >>> # Mengubah Judul Lembar
>>> lembar.judul = 'Lembar Kedua'
>>> # Judul Lembar Cetak
>>> cetak (lembar).judul)
Lembar Kedua

Demikian pula, kita dapat menghapus lembar dari dokumen Excel. Modul openpyxl menawarkan metode remove() untuk menghapus sheet. Metode ini mengambil nama lembar yang akan dihapus sebagai argumen dan kemudian menghapus lembar itu. Kami dapat menghapus Lembar Kedua sebagai berikut:

>>> # menghapus Lembar dengan nama
>>> buku_kerja.hapus(work_book['Lembar Kedua'])
>>> # mendapatkan semua lembar
>>> buku_kerja.nama sheet
['Lembar Pertama']
>>> # menyimpan Dokumen Excel
>>> buku_kerja.simpan('contoh.xlsx')

Menambahkan Data ke Sel

Sejauh ini, kami telah menunjukkan kepada Anda cara membuat atau menghapus lembar dalam dokumen Excel. Sekarang, kita akan menambahkan data ke sel dari lembar yang berbeda. Dalam contoh ini, kami memiliki satu lembar bernama Lembar Pertama di dokumen kami, dan kami ingin membuat dua lembar lagi.

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # Membuat Lembar baru di indeks pertama
>>> buku_kerja.create_sheet(indeks=1, judul='Lembar Kedua')

>>> # membuat Lembar baru di indeks ke-2
>>> buku_kerja.create_sheet(indeks=2, judul='Lembar Ketiga')

>>> # mendapatkan semua lembar
>>> buku_kerja.nama sheet
['Lembar Pertama', 'Lembar Kedua', 'Lembar Ketiga']

Sekarang, kami memiliki tiga lembar, dan kami akan menambahkan data ke sel lembar ini.

>>> # Mendapatkan Lembar Pertama
>>> sheet_1 = work_book['Lembar Pertama']
>>> # Menambahkan Data ke Sel 'A1' dari Lembar Pertama
>>> sheet_1['A1'] = 'Nama'
>>> # Mendapatkan Lembar Kedua
>>> sheet_2 = work_book['Lembar Kedua']
>>> # Menambahkan Data ke Sel 'A1' dari Lembar Kedua
>>> sheet_2['A1'] = 'ID'
>>> # Mendapatkan Lembar Ketiga
>>> sheet_3 = work_book['Lembar Ketiga']
>>> # Menambahkan Data ke Sel 'A1' dari Lembar Ketiga
>>> sheet_3['A1'] = 'Nilai'
>>> # Menyimpan Buku Kerja Excel
>>> buku_kerja.simpan('contoh.xlsx')

Membaca Lembar Excel

Modul openpyxl menggunakan atribut nilai sel untuk menyimpan data sel itu. Kita dapat membaca data dalam sel dengan memanggil atribut nilai sel. Sekarang, kami memiliki tiga lembar, dan setiap lembar berisi beberapa data. Kita dapat membaca data dengan menggunakan fungsi berikut di openpyxl:

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # Mendapatkan Lembar Pertama
>>> sheet_1 = work_book['Lembar Pertama']
>>> # Mendapatkan Lembar Kedua
>>> sheet_2 = work_book['Lembar Kedua']
>>> # Mendapatkan Lembar Ketiga
>>> sheet_3 = work_book['Lembar Ketiga']
>>> # mencetak data dari sel 'A1' Lembar Pertama
>>> cetak(sheet_1['A1'].nilai)
Nama
>>> # mencetak data dari sel 'A1' Lembar Kedua
>>> cetak(sheet_2['A1'].nilai)
Indo
>>> # mencetak data dari sel 'A1' Lembar Ketiga
>>> cetak(sheet_3['A1'].nilai)
Nilai

Mengubah Font dan Warna

Selanjutnya, kami akan menunjukkan cara mengubah font sel dengan menggunakan fungsi Font(). Pertama, impor openpyxl.objek gaya. Metode Font() mengambil daftar argumen, termasuk:

nama (tali): nama fontnya
ukuran (int atau float): ukuran font
garis bawah (string): tipe garis bawah
warna (tali): warna heksadesimal dari teks
miring (bool): apakah font dicetak miring
tebal (bool): apakah fontnya dicetak tebal

Untuk menerapkan gaya, pertama-tama kita harus membuat objek dengan meneruskan semua parameter ke metode Font(). Kemudian, kami memilih lembar, dan di dalam lembar, kami memilih sel yang ingin kami terapkan gayanya. Kemudian, kami menerapkan gaya ke sel yang dipilih.

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # mengimpor metode Font dari openpyxl.gaya
>>> dari openpyxl.gaya impor Font
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # Membuat objek gaya
>>> style = Font(name='Consolas', size=13, bold=True,
… italic=Salah)
>>> # Memilih Lembar dari Buku Kerja
>>> sheet_1 = work_book['Lembar Pertama']
>>> # Memilih sel yang ingin kita tambahkan gaya
>>> a1 = sheet_1['A1']
>>> # Menerapkan Gaya ke sel
>>> a1.font = gaya
>>> # Menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Menerapkan Batas ke Sel

Kita dapat menerapkan batas ke sel dalam lembar Excel menggunakan metode Border() dan Side() dari openpyxl.gaya.modul perbatasan. Kita dapat melewatkan fungsi yang berbeda sebagai parameter ke metode Border(). Berikut ini adalah beberapa fungsi yang dilewatkan sebagai parameter ke metode Border() untuk menentukan dimensi batas:.

kiri: menerapkan batas ke sisi kiri sel
Baik: menerapkan batas ke sisi kanan sel
puncak: menerapkan batas ke bagian atas sel
bawah: menerapkan batas ke bagian bawah sel

Fungsi-fungsi ini mengambil atribut gaya sebagai parameter. Atribut style mendefinisikan gaya perbatasan (mis.g., padat, putus-putus). Parameter gaya dapat memiliki salah satu dari nilai berikut:.

dua kali lipat: perbatasan garis ganda
putus asa: batas putus-putus
tipis: batas tipis
medium: batas sedang
mediumDashDot: batas putus-putus dan putus-putus dengan bobot sedang
tebal: batas tebal
tanda titik: batas putus-putus dan putus-putus
rambut: batas yang sangat tipis
burik: batas putus-putus

Sekarang, kami akan menerapkan berbagai jenis batas ke sel yang berbeda dari spreadsheet kami. Pertama, kami memilih sel, dan kemudian, kami mendefinisikan gaya batas dan menerapkan gaya ini ke sel yang berbeda.

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # mengimpor kelas Perbatasan dan Sisi
>>> dari openpyxl.gaya.perbatasan impor Perbatasan, Sisi
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # Memilih Lembar
>>> sheet_1 = work_book['Lembar Pertama']
>>> # Memilih sel yang berbeda dari lembar
>>> sel_1 = sheet_1['A1']
>>> sel_2 = sheet_1['B2']
>>> sel_3 = sheet_1['C3']
>>> # Mendefinisikan gaya perbatasan yang berbeda
>>> style_1 = Border(bottom=Side(style='dotted'))
>>> style_2 = Batas(kanan=Sisi(style='tipis'))
>>> style_3 = Batas(atas=Sisi(style='dashDot'))
>>> # menerapkan gaya batas ke sel
>>> sel_1.perbatasan = gaya_1
>>> sel_2.perbatasan = gaya_2
>>> sel_3.perbatasan = gaya_3
>>> # Menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Menyesuaikan Dimensi Baris dan Kolom

Tinggi baris dan lebar kolom dokumen Excel juga dapat disesuaikan menggunakan Python. Modul openpyxl memiliki dua metode bawaan yang dapat digunakan untuk melakukan tindakan ini. Pertama, kami memilih lembar yang ingin kami ubah lebar kolom atau tinggi barisnya. Kemudian, kami menerapkan metode ke baris atau kolom tertentu.

>>> # mengimpor openpyxl
>>> impor openpyxl
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # lembar pemilihan
>>> sheet_1 = work_book['Lembar Pertama']
>>> # mengubah tinggi baris pertama
>>> lembar_1.baris_dimensi[1].tinggi = 50
>>> # Menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Demikian pula, kita dapat mengubah lebar kolom menggunakan kode berikut:

>>> # memilih lembar dari buku kerja excel
>>> sheet_2 = work_book['Lembar Kedua']
>>> # mengubah lebar kolom A
>>> lembar_2.kolom_dimensi['A'].lebar = 50
>>> # Menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Kode di atas akan mengubah tinggi baris pertama menjadi 50 pts dan lebar kolom A menjadi 50 pts.

Menggabungkan dan Memisahkan Sel

Saat bekerja dengan spreadsheet Excel, kita sering perlu menggabungkan dan memisahkan sel. Untuk menggabungkan sel dengan Python, fungsi sederhana berdasarkan openpyxl dapat digunakan. Modul openpyxl menawarkan metode merge_cells(), yang dapat digunakan untuk menggabungkan sel di Excel. Sel baru akan mengambil nama sel kiri atas. Misalnya, 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-tama kita pilih sheet, lalu kita terapkan metode merge_cells() ke sheet.

>>> # mengimpor modul openpyxl
>>> impor openpyxl
>>> # memuat buku kerja
>>> work_book = openpyxl.load_workbook('contoh.xlsx')
>>> # memilih lembar pertama dari buku kerja excel
>>> sheet_1 = work_book['Lembar Pertama']
>>> # menggabungkan sel dari A1 ke B2 di Lembar 1
>>> lembar_1.merge_cells('A1:B2')
>>> # menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Demikian pula, metode unmerge_cells() dapat digunakan untuk memisahkan sel dalam spreadsheet Excel. Kode berikut dapat digunakan untuk memisahkan sel:

>>> # memilih lembar dari buku kerja
>>> sheet_1 = work_book['Lembar Pertama']
>>> # memisahkan sel dari A1 ke B2
>>> lembar_1.unmerge_cells('A1:B2')
>>> # menyimpan buku kerja
>>> buku_kerja.simpan('contoh.xlsx')

Kesimpulan

Spreadsheet Excel biasanya digunakan untuk manipulasi data. Namun, tugas seperti itu bisa monoton can. Oleh karena itu, dalam kasus seperti itu, pemrograman dapat digunakan untuk mengotomatisasi manipulasi spreadsheet.

Dalam artikel ini, kami membahas beberapa fungsi yang berguna dari modul openpyxl Python. Kami menunjukkan cara membuat, membaca, menghapus, dan memodifikasi lembar bentang Excel, cara mengubah gaya, menerapkan font, batas, dan dimensi sel, serta cara menggabungkan dan memisahkan sel. Dengan menerapkan fungsi-fungsi ini, Anda dapat mengotomatiskan banyak tugas manipulasi spreadsheet menggunakan Python.