Excel Interop Code Examples

Writing code in C# with Excel Interop.

General

All code examples begin and end with this code.

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application app = new Excel.Application(); // Create Excel object instance
app.DisplayAlerts = false; // Don't display any Excel prompts
Excel.Workbook wb = app.Workbooks.Open(input_filepath); // Create workbook instance

[ANY CODE EXAMPLE]

wb.Save(); // Save workbook
wb.Close(); // Close workbook
app.Quit(); // Quit Excel application

Find if spreadsheet has sheets

int count = wb.Worksheets.Count;
if (count = 0)
{
    Console.WriteLine("--> Spreadsheet has no sheets");
}

Find if any cell values exist

int used_cells_count = 0;
foreach (Excel.Worksheet sheet in wb.Sheets)
{
    try
    {
        Excel.Range range = (Excel.Range)sheet.UsedRange;
        foreach (Excel.Range cell in range.Cells)
        {
            var value = cell.Value2;
            if (value != null)
            {
                used_cells_count++;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException) // Catch if cell has no value
    {
        // Do nothing
    }
    finally
    {
        if (used_cells_count == 0)
        {
            Console.WriteLine("--> No cell values detected. Exempt spreadsheet from archiving");
        }
    }
}

Find and remove data connections

int count_conn = wb.Connections.Count;
if (count_conn > 0)
{
    for (int i = 1; i <= wb.Connections.Count; i++)
    {
        wb.Connections[i].Delete();
        i = i - 1;
    }
    count_conn = wb.Connections.Count;
}

Find and replace external cell chains with their cell values

This code finds and replaces the formula of cells with chains with the values. It does not remove the registry of the external chain, which causes an error when converting the file to .ods.

foreach (Excel.Worksheet sheet in wb.Sheets)
{
    try
    {
        Excel.Range range = (Excel.Range)sheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
        foreach (Excel.Range cell in range.Cells)
        {
            var value = cell.Value2;
            string formula = cell.Formula.ToString();
            string hit = formula.Substring(0, 2); // Transfer first 2 characters to string
            if (hit == "='")
            {
                cell.Formula = "";
                cell.Value2 = value;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException) // Catch if no formulas in range
    {
        // Do nothing
    }
}

Alternative method below. However it causes same error and supposedly generates errors if the external chain is used in certain formulas i.e. SUMIF. It can be repaired by creating a named range with value 1 and adding "*1" to the end of formulas. The code example below does not include this.

Array links = (Array)((object)wb.LinkSources());
if (links != null)
{
    foreach (string link in (Array)links)
    {
        wb.BreakLink(link, Excel.XlLinkType.xlLinkTypeExcelLinks);
    }
}

Remove information from file property details

if (wb.Author != "" || wb.Title != "" || wb.Subject != "" || wb.Keywords != "" || wb.Comments != "")
{
    Console.WriteLine("--> Removed file property details");
}
wb.Author = ""; // Remove author information
wb.Title = ""; // Remove title information
wb.Subject = ""; // Remove subject information
wb.Keywords = ""; // Remove keywords information
wb.Comments = ""; // Remove comments information

Find and replace RTD functions with cell values

foreach (Excel.Worksheet sheet in wb.Sheets)
{
    try
    {
        Excel.Range range = (Excel.Range)sheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
        foreach (Excel.Range cell in range.Cells)
        {
            var value = cell.Value2;
            string formula = cell.Formula.ToString();
            string hit = formula.Substring(0, 4); // Transfer first 4 characters to string
            if (hit == "=RTD")
            {
                cell.Formula = "";
                cell.Value2 = value;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException) // Catch if no formulas in range
    {
        // Do nothing
    }
}

Find OLE objects

foreach (Excel.Worksheet sheet in wb.Sheets) // Loop over all sheets
{
    Excel.OLEObjects ole = (Excel.OLEObjects)sheet.OLEObjects(); // Find OLE objects in sheet
    int ole_count = ole.Count; // Count OLE objects in sheet
    if (ole_count > 0) // If any
    {
        Console.WriteLine($"--> {ole_count} OLE objects detected in sheet {sheet.Name}. Extract objects manually.");
    }
}

Convert to .xlsx Strict

wb.SaveAs(filepath, 61); // Save workbook as .xlsx Strict
wb.Save(); // You can delete this line, you do not need to save again

If the spreadsheet has embedded files (OLE objects), then the above code will not transform the namespaces of the embedded objects from Transitional to Strict namespaces. This should be considered a bug in Excel.