Erase all names in Excel workbooks in C #

1 minute read

Motivation

When I copy an old Excel file and use it, it sometimes appears Colle is too annoying, C # I made a software to delete the “name” of the Excel workbook from the application.

When erasing manually

image.png

image.png

You can’t select all, and you end up erasing one by one. If there are many, stress will be great …

Really MS Office is shit ...

Notes

If you delete the “name” carelessly, formulas and macros may not work.
Even if you are not using it in the Excel itself, it is possible that other tools are designed to use the “name” in the Excel file.

Source code


using System;
using System.Drawing;
using System.Runtime.CompilerServices; // to use [MethodImpl(MethodImplOptions.NoInlining)]
using System.Runtime.InteropServices;
//using System.Text.RegularExpressions;
using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Core;

class ExcelNameRemover : Form
{
    Button btnStartTryGet;


    [MethodImpl(MethodImplOptions.NoInlining)]
    void TryGetActiveBook()
    {
        try {
            var oExcelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            
            if (oExcelApp == null) {return;}

            dynamic book;
            book = oExcelApp.ActiveWorkbook;

            var res = MessageBox.Show("Are you sure to delete all names in the excel: \"" + book.Name+"\"", "Confirmation", MessageBoxButtons.OKCancel );
            if (res == DialogResult.OK ) {
                dynamic names = book.Names;
                int count=0;
                foreach(dynamic name in names){
                    name.Delete();
                    count++;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
                Console.WriteLine(count.ToString() + " names are deleted.");
            }
        }
        catch(Exception e) {
            if (e is Microsoft.CSharp.RuntimeBinder.RuntimeBinderException ||
                e is COMException ) {
                
                Console.WriteLine(e);
                //Flush
                return;
            }
            throw e;
        }
        finally {
        }
    }


    ExcelNameRemover()
    {
        Controls.Add(btnStartTryGet = new Button(){
            Text = "Remove all names",
            Location = new Point(0, 0),
            Width = 200
        });
        btnStartTryGet.Click += (s,e)=>{
            TryGetActiveBook();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        };
    }

    [STAThread]
    static void Main(string[] args)
    {
        //DumpTextOfActiveSheet();
        Application.Run(new ExcelNameRemover());
    }
}

How to compile

  • I think the dll path depends on the environment.

compile.bat



csc ^
 /r:C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll ^
 /r:C:\Windows\assembly\GAC_MSIL\office\15.0.0.0__71e9bce111e9429c\Office.dll ^
 %*

compile.bat filename.cs
You can compile with.

You May Also Enjoy