c# create excel file (Simple beginner's example)

In this example I will read from a table in SQL server and write that into an Excel 2007 file. I will use EPPlus version 3.1 library to create and write into the Excel file.  To follow this example, add a reference to EPPlus.dll that comes with the download of  EPPlus version 3.1.

Create a table called ExcelUsers in your sql database which has three columns

CREATE TABLE [dbo].[ExcelUsers](

      [FirstName] [varchar](50) NULL,

      [LastName] [varchar](50) NULL,

      [Age] [int] NULL

) 

and add some data into that table

using System.Collections.Generic;

using System.Data.SqlClient;

using System.IO;

using OfficeOpenXml;

namespace CreateExcelFromDataTable

{

    public class ExcelUser

    {

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public int Age { get; set; }

    }

    internal class Program

    {

        private static void Main(string[] args)

        {

            string fileLocation = @"C:\Play\newExcelFile.xlsx";

            var users = new List<ExcelUser>();

            /*Read data from Database*/

            var conn =

                new SqlConnection(

                    @"data source=MyServer;initial catalog=MyDatabase;persist security info=True;Integrated Security=SSPI;");

            var cmd = new SqlCommand(@"SELECT * FROM [MyDatabase].[dbo].[ExcelUsers]", conn);

            using (conn)

            {

                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    users.Add(new ExcelUser

                        {

                            FirstName = reader["FirstName"].ToString(),

                            LastName = reader["LastName"].ToString(),

                            Age = int.Parse(reader["Age"].ToString())

                        });

                }

                reader.Close();

            }

            /*Write to excel*/

            var excelFile = new FileInfo(fileLocation);

            using (var package = new ExcelPackage(excelFile))

            {

                //Add a worksheet

                ExcelWorksheet ws = package.Workbook.Worksheets.Add("Users");

                ExcelWorkbook workBook = package.Workbook;

                //Headers

                ws.Cells["B1"].Value = "First Name";

                ws.Cells["C1"].Value = "Last Name";

                ws.Cells["D1"].Value = "Age";

                ws.Cells["B1:D1"].Style.Font.Bold = true;

                int i = 2;

                foreach (ExcelUser excelUser in users)

                {

                    ws.Cells["B" + i.ToString()].Value = excelUser.FirstName;

                    ws.Cells["C" + i.ToString()].Value = excelUser.LastName;

                    ws.Cells["D" + i.ToString()].Value = excelUser.Age;

                    i++;

                }

                package.Save();

            }

        }

    }

}

When you run this code a new excel file gets created at C:\Play.