2
Vote

OfficeOpenXml.Drawing.ExcelDrawing.SetPixelWidth Divide by Zero when running on Azure Websites

description

Inserting an image with ExcelPicture pic = ws.Drawings.AddPicture(uniqueImageName, img) and also a chard with

var chart = pivotWorksheet.Drawings.AddChart("PivotChart", eChartType.ColumnClustered, pivotTable);
chart.SetPosition(endRow + 2, 20, 1, 10);
chart.SetSize(600, 400);

gives a divide by zero error. Identical code identical data runs in other environments.

comments

andiih wrote Sep 18, 2012 at 10:21 AM

I've pulled the EPPlus source and done some sleuthing.

The issue is in ExcelWorkbook.cs line 283 which calls

System.Windows.Forms.TextRenderer.MeasureText(string,Font).Width

which appears to return Zero in an Azure website.

For my own purposes I've hacked a constant in if it returns zero, but this needs a better fix.

JanKallman wrote Sep 19, 2012 at 3:01 PM

Ok, so no GDI. This method probably uses gdi under the hood as well, but give it a try.
I have no access to Asure, so try this for me...
    public decimal MaxFontWidth 
    {
        get
        {
            if (_standardFontWidth == decimal.MinValue)
            {
                var font = Styles.Fonts[0];
                System.Drawing.Font f = new System.Drawing.Font(font.Name, font.Size);
                _standardFontWidth=(decimal)(System.Windows.Forms.TextRenderer.MeasureText("00", f).Width - System.Windows.Forms.TextRenderer.MeasureText("0", f).Width);
                if(_standardFontWidth<=0)
                {
                    using (Bitmap b = new Bitmap(1, 1))
                    {
                        using (Graphics g = Graphics.FromImage(b))
                        {
                            _standardFontWidth = (decimal)Math.Truncate(g.MeasureString("00", f).Width - g.MeasureString("0", f).Width);
                        }
                    }
                }
            }
            return _standardFontWidth;
        }
    }

noocyte wrote Sep 19, 2012 at 8:41 PM

Will try tomorrow (GMT+2), but please note that GDI is NOT support when using ASP.Net: http://msdn.microsoft.com/en-us/library/system.drawing.aspx
So it's really not an optimal fix, but I'm guessing EPPlus is using System.Drawing in more than just this one method?

JanKallman wrote Sep 19, 2012 at 9:10 PM

It's not optimal, but I'm not sure what the alternative is, unless you want to write your own text measuring code. And thats is neither easy or optimal. If you (or anyone else) has any other ideas, please let me know.

noocyte wrote Sep 20, 2012 at 6:56 AM

I have had a chance to test now; same error I'm afraid.

JanKallman wrote Sep 20, 2012 at 7:48 AM

Ok, not surprising. I guess you have the same problem with autofit columns and using pictures on Asure as well then. System.Drawings is also used for Colors in various places, but hopefully that works anyway.

andiih wrote Sep 20, 2012 at 8:40 AM

Is the issue just MaxFontWidth? When I do if (_standardFontWidth==0) _standardFontWidth=7; then I get a working system (at least for the bits of EPP I'm using although 7 was just 'my' value). I'm wondering if a fallback function along the lines of (font.Size * k) and estimate a value for k would work - could even do a lookup table based on font.Name for k? Or is more than MeasureText the issue?

noocyte wrote Sep 20, 2012 at 8:42 AM

For this particular issue perhaps a 'sane' default value would be appropriate? That way it might look funny, but at least it won't crash.

andiih wrote Sep 20, 2012 at 8:42 AM

I should have said - my code was inserting an image, so the other bits used to insert an image work OK. It was very basic usage - insert and image and set a size only, so with a fix to this EPPlus can put an image in a spreadsheet on an Azure website.

JanKallman wrote Sep 20, 2012 at 2:35 PM

Ok, try this fix. I just added a simple approx. for the the size and added a set method, so the value can be manually adjusted. Not so pretty, but ...
    /// <summary>
    /// Max font width for the workbook
    /// <remarks>This method uses GDI. If you use Asure or another environment that does not support GDI, you have to set this value manually if you don't use the standard Calibri font</remarks>
    /// </summary>
    public decimal MaxFontWidth 
    {
        get
        {
            if (_standardFontWidth == decimal.MinValue)
            {
                var font = Styles.Fonts[0];
                try
                {
                    Font f = new Font(font.Name, font.Size);
                    using (Bitmap b = new Bitmap(1, 1))
                    {
                        using (Graphics g = Graphics.FromImage(b))
                        {
                            _standardFontWidth = (decimal)Math.Truncate(g.MeasureString("00", f).Width - g.MeasureString("0", f).Width);
                        }
                    }
                    if (_standardFontWidth <= 0) //No GDI?
                    {
                        _standardFontWidth = (int)(font.Size * (2D / 3D)); //Aprox. for Calibri.
                    }
                }
                catch   //Error, set default value
                {
                    _standardFontWidth = (int)(font.Size * (2D / 3D)); //Aprox for Calibri.
                }
            }
            return _standardFontWidth;
        }
        set
        {
            _standardFontWidth = value;
        }
    }

noocyte wrote Sep 21, 2012 at 5:58 AM

Fail again... :(

This back and forward isn't doing any of us any good. :) You could sign up for a free Azure Trial here: https://www.windowsazure.com/en-us/pricing/free-trial/ Remember it's the Azure Websites, not Web role (cloud services) that's causing problems for us.

Otherwise I will have to dig in and create a new project to test/fix this.

JanKallman wrote Sep 21, 2012 at 7:24 AM

Yes the Graphics call will fail, its the same as before, but you get the approximated value back ((int)(font.Size * (2D / 3D)); //Approx. for Calibri.
}), right?