ExcelAddressUtil.NameInvalidChars missing "\""

[angelaki]

EPPlus usage

Personal use

Environment

Windows

Epplus version

6.2.10

Spreadsheet application

Excel

Description

When using the LoadFromDataTable method on a dataTable that has a Quote (") in it's name, the method fails.

By the way, one more question: since I do not want to change my DataTable's name, how about passing an alternate name with the configuration (beside bool PrintHeaders, TableStyles? TableStyle)? So the DT's name doesn't need to change? I'd offer a PR, if requested :)

[OssianEPPlus]

Hi there!

I tried the following in epplus 6.2.10

    using (var package = new ExcelPackage())
    {
        var sheet = package.Workbook.Worksheets.Add("NewSheet");

        var table = new DataTable("A Table \"named\"");

        DataColumn column = new DataColumn();
        column.DataType = Type.GetType("System.Int32");
        column.ColumnName = "rowNr";
   
        table.Columns.Add(column);

        DataRow row;
        for (int i = 0; i <= 2; i++)
        {
            row = table.NewRow();
            row["rowNr"] = i;
            table.Rows.Add(row);
        }

        sheet.Cells["A1:B10"].LoadFromDataTable(table);

        package.SaveAs("C:\\temp\\QuoteTable.xlsx");
    }

And was unable to reproduce the issue as it functioned as expected. Perhaps I assumed the way you're calling LoadFromDatatable wrong?

Could you provide a reproducible sample so we can see what's different? Are you perhaps trying to import the whole table including headers into an excelTable? Quote (") is not allowed for the names of tables in excel. An alternate name in the same configuration could be one potential solution for that sure.

Also thanks for offering the PR! But we prefer to resolve things internally.

[angelaki]

Yeah, you're almost right - the problem only occurs if i set a Table style:

using (var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("NewSheet");

    var table = new DataTable("A Table \"named\"");

    DataColumn column = new DataColumn();
    column.DataType = Type.GetType("System.Int32");
    column.ColumnName = "rowNr";

    table.Columns.Add(column);

    DataRow row;
    for (int i = 0; i <= 2; i++)
    {
        row = table.NewRow();
        row["rowNr"] = i;
        table.Rows.Add(row);
    }

    sheet.Cells["A1:B10"].LoadFromDataTable(table, false, OfficeOpenXml.Table.TableStyles.Dark1);
}

And ensuring a correct name via new DataTable(ExcelAddressUtil.GetValidName("A Table \"named\"")); works for all characters except " - that one seams to be missing in ExcelAddressUtil.NameInvalidChars.

(By the way: your ExcelAddressUtil.SheetNameInvalidChars misses '[], too).

Why is the table name even relevant? It is not used for the output, is it? Since I may not change my object names, I edit a copy of them right now (dataTable = dataTable.Copy();) what could be quite slow in some scenarios. Sure I could also change the name forth and back. But if the table name actually is required: an alternate name in the parameters would be handy. Thank you!

[OssianEPPlus]

The problem appears when you set a tableStyle because when you do Epplus creates an ExcelTable in the worksheet. This as the intended behaviour is if you supply a tableStyle you want a table with that style to appear in Excel. And a table must have a name and as such uses the datatable's name and therefore is relevant for the output.

However throwing because of it is unintended behaviour. We've created a fix now that will generate a new basic table name (in this example Table1) if the dataTable name is invalid. It'll be in our next release most likely next week.

" Does seem to be missing. That char is not allowed in Named Ranges either which the util is also used for. We'll look into that. Could be an edge-case where we need to allow it or have previously but its no longer relevant.

as for ExcelAddressUtil.SheetNameInvalidChars the ' character is allowed in excel sheet names and so correctly not in there. Good catch on [] though. That should most likely be in there, we'll look into it.

[angelaki]

At least the German Excel version doesn't block you from typing a ', but saving it gives me an error. Thank you for checking!

[angelaki]

Just checked it even more: ' is allowed but musn't be the first / last character.

Edit: and now I saw you're already checking this with an exception. Everything's fine :)