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

Free Support Forum - aspose.com

Loading Selected Worksheets only from Stream

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?

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[] { };