Interview Series – Excel Operations

Microsoft Excel is the world’s most widely-used SpreadSheet software. Doing operations in them through C# code is central for many applications, including Data Analytics.

Here in this setup of an Interview, we’ll see how to do Bulk-Insertion of Data-Sets, Bulk-Coloring by Conditional Formatting and Charting Data-Sets.


We start-off by adding the Interop Excel dll into our application. This enables us to launch through C#, an Excel application having a Workbook with any number of worksheets.

When it comes to inserting data, we can either insert values into Excel Cell-by-Cell or bind a Datasource into Excel. Cell-by-Cell insertion is a costly operation because the C# code continuously sends data as “packets” that needs to be “caught” by Excel. Bulk insertion is a one-time affair that happens in a single shot.

In the Interview, we demonstrate how to bulk-insert data as a whole and how to bulk-insert data according to their data-types (Here, string & double.) There are merits and demerits to both approaches.

Next, we look into “Conditional Formatting”; a cool feature in Microsoft Excel. We can trigger this feature using C# code to apply background colours to cells that have specific ranges of values. Like before, there’s a way to apply background colour either Cell-by-Cell or do bulk-colouring. There are different colourization techniques adopted by Excel and C#. This difference forces us to write our custom class that bridges this gap.

Finally, we wind up the Interview by Charting our data-set. We can align the chart(s) by precise measurements and generate any type of chart that’s chartable in Excel. This feature is key to making effective analysis of Raw Data.

Please check out the 7 video-links where I go through this simulated Interview in much more detail.

C# Experiments: Excel Operations (Part 1)

C# Experiments: Excel Operations (Part 2)

C# Experiments: Excel Operations (Part 3)

C# Experiments: Excel Operations (Part 4)

C# Experiments: Excel Operations (Part 5)

C# Experiments: Excel Operations (Part 6)

C# Experiments: Excel Operations (Part 7)

The code typed-in during the interview series is as follows for your reference:-

    ExcelApp.Application excelApp = new ExcelApp.Application();
    Workbook wb = excelApp.Workbooks.Add(XlSheetType.xlWorksheet);
    Worksheet ws = excelApp.ActiveSheet;

    excelApp.Visible = true;

    var lifeExpectancies = new Dictionary<string, double>()
    {
        {"Kenya", 58.1},
        {"United States", 78.8},
        {"South Africa", 54.0},
        {"Russia", 69.3},
        {"Brazil", 74.1},
        {"India", 66.1},
    };

    //ws.Cells[1, 1] = "Kenya";

    //string[,] rangeArray = new String[lifeExpectancies.Count, 2];

    string[,] textRangeArray = new String[lifeExpectancies.Count, 1];
    double[,] numberRangeArray = new Double[lifeExpectancies.Count, 1];

    int counter = 0;

    foreach (var keyValueSet in lifeExpectancies)
    {
        //rangeArray[counter, 0] = keyValueSet.Key;
        //rangeArray[counter, 1] = keyValueSet.Value;

        textRangeArray[counter, 0] = keyValueSet.Key;
        numberRangeArray[counter, 0] = keyValueSet.Value;

        counter++;
    }

    Range r1 = ws.Cells[1, 1];
    Range r2 = ws.Cells[textRangeArray.GetLength(0), textRangeArray.GetLength(1)];

    Range range = ws.get_Range(r1, r2);
    range.Value = textRangeArray;

    r1 = ws.Cells[1, r2.Column + 1];
    r2 = ws.Cells[numberRangeArray.GetLength(0), numberRangeArray.GetLength(1) + r2.Column];

    range = ws.get_Range(r1, r2);
    range.Value = numberRangeArray;

    int lessThanfiftyColor = ConvertColor(Color.IndianRed);
    int sixtiesColor = ConvertColor(Color.SkyBlue);
    int aboveSeventyColor = ConvertColor(Color.LimeGreen);

    FormatCondition condition = range.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlLess, 60);
    condition.Interior.Color = lessThanfiftyColor;

    condition = range.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlBetween, 60, 70);
    condition.Interior.Color = sixtiesColor;

    condition = range.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlGreater, 70);
    condition.Interior.Color = aboveSeventyColor;

    Range chartRange;

    ChartObjects xlCharts = ws.ChartObjects();
    ChartObject myChart = xlCharts.Add(50, 100, 300, 250);
    Chart chart = myChart.Chart;

    r1 = ws.Cells[1, 1];
    chartRange = ws.get_Range(r1, r2);
    chart.SetSourceData(chartRange, Type.Missing);
    chart.ChartType = XlChartType.xl3DColumn;



    public static int ConvertColor(Color colour)
    {
        return colour.R + (colour.G * 256) + (colour.B * 65536);
    }


Thank you for reading this post and watching the videos. Please Subscribe, Comment and Rate the Channel if you liked the video.

Goto C# Experiments to access more of such content! Thanks again!