How to read excel data with formatted values in C#...

Question

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 7-Jan-21 14:07pm

Member 15040325

Updated 21-Aug-22 1:15am

Add a Solution

Comments

[no name] 8-Jan-21 1:53am

Format it yourself; it's an "N2".

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2021-01-07T20:02:00

Solution 1

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.

Posted 7-Jan-21 20:02pm

Maciej Los

Richard Deeming · Answer 2 · 2021-01-07T22:50:00

Solution 4

The GitHub project you're using explains how to handle formatting:

GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files[^]:
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.

Posted 7-Jan-21 22:50pm

Richard Deeming

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. :)

velkumar_in · Answer 3 · 2022-08-21T01:15:00

Hi,

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())
                {
                    dataTable.Rows.Add();
                    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;
                    }
                }