Show and set focus first Sheet tab in Excel file in .NET

Anishc · January 28, 2020, 9:58am

Dear Team,

We have issue with the Aspose.Cells. We are having a base template with around 12 sheets. We fill the data using Aspose and save it back.

The issue is after doing this, We have set the active sheet index to 0. When we open in excel, The visible sheet is the first one only but the sheet tabs below in excel is focused to the last So we have to scroll manually to go to the default first sheet. Screen attached.

Thanks
Anish

shakeel.faiz · July 3, 2017, 3:20am

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use the following code to set the active sheet to the first worksheet.

C#
workbook.Worksheets.ActiveSheetIndex = 0;

Anishc · July 3, 2017, 3:20am

Hi

We are already using this.

'set the first active sheet
oBook.Worksheets.ActiveSheetIndex = 0

And the focus is coming correctly to the first sheet. The issue is the tabs showing the sheet names below is not visible (Pls see the screen shot in my first post)

Anish

Amjad_Sahi · July 3, 2017, 3:20am

Hi,

Moreover, if you meant the Evaluation Warning sheet is set to active by default when you open the output file into MS Excel, for your information, when you don’t use and set the license file, an extra evaluation watermark sheet would be always added and set active. You have to use license file and set it before using any Aspose.Cells APIs. e.g

Sample code:

Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense(@“e:\test2\Aspose.Cells.lic”);

See the document for your reference:

http://www.aspose.com/docs/display/cellsnet/Licensing

If you still find the issue, kindly attach your template file and sample code to reproduce the issue on our end, we will check your issue soon.

Thank you.

Amjad_Sahi · July 3, 2017, 3:21am

Hi,

Anishc:

Hi

We are already using this.

'set the first active sheet
oBook.Worksheets.ActiveSheetIndex = 0

And the focus is coming correctly to the first sheet. The issue is the tabs showing the sheet names below is not visible (Pls see the screen shot in my first post)

Anish

Could you also try to set the active cell and other options e.g first visible row/col, see the topic for your reference:

http://www.aspose.com/docs/display/cellsnet/Activating+Sheets+and+Making+an+Active+Cell+in+the+Worksheet

If you still could not sort out, kindly attach your template file here, we will check your issue soon.

Thank you.

Anishc · July 3, 2017, 3:21am

Hi Team,

We are using a licensed version of Aspose.Cells . V 7.3.5.0

As per your request I have created a sample application and tested the scenario but the issue still persists. Attaching the code & excel for you reference. Request you to please help

Thanks
Anish

Amjad_Sahi · January 28, 2020, 9:55am

Hi,

Thanks for the sample file and code segment.

I think, now I can understand your requirement and issue. You should use FirstVisibleTab attribute for WorkbookSettings accordingly.

Please see the following sample code, especially the line in bold:

Sample code:

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense(“e:\test2\Aspose.Cells.lic”)

Dim workbook As Workbook = New Workbook(“e:\test2\Test2.xls”)

workbook.Worksheets.ActiveSheetIndex = 0

**workbook.Settings.FirstVisibleTab = 0**

workbook.Save(“e:\test2\out.xls”)

Let us know if you still have any issue or confusion.

Thank you.

Anishc · July 3, 2017, 3:21am

Hi Team,

Thanks a lot that solved the tab issue. Now the issue is with the sheets. Almost all our sheets have more than 500 rows. As you can see from my above code, We have given to default to A1:A1 cell as active as below

wkBook.Worksheets(sSheetName).ActiveCell = “A1”

wkBook.Worksheets(sSheetName).FirstVisibleColumn = 0

wkBook.Worksheets(sSheetName).FirstVisibleRow = 0

but still the default row visible is in the last row in the sheet. Request your help to focus it to the firt row by default.

Thanks

Anish

Amjad_Sahi · July 3, 2017, 3:21am

Hi,

Good to know that your original issue is resolved now.

Anishc:
Hi Team,
Now the issue is with the sheets. Almost all our sheets have more than 500 rows. As you can see from my above code, We have given to default to A1:A1 cell as active as below
wkBook.Worksheets(sSheetName).ActiveCell = "A1"
wkBook.Worksheets(sSheetName).FirstVisibleColumn = 0
wkBook.Worksheets(sSheetName).FirstVisibleRow = 0
but still the default row visible is in the last row in the sheet. Request your help to focus it to the firt row by default.
Thanks
Anish

For this issue, kindly provide me your simple input Excel file and output Excel file, also paste your sample (runnable) code here, we will check your issue soon.

Thank you.

Anishc · July 3, 2017, 3:21am

Hi Team,

Please find the input, output excel & Code attached. check the 12'th sheet. I think the issue is we have a split freeze. Is there any methods which scroll to top of the sheet.

Thanks
Anish

Amjad_Sahi · July 3, 2017, 3:21am

Hi,

Thanks for your files and sample code.

I think I now understand your issue. Please use PaneCollection object to specify the corresponding attributes accordingly for the worksheet (e.g 12th sheet). See the following sample code especially the lines in bold for your reference:

Sample code:

Dim license As New Aspose.Cells.License()

license.SetLicense(“e:\test2\Aspose.Cells.lic”)

Dim workbook As New Workbook(“e:\test2\TEST1_IN.xls”)

'Set the 12th sheet as an active sheet

workbook.Worksheets(11).ActiveCell = “A1”

workbook.Worksheets(11).FirstVisibleColumn = 0

workbook.Worksheets(11).FirstVisibleRow = 0

Dim panes As PaneCollection = workbook.Worksheets(11).GetPanes()

panes.FirstVisibleColumnOfRightPane = 0

panes.FirstVisibleRowOfBottomPane = 9

workbook.Settings.FirstVisibleTab = 0

workbook.Save(“e:\test2\output.xls”)

Thank you.