How do read excel percentage columns as a string to datatabel

Question

0.00/5 (No votes)

See more:

Hi Team,

I am converting an Excel file into a data table using

ExcelReaderFactory

. problem is percentage columns automatically convert into numbers. I need the exact format that I have In excel.

Assume below like Excel format

CYear	LYear
8.32%	19.43%
7.72%	6.54%

after converting into datatable, values
like below

CYear	LYear
0.0832	0.1943
0.0772 0.0654

but I need the exact values like below(I will replace % in c# before saving into the database)

CYear	LYear
8.32	19.43
7.72	6.54

Note: excel file columns will be dynamic. so we could not convert them to percentages manually by code since we don't know the column names.

Thanks in advance,
Vels

What I have tried:

var stream = File.Open(file.FullName, FileMode.Open, FileAccess.Read);
               var reader =  ExcelReaderFactory.CreateOpenXmlReader(stream);

               var input = reader.AsDataSet(new ExcelDataSetConfiguration()
               {
                   UseColumnDataType = false,

                   FilterSheet = (tableReader, sheetIndex) => tableReader.VisibleState == "visible",
                   ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                   {
                       UseHeaderRow = true,
                       FilterRow = (rowReader) =>
                       {
                           int progress = (int)Math.Ceiling((decimal)rowReader.Depth / (decimal)rowReader.RowCount * (decimal)100);
                           return true;
                       }
                   }
               });

Posted 4-Apr-23 21:29pm

avelsamy

Updated 5-Apr-23 14:05pm

Add a Solution

2 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)

OriginalGriff · Answer 1 · 2023-04-04T21:44:00

The values in Excel are the values you get: just that the columns (or cells) in the sheet have a format applied to them: "Percentage". You can test this by editing the sheet, highlighting the cells, and right clicking. Select "Format Cells" and you will see the display mode selected.

When you import the data to a DataTable, the data does not include presentation format, so you will need to manually multiply every value by 100 before you send it to the DB.

Don't convert it to a string to include the "%" character - if you do, the DB data will not be usable directly for any maths operations on other data.

Me? I'd leave it the same as the values from Excel, and apply percentage infor when I present it to the user again rather than modify any values specifically for the DB.

cmarcotte · Answer 2 · 2023-04-05T14:05:00

Hi,

If it is just a matter of presentation you can use String.Format() method.

String.Format Method (System) | Microsoft Learn[^]

But you can also query an Excel file directly without importing it in text format. I got this example (sorry it is in French) from Bing. It reads data from an Excel sheet and put it in a DataSet. (but i am not a C# guy.)

using System.Data;
using System.Data.OleDb;

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\myFolder\\myExcel2007file.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
string query = "SELECT * FROM [Sheet1$]";

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Dans cet exemple, le fichier Excel est situé dans le dossier C:\myFolder et s’appelle myExcel2007file.xlsx. La requête SQL sélectionne toutes les colonnes et toutes les lignes de la feuille Sheet1.

How do read excel percentage columns as a string to datatabel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0