none
OpenXml,Excel,C# RRS feed

  • Question

  • Hi,

    Please explain, how to freeze particular row in the excel sheet using openXML in C#.

     

    thanks,

    Elangovan P

    Tuesday, December 20, 2011 2:46 PM

All replies

  • Hi Elangovan P,

    Thank you for posting.

    I coded the following console application which can freeze the A10 in Sheet1:

    using System.Collections.Generic;
    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
     
    namespace OpenXmlExcelFreezeExistingSheet
    {
        class Program
        {
            static void Main(string[] args)
            {
                if (File.Exists("copy.xlsx"))
                {
                    File.Delete("copy.xlsx");
                }
     
                File.Copy("template.xlsx""copy.xlsx");
                using (SpreadsheetDocument xl = SpreadsheetDocument.Open("copy.xlsx"true))
                {
                    WorkbookPart wbp = xl.WorkbookPart;
                    WorkbookPart workbook = xl.WorkbookPart;
                    IEnumerable<Sheet> sheets = xl.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1");
     
                    if (sheets.Count() == 0)
                    {
                        // The specified worksheet does not exist.  
                        return;
                    }
                    WorksheetPart wsp = (WorksheetPart)xl.WorkbookPart.GetPartById(sheets.First().Id);
     
                    SheetView sw = wsp.Worksheet.SheetViews.FirstOrDefault() as SheetView;
     
                    // the freeze pane
                    Pane pane = new Pane()
                    {
                        VerticalSplit = 9D,
                        TopLeftCell = "A10",
                        ActivePane = PaneValues.BottomLeft,
                        State = PaneStateValues.Frozen
                    };
     
                   // Selection selection = new Selection() { Pane = PaneValues.BottomLeft };
                    sw.Append(pane);
                   // sw.Append(selection);
                    wsp.Worksheet.Save();
                    wbp.Workbook.Save();
                    xl.Close();
                }
            }
        }
    }
    

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 22, 2011 3:36 AM
  • Hi Bruce,

    I tried the above but couldn't able to freeze the particular row .

     

    thanks,

    Elangovan P

    Thursday, December 22, 2011 7:36 AM
  • Hi Elangovan P,

    What do you mean about freezing the particular row? I just froze the 10th row as an example:

    Is this not what you wanted? If I have misunderstood you, just feel free to let me know.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 22, 2011 8:15 AM
  • Hi Bruce,

    Yes, thats what i want. But its not freezing the row .

    I got the below error. while executing the line  

     

    sw = wsp.Worksheet.SheetViews.FirstOrDefault() as SheetView

     

    Value cannot be null.
    Parameter name: source

    Thursday, December 22, 2011 10:22 AM
  • Hi Elangovan,

    I do not know why you can't get the SheetView element with you Excel file. You can download the project named OpenXmlExcelFreezeExistingSheet from the skydrive: http://www.skydrive.com which I have uploaded:

    https://skydrive.live.com/?cid=C7D080162E80A07A&id=C7D080162E80A07A%21115

    See whether it works well on your side?

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 4:31 AM
  • Elangovan, how about the problem on your side? If you still show any concern on the problem, just feel free to let us know.Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 27, 2011 4:58 AM
  • Hi Bruce,

    Thanks for the reply. Still have the same problem.

    thanks,

    Elangovan P

    Tuesday, December 27, 2011 5:05 AM
  • Hi Elangovan P,

    Would you please send me a copy of your Excel file? You can send my email address: v-bpeng@microsoft.com , I will test on my side with your file, I think the problem is related to your file because it indeed works well on my side?

    How about creating a new Excel file on your side? Does that work well?

    I may reply to you next week becuase of my two days annual leave. Thank you for your patient. Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    • Edited by Bruce Song Wednesday, December 28, 2011 3:27 AM
    Wednesday, December 28, 2011 3:24 AM
  • Hi Bruce,

    I will sent it to you. Can you please  help me on pivot tables. please explain to create pivot table in sheet1 based on the data in sheet2 .

     

    thanks,

    Elangovan P



    Wednesday, December 28, 2011 5:06 AM
  • Hi Elangovan P,

    Sorry for the late reply.

    It seems that you can't get the SheetView element from your Excel file. Would you send me a copy of your Excel file for me to test? Or it will be better if you can send me your whole project so that I can debug on my side.

    My sample code always works well on my side? How about creating a new file on you side for testing?

    Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Bruce Song Monday, January 2, 2012 2:21 PM
    Monday, January 2, 2012 4:02 AM
  • I bet that 
    developer
    0 Points

     is not using a Template but is creating from scratch.

    What would be helpful is some code for retrieving the SheetViews part (or creating if it doesn't exist)

    Then retrieving the SheetView (like you have) would probably work for her/him (sorry, I have never seen the name Elangovan before)

    Mitch


    Mitch Bird

    Wednesday, March 14, 2012 6:34 PM
  • Hi anyone resolved this issue of having SheetView 'null'?

    Monday, March 3, 2014 9:43 AM