SpreadsheetGear for Windows 9.X UPDATE

SpreadsheetGear for Windows 9.X UPDATE

SpreadsheetGear for Windows IS Excel Calculations
Evaluate complex Excel based models and business rules with the fastest and most complete Excel compatible calculation engine available, including 449 Excel compatible functions, multi-threaded recalculations, array formula support and more.

Controls for WPF and Windows Forms
Add powerful viewing, editing, formatting, calculating, charting and printing capabilities to your Windows Forms or WPF applications with WorkbookView and FormulaBar controls. WinForms and WPF applications can also utilize the Workbook Explorer, Range Explorer, Chart Explorer and Shape Explorer dialogs.

 Recent Changes

Please see the below list for release notes relating to the most recent versions of SpreadsheetGear for Windows, and refer further down on this page for an exhaustive list of release notes for all current and past versions.

• 9.2.11 Fixed CommandManager(IWorkbookSet) constructor to work with no WorkbookView attached to the workbook set. (Oct 12, 2023)
• 9.2.12 Fixed INames.Add to throw an exception if 'formula' is text (not a formula with leading '=') longer than 255 characters as Excel does. (Oct 18, 2023)
• 9.2.12 Fixed issues with OR, AND and XOR functions which caused them to return incorrect results in certain cases involving 0.0 and / or certain text values. (Oct 18, 2023)
• 9.2.13 Added a work around to read workbooks written by certain 3rd party software with invalid VML. (Nov 1, 2023)
• 9.2.14 Fixed issues with SpreadsheetGear for Windows text layout when only single partial characters are viewable. (Nov 4, 2023)
• 9.2.14 Fixed copying conditional formats to fixup formulas in Extended Criteria. (Nov 4, 2023)
• 9.2.14 Fixed an issue which caused worksheet corruption and various exceptions when copying formulas containing text to certain types of destinations. (Nov 4, 2023)
• 9.2.14 Fixed issues with SpreadsheetGear for Windows text layout when only single partial characters are viewable. (Nov 4, 2023)
• 9.2.20 Fixed adding cell comments to position the comments within visible rows and columns. (Nov 9, 2023)
• 9.2.21 Fixed an issue which caused SUMIF and COUNTIF to sometimes return incorrect results if the first argument only has one row. (Nov 10, 2023)
• 9.2.21 Fixed issues with data validation listbox when used on cells near or at end of worksheet. (Nov 10, 2023)
• 9.2.22 Fixed an issue which caused Hyperlinks in cells to display incorrectly in certain cases. (Nov 13, 2023)

SpreadsheetGear for Windows Version 9

September 26, 2023 - Version 9 adds a new "SpreadsheetGear for Windows" product for .NET 6 for Windows with support for high DPI and rich text rendering, cell comments and form controls in Open XML files, fine-grained worksheet protection options, embedded pictures in print headers and footers, document properties, shape enhancements, official support for Excel 2021 and more.

New Features

• NEW PRODUCT SpreadsheetGear for Windows
  • Optimized for .NET 6+ for Windows.
  • Brings the full power of SpreadsheetGear for .NET Framework to .NET 6+ Windows Forms, WPF and ASP.NET Core developers.
  • Includes SpreadsheetGear Engine for .NET.
  • Full Windows Per-Monitor (V2) high DPI support.
  • Added new text layout engine to provide consistent text layout between WPF, Windows Forms and Image Rendering APIs.
• Support for reading and writing cell comments and form controls with Open XML (.xlsx and .xlsm) workbooks.
• Official support for Excel 2021 and the latest versions of Excel for Microsoft 365.
• Support for embedded pictures in print headers and footers.
• Support for standard and custom document properties.
• Chart enhancements including:
  • Support for embedding shapes within charts.
  • Support for creating chart sheets.
  • Added a number of IChartSheet properties and methods as well as the related IChartSheetWindowInfo and IChartSheetWindowInfos interfaces.
• Shape enhancements for better compatibility with Excel including:
  • Arrowheads and other markers on lines.
  • Better support for radio button form controls.
  • Support for grouping and ungrouping shapes.
  • Support for autosizing of cell comments and text objects.
  • Support for picture cropping including support for negative picture crop values.
• Support for fine-grained worksheet protection options including:
  • Select locked cells
  • Select unlocked cells
  • Format cells
  • Format columns
  • Format rows
  • Insert columns
  • Insert rows
  • Insert hyperlinks
  • Delete columns
  • Delete rows
  • Sort
  • Use AutoFilter
  • Edit objects
• Updated new workbook theme to more closely match recent versions of Excel.
• The SpreadsheetGear.Drawing.Image class supports setting the output DPI.
• Added support for Alt+Down key combination to display the data validation dropdown list control.
• Support for rendering rich text in cells in Windows Forms WorkbookView (.NET 6+ only).
• Added an IWorksheet.Comments[...] collection that returns all cell comments for the specified worksheet.

Breaking Changes

• As part of adding the IWorksheet ProtectDrawingObjects and ProtectScenarios properties, setting IWorksheet.ProtectContents was changed to no longer have the effect of modifying these two properties.
• Changed the CHAR and CODE worksheet functions to use CultureInfo.TextInfo.ANSICodePage to convert to and from Unicode.
• Changed IWorksheets.Add to keep track of the last number used to automatically generate the name such as "Sheet1". This number is reset to 1 when a workbook is loaded which will lead to different behavior. The new IChartSheets.Add follows the same strategy with it's own next sheet number. This behavior is the same as Excel.
• Removed the deprecated LineStyle.Continous enumerated value. Please use LineStyle.Continuous instead.
• Changed IWorkbookSet.CalculateFullRebuild to no longer try to optimize memory usage and added new IWorkbookSet.GC method which does try to optimize memory usage.
• Changed the WEBSERVICE function to be disabled by default. To enable WEBSERVICE you must set IWorkbookSet.EnableWebService to true.

Issues Addressed

• Improved number parsing and formatting accuracy.
• Fixed an issue which caused a failure to fixup cell references in certain cases where multiple conditional format conditions apply to a cell and the criteria is a complex formula with relative references.
• Fixed number formatting to handle cases such as "a"General"b".
• Fixed to allow copying from a partial array formula range.
• Fixed WorkbookView.ContextMenu property to initialize the context menu before returning it in the property getter.
• Improved IRR to no longer favor values between the guess and zero over values closer to the guess.
• Fixed hiding and showing of rows and columns to update IRange.ShowDetails as needed.
• Fixed issue with changed formula cell in view area not rendering in some cases when scrolling.
• Fixed thousands separator parsing and formatting to consider '\u202F' as well as '\u00A0'.
• Fixed a performance issue which occurred when there are large numbers of defined names where many of these names refer to names which refer to names, etc...
• Fixed some painting issues when scrolling past ends of worksheet.
• Fixed various issues with Data Validation dropdown.
• Fixed a problem which caused an exception to be thrown if identical Unicode text longer than 127 characters is contained in more than 255 cells.
• Fixed a problem which caused reading of certain password protected Open XML workbooks to throw an exception.
• Fixed a case where we were trying to handle OnPaint when calling Control.Dispose while updating the screen after scrolling (another case of the OLE COM message pump issue).
• Fixed a NullReferenceException with cell validation when a modal dialog is displayed after clicking the mouse.
• Fixed a problem which sometimes occurred when copying array formulas to other cells in the same column.
• Fixed a problem with copying to the clipboard in some cases when auto-filters are applied and the source range has exactly one visible row.
• Fixed a problem with EOMONTH which caused it to return an incorrect result when used in a non-array cell formula with arguments including a MATCH function with an array constant for the 'lookup_array' argument.
• Fixed a problem with IRange.Copy which occurred when used with PasteType.Values and fewer rows in the source range than the destination range.
• Fixed a variety of issues with print black and white.
• Fixed an issue which caused a null reference exception to be thrown when copying a worksheet which contains worksheet specific defined names which contain circular references.
• Fixed an issue which caused an exception to be thrown in certain cases when pasting cell formulas which reference worksheet scoped defined names on a worksheet in another workbook.
• Fixed an issue which caused certain complex conditional formats to not repaint after a workbook set is calculated.
• Fixed an issue which caused LINEST, LOGEST, GROWTH and TREND to sometimes return inconsistent results if the number of columns in 'known_xs' is greater than the number of rows in 'known_ys'.
• Fixed an issue which caused LINEST, LOGEST, GROWTH and TREND to fail to remove colinear columns in certain cases when there are multiple colinear columns.
• Fixed an issue which caused conditional formats to sometimes not be rendered correctly when they are scrolled into view.
• Fixed an issue which caused exceptions to be thrown in cases where multiple unused WorkbookView controls are reclaimed by the garbage collector at one time without having had Dispose called on them.
• Fixed cases where adding a merged cell incorrectly enlarges the current selected range even if the selection contains entire rows or entire columns.
• Fixed column width issue which caused large column widths to be truncated with smaller than normal default workbook fonts.
• Fixed data validation listboxes to ignore blank entries in criteria specified by hard coded comma separated lists.
• Fixed dropdown listboxes to work with a single cell reference for the linked range.
• Fixed hyperlinks to handle square braces such as [address]subaddress.
• Fixed IFERROR problems with certain complex formulas.
• Fixed issue with number formatting and precision as displayed where format == "# ?/?%" and the value is 9E21.
• Fixed issues with Hyperlink linked to external workbook reference.
• Fixed parsing formatted numbers which are both fractions and percent.
• Fixed Outline expanders to work more like Excel when rows or columns are hidden within a grouped range.
• Fixed saving of merged cells extending beyond IV65536 to xls workbooks to maintain the partial merged cell as Excel does.
• Fixed to be able to read workbooks with table references containing double quotes.
• Fixed worksheet and chartsheet numbering for new sheets to work like Excel. They each keep their own next sheet number and that number gets reset to 1 when a workbook is opened.
• Fixed various issues with data validation listbox not refreshing location when changing selection.
• Fixed writing column widths to xls when width is > UInt16.MaxValue.
• Fixed various issues with TextBox editing on charts and chart sheets.
• Fixed WorkbookView OnPaint exception in Windows Forms when closing Workbook Designer with two child workbooks each containing a form control.
• Improved performance of merging large ranges of cells.
• 9.2.11 Fixed CommandManager(IWorkbookSet) constructor to work with no WorkbookView attached to the workbook set. (Oct 12, 2023)
• 9.2.12 Fixed INames.Add to throw an exception if 'formula' is text (not a formula with leading '=') longer than 255 characters as Excel does. (Oct 18, 2023)
• 9.2.12 Fixed issues with OR, AND and XOR functions which caused them to return incorrect results in certain cases involving 0.0 and / or certain text values. (Oct 18, 2023)
• 9.2.13 Added a work around to read workbooks written by certain 3rd party software with invalid VML. (Nov 1, 2023)
• 9.2.14 Fixed issues with SpreadsheetGear for Windows text layout when only single partial characters are viewable. (Nov 4, 2023)
• 9.2.14 Fixed copying conditional formats to fixup formulas in Extended Criteria. (Nov 4, 2023)
• 9.2.14 Fixed an issue which caused worksheet corruption and various exceptions when copying formulas containing text to certain types of destinations. (Nov 4, 2023)
• 9.2.14 Fixed issues with SpreadsheetGear for Windows text layout when only single partial characters are viewable. (Nov 4, 2023)
• 9.2.20 Fixed adding cell comments to position the comments within visible rows and columns. (Nov 9, 2023)
• 9.2.21 Fixed an issue which caused SUMIF and COUNTIF to sometimes return incorrect results if the first argument only has one row. (Nov 10, 2023)
• 9.2.21 Fixed issues with data validation listbox when used on cells near or at end of worksheet. (Nov 10, 2023)
• 9.2.22 Fixed an issue which caused Hyperlinks in cells to display incorrectly in certain cases. (Nov 13, 2023)