Click here to Skip to main content
14,937,843 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...

e.g.

Name	Email	        Phone	  Amount (formatted cell value)	Amount(Actual cell vlaue)
Bob	bob@abc.com	555-2233  1,000.05	                1000.0545
Carol	carol@abc.com	555-3344  10,000.05	                10000.0545
Ted	ted@thg.com	555-4455  250,000.05	                250000.0546
Alice	alice@abc.com	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;

             reader.Read();
             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())
             {
                 dtNew.Rows.Add();
                 foreach (DataColumn dc in dtNew.Columns)
                 {
                     int index = dtNew.Columns.IndexOf(dc);
                     dtNew.Rows[dtNew.Rows.Count - 1][dc] = reader.GetValue(index).ToString();
                 }
             }
         }
     }

     dtNew.AcceptChanges();
Posted
Updated 7-Jan-21 22:50pm
Comments
Gerry Schmitz 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.
   
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.
   
Comments
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. :)

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