Insert data into columns of Excel sheet from SQL Server table

Have you looked at using a C# library such as NPOI or EPPlus to modify your Excel sheet?

Are you familiar with Entity Framework and how to use NuGet to install it in your project? You'll want to use EF along with LINQ to retrieve the data in your SQL table. It all depends on the flow of your application and what you want to trigger the update of the excel sheet but you'll likely want to have an action method inside your controller that responds to user interaction and then accesses the db data and writes it to the sheet. Or are you just looking to do a one time extraction and load of the data from SQL Server to Excel? Is this going to be a web app or just a basic console app?

EDIT (EPPlus example):

Here's a simple example I just put together that shows how to modify an Excel sheet using EPPlus:

    var file = new FileInfo("C:\\Projects\\ConsoleApplication1\\test.xlsx");
    if (file.Exists)
    {
        using (ExcelPackage ep = new ExcelPackage(file))
        {
            var workSheet = ep.Workbook.Worksheets[1];
            workSheet.Cells[2, 4].Value = "test";
            ep.Save();
        }
    }

In this example, I open the Excel file using FileInfo from System.IO and then assuming the file exists, create a new ExcelPackage object and pass in the FileInfo object as a parameter. I then access the first worksheet and access the cell in the 2nd row, 4th column and set the value to "test". Finally, I save the changes. As you can see, it's very straightforward to use. If you need further clarification or an expanded example, let me know.

Just remember to include System.IO and OfficeOpenXml (after you've installed the EPPlus package from NuGet):

using OfficeOpenXml;
using System.IO;

EDIT (Entity Framework guidance):

Assuming you're not using .NET Core, since you already have an existing database I recommend you use the simpler Database First approach in Entity Framework 6 to query and retrieve the data out of your SQL Server db. Follow the instructions listed here to set up your Entity Data Model within your web app. Once you've created the EDMX file and have your controller with your action method created, you're ready to write a LINQ query to get the data. I don't know the particulars of your db schema or how much data you're going to need to fill a column or whether the user is going to supply input as part of the query so it makes it a bit difficult to provide a specific example, but I can show you a generic example and then hopefully can run with it from there. Here is a quick and dirty example which combines querying the database with writing the data to an Excel sheet. In this example, I'm querying for all the Blue cars in the Cars table and then writing the Make and Model of each car in the result set out to the Excel spreadsheet (starting in the first row). I increment the row count as I go through the result set and this assumes that the Make is in the first column and the Model is in the 2nd column of the sheet.

    var file = new FileInfo("C:\\Projects\\ConsoleApplication1\\test.xlsx");
    if (file.Exists)
    {
        using (CarEntities dc = new CarEntities())
        {
            var carList = from c in dc.Cars
                          where c.Color == "Blue"
                          select c;

            var rowCount = 1;

            using (ExcelPackage ep = new ExcelPackage(file))
            {
                foreach (var car in carList)
                {
                    var workSheet = ep.Workbook.Worksheets[1];
                    workSheet.Cells[rowCount, 1].Value = car.Make;
                    workSheet.Cells[rowCount, 2].Value = car.Model;

                    rowCount++;
                }

                ep.Save();
            }
        }
    }