Handling Excel Lists with Interop

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.

//ADJUSTING PATHS AND VARIABLES

static string listPath = "C:\\list.xlsx"; 
static string deletePath = @"C:\delete.xlsx";
static string resultPath = @"C:\result.xlsx";


static int listRowStartPoint; //1 if file has no headers in the 
	//first row like "Name","Surname" or "E-Mail"
static int delRowStartPoint;  //2 if file has headers in the first row
static int RowTemp;

After adding Interop, we need to open an Excel instance to work on. In this case, we used listPath.

//INITIALIZING EXCEL APPLICATION

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; //This sets the range to all used cells.

From now on, we have to get items for working on them.

//INITIALIZING ROW VARIABLES FOR ADJUSTING for LOOPS

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.

//INITIALIZING DELETE LIST 
//You can initialize your list with any of these methods.

//string[] delList = new string[arrayIndex1]; //00:03:40.7156816ms 
//(this will require a change from the delList.add method to delList[RowTemp])
//ArrayList delList = new ArrayList(); //00:03:52.3921806ms
//HashSet<string> delList = new HashSet<string>(); //00:03:40.6121898ms
HashSet<string> delList = 
	new HashSet<string>(StringComparer.OrdinalIgnoreCase); //00:03:39.4508162ms

//ADDING DATA TO THE ARRAYLIST

for (RowTemp = delRowStartPoint;RowTemp<=delRowCount;RowTemp++)
//delList[RowTemp]= (string)(delRange.Cells[RowTemp, 1] 
as Microsoft.Office.Interop.Excel.Range).Value2; //00:03:40.7156816ms
delList.Add((string)(delRange.Cells[RowTemp,1] as 
Microsoft.Office.Interop.Excel.Range).Value2); //00:03:39.4508162ms

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.

//DELETING UNWANTED ENTRIES FROM EXCEL LIST

string CellTemp; 
//this Temp value will act as the list array, 
//but the data won't be stored. So this will increase efficiency.

for (RowTemp = listRowStartPoint; RowTemp <= listRowCount; RowTemp++)
{
    //CellTemp = (string)(listWorksheet.Cells[RowTemp, 3] as Excel.Range).Value2; // 00:03:49.7046539ms
    CellTemp = delRange.Cells[RowTemp, 3].Value2; //00:02:03.4148882ms
    if (delList.Contains(CellTemp))
    {
        //((Excel.Range)listWorksheet.Rows[RowTemp]).Delete
        //(Excel.XlDeleteShiftDirection.xlShiftUp); //00:02:03.4148882ms
        ((Excel.Range)listWorksheet.Rows[RowTemp]).Delete(); //00:02:11.4026696ms
        RowTemp--; //this will prevent skipping to the next row after deletion.
        listRowCount--; //this will prevent out of index errors.
    }
}

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); //we do not need "type.missing" 
	//identifiers since the library handles the other variables automatically.
listApp.DisplayAlerts = true;
delWorkbook.Close(true); //Closing workbooks
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.