1

I am creating an Excel spreadsheet (XLSX) from an existing template and adding a dataset to a table on one of the worksheets. I have been able to programmatically set the active sheet, but I can't set where the scroll position of the sheet is so that the last rows of data are displayed when someone opens the spreadsheet in Excel.

I'm adding 5000+ rows but when someone opens the file in Excel, the scroll position is where it was when the original template was saved. The cell I set to selected is indeed selected but it's off screen.

Here's my code:

using (var workbook = new XLWorkbook(fileInfo.FullName))
{
workbook.Properties.Author = _configuration.GetSection("ExcelAuthor").Value;

var workSheet = workbook.Worksheet(_configuration.GetSection("ExcelWorkSheetName").Value);
var table = workSheet.Table(_configuration.GetSection("ExcelTableName").Value);

var range = table.ReplaceData(data.Tables[0]);

//Set the sheet as the active sheet
foreach (var sheet in workbook.Worksheets)
{
    var isCurrentSheet = sheet.Equals(workSheet);

    sheet.SetTabActive(isCurrentSheet);
    sheet.SetTabSelected(isCurrentSheet);
}

 //workSheet.ActiveCell = range.LastCell();
 workSheet.Cell(range.LastCell().Address).Select();

 //range.LastCell().Select();
 //range.LastCell().SetActive();



path = string.Format("{0} - {1}.xlsx", _configuration.GetSection("FileNamePrefix").Value, DateTime.Now.ToString("yyyy-MM-dd"));
path = string.Format("{0}\\{1}", _configuration.GetSection("OutputFolder").Value.TrimEnd(new[] { '/', '\\' }), path);
workbook.SaveAs(path);

};

Warren Stroebel
  • 13
  • 4

2 Answers2

1

I submitted a pull request and this is fixed in https://github.com/ClosedXML/ClosedXML/pull/1561

Francois Botha
  • 4,520
  • 1
  • 34
  • 46
0

I'm not sure that it's possible to set the exact scroll position, but you can set the currently selected cell.

var wb = new XLWorkbook();
var wsActiveCell = wb.AddWorksheet("Set Active Cell");
wsActiveCell.Cell("B2000").SetActive();

Reference: https://github.com/ClosedXML/ClosedXML/wiki/Selecting-Cells-and-Ranges

Francois Botha
  • 4,520
  • 1
  • 34
  • 46