Windows Forms App - Export Data to an Excel Sheet with AutoFormat Feature using C#

You must be aware of the AutoFormat feature in Excel: it allow users to format a worksheet (or a particular range) with various formatting options. Here in this article, I’ll show you how to export data to an Excel sheet in a Windows Forms application using C# and apply AutoFormat feature dynamically to the worksheet. In-addition, I’ll also show you how to populate a WinForms CheckedListBox control using data extracted from a database.

If you are a VB Developer, see this link.

To get access to Excel and AutoFormat features in your WinForms application, you’ll have to add the Interop namespace to your application.

using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;

You will have to add the above namespaces in the beginning of your application. However, to get started, first create a reference to the Excel library.

To add a reference, click "Project" from the top menu list in your .Net IDE and select Add Reference… option. This will open the "Add Reference" window. Select the COM tab in the window, find and select "Microsoft Excel 12.0 Object Library" from the list. Click OK.

Windows Forms Add Reference for Excel

Next, we’ll create a form by adding few controls such as a CheckBox, a CheckedListBox and a Button. We’ll first fill the CheckedListBox dynamically with some unique data in it. For that you will have to create two tables in your SQL Server database. I have already created these tables for you. Use it.

Employee table: dbo.Employee
Employee detail table: dbo.EmployeeDetails

Your form design will look like this.

Windows Forms Design

The C# Code for Exporting Data to Excel

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Windows.Forms;

using System.Data;
using System.Data.SqlClient;                        // FOR SQL CONNECTION AND COMMAND.

using Excel = Microsoft.Office.Interop.Excel;       // EXCEL APPLICATION.
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;       // TO AUTOFORMAT THE SHEET.

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        // SET CONNECTION STRING.
        string sCon = "Data Source=DNA;Persist Security Info=False;" + 
            "Integrated Security=SSPI;" +
            "Initial Catalog=DNA_Classified;" +
            "User Id=sa;Password=demo;Connect Timeout=30;";

        DataSet ds = new DataSet();

        string sSql = "";
        int iRowCnt = 0;        // JUST A COUNTER.

        public Form1()
        { InitializeComponent(); }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(sCon))
            {
                sSql = "SELECT EmpName FROM dbo.Employee";

                SqlDataAdapter sda = new SqlDataAdapter(sSql, con);
                try {
                    sda.Fill(ds, "tabEmployees");
                    FillListbox();
                }
                catch (Exception ex) {
                    MessageBox.Show(ex.Message, "Connection Error", 
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally {
                    sSql = "";
                }
            }
        }

        private void FillListbox()
        {
            // POPULATE THE "CheckedListBox" WITH DYNAMIC DATA.
            // YOU WILL HAVE OPTION TO EITHER EXPORT ALL EMPLOYEES DATA OR SELECTED DATA.

            lstEmpDet.Items.Clear();

            foreach (DataRow row in ds.Tables["tabEmployees"].Rows)
            {
                lstEmpDet.Items.Add(ds.Tables["tabEmployees"].Rows[iRowCnt][0]);
                iRowCnt = iRowCnt + 1;
            }
        }

        private void chkAll_CheckedChanged(object sender, EventArgs e)
        {
            // CHECK/UN-CHECK EMPLOYEES FROM THE CheckedListBox.

            Cursor.Current = Cursors.WaitCursor;
            if (chkAll.Checked)
            {
                for (var iCnt = 0; iCnt <= lstEmpDet.Items.Count - 1; iCnt++)
                {
                    lstEmpDet.SetItemCheckState(iCnt, CheckState.Checked);
                    lstEmpDet.SetSelected(iCnt, true);
                }
                chkAll.Text = "Unselect All";
            }
            else
            {
                for (var iCnt = 0; iCnt <= lstEmpDet.Items.Count - 1; iCnt++)
                {
                    lstEmpDet.SetItemCheckState(iCnt, CheckState.Unchecked);
                    lstEmpDet.SetSelected(iCnt, false);
                }
                chkAll.Text = "Select all from the list";
            }
            Cursor.Current = Cursors.Default;
        }

        private void btExport_Click(object sender, EventArgs e)
        {
            // EXPORT EMPLOYEE DETAILS TO EXCEL.

            Cursor.Current = Cursors.WaitCursor;

            string sEmpList = "";

            // FIRST CHECK IF ANY EMPLOYEES ARE SELECTED.
            for (var iCnt = 0; iCnt <= lstEmpDet.CheckedItems.Count - 1; iCnt++)
            {
                if (string.IsNullOrEmpty(sEmpList.Trim()))
                {
                    sEmpList = "'" + lstEmpDet.CheckedItems[iCnt] + "'";
                }
                else
                {
                    sEmpList = sEmpList + ", '" + lstEmpDet.CheckedItems[iCnt] + "'";
                }
            }

            using (SqlConnection con = new SqlConnection(sCon))
            {
                // SQL QUERY TO FETCH RECORDS FROM THE DATABASE.
                sSql = "SELECT *FROM dbo.EmployeeDetails " +
                    (!string.IsNullOrEmpty(sEmpList.Trim()) ? 
                        " WHERE EmpName IN (" + sEmpList.Trim() + ")" : "");

                SqlDataAdapter sda = new SqlDataAdapter(sSql, con);
                try
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    Excel.Application xlAppToUpload = new Excel.Application();
                    xlAppToUpload.Workbooks.Add();

                    Excel.Worksheet xlWorkSheetToUpload = default(Excel.Worksheet);
                    xlWorkSheetToUpload = xlAppToUpload.Sheets["Sheet1"];

                    // SHOW THE EXCEL SHEET. 
                    // SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL.
                    xlAppToUpload.Visible = true;              

                    if (dt.Rows.Count > 0)
                    {
                        iRowCnt = 4;                      // ROW AT WHICH PRINT WILL START.

                        // SHOW THE HEADER BOLD AND SET FONT AND SIZE.
                        xlWorkSheetToUpload.Cells[1, 1].value = "Employee Details";
                        xlWorkSheetToUpload.Cells[1, 1].FONT.NAME = "Calibri";
                        xlWorkSheetToUpload.Cells[1, 1].Font.Bold = true;
                        xlWorkSheetToUpload.Cells[1, 1].Font.Size = 20;

                        // MERGE CELLS OF THE HEADER.
                        xlWorkSheetToUpload.Range["A1:E1"].MergeCells = true;           

                        // SHOW COLUMNS ON THE TOP.
                        xlWorkSheetToUpload.Cells[iRowCnt - 1, 1].value = "Employee Name";
                        xlWorkSheetToUpload.Cells[iRowCnt - 1, 2].value = "Mobile";
                        xlWorkSheetToUpload.Cells[iRowCnt - 1, 3].value = "PresentAddress";
                        xlWorkSheetToUpload.Cells[iRowCnt - 1, 4].value = "Country";
                        xlWorkSheetToUpload.Cells[iRowCnt - 1, 5].value = "Email Address";

                        // NOW WRITE DATA TO EACH CELL.
                        for (var i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            xlWorkSheetToUpload.Cells[iRowCnt, 1].value = dt.Rows[i]["EmpName"];
                            xlWorkSheetToUpload.Cells[iRowCnt, 2].value = dt.Rows[i]["Mobile"];
                            xlWorkSheetToUpload.Cells[iRowCnt, 3].value = dt.Rows[i]["PresentAddress"];
                            xlWorkSheetToUpload.Cells[iRowCnt, 4].value = dt.Rows[i]["Country"];
                            xlWorkSheetToUpload.Cells[iRowCnt, 5].value = dt.Rows[i]["Email"];

                            iRowCnt = iRowCnt + 1;
                        }

                        // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                        xlAppToUpload.ActiveCell.Worksheet.Cells[4, 1].AutoFormat(
                            ExcelAutoFormat.xlRangeAutoFormatList2);
                        xlAppToUpload = null;
                    }
                }
                catch (Exception ex) {
                    MessageBox.Show(ex.Message, "You got an Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally {
                    sSql = "";
                }
            }
            Cursor.Current = Cursors.Default;
        }
    }
}

Output

The final output will show data formatted automatically. The formatting actually happens once all the data is exported to the sheet. Since, I have set the "visible" property to "true" early in code, you can clearly see the data while it writes in cell, like a typewriter.

Data Exported to Excel with AutoFormat in Windows Forms C#

See this if you are a VB Developer.

← PreviousNext →