Search code examples
c#exceldateepplus

How can I prevent Excel from converting values to date format?

Recently (nobody noticed it happening before, if it did) some "ID"-type values are being converted by Excel to dates. There is some logic to which values are being converted, as you can see here, where the user added a column to show what the underlying values really are, and how they should be represented (raw, no conversion):

enter image description here

So because there's a "-" after "01" Excel is thinking that "01-" should be January, an assuming the final two characters represent the year.

In Item codes that contain no dash, they are left alone. How can I prevent Excel from being "helpful" in this way and converting these values to dates?

UPDATE

In response to Scott Craner's comment, this is the code I have to write out that value:

using (var memberItemCodeCell = priceComplianceWorksheet.Cells[rowToPopulate, DETAIL_MEMBERITEMCODE_COL])
{
    memberItemCodeCell.Style.Font.Size = DATA_FONT_SIZE;
    memberItemCodeCell.Value = _memberItemCode;
    memberItemCodeCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}

...and when I try to figure out how to format the value as Text or General by typing "for" after the cells name, I see these options:

enter image description here

So what do I need to use - FormatedText (sic) or ConditionalFormatting, and how specifically do I set those up to format the column as Text or General, and which if any of these two are preferred?

Solution

  • What ended up working for me was using this technique to assign the proper data type to the value in the cell (with the call to ConvertValueToAppropriateTypeAndAssign()) and then formatting as necessary after the fact:

    public static readonly string NUMBER_FORMAT_CURRENCY = "$#,##0.00;($#,##0.00)";
public static readonly string NUMBER_FORMAT_THOUSANDS = "#,##0";
public static readonly string PERCENTAGE_FORMAT = "0.00%;[Red]-0.00%";
public static readonly string NUMBER_FORMAT_TEXT = "@";
public static readonly string NUMBER_FORMAT_DOUBLE = "0.00"; 

. . .

using (var percentageCell = priceComplianceWorksheet.Cells[rowToPopulate, SUMMARY_PERCENTAGE_COL])
{
    ConvertValueToAppropriateTypeAndAssign(percentageCell, totalPercentage);
    percentageCell.Style.Numberformat.Format = PERCENTAGE_FORMAT;
}

. . .

// Adapted from https://stackoverflow.com/questions/26483496/is-it-possible-to-ignore-excel-warnings-when-generating-spreadsheets-using-epplu
public static void ConvertValueToAppropriateTypeAndAssign(this ExcelRangeBase range, object value)
{
    string strVal = value.ToString();
    if (!String.IsNullOrEmpty(strVal))
    {
        decimal decVal;
        double dVal;
        int iVal;

        if (decimal.TryParse(strVal, out decVal))
            range.Value = decVal;
        if (double.TryParse(strVal, out dVal))
            range.Value = dVal;
        else if (Int32.TryParse(strVal, out iVal))
            range.Value = iVal;
        else
            range.Value = strVal;
    }
    else
        range.Value = null;
}