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();
}
}
}
}
}