Deleting Headers in an Excel Document using C#

The Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelPractice
{
    class Program
    {
        static void Main(string[] args)
        {            
            //string mySheet = "*Insert path of Excel file*"            
            
            //Initialize Excel object library
            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = false;

            //Opens up the worksheet and activates it
            Microsoft.Office.Interop.Excel.Workbook clsWorkbook = excelApp.Workbooks.Open
            (mySheet, 2, false, 5, "", "", true, 
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
            "", true, false, 0, false, true, 
            Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);

            Microsoft.Office.Interop.Excel.Worksheet clsWorksheet = 
                                (Excel.Worksheet)clsWorkbook.ActiveSheet;

            //The range is set to the range of rows that have information typed in
            Excel.Range range  = clsWorksheet.UsedRange;

            //The timestamp value is set to the time that the data is updated
            String timeStampValue = GetCell(range, 1, 1);//Grab time stamp
            
            //The reportName variable is set to an empty string            
            String reportName = string.Empty;//here insert report name 
            
            //Let's delete the heading except header line 1 now 
            //Delete 1  - Delete first Row 2 - 
            int rowsToBeDeleted = 0;            

            int rowIndex;
            rowIndex = 1;
            //Let's hardcode cell1 values for each report 

            //These variables will be used later in the code             
            string cellOneValue1 = string.Empty; string cellOneValue2 = 
                     string.Empty; string cellOneValueTimeStamp = string.Empty;
            string cellOneValue_Line1header = string.Empty; 
            string cellOneValue_Line2header = string.Empty;            

            cellOneValueTimeStamp=timeStampValue;//It will be same for all reports

            //This is a hard coded value
            //The reportName, may differ for your file, 
            //is set to the string "Inventory Master File"
           reportName = "Inventory Master File";

            //An if loop is used to add the values to the other strings 
            //if its condition is met
            if (reportName == "Inventory Master File")
            {
                //Will change according to your report 
                //and what values you would like to search
                cellOneValue1 = "Inventory Master File"; cellOneValue2 = "From Store";
                cellOneValue_Line1header = "Item"; cellOneValue_Line2header = "Number";
            }

            //Let's spin through the entire file and delete these rows with 
            //cell 1 found in any row except at the top
            rowIndex = FindRow(range, timeStampValue);

            //These functions will delete any rows with any of the values 
            //we assigned previously
            DeleteRowStartingWithString(clsWorksheet, timeStampValue);
            DeleteRowStartingWithString(clsWorksheet, cellOneValue1);
            DeleteRowStartingWithString(clsWorksheet, cellOneValue2);
            DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line1header);
            DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line2header);

            //Save the Excel sheet, then exit the worksheet
            clsWorkbook.Save();
            clsWorkbook.Close(true);// Type.Missing, Type.Missing, Type.Missing);
            excelApp.Application.Quit();        
        }
        //This is our delete rows function
        static void DeleteRowStartingWithString
            (Excel.Worksheet clsWorksheet, string rowStartingWithString)
        {
            int rowIndex =1;
            Excel.Range range  = clsWorksheet.UsedRange;

            //Will begin deleting the rows passed into this function 
            //until there are no rows with the string left
            if (rowStartingWithString.Length > 0)
            {
                while (rowIndex >= 1)// Let's spin through the entire spreadsheet 
                                     // till all rows are deleted 
                {
                    if (rowIndex >= 1) 
                    {
                        {
                            ((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
                        }
                        
                    };
                    rowIndex = FindRow(range, rowStartingWithString);
                }
            }
        }
        
        //This function gets the value of the string in the specified row and column
        static String GetCell(Excel.Range range, int row, int column)
        {
            try
            {
                if ((row >= 0) && (column >= 0))
                {
                    Object obj = new object();
                    obj = range.Cells[row, column].Value;
                    if (obj == null) { obj = (String)""; }
                    return obj.ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception ex)
            {
                return string.Empty;
            }
        }
        //This function will get the row of the marker string
        static int FindRow(Excel.Range range, String marker)
        {
            int row = -1;
            try
            {
                row = range.Find(marker).Row;
                return row;
            }
            catch (Exception ex)
            {
                row = -1;
                return row;
            }
        }              
    }
}

Explanation of the Code

Before we start, we need to import our libraries:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

Next, we want to set the mySheet variable to the path of your Excel sheet, commented out, and we will initialize our Excel object library.

namespace ExcelPractice
{
    class Program
    {
        static void Main(string[] args)
        {            
            //string mySheet = "*Insert path of Excel file*"           
            
            //Initialize Excel object library
            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = false;

We're going to open up to the worksheet and activate it using:

Microsoft.Office.Interop.Excel.Workbook clsWorkbook = excelApp.Workbooks.Open
(mySheet, 2, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"", true, false, 0, false, true,
Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);

Microsoft.Office.Interop.Excel.Worksheet clsWorksheet =
                     (Excel.Worksheet)clsWorkbook.ActiveSheet;

Now that the worksheet is active, we can get the range of rows that have data in them (including headers):

//The range is set to the range of rows that have information typed in
Excel.Range range  = clsWorksheet.UsedRange;

At this point, we have all the pre-requisites for this code besides our variables. We will initialize these now.

//The timestamp value is set to the time that the data is updated
String timeStampValue = GetCell(range, 1, 1);//Grab time stamp

//The reportName variable is set to an empty string
String reportName = string.Empty;//here insert report name

//Let's delete the heading except header line 1 now
//Delete 1  - Delete first Row 2 -
int rowsToBeDeleted = 0;

int rowIndex;
rowIndex = 1;
//Let's hardcode cell1 values for each report

//These variables will be used later in the code
string cellOneValue1 = string.Empty; string cellOneValue2 =
    string.Empty; string cellOneValueTimeStamp = string.Empty;
string cellOneValue_Line1header =
    string.Empty; string cellOneValue_Line2header = string.Empty;

cellOneValueTimeStamp=timeStampValue;//It will be the same for all reports

*Note* The timestamp value is for reports that include the date and time the data set is entered. If your dataset does not have a timestamp value, you will need to delete this value.

Next, we want to assign values to the variables we created. However, this is the point in the code where you will want to check with your own Excel spreadsheet and make sure that you have these strings in your own sheet. If there are different values, make sure to change them to the appropriate string that you want this code to scan for!

//This is a hard coded value
//The reportName, may differ for your file,
//is set to the string "Inventory Master File"
reportName = "Inventory Master File";

//An if loop is used to add the values to the other strings
//if its condition is met
if (reportName == "Inventory Master File")
{
    //Will change according to your report and
    //what values you would like to search
    cellOneValue1 = "Inventory Master File"; cellOneValue2 = "From Store";
    cellOneValue_Line1header = "Item"; cellOneValue_Line2header = "Number";
}

//Let's spin through the entire file and delete these rows with cell 1
//found in any row except at the top
rowIndex = FindRow(range, timeStampValue);

With our variables correctly assigned, we can move onto deleting them from the Excel spreadsheet.

//These functions will delete any rows
//with any of the values we assigned previously
DeleteRowStartingWithString(clsWorksheet, timeStampValue);
DeleteRowStartingWithString(clsWorksheet, cellOneValue1);
DeleteRowStartingWithString(clsWorksheet, cellOneValue2);
DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line1header);
DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line2header);

Everything is deleted so we can now close and save our sheet.

    //Save the Excel sheet then exit the worksheet
    clsWorkbook.Save();
    clsWorkbook.Close(true);// Type.Missing, Type.Missing, Type.Missing);
    excelApp.Application.Quit();
}

At the bottom of this program, you will find the functions that delete rows, grab cell ranges, and grab rows. These essentially consist of multiple if-else loops passing our row values back in forth looking for the rows with our specified strings at the top of the program.

        //This is our delete rows function
        static void DeleteRowStartingWithString
               (Excel.Worksheet clsWorksheet, string rowStartingWithString)
        {
            int rowIndex =1;
            Excel.Range range  = clsWorksheet.UsedRange;

            //Will begin deleting the rows passed into this function 
            //until there are no rows with the string left
            if (rowStartingWithString.Length > 0)
            {
                while (rowIndex >= 1)// Let's spin through the entire spreadsheet 
                                     // till all rows are deleted 
                {
                    if (rowIndex >= 1) 
                    {
                        {
                            ((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
                        }                        
                    };
                    rowIndex = FindRow(range, rowStartingWithString);
                }
            }
        }
        
        //This function gets the value of the string in the specified row and column
        static String GetCell(Excel.Range range, int row, int column)
        {
            try
            {
                if ((row >= 0) && (column >= 0))
                {
                    Object obj = new object();
                    obj = range.Cells[row, column].Value;
                    if (obj == null) { obj = (String)""; }
                    return obj.ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception ex)
            {
                return string.Empty;
            }
        }
        //This function will get the row of the marker string
        static int FindRow(Excel.Range range, String marker)
        {
            int row = -1;
            try
            {
                row = range.Find(marker).Row;
                return row;
            }
            catch (Exception ex)
            {
                row = -1;
                return row;
            }
        }            
    }
}

Points of Interest

This project was done as part of my final project for my Senior Capstone Experience. It was definitely hard for someone who has never coded in C# to learn and do but I had a blast doing this!

History

• 18^th April, 2020: Initial version