Splitting sheets in a Excel workbook and save it differently

Aspose.Cells Product Family
1

Hi,

We are using Aspose.Cells dll while extracting data from Excel; and saving it into SQL DB.
Now the same application is generating reports in Excel. The report contains data in different worksheets and we want to split the same in multiple excel files.

We are using following code for this:
for (int j = 0; j < workbook.Worksheets.Count; j++)
{
Worksheet ws = workbook.Worksheets[j];
workbook.Save(@“D:\Practice\ExcelToDatabase\SplitExcel” + ws.Name + “.xls”);
if (j < workbook.Worksheets.Count - 1)
{
workbook.Worksheets[j + 1].IsVisible = true;
workbook.Worksheets[j].IsVisible = false;

}
}

This is working fine, but it is just hiding the sheets in each case and not deleting them.
As we are generating data for reporting purpose, we want the hidden sheets as per above logic to be deleted as it will decrease the size of final file.
OR Atleast Hide them in a manner that it will not be made visible ever again…

Any help on the above is highly appreciated…!!
Thanks

2

Hi,


Thanks for providing us some details.

I think you have two options that you may try, you may choose any one accordingly.

1) You may try to use:
e.g
Sample code:

…
worksheet.VisibilityType = VisibilityType.VeryHidden;
this option sets the sheet to hidden state in such a way that cannot be shown in the default user interface (UI) of MS Excel. This state can be changed programmatically.

2) You may deleted the unwanted sheets by using WorksheetCollection.RemoveAt() method, see the sample line of code here:
e.g
Sample code:

…
workbook.Worksheets.RemoveAt(index);

Hope, this helps a bit.

Thank you.

3

Thanks for your quick response.
I think both the solutions work for me. I will try them and let you know my comments.

Thanks a ton…!!

4

Hi,

Thanks for your appreciation and using Aspose.Cells.

We are glad to know that you loved both the solutions shared by Amjad. Hopefully they will work for you. Please do give them a try and let us know your feedback. Also, please feel free to let us know if you encounter any other issue, we will like to help you more. Have a good day.