Horizontal Scroll Bar position not maintained?

Aspose.Cells Product Family
1

I have a VERY picky client and they are complaining that the width of the horizontal scroll bar is not being maintained.

If I update the position (shrink it so that all the tabs they have are visible) and save, when I open it in excel it remembers the width, but when I process it through the API - it just shows up as its default width, is there something I am missing or doing wrong?

2

Hi,

I think you may make a sheet as an active sheet and set the active cell before saving your file. So, when you open your excel file into MS Excel these settings will be retained.

May the following code help you for your requirement.Mind you the following code only works with a valid Aspose.Cells license.

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense( "Aspose.Cells.lic" );
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
workbook.Open("d:\\test\\MyBook.xls");
//Get some worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets["Sheet7"];
//Set the sheet as an active sheet.
workbook.Worksheets.ActiveSheetIndex = worksheet1.Index;
//Set Z200 cell as an active cell in the worksheet.
worksheet1.ActiveCell= "Z200";
//Set the visibility control for horizontal and vertical scrollbars.
worksheet1.FirstVisibleColumn =20;
worksheet1.FirstVisibleRow = 180;
//Save the excel file.
workbook.Save("d:\\test\\setactivesheet.xls");
For further reference, please check:

http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/ActiveCells.html

Thank you.
3

I don't think I explained myself correctly, its the width of the bottom horizontal scroll bar that is not maintained - not the position - that we have working fine.

I've attached a before image to show an example, see the section circled in blue - notice how the scroll bar in the before file is resized to be able to see the tabs, where in the after shot, its not maintained and you cannot see all the tabs.

I will do a second post to show the after image

4

Here is the after shot

5

Hi,

is it possible that you might have used some cells in some farthest columns on the right side of the worksheet. Or you might have format some rows entirely in the worksheet.

Thank you.

6

No - reviewing the code - the only thing we do is open the spreadsheet, fill data from column 0 to 76, with as many rows as there is data - and then save it out to the client via the Output Stream and using FileFormatType.Excel2003.

Here is the code:

///


/// This is to write data record into a formatted excel from the client. The excel displays different charts in different worksheet base on the data saved in excel.
///
/// reference to the page object
/// DataTable that will supply data to excel
/// the position of the worksheet in excel that will be active when excel is opened
public static void DisplayDebtorExcel(Page page, DataTable dtDebtorCustomReport, int activeWorksheet) {
License license = new License();
license.SetLicense("Aspose.Cells.lic");

Workbook book = new Workbook();
book.Open(".../DebtorDownloadSpreadsheet.xls"), FileFormatType.Excel2003);
FillInvoiceData(book.Worksheets["Debtor Report"],dtDebtorCustomReport);
book.Worksheets.ActiveSheetIndex = activeWorksheet;

string contentDispositionMode = "attachment";
string contentType = "application/vnd.ms-excel";

page.Response.Buffer = false;
page.Response.Clear();
page.Response.ContentType = contentType;
page.Response.AddHeader("Content-Disposition", contentDispositionMode + "; filename=DebtorReport.xls");

book.Save(page.Response.OutputStream, FileFormatType.Excel2003);
page.Response.End();
}

The FillInvoiceData method basically loops through the data rows with code like similar to this:

ws.Cells[1+lup, 0].PutValue(dtDebtorCustomReport.Rows[lup]["clientReference"]);
ws.Cells[1+lup, 1].PutValue(dtDebtorCustomReport.Rows[lup]["debtorName"]);
ws.Cells[1+lup, 2].PutValue(dtDebtorCustomReport.Rows[lup]["assignedDate"]);
ws.Cells[1+lup, 2].Style.Number = 14;

Do you see anything in this code that would cause the width of the bottom horizontal scroll bar to change?

7

Hi,

We will support the feature that set the sheet tab tab width soon. Thanks for your patience.

8

Thank you for the update - I take it the next hot fix will include this?

Is it possible to have some notification of when this is available or do I need to just keep an eye on the downloads section, and is an eta available? (I know my client will ask me)

9

Hi,

We will notify you in this thread when this will be supported.

Thank you.

10

Hi,

Please try this fix. Please use workbook.Worksheets.SheetTabBarWidth property(in unit of 1/1000 of window width) to set the width of sheet tab bar width.

In this fix,we use Cell.GetStyle and SetStyle method replace the property of Cell.Style. The two methods will save more memory usage than cell.Style property.

If you want to set style of the cell, please change your codes as :

//Getting the style of the cell.

Style style = cell.GetStyle();

//Setting the property of the style.

.......

//Setting the style of the cell

cell.SetStyle(style);