cmiles - info

Life, Tech and Unimportant Minutiae

ClosedXML and OpenXML - Splitting Worksheets in Excel - 1/5/2023

Created by Charles on 1/1/2023. Updated on 6/17/2023.

2023 January Saguaros and Cloud Break Light
Saguaros and Cloud Break Light. Charles Miles. 1/1/2023.

Andrew Whitechapel's 2005 "Getting the Application Object in a Shimmed Automation Add-in" - now only available via the Wayback Machine - was an early code source and inspiration when I start creating Excel files in .NET over 15 years ago.

For many years the COM Interop approach facilitated by the code above was the main technique I used for generating Excel files - but by the mid-2010s it was clear to me that using libraries like ClosedXML, which don't require the Excel application to be installed, are a better approach.

At work the task of moving many years of Excel COM Interop based reporting to ClosedXML is an ongoing, long-term, low-priority task.

At the end of 2022 I tackled converting a heavily used report where the final step of the report creation is splitting and positioning the user's initial view. (Note here that 'splitting' is not the same as 'freezing'...)

I couldn't find a way to do this in ClosedXML... So I decided that after writing the file to disk with ClosedXML I would use OpenXML to setup the split. A simple enough approach (that eventually worked), but working directly thru OpenXML is not my usual code path and the first few failures made it clear that I was going to need some reference material on what properties to set with what values.

I used the one trick I remembered for these files to help me look at the underlying XML - I renamed the file with a .zip extension and then navigated into it. This works and it is a good trick because it doesn't require any additional programs/tooling - if I had managed to immediately intuit the correct code changes I wouldn't have given this hack a second thought... But I didn't get the code right in the first few tries and as I tested code changes I very quickly wanted a better way to take a direct look at the files' XML.

After a little searching I found the Borislav Ivanov's Open XML Package Editor Power Tool for Visual Studio. Once installed in Visual Studio you can drop an Open XML/Excel file into Visual Studio and start exploring. This was fantastic and let me find the last detail I needed...

The code below has so far stood up to some light testing - presented as research and reading material more than code you should copy and paste...

/// <summary>
///     Splits the first sheet in an OpenXML Excel File into 2 vertical panes.
///     If the file, workbook or worksheet don't exist the method will exit
///     without throwing an exception.
/// </summary>
/// <param name="filename">Full Path and Filename of the OpenXML Excel Files</param>
/// <param name="verticalSplitHeight">Value in 1/20th of a point</param>
/// <param name="topLeftCellAddressForUpperPane">
///     In A1 style - upper left corner of the top pane, left column for both
///     panes
/// </param>
/// <param name="topRowForLowerPane">Top row for the lower pane</param>
/// <returns>The Filename</returns>
public static string ExcelFileFirstSheetTwoSplitVerticalPanes(this string filename, double verticalSplitHeight,
    string topLeftCellAddressForUpperPane, int topRowForLowerPane)
    //1/1/2023 - As far as I can tell ClosedXML doesn't offer options to create a Vertical
    //Split (not Freeze) with the TopLeft Cell set for both panes.
    //Let me know if this is incorrect and this functionality is available - it would be
    //better not to re-open and re-save the file...But all things considered getting the
    //correct view setup for a user can be an important enough
    //feature to merit this approach.

    if (!File.Exists(filename)) return filename;

    using var xl = SpreadsheetDocument.Open(filename, true);

    var workbook = xl.WorkbookPart;
    var worksheet = workbook?.WorksheetParts.FirstOrDefault();
    if (worksheet?.Worksheet.SheetViews?.FirstOrDefault() is not SheetView view) return filename;

    //Clear the view for the new Pane and Selections created below

    //This will set the upper pane's top left cell
    view.TopLeftCell = topLeftCellAddressForUpperPane;

    //Setup the split - note that VerticalSplit is the number of rows when Freezing
    //but is "Vertical position of the split, in 1/20th of a point" when splitting.
    var newPane = new Pane
        VerticalSplit = verticalSplitHeight,
        TopLeftCell = $"A{topRowForLowerPane}",
        ActivePane = PaneValues.BottomLeft,
        State = PaneStateValues.Split

    //Set a reasonable Selection
    var lowerPaneTopLeftCellAddress =
        $"{string.Concat(topLeftCellAddressForUpperPane.Where(x => !char.IsDigit(x)))}{topRowForLowerPane}";

    var selectionSor = new ListValue<StringValue>();

    var selection = new Selection
        SequenceOfReferences = selectionSor,
        ActiveCell = lowerPaneTopLeftCellAddress,
        Pane = PaneValues.BottomLeft

    //Add the Pane and Selection - save the file.

    return filename;
2023 January A Rainbow for the New Year
A Rainbow for the New Year. Charles Miles. 1/1/2023.

PS - COM Interop is still a key desktop technology especially if you want to read data from, or write data to, a file the user has open in Excel. For a modern update to Andrew Whitechapel's approach see Jamie Faix's Automate multiple Excel instances on Codeproject. I have lightly modified Faix's code for the ExcelInteropExtensions in my Pointless Waymarks Project.

Posts Before/After: