This project has moved. For the latest updates, please go here.

Scroll to the latest cell modified

Jul 9, 2015 at 9:37 AM
Hello and thanks for your awsome work!

By the way, I would want to know if there is a way to positioning the document in the last cell you modifed when you open it again since your last saved.

In the Microsoft.Office.Interop.Excel you can do this with the following commands:
            xlApp.ActiveWindow.ScrollRow = 100
            xlApp.ActiveWindow.ScrollColumn = 2
This will position the cursor to that cell (100,2) scrolling the document automatically in the active tab when you opened.

Thanks in advance,

Javier
Editor
Jul 9, 2015 at 11:42 AM
Edited Jul 9, 2015 at 11:44 AM
Hi,

Yes, you can achieve this with ActiveCell property.
            var pck = new ExcelPackage(new FileInfo(@"c:\test.xlsx"));
            var ws = pck.Workbook.Worksheets.Add("Sample1");
            ws.View.ActiveCell = "H1";
            ws.Cells["A1"].Value = "Sample 1";
            ws.Cells["A1"].Style.Font.Bold = true;

            pck.Save();
Jul 9, 2015 at 1:09 PM
Thanks for your answer,

I already managed to set the active cell using this sentence instead:

xlWorkSheet.Select(xlWorkSheet.Cells(RowIndex, 1).Address, True)

However I want to scroll automatically to that position once I open the document but I cannot find the solution in any of the examples you provide with the solution. Do you think this is possible using EPPlus?

Thanks again in advance
Javier
Editor
Jul 9, 2015 at 1:48 PM
Edited Jul 9, 2015 at 2:13 PM
Hi,

I was thinking that excel will scroll to active cell after opened a document, but it is not true. You can emulate this behaviour with TopLeftCell but there is no implementation of this attribute in EPPlus, so you'll need to edit our code and build your own dll.

ExcelWorksheetView.cs
        public string TopLeftCell
        {
            get
            {
                return GetXmlNodeString("@topLeftCell");
            }
            set
            {
                SetXmlNodeString("@topLeftCell", string.IsNullOrEmpty(value) ? "A1" : value );
            }
        }
Example:
            var pck = new ExcelPackage(new FileInfo(@"c:\temp\test1.xlsx"));
            var ws = pck.Workbook.Worksheets.Add("Sample1");
            ws.View.ActiveCell  = "A2000";
            ws.View.TopLeftCell = "A1910";
            ws.Cells["A1"].Value = "Sample 1";
            ws.Cells["A1"].Style.Font.Bold = true;

            pck.Save();
Jul 10, 2015 at 7:37 AM
Edited Jul 10, 2015 at 7:42 AM
Thanks again!,
that works good with your example. However if you use freeze panes the behaviour is completely different. For example, if you add the following line to your code, you will see the table is being truncated until TopLeftCell.
        Dim pck As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo("c:\test.xlsx"))
        Dim ws As OfficeOpenXml.ExcelWorksheet = pck.Workbook.Worksheets.Add("Sample1")
        ws.View.ActiveCell = "H100"
        ws.View.TopLeftCell = "A85"
        ws.Cells("A1").Value = "Sample 1"
        ws.Cells("A1").Style.Font.Bold = True

        ws.View.FreezePanes(2, 1)

        pck.Save()
I have tried a workaround commenting the folloinwg line in the method FreezePanes(int Row, int Column) but it's still not working.

//paneNode.SetAttribute("topLeftCell", ExcelCellBase.GetAddress(Row, Column));

Do you have any idea how to solve this issue?

Thanks in advance
Javier
Editor
Jul 10, 2015 at 1:18 PM
I managed (somehow) to do this but I don't know if it will work for all cases...

class ExcelWorksheetPanes inside ExcelWorksheetView.cs
            string _paneNodePath = "d:pane";
            public string TopLeftCell
            {
                get
                {
                    var paneNode = TopNode.ParentNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
                    if (paneNode != null && paneNode.HasAttribute("topLeftCell")) return paneNode.GetAttribute("topLeftCell");
                }

                set
                {                    
                    var paneNode = TopNode.ParentNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
                    if (paneNode != null && !string.IsNullOrEmpty(value)) paneNode.SetAttribute("topLeftCell", value);
                }
            }
         var pck = new ExcelPackage(new FileInfo(@"c:\temp\test.xlsx"));
            var ws = pck.Workbook.Worksheets.Add("Sample1");            
            ws.Cells["A1"].Value = "Sample 1";
            ws.Cells["A1"].Style.Font.Bold = true;
            ws.View.FreezePanes(2, 1);
            ws.View.Panes[0].TopLeftCell = "A30";
            pck.Save();
Jul 14, 2015 at 6:10 AM
Thanks for your help!
It works like a charm.
Regards,
Javier