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 1/12/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...

  1.     /// <summary>
  2.     ///     Splits the first sheet in an OpenXML Excel File into 2 vertical panes.
  3.     ///     If the file, workbook or worksheet don't exist the method will exit
  4.     ///     without throwing an exception.
  5.     /// </summary>
  6.     /// <param name="filename">Full Path and Filename of the OpenXML Excel Files</param>
  7.     /// <param name="verticalSplitHeight">Value in 1/20th of a point</param>
  8.     /// <param name="topLeftCellAddressForUpperPane">
  9.     ///     In A1 style - upper left corner of the top pane, left column for both
  10.     ///     panes
  11.     /// </param>
  12.     /// <param name="topRowForLowerPane">Top row for the lower pane</param>
  13.     /// <returns>The Filename</returns>
  14.     public static string ExcelFileFirstSheetTwoSplitVerticalPanes(this string filename, double verticalSplitHeight,
  15.         string topLeftCellAddressForUpperPane, int topRowForLowerPane)
  16.     {
  17.         //1/1/2023 - As far as I can tell ClosedXML doesn't offer options to create a Vertical
  18.         //Split (not Freeze) with the TopLeft Cell set for both panes.
  19.         //
  20.         //Let me know if this is incorrect and this functionality is available - it would be
  21.         //better not to re-open and re-save the file...But all things considered getting the
  22.         //correct view setup for a user can be an important enough
  23.         //feature to merit this approach.
  24.  
  25.         if (!File.Exists(filename)) return filename;
  26.  
  27.         using var xl = SpreadsheetDocument.Open(filename, true);
  28.  
  29.         var workbook = xl.WorkbookPart;
  30.         var worksheet = workbook?.WorksheetParts.FirstOrDefault();
  31.         if (worksheet?.Worksheet.SheetViews?.FirstOrDefault() is not SheetView view) return filename;
  32.  
  33.         //Clear the view for the new Pane and Selections created below
  34.         view.RemoveAllChildren();
  35.  
  36.         //This will set the upper pane's top left cell
  37.         view.TopLeftCell = topLeftCellAddressForUpperPane;
  38.  
  39.         //Setup the split - note that VerticalSplit is the number of rows when Freezing
  40.         //but is "Vertical position of the split, in 1/20th of a point" when splitting.
  41.         //   https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.pane?view=openxml-2.8.1
  42.         var newPane = new Pane
  43.         {
  44.             VerticalSplit = verticalSplitHeight,
  45.             TopLeftCell = $"A{topRowForLowerPane}",
  46.             ActivePane = PaneValues.BottomLeft,
  47.             State = PaneStateValues.Split
  48.         };
  49.  
  50.         //Set a reasonable Selection
  51.         var lowerPaneTopLeftCellAddress =
  52.             $"{string.Concat(topLeftCellAddressForUpperPane.Where(x => !char.IsDigit(x)))}{topRowForLowerPane}";
  53.  
  54.         var selectionSor = new ListValue<StringValue>();
  55.         selectionSor.Items.Add(lowerPaneTopLeftCellAddress);
  56.  
  57.         var selection = new Selection
  58.         {
  59.             SequenceOfReferences = selectionSor,
  60.             ActiveCell = lowerPaneTopLeftCellAddress,
  61.             Pane = PaneValues.BottomLeft
  62.         };
  63.  
  64.         //Add the Pane and Selection - save the file.
  65.         view.Append(newPane);
  66.         view.Append(selection);
  67.         worksheet.Worksheet.Save();
  68.         workbook.Workbook.Save();
  69.         xl.Close();
  70.  
  71.         return filename;
  72.     }
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.


Tags:
Posts Before: