Sabtu, 05 Mei 2012

Membuat Laporan Report Excel Macro VBA dengan C#.NET

Report Excel Macro dengan C#.NET


Report yang dibuat menggunakan Crystal Report kadangkala dirasa tidak fleksibel, karena pengguna kebanyakan lebih familiar dengan Microsoft Office, Word atau Excel. Pengguna ingin report mereka langsung terbentuk dalam format Microsoft Excel sehingga dapat diubah-ubah setiap waktu sesuai kebutuhan dan memiliki bentuk yang tepat seperti yang mereka inginkan.
Untuk membuat Report menggunakan Microsoft Excel, program yang dibuat harus dapat memanggil dan mengendalikan Microsoft Excel. Untuk melakukan hal ini program harus diberi tambahan Reference Library Microsoft Excel maupun Microsoft Office, caranya klik menu Project – Add Reference…

 

Setelah muncul Dialog Add Reference, pilih library Microsoft Excel Object Library dan Microsoft Office Object Library, pilih versi yang paling baru. Klik OK.





Setelah Reference ditambahkan dapat dimulai penulisan kode program. Pada bagian teratas (using), ketikkan using Microsoft.Office.Interop;. Kemudian kode program untuk memanggil dan mengendalikan Microsoft Excel dapat dicoba dan dilihat pada fasilitas Macro Microsoft Excel (VBA / Visual Basic Application), kode program selengkapnya di bawah ini :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
//Microsoft.Office.Interop;

namespace ExcelApp
{
    public partial class FExcel : Form
    {
        public OleDbConnection cn;
        public System.Data.DataSet ds;
        public System.Data.DataTable tb;

        public FExcel()
        {
            InitializeComponent();
        }

        private void FExcel_Load(object sender, EventArgs e)
        {
            int i;

            //cn = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security Info=True;User ID=sa;Initial Catalog=world;Password=")
            //cn = New OleDbConnection("Provider=MSDAORA;Data Source=localhost.world;Persist Security Info=True;User ID=admin;Password=")
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath) + "\\world.mdb");

            cn.Open();

            OleDbCommand cm = new OleDbCommand("SELECT name, address, phone FROM contacts", cn);
            OleDbDataReader rd;

            rd = cm.ExecuteReader();

            ds = new System.Data.DataSet();
            tb = new System.Data.DataTable();
            tb.TableName = "contacts";
            ds.Tables.Add(tb);

            ds.Load(rd, LoadOption.Upsert, tb);

            for (i = 0; i < (tb.Columns.Count - 1); i++)
            {
                tb.Columns[i].MaxLength = -1;
            }

            rd.Close();
            cn.Close();

            bs.DataSource = ds;

            bs.DataMember = "contacts";

            gContacts.DataSource = bs;
        }

        private void bExcel_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkbook;
            Excel.Worksheet xlWorksheet;
            Object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            //xlWorkbook = xlApp.Workbooks.Open("c:\\Data.xls", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            xlWorkbook = xlApp.Workbooks.Add(misValue);
            xlWorksheet = (Excel.Worksheet)(xlWorkbook.Sheets["Sheet1"]);

            xlWorksheet.get_Range("C3", "C3").FormulaR1C1 = "Contact List";
            //xlWorksheet.Cells[3, 3] = "Contact List";
            //((Excel.Range)(xlWorksheet.Cells[3, 3])).FormulaR1C1 = "Contact List";

            xlWorksheet.get_Range("A1", "A1").ColumnWidth = 3.86;
            //((Excel.Range)(xlWorksheet.Cells[1, 1])).ColumnWidth = 3.86;
            xlWorksheet.get_Range("B1", "B1").ColumnWidth = 22.29;
            xlWorksheet.get_Range("C1", "C1").ColumnWidth = 28.86;
            xlWorksheet.get_Range("D1", "D1").ColumnWidth = 16.71;

            xlWorksheet.get_Range("C3","C3").Font.Name = "Arial";
            xlWorksheet.get_Range("C3","C3").Font.FontStyle = "Bold";
            xlWorksheet.get_Range("C3","C3").Font.Size = 14;
            xlWorksheet.get_Range("C3","C3").Font.Strikethrough = false;
            xlWorksheet.get_Range("C3","C3").Font.Superscript = false;
            xlWorksheet.get_Range("C3","C3").Font.Subscript = false;
            xlWorksheet.get_Range("C3","C3").Font.OutlineFont = false;
            xlWorksheet.get_Range("C3","C3").Font.Shadow = false;
            xlWorksheet.get_Range("C3","C3").Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone;
            xlWorksheet.get_Range("C3","C3").Font.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5","B5").FormulaR1C1 = "Name";
            xlWorksheet.get_Range("C5","C5").FormulaR1C1 = "Address";
            xlWorksheet.get_Range("D5","D5").FormulaR1C1 = "Phone";

            xlWorksheet.get_Range("B5","D5").Interior.ColorIndex = 15;
            xlWorksheet.get_Range("B5","D5").Interior.Pattern = Excel.XlPattern.xlPatternSolid;
            xlWorksheet.get_Range("B5","D5").Interior.PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
           
            //int i;

            for (int i = 0; i < bs.Count; i++)
            {
                xlWorksheet.get_Range("B" + (i + 6).ToString(), "B" + (i + 6).ToString()).FormulaR1C1 = ((DataRowView)bs[i])["name"].ToString();
                xlWorksheet.get_Range("C" + (i + 6).ToString(), "C" + (i + 6).ToString()).FormulaR1C1 = ((DataRowView)bs[i])["address"].ToString();
                xlWorksheet.get_Range("D" + (i + 6).ToString(), "D" + (i + 6).ToString()).FormulaR1C1 = ((DataRowView)bs[i])["phone"].ToString();
            }

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            xlWorksheet.get_Range("B5:D" + (bs.Count + 5).ToString(), "B5:D" + (bs.Count + 5).ToString()).Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            xlWorksheet.get_Range("A1", "A1").EntireColumn.AutoFit();

            xlApp.Visible = true;
            //xlWorkbook.Save();
            try
            {
xlWorksheet.SaveAs(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath) + "\\Report.xls", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Report Masih Terbuka", "Konfirmasi", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }
    }
}

Klik menu Debug – Start Debugging (F5) untuk menjalankan program, kemudian klik Tombol untuk mencoba penggunaan Microsoft Excel dari kode program Visual Basic.NET


Microsoft Excel akan dipanggil dan ditulisi report di dalamnya sesuai isi database dan dengan format / style sesuai kode program yang dituliskan. Document Excel tersebut secara otomatis juga akan tersimpan pada file Report.xls.