Wednesday, September 11, 2013

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)
                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())

            /*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;

                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;


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

No comments:

Post a Comment

Comments will appear once they have been approved by the moderator