Error uploading Excel file from ASP.NET application to MySQL

Question

I'm working on a web application (ASP.NET) with a MySQL database. When I try to upload an Excel file (.xlsx) via this application I have no issues. The issue begins when the file is more than 24904 records.

At that point I get the following error:

This table contains cells that are outside the range of cells defined in this spreadsheet.

and it writes the first 24904 records.

I tried to split the load in multiples batches and it did not work.

Any thoughts?

Dim connExcel As New System.Data.OleDb.OleDbConnection(conStr)
Dim cmdExcel As New System.Data.OleDb.OleDbCommand()
Dim dt As New DataTable()
Dim dataset As New DataSet
Dim x As Integer = 2
Dim y As Integer = 20001
Dim range As String = "A" + x.ToString + ":" + "I" + y.ToString

cmdExcel.Connection = connExcel

If erro = 0 Then
    Try

        For i As Integer = 0 To 50
            connExcel.Open()

            dataset.Reset()
            dataset.Clear()

            Dim oda As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$" & range.ToString & "]", connExcel)
            oda.TableMappings.Add("Table", "ExcelTest")

            oda.Fill(dataset)
            connExcel.Close()

            If dataset.Tables(0).Rows.Count > 0 Then
                SendToDB(dataset)
            Else
                i = 50
            End If

            x = x + 20000
            y = y + 20000

            range = "A" + x.ToString + ":" + "I" + y.ToString
        Next

        Label7.Visible = True
        Label7.Text = "The information has been written successfully from 0 to " + y.ToString

    Catch ex As Exception
        Label9.Visible = True
        Label9.Text = "Database Error 2:" + ex.Message
        connExcel.Close()
    End Try
End If

Answer 1

I Don't Use OLDB or excel since its create many issue while reading excel

I use ExcelDataReader https://github.com/ExcelDataReader/ExcelDataReader

Try This

Install-Package ExcelDataReader

public static DataSet GetExcelDataSet(string filePath)
    {
        FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        IExcelDataReader excelReader;
        if (Path.GetExtension(filePath) == ".xls")
        {
            //Choose one of either 1 or 2
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else
        {
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        }
        //Choose one of either 3, 4, or 5
        //3. DataSet - The result of each spreadsheet will be created in the result.Tables

        ////////DataSet result = excelReader.AsDataSet();

        //4. DataSet - Create column names from first row
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        //5. Data Reader methods
        //while (excelReader.Read())
        //{
        //    //excelReader.GetInt32(0);
        //}

        //6. Free resources (IExcelDataReader is IDisposable)
        excelReader.Close();
        return result;
    }