Excel sheet Missing Number finder | Ods file Missing Number finder

Excel sheet Missing Number finder | Ods file Missing Number finder

Sample:

Imports DevExpress.XtraEditors.Controls
Imports DevExpress.XtraGrid.Columns
Imports DevExpress.XtraEditors.Repository

Public Class Form1
    Private odsFile As New DataSet
    Dim dtSetCalc As New DataTable
    Dim dtOpenCalc As New DataTable
    Private Sub btnsubmit_Click(sender As Object, e As EventArgs) Handles btnsubmit.Click

        Dim dtmissingvalues As New DataTable
        Try

            If ButtonEdit1.Text = "" Then
                MsgBox("Please select A excel file", MsgBoxStyle.Exclamation)
                ButtonEdit1.Focus()
                Exit Sub
            End If
            If seMaxbookNo.Value = 0 Then
                MsgBox("Maxminum Books Number Needed", MsgBoxStyle.Exclamation)
                seMaxbookNo.Focus()
                Exit Sub
            End If
           

            If seStartingRow.Value = 0 Then
                MsgBox("Starting rows Needed", MsgBoxStyle.Exclamation)
                seStartingRow.Focus()
                Exit Sub
            End If

            If seEndingRow.Value = 0 Then
                MsgBox("Ending rows Needed", MsgBoxStyle.Exclamation)
                seEndingRow.Focus()
                Exit Sub
            End If
            lblstatus.Text = "Please Wait..."
            Application.DoEvents()
            ReadOdsFile()
            Dim dtFinalImport As New DataTable
            dtFinalImport.Merge(odsFile.Tables(0))
            dtSetCalc = dtFinalImport.Clone
            Dim k As Integer = 0
            '  Dim drIIns As DataRow
            For Each dr As DataRow In dtFinalImport.Rows
                k = k + 1
                If k = seEndingRow.EditValue + 1 Then Exit For
                dtSetCalc.ImportRow(dr)
                dtSetCalc.AcceptChanges()
            Next
            Dim inc As Integer = 0
            dtOpenCalc = dtSetCalc.Clone
            For Each dr As DataRow In dtSetCalc.Rows
                If inc >= seStartingRow.EditValue - 1 Then
                    dtOpenCalc.ImportRow(dr)
                    dtOpenCalc.AcceptChanges()
                End If
                inc = inc + 1
                Application.DoEvents()
            Next

            dtmissingvalues.Columns.Add("Missing Values")

            For c As Integer = 0 To seMaxbookNo.Value - 1
                lblstatus.Text = "Please Wait : " & seMaxbookNo.Value - 1 & " / " & c
                Application.DoEvents()
                For i As Integer = 0 To dtOpenCalc.Columns.Count - 1
                    For j As Integer = 0 To dtOpenCalc.Rows.Count - 1
                        If dtOpenCalc.Rows(j)(dtOpenCalc.Columns(i).Caption).ToString() = c.ToString() Then
                            GoTo f
                        End If
                    Next
                Next
                Dim Insert As DataRow = dtmissingvalues.NewRow
                Insert("Missing Values") = c
                dtmissingvalues.Rows.Add(Insert)
f:
            Next

            lblstatus.Text = ""
            gcmissingnumber.DataSource = dtmissingvalues

        Catch ex As Exception
            MessageBox.Show("Exception!" & vbLf & vbLf + ex.Message)
        End Try

    End Sub
    Private Sub ReadOdsFile()
        Try
            odsFile = OdsReaderNew.ReadOdsFile(ButtonEdit1.Text)
            '  Me.ShowOdsFile()
        Catch ex As Exception
            MessageBox.Show("Exception!" & vbLf & vbLf + ex.Message)
        End Try
    End Sub
    Private Sub ButtonEdit1_ButtonClick(sender As Object, e As DevExpress.XtraEditors.Controls.ButtonPressedEventArgs) Handles ButtonEdit1.ButtonClick
        ofdImportfile.Filter = "(*.ods)|*.ods|(*.xls)|*.xls|(*.xlsx)|*.xlsx"

        ofdImportfile.InitialDirectory = "C:\"


        ofdImportfile.ShowDialog()
        ButtonEdit1.Text = ofdImportfile.FileName
    End Sub
    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        AddHandler gvmissingnumber.InvalidRowException, AddressOf GridControlInvalidRowException
        AddHandler gvmissingnumber.RowUpdated, AddressOf GridControlRowUpdated
        AddHandler gvmissingnumber.CustomDrawRowIndicator, AddressOf GridControlCustomDrawRowIndicator
    End Sub
    Public Sub GridControlCustomDrawRowIndicator(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs)
        If e.Info.IsRowIndicator AndAlso e.RowHandle >= 0 Then
            e.Info.DisplayText = (e.RowHandle + 1).ToString
        End If
    End Sub

    Public Sub GridControlRowUpdated(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Base.RowObjectEventArgs)
        Try
            Dim gvGridView As DevExpress.XtraGrid.Views.Grid.GridView = sender

            If gvGridView Is Nothing Then Exit Sub

            For Each gridCol As GridColumn In gvGridView.Columns
                Dim rItem As RepositoryItem = gridCol.ColumnEdit

                If rItem IsNot Nothing AndAlso e.Row IsNot Nothing Then
                    Try
                        With CType(e.Row, DataRowView)
                            If IsDBNull(.Item(gridCol.FieldName)) AndAlso _
                                (rItem.EditorTypeName = "TextEdit" OrElse _
                                rItem.EditorTypeName = "MemoEdit") _
                                Then
                                .BeginEdit()
                                .Item(gridCol.FieldName) = ""
                                .EndEdit()
                            End If
                        End With
                    Catch ex As Exception
                    End Try
                End If
            Next
        Catch ex As Exception
        End Try
    End Sub

    Public Sub GridControlInvalidRowException(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Base.InvalidRowExceptionEventArgs)
        e.ExceptionMode = ExceptionMode.NoAction
    End Sub
End Class

Imports System.IO
Imports Aspose.Cells
Module OdsReaderNew
    Public Function ReadOdsFile(ByVal filepath As String) As DataSet
        Dim dsFile As New DataSet
        Try
            'Creating a file stream containing the Excel file to be opened
            Using fstream As FileStream = New FileStream(filepath, FileMode.Open)
                'Instantiating a Workbook object
                'Opening the Excel file through the file stream
                Dim workbook As Workbook = New Workbook(fstream)

                'Accessing the first worksheet in the Excel file
                For i As Integer = 0 To workbook.Worksheets.Count - 1
                    Dim worksheet As Worksheet = workbook.Worksheets(i)
                    If worksheet.Cells.MaxRow <> "-1" Then
                        Dim Dttable As DataTable = New DataTable()
                        Dttable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, False)
                        dsFile.Tables.Add(Dttable)
                    End If
                Next
                'Closing the file stream to free all resources

                fstream.Dispose()
                fstream.Close()
            End Using
            Return dsFile
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf & _
                            " (Key cannot be null.Parameter name: key error means " & vbCrLf & _
                            " Ods file edit and save once!) or (row number column number can't be null if this error occured have to check the blank rows!)", "Ods file error!", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Return Nothing
        Finally
            'GC.Collect()
        End Try
    End Function
   
End Module