Excel like Columns names

[wahmedswl]

Hi,

Currently column names are index based but it should default to Excel like column name eg: A-Z, AA-AZ ... as these are more intuitive and symmetrical to Excel.

Also, it would be great to skip adding index based columns when Header is already present as it duplicates the whole data.

Thanks

[mganss]

Re column names: Do you have an idea what the API should look like considering we already have the Name property for the ColumnAttribute along with the corresponding constructor? Perhaps use an IsLetter property so you can say e.g.[Column("A", IsLetter = true)]?

I don't understand the second point. Can you open a separate issue and add a snippet of demo code?

[wahmedswl]

I guess you are talking about typed mapping, where you can add attributes. I was talking about Dynamic mapping via Fetch that returns ExpandoObject. In case of that, returned EO has both index and name key added so it duplicates stuff.

[wahmedswl]

Also, it would be great to add Excel like columns rather index based as that feels natural in ExpandoObject.

[mganss]

So how about removing integer indexes as keys for the ExpandoObject altogether and use letters as names if there's no header row? In other words, if you have a header row you can say o.Name and if you don't, you can say o.A?

[wahmedswl]

yes, but from where o.A will come? Index mapped to Excel like name?

Thanks

[mganss]

yes, but from where o.A will come? Index mapped to Excel like name?

Yes, exactly.

[wahmedswl]

Hi, i have updated DynamicColumnInfo temporarily to hook up this functionality but it doesn't seems clean. Because i have to update the field from ExcelMapper before https://github.com/mganss/ExcelMapper/blob/master/ExcelMapper/ExcelMapper.cs#L443

like this:

if (type == null)
{
    var dynamicCellInfo = (DynamicColumnInfo)ci;
    dynamicCellInfo.SkipIndex = SkipIndexColumns;
    dynamicCellInfo.ExcelColumnNames = ExcelColumnNames;
}

There is rough implementation to generate Excel like name

internal static string ExcelColumnName(int columnIndex)
{
    //  eg  (0) should return "A"
    //      (1) should return "B"
    //      (25) should return "Z"
    //      (26) should return "AA"
    //      (27) should return "AB"
    //      ..etc..
    char firstChar;
    char secondChar;
    char thirdChar;

    if (columnIndex < 26)
    {
        return ((char)('A' + columnIndex)).ToString();
    }

    if (columnIndex < 702)
    {
        firstChar = (char)('A' + (columnIndex / 26) - 1);
        secondChar = (char)('A' + (columnIndex % 26));

        return string.Format("{0}{1}", firstChar, secondChar);
    }

    int firstInt = columnIndex / 676;
    int secondInt = (columnIndex % 676) / 26;
    if (secondInt == 0)
    {
        secondInt = 26;
        firstInt = firstInt - 1;
    }

    int thirdInt = (columnIndex % 26);
    firstChar = (char)('A' + firstInt - 1);
    secondChar = (char)('A' + secondInt - 1);
    thirdChar = (char)('A' + thirdInt);

    return string.Format("{0}{1}{2}", firstChar, secondChar, thirdChar);
}

I like that you take a look to better integrate it. Thanks

[mganss]

There's a builtin method for letter to index conversion in NPOI: CellReference.ConvertColStringToIndex (and CellReference.ConvertNumToColString for the reverse).

The problem with removing the integer index properties is that they were used for mapping to the correct columns when saving. I have moved the index mapping to its own property called __indexes__ which is a Dictionary<string, int> that maps property names to column indexes. So you'll still have an extra metadata property but not twice as many properties.

If you specify HeaderRow = false the properties will have column letter names.

If you save dynamic objects that have no index map property and all properties have one- or two-letter column names they'll be automatically mapped to the corresponding column index.