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, Password: "'", WriteResPassword: "'", IgnoreReadOnlyRecommended: true, Notify: false); // Create workbook instance

[ANY CODE EXAMPLE]

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

// If run on Windows release Excel from task manager
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
    Marshal.ReleaseComObject(wb); // Delete workbook task in task manager
    Marshal.ReleaseComObject(app); // Delete Excel task in task manager
}

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 references with cell values

This code finds and replaces the cell reference formula with the cell values. It does not remove the registry of the external cell reference, 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

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.");
    }
}

Make first sheet the active sheet

if (app.Sheets.Count > 0)
{
    Excel.Worksheet firstSheet = (Excel.Worksheet)app.ActiveWorkbook.Sheets[1];
    firstSheet.Activate();
    firstSheet.Select();
}

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.