Splitting a workbook into multiple sheets

Aspose.Cells Product Family
1

Hi,

We would like to break apart a single Excel workbook into multiple worksheets where every page (using hard not soft page breaks) will be a separate sheet. I guess this will require reading the main sheet and moving each page – bounded by a hard page break – into a separate sheet.

Is this possible in Aspose?

Thanks for your cooperation,

Ronny Shahrabani

rshahrabani@shahrabani.com

2

Hi,

Thanks for your query.

Well, you may accomplish the task via Range.Copy() method. I have written a sample code to demonstrate your requirements a bit. I have used a simple template file (attached) which has a worksheet having four pages. Now I find out the page breaks and create a range based on each page. I create a new (blank) workbook and create a destination range (accordingly) in a new sheet (every time) to copy the source range into it. Finally I save the new Workbook (attached). See the sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Bk_split1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];

CellArea[] area = worksheet.GetPrintingPageBreaks(printoption);
MessageBox.Show(area.Length.ToString());//This will give number of pages (as shown print preview of the sheet)

//Instantiate a blank workbook.
Workbook wb = new Workbook();
wb.Worksheets.Clear();
for (int i = 0; i < area.Length; i++)
{

//Get the page rows of each area.
int strow = area[i].StartRow;
int stcol = area[i].StartColumn;

int erow = area[i].EndRow;
int ecol = area[i].EndColumn;

Range sourceRange = worksheet.Cells.CreateRange(CellsHelper.CellIndexToName(strow, stcol), CellsHelper.CellIndexToName(erow, ecol));

Range destRange = wb.Worksheets[wb.Worksheets.Add()].Cells.CreateRange(0, 0,
sourceRange.RowCount, sourceRange.ColumnCount);

destRange.Copy(sourceRange);


}

wb.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.