Excel

This application creates XLS and XLSX files. These files are hard to read in C# programs. They are handled with the Microsoft.Office.Interop.Excel assembly.

This assembly sometimes creates performance issues. Step-by-step instructions are helpful. Excel interop is difficult, but with some care we can make it usable.

First example

We must include a namespace to use Excel in your C# program. We need to add an assembly to your program to use Excel interop—use the Add Reference command for this.

/// <summary>
/// This class contains the Excel Interop code we need.
/// It can be shared in many places to avoid duplication.
/// </summary>
class ExcelReaderInterop
{
    /// <summary>
    /// Store the Application object we can use in the member functions.
    /// </summary>
    Application _excelApp;

    /// <summary>
    /// Initialize a new Excel reader. Must be integrated
    /// with an Excel interface object.
    /// </summary>
    public ExcelReaderInterop()
    {
        _excelApp = new Application();
    }
}

Interop class

This class stores our Excel information. Put an Application object called _excelApp as a member. In the constructor, make the _excelApp a new Application object.

/// <summary>
/// Open the file path received in Excel. Then, open the workbook
/// within the file. Send the workbook to the next function, the internal scan
/// function. Will throw an exception if a file cannot be found or opened.
/// </summary>
public void ExcelOpenSpreadsheets(string thisFileName)
{
    try
    {
        //
        // This mess of code opens an Excel workbook. I don't know what all
        // those arguments do, but they can be changed to influence behavior.
        //
        Workbook workBook = _excelApp.Workbooks.Open(thisFileName,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);

        //
        // Pass the workbook to a separate function. This new function
        // will iterate through the worksheets in the workbook.
        //
        ExcelScanInternal(workBook);

        //
        // Clean up.
        //
        workBook.Close(false, thisFileName, null);
        Marshal.ReleaseComObject(workBook);
    }
    catch
    {
        //
        // Deal with exceptions.
        //
    }
}

Loop

To get data from workbooks, you will need to loop over the sheets. An Excel workbook has one or more sheets. In Excel, you can switch between sheets by clicking on the tabs on the bottom.

/// <summary>
/// Scan the selected Excel workbook and store the information in the cells
/// for this workbook in an object[,] array. Then, call another method
/// to process the data.
/// </summary>
private void ExcelScanInternal(Workbook workBookIn)
{
    //
    // Get sheet Count and store the number of sheets.
    //
    int numSheets = workBookIn.Sheets.Count;

    //
    // Iterate through the sheets. They are indexed starting at 1.
    //
    for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
    {
        Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];

        //
        // Take the used range of the sheet. Finally, get an object array of all
        // of the cells in the sheet (their values). You can do things with those
        // values. See notes about compatibility.
        //
        Range excelRange = sheet.UsedRange;
        object[,] valueArray = (object[,])excelRange.get_Value(
            XlRangeValueDataType.xlRangeValueDefault);

        //
        // Do something with the data in the array with a custom method.
        //
        ProcessObjects(valueArray);
    }
}

Performance

There are possible performance issues with Excel interop. The Cells indexer on a range, or getting the range with the Range property can be slow.

Cells[]:                30.0 seconds
get_Range(), Cells[]:   15.0 seconds
UsedRange, get_Value():  1.5 seconds [fastest]

Charts

It is possible to create charts directly in your Excel workbooks. The ChartWizard method is useful for this. We use the ChartObjects property and Add a Chart.

using Microsoft.Office.Interop.Excel;

class Program
{
    const string fileName = "C:\\Book1.xlsx";
    const string topLeft = "A1";
    const string bottomRight = "A4";
    const string graphTitle = "Graph Title";
    const string xAxis = "Time";
    const string yAxis = "Value";

    static void Main()
    {
        // Open Excel and get first worksheet.
        var application = new Application();
        var workbook = application.Workbooks.Open(fileName);
        var worksheet = workbook.Worksheets[1] as
            Microsoft.Office.Interop.Excel.Worksheet;

        // Add chart.
        var charts = worksheet.ChartObjects() as
            Microsoft.Office.Interop.Excel.ChartObjects;
        var chartObject = charts.Add(60, 10, 300, 300) as
            Microsoft.Office.Interop.Excel.ChartObject;
        var chart = chartObject.Chart;

        // Set chart range.
        var range = worksheet.get_Range(topLeft, bottomRight);
        chart.SetSourceData(range);

        // Set chart properties.
        chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
        chart.ChartWizard(Source: range,
            Title: graphTitle,
            CategoryTitle: xAxis,
            ValueTitle: yAxis);

        // Save.
        workbook.Save();
    }
}

The program requires an Excel document named Book1.xlsx located at C:\Book1.xlsx. In this file, you need to add 4 values in the first column.

Compatibility

There is a compatibility issue with the Excel program on the macOS platform. Excel stores OA dates in different formats on Macs and PCs.

We used Excel interop and avoided performance problems. It is confusing to call the complicated Open method with Type.Missing in more places than necessary.