Splitting excel

Aspose.Cells Product Family
1

Hi Aspose,


Do you have a code snippet to split an excel document by N numbers of rows?

So I have 100000 rows excel doc, and i want to split it into 10000 rows each.


Thanks
2

Hi Arthur,

Thank you for contacting Aspose support.

Please consider the following piece of code to split the spreadsheet rows to separate files based on N number of rows. Please note, you may use the counter variable to set the number of rows per resultant spreadsheet.

C#

int counter = 20;
bool hasMoreRows = true;
int rowSize = 0;
var sourceBook = new Workbook(“C:/temp/book2.xlsx”);
var sourceSheet = sourceBook.Worksheets[0];
var sourceCells = sourceSheet.Cells;
while(hasMoreRows)
{
var sourceRange = sourceCells.CreateRange(rowSize, 0, counter, sourceCells.MaxDataColumn +1);
var destinationBook = new Workbook();
var destinationSheet = destinationBook.Worksheets[0];
var destinationCells = destinationSheet.Cells;
var destinationRange = destinationCells.CreateRange(0, 0, counter, sourceRange.ColumnCount);
destinationRange.Copy(sourceRange);

rowSize = rowSize + counter;

destinationBook.Save(“C:/temp/” + rowSize + “.xlsx”);

if ((sourceCells.MaxDataRow - rowSize) <= 0)
{
hasMoreRows = false;
}
}