How do I read formatted text from Excel into vb.net?

BOIT
BOIT used Ask the Experts™
on
I am trying to read an Excel worksheet into my application, where the data will be manipulated in a database setting.

The code I am using is:

Dim x As Object = CreateObject("Excel.application")
x.Workbooks.Open(Filename)
Dim t as object = x.ActiveWorkbook.Sheets(1).cells(rowno, cellno).value

The problem is that in the case of formatted text (containing bold, underline etc) only the text string is retrieved. How do I read the formatting code from Excel?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Suppose, even if you were able to extract the formatting information from excel, how will you store it in database?

Author

Commented:
I've got some VBA code I previously used when reading Excel 2003 into Access. It went through the Excel formatting characters and converted them. I am planning to adjust this but use the same principle.

1. Read from Excel
2. Change to RTF format (suitable for .net richtextbox)
3. Save with formatting to SQL Server.

Unfortunately, I've hit problems with step 1. I can't work out how to read the formatting from Excel!

I'll consider a different approach if anyone can suggest one?
Commented:
I have worked out that you can read the formatting of an Excel cell via automation (the Excel macro recorder is a wonderful thing)

cells(row,column).characters is a collection of the characters of text in the cell

cells(row,column).characters.count is the total number of characters

cells(row,column).characters(1,1).font is font of first character
cells(row,column).charcaters(2,1).font is font of second character etc.

I am thinking that I can iterate through the characters, pick up font changes and write the data to a richtextbox.

If anyone has a better solution I will accept it (this is a bit cumbersome) otherwise I will accept this as the solution.
You can also try to save the sheet in csv (first paste your data in a clean sheet, by using a special paste, specifying: formatted values).
When you read each line of the cvs file, you can immediately put it in a datarow...
Hope this helps

Author

Commented:
The CSV format removes all text formatting. My application requires bold, underline, italic and strikethro at least to be preserved from Excel.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial