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
- view.RemoveAllChildren();
- //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.
- // https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.pane?view=openxml-2.8.1
- 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>();
- selectionSor.Items.Add(lowerPaneTopLeftCellAddress);
- var selection = new Selection
- {
- SequenceOfReferences = selectionSor,
- ActiveCell = lowerPaneTopLeftCellAddress,
- Pane = PaneValues.BottomLeft
- };
- //Add the Pane and Selection - save the file.
- view.Append(newPane);
- view.Append(selection);
- worksheet.Worksheet.Save();
- workbook.Workbook.Save();
- xl.Close();
- return filename;
- }
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.
