Update Excel From C# Based on Column Name

Question

I am looking for a way to update an existing Excel spreadsheet with data from a SQL query via C# putting data in columns based on column header. For instance, if I have the following returned query/dataset

Width    Height    Length
  2        2          2     
  2        3          4
  3        4          5

And I have an Excel workbook like so:

Width    Height       Area      Length     Volume
                    =(A1*B1)              =(C1*D1)
                    =(A2*B2)              =(C2*D2)
                    =(A3*B3)              =(C3*D3)

I would like to insert Width, Length and Height into the workbook without affecting Area or Volume, i.e.:

Width    Height       Area      Length     Volume
  2        2        =(A1*B1)      2       =(C1*D1)
  2        3        =(A2*B2)      4       =(C2*D2)
  3        4        =(A3*B3)      5       =(C3*D3)

Is there a way to specify in code that the Width from the dataset should go in the Width column, etc.? I am currently using the EPPlus package to do Excel tasks.

If you know the column index and you are pulling values from Entity Framework why not 'Worksheet.Cells["B1"].Value = Entity.Height;' Where worksheet is the name of the worksheet and your 'Height' information is being stored in column B. — code, Jun 30 '15 at 17:41

score 0 · Answer 1 · edited May 23 '17 at 12:21

A couple of approaches for this 1. You can hard-code the Excel column name's index 2. You can resolve it and put it in a dictionary

I'm going to go with option 2 so it's easier for you. However a couple of assumptions.

You know how to get the Worksheet property of your application through Interop.Excel
You are able to specify the row where you start entering the data, and row of where all the column names are

Here's the code

using Microsoft.Office.Interop.Excel;

public void SyncData(Worksheet ws, DataTable dt, int startRow){
    //Get the columns and their corresponding indexes in excel
    Dictionary<string, int> columnMap = ExcelColumnResolver(ws, dt, 1);

    //The row number in excel youre starting to update from
    int currRow = startRow;

    //Iterate through the rows and the columns of each row
    foreach(DataRow row in dt.Rows){
        foreach(DataColumn column in dt.Columns){

            //Only update columns we have mapped
            if(columnMap.ContainsKey(column.ColumnName)){
                ws.Cells[currRow, columnMap[column.ColumnName]] = row[column.ColumnName];
            }
        }

        currRow++;
    }
}

//columnsRow = Row in which the column names are located (non-zero indexed)
public Dictionary <string, int> ExcelColumnResolver(Worksheet ws, DataTable dt, int columnsRow) {
    Dictionary<string, int> nameToExcelIdxMap = new Dictionary<string, int>();

    //The row in Excel that your column names are located
    int maxColumnCount = 10;

    //Excel cells start at index 1
    for (int i = 1; i < maxColumnCount; i++) {
        string col = ws.Cells[columnsRow, i].ToString();

        if (dt.Columns.Contains(col)){
            nameToExcelIdxMap[col] = i;
        }
    }

    return nameToExcelIdxMap;
}

Here's a tutorial on how you can access the Excel worksheet

Runtime is O(n^2), but for performance I would recommend:

Populating the data in an object array and using the Worksheet.Range property to set a group of cells, instead of individually updating the cells 1 by 1.
Parallel the writing of rows to the object array, since there are no dependencies between the rows

score 0 · Answer 2 · answered Jul 01 '15 at 17:06

Using EPPlus and assuming GetDataFromSql returns DataTable, you can use the following code:

var data = GetDataFromSql();

using (var excelPackage = new ExcelPackage(new FileInfo(@"C:\Proj\Sample\Book1.xlsx")))
{
    var worksheet = excelPackage.Workbook.Worksheets.First();

    // Get locations of column names inside excel:
    var headersLocation = new Dictionary<string, Tuple<int, int>>();
    foreach (DataColumn col in data.Columns)
    {
        var cell = worksheet.Cells.First(x => x.Text.Equals(col.ColumnName));
        headersLocation.Add(col.ColumnName, new Tuple<int, int>(cell.Start.Row, cell.Start.Column));
    }

    for (var i = 0; i < data.Rows.Count; i++)
    {
        foreach (DataColumn col in data.Columns)
        {
            // update the value
            worksheet.Cells[headersLocation[col.ColumnName].Item1 + i + 1,
                headersLocation[col.ColumnName].Item2
                ].Value = data.Rows[i][col];
        }
    }

    excelPackage.Save();
}

Update Excel From C# Based on Column Name

2 Answers2