How to read Excel cells data types without Variant types 
Author Message
 How to read Excel cells data types without Variant types

My question is how or the best way to do this in VB.NET.

One of the problems of reading an Excel spreadsheet that has been submitted
by outside users it that the cell data types may not be consistent.  For
example if a column for PartNumbers is supposed to be a string for values
like ACC123R,  BRR345R,  or A-Very_big-Part happens to have a couple of rows
of part numbers 123,  0r 789 then I have read numeric types.

My solution has been to first read all the cells into a Variant, determine
the data type and then convert all numerics into strings before processing
the file.

Below is some example VB6 code that I use:

Set xlApp = GetObject("", "Excel.Application")
Set xlBook = xlApp.Workbooks.Open(fname)
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Unprotect

' loop thru cells checking for numeric types
For Row = 1 To NoRows
  For Col = 1 To NoCols
    v1 = xlSheet.Cells(Row, Col)
    If IsNumeric(v1) Then ' if numeric then add a single quote
      xlSheet.Cells(Row, Col) = "'" & v1
    End If
  Next Col
Next Row



Fri, 06 Aug 2004 20:09:43 GMT  
 How to read Excel cells data types without Variant types
Hello{*filter*},

Visual Basic.NET uses the COM object Libraries, so basically use very
similar technique to access the Excel Document as the COM wrapper is used
in VB.NET.
Instead of using GetObject you can use the NEW operator to create a Excel
Application object as is shown below.

Quote:
>>Below is some example VB6 code that I use:

>>Set xlApp = GetObject("", "Excel.Application")
>>Set xlBook = xlApp.Workbooks.Open(fname)
>>Set xlSheet = xlBook.Worksheets(1)
>>xlSheet.Unprotect

Dim objExcel As Excel.Application
Dim m_objOpt = System.Reflection.Missing.Value
Dim Books As Excel.Workbooks
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet

objExcel = New Excel.Application()
Books = objExcel.Workbooks
Book = Books.Open("C:\Test.xls")
Sheet = Book.Worksheets(1)
Sheet.Unprotect()

To Check the datatype...

Quote:
>>' loop thru cells checking for numeric types
>>For Row = 1 To NoRows
>>  For Col = 1 To NoCols
>>    v1 = xlSheet.Cells(Row, Col)
>>    If IsNumeric(v1) Then ' if numeric then add a single quote
>>      xlSheet.Cells(Row, Col) = "'" & v1
>>    End If
>>  Next Col
>>Next Row

I used a sheet with 4 rows and 2 columns

Data as shown
------------------------
A       B                        ' Data Type returned is a string
A2      B2                      ' Data Type returned is a string
3A      3B                      ' Data Type returned is a string
4       5                        ' Data Type returned is a double

For i = 0 To 1 ' Col
   For j = 1 To 4 ' Rows
      If IsNumeric(Sheet.Range(Chr(65 + i) & j, m_objOpt).Value) Then  
'Access Locations A1, A2, A3, A4, B1, B2, B3, B4
          MessageBox.Show("NUMERIC")
      Else
          MessageBox.Show("NOT NUMERIC")
      End If
        MessageBox.Show(eRange.Value.GetType().ToString) ' Display the Data
Type
  Next
Next

I hope this helps,

Heikki
Microsoft PSS

This posting is provided "AS IS" with no warranties, and confers no rights.
?2002 Microsoft Corporation. All rights reserved



Sat, 07 Aug 2004 01:03:47 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Q: variant data type with user defined data type

2. Prob. w\ TYPE data type: err:Type Mismatch

3. GetRows() Method and Variant data type

4. Variant data type

5. Get error: Disallowed implicit conversion from data type varchar to data type money

6. Data type conversion error when saving numeric data types

7. RDO and Variant data types

8. Variant Data Type variables -Can't print individually

9. Variant data types, VC++ and VB

10. RDO and Variant data types

11. Variant data types

12. C data type vs. VB Data type

 

 
Powered by phpBB® Forum Software