Loading Selected Worksheets only from Stream

Aspose.Cells Product Family
1

When loading the stream with loadOptions (where it sets the worskheet indexes), it still loads all the worksheets, and the output pdf contains all three pages, where as I expected only the first sheet. Am I doing anything wrong here:

Dim fileBytes As Byte()
Dim retFileName As String = "OutputFile.pdf"
fileBytes = Function2ReturnFileInByteArray()

Dim fileStream As New memoryStream(fileBytes)

'Define a new Workbook.
Dim workbook As Workbook

'Set the load data option with selected sheet(s).
Dim dataOption As New LoadDataOption()
dataOption.SheetIndexes = New Integer() {0} '---> ONLY LOAD SHEET 1

'Load the workbook with the spcified worksheet only.
Dim loadOptions As New LoadOptions(LoadFormat.Auto)
loadOptions.LoadDataOptions = dataOption
loadOptions.LoadDataOnly = False

'Creat the workbook.
workbook = New Workbook(fileStream, loadOptions)

'Perform your desired task.
workbook.SaveOptions.SaveFormat = SaveFormat.Pdf
workbook.SaveOptions.CreateDirectory = True

workbook.Save(HttpContext.Current.Response, Path.GetFileNameWithoutExtension(retFileName) & ".pdf", ContentDisposition.Inline, workbook.SaveOptions)

'End response to avoid unneeded html after xls
HttpContext.Current.Response.End()

I hav etried removing the sheets that I do not want, and that has worked, but that is a workaround I would not prefer taking, due to the variable number of sheets and the sheet that i would want to be converted to pdf. Why does loadOptions -- SheetIndexes does not work?

2

Hi,

Please download latested version: Aspose.Cells
for .NET v7.1.0.1


Please try the following code with the sample xlsx file attached by me.

a) - If you just want to load data of one worksheet, please try the following code:

C#


LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
LoadDataOption dataOption = new LoadDataOption();
dataOption.SheetIndexes = new int[] { 0 };
dataOption.ImportFormula = true;
loadOptions.LoadDataOnly = true;

//Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption;

//Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(Constants.sourcePath + “Source.xlsx”, loadOptions);


Worksheet sheet1 = wb.Worksheets[0];
Worksheet sheet2 = wb.Worksheets[1];
Worksheet sheet3 = wb.Worksheets[2];


Cell cellA1OfSheet1 = sheet1.Cells[“A1”];
Cell cellA1OfSheet2 = sheet2.Cells[“A1”];
Cell cellA1OfSheet3 = sheet3.Cells[“A1”];


Debug.Assert(cellA1OfSheet2.Type == CellValueType.IsNull, “This message should not appear”);

Debug.Assert(cellA1OfSheet3.Type == CellValueType.IsNull, “This message should not appear”);

VB.NET
Dim filePath As String = "F:\Shak-Data-RW\Downloads\source.xlsx"

Dim loadOptions As LoadOptions = New LoadOptions(LoadFormat.Xlsx)
Dim dataOption As LoadDataOption = New LoadDataOption()
dataOption.SheetIndexes = New Integer() {0}
dataOption.ImportFormula = True
loadOptions.LoadDataOnly = True

'Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption

'Create a Workbook object and opening the file from its path
Dim wb As Workbook = New Workbook(filePath, loadOptions)

Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim sheet2 As Worksheet = wb.Worksheets(1)
Dim sheet3 As Worksheet = wb.Worksheets(2)

Dim cellA1OfSheet1 As Cell = sheet1.Cells("A1")
Dim cellA1OfSheet2 As Cell = sheet2.Cells("A1")
Dim cellA1OfSheet3 As Cell = sheet3.Cells("A1")

Debug.Assert(cellA1OfSheet2.Type = CellValueType.IsNull, "This message should not appear")
Debug.Assert(cellA1OfSheet3.Type = CellValueType.IsNull, "This message should not appear")

b) - If you just want to get the all sheets’ name, please use the following code:

C#
dataOption.SheetIndexes = new int[] { };