Windows Forms: Check Excel columns for lower case words using ClosedXML

Hi luckydead,

Refer below sample.

Excel file

The Form

The form contains a DataGridView and a Button control.

Namespaces

C#

using System.Data;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports ClosedXML.Excel

Code

C#

protected void btnSearch_Click(object sender, EventArgs e)
{
    using (XLWorkbook workBook = new XLWorkbook(@"C:\Users\Admin\Desktop\Test.xlsx"))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                IXLCell cellA = row.Cell(1);
                IXLCell cellB = row.Cell(2);
                IXLCell cellC = row.Cell(3);
                bool isLowerLetter = false;
                if (cellB.Value.ToString().Any(char.IsLower))
                {
                    isLowerLetter = true;
                }

                if (cellC.Value.ToString().Any(char.IsLower))
                {
                    isLowerLetter = true;
                }
                if (isLowerLetter)
                {
                    dt.Rows.Add(cellA.Value.ToString(), cellB.Value.ToString(), cellC.Value.ToString());
                }
            }
        }

        dataGridView1.DataSource = dt;
    }
}

VB.Net

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles button1.Click
    Using workBook As XLWorkbook = New XLWorkbook("C:\Users\Admin\Desktop\Test.xlsx")
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next

                firstRow = False
            Else
                Dim cellA As IXLCell = row.Cell(1)
                Dim cellB As IXLCell = row.Cell(2)
                Dim cellC As IXLCell = row.Cell(3)
                Dim isLowerLetter As Boolean = False

                If cellB.Value.ToString().Any(AddressOf Char.IsLower) Then
                    isLowerLetter = True
                End If

                If cellC.Value.ToString().Any(AddressOf Char.IsLower) Then
                    isLowerLetter = True
                End If

                If isLowerLetter Then
                    dt.Rows.Add(cellA.Value.ToString(), cellB.Value.ToString(), cellC.Value.ToString())
                End If
            End If
        Next

        dataGridView1.DataSource = dt
    End Using
End Sub

Screenshot

dharmendr says:

                If cellC.Value.ToString().Any(AddressOf Char.IsLower) Then

                    isLowerLetter = True

                End If

Remove the if condition and remove 1st column from the DataTable and set the DataSource.

dt.Columns.RemoveAt(0)
dataGridView1.DataSource = dt

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles button1.Click
    Using workBook As XLWorkbook = New XLWorkbook("C:\Users\Admin\Desktop\Test.xlsx")
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next

                firstRow = False
            Else
                Dim cellA As IXLCell = row.Cell(1)
                Dim cellB As IXLCell = row.Cell(2)
                Dim cellC As IXLCell = row.Cell(3)
                Dim isLowerLetter As Boolean = False

                If cellB.Value.ToString().Any(AddressOf Char.IsLower) Then
                    isLowerLetter = True
                End If

                'If cellC.Value.ToString().Any(AddressOf Char.IsLower) Then
                '    isLowerLetter = True
                'End If

                If isLowerLetter Then
                    dt.Rows.Add(cellA.Value.ToString(), cellB.Value.ToString(), cellC.Value.ToString())
                End If
            End If
        Next
        dt.Columns.RemoveAt(0)
        dataGridView1.DataSource = dt
    End Using
End Sub