Metode & Algoritma | List Tutorials | Source Code | About | Sitemap
Konsultan Tesis
Bimbingan dan Konsultasi Tesis Informatika bersama team Project Graduate Indonesia. Konsultasi hanya untuk yang sudah me-Like FB kami (Silahkan LIKE tombol ini jika belum).
. Scroll kebawah untuk memasukan kode AntiSpam Protection. Hasil konsultasi akan kami kirimkan ke email Anda.

» » Membuat Laporan Report Excel Macro VBA dengan C#.NET


Membuat Laporan Report Excel Macro VBA dengan C#.NET



.
Metode dan Algoritma | Membuat Laporan Report Excel Macro VBA dengan C#.NET . Anda bisa melakukan konsultasi tentang Membuat Laporan Report Excel Macro VBA dengan C#.NET melalui form di samping kanan !!!

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.

 


Source Code ActionScript AS3 ASP.NET AJAX C / C++ C# Clipper COBOL ColdFusion DataFlex Delphi Emacs Lisp Fortran FoxPro Java J2ME JavaScript JScript Lingo MATLAB Perl PHP PostScript Python SQL VBScript Visual Basic 6.0 Visual Basic .NET Flash MySQL Oracle Android
Related Post :

Project-G
Judul: Membuat Laporan Report Excel Macro VBA dengan C#.NET
Rating: 100% based on 99998 ratings. 5 user reviews.
Ditulis Oleh hank2

Anda sedang membaca artikel tentang Membuat Laporan Report Excel Macro VBA dengan C#.NET, Semoga artikel tentang Membuat Laporan Report Excel Macro VBA dengan C#.NET ini sangat bermanfaat bagi teman-teman semua, jangan lupa untuk mengunjungi lagi melalui link Membuat Laporan Report Excel Macro VBA dengan C#.NET.

Posted by: Metode Algoritma Updated at: 21.48
di 21.48 - Label: -

{ 0 komentar... Views All / Send Comment! }


Posting Komentar

Label