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 not find the solution
Contact Support