Query excel spreadsheet for cell format.... (percentage)

views:

155

answers:

Query excel spreadsheet for cell format.... (percentage)

I am using the .Net 4.0 and excel 2003

How can i use an oledb connection to retrieve the cell format of an excel spreadsheet... I specifically want to find out if a cell column (or cell itself) is in a numeric percentage format.

I cannot seem to find this information in the GetOleDbSchemaTable method.

EX: My web app reads numbers from an excel spreadsheet. This works fine; However, if the numbers are in a percentage format, excel displays it as (fraction*100) but the actual value is a fractional decimal (1/3 = .3333..) - Excel displays as 33.33% - (Notice the decimal point).

Therefore, i need a way of distinguishing between what is a percentage & what is not to allow my webapp to work properly...

Any ideas? Thanks in advance.....

You might be able to get out this information with OleDbConnection.GetSchema, but I'm not sure what information you'll get for an Excel sheet with that. Documentation here.

ho1 2010-06-22 15:32:39

No info there....

Guyanese4life 2010-06-22 20:09:49

@Guyanese4life: Well, it's fairly sparse, but it tells you that if you call that method you'll get out a DataTable with some data, that data might contain the info you need (it would if connected to a database but I've never tried it while connected to Excel).

ho1 2010-06-22 20:25:57

@Guyanese4life: Here's another link discussing how to use it against Excel: http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx

ho1 2010-06-22 20:28:15

Can you just read the first row of data as a string and parse it looking for '%' in the string.

Robin Robinson 2010-06-22 15:42:24

-1 judging from the op's question the cell value doesn't contain %. The % is formatting applied by Excel specified in cell formatting.

Graham Miller 2010-06-22 15:57:51

Correct! This seems true. The table schema information says that the datatype of the specified column is "5", which correlates to a double or float (http://www.w3schools.com/ado/ado_datatypes.asp); It does not give the excel cell(or column) format - which is a percentage

Guyanese4life 2010-06-22 16:11:00

Well that sucks. When you export an Excel worksheet to csv, the % sign comes with it. Actually what gets saved is exactly what you see on the screen, even down to the number if decimal places.

Robin Robinson 2010-06-29 17:58:02

Search the NumberFormat property of the Cell (Range) in question for a % sign.

Also, if you can get the format type, then you're looking for a format that starts with 'P', like 'P1'.

EDIT: The only way I can see is either using XML or Automation. For automation you need to use the Interop Assembly. Namespace: Microsoft.Office.Interop.Excel, Interface: DisplayFormat, Property: NumberFormat.

Lance Roberts 2010-06-22 17:26:01

There are no number format properties.. Keep in mind that this is being done by an oledb query.. I am NOT openning an instance of excel.

Guyanese4life 2010-06-22 17:43:25

@Guy, I think they're gettable, but you may have to use a different function. I've also edited in another way, but I also don't have the details on how to get that value. I'll try to do some research when I can, because I'm all curious now on how to get this done. Please update if you find the answer.

Lance Roberts 2010-06-23 22:23:55

@Guy, ok, more info. I realize that it's still not a complete answer, but hopefully it may lead you closer.

Lance Roberts 2010-07-01 23:38:30

ansaurus

tags:

views:

answers:

Query excel spreadsheet for cell format.... (percentage)

related questions