public void ReadBatchTest_AllSheet()
{
DataSet dataSet = null;
DataTable dataTable = null;
int rowCount = 0;
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(Helper.GetTestWorkbook("TestMultiSheet")))
{
if (!excelReader.IsValid)
{
throw new Exception(excelReader.ExceptionMessage);
}
dataSet = excelReader.GetSchema();
excelReader.BatchSize = 5; // Modify as per need, default is 1000
foreach (DataTable dt in dataSet.Tables)
{
excelReader.SheetName = dt.TableName;
excelReader.IsFirstRowAsColumnNames = Convert.ToBoolean(dt.ExtendedProperties["IsFirstRowAsColumnNames"]);
excelReader.SkipRows = Convert.ToInt32(dt.ExtendedProperties["SkipRows"]);
rowCount = 0;
while (excelReader.ReadBatch())
{
dataTable = excelReader.GetCurrentBatch();
rowCount += dataTable.Rows.Count;
if (rowCount == excelReader.BatchSize)
{
Assert.AreEqual(5, rowCount);
}
}
}
}
}
public void ReadBatchTest_OneSheet()
{
DataTable dataTable = null;
int rowCount = 0;
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(Helper.GetTestWorkbook("TestMultiSheet")))
{
if (!excelReader.IsValid)
{
throw new Exception(excelReader.ExceptionMessage);
}
// Read one sheet of excel in batch
excelReader.SheetName = "Sheet2";
excelReader.IsFirstRowAsColumnNames = false; // default is true
excelReader.SkipRows = 0; // default is 0
excelReader.BatchSize = 5;
while (excelReader.ReadBatch())
{
dataTable = excelReader.GetCurrentBatch();
rowCount += dataTable.Rows.Count;
if (rowCount == excelReader.BatchSize)
{
Assert.AreEqual(5, rowCount);
}
}
}
Assert.AreEqual(12, rowCount);
}
private static void GenerateFile(bool isFirstRowAsColumnNames, string sampleFilePath, string outputFilePath, int sizeOfOutputFileInMb)
{
string firstSheetName = string.Empty;
int batchSize, desiredRows, sizeOfBatchInKb;
DataTable datatable = null, batchSizeDataTable = null;
FileInfo destFileInfo = new FileInfo(outputFilePath);
Console.WriteLine("Process started...");
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(File.OpenRead(sampleFilePath)))
{
if (!excelReader.IsValid)
{
throw new Exception(excelReader.ExceptionMessage);
}
// Read one sheet of excel in batch
excelReader.SheetName = firstSheetName = excelReader.GetSheetNames().FirstOrDefault();
excelReader.IsFirstRowAsColumnNames = isFirstRowAsColumnNames;
if (excelReader.ReadBatch())
{
datatable = excelReader.GetCurrentBatch();
}
}
batchSize = 1000;
batchSizeDataTable = CreateBatchSizeTable(batchSize, datatable);
SaveTableToExcel(batchSizeDataTable, firstSheetName, isFirstRowAsColumnNames, true, ref destFileInfo);
sizeOfBatchInKb = Convert.ToInt32(destFileInfo.Length / 1024);
desiredRows = Convert.ToInt32(batchSize * (((sizeOfOutputFileInMb * 1024) / (decimal)sizeOfBatchInKb)));
// making optimal batch size
if (sizeOfBatchInKb < 1024 && desiredRows > batchSize)
{
int optimalBatchSizeInMb = sizeOfOutputFileInMb >= 5 ? 5 : 1; // // 5 mb or 1 mb batch
batchSize = Convert.ToInt32(batchSize * ((optimalBatchSizeInMb * 1024) / (decimal)sizeOfBatchInKb));
batchSizeDataTable = CreateBatchSizeTable(batchSize, batchSizeDataTable);
SaveTableToExcel(batchSizeDataTable, firstSheetName, isFirstRowAsColumnNames, false, ref destFileInfo);
sizeOfBatchInKb = Convert.ToInt32(destFileInfo.Length / 1024);
desiredRows = Convert.ToInt32(batchSize * (((sizeOfOutputFileInMb * 1024) / (decimal)sizeOfBatchInKb)));
}
if (desiredRows > 1000000)
{
Console.WriteLine("The generated file would exceed excel row limit of ~1 million rows. Please try one or more of the following: a) Increase number of columns in sample excel file. b) Increase data in rows of sample excel file. c) Reduce the size of output file to be generated. ");
return;
}
int loopCounter = (sizeOfOutputFileInMb * 1024 / sizeOfBatchInKb) + 1;
int lastBatchRows = (desiredRows % batchSize);
bool isLastBatchResizeRequired = batchSize - lastBatchRows > 0;
int updateRow, updateRowHeaderAdjuster = isFirstRowAsColumnNames ? 2 : 1;
long totalRows = batchSize;
if (loopCounter > 1)
{
Console.WriteLine("Iteration: 0");
Console.WriteLine($"Rows: {totalRows}");
using (ExcelPackage excelPackage = new ExcelPackage(destFileInfo))
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[firstSheetName];
for (int i = 1; i < loopCounter; i++)
{
if (i < loopCounter - 1)
{
totalRows += batchSize;
}
else if (isLastBatchResizeRequired) // Adjusting rows of last batch
{
totalRows += lastBatchRows;
batchSizeDataTable = batchSizeDataTable.AsEnumerable().Take(lastBatchRows).CopyToDataTable();
}
Console.WriteLine($"Iteration: {i}");
Console.WriteLine($"Rows: {totalRows}");
// Adding cells data
updateRow = i * batchSize + updateRowHeaderAdjuster;
//ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[firstSheetName];
worksheet.Cells[updateRow, 1].LoadFromDataTable(batchSizeDataTable, false);
}
DateTime dt = DateTime.Now;
Console.WriteLine("File save on disk started...");
excelPackage.SaveAs(destFileInfo);
Console.WriteLine($"File save on disk completed in {(DateTime.Now - dt).TotalMinutes} minutes.");
}
}
else
{
if (isLastBatchResizeRequired)
{
totalRows = totalRows - batchSize + lastBatchRows;
batchSizeDataTable = batchSizeDataTable.AsEnumerable().Take(lastBatchRows).CopyToDataTable();
SaveTableToExcel(batchSizeDataTable, firstSheetName, isFirstRowAsColumnNames, true, ref destFileInfo);
}
Console.WriteLine("Iteration: 0");
Console.WriteLine($"Rows: {totalRows}");
}
Console.WriteLine("");
Console.WriteLine("******************");
Console.WriteLine($"File of size ~{sizeOfOutputFileInMb} Mb having {totalRows} data rows created successfully.");
Console.WriteLine("******************");
}