Introduction
This snippet shows and acts as a tutorial on handling Excel lists. This will help people to understand the Excel Interop commands.
Using the Code
This snippet includes other methods to handle lists, and in every method, a worktime is shown using the same Excel files.
First of all, we have to enable Excel to be used on the app environment.
using Excel = Microsoft.Office.Interop.Excel;
We need to specify paths, you can adjust paths as you see fit.
static string listPath = "C:\\list.xlsx";
static string deletePath = @"C:\delete.xlsx";
static string resultPath = @"C:\result.xlsx";
static int listRowStartPoint;
static int delRowStartPoint;
static int RowTemp;
After adding Interop, we need to open an Excel instance to work on. In this case, we used listPath
.
Excel.Application listApp = new Excel.Application();
Excel.Workbook listWorkbook = listApp.Workbooks.Open(listPath);
Excel.Worksheet listWorksheet = (Excel.Worksheet)listWorkbook.Worksheets.get_Item(1);
Excel.Range listRange = listWorksheet.UsedRange;
From now on, we have to get items for working on them.
int listRowCount = listRange.Rows.Count;
int delRowCount = delRange.Rows.Count;
Below, there are 4 methods to initialize a list to store data. Elapsed runtime included with each method to provide more information.
HashSet<string> delList =
new HashSet<string>(StringComparer.OrdinalIgnoreCase);
for (RowTemp = delRowStartPoint;RowTemp<=delRowCount;RowTemp++)
as Microsoft.Office.Interop.Excel.Range).Value2;
delList.Add((string)(delRange.Cells[RowTemp,1] as
Microsoft.Office.Interop.Excel.Range).Value2);
At this point, we have all the data from delete.xlsx file stored in delList
array or list by your preference.
Now, we have to compare these items with the Old List to create a Clean List.
What we do here is go through all rows in the third column of the Old List file to check if they include any address from delList
.
This example uses [RowTemp,3]
as the cell, since our Old List example stores e-mails in third column.
string CellTemp;
for (RowTemp = listRowStartPoint; RowTemp <= listRowCount; RowTemp++)
{
CellTemp = delRange.Cells[RowTemp, 3].Value2;
if (delList.Contains(CellTemp))
{
((Excel.Range)listWorksheet.Rows[RowTemp]).Delete();
RowTemp--;
listRowCount--;
}
}
At this point, we cleared our Old List into a Clean List. So we save our new Result File and close workbooks to finalize the process.
listApp.DisplayAlerts = false;
listWorkbook.SaveAs(resultPath);
listApp.DisplayAlerts = true;
delWorkbook.Close(true);
listWorkbook.Close(true);
delApp.Quit();
listApp.Quit();
Points of Interest
Old List:
- Original list with unwanted entries.
- Our sample list has 3 columns:
Name
, Surname
and E-Mail
. - This list is included in specified Excel file in
listPath
.
Unwanted List Entries:
- These entries include email addresses to be deleted from the Old List.
- Our sample Delete List has only one column:
E-Mail
. - These entries are included in
deletePath
Excel file.
New List:
- This list is the result of deleting unwanted entries from the Old List.
- The New List will result in 3 columns since we used a 3-column Old List.
- List is included in specified Excel file in
resultPath
.
The "rowtemp--;"
part is essential if you want to get accurate results. If not used, the program will ignore every subsequent "should be deleted data" after deleting the first one. This is caused by Excel delete
function's moving other rows to one upper after deletion option. And the rowtemp--;
prevents these.