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.
