How can I remove empty columns in spreadsheet

[dczen]

Hello.

I am looping through files in a folder and need to remove empty columns of a spreadsheet in a given range of A-G (at least any column between these ranges may be blank). I eventually want to do the same thing with the rows. I have revised my code, but am getting a nullException error. How can I properly check if the column is empty and then remove it? I don't want to open the files as this will become an overnight process. Please advise. Thanks in advance.

class removeEmpty
    {
        public void removeEmptyCols()
        {
            Microsoft.Office.Interop.Excel.Application oXL = null;
            Microsoft.Office.Interop.Excel.Workbook oWB = null;
            Microsoft.Office.Interop.Excel.Worksheet oWS = null;
            Excel.Range oRng = null;

            int numSheets = oWB.Sheets.Count;

            for (int sheetnum = 1; sheetnum < numSheets + 1; sheetnum++)
            {
                oWS = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[sheetnum];
                oRng = oWS.get_Range("A2", "G64000");

                oRng.EntireColumn.Delete(Missing.Value);
            }
            Console.WriteLine("Successfully removed columns");
        }
    }

in my overall program, I am looping through the directory in a transaction:

using (TransactionScope tx = new TransactionScope())
                {
                    removeEmpty em = new removeEmpty(); 
foreach (string myfile in excelFiles)
                    {
                        //remove empty columns
                         em.removeEmptyCols();
}
}

[Lusiphur]

One thing I can think of here that might be causing your issue is this...

When you delete a column, all subsequent columns are renumbered to reflect the now missing column no longer being there.

Two possible solutions exist:Delete from highest column number to lowest column number
Loop your process such that each time a column is deleted the 'empty check' occurs again and gives the new column number for the next empty column.
Otherwise if, for example, you had empty columns # 2, 5, 7 and full columns ranging from 1-15 (excluding those 3)... Deleting column 2 pushes everything down a number making the remaining 'empty' columns 4 and 6 instead. Deleting 4 moves it again making what was originally 7 now 5.

Conversely, if you start from highest to lowest the column renumbering doesn't affect the other 'empty' columns and no loop/recheck is required.

Hope this helps :) Please remember to mark the thread solved once your issue is resolved.

[nick.crane]

Nowhere are you setting the values for the objects oXL and oWB.
I suspect the null exception is due to this.

You cannot manipulate an excel file without opening it.

Your removeEmptyCols method does not open any files nor does it even know which file it is working on.

You should pass the myfile string in to removeEmptyCols as a parameter to allow it to open the file and edit it appropriately.

Take a look at this link on using excel-interop.
http://dotnetperls.com/excel-interop

[Lusiphur]

Nowhere are you setting the values for the objects oXL and oWB. I suspect the null exception is due to this.

heh, I assumed he did that elsewhere and hadn't included the code... my bad :(

[dczen]

Hello. Thanks for your reply. as the process of looping through the directory will be automated, is opening the excel always necessary? i am imaging all of the files in the directory opening. Or is this completely off.

[nick.crane]

In order to make changes to any file (Excel or otherwise) you have to open the file.
To edit an Excel file in C# you will need to start the Excel interop application and open each file in turn.
If you organise your app correctly you should only need to start Excel interop once for all the files.
If you use ReleaseComObject to relese each file (like in the link I posted) resources should not be an issue; only execution time.
Hope this helps.

[dczen]

Ok. I worked on simplifying the application and I still get a comm error.

System.Runtime.InteropServices.COMException (0x800A01A8): Exception from HRESULT: 0x800A01A8
at System.RuntimeType.ForwardCallToInvokeMember(String

i am focusing on removing the columns that are empty between column "A" and "G".

public void removeEmptyCols()
        {
            //instantiate a new 
            Excel.Application oXL = new Excel.ApplicationClass();
            oXL.Visible = false;

            //open workbook
            string workbookPath = @"C:\Inetpub\wwwroot\BALAReporting\InitialReports\RapidCue\TestDocs\11012009_12282009.xls";

            Excel.Workbook oWB = oXL.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            Excel.Sheets oSheets = oWB.Worksheets;

           
            string currentSheet = "Sheet1";
            Excel.Worksheet oWS = (Excel.Worksheet)oSheets.get_Item(currentSheet); 

            Excel.Range oRng = (Excel.Range)oWS.get_Range("B1", "G64000"); 

            try
            {
                for (int i = 0; i < oRng.Count; i++)
                {
                    //oRng.EntireColumn.Delete(Missing.Value);
                    oRng.Delete(Missing.Value);
                }

                //close excel objects
                oWB.Close(true, null, null); 

                oXL.Workbooks.Close();
                oXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

                Console.Write("Successfully removed columns " + oRng);
            }
            catch (Exception ohno)
            {
                String sSource = "Error Removing Columns";
                //String sLog ="Application";

                string message = Convert.ToString(ohno);

                EventLog.WriteEntry(sSource, message);
            }
        
        }