How to extract a particular work sheet of excel work book

Aspose.Cells Product Family
1

Hi @,

I have a requirement in which I have to extract a particular work sheet from excel work book.

The following example will explain the scenario:

If I have a work book with 10 work sheets. I should get the 2nd work sheet in a separate excel file when I pass 2 as parameter. If I pass 4 then 4th work sheet has to come in seperate excel file.

Please let us know if this is possible with any of the aspose components. We thought Aspose.Cells should give us some solution.

Please share the sample code if any.

Any help will be appreciated.

Thanks in advance.

2

Hi,

Well, you may load only specific worksheet in a workbook either by specify its names or sheet indexes, see the following sample code:
//Load only specific sheet with data and formulas,
//Other objects, items etc. would also be extracted.

//Instantiate LoadOptions specified by the LoadFormat
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);

//Set the LoadDataOption
LoadDataOption dataOption = new LoadDataOption();

//Specify the sheet(s) in the template file to be loaded - only the second worksheet.
dataOption.SheetIndexes = new int[] { 1 };
dataOption.ImportFormula = true;
loadOptions.LoadDataOnly = false;
//Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption;
//Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(“e:\test\Book1.xlsx”, loadOptions);

wb.Save(“e:\test\nnkkoutadfadfloadopen.xlsx”);

See the document (especially the sub topic “

Opening File with Data only

”: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/opening-files.html


Moreover, you may open the workbook with 10 worksheet, now instantiate a new workbook and then copy your desired worksheet from the first workbook to your new workbook for your your need.

See the document for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/copying-and-moving-worksheets.html


Thank you.


3

Hi Amjad,

I am getting the following errors when i am trying to use the above code.

1.Aspose.Cells.LoadDataOption' does not contain a definition for 'ImportFormula'.

2.'Aspose.Words.LoadOptions' does not contain a definition for 'LoadDataOnly'.

3.'Aspose.Words.LoadOptions' does not contain a definition for 'LoadDataOptions'.

I am using 9.3.0.0 version of Aspose.Words.dll and 4.9.1.0 version of Aspose.cells.dll.

Please let me know how to proceed further.

Thanks in advance.

Regards

Aneesh

4

Hi,

1) Please use latest version as the api is new, you may download and try latest version as it does have the ImportFormula attribute for Aspose.Cells.LoadDataOption (v5.1.1): http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry250805.aspx


For 2) and 3) since the queries are related to Aspose.Words product, so you should ask Aspose.Words team (in Aspose.Words forum) for help.

Thank you.