1

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.

swcraft
  • 2,014
  • 3
  • 22
  • 43

1 Answers1

1

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);
}
user8728340
  • 646
  • 5
  • 7
  • Thanks for your answer. I worked on their source code to add custom dateTime formatter and it seems doing the job for now. Probably I will look into the ExcelnumberFormat library as well. However another issue I am trying to solve is my work around or your answer converts excel value into string. It would be nice not to lose the thorough culture info excel file has for each data. Please take a look at this link for further discussion: https://stackoverflow.com/questions/61559823/c-sharp-object-containing-datetime-and-specific-cultureinfo – swcraft May 02 '20 at 13:47