Breaking Changes in EPPlus 6

Targeting frameworks¶

The EPPlus 6 Nuget package will no longer target .NET 4.0 and 4.5, as these versions are no longer supported.
EPPlus 6 will instead target .NET 4.5.2. A target for .NET 6 has also been added.

Removing dependency on System.Drawing.Common¶

In EPPlus 6 all public interfaces referencing to System.Drawing.Common has been removed. The reason for this is that Microsoft has deprecated System.Drawing.Common on all non-Windows platforms. To replace System.Drawing.Common we have implemented native code to handle the different image formats and text measurings. To handle Colors EPPlus adds a reference to System.Drawing.Primitives. This means that all references to Color will work as before.

The following methods and properties have been removed or changed:

• ExcelDrawings.AddPicture(string, Image) - Has been removed. Use other overloads.
• ExcelDrawings.AddPicture(string, Image, Uri) - Has been removed. Use other overloads.
• ExcelDrawings.AddPictureAsync(string, Image) - Has been removed. Use other overloads.
• ExcelDrawings.AddPictureAsync(string, Image, Uri) - Has been removed.
• ExcelPicture.ImageFormat - Has been removed. Use other overloads.
• ExcelBackgroundImage.Image - Property has changed data type to ExcelImage. See description on ExcelImagebelow.
  ExcelBackgroundImage.Image can no longer be set to null. Use ExcelBackgroundImage.Remove instead.
• ExcelDrawingBlipFill.Image - Property has changed data type to ExcelImage. See description on ExcelImagebelow.
• ExcelDrawingBlipFill.ImageFormat - Has been removed. Use ExcelImage.Type instead.
• ExcelVmlDrawingPicture.Image - Property has changed data type to ExcelImage. See description on ExcelImagebelow.
• ExcelVmlDrawingPicture.InsertPicture - Has been removed.
• ExcelVmlDrawingPictureFill.Image - Property has changed data type to ExcelImage. See description on ExcelImagebelow.
• ExcelFont.SetFromFont(Font) - Has been replaced with new signature.
• ExcelTextFont.SetFromFont(Font) - Has been replaced with new signature.
• ExcelFontXml.SetFromFont(Font)- Has been replaced with new signature.

The ExcelImage class¶

The ExcelImage class replaces all System.Drawing.Common.Image properties (see the list above).

All SetImage and SetImageAsync methods returns the ExcelImage object. This object can be used with SetImage(ExcelImage) overload.

Supported Image Formats.¶

The internal image handler supports the following image formats: * Bmp * Jpeg/Exif * Gif * Png * Tif * Ico * Svg * WebP * Wmf/Wmz * Emf/Wmz

Adding extension methods for removed methods referencing System.Drawing.Common¶

Switching to overloads supported natively by EPPlus 6 is recommended, but if you have a lot of references to System.Drawing.Common, adding extension methods for images and fonts might simplify...

using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
namespace OfficeOpenXml.Drawing.Extensions
{
    public static class EPPlusDrawingExtensions
    {
        /// <summary>
        /// Adds a picture to the drawings 
        /// </summary>
        /// <param name="drawings">The collection </param>
        /// <param name="name">The name of the drawing</param>
        /// <param name="image">The image object to add</param>
        /// <returns>A picture added to the drawings collection.</returns>
        public static ExcelPicture AddPicture(this ExcelDrawings drawings, string name, Image image)
        {
            var ms = new MemoryStream();
            image.Save(ms, image.RawFormat);
            return drawings.AddPicture(name, ms, GetPictureType(image));
        }
        /// <summary>
        /// Adds a picture to the drawings 
        /// </summary>
        /// <param name="drawings">The collection </param>
        /// <param name="name">The name of the drawing</param>
        /// <param name="image">The image object to add</param>
        /// <param name="uri">An hyperlink reference used when clicking the drawing object.</param>
        /// <returns>A picture added to the drawings collection.</returns>
        public static ExcelPicture AddPicture(this ExcelDrawings drawings, string name, Image image, Uri uri)
        {
            var ms = new MemoryStream();
            image.Save(ms, image.RawFormat);
            return drawings.AddPicture(name, ms, GetPictureType(image), uri);
        }
        /// <summary>
        /// Sets the image
        /// </summary>
        /// <param name="xlImage">The image object </param>
        /// <param name="image">The image to set</param>
        public static void SetImage(this ExcelImage xlImage, Image image)
        {
            var ms = new MemoryStream();
            image.Save(ms, image.RawFormat);
            xlImage.SetImage(ms, GetPictureType(image));
        }
        /// <summary>
        /// Sets the font from a font object 
        /// </summary>
        /// <param name="xlFont">The EPPlus Text Font object</param>
        /// <param name="font">The font object.</param>
        public static void SetFromFont(this ExcelTextFont xlFont, Font font)
        {
            xlFont.SetFromFont(font.Name, font.SizeInPoints, font.Bold, font.Italic, font.Underline, font.Strikeout);
        }
        /// <summary>
        /// Sets the font from a font object 
        /// </summary>
        /// <param name="xlFont">The EPPlus Text Font object</param>
        /// <param name="font">The font object.</param>
        public static void SetFromFont(this OfficeOpenXml.Style.ExcelFont xlFont, Font font)
        {
            xlFont.SetFromFont(font.Name, font.SizeInPoints, font.Bold, font.Italic, font.Underline, font.Strikeout);
        }
        private static ePictureType GetPictureType(Image image)
        {
            if (image.RawFormat.Equals(ImageFormat.Jpeg) || image.RawFormat.Equals(ImageFormat.Exif))
            {
                return ePictureType.Jpg;
            }
            else if (image.RawFormat.Equals(ImageFormat.Gif))
            {
                return ePictureType.Gif;
            }
            else if (image.RawFormat.Equals(ImageFormat.Png))
            {
                return ePictureType.Png;
            }
            else if (image.RawFormat.Equals(ImageFormat.Emf))
            {
                return ePictureType.Emf;
            }
            else if (image.RawFormat.Equals(ImageFormat.Wmf))
            {
                return ePictureType.Wmf;
            }
            else if (image.RawFormat.Equals(ImageFormat.Tiff))
            {
                return ePictureType.Tif;
            }
            else if (image.RawFormat.Equals(ImageFormat.Icon))
            {
                return ePictureType.Ico;
            }
            else
            {
                return ePictureType.Bmp;
            }
        }
    }
}

Imports System
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.IO
Imports System.Runtime.CompilerServices
Imports OfficeOpenXml.Drawing
Imports OfficeOpenXml.Style
Namespace OfficeOpenXml.Drawing.Extensions
    Public Module EPPlusDrawingExtensions
        ''' <summary>
        ''' Adds a picture to the drawings 
        ''' </summary>
        ''' <param name="drawings">The collection </param>
        ''' <param name="name">The name of the drawing</param>
        ''' <param name="image">The image object to add</param>
        ''' <returns>A picture added to the drawings collection.</returns>
        <Extension()>
        Public Function AddPicture(drawings As ExcelDrawings, name As String, image As Image) As ExcelPicture
            Dim ms = New MemoryStream()
            image.Save(ms, image.RawFormat)
            Return drawings.AddPicture(name, ms, GetPictureType(image))
        End Function
        ''' <summary>
        ''' Adds a picture to the drawings 
        ''' </summary>
        ''' <param name="drawings">The collection </param>
        ''' <param name="name">The name of the drawing</param>
        ''' <param name="image">The image object to add</param>
        ''' <param name="uri">An hyperlink reference used when clicking the drawing object.</param>
        ''' <returns>A picture added to the drawings collection.</returns>
        <Extension()>
        Public Function AddPicture(drawings As ExcelDrawings, name As String, image As Image, uri As Uri) As ExcelPicture
            Dim ms = New MemoryStream()
            image.Save(ms, image.RawFormat)
            Return drawings.AddPicture(name, ms, GetPictureType(image), uri)
        End Function
        ''' <summary>
        ''' Sets the image
        ''' </summary>
        ''' <param name="xlImage">The image object </param>
        ''' <param name="image">The image to set</param>
        <Extension()>
        Public Sub SetImage(xlImage As ExcelImage, image As Image)
            Dim ms = New MemoryStream()
            image.Save(ms, image.RawFormat)
            xlImage.SetImage(ms, GetPictureType(image))
        End Sub
        ''' <summary>
        ''' Sets the font from a font object 
        ''' </summary>
        ''' <param name="xlFont">The EPPlus Text Font object</param>
        ''' <param name="font">The font object.</param>
        <Extension()>
        Public Sub SetFromFont(xlFont As ExcelTextFont, font As Font)
            xlFont.SetFromFont(font.Name, font.SizeInPoints, font.Bold, font.Italic, font.Underline, font.Strikeout)
        End Sub
        ''' <summary>
        ''' Sets the font from a font object 
        ''' </summary>
        ''' <param name="xlFont">The EPPlus Text Font object</param>
        ''' <param name="font">The font object.</param>
        <Extension()>
        Public Sub SetFromFont(xlFont As OfficeOpenXml.Style.ExcelFont, font As Font)
            xlFont.SetFromFont(font.Name, font.SizeInPoints, font.Bold, font.Italic, font.Underline, font.Strikeout)
        End Sub
        Private Function GetPictureType(image As Image) As ePictureType
            If image.RawFormat.Equals(ImageFormat.Jpeg) OrElse image.RawFormat.Equals(ImageFormat.Exif) Then
                Return ePictureType.Jpg
            ElseIf image.RawFormat.Equals(ImageFormat.Gif) Then
                Return ePictureType.Gif
            ElseIf image.RawFormat.Equals(ImageFormat.Png) Then
                Return ePictureType.Png
            ElseIf image.RawFormat.Equals(ImageFormat.Emf) Then
                Return ePictureType.Emf
            ElseIf image.RawFormat.Equals(ImageFormat.Wmf) Then
                Return ePictureType.Wmf
            ElseIf image.RawFormat.Equals(ImageFormat.Tiff) Then
                Return ePictureType.Tif
            ElseIf image.RawFormat.Equals(ImageFormat.Icon) Then
                Return ePictureType.Ico
            Else
                Return ePictureType.Bmp
            End If
        End Function
    End Module
End Namespace

Remember to add the name space where you want to use the methods, so they are accessible.

using OfficeOpenXml.Drawing.Extensions;

Changed visibility of Formula calculation classes/interfaces.¶

One of our major focus areas for the continued development of EPPlus 6 is improving performance and functionality of the formula calculation. For this reason we have changed a number of classes, interfaces, methods and properties that in previous versions were public to internal as they are likely to go away or change drastically. This means that these items will no longer be accessible via the interfaces of EPPlus 6.

From public to internal¶

• OfficeOpenXml.FormulaParsing.ExcelDataProvider
• OfficeOpenXml.FormulaParsing.EpplusExcelDataProvider
• OfficeOpenXml.FormulaParsing.EpplusNameValueProvider
• OfficeOpenXml.FormulaParsing.EpplusNameValueProvider
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.DatabaseFunction
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.Dsum
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.Dcount
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.Dget
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.Dmin
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.Dvarp
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.ExcelDatabase
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.RowMatcher
• OfficeOpenXml.FormulaParsing.Excel.Functions.Database.ExcelDatabaseCriteria
• OfficeOpenXml.FormulaParsing.Excel.Functions.Math.MultipleRangeCriteriasFunction
• OfficeOpenXml.FormulaParsing.ExcelUtilities.AddressTranslator
• OfficeOpenXml.FormulaParsing.ExcelUtilities.AddressTranslator.RangeCalculationBehaviour
• OfficeOpenXml.FormulaParsing.ExcelUtilities.AddressTranslator.IndexToAddressTranslator
• OfficeOpenXml.FormulaParsing.ExcelUtilities.AddressTranslator.RangeAddressFactory
• OfficeOpenXml.FormulaParsing.ExcelUtilities.AddressTranslator.RangeAddressFactory
• OfficeOpenXml.FormulaParsing.ExcelUtilities.ExpressionEvaluator
• OfficeOpenXml.FormulaParsing.ExpressionGraph.ExcelAddressExpression
• OfficeOpenXml.FormulaParsing.ExpressionGraph.ExpressionFactory
• OfficeOpenXml.FormulaParsing.ExpressionGraph.ExpressionGraphBuilder
• OfficeOpenXml.FormulaParsing.ExpressionGraph.IExpressionGraphBuilder
• OfficeOpenXml.FormulaParsing.ExpressionGraph.GroupExpression
• OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression
• OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionArgumentExpression
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenHandler
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenizerContext
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.PostProcessing.TokenizerPostProcessor
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenSeparatorHandlers.TokenSeparatorHandler
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenSeparatorHandlers.MultipleCharSeparatorHandler
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenSeparatorHandlers.SeparatorHandler
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenSeparatorHandlers.SheetnameHandler
• OfficeOpenXml.FormulaParsing.LexicalAnalysis.TokenSeparatorHandlers.StringHandler

Other changes related to formula calc¶

• OfficeOpenXml.FormulaParsing.ExcelDataProvider.IRangeInfo moved to OfficeOpenXml.FormulaParsing.IRangeInfo
• OfficeOpenXml.FormulaParsing.ExcelDataProvider.ICellInfo moved to OfficeOpenXml.FormulaParsing.ICellInfo
• OfficeOpenXml.FormulaParsing.ExcelDataProvider.INameInfo moved to OfficeOpenXml.FormulaParsing.INameInfo
• OfficeOpenXml.FormulaParsing.FormulaParser constructors from previous versions made internal. New public constructor FormulaParser(ExcelPackage package)
• Method OfficeOpenXml.FormulaParsing.FormulaParser.Configure() removed/made internal.
• PropertyOfficeOpenXml.FormulaParsing.ParsingConfiguration.GraphBuilder removed/made internal.
• Method OfficeOpenXml.FormulaParsing.ParsingConfiguration.SetGraphBuilder() removed/made internal.
• PropertyOfficeOpenXml.FormulaParsing.ParsingContext.ExcelDataProvider removed/made internal.
• PropertyOfficeOpenXml.FormulaParsing.ParsingContext.RangeAddressFactory removed/made internal.

FontSize class¶

Static class 'FontSize' has splitted width and heights into two dictionaries. FontSizes are lazy-loaded when needed.

Removed methods¶

• Misspelled method ExcelNamedRangeCollection.AddFormla has been removed. Please use ExcelNamedRangeCollection.AddFormula
• Method OfficeOpenXml.FormulaParsing.FormulaParser.Configure() removed/made internal.
• Method OfficeOpenXml.FormulaParsing.ParsingConfiguration.SetGraphBuilder() removed/made internal.

Also see Breaking Changes in EPPlus 5