OfficeOpenXml / Excel setting cell formula lead to security errors on downloaded file

Question

User-1939834628 posted

I wrote the following code to create a simple report in Excel:

        private void EsportaXLS(DataTable table, bool protect = false)
        {
            using (ExcelPackage _pck = new ExcelPackage())
            {
                foreach (IGrouping<int, DataRow> _convenzionato in table.AsEnumerable().GroupBy(_row => _row.Field<int>("id_convenzionato")))
                {
                    StringBuilder _subtotali = new StringBuilder();

                    ExcelWorksheet _ws = _pck.Workbook.Worksheets.Add(_convenzionato.First().Field<string>("ragione_sociale"));

                    if (protect)
                    {
                        _ws.Protection.IsProtected = true;
                        _ws.Protection.SetPassword(PASSWORD);
                    }

                    _ws.Cells.Style.Font.Size = 8;
                    _ws.Cells.Style.Font.Name = "Arial";
                    _ws.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    using (var _rng = _ws.Cells[1, 1, 1, 6])
                    {
                        _rng.Merge = true;
                        _rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        _rng.Value = "REPORT SITUAZIONE NOLEGGI MENSILE";
                        _rng.Style.Font.Size = 13;
                    }

                    using (var _cell = _ws.Cells[2, 1])
                    {
                        _cell.Value = "Mese";
                        _cell.Style.Font.Size = 9;
                    }

                    using (var _cell = _ws.Cells[3, 1])
                    {
                        _cell.Value = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(int.Parse(_hdnMese.Value)) + " " + _hdnAnno.Value;
                        _cell.Style.Font.Size = 9;
                        _cell.Style.Font.Bold = true;
                    }

                    _ws.Cells[2, 1, 2, 6].Style.Font.Bold = true;

                    int _offset = 5;

                    bool _totale = true;

                    foreach (IGrouping<int, DataRow> _veicolo in _convenzionato.GroupBy(_row => _row.Field<int>("id_veicolo")))
                    {
                        _ws.Cells[_offset, 1].Value = "Modello";
                        _ws.Cells[_offset, 2].Value = "Targa";
                        _ws.Cells[_offset, 3].Value = "Canone";
                        _ws.Cells[_offset, 4].Value = "Perc. recupero";
                        _ws.Cells[_offset, 5].Value = "VIP";

                        using (var _rng = _ws.Cells[_offset, 1, _offset, 5])
                        {
                            _rng.Style.Font.Bold = true;
                            _rng.Style.Font.Size = 9;
                        }

                        _offset++;

                        _ws.Cells[_offset, 1].Value = _veicolo.First().Field<string>("modello");
                        _ws.Cells[_offset, 2].Value = _veicolo.First().Field<string>("targa");

                        using (var _cell = _ws.Cells[_offset, 3])
                        {
                            _cell.Value = _veicolo.First().Field<double?>("canone");
                            _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                            _cell.Style.Font.Color.SetColor(Color.Red);
                            _cell.Style.Font.Size = 12;
                            _cell.Style.Font.Bold = true;
                        }

                        using (var _cell = _ws.Cells[_offset, 4])
                        {
                            _cell.Value = _veicolo.First().Field<double>("aliquota") / 100.0;
                            _cell.Style.Numberformat.Format = "#0%";
                            _cell.Style.Font.Bold = true;
                            _cell.Style.Font.Size = 9;
                            _cell.Style.Font.Color.SetColor(Color.Green);
                        }

                        _ws.Cells[_offset, 5].Value = _veicolo.First().Field<bool>("vip") ? "Si" : "No";

                        foreach (IGrouping<string, DataRow> _modalita in _veicolo.GroupBy(_row => _row.Field<string>("modalita_noleggio")))
                        {
                            _offset += 2;

                            using (var _cell = _ws.Cells[_offset, 1])
                            {
                                _cell.Value = "Modalità";
                                _cell.Style.Font.Bold = true;
                            }

                            _offset++;

                            using (var _cell = _ws.Cells[_offset, 1])
                            {
                                _cell.Value = _modalita.First().Field<string>("modalita_noleggio");
                                _cell.Style.Font.Bold = true;
                                _cell.Style.Font.Size = 9;
                            }

                            _offset += 2;

                            _ws.Cells[_offset, 1].Value = "Numero";
                            _ws.Cells[_offset, 2].Value = "Serie";
                            _ws.Cells[_offset, 3].Value = "GG. Fattur.";

                            if (_modalita.First().Field<string>("modalita_noleggio").Equals("S"))
                            {
                                _ws.Cells[_offset, 4].Value = "Importo pratica PRESUNTO";
                                _ws.Cells[_offset, 5].Value = "Importo maturato PRESUNTO";
                            }
                            else
                            {
                                _ws.Cells[_offset, 4].Value = "Importo pratica";
                                _ws.Cells[_offset, 5].Value = "Importo maturato";
                            }

                            using (var _rng = _ws.Cells[_offset, 1, _offset, 5])
                            {
                                _rng.Style.Font.Bold = true;
                                _rng.Style.Font.Size = 9;
                            }

                            _offset++;

                            StringBuilder _importi = new StringBuilder();

                            foreach (DataRow _noleggio in _modalita)
                            {
                                _ws.Cells[_offset, 1].Value = _noleggio["numero"];

                                using (var _cell = _ws.Cells[_offset, 2])
                                {
                                    _cell.Value = _noleggio["serie"];
                                    _cell.Style.Font.Bold = true;
                                    _cell.Style.Font.Size = 12;
                                }

                                _ws.Cells[_offset, 3].Value = _noleggio["giorni_lavorativi"];

                                using (var _cell = _ws.Cells[_offset, 4])
                                {
                                    _cell.Value = _noleggio["importo_pratica"];
                                    _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                                    _cell.Style.Font.Bold = true;
                                    _cell.Style.Font.Size = 9;
                                    _cell.Style.Font.Color.SetColor(Color.Red);
                                }

                                using (var _cell = _ws.Cells[_offset, 5])
                                {
                                    _cell.Value = _noleggio["importo_maturato"];
                                    _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                                    _cell.Style.Font.Bold = true;
                                    _cell.Style.Font.Size = 9;
                                    _cell.Style.Font.Color.SetColor(Color.Red);
                                }

                                _importi.Append(string.Format("E{0};", _offset));

                                _offset++;
                            }

                            if (_modalita.First().Field<string>("modalita_noleggio").Equals("S") && _veicolo.First().Field<bool>("vip") && (_modalita.Count() >= CCostanti.VIP_SOGLIA))
                            {
                                _ws.Cells[_offset, 1].Value = "BONUS VIP";

                                using (var _cell = _ws.Cells[_offset, 5])
                                {
                                    _cell.Value = CCostanti.VIP_PREZZO_UNITARIO;
                                    _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                                    _cell.Style.Font.Bold = true;
                                    _cell.Style.Font.Size = 9;
                                    _cell.Style.Font.Color.SetColor(Color.Red);
                                }

                                _offset++;
                            }

                            _offset++;

                            using (var _cell = _ws.Cells[_offset, 4])
                            {
                                _cell.Value = "SUB-TOTALE";
                                _cell.Style.Font.Bold = true;
                                _cell.Style.Font.Size = 9;
                            }

                            using (var _cell = _ws.Cells[_offset, 5])
                            {
                                _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                                _cell.Formula = "SUM(" + _importi.ToString().TrimEnd(';') + ")";
                                _cell.Style.Font.Size = 12;
                                _cell.Style.Font.Bold = true;

                                _cell.Calculate();
                            }

                            _subtotali.Append(string.Format("E{0};", _offset));
                        }

                        _offset += 3;
                    }

                    if (_totale)
                    {
                        using (var _cell = _ws.Cells[_offset, 4])
                        {
                            _cell.Value = "TOTALE";
                            _cell.Style.Font.Bold = true;
                            _cell.Style.Font.Size = 9;
                        }

                        using (var _cell = _ws.Cells[_offset, 5])
                        {
                            _cell.Formula = "SUM(" + _subtotali.ToString().TrimEnd(';') + ")";
                            _cell.Style.Numberformat.Format = CURRENCY_FORMAT;
                            _cell.Style.Font.Size = 12;
                            _cell.Style.Font.Bold = true;

                            _cell.Calculate();
                        }
                    }

                    _ws.Cells[_ws.Dimension.Address].AutoFitColumns();
                }

                Response.Clear();

                Response.ContentType = System.Web.MimeMapping.GetMimeMapping(FILENAME);
                Response.AddHeader("Content-Disposition", string.Format("attachment;  filename={0}", FILENAME));
                Response.BinaryWrite(_pck.GetAsByteArray());
                Response.End();
            }
        }

The worksheet is created correctly .. the only issue comes with formulas ... both Excel and Libreoffice report some potential security error and remove formula. The behaviour is pretty strange .. sometime it apply to intermediate sum sometime on the latest. LibreOffice details it with error code 508 (missing parenthesis) but it's not the case the format is correct, also trough debugging _formula1 and _formula2 values are correctly set.

Here's a generated sample file:

generated excel file

P.S. Google Documents and Onedrive Office apps handle such file without errors.

I tried also adding:

_pck.Workbook.Calculate();

or 

_ws.Calculate();

Before Response.Clear() call without success.

I tried also to prepend = to formulas (removing .Calculate() calls), but I got the same error.

I noticed also that:

_ws.Cells[_ws.Dimension.Address].AutoFitColumns();

Do not affect all the cells.

-- UPDATE --

I found the cause (but not the solution).

The semicolon separator used between sum terms cannot be used in streams (why?!) . I need to find a different approach .

Answer 1

User475983607 posted

According to Excel support, the semi-colon or comma in the Sum() function is dependent on the client's region settings.  In the US, comma are used whereas other locals use the comma is a decimal separator.  

Try checking the client's culture settings and adjust your logic accordingly.  Otherwise see Excel support for assistance.

Answer 2

User-1939834628 posted

I see your point but the same Excel 'client' that's processing the downloaded file will accept semicolon if entered in the cell as a formula cell reference separator.

This's probably one of the formula localization troubles hiding in Excel... 

Anyway thanx to your hint I found the solution:

if (_subtotali.Length > 0)
    _subtotali.Append(",");

_subtotali.Append(string.Format("E{0}", _offset));

instead:

_subtotali.Append(string.Format("E{0};", _offset));

Same fixes needed for _importi variable.

Using comma fixes (and relocate automatically on the Excel client).