How to check if a column exists in Excel table using XlsIO?
XlsIO allows checking if a column exists in an Excel table header by iterating through all the table columns using the 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 a 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 they exist.
if (IsFieldExist(table, columns))
{
MessageBox.Show("All the column names exist in the table.");
}
else
{
MessageBox.Show("Some of the column names do not exist in the table.");
}
// Saving the workbook.
workbook.SaveAs("OutputFile.xlsx");
// Closing the workbook and disposing of 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")
'Closing the workbook and disposing of 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 illustrating the behavior above behavior can be downloaded here.
Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.
Conclusion
I hope you enjoyed learning about how to check if a column exists in Excel table using XlsIO.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
