Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
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
Updated 5-Apr-23 14:05pm

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.
 
Share this answer
 
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.
 
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