Aplikasi database dengan Excel? Yap, judul di atas tidak keliru. Pada dasarnya Excel adalah database, hanya saja berbeda dengan database relational dan pemrosesannya lebih manual jika tanpa program khusus. Kita harus membukanya dengan Aplikasi Microsoft Excel untuk mengolah file Excel. So, kenapa pada blog ini ada tutorial tentang Excel? :D Mari saya ceritakan sedikit.
Dua pekan yang lalu ada orang dari instansi bertaraf nasional yang minta dibuatkan aplikasi reminder yang datanya dari file Excel. Data yang ingin dia olah adalah data pelanggan yang terkait dengan keuangan. Dengan demikian, dia sudah punya format file Excel yang akan diolah dan datanya sudah lengkap. Jadi, yang dia minta kerjakan adalah mengambil tanggal dari data Excel tersebut kemudian diolah untuk dijadikan aplikasi pengingat tagihan keuangan pelanggan.
Karena tenggat waktu yang ditentukan sangat mepet, yaitu tidak sampai 2 pekan, maka coba googling tentang bahasa pemrograman tercepat untuk develop aplikasi pengolah excel. Dan hasilnya adalah : Bahasa pemrograman VB, tepatnya VB.Net, wew! Sebelumnya saya belum pernah membuat aplikasi yang serius dengan VB, ada buku VB.Net di rumah, tapi tidak pernah digunakan :) . Untuk pengolah Excel dulu sering menggunakan PHP, itu pun hanya mengekstrak isinya saja dan tanpa manipulasi. Sedangkan kali ini, file Excel harus dimanipulasi menambah beberapa keterangan untuk setiap pelanggan.
Awalnya, file Excel akan dimanipulasi dengan manual. Kalau manipulasi manuai berhasil, berarti nanti harus mencari cara agar bisa melakukan pencarian data dengan rumus-rumus yang ada di Excel. Berikut ini adalah sampel source code untuk membaca dan memanipulasi file Excel dengan VB.Net.
''// import modul excel
Imports Excel = Microsoft.Office.Interop.Excel
'' /*bagian untuk mengakses file excel
' ketika Button1 diklik, maka buka file Excel ( D:\Book1.xlsx )
' Buka excel, buka workbook, dan buka sheet yang akan diolah
' Tampilkan data pada cell 2,2 dengan popup
' lalu manipulasi cell 2,2 dengan teks "elangsakti.com"
' tutup file Excel*/
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
xlApp = New Excel.Application
xlWorkbook = xlApp.Workbooks.Open("E:Book1.xlsx")
xlWorksheet = xlWorkbook.Worksheets("sheet1")
MsgBox(xlWorksheet.Cells(2, 2).value)
xlWorksheet.Cells(2, 2) = "elangsakti.com"
xlApp.DisplayAlerts = False
If Not xlWorkbook.Saved Then
xlWorkbook.Save()
End If
xlApp.DisplayAlerts = True
xlWorkbook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkbook)
releaseObject(xlWorksheet)
End Sub
Yak, script di atas untuk mengolah dan manipulasi file Excel dengan "Manual". Pada contoh program di atas, file Excel belum diakses sebagaimana mengakses database. Apa bedanya mengolah Excel sebagai database dan tidak sebagai database? Penjelasan sederhananya :
- Jika kita mengola file dengan koneksi database, maka perintah untuk mengambil data pasti dilakukan dengan Query, contohnya "SELECT * FROM blabla..... WHERE ... ORDER BY ..". Tapi jika tidak sebagai database, maka kita secara manual menentukan cel-cel yang akan diolah.
- Jika ingin mengubah data dengan koneksi database, maka perintahnya adalah dengan "UPDATE blabla ...". Jadi, jika menggunakan koneksi database, maka perintah CRUD-nya juga menggunakan SQL.
Lalu, Bagaimana Cara Membuat Excel Sebagai Database?
Berikut ini adalah script untuk koneksi database Excel pada VB.Net. Haruskah VB.Net, sepertinya tidak :D . Untuk menampilkan data di Excel ke dalam DataGridView, silakan gunakan script di bawah ini.
Dim Excel_fileName As String = "E:Book1.xlsx"
Dim Excel_sheetName As String = "Sheet1"
Dim Excel_Connection As System.Data.OleDb.OleDbConnection
Dim Excel_Dataset As System.Data.DataSet
Dim Excel_Command As System.Data.OleDb.OleDbDataAdapter
Excel_Connection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" _
& Excel_fileName & "'; Extended Properties=Excel 8.0;")
''// select data
Excel_Connection.Open()
Excel_Command = New System.Data.OleDb.OleDbDataAdapter("Select * from [" & Excel_sheetName & "]", Excel_Connection)
Excel_Dataset = New System.Data.DataSet
Excel_Command.Fill(Excel_Dataset)
''// tampilkan pada datagridview
DataGridView1.DataSource = Excel_Dataset.Tables(0)
Excel_Connection.Close()
Sedangkan jika ingin mengubah data di row Excel, gunakan script di bawah ini.
Dim Excel_fileName As String = "E:Book1.xlsx"
Dim Excel_sheetName As String = "Sheet1"
Dim Excel_Connection As System.Data.OleDb.OleDbConnection
Dim Excel_Command_Update As New System.Data.OleDb.OleDbCommand
Dim KOLOM_KETERANGAN As String = "Keterangan"
Dim KOLOM_ID As String = "ID"
Dim KOLOM_ID As Integer = 3
Excel_Connection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + Excel_fileName + "'; Extended Properties=Excel 8.0;")
Excel_Connection.Open()
Excel_Command_Update.Connection = Excel_Connection
''// update keterangan
Dim sql As String = "Update [" & Excel_sheetName & "] set [" & KOLOM_KETERANGAN & "]='" & keterangan & "' where [IDPEL]='" & idpel & "'"
Excel_Command_Update.CommandText = sql
Excel_Command_Update.ExecuteNonQuery()
Excel_Connection.Close()
Perhatikan, command yang digunakan ketika ingin menampilkan data berbeda dengan ketika ingin mengubah data. Jika ada yang belum jelas, Anda boleh bertanya di kolom komentar. :)