CODEWITHSUNDEEP
c#winformsexceloffice-interopexcel-interop
Vbp
Vbp

Reputation: 1962

Freeze Panes in multiple worksheets C#

I am working on MS Excel 2013 generating report where all the worksheets in workbook should have freeze pane at column 6 and row 1. I have searched on Google but could not find any solution as for freezing the pane, workbook has to be active. I have tried a lot of things but no success. I will really appreciate if someone can help me.

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open("filelocation");

foreach (Excel.Worksheet ws in workbook.Worksheets)
{
    ws.Application.ActiveWindow.SplitColumn = 6;
    ws.Application.ActiveWindow.SplitRow = 1;
    ws.Application.ActiveWindow.FreezePanes = true;
}

excel.Visible = true;

Upvotes: 6

Views: 6518

Answers (2)

Mel
Mel

Reputation: 21

To freeze the panes on each worksheet you need to modify your for loop to add a line to activate the current sheet prior to setting the other properties. Here is my solution:

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open("filelocation");

foreach (Excel.Worksheet ws in workbook.Worksheets)
{
    ws.Activate();
    ws.Application.ActiveWindow.SplitColumn = 6;
    ws.Application.ActiveWindow.SplitRow = 1;
    ws.Application.ActiveWindow.FreezePanes = true;
}

excel.Visible = true;

Upvotes: 2

Vbp
Vbp

Reputation: 1962

I Hope it help others. I have used ClosedXML Library for Excel and after creating each worksheet I used 

worksheet.SheetView.Freeze(1,6);

This freezes the Row 1, Col 6. You can freeze any row/column. Here is link to ClosedXML. It's widely supported and very good documentation.

Upvotes: 12

Related Questions