asked on

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

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?

kishoreb123

Suppose, even if you were able to extract the formatting information from excel, how will you store it in database?

BOIT

ASKER

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?

ASKER CERTIFIED SOLUTION

BOIT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Giuseppe Pizzuto

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

BOIT

ASKER

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