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

Free Support Forum - aspose.com

Splitting excel

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

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