EPPlus 5/6/7
Features and technical overview
Change log/version history
Version 7.4.1
Released: 2024-10-11, Github release
- Updated for vulnerability in System.Text.Json 8.0.4 - Microsoft.Extensions.Configuration.Json 8.0.0 -> 8.0.1
Version 7.4.0
Released: 2024-10-02, Github release
Features
- Support for Conditional Formatting's in Pivot tables via Pivot Areas.
- Added support for multiple addresses in function arguments, i.e Sum((A1:A2,A4:A5)) should be handled as a single argument.
- Added support for linked pictures in the ExcelDrawings.AddPicture method using the 'PictureLocation' argument.
- Added new function
- AREAS
- TDIST
Fixed issues
- Loading a vml drawing without the Anchor element caused EPPlus to throw an Exception.
- Removed unused classes ParsingScope and ParsingScopes and the interface IParsingLifetimeEventHandler
Version 7.3.2
Released: 2024-09-17, Github release
Fixed issues
- Upgraded System.Text.Json to version 8.0.4 and System.Formats.Asn1 to version 8.0.1 to address vulnerabilities in previous versions of these libraries. See our Security Policy
- Upgraded Microsoft.IO.RecyclableMemoryStream from version 3.0.0 to 3.0.1.
- Fixed an issue with the MATCH function when a range was sorted in descending order.
- When inserting columns, shifting right into a range, an Exception was thrown in rare cases.
Version 7.3.1
Released: 2024-09-05, Github release
Fixed issues
- Fixed duplication of conditional formatting’s which could occur on repeated saves in some cases.
- Fixed issues with Boolean values in functions COUNTIFS, SUMIFS and AVERAGEIFS.
- ExcelWorksheet.DimensionByValue sometimes gave an incorrect address.
- EPPlus crashed on loading a workbook, if style Fills contained less than 2 items.
- ExcelRange.Insert did not shift formula address on other worksheets.
- The formula tokenizer failed when having a minus before an Error value (i.e. #REF) in some cases.
- Accessing PivotTables when "Save source data with file" was off caused a NullReferenceException.
- The Dec2Hex, Dec2Oct and Dec2Bin functions rounded values instead of truncating them.
Version 7.3.0
Released: 2024-08-20, Github release
Features
- Add support for Precision As Displayed when calculating formulas, via the 'ExcelWorkbook.FullPrecision' property.
- Added new functions
- GROWTH
- DPRODUCT
- DSTDEV
- DSTDEVP
Fixed issues
- EPPlus now supports reading workbooks encoded with other encodings than UTF8.
- Added new parameter to ExcelRangeBase.CreateArrayFormula for dynamic array formulas, to set meta data for the cell.
- Deleting all pivot tables in a workbook caused the workbook to become corrupt as the pivotCache element was not deleted.
- MinIfs and MaxIfs returned an incorrect value if the criteria's was an error like #NUM or #REF.
- EPPlus failed to load a workbook containing a worksheet with a space as name.
- Loading a workbook with a part exceeding 2GB failed to load.
- EPPlus failed to load a workbook when the worksheet xml had a xml prefix for the default namespace and had conditional formatting's or data validations.
- Loading pivot tables with date grouping sometimes did not convert the grouped values to dates.
- Fixed issue were EPPlus only allowed 253 characters in Data Validation List, We now allow 255 characters.
Version 7.2.2
Released: 2024-07-24, Github release
Fixed issues and minor features
- Worksheet references in formulas proceeded by `-` now work as expected.
- Theme templates now appropriately apply theme colors to charts.
- LoadFromCollections with CamelCased attributes did not apply HeaderParsingTypes.CamelCaseToSpace in some cases.
- Refreshing a pivot table could sometimes incorrectly clear the selected item in a page field.
- The pivot table cache HashSet did not match numeric values, if they were of different data types (for example int and double).
- The 'ExcelRangeBase.Text' property did not handle the General format if additional formatting was added.
- Pivot tables fail to save when having dates with milliseconds and date grouping on a field.
- Calculated fields in Pivot tables did not always calculate depending fields, causing the calculation to throw an unhandled exception.
- Having empty rPr-elements (rich text properties) in a shared string item, caused the reading of shared string to be ignored and get the shared string indexes to get out of sync.
- Selected items in pivot table slicers was not always retained, it the field has a page field with a single selected item.
- Add new functions to calculate multiple pivot tables. Using these methods will make sure any shared pivot cache is only refreshed once.
- ExcelWorkbook.CalculateAllPivotTables(refresh)
- ExcelPivotTableCollection.Calculate(refresh)
- New functions supported in formula calculations.
- LINEST
- LOGEST
- TREND
Version 7.2.1
Released: 2024-07-02, Github release
Fixed issues and minor features
- Fixed several issues with the ExcelRangeBase.Text function.
- Added new static property `ExcelPackageSettings.CultureSpecificBuildInNumberFormats` to specify culture specific number formats for individual cultures.
- Added new call-back function `ExcelWorkbook.NumberFormatToTextHandler` to override the default Text value.
- Pivot Table Calculation throwed an exception if a field was sorted and contained empty cells.
- Using implicit intersection (@) between two ranges did not work correctly in the formula parser.
- Added support for DateOnly and TimeOnly (supported from .NET 6 and later).
- Fixed some conditional formatting's not automatically extending when a row was inserted underneath them.
- Fixed issue where conditional formatting on pivot tables would cause corrupt worksheet when read and saved in some cases.
- Changed thread locking object in the cell store.
- New functions supported in formula calculations.
- YIELDDISC
- LOGINV
- CHITEST
- TDIST
- GAMMAINV
- GAMMA.INV
Version 7.2.0
Released: 2024-06-18, Github release
Features
- Added support for calculating pivot tables See our wiki page
- Supports calculation of data fields on column and row fields.
- Page field filtering
- Filters
- Slicers
- Show data as on data fields
- Calculated fields.
- Access calculated pivot table data via the ExcelPivotTable.CalculatedData property of the ExcelPivotTable.GetPivotData function
- GetPivotData function.
- Added support for copying drawings.
- Many types of drawings:
- Shapes
- Charts
- Pictures
- Controls
- Slicers
- Group Shapes
- Copy individual drawings.
- Copying a range will include drawings.
- Set flag to ignore drawings.
- Added support for importing and exporting fixed width text files.
- Transpose.
- Transpose ranges in import and export functions.
- Transpose i range Copy.
- New functions supported in formula calculations.
- GETPIVOTDATA
- MMULT
- MINVERSE
- MDETERM
- MUNIT
- TEXTSPLIT
- TEXTAFTER
- TEXTBEFORE
- LET
- Added Full-fledged support for icon sets and databar conditional formattings in HTML-exporter, New features include:
- Exporting full visuals of positive and negative databars with borders and axis colors, position and bar direction
- Custom-made embedded .svgs similar to each icon excel supports.
- Custom icon sets displaying appropriately and in order.
- Icons moving with text when aligned top, middle or bottom as in excel.
- Theme colors for color scales now works correctly in the HTML exporter.
Minor Features and fixed issues
- Cell text/content now default to vertical-align bottom as data in excel cells are bottom-aligned by default.
- Added new properties `FirstValueCell`, `LastValueCell` and `DimensionByValue` to ExcelWorksheet to manage cell value boundries for a worksheet.
- Added ManualLayout property for data labels on charts. Labels can now be positioned, and their textbox resized directly. It is accessed via e.g `Chart.Series[0].DataLabel.DataLabels[0].Layout.ManualLayout`
- Conditional formatting color scales now support theme color correctly.
- Multiple data labels can now be added to the same series.
- Formula calculation sometimes incorrectly returns #VALUE if `IsWorksheets1Based` is true.
- Line breaks were not handled correctly on saving the workbook if multiple CR where used in combination with CRLF or LF.
Version 7.1.3
Released: 2024-05-28, Github release
Fixed issues
- Dxf styles on tables got corrupt if a style contained an alignment and border element.
- When calculating formulas, you could get a CirculareReferenceException, if a formula referenced a non-existing worksheet.
- Conditional formatting’s with the pivot flag set was incorrectly handled if they had no worksheet address set.
- Clearing data validations on cells, could cause an exception when trying to add new data validations to these cells.
- Conditional formatting icon sets now handles all operators and types appropriately ...
- ExcelRange.Text returned an invalid formatting on formats with "?" in some cases.
- Name indexer on group drawings did not work.
- Data validation lists did not handle the `showDropDown` attribute.
- Loading a workbook with rich text elements with no style element could hang.
- The rich text `Text` property was not decode for restricted characters.
Version 7.1.2
Released: 2024-04-23, Github release
Fixed issues
- Defined Names with `"` symbols no longer get extraneous `"` added when saving in EPPlus.
- Reading RichText data on in-line strings now works as expected.
- Negations of Defined Names and Ranges in shared formulas sometimes received the wrong sign in the calculation as the negation flag was not cleared.
- 'ExcelRangeBase.ToCollection()' auto-mapping sometimes threw an exception as the wrong property type was used.
- Using 'ExcelRangeBse.LoadFromCollection()' with Nullable property members in a collection now returns columns as expected.
Version 7.1.1
Released: 2024-04-08, Github release
- Added properties Rotation, HorizontalFilp and VerticalFlip to ExcelShapeBase and ExcelPicture
- Fixed an issue where RichText wasn't set properly on a multicell range.
- Escape character before an apostrophe in date formats are no longer removed by EPPlus
- The GenericImageReader failed to read some jpeg/exif images.
- Setting the TextBody.Rotation on Chart DataLabel's caused the workbook to become corrupt in some cases.
- Added SetColor() method to ExcelDxfColor with int parameters as in ExcelColor
- Fixed issue applying PatternFill without applying BackgroundFill now works as expected
Version 7.1.0
Released: 2024-03-28, Github release
Features
- Improved HTMLExport
- The HTML exporter can now export all conditional formatting's except icon sets and data bars and their priority order.
- Improved performance on range rich text.
- ExcelRangeBase.LoadFromCollection improvment:
- Number format for columns added via LoadFromCollection can be set in runtime via the IExcelNumberFormatProvider interface.
Fixed issues
- Inserting rows would cause an exception to occur in formulas in rare cases.
- Special signs such as `'` when last in a formula would throw an exception in rare cases.
- Reading Conditional Formatting's with property PivotTable equals to true failed to read in property.
- Tokenize an intersect operator with the _keepWhitespaces set, caused both a white-space token and an intersect operator to be added.
- Added Exception when over maximum data validations that excel allows.
- Fixed sort order in LoadFromCollection. Instances of MemberInfo supplied to the function will always override sort order set via attributes.
- The DeleteRow method could cause formulas that referenced the delete range from another worksheet to become corrupt in some cases.
- The DeleteRow method did not update the end cell in the "ref" address for share/array formulas in some cases.
- Fixed handling of double quotes in conditional formatting number formats.
- Having an escaped character (as for example # or [) in a table address corrupted the formula.
- Having a conditional formatting cfRule without an address caused the reading of the workbook to hang.
- Extended charts failed to load if the xml namespace prefix was not standard.
Version 6.2.19
Released: 2024-03-27, Github release
- Operators did not compare numeric values and strings correctly
- The NumberValue function returned #VALUE instead of 0 when called with an empty string.
Version 7.0.10
Released: 2024-03-11, Github release
Fixed issues
- Having a workbook with group drawings in group drawings caused EPPlus to fail on load.
- Having #REF with a sheet reference when inserting a Row/Column caused the formula to become corrupt.
- Files from 7.0.6 and prior with Data Validations would sometimes fail to be read.
- Data Validations with AlternateContent nodes are now read if the Fallback node contains formulas.
- Some cultures would sometimes get double negative signs in the .Text property of cells.
- Invalid characters in the name parameter were not validated for the AddValue and AddFormula methods of ExcelNamedRangeCollection.
- Defined names with string values was not xml encoded on saving the package.
- Setting style's (like Font for a cell) on the row level did not get the cell style from the column level causing cells intersecting to loose that style.
- ExcelRangeBase.SaveToText and ExcelRangeBase.SaveToTextAsync with a FileInfo did not close the file.
- Intersect operator was replaced with 'isc' when copying cells
- EPPlus removed all styling when setting a Table's CalculatedFormula to an empty string
- ActiveTab was not re-calculated when moving worksheet
Version 6.2.18
Released: 2024-03-11, Github release
- Setting style's (like Font for a cell) on the row level did not get the cell style from the column level, causing cells intersecting to loose that style.
- ExcelRangeBase.SaveToText and ExcelRangeBase.SaveToTextAsync with a FileInfo did not close the file.
Version 7.0.9
Released: 2024-02-15, Github release
Fixed issues
- The formula tokenizer did not handle minus correctly before table addresses.
- Inserting rows/columns could cause drawings to get a incorrect width/height.
- Saving multiple times caused hyperlinks to multiply.
- Saving multiple times caused dxf border styles for tables to become corrupt if set.
- EPPlus can now handle up to 66 indexed colors
- VALUE function did not support multicell input
- Deleting the first worksheet in a workbook where IsWorksheets1Based is true no longer throws out of range exception.
- Ensured workbooks do not become corrupted after SaveAs if they have certain empty xml nodes.
- Inserting cells, rows or columns next to Conditional Formatting ranges now automatically extends those ranges to the new cells as in Excel.
- Cell with bool value no longer returns "0" and "1" on text property now returns "TRUE" or "FALSE" instead as in Excel.
- Conditional formatting’s with space separated addresses now saves appropriately.
- Text input with a "-" such as " -ACat" in some functions such as e.g. SumIf resulted in faulty error calculations.
- Adding a new table column and change the Name property caused the total row to incorrectly return #DIV/0.
Version 6.2.17
Released: 2024-02-15, Github release
Fixed issues
- EPPlus can now handle up to 66 indexed colors.
- Formatted text for cells with Boolean values now returns TRUE or FALSE.
- Deleting the first worksheet in a workbook that has IsWorksheets1Based set to true no longer throws out of range exception
- Ensured workbooks do not become corrupted after SaveAs if they have certain empty xml nodes
Version 7.0.8
Released: 2024-01-24, Github release
- Validation of VBA module names failed when containing a space
- Decryption of workbooks where the hash algorithm SHA1 was used sometimes failed.
Version 7.0.7
Released: 2024-01-19, Github release
- Implicit intersection in formulas with full row or full column addresses incorrectly calculated to #VALUE.
- Inserting in a range with a formula that has a table address with two parts, i.e. Table1[[#This Row],[column1], caused the formula to become corrupt.
- Conditional formatting’s with #REF addresses caused an Exception.
- HeaderFooter - Fixed issue introduced 7.0.6 where RightAlignedText was set as CenterAlignedText.
- Formula parser handled negation incorrectly in some cases before addresses with worksheet name specified.
- Conditional formatting data bars can now take formula addresses and formulas for high and low input.
- Calculating the first cell of a shared formula was incorrectly cached the value causing a second calculation to to use the previously cached value.
- Disposed internal Memory Stream’s in package parts were not disposed correctly.
- Formula Tokenizer could incorrectly identify a token as exponential causing an exception in the formula calculation.
Version 7.0.6
Released: 2024-01-10, Github release
Minor Features
- Added new property TextSettings to set text fills, outlines and effects on chart elements to ExcelChartTitle, ExcelChartLegend, ExcelChartAxis and ExcelChartDatalabel.
- Upgraded RecyclableMemoryStream to 3.0.0.
Fixed issues
- Improved performance when opening files with many defined names in excel.
- ToDataTable didn't handle RichText correctly when exporting values
- Calculation threw a NullReferenceException on calculating a copied worksheet with shared formulas in some cases.
- Calculation of XLOOKUP failed if it was set as a shared formula that did not return a dynamic result.
- Fixed an issue where the formula tokenizer in the formula calculation handled whitespaces in the wrong order compared to negators.
- Added a check for maximum header and footer text length.
- RichData parts did not add the content types to the [Content_Types].xml.
Version 6.2.16
Released: 2024-01-10, Github release
Fixed issues
- ToDataTable didn't handle RichText correctly when exporting values
- Pivot table fields with a specified subtotal function sometimes caused the workbook to become corrupt.
- Improved performance when opening files with many defined names in Excel.
- Fixes an issue where reading a workbook fails due to invalid xml.
Version 7.0.5
Released: 2023-12-22, Github release
Fixed issues
- Calculating formulas with expressions that had double cell negations, returned an incorrect result.
- Calculating a formula that had a negation of an empty cell returned a #VALUE error.
- Pivot table fields with a specified subtotal function sometimes caused the workbook to become corrupt.
- Deleting a worksheet with hyperlinks that refers to an internal address caused an exception.
Version 7.0.4
Released: 2023-12-15, Github release
Minor Features
- Added follow dependency-chain option which allows calculating the given cells without calculating dependent cells
Fixed issues
- Deleting pivot tables sometimes did not clear their pivot caches.
- The formula tokenizer did not handle single/double quotes and encoding correctly in table addresses.
- The JSON export did not encode column header cells and comment texts.
- Worksheet Copy did not copy images with hyperlinks correctly
- ExcelRangeBase.FormulaR1C1 translation did return a correct value when having a minus operator in some cases.
Version 6.2.15
Released: 2023-12-15, Github release
Fixed issues
- The JSON export did not encode column header cells and comment texts.
- Worksheet Copy did not copy images with hyperlinks correctly.
Version 7.0.3
Released: 2023-12-05, Github release
Minor Features
- Added Alignment and Protection properties to ExcelDxfStyle - Affects Table and Pivot Table Stylings.
- Added Target framework .NET 8.
Fixed issues
- Improved handling of negation of ranges in the formula calculation.
- Added AlwaysAllowNull property to ToDataTableOptions parameter of the ExcelRangeBase.ToDataTable function.
- ExcelValueFilterColumn.Filters. Blank property now hides rows even if it contains no other filters.
- Resolved issue where ExcelValueFilterCollection.Add("") or adding null on ExcelFilterValueItem generated corrupt worksheet. The Blank property is now set to true instead.
- Hyperlinks were not correctly encoded if Unicode characters was used.
- External references in the lookup range did not work in the VLOOKUP & HLOOKUP functions.
- Copying worksheets with pivot tables caused a corrupt workbook in some cases.
- Insert/Deleting in ranges sometimes affected addresses referencing other worksheets.
- The ExcelRangeBase.Text property sometimes returned a formatted value with both - and () for negative values on some cultures.
Version 6.2.14
Released: 2023-12-05, Github release
Fixed issues
- Added support for negation of single cell named values in the formula calculation.
- Added AlwaysAllowNull property to ToDataTableOptions parameter of the ExcelRangeBase.ToDataTable function.
- ExcelValueFilterColumn.Filters.Blank property now hides rows even if it contains no other filters
- Resolved issue where ExcelValueFilterCollection.Add("") or adding null on ExcelFilterValueItem generated corrupt worksheet. The Blank property is now set to true instead.
- Hyperlinks were not correctly encoded if Unicode characters was used.
- The ExcelRangeBase.Text property sometimes returned a formatted value with both - and () for negative values on some cultures.
Version 7.0.2
Released: 2023-11-23, Github release
Fixed issues
- External references did not work correctly with the VLOOKUP function.
- Table addresses sometimes returned an incorrect address in the formula calculation.
- Empty arguments was not handled correctly in the Unique, Sort and SortBy functions.
- Corrected behaviour for comparisons between null values and empty strings in range operators.
- Fixed a bug where adding the same image to a worksheet twice with the same path resulted in a null reference.
- Resolved workbooks becoming corrupt when setting ShowTotalRow on tables to true, if data existed on the row below the table. The row will now be overwritten by the total row.
- Tab-characters in Richtext's are now decoded correctly.
- Last character of RichText.Text were truncated under Linux.
- LoadFromCollection: support for SortOrder attribute on nested classes.
- LoadFromCollection: support for Dictionary properties.
Version 6.2.13
Released: 2023-11-23, Github release
Fixed issues
- Fixed a bug where adding the same image to a worksheet twice with the same path resulted in a null reference.
- Resolved workbooks becoming corrupt when setting ShowTotalRow on tables to true, if data existed on the row below the table. The row will now be overwritten by the total row.
- Tab-characters in Richtext's are now decoded correctly.
- Last character of RichText.Text were truncated under Linux.
- LoadFromCollection: support for SortOrder attribute on nested classes.
Version 7.0.1
Released: 2023-11-09, Github release
Fixed issues
- Copying a worksheet with the ExcelWorksheet.CodeModuleName set and not having a VBA project in the workbook caused the name to be duplicated.
- Delete and create an Auto filter caused the workbook to become corrupt.
- Worksheet Copy did not copy images in the header/footer when the destination worksheet was in another workbook.
- Worksheet Copy did not copy images inside group shapes correctly when the destination worksheet was in another workbook.
- Match function did not work with single cells in lookup array argument.
- Copying a pivot table sometimes caused the workbook to become corrupt.
- Disposed some internal MemoryStream's were not disposed correctly.
Version 6.2.12
Released: 2023-11-09, Github release
Fixed issues
- Copying a worksheet with the ExcelWorksheet.CodeModuleName set and not having a VBA project in the workbook caused the name to be duplicated.
- Worksheet Copy did not copy images in the header/footer when the destination worksheet was in another workbook.
- Worksheet Copy did not copy images inside group shapes correctly when the destination worksheet was in another workbook.
- Copying a pivot table sometimes caused the workbook to become corrupt.
- Disposed some internal MemoryStream's were not disposed correctly.
Version 7.0.0
Released: 2023-10-26, Github release
- Calculation engine update to support array formulas.
- Support for calculating legacy / dynamic array formulas.
- Support for intersect operator.
- Support for implicit intersection.
- Support for array parameters in functions.
- Better support for using the colon operator with functions.
- Better handling of circular references
- 90 new functions
- Faster optimized calculation engine with configurable expression caching.
- Breaking changes: Updated calculation engine, See Breaking Changes in EPPlus 7 for more information.
- Conditional Formatting improvements
- Improved performance, xml is now read and written on load and save.
- Cross worksheet support formula support.
- Extended styling options for color scales, data bars and icon sets.
- New functions for Taking and Skipping rows/columns on a range
- Bug fixes
Version 6.2.11
Released: 2023-10-26, Github release
Fixed issue
- ROUNDUP function sometimes rounded incorrectly.
- Some internal MemoryStream's were not disposed correctly.
- Setting the Pivot table SourceRange to the same range as an existing Pivot Cache sometimes caused the workbook to be corrupt.
- LoadFromCollection MemberInfo[] now works correctly with attributes, but are ignored on nested classes.
- The SUBSTITUTE function did incorrectly handled Excel errors as strings.
- ExcelRangeBase.LoadFromDataTable method did now checks the data table name to be valid, or otherwise sets the table name to TableX.
- ExcelAddressBase.IsValidAddress did not handle table addresses.
- ExcelHyperlink did not handle sub addresses, i.e., http://xxx.yy/zzz/#aa,bb. The ExcelHyperLink.ReferenceAddress will now contain the sub address path.
- Setting the source range of a pivot table that shared the pivot cache with another pivot table caused a corrupt workbook.
Version 6.2.10
Released: 2023-10-02, Github release
Minor Feature
- Hyperlinks loaded via the LoadFromCollection method will now be styled with the built-in Hyperlink Style. This style will also be added to the NamedStyles collection of the workbook if it does not exist.
Fixed issue
- LoadFromCollection filter nested class properties-based on the supplied list of MemberInfo
- Fixed behaviour for SUBTOTAL with filters in calculations
- Performance improvement and handling of DateTime null values in ToDataTable()
- Auto filter was not always removed when when ExcelWorksheet.AutoFilterAddress was set to null.
- Some workbooks could not be loaded due to the worksheet's rolling buffer being too small in some scenarios.
- Fixed a performance issue when adding comments and controls.
Version 6.2.9
Released: 2023-09-08, Github release
Fixed issue
- Fixed an issue where empty DataValidationnodes caused a corrupt workbook.
- Ungrouping drawings put the drawings in the wrong position and sometimes caused the workbook to become corrupt.
- VLOOKUP / HLOOKUP and MATCH did not work with external ranges.
- The INDEX function handled rowno as colno when the argument was only one row.
- Deleting a worksheet that was selected sometimes caused a hidden worksheet to become visible.
- The CEILING and FLOOR functions did not handle null values correctly in the second parameter.
- Fix for loading classes with only EPPlusNestedTableColumn attributes in ExcelRangeBase.LoadFromCollection.
- Fixed an issue when using concatenation operator with Excel errors.
Version 6.2.8
Released: 2023-08-15, Github release
Fixed issues
- Boolean style xml elements (like b, i or strike), with attribute 'val' set to 'false' or 'true' did not work.
- The ExcelRangeBase.Insert and ExcelRangeBase.Delete methods failed if a defined name referenced another defined name.
- The AND and OR functions did'nt handle multi-cell ranges as parameters.
Version 6.2.7
Released: 2023-07-19, Github release
Fixed issues
- Copying a worksheet with more than two tables to a new workbook sometimes throws an exception due to different table ids.
- Matching an existing pivot table cache against source data was case-sensitive.
- Added support for hidden columns in EPPlusTableColumn attribute.
- The Calculate method threw an exception if a defined name contained an error value.
- Fixed an issue when updating formulas in data validations and conditional formatting’s when inserting/delete rows or columns.
- Conditional formatting text types would fail to function correctly after deleting a column.
- Copying a worksheet with a defined name with a formula pointing to another worksheet caused a NullReferenceException.
Version 6.2.6
Released: 2023-06-15, Github release
Fixed issues
- Updated System.Security.Cryptography.Pkcs for security vulnerability in .NET 6 and 7. See https://github.com/dotnet/runtime/issues/87498
- An ArgumentOutOfRangeException was sometimes thrown when loading a workbook.
Version 6.2.5
Released: 2023-06-14, Github release
Fixed issues
- EPPlus now allows saving of drawing groups containing drawings with same name.
- Copying a formula containing a table reference caused an invalid formula.
- Deleting and inserting into worksheets with data validations sometimes blocked adding new data validations on valid ranges.
- Data validations now allows empty formulas.
- REPLACE function can now handle a num_char argument that exceeds the length of the text.
- EPPlus threw an incorrect CircularReferenceException when referencing the same cell on a different worksheet in some cases.
- When copying a worksheet, Excel displayed the save dialog on close, due to the worksheets having the same uid.
Version 6.2.4
Released: 2023-05-15, Github release
Minor Features
- Added IRangeDataValidation. ClearDataValidation to clear data validations from a range.
Fixed issues
- Having a table data source set to a defined name, and then insert rows into the range, caused the table source to be inverted into a range without inserting the rows.
- An error occurred when setting the Shape.Text in some cases."Name cannot begin with the ' ' character, hexadecimal value 0x20, due to invalid xml.
- Scientific notation numbers were not being recognized in the calculation when there were leading or trailing whitespaces.
- Formulas update for Data Validation and Conditional Formatting sometimes updated the addresses wrong when inserting and deleting.
- The worksheet xml got corrupt in rare cases, when having extLst items.
- A pivot table's SourceRange property was not read on load.
Version 6.2.3
Released: 2023-05-02, Github release
Fixed issues
- Setting sparklineGroups.MaxAxisType to eSparklineAxisMinMax.Group did not work.
- Extracting the worksheet xml sometimes fails when having an extLst element.
- Having a DataTable formula sometimes caused an InvalidDataException due to missing shared formula id.
- Setting ExcelRange.IsRichText caused an exception in some cases.
- Inserting columns in tables with calculated columns sometimes caused a corrupt workbook upon save.
- Having a workbook with picture-drawings with duplicate names caused Drawing.Delete(string) to fail.
- Fixed an encoding issue with data validation attributes having values containing double quotes (").
- Data validation's sometimes corrupted the workbook on save when referencing another worksheet in the formulas.
Version 6.2.2
Released: 2023-04-17, Github release
Fixed issues
- Insert row did not update formula cells correctly in some cases.
- Copying a worksheet to another workbook sometimes doesn't copy the correct style.
- Creating a sparkline group with an ExcelAddress caused a corrupt workbook.
- DataValidation objects with a ImeMode property set, threw an exception on save.
- DataValidation objects sometimes caused a corrupt workbook when used with slicers and sparklines (extLst).
Version 6.2.1
Released: 2023-04-06, Github release
Fixed issues
- Having data validations referring to other worksheets could break the xml causing the workbook to become corrupt.
- Deleting a worksheet and having the last worksheet selected caused the workbook to become corrupt.
- Fixed a rounding bug in ExcelTime, affecting the Time formula and the data validation time rule.
- List data validations could not have empty value as a list item
Version 6.2.0
Released: 2023-03-24, Github release
Features
- Improved performance and better support for cross-worksheet references in data validations.
Fixed issues
- ExcelRange.RichText.Clear method is cleared all cell style properties.
- Match functions compares null values as exact match.
- ExcelChartSerie.Border.Fill.Color throws an exception on set, if a chart style has applied a border style.
- Cloning rows when copying a worksheet now clones the RowInternal class.
- Switched to internal image handler instead of System.Drawing.Common for .NET framework.
- Deleting rows deleted conditional formatting and data validatio9ns for full rows/columns
- The stream was closed but not disposed when calling ExcelPackage.GetAsByteArray().
- The formula parser did not handle exponential numbers in calculation correctly.
Version 6.1.3
Released: 2023-02-24, Github release
Fixed issues
- When clearing a formula and then insert a row into the worksheet an exception was thrown.
- Having a pivot field with string grouping caused an Exception on loading a workbook.
- ExcelExternalWorkbook.UpdateCache() throw an NullReferenceException if a worksheet name did not exist.
- Applying a style for a worksheet that has a style set on the column level, did not retain the style for the last columns in some cases.
- Could not delete the last row (1048576) in a worksheet.
- Group drawings did not update children when rows were resized. Thanks to gnoul-mah for pr.
Version 6.1.2
Released: 2023-01-19, Github release
Fixed issues
- Fixed an issue with the Roman function. Thanks to ihorbach.
- Fixed a performance issue with calculated table columns.
- Having hyperlinks longer than 2079 characters resulted in a corrupt workbook. EPPlus will now crop hyperlinks over 2079 characters
- Date functions Month, Day, Hour,Second and DateDiff used InvariantCulture instead of CurrentCulture.
- Checkboxes in the style dialog for named styles was not retained after a workbook was saved with EPPlus.Thanks to ihorbach.
- Iterating over a range with multiple comma-separated ranges iterated the first range twice.
- Creating Array Formulas via the ExcelRange.CreateArrayFormula did not create the formula correctly.
- Custom row heights was not copied correctly.
Version 6.1.1
Released: 2022-11-21, Github release
Minor features
- Add support for linking a cell to a chart title text
Fixed issues
- EPPlus did not preserve "Host Extender Info" in the vba project.
- VBA signing failed on new workbooks.
- ExcelTable sorting corrupted relative formulas.
- Images in a group did not change position when row height changed.
- RichText.Color and ExcelColor.LookupColor did not return the correct color if the color was Auto or Theme and did not always adjust for Tint.
Other changes
- Added Target framework .NET 7.
Version 6.1.0
Released: 2022-11-03, Github release
Features
- Support for new types of VBA signing. See this link
- Agile VBA Signing
- V3 VBA Signing
- Change hash algorithm on a VBA signature. Supports MD5, SHA1, SHA256, SHA384 and SHA512.
- ExcelRange.ToCollection method - to map ranges and tables to an collection of T. See this link
- New methods to group, ungroup, collapse, and expand rows and columns - See this link
- Group method method
- Ungroup method method
- CollapseChildren method
- ExpandChildren method
- SetVisibleOutlineLevel method
- New overloads of Drawings.AddPicture that reads the signature of the image from stream to identify the type of image.
- AddPicture(string, Stream)
- AddPicture(string, Stream, Uri)
- AddPicture(string, Stream, ePictureType) has been deprecated
- AddPicture(string, Stream, ePictureType, Uri) has been deprecated
- Preserve the 'Table' formula properties (Created via the What-If Analysis-Data Table).
Fixed issues
- Invalid formatted hyperlinks were not loaded and saved correctly.
- Rows to repeat is not copied when adding a worksheet with another worksheet as template.
- Rows with the default height got an incorrect height when copied to a new worksheet if the Normal style had a font other than the default.
- The GenericImageHandler failed to load images in .NET in Unity as System.Drawing was not available.
- EpplusTableColumnAttribute NumberFormat is ignored when Header contains space.
- Workbooks with color styles not having any attributes, failed to load.
Other changes
- Target framework .NET 5.0 has been removed as it is out of support by Microsoft.
Version 6.0.8
Released: 2022-09-21, Github release
Fixed issues
- Fixed issue with the DataValidations.Clear() method, where DataValidations were only removed from the collection classes in EPPlus but not removed from the underlying xml.
- Fixed issue where inserting/deleting columns in the source of a pivot table sometimes caused the PivotTable to become corrupt.
- Fixed issue with Png files without the pHYs chunk that failed to Add to the Drawings collection.
- Adjusted the EPPlus source code to avoid validation errors from the PEVerify tool
- Added error handling to the initialization of RecyclableMemoryStreamManager which previously caused an uncontrolled Exception on the Unity platform
- When saving a workbook with external references to another workbook, EPPlus now updates the reference with a relative path instead of an absolute path.
Version 5.8.14
Released: 2022-09-21, Github release
Fixed issues
- Fixed issue with the DataValidations.Clear() method, where DataValidations were only removed from the collection classes in EPPlus but not removed from the underlying xml.
- Fixed issue where inserting/deleting columns in the source of a pivot table sometimes caused the PivotTable to become corrupt.
- Adjusted the EPPlus source code to avoid validation errors from the PEVerify tool
- When saving a workbook with external references to another workbook, EPPlus now updates the reference with a relative path instead of an absolute path.
Version 6.0.7
Released: 2022-08-30, Github release
Minor features
- New static method Configure on ExcelPackage to configure location of config files and error handling. See This link...
Fixed issues
- Copying comments sometimes did not change the name in the vml drawing causing OOXML validation to complain on duplicate drawings.
- The count attribute on 'xsf' node in Styles.xml was not correctly set.
- Setting drawing coordinates did not update the xml on save if ExcelPackage.DoAdjustDrawings was set to false.
- IF function now handle errors correcly.
- Added method ExcelRangeBase.SetErrorValue to set a cell to an error value. Added static methods ExcelErrorValue.Create and ExcelErrorValue.Parse.
- Referencing a worksheet to a cell address after a colon (for example 'sheet1'!a1:'sheet1'!A3) did not work correctly in the formula calculation.
- Added RichText and TextBody properties to ExcelControlWithText.
- Removing / Clearing or inserting into a table with a Calculated Column Formula sometimes caused a corrupt workbook or an Exception.
- Removing VBA signtures did not remove the newer types of signatures, Agile and V3.
- ExcelRangeBase.ToDataTable could not export a range with no column names and a single row.
- LoadFromCollection with attributes now uses HeaderPrefix for in column names for nested complex type properties
Version 5.8.13
Released: 2022-08-30, Github release
Fixed issues
- Copying comments sometimes did not change the name in the vml drawing causing OOXML validation to complain on duplicate drawings.
- The count attribute on 'xsf' node in Styles.xml was not correctly set.
- Setting drawing coordinates did not update the xml on save if ExcelPackage.DoAdjustDrawings was set to false.
- IF function now handle errors correcly.
- Referencing a worksheet to a cell address after a colon (for example 'sheet1'!a1:'sheet1'!A3) did not work correctly in the formula calculation.
- Removing / Clearing or inserting into a table with a Calculated Column Formula sometimes caused a corrupt workbook or an Exception.
- ExcelRangeBase.ToDataTable could not export a range with no column names and a single row.
- Removing VBA signtures did not remove the newer types of signatures, Agile and V3.
- LoadFromCollection with attributes now uses HeaderPrefix for in column names for nested complex type properties
Version 6.0.6
Released: 2022-07-08, Github release
- Fixed an error in Positioning and sizing of form controls introduced in 5.8.12.
- Pivot table styles in template workbooks sometimes corrupts the new workbook.
- Spaces were not preserved in rich text in drawing objects.
- Defined names referencing external reference sometimes loaded incorrectly.
- Drawing.ChangeCellAnchor causes a corrupt package in some cases
Version 5.8.12
Released: 2022-07-08, Github release
- Fixed an error in Positioning and sizing of form controls introduced in 5.8.12.
- Pivot table styles in template workbooks sometimes corrupts the new workbook.
- Spaces were not preserved in rich text in drawing objects.
- Defined names referencing external reference sometimes loaded incorrectly.
Version 6.0.5
Released: 2022-06-10, Github release
- VBA code modules with LF only as EOL, causes code module to load incorrectly.
- INDIRECT function did not always set the correct data type when returning a single cell.
- Clearing ranges with threaded comments caused an exception.
- Copying drawings with hyperlinks failed.
- Improves insert/delete performance when working with drawings.
- Insert row caused images to change size in some cases when having two anchored drawings.
- Improved handling of percentage values in strings in formula calculation.
- Custom index colors are now loaded from the styles.xml, thanks to Raboud.
- Fix to get the app0 header length correct for internal jpg reader for the internal image reader.
- Images in the header row was not correctly rendered in the HTML export for ranges.
- Upgraded .NET 4.5.2 to 4.6.2, as 4.5.2 has reached End of Support
Version 5.8.11
Released: 2022-06-10, Github release
- VBA code modules with LF only as EOL, causes code module to load incorrectly.
- INDIRECT function did not always set the correct data type when returning a single cell.
- Clearing ranges with threaded comments caused an exception.
- Copying drawings with hyperlinks failed.
- Improves insert/delete performance when working with drawings.
- Insert row caused images to change size in some cases when having two anchored drawings.
- Improved handling of percentage values in strings in formula calculation.
Version 6.0.4
Released: 2022-05-12, Github release
Fixed issues
- The HTML export of ranges sometimes rendered styles in merged cells incorrectly
- Improved handling of circular references for SUMIF and COUNTIF
- Memory optimization reading directly from the zip stream, when reading the worksheet xml, allowing unexteracted parts larger than 2GB.
- Hyperlinks in cells added with 'Display' is null will now use the formatted cell value as text for in workbook links.
- Remove invalid attribute TopLeftCell on the Selection element for splited/freezed worksheets.
- Fixed an issue in the unziping of packages using ZIP64 file headers and data descriptors
- Fix for removing rows from ExcelTable with options ShowHeader set to false.
- Improved handling of workbook- and worksheet-names when loaded from file with a relative address
- Box and Whisker chart series failed when copied to a new worksheet.
Version 5.8.10
Released: 2022-05-12, Github release
Fixed issues
- Hyperlinks in cells added with 'Display' is null, will now use the formatted cell value as text for in workbook links.
- Remove invalid attribute TopLeftCell on the Selection element for splited/freezed worksheets.
- Fixed an issue in the unziping of packages using ZIP64 file headers and data descriptors
- Fix for removing rows from ExcelTable with options ShowHeader set to false.
- Improved handling of workbook- and worksheet-names when loaded from file with a relative address
- Box and Whisker chart series failed when copied to a new worksheet.
Version 6.0.3
Released: 2022-04-12, Github release
Features
- Html Export
- Json Export
- Removed direct reference to System.Drawing.Common.See Breaking Changes
- 32 new functions
- BETADIST
- BETA.DIST
- BETAINV
- BETA.INV
- CHIDIST
- CHISQ.DIST.RT
- CHIINV
- CHISQ.INV
- CHISQ.INV.RT
- CORREL
- EXPONDIST
- EXPON.DIST
- FISHER
- FISHERINV
- FORECAST
- FORECAST.LINEAR
- GAUSS
- GEOMEAN
- HARMEAN
- INTERCEPT
- KURT
- PEARSON
- PHI
- RSQ
- SKEW
- SKEW.P
- STANDARDIZE
- ACCRINT
- ACCRINTM
- TBILLEQ
- TBILLPRICE
- TBILLYIELD
Minor Features
- Breaking Change: Static class 'FontSize' has splitted width and heights into two dictionaries. FontSizes are lazy-loaded when needed.
- New ExcelRangeBase.GetCellValue
method - New overload for ExcelRangeBase.LoadFromDictionaries method with IEnumerable
. - Added Datatypes and Culture to LoadFromDictionariesParams. This is the settings for the ExcelRangeBase.ExcelRangeBase.LoadFromDictionaries.
- Added ExcelRichTextCollection.HtmlText and ExcelRichText.HtmlText property.
- Added new parameter to ExcelAutoFilter.ApplyFilter to calculate formulas for the filter range.
Version 5.8.9
Released: 2022-04-11, Github release
- Fixed issue with start_num parameter for functions FIND and SEARCH
- Pivot table slicers in a template sometimes caused a corrupt workbook on save
- Pivot table fields that had subtotals and null values in shared cache items caused the package to fail on load in some cases.
- Having the value set to 0(zero) and the number format to date or time returned the format instead of the formatted value.
- DeleteColumn caused the worksheet to expands to the maximum column properties extended to the last column (XFD).
- The UPPER and LOWER functions did not handle empty cell values correctly.
- Fixed an issue in ExpressionEvaluator when evaluating empty string criterias.
- Fixed issue where members with complex classes and attributes caused LoadFromCollection to crash.
Version 5.8.8
Released: 2022-03-22, Github release
Fixed issues
- Removed unnecessary Nuget references to packages already included in the targeting frameworks.
- Fixed calculation issue when ExcelPackage.Compatibility.IsWorksheets1Based is set to true
Version 5.8.7
Released: 2022-03-01, Github release
- LoadAsync(FileInfo) and LoadAsync(string) did not close the file stream.
- Hyperlinks referencing the same relation fails to load the package.
- Monochromatic chart color schemes gave the wrong tint/shade for multiple series.
- Added missing property 'JustifyLastLine' to ExcelStyle and ExcelXfsXml. Collapsed do not set Hidden on columns when set to true.
- Improved handling of defined names in range addresses in formula calc, for example SUM("MyRange1:MyRange2")
- Escaped double quotes did not work properly for shared formulas in formula calc.
- Table.AddRow did not adjust Conditional Formatting and Data Validation.
- Support for numeric criteria with operators in MINIFS/MAXIFS functions.
- ExcelWorksheet.ClearFormulas method throw a NullReferenceException if ExcelWorksheet.Dimension was null.
Version 5.8.6
Released: 2022-02-03, Github release
- Rounding functions now returns 0 when referencing empty cells
- Copying elements in vml files caused attributes to lose there name space and create a duplicate.
- Removed most fonts from the FontSize class and lazy-load them when needed to avoid memory spikes.
- Pie chart with horizontal serie direction did not use different colors when the VaryColors property was set.
- ExcelWorksheet.Dimension didn't correctly determine sheet dimension if data resides on last excel row.
- Hyperlinks referencing multiple cells was only loaded for the first cells in a range.
- Improve SUBTOTAL handling of cells hidden by filters.
Version 5.8.5
Released: 2022-01-13, Github release
Minor Features.
- LoadFromCollection with attribute - Added EPPlusTableColumnSortOrder which enables column sort order on class level.
Fixed issues
- ExcelDrawings.AddBarChart method for pivot charts had the wrong signature.
- Formula calculation adjusted to Excels behaviour regarding when 0 is returned instead of null. Thanks to Colby Hearn for the PR.
- The ExcelRange.Clear method did not remove threaded comments.
- Setting ExcelRange.Value to null only sets the value of the top-left cell.
- Fixed an issue with pivot cache fields having an empty header in the source when updating the cache.
- Pivot field cache containing float and null values caused a corrupt workbook.
- EPPlus could not open workbooks without a normal style
- SUMIF cannot handle single value, bug fix via Colby Hearn's PR 570. Invalid handling of numeric strings in COUNTIF, COUNTIFS and AVERAGEIF
- MINIFS and MAXIFS now return zero when there are no matches.
- Added support for setting LicenseContext using a process level environment variable.
Version 5.8.4
Released: 2021-12-14, Github release
Features
- 6 new functions:
- NORMINV
- NORM.INV
- NORMSINV
- NORM.S.INV
- NORMDIST
- NORM.DIST
Fixed issues
- Default behaviour of SUM, AVERAGE, AVERAGEA, MEDIAN, LARGE, SMALL and PRODUCT functions did not handle cells with errors correctly
- Fixed load/save of .xlsm files not having an vbaproject.bin in the package.
- EPPlus threw an exception when handling extlist logic for space separated data validations.
- IntParser (formula calc.) could not handle boolean values
- Copy of comments within the same worksheet caused an ArgumentException when loading the workbook again.
- Range.Copy of conditional formattings with multiple addresses did not work.
- Changed Uri reference handling to avoid relative references to the root.
Version 5.8.3
Released: 2021-11-19, Github release
Fixed issues
- Inserting rows into a worksheet sometimes didn't update addresses on workbook defined names.
- Overlapping data validation addresses was validated on save which cause workbooks containing such not to be saved.
- Failed to copy cells when data validations or conditional formatting was set.
- COUNTIFS, AVERAGEIFS and SUMIFS fails with single cell ranges.
- Packages with VBA project with a component reference with encoded characters causes the saved package to become corrupt.
- It was not possible to specify legend entry properties for items from secondary y-axis.
Version 5.8.2
Released: 2021-10-29, Github release
Fixed issues
- Range.Text returned the wrong value for format #.##0"*";(#.##0)"*" on negative values
- LoadFromCollection re-ordered the columns when no order was specified and and the item had more than 16 columns.
- Adding an Unchecked CheckBox Control Created Invalid XLSX File
Version 5.8.1
Released: 2021-10-26, Github release
Fixed issues & minor features
- Support for complex types in LoadFromCollection with attributes.
- Fixed a bug where ExcelFunction.ArgToDecimal rethrow other error types as #VALUE.
- Improved handling of decimals in Concat operations during calculation.
- High-Low lines / Up-Down bars and Droplines were not loaded from an existing package.
- Removed validation for negative values in conditional formatting priority, as negative values should be allowed
Version 5.8.0
Released: 2021-10-21, Github release
Features
- ExcelWorksheetView.SplitPanes method added
- ExcelRangeBase Fill method added
- FillNumber
- FillDateTime
- FillList
- New collection properties for Rows and Columns
- ExcelWorksheet.Rows
- ExcelWorksheet.Columns
- ExcelRangeBase.EntireRow
- ExcelRangeBase.EntireColum
- Support for formatting and deleting individual Chart Legend Entries.
- Range.Copy improvments.
- Handle complex types in LoadFromCollection with attributes
- The ExcelPackage constructor and the Load/Save methods will now take a string path argument as well as a FileInfo.
Fixed issues
- Renaming table to a name which contains the old name doesn't correctly update column references
- Range.Text did not handle empty formats like ;;; correctly.
- Range.Text - Strings within a format with percent, 0"%", incorrectly divides by 100
- VBA module names that started with a underscore (_) caused the ExcelWorksheet.Copy method to fail.
- Using range.RichText.Remove did not reflect the text to Range.Text.
- Adding a column to table with one column did not add the column.
- ExcelRangeBase.SaveToText did not add TextQualifiers around a formatted numeric value containing the column separator.
- Deleting cells sometimes didn't delete comments.
- Improved handling of ranges as return values from functions in the formula calculation.
Version 5.7.5
Released: 2021-09-21, Github release
Fixed issues
- ExcelTable.CalculatedColumnFormula were not updated when inserting/deleting rows and columns.
- Copying a worksheet- or adding a VBA module- with a name starting with a number caused the macro-enabled workbook to be corrupt.
- LoadFromCollection with attributes didn't create a table if TableStyle was none.
- Fixed LEN functions incorrect handling of cached addresses.
- Fixed handling of worksheet names with escaped single quotes in the formula parser.
- ExcelPicture.SetSize sets an incorrect width when having a non-default dpi on an image.
- ExcelColumn.ColumnMax was not correctly set when deleting a column within a column span.
- Updating a pivot cache share among multiple pivot tables did not update/remove fields if the source range fields had been altered.
- Clearing or overwriting a cell in a shared formula range did not work correctly.
- Formulas in conditional formatting and data validations were not updated when inserting and deleting rows and columns.
- When deleting columns, defined names were not always correctly updated.
- ExpressionEvaluator could not handle a leading equal operator in combination with wildcards
Version 5.7.3
Released: 2021-08-10, Github release
Fixed issues & minor features
- Add static methods to ExcelEncryption to encrypt and decrypt raw packages
- ExcelEncryption.EncryptPackage
- ExcelEncryption.DecryptPackage
- Conditional formatting lost some styles and added hair borders to empty border elements.
- COUNTBLANK and other functions using the ExpressionEvaluator don't handle time values correctly.
- COUNTBLANK does not handle cached addresses correctly.
- LoadFromText and SaveToTest did not work correctly with apostrophes.
- Changing the font of the normal style and then create a new named style did not inherit the font correctly.
- Data validations were shifted down instead of right when inserting cells into a range
- Comments and threaded comments were not shifted correctly to the right when inserting cells.
- EPPlus will now throw an exception if merging a range that overlaps a table.
Version 5.7.2
Released: 2021-07-22, Github release
Fixed issues
- Pivot cache fields that contains both int's and float's corrupts the pivot cache.
- Added new methods to themes major- and minor- font collection - SetLatinFont, SetComplexFont, SetEastAsianFont, Remove and RemoveAt.
- Null or non existing external references to images on picture objects causes save to crash.
- VBA projects with the "dir" stream containing the unhandled value 0x4a, caused the workbook to become corrupt.
- Defined names with prefix and external reference throw a NullReference on load.
Version 5.7.1
Released: 2021-07-06, Github release
Fixed issues
- Using a number format with AM/PM resulted in an output of AM or PM only.
- Validation of data validations throw an exception when Formula1 is empty even if AllowBlank was set to true
- Table behavior is incorrect when inserting rows if another table is below.
- Table calculated columns don't update the formula for added rows.
- Added cache for texts/fonts in AutoFitColumns, thanks to Simendsjo
- Setting Range.IsRichText on ranges with more than one cell did not work correctly.
- Loading packages with external references that didn't have a valid Uri failed on load.
- A value of null in a cell returned "0" in the Text property.
Version 5.7.0
Released: 2021-06-22, Github release
Features
- External links
- Adding, removing and break links to external workbooks.
- Updating external workbook value/defined name caches.
- Using external workbook caches in the formula parser.
- Using loaded external packages (workbooks) in the formula parser.
- Enhanced sorting
- Pivot table auto sort - Sort on data fields using pivot areas.
- Sort state in tables and auto filters.
- Left-to-right sorting and sorting with custom lists.
- Support for Show Data As on pivot table data fields
Fixed issues
- Support for ErrorBars on both X and Y axis on scatter, bubble and area charts.
- Copying a worksheet to a new package fails with named styles in some cases.
- When having multiple identical named styles (with the same styles set) only the first is copied.
- Handling of DBNull.Value in LoadFromDataTable function now works as expected.
- Added HideDropDown property to Data validation of type list.
- Added better exception messages for access to a package that has been disposed.
- Handling drawing objects had a concurrency when the size and position were asjusted.
- Pivot table caches with numeric/int and null values got corrupt due to an incorrect value on the containsMixedTypes attribute.
Version 5.6.4
Released: 2021-05-12, Github release
Fixed issues
- Setting TableColumn.CalculatedColumnFormula doesn't set the formula correctly on the range.
- SUMIF function could not handle arrays or ranges of criteria.
- Fixed a bug in the Tokenizer where arrays of strings did not work properly.
- Comments were removed from cells when the IsRichText flag was set.
- AutoFitColumns did not calculate the font widths correctly for unknown font sizes.
- AutoFitColumns did not take DPI settings into account.
- Some styles did not get applied in Libre Office do to missing apply attributes on the style.
- Merged cells could set duplicate ranges causing the workbook to be corrupt.
- Vml documents containg unclosed <BR> tags failed to load.
Version 5.6.3
Released: 2021-04-16, Github release
Fixed issues
- Changing the Normal style does not reflect correctly to cells with no style.
- Formula calculation does not ignore nested SUBTOTAL values when more than one level.
- Reading a workbook with a pivot table slicer can cause the document to be corrupt on save
- Invalid handling in the RowHeightCache for resizing drawings caused KeyNotFoundException in some cases.
- Reverted Microsoft.Extensions.Configuration.Json for .NET standard 2.0 to 2.1.1 for support with ASP.NET Mvc Core 2.1
- The calculation DependencyChain caused an unhandled exception with workbook defined names and when having references to deleted worksheets.
Version 5.6.2
Released: 2021-04-07, Github release
Fixed issues
- InvalidOperationException is thrown if Data Validation formula length exceeds 255 characters due to added xlfn/xlws prefixes.
- Pivot tables with row/column fields of an enum type corrupted the pivot cache.
- Reading conditional formatting from a workbook causes fonts and number formats to clear
- Custom data validation could not handle formula reference to other worksheet.
- Normal build in style causes corruption when a style named Normal exists.
- Optimize handling of image sizing and positioning on save and resizing.
- Exception on copying worksheet with a chart that has no valid X Series.
- Match function could not handle named ranges in the range argument.
- Preserve table names when copying worksheet to a new workbook.
- Number formats with localization did not set the culture code correctly.
Version 5.6.1
Released: 2021-03-23, Github release
Fixed issues & minor features
- Styles xml could get corrupt in some cases as the numFmt dxf element was created in the wrong order according to the schema.
- Merged cells could get index out of range if deleting a merged area.
- LoadFromCollection will now set the cell's Hyperlink property for class members of type Uri or ExcelHyperlink.
- EPPlus will now preserve the 'aca' and 'ca' attributes for array formulas.
- ExcelRange.Formula and ExcelRange.FormulaR1C1 didn't return a value for array formulas except for the first cell in the range.
- Defined names containing #REF
throw an exception when copying a worksheet.
- Worksheet.FirstSheet property was set to the first sheet visible, if worksheet with position 0 was hidden.
Version 5.6.0
Released: 2021-03-15
Features
- Custom table styles.
- Create and modify names table styles that can be applied to tables and pivot tables.
- Create and modify named slicer styles
- Pivot table styling using pivot areas.
- Enhanced table styling
- Added three new style properties to the ExcelTable and ExcelTableColumn
- HeaderRowStyle
- DataStyle
- TotalsRowStyle
Version 5.5.5
Released: 2021-02-23
Fixed issues
- Dependency chain sometimes drops refererences when cross-worksheet addresses are used with defined names in the formula parser.
- ExcelWorksheetView ShowHeaders had an incorrect default value causing it not to work.
- Fixed issue when EPPlus crashes on load if a pivot table uses an external source.
- EPPlus will now preserve the 'cm' and 'vm' attributes of the sheet xml - 'c' element
Version 5.5.4
Released: 2021-02-16
Fixed issues
- Formula calculation returns 0 in some cases when Excel returns null/empty in for example the MIN and MAX functions
- Fixed a bug in SUMIFS function that occured with more than 3 criterias
- The INDEX function could not handle that the range covered the cell containing the formula.
- AutoFilter.ShowButton property did not work.
- Number format "(#,##0)" got incorrectly formatted with a "-" prefix in the ExcelRange.Text property.
- Load using some CultureInfo did not work properly due to different StringComparation behavior.
Version 5.5.3
Released: 2021-02-03
Fixed issues
- Min and Max could not handle empty ranges. They now returns 0 like Excel does if the range is empty.
- Added fallback for encoding of unknown unicode characters when saving shared strings. Thanks to SamSaint.
- ExcelTableColumnCollection.Insert used an invalid key when creating the name dictionary. Thanks Meigyoku-Thmn
- Fixed ' object reference not set to an instance of an object ' if a middle column cell is null in the ToDataTable method. Thanks to Mengfw
- Added support for handling multiple colons in addresses, e.g. a1:a5:b3
- Fixed handling of the tilde(~) char in WildcardValueMatcher when not being an escape character. Used by If functions.
- Setting styles over empty columns caused cell to be removed in some cases.
- Having pivot table shared items with both null and empty string causes an exception on load.
- Defined names referencing non-existing worksheets caused crash on load.
- Exposed static method RecyclableMemory.SetRecyclableMemoryStreamManager. Thanks to LIFEfreedom.
Version 5.5.2
Released: 2021-01-18
Fixed issues
- Fixed a bug in ExpressionEvaluator that caused search criteria's starting with a wildcard character to cause an Exception.
- Setting cell value via an array overwriting rich text causes invalid cell content.
- Removed invalid handling of defined names on load if the name contained a link to an external workbook.
- Dependency chain referenced the wrong worksheet in some cases when a formula had off-sheet references when checking for circular references.
- Table headers with rich text caused corrupt workbook.
- Fixed error handling and handling of single cell addresses in COUNTIFS function
- Downgraded .NET 5 referenced packages for .NET standard 2.0 and 2.1 build.
Version 5.5.1
Released: 2021-01-12
Features
- 10 new functions:
- COMPLEX
- DEVSQ
- AVEDEV
- GAMMALN
- GAMMALN.PRECISE
- GAMMA
- DB
- SHEET
- INTRATE
- MDURATION
Fixed issues
- LoadFromCollection thrown an exception if collection was empty and PrintHeaders was false
- Adding controls after an table was added caused an corrupt workbook.
- PrecisionAndRoundingStrategy.Excel doesn't work in range -1 to 1
- EPPlus no longer validate defined names on load.
- Setting the IsRichText property to true don't convert the value to Rich text properly.
- Xml formatting issue on Saving DrawingXml. Thanks to ZaL133 for pull request.
Version 5.5.0
Released: 2020-12-18
Features
- Form Controls
- Buttons
- Drop-Downs
- List Boxes
- Check Boxes
- Radio Buttons
- Spin Buttons
- Scroll Bars
- Labels
- Group Boxes
- Group/Ungroup drawing object via the ExcelDrawing.Group and ExcelDrawing.Ungroup methods
- New attribute support for LoadFromCollection. See https://github.com/EPPlusSoftware/EPPlus/wiki/LoadFromCollection-using-Attributes
- 20 new functions
- AGGREGATE
- PERCENTILE.EXC
- DATEDIF
- QUARTILE.EXC
- STDEVA
- STDEVPA
- VARA
- VARPA
- COVAR
- COVARIANCE.P
- COVARIANCE.S
- RANK
- DOLLAR
- DOLLARDE
- DOLLARFR
- PERMUT
- PERMUTATIONA
- XOR
- MULTINOMIAL
- YIELDMAT
Fixed issues & minor fixes
- Round methods can now use only 15 sigificant figures with calculation option - PrecisionAndRoundingStrategy
- R1C1 causes corrupt worksheet if used as shared formula with off-sheet reference.
- Using double qoutes in R1C1 didn't encode correctly.
- Altering fields on a table connected to a pivot table causes the pivot table to become corrupt.
- Pivot tables with a boolean column and a filter got corrupt on save.
- Deleting a worksheet and adding it again with the same name causes a crash on save if the worksheet is referenced by a pivot table. This happends due to the SourceRange property still referencing the deleted worksheet.
- Changed ExcelAddressBase.FullName to public
- Reference table name only in indirect formula did not work.
- Replaced MemoryStrem internally with Microsoft.IO.RecyclableMemoryStream. Thanks to LIFEfreedom.
- Referencing a single cell with rich text from a formula returned an invalid value.
- Reverted .NET 5.0 references for .NET standard 2.0 and 2.1, to be compatible with Azure Functions.
Version 5.4.2
Released: 2020-11-19
Fixed issues
- .NET 5.0 to Nuget package.
- Deselecting pivot table items did not work for null values in some cases
- Deleting a worksheet containing a pivot table was not properly cleaned.
- Save crashed if a pivot tables shared items had the same string value with different case.
- Reading Hyperlinks in the format #'sheet'
A1 will now work.
- Fixed an issue with some table properties overwiting other table properties
- Fixed an issue with Range.SaveToText hanging with the FileInfo overload
- ExcelShape.SetFromFont did not set fonts correctly
- Inserting more than 32K rows at once did not work.
- Worksheets with gaps of more than 32K rows causes invalid values to be returned in some cases.
Version 5.4.1
Released: 2020-11-05
Minor new features
- WriteProtection added to Workbook.Protection. Allows to set a workbook to read-only with a password.
- ToDataTable method added to ExcelRange
Fixed issues
- Worksheet names starting with R1C1 format creates invalid addresses
- Row array in pages in the cell store caused an index out of bounds exception in rare cases.
- Setting a shared formula with an external reference causes the workbook to become corrupt in some Excel versions
- A Pivot table get corrupt if a TimeSpan is used in a column that needs shared items in the cache.
- Cell.Text returns an incorrect text when formatted #,##0;#,##0;- and value is rounded to zero.
- Delete Table With ExcelTableCollection.Delete() did not remove the xml in the package causing the table not to be deleted.
- Pivot tables with 'Save source data with file' caused an exception.
- Signing VbaProject under .NET core did not work correcly.
Version 5.4.0
Released: 2020-10-08
Features
- Pivot tables filters
- Item filters - Filters on individual items in row/column or page fields.
- Caption filters (label filters) - Filters for text on row and column fields.
- Date, numeric and string filters - Filters using various operators such as Equals, NotBetween, GreaterThan, etc.
- Top 10 filters - Filters using top or bottom criterias for percent, count or value.
- Dynamic filters - Filters using various date- and average criterias.
- Add calculated fields to pivot tables.
- Support for pivot table shared caches via the new overload for PivotTables.Add.
- Support for pivot table slicers
- Support for table slicers
Minor new features
- Number format property has been added to pivot fields and pivot cache fields.
- Added PivotTableStyle property and enum for easier setting all pivot table styles
Fixed issues
- Chart series indexes got corrupt when removing and adding series in some cases.
- Inheriting cells styling from column to cell did not work correctly in some cases.
Version 5.3.2
Released: 2020-09-14
Fixed issues
- Workbook got corrupt on copy if a worksheet VBA Code module was null.
- Setting chart series color settings to WithinLinear caused an exception in some cases.
- LoadFromCollection did not load all members as in previous version when suppling binding flags and MemberInfo's.
- If first worksheet(s) are hidden in the worksheet collection, print preview crashed in Excel.
- Worksheet Move methods failed with a crash in some cases.
Version 5.3.1
Released: 2020-09-02
Features
- Added support for copying threaded comments
- Add FormulaParserManager.Parse method.
Fixed issues
- Formulas and formula values did not encode characters below 0x1F correctly
- ExcelPackage.LoadAsync did not work with non seekable streams
- A chart that reference within its own worksheet will now change the worksheet in the series addresses for any copy made with the Worksheets.Add method
- ExcelRange.ToText method did not work correctly with rich text cells.
Version 5.3.0
Released: 2020-08-20
Features
- Support for threaded comments with mentions. See https://github.com/EPPlusSoftware/EPPlus/wiki/Threaded-comments
- Support for two new functions:
- MINIFS
- MAXIFS
Fixed issues
- Removed the extra comman added to the end of the header row in the ExcelRange.ToText method.
- The abstract class ExcelChart missed the properties DisplayBlankAs, RoundedCorners, ShowDataLabelsOverMaximum, ShowHiddenData after the version 5.2 update. The properties have been moved back again.
- ExcelColorXml.Indexed returned 0 if was not set, causing the LookupColor method to return an incorrect color.
Version 5.2.1
Released: 2020-07-22
Features
- New method ExcelRange.LoadFromDictionary.
- Support for Dynamics in ExcelRange.LoadFromCollection (ExpandoObject).
- New Lambda config parameter to ExcelRange.LoadFromCollection with new options for header parsing (for example: UnderscoreToSpace, CamelCaseToSpace)
- Zip64 support in packaging to enable larger packages.
Fixed issues
- Wildcards in MATCH function not working
- Performance issue in ExpressionEvaluator.GetNonAlphanumericStar
- Using the sheet name to reference the entire worksheet did not work in formulas.
- GetAsByteArray corrupted the package if ExcelPackage.Save was called before.
- Parsing issue in the Value function
Version 5.2.0
Released: 2020-06-24
Features
- Support for extended chart types and Stock charts:
- Four types of stock charts: High-Low-Close, Open-High-Low-Close, Volume-High-Low-Close and Volume-Open-High-Low-Close
- Sunburst Chart
- Treemap Chart
- Histogram Chart
- Pareto Chart
- Funnel Chart
- Waterfall Chart
- Box & Whisker Chart
- Region Map Chart
- Support for 49 new functions:
- BESSELI
- BESSELJ
- BESSELK
- BESSELY
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CUMIPMT
- CUMPRINC
- DDB
- DISC
- DURATION
- EFFECT
- ERF
- ERF.PRECISE
- ERFC
- ERFC.PRECISE
- FV
- FVSCHEDULE
- IPMT
- IRR
- ISPMT
- MIRR
- MODE
- MODE.SNGL
- NOMINAL
- NPER
- NPV
- PDURATION
- PERCENTILE
- PERCENTILE.INC
- PERCENTRANK
- PERCENTRANK.INC
- PPMT
- PRICE
- PV
- RATE
- RRI
- SLN
- SYD
- TEXTJOIN
- VAR.P
- VAR.S
- XIRR
- XNPV
- YIELD
- Four new properties to Style.Font
- Charset
- Condense
- Extend
- Shadow
- Added * New `As` property to the Drawing, ConditionalFormatting and DataValidation objects for easier type cast. For example `var sunburstChart
worksheet.Drawings[0].As.Chart.SunburstChart;` or `var listDv
sheet.DataValidations.First().As.ListValidation;`
- OFFSET function calculation can now be a part of a range in formula calculation. For example `SUM(A1:OFFSET(A3, -2, 0))`
- Added ShowColumnHeaders, ShowColumnStripes, ShowRowHeaders, ShowRowStripes and ShowLastColumn properties to ExcelPivotTable
Fixed issues
- Ignore a leading "_xlfn." in function names when calculating formulas.
- Only the fist paragraph was loaded when the RichText property was loaded from the XML.
- VerticalAlign property did not delete the XmlNode properly when set to None.
- A named style with a missing fontid crashed on load.
- Deleting a worksheet in a macro enabled workbook causes a NullReferenceException.
Version 5.1.2
Released: 2020-05-13
Features
- Added ClearFormulas and ClearFormulaValues to Range, Worksheet and Workbook. ClearFormulas removes all formulas, ClearFormulaValues removes all previously calculated/cached values.
- Added support for 19 new engineering functions:
- CONVERT
- DELTA
- BIN2DEC
- BIN2HEX
- BIN2OCT
- DEC2BIN
- DEC2HEX
- DEC2OCT
- HEX2BIN
- HEX2DEC
- HEX2OCT
- OCT2BIN
- OCT2DEC
- OCT2HEX
- BITLSHIFT
- BITAND
- BITOR
- BITRSHIFT
- BITXOR
Fixed issues
- Delete method adjusted row offset on drawings incorrectly.
- When copying a worksheet with images to an other package did not work correctly in some cases.
- Datavalidation addresses did not update correctly when deleting columns.
- Opening a packages saved with System.IO.Compression caused a corrupted package on save.
- Added a check to the ExcelPackage Construcors if the FileInfo exists and is zero-byte. Supplying a zero-byte file will now create a new package. Supplying a zero-byte template will throw an exception.
- Fixed scaling for pictures. Changed data type for internal pixel variables from int to double.
- Delete/Insert din't handle comma separated addresses in data validation / conditional formatting
- ColumnMin and ColumnMax were not correctly updated when one or more columns were deleted.
Version 5.1.1
Released: 2020-04-27
Features
- Added method RemoveVBAProject to ExcelWorkbook.
Fixed issues
- CalculatedColumnFormula property was set to the range on save, overwriting any cell value that was changed in the range of the formula.
- VbaProject. Remove didn't fully remove the VBA project.
- LoadFromCollection didn't work will List<object>.
- Group shapes containg shapes with the same name throw exception.
- Worksheets with a large amount of columns had bad performance on save.
Version 5.1.0
Released: 2020-04-07
Features
- Insert and Delete method added to ExcelRange. Cells will be shifted depending on the argument supplied.
- AddRow, InsertRow and DeleteRow added to ExcelTable.
- Add, Insert and Delete added To ExcelTableColumnCollection.
- Added support for new functions:
- FACTDOUBLE
- COMBIN
- COMBINA
- SEC
- SECH
- CSC
- CSCH
- COT
- COTH
- RADIANS
- ACOT
- ACOTH
- ROMAN
- GCD
- LCM
- FLOOR.PRECISE
- CEILING.PRECISE
- MROUND
- ISO.CEILING
- FLOOR.MATH
- CEILING.MATH
- SUMXMY2
- SUMX2MY2
- SUMX2PY2
- SERIESSUM
Fixed issues
- Images added with AddImage(Image) did not use the oneCellAnchore element.
- ExcelPackage.CopyStreamAsync did not fully use async (Flush-->FlushAsync), causing an exception.
- VBA module names restricts some allowed characters.
- Shared Formulas are not handled correctly when an address argument reference another worksheet.
- Adding a Sparklinegroup does not add it to the SparklineGroups collection.
- Files saved in LibreOffice did not handle boolean properties correctly for rows and columns, (for example the hidden property).
- Data validation - List validation did not set the ShowErrorMessage when an address referenced another worksheet.
- Addresses with style: 'sheet'
A1:'sheet'
A2 was not handled correctly.
Version 5.0.4
Released: 2020-03-10
Fixed issues
- Datavalidation on lists failed if the formula was an defined name.
- Merged cells got cleared if a value was set over multiple cells
- RichText causes xml "corruption" if a blank string or null was added to the collection.
Version 5.0.3
Released: 2020-03-08
Minor new features
- Chart series will from version 5 handles both addresses and arrays. Arrays are handled in the StringLiteralsX, NumberLiteralsX and NumberLiteralsY arrays when a series is set to an Array ( for example {1,2,3} ).
- Add support for BringToFront and SendToBack methods on the Drawings Collection to handle drawing overlap.
- Add TopLeftCell to ExcelWorksheetView
- Enabled Style.TextRotation
255 for Vertical Text in cells. Added new method ExcelStyle.SetTextVertical.
- Add Pivot property to conditional formatting, to indicate a pivot source.
- RichText on drawings can now handle paragraphs to get line breaks. Add method has new parameter NewParagraph.
- Table source overload to PivotTable.Add method
- 13 new functions supported in the Formula calculation engine: IFS, SWITCH, WORKDAY.INTL, TYPE, ODD, EVEN, DAYS, NUMBERVALUE, UNICHAR, UNICODE, CLEAN, TRIM and CONCAT
General fixes
- Cellstore has been rewritten. This should fix some issues with inserting and deleting rows and columns. Also fixes a sorting issue.
- Fixed SchemaNodeOrder in many drawing classes.
- Handling of circular references has been redesigned.
Fixed issues
- Worksheet.Hidden does not always hide the worksheet.
- Drawings will now move and size when inserting/deleting rows/columns depending on the ExcelDrawing.EditAs property.
- Adding a Table caused an exception if a chart sheet existed in the workbook.
- Adding a PivotTable caused an exception if a chart sheet existed in the workbook.
- Custom document properties are case insensitive.
- Sheet with rich text inline string can not handle whitespaces only.
- StackOverflowException when calling Clear on a comma separated Range.
- ExcelWorksheet.Copy corrupts package if a relationship to drawing.xml with no drawings exists.
- Copying formulas in ranges can lead to invalid #REF
for fixed addresses.
- Table column names are validated for duplicates on a non-encoded value
- Packages with a prefix for the main xml namespace for a worksheet gets corrupted.
- ExcelRangeBase.AutofitColumns() unhides hidden columns
- The Normal style does not work correctly if not named Normal. The Normal style is now found using the first occurrance of the BuildInId
0 criteria.
- URI for the sharedstrings and the styles part were not fetched by RelationshipType when a package was loaded.
- Setting a cell value to a char datatype will result i "0" when saved
- Structs in a cell value can result in a null value when converted to string on save
- Conditional formatting styles crashed when copying a worksheet from another package.
- EPPlus crashes on load if a workbook or worksheet has more than one defined name with the same name.
- Row styles were not copied correctly copied when inserting rows
- Overwriting a shared formulas first cell causes a crash.
- Workbooks with Empty series for Scatter- and Doughnut- charts crashes on load
- FileStream for compound documents are not closed.
- If CustomSheetView element contained row/column breaks, the package could not be loaded.
- Pivot tables crashed if SubTotalFunction were set to eSubTotalFunctions.None and there were no values in the source data.
- Data validations of type list don't support formula references to other worksheets.
- Formula calc: Does not remove double-negation from formulatokens.
- Formula calc: Value matcher now supports comparisons between DateTime and double. CompileResultFactory includes float type in DataType.Decimal.
- Formula calc: MultipleRangeCriterasFunction.GetMatchIndexes() looped through max number of rows when a range argument was an entire column now stops as Dimension.End.Row. Fixed a bug in CountIfs function which wasn't working properly with multiple criteria's
- Formula calc: Support Instance_num parameter of SUBSTITUTE function.