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

excel desktop

← PrevNext →

Tools you can use

You must be aware of the AutoFormat feature in Excel, which allow users to format a sheet (or a particular range) with various formatting options. Don’t worry if you have not come across it yet, because 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 sheet. Along with this, I’ll also show you how to populate a WinForms CheckedListBox control using data extracted from a database.

Click this link, if you are a VB Developer.

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

Finally, let's add the C# code.

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#

Click this link, if you are a VB Developer.

That’s it. Please let me know if this example helped you in any way. If you have any queries or suggestions regarding this article and its example, please let me know. Leave your message below.

Thanks for reading ☺.

← PreviousNext →