Excel Interop Code Examples

Writing code in C# with Excel Interop.

All code examples begin and end with

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 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;
    Console.WriteLine("--> Data connections detected and removed");
}

Find and replace external cell chains with 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.

bool hasChain = false;
foreach (Excel.Worksheet sheet in wb.Sheets)
{
    Excel.Range range = (Excel.Range)sheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas); // Find range
    foreach (Excel.Range cell in range.Cells)
    {
        string address = cell.Address;
        var value = cell.Value2;
        string formula = cell.Formula.ToString();
        string hit = formula.Substring(0, 2); // Transfrer first 4 characters to string
        if (hit == "='")
        {
            hasChain = true;
            cell.Formula = "";
            cell.Value2 = value;
        }
    }
}
if (hasChain == true)
{
    Console.WriteLine("--> External cell chains detected and replaced with cell values"); // Inform user
}

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 author, subject and comments 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)
{
    Excel.Range range = (Excel.Range)sheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas); // Find range
    foreach (Excel.Range cell in range.Cells) // Loop each cell in range
    {
        var value = cell.Value2; // Store cell value
        string formula = cell.Formula.ToString(); // Transfer formula to string
        string hit = formula.Substring(0, 4); // Transfer first 4 characters of formula to string

        if (hit == "=RTD") // If formula begins with "=RTD"
        {
            cell.Formula = ""; // Delete formula
            cell.Value2 = value; // Write stored cell value to cell
        }
    }
}

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 (count_conn > 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.