CODEWITHSUNDEEP
c#excelexceldatareader
swcraft
swcraft

Reputation: 2112

how to keep the datetimeformat of excel.cell into dataset cell

Having some hard time understanding the conversion between excel file to .net object type such as DataSet or DataTable.

One issue I see in this 3rd party library (and the issue still lingers in the latest I believe) called ExcelDataReader is its conversion of datetime format.

For example the excel file where user set custom format of dd/mm/yyyy gets converted in the result of dataSet as mm/dd/yyyy after importing the excel file to the object done.

I was thinking maybe to fix this, the library code needs to passively receive the excel cell format settings, not trying to convert with its own settings.

Wonder how in the code it can be done such that 

if (excelfile.row[somenumber].cell.TypeSetting == typesetting.dateTime)
{ 
   dataset.cell[somenumber].dateTimeFormatSetting = excelfile.row[somenumber].cell.dateTimeFormatSetting
}

probably this code isn't close to be realistic, just a wild guess of what needs to be done.

Upvotes: 1

Views: 1496

Answers (1)

user8728340
user8728340

Reputation: 676

ExcelDataReader loses Excel's formatting information when using AsDataSet(), which basically gives you only the raw values.

F.ex a raw date object alone does not know whether it should be displayed as 'mm-dd-yy' or 'dd/mm/yy'. For this you need the "number format string", which is ignored by AsDataSet().

To work around this, instead of calling AsDataSet(), you need to manually loop over the rows/columns using ExcelDataReader's lower level reader APIs, and using the third party library ExcelNumberFormat to format the values like Excel.

This relevant snippet from their readme shows how to format a single value from the reader object:

string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
{
    var value = reader.GetValue(columnIndex);
    var formatString = reader.GetNumberFormatString(columnIndex);
    if (formatString != null)
    {
        var format = new NumberFormat(formatString);
        return format.Format(value, culture);
    }
    return Convert.ToString(value, culture);
}

Upvotes: 1

Related Questions