Click here to Skip to main content
15,921,941 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have tried the code mentioned below but I am getting the Actual values from the sheet, not the formatted one. Please help...


Name	Email	        Phone	  Amount (formatted cell value)	Amount(Actual cell vlaue)
Bob	555-2233  1,000.05	                1000.0545
Carol	555-3344  10,000.05	                10000.0545
Ted	555-4455  250,000.05	                250000.0546
Alice	01245	  1,080.60	                1080.599898

What I have tried:

var fileName = Server.MapPath("~/Users.xlsx");
     DataTable dtNew = new DataTable();

     using (var stream = System.IO.File.Open(fileName, FileMode.Open, FileAccess.Read))
         using (var reader = ExcelReaderFactory.CreateReader(stream))
             int ColumnCount = reader.FieldCount;

             for (int i = 0; i < ColumnCount; i++)
                 string ColumnName = reader.GetValue(i).ToString();
                 if (!dtNew.Columns.Contains(ColumnName))
                    { dtNew.Columns.Add(ColumnName); }

             while (reader.Read())
                 foreach (DataColumn dc in dtNew.Columns)
                     int index = dtNew.Columns.IndexOf(dc);
                     dtNew.Rows[dtNew.Rows.Count - 1][dc] = reader.GetValue(index).ToString();

Updated 21-Aug-22 1:15am
[no name] 8-Jan-21 1:53am    
Format it yourself; it's an "N2".

DataTable stores data, not it's format. If you would like to display data existing in the datatable object you need to use reporting tools.
Share this answer
The GitHub project you're using explains how to handle formatting:
ExcelDataReader does not support formatting directly. Users may retreive the number format string for a cell through IExcelDataReader.GetNumberFormatString(i) and use the third party ExcelNumberFormat library[^] for formatting purposes.
Share this answer
Maciej Los 8-Jan-21 8:58am    
Hi, Richard!
Happy New Year!

OP loads data into datatable object. So, as i mentioned, datatable stores data, not it's format.
Richard Deeming 8-Jan-21 9:00am    
The OP seems to be storing strings in the datatable, so I suspect they just want to store the formatted value.

Whether that's a good idea or not is a different issue. :)

Install below NuGet package "NuGet Gallery | ExcelNumberFormat 1.1.0[^]

private static string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
           var value = reader.GetValue(columnIndex);
           var formatString = reader.GetNumberFormatString(columnIndex);
           if (formatString == null)
               return Convert.ToString(value, culture);

           var format = new NumberFormat(formatString);
           return format.Format(value, culture);

while (reader.Read())
                    foreach (DataColumn dataColumn in dataTable.Columns)
                        var index = dataTable.Columns.IndexOf(dataColumn);
                        var value = GetFormattedValue(reader, index, CultureInfo.CurrentCulture);
                        dataTable.Rows[dataTable.Rows.Count - 1][dataColumn] = value;
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900