Category / Section
How to check if a column exists in Excel table using XlsIO?
2 mins read
XlsIO allows to check if a column exists in Excel table header by iterating all the table columns using IListObjectColumn.Name property.
The below code has a custom method IsFieldExists() to check whether the table header has given column names using XlsIO.
C#
ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; //Loading a workbook with table. IWorkbook workbook = application.Workbooks.Open("ExcelTable.xlsx"); //The first worksheet is accessed. IWorksheet worksheet = workbook.Worksheets[0]; IListObject table = worksheet.ListObjects[0]; string[] columns = { "Name", "Time", "Location" }; //Passing the table object and column names to check whether it exists. if (IsFieldExist(table, columns)) { MessageBox.Show("All the column names exists in the table."); } else { MessageBox.Show("Some of the column names does not exist in the table."); } //Close the workbook. workbook.SaveAs("OutputFile.xlsx"); //Close the workbook and dispose the engine. workbook.Close(); excelEngine.Dispose(); private bool IsFieldExist(IListObject table, string[] columnNames) { IList<IListObjectColumn> tableColumn = table.Columns; IList<string> columnNamesList = columnNames.ToList<string>(); foreach (IListObjectColumn column in tableColumn) { if (columnNamesList.Contains(column.Name)) { columnNamesList.Remove(column.Name); } } return columnNamesList.Count == 0 ? true : false; }
VB
Dim excelEngine As ExcelEngine = New ExcelEngine Dim application As IApplication = excelEngine.Excel 'Loading a workbook with table. Dim workbook As IWorkbook = application.Workbooks.Open(("ExcelTable.xlsx")) 'The first worksheet is accessed. Dim worksheet As IWorksheet = workbook.Worksheets(0) Dim table As IListObject = worksheet.ListObjects(0) Dim columns As String() = {"Name", "Time", "Location"} 'Passing the table object and column names to check whether it exists. If IsFieldExist(table, columns) Then MessageBox.Show("All the column names exists in the table.") Else MessageBox.Show("Some of the column names does not exist in the table.") End If 'Save the workbook. workbook.SaveAs("OutputFile.xlsx") 'Close the workbook and dispose the engine. workbook.Close() excelEngine.Dispose() Private Function IsFieldExist(table As IListObject, columnNames As String()) As Boolean Dim tableColumn As IList(Of IListObjectColumn) = table.Columns Dim columnNamesList As IList(Of String) = columnNames.ToList() For Each column As IListObjectColumn In tableColumn If columnNamesList.Contains(column.Name) Then columnNamesList.Remove(column.Name) End If Next Return If(columnNamesList.Count = 0, True, False) End Function
The sample which illustrates the above behavior can be downloaded here.