Articles in this section
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.

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied