Cant Read Cells Contain '$' from Excel

Question

0.00/5 (No votes)

See more:

, +

I am Using this code to read from excel but there are some cells{D2 , D4 }the prog . cant read them from the excel file which contain ('$') {D2 , D4} any help to read this cells ..

try
            {
                OpenFileDialog ex = new OpenFileDialog();
                ex.Filter = "XLS|*.xls|All|*.*";
                if (ex.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    try
                    {
                        var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", ex.FileName);
                        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet7$]", connectionString);
                        var ds = new DataSet();
                        adapter.Fill(ds);
                        DataTable data = ds.Tables[0];
                        DATAGRID1.DataSource = data;
                    }

So how to get this values as shown in the picture

http://postimg.org/image/xeanxst5v/[^]

Posted 8-Feb-15 1:37am

Dev_Fady...

Add a Solution

Comments

Kornfeld Eliyahu Peter 8-Feb-15 7:42am

What do you mean by "can't read"? Do you got a different value than expected, or got some error?

Dev_Fady... 8-Feb-15 8:10am

check this photo and you will understand what i mean
http://postimg.org/image/xeanxst5v/

Kornfeld Eliyahu Peter 8-Feb-15 8:12am

I can't...Can you explain it in words?

Dev_Fady... 8-Feb-15 9:10am

sure
i have a column with this values on cells
100
105 $
110 $
200
when i use the above code
the returen is as
100

200
all cells with $ don't appears
so can help me to solve it ...

Kornfeld Eliyahu Peter 8-Feb-15 9:11am

At last I was able to see the image...See my answer...

[no name] 8-Feb-15 7:57am

Try to Google for "oledb Excel read formatted cells".

Dev_Fady... 8-Feb-15 8:10am

but this is not a different format !!!

[no name] 8-Feb-15 8:17am

There is a value in Excel and there is a Format for the cell how Excel will Display the value...that I assume is the Problem you have.
The cell data is e.g. int, "99".
The cell's Display Format is "#### $".

A simple test to confirm whether this is the problem, remove cell's format and try again to read.

See also the answer of Richard.

2 solutions

Add a Solution

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

Kornfeld Eliyahu Peter · Answer 1 · 2015-02-08T02:28:00

Solution 2

Finally I was able to see the image you posted (better explain it by words), and understood that some of the values (those with $ in it) return as empty...
The problem is that Excel has no data type, so the OELDB driver have to decide what datatype to assign to every column upon reading it...
In case of mixed types the driver will decide using a look-ahead method, where it will check a certain number of rows and will decide according the majority - in your case the driver decided that this column is numeric and wiped out any non-numeric values...
You can change it by adding some extended attributes to the connection string...

C#

Extended Properties=\"HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"

This will tell the driver to read the first row as header-text, do not do any type guessing and use Text as type for mixed columns...
There is more info about Excel connection here: https://www.connectionstrings.com/excel/[^]

Posted 8-Feb-15 2:28am

Kornfeld Eliyahu Peter

Comments

Richard MacCutchan 8-Feb-15 9:31am

Sorry, but that will not work, for the reasons I outlined in my answer. The contents of any non-text cell (numbers, dates etc) are stored as numeric values, In order to display them in a particular format an editing template has to be attached to the cell. But that is not used by the Jet driver, it just extracts the cell contents.

Kornfeld Eliyahu Peter 8-Feb-15 9:36am

According to the image and the clarifications of OP it is a classic case of mixed types...One cell has the value of 1000 while the other has the value of 1000$ (no formatting but actually the $ sign included)...

Richard MacCutchan 8-Feb-15 9:43am

Makes no difference what the OP posts, I suspect that in the latter case he has changed the cells to text strings, rather than formatted numbers. It's easy enough to demonstrate.

Dev_Fady... 9-Feb-15 2:16am

Kornfeld Eliyahu Peter
Really Thanks For your Support and sorry for make a more effort when you try to read the photo but ... when i try your solution this error appears to me .
"Format of the initialization string does not conform to specification starting at index 96."
so can support me why this error .... :(
?!!

Kornfeld Eliyahu Peter 9-Feb-15 2:17am

Use double-double-quote ("") instead of \"...

Dev_Fady... 9-Feb-15 2:25am

Do you mean that the connection string will appear as
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text"", ex.FileName);
but there are 6 errors :O

Kornfeld Eliyahu Peter 9-Feb-15 2:42am

Do the errors about the quotes?
Please read this: http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

Richard MacCutchan · Answer 2 · 2015-02-08T01:56:00

Solution 1

Most likely because what you see in Excel is the display format of a numeric value. When you read a cell's value you just get the number that is stored inside it. To get the formatting information you may need to use the Excel.Interop.

Posted 8-Feb-15 1:56am

Richard MacCutchan

Comments

Kornfeld Eliyahu Peter 8-Feb-15 8:29am

I think that's not the problem...Please see my answer...

Dev_Fady... 8-Feb-15 9:01am

Richard MacCutchan
First thanks for you support but in other example
check this photo
http://postimg.org/image/5u96d3d11/
when i change all column values the reader read all of the so i cant under stand it

Richard MacCutchan 8-Feb-15 9:32am

What do you mena by "change all column values"?

Dev_Fady... 9-Feb-15 1:54am

i mean that when i make the columns values
100 $
200 $
300 $
400 $
the program read them as they in excel
100 $
200 $
300 $
400 $

Dev_Fady... 9-Feb-15 2:28am

i dont know more about the Excel.Interop
can you send me what the edit in code i need to achieve this problem ...

Richard MacCutchan 9-Feb-15 3:30am

See https://msdn.microsoft.com/en-us/library/Microsoft.Office.Interop.Excel.aspx.

Cant Read Cells Contain '$' from Excel

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Cant Read Cells Contain '$' from Excel

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us