In certain situations, having an Excel file without headers lets us look at the data inside better as well as import this data more easily. This tip will show you how to delete certain headers from the Excel spreadsheet.
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)
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
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;
Excel.Range range = clsWorksheet.UsedRange;
String timeStampValue = GetCell(range, 1, 1);
String reportName = string.Empty;
int rowsToBeDeleted = 0;
int rowIndex;
rowIndex = 1;
string cellOneValue1 = string.Empty; string cellOneValue2 =
string.Empty; string cellOneValueTimeStamp = string.Empty;
string cellOneValue_Line1header = string.Empty;
string cellOneValue_Line2header = string.Empty;
cellOneValueTimeStamp=timeStampValue;
reportName = "Inventory Master File";
if (reportName == "Inventory Master File")
{
cellOneValue1 = "Inventory Master File"; cellOneValue2 = "From Store";
cellOneValue_Line1header = "Item"; cellOneValue_Line2header = "Number";
}
rowIndex = FindRow(range, timeStampValue);
DeleteRowStartingWithString(clsWorksheet, timeStampValue);
DeleteRowStartingWithString(clsWorksheet, cellOneValue1);
DeleteRowStartingWithString(clsWorksheet, cellOneValue2);
DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line1header);
DeleteRowStartingWithString(clsWorksheet, cellOneValue_Line2header);
clsWorkbook.Save();
clsWorkbook.Close(true);
excelApp.Application.Quit();
}
static void DeleteRowStartingWithString
(Excel.Worksheet clsWorksheet, string rowStartingWithString)
{
int rowIndex =1;
Excel.Range range = clsWorksheet.UsedRange;
if (rowStartingWithString.Length > 0)
{
while (rowIndex >= 1)
{
if (rowIndex >= 1)
{
{
((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
}
};
rowIndex = FindRow(range, rowStartingWithString);
}
}
}
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;
}
}
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)
{
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):
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.
String timeStampValue = GetCell(range, 1, 1);
String reportName = string.Empty;
int rowsToBeDeleted = 0;
int rowIndex;
rowIndex = 1;
string cellOneValue1 = string.Empty; string cellOneValue2 =
string.Empty; string cellOneValueTimeStamp = string.Empty;
string cellOneValue_Line1header =
string.Empty; string cellOneValue_Line2header = string.Empty;
cellOneValueTimeStamp=timeStampValue;
*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!
reportName = "Inventory Master File";
if (reportName == "Inventory Master File")
{
cellOneValue1 = "Inventory Master File"; cellOneValue2 = "From Store";
cellOneValue_Line1header = "Item"; cellOneValue_Line2header = "Number";
}
rowIndex = FindRow(range, timeStampValue);
With our variables correctly assigned, we can move onto deleting them from the Excel spreadsheet.
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.
clsWorkbook.Save();
clsWorkbook.Close(true);
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.
static void DeleteRowStartingWithString
(Excel.Worksheet clsWorksheet, string rowStartingWithString)
{
int rowIndex =1;
Excel.Range range = clsWorksheet.UsedRange;
if (rowStartingWithString.Length > 0)
{
while (rowIndex >= 1)
{
if (rowIndex >= 1)
{
{
((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
}
};
rowIndex = FindRow(range, rowStartingWithString);
}
}
}
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;
}
}
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
- 18th April, 2020: Initial version