We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

sheet.Cells.ImportData skipping row

Version 20.1.0 C#.

I’m trying to write 10k rows per sheet from a SqlDataReader with 1 million rows. Each call to
sheet.Cells.ImportData is skipping two rows. So sheet 1 ends with 10000 sheet two begins with 10002

skipping record 10001

Below is my test code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
int count = 0;
using (SqlCommand commandCount = new SqlCommand(queryStringCount, connection))
{
if (connection.State != ConnectionState.Open)
connection.Open();
count = (int)commandCount.ExecuteScalar();
}

            SqlCommand commandSelect = new SqlCommand(queryStringSelect, connection);
            using (SqlDataReader reader = commandSelect.ExecuteReader())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();


                string OutputPath = $"{Path.GetTempPath()}WorkbookInsertDataReaderTest.{Aspose.Cells.SaveFormat.Xlsx.ToString()}";
                if (!File.Exists(OutputPath))
                    File.Create(OutputPath).Close();

                using (var fs = new FileStream(OutputPath, FileMode.OpenOrCreate))
                {
                    fs.Seek(0, SeekOrigin.Begin);

                    using (var book = new Workbook(fs))
                    {
                        if (book.Worksheets.Any(s => s.Name.ToLower() == "sheet1"))
                            book.Worksheets.RemoveAt(book.Worksheets.Where(s => s.Name.ToLower() == "sheet1").FirstOrDefault().Index);
                        book.Settings.MemorySetting = MemorySetting.MemoryPreference;
                        book.Settings.WarningCallback = new ExcelWarningCallback();

                        Worksheet sheet;
                        try
                        {

                            sheet = book.Worksheets.Add("test");

                            int totalInsertCount = 0, insertCount = 0;

                            int MaxSheetRecords = 10000;

                            totalInsertCount += insertCount = sheet.Cells.ImportData(reader, 0, 0, new ImportTableOptions() { ShiftFirstRowDown = true, InsertRows = true, TotalRows = MaxSheetRecords, IsFieldNameShown = true });
                            //sheet.Cells.ImportCustomObjects(reader.)

                            for (int row = 0; row <= sheet.Cells.MaxDataRow; row++)
                            {
                                Console.WriteLine("col 0: " + sheet.Cells[row, 0].StringValue);
                            }

                            //Start a new sheet for the remaining records with MaxSheetRecords
                            while (insertCount >= MaxSheetRecords)
                            {
                                sheet = book.Worksheets.Add($"{"test"}_{sheet.Index}");

                                totalInsertCount += insertCount = sheet.Cells.ImportData(reader, 0, 0, new ImportTableOptions() { ShiftFirstRowDown = true, InsertRows = true, TotalRows = MaxSheetRecords, IsFieldNameShown = true });

                                for (int row = 0; row <= sheet.Cells.MaxDataRow; row++)
                                {
                                    Console.WriteLine("col 0: " + sheet.Cells[row, 0].StringValue);
                                }
                            }

                            book.Save(fs, SaveFormat.Xlsx);
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }
                        finally
                        {
                            fs.Flush();
                        }
                    }
                }


            }
        }

@mhunts,

Thanks for the code segment.

We appreciate if you could create a standalone console application (runnable) to reproduce the issue. Please use the objects like, OleDbConnection, OleDbCommand, etc. in code instead and either create DataSet/DataTable(s) in code or use MS Access database, so we could execute your sample project seamlessly. This will help us analyze your issue precisely to figure it out soon.

PS. please zip the project and other attachments prior attaching here.

Thank you for taking a look, attached is the console app reproducing the bug. Is there another approach I can use to get around this issue? ConsoleApp1.zip (50.5 KB)

@Amjad_Sahi See above sample project

@mhunts,

We reproduced the issue as you mentioned by using your sample code with your sample database file. We found Worksheet.Cells.ImportData is skipping a few rows when splitting data into multiple sheets dynamically. We noticed record 4 is missing in the second sheet while in the third sheet, record 8 is missing. We have logged a ticket with an id “CELLSNET-47398” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

By the way, if you like you may use our Smart Markers feature to accomplish the task, see the documents for your reference:
https://docs.aspose.com/display/cellsnet/Auto+Populate+Smart+Marker+Data+to+Other+Worksheets+if+Data+is+too+Large

@mhunts,

We are pleased to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@Amjad_Sahi Will this be a path under version 20.1.X ?

@mhunts,

No, the fix will be included in our upcoming release (e.g Aspose.Cells for .NET v20.6), so you need to try/use it. The next release will be published in the next week hopefully.

The issues you have found earlier (filed as CELLSNET-47398) have been fixed in Aspose.Cells for .NET v20.6. you may also get the version @ NuGet repos. here (https://www.nuget.org/packages/Aspose.Cells/20.6.0).This message was posted using Bugs notification tool by Amjad_Sahi

The issues you have found earlier (filed as CELLSNET-47398) have been fixed in Aspose.Cells for .NET v20.6. you may also get the version @ NuGet repos. here (https://www.nuget.org/packages/Aspose.Cells/20.6.0).This message was posted using Bugs notification tool by Amjad_Sahi

Thank you for the quick turn around @Amjad_Sahi !

@mhunts,

You are welcome.