Membuat aplikasi Windows Forms yang dapat membuka file Excel saat tombol diklik merupakan persyaratan umum untuk aplikasi desktop. Baik Anda membuat formulir entri data, pembuat laporan, atau alat pemrosesan file, mengintegrasikan operasi Excel dengan peristiwa klik tombol sangatlah penting.

Operasi File Excel pada Klik Tombol di C# Windows Forms

Skenario Umum

  • Dialog Buka File Excel: Izinkan pengguna memilih file Excel
  • Proses Data Excel: Membaca dan menampilkan konten Excel
  • Ekspor ke Excel: Hasilkan file Excel dari data formulir
  • Perbarui File Excel: Ubah file Excel yang ada

Menyiapkan Proyek Windows Forms

Pertama, buat proyek Windows Forms baru dan tambahkan Openize.OpenXML SDK:

<PackageReference Include="DocumentFormat.OpenXml" Version="3.2.0" />

using Openize.Cells;
using System;
using System.IO;
using System.Windows.Forms;

Contoh 1: Buka File Excel dengan Mengklik Tombol

public partial class ExcelForm : Form
{
    private string selectedFilePath = "";
    
    public ExcelForm()
    {
        InitializeComponent();
    }
    
    // Button click to open file dialog
    private void btnOpenFile_Click(object sender, EventArgs e)
    {
        using (OpenFileDialog openFileDialog = new OpenFileDialog())
        {
            // Configure file dialog
            openFileDialog.Filter = "Excel Files|*.xlsx;*.xls|All Files|*.*";
            openFileDialog.Title = "Select Excel File";
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Documents);
            
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                selectedFilePath = openFileDialog.FileName;
                lblFileName.Text = $"Selected: {Path.GetFileName(selectedFilePath)}";
                btnProcessFile.Enabled = true;
                
                // Immediately show file info
                DisplayFileInfo();
            }
        }
    }
    
    private void DisplayFileInfo()
    {
        try
        {
            using (var workbook = new Workbook(selectedFilePath))
            {
                var worksheet = workbook.Worksheets[0];
                
                lblWorksheetName.Text = $"Worksheet: {worksheet.Name}";
                lblRowCount.Text = $"Rows: {worksheet.GetRowCount()}";
                lblColumnCount.Text = $"Columns: {worksheet.GetColumnCount()}";
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error reading file info: {ex.Message}", "Error");
        }
    }
}

Contoh 2: Memproses Data Excel dan Menampilkannya di DataGridView

public partial class ExcelProcessorForm : Form
{
    // Button click to process Excel file
    private void btnProcessExcel_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(selectedFilePath))
        {
            MessageBox.Show("Please select an Excel file first.", "No File Selected");
            return;
        }
        
        try
        {
            ProcessExcelFile(selectedFilePath);
            MessageBox.Show("Excel file processed successfully!", "Success");
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error processing Excel file: {ex.Message}", "Error");
        }
    }
    
    private void ProcessExcelFile(string filePath)
    {
        using (var workbook = new Workbook(filePath))
        {
            var worksheet = workbook.Worksheets[0];
            
            // Clear existing data
            dataGridView1.Rows.Clear();
            dataGridView1.Columns.Clear();
            
            int rowCount = worksheet.GetRowCount();
            int colCount = worksheet.GetColumnCount();
            
            // Add columns to DataGridView
            for (int col = 1; col <= colCount; col++)
            {
                string headerText = worksheet.Cells[GetCellReference(1, col)].GetValue();
                if (string.IsNullOrEmpty(headerText))
                    headerText = $"Column {col}";
                    
                dataGridView1.Columns.Add($"Col{col}", headerText);
            }
            
            // Add rows to DataGridView
            for (int row = 2; row <= rowCount; row++)
            {
                string[] rowData = new string[colCount];
                for (int col = 1; col <= colCount; col++)
                {
                    string cellRef = GetCellReference(row, col);
                    rowData[col - 1] = worksheet.Cells[cellRef].GetValue();
                }
                dataGridView1.Rows.Add(rowData);
            }
            
            // Update status
            lblStatus.Text = $"Loaded {rowCount - 1} rows and {colCount} columns";
        }
    }
    
    static string GetCellReference(int row, int col)
    {
        return $"{GetColumnLetter(col)}{row}";
    }
    
    static string GetColumnLetter(int columnNumber)
    {
        string columnLetter = string.Empty;
        while (columnNumber > 0)
        {
            columnNumber--;
            columnLetter = (char)('A' + columnNumber % 26) + columnLetter;
            columnNumber /= 26;
        }
        return columnLetter;
    }
}

Contoh 3: Ekspor Data Formulir ke Excel saat Tombol Diklik

public partial class DataExportForm : Form
{
    // Button click to export data to Excel
    private void btnExportToExcel_Click(object sender, EventArgs e)
    {
        try
        {
            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "Excel Files|*.xlsx";
                saveFileDialog.Title = "Save Excel File";
                saveFileDialog.FileName = "ExportedData.xlsx";
                
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    ExportDataToExcel(saveFileDialog.FileName);
                    MessageBox.Show($"Data exported successfully to:\n{saveFileDialog.FileName}", "Export Complete");
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error exporting data: {ex.Message}", "Export Error");
        }
    }
    
    private void ExportDataToExcel(string filePath)
    {
        using (var workbook = new Workbook())
        {
            var worksheet = workbook.Worksheets[0];
            worksheet.Name = "Exported Data";
            
            // Export form data
            worksheet.Cells["A1"].PutValue("Name");
            worksheet.Cells["B1"].PutValue("Email");
            worksheet.Cells["C1"].PutValue("Phone");
            worksheet.Cells["D1"].PutValue("Department");
            
            // Get data from form controls
            worksheet.Cells["A2"].PutValue(txtName.Text);
            worksheet.Cells["B2"].PutValue(txtEmail.Text);
            worksheet.Cells["C2"].PutValue(txtPhone.Text);
            worksheet.Cells["D2"].PutValue(cboDepartment.Text);
            
            // Export DataGridView data if available
            if (dataGridView1.Rows.Count > 0)
            {
                // Add headers from DataGridView
                for (int col = 0; col < dataGridView1.Columns.Count; col++)
                {
                    worksheet.Cells[GetCellReference(4, col + 1)].PutValue(dataGridView1.Columns[col].HeaderText);
                }
                
                // Add data rows
                for (int row = 0; row < dataGridView1.Rows.Count; row++)
                {
                    for (int col = 0; col < dataGridView1.Columns.Count; col++)
                    {
                        var cellValue = dataGridView1.Rows[row].Cells[col].Value?.ToString() ?? "";
                        worksheet.Cells[GetCellReference(row + 5, col + 1)].PutValue(cellValue);
                    }
                }
            }
            
            workbook.Save(filePath);
        }
    }
}

Contoh 4: Lengkapi Formulir Manajer Excel

public partial class ExcelManagerForm : Form
{
    private string currentFilePath = "";
    
    public ExcelManagerForm()
    {
        InitializeComponent();
        UpdateButtonStates();
    }
    
    // Open Excel file
    private void btnOpen_Click(object sender, EventArgs e)
    {
        using (OpenFileDialog ofd = new OpenFileDialog())
        {
            ofd.Filter = "Excel Files|*.xlsx;*.xls";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                currentFilePath = ofd.FileName;
                LoadExcelFile();
                UpdateButtonStates();
            }
        }
    }
    
    // Create new Excel file
    private void btnNew_Click(object sender, EventArgs e)
    {
        try
        {
            using (var workbook = new Workbook())
            {
                var worksheet = workbook.Worksheets[0];
                worksheet.Name = "New Sheet";
                
                // Add some default content
                worksheet.Cells["A1"].PutValue("Column 1");
                worksheet.Cells["B1"].PutValue("Column 2");
                worksheet.Cells["C1"].PutValue("Column 3");
                
                using (SaveFileDialog sfd = new SaveFileDialog())
                {
                    sfd.Filter = "Excel Files|*.xlsx";
                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        workbook.Save(sfd.FileName);
                        currentFilePath = sfd.FileName;
                        LoadExcelFile();
                        UpdateButtonStates();
                        MessageBox.Show("New Excel file created!", "Success");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error creating file: {ex.Message}", "Error");
        }
    }
    
    // Save changes to Excel file
    private void btnSave_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(currentFilePath))
        {
            MessageBox.Show("No file is currently open.", "No File");
            return;
        }
        
        try
        {
            SaveDataGridToExcel();
            MessageBox.Show("File saved successfully!", "Saved");
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error saving file: {ex.Message}", "Save Error");
        }
    }
    
    private void LoadExcelFile()
    {
        try
        {
            using (var workbook = new Workbook(currentFilePath))
            {
                var worksheet = workbook.Worksheets[0];
                
                // Load data into DataGridView
                dataGridView1.Rows.Clear();
                dataGridView1.Columns.Clear();
                
                int rowCount = worksheet.GetRowCount();
                int colCount = worksheet.GetColumnCount();
                
                // Add columns
                for (int col = 1; col <= colCount; col++)
                {
                    string header = worksheet.Cells[GetCellReference(1, col)].GetValue();
                    dataGridView1.Columns.Add($"Col{col}", string.IsNullOrEmpty(header) ? $"Column {col}" : header);
                }
                
                // Add rows
                for (int row = 2; row <= rowCount; row++)
                {
                    string[] rowData = new string[colCount];
                    for (int col = 1; col <= colCount; col++)
                    {
                        rowData[col - 1] = worksheet.Cells[GetCellReference(row, col)].GetValue();
                    }
                    dataGridView1.Rows.Add(rowData);
                }
                
                lblFileName.Text = Path.GetFileName(currentFilePath);
                lblStatus.Text = $"Loaded: {rowCount - 1} rows, {colCount} columns";
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error loading file: {ex.Message}", "Load Error");
        }
    }
    
    private void SaveDataGridToExcel()
    {
        using (var workbook = new Workbook(currentFilePath))
        {
            var worksheet = workbook.Worksheets[0];
            
            // Save headers
            for (int col = 0; col < dataGridView1.Columns.Count; col++)
            {
                worksheet.Cells[GetCellReference(1, col + 1)].PutValue(dataGridView1.Columns[col].HeaderText);
            }
            
            // Save data
            for (int row = 0; row < dataGridView1.Rows.Count; row++)
            {
                for (int col = 0; col < dataGridView1.Columns.Count; col++)
                {
                    var value = dataGridView1.Rows[row].Cells[col].Value?.ToString() ?? "";
                    worksheet.Cells[GetCellReference(row + 2, col + 1)].PutValue(value);
                }
            }
            
            workbook.Save();
        }
    }
    
    private void UpdateButtonStates()
    {
        bool hasFile = !string.IsNullOrEmpty(currentFilePath);
        btnSave.Enabled = hasFile;
        btnProcess.Enabled = hasFile;
    }
}

Praktik Terbaik

  1. Selalu Gunakan Try-Catch: Tangani operasi file dengan aman
  2. Validasi Jalur File: Periksa apakah file ada sebelum diproses
  3. Umpan Balik Pengguna: Menampilkan kemajuan operasi jangka panjang
  4. Aktifkan/Nonaktifkan Tombol: Perbarui status UI berdasarkan konteks
  5. Filter File: Gunakan filter yang sesuai dalam dialog

Kesimpulan

Mengintegrasikan operasi Excel dengan klik tombol Windows Forms menyediakan fungsionalitas desktop yang canggih. SDK Openize.OpenXML memudahkan untuk:

  • ✅ Buka file Excel melalui dialog
  • ✅ Memproses dan menampilkan data Excel
  • ✅ Ekspor data formulir ke Excel
  • ✅ Buat antarmuka manajemen Excel yang lengkap Mulailah membangun aplikasi Windows Forms profesional berbasis Excel hari ini!