Microsft Jet oledb connection to excel loses formulae

Question

I have a dataset and I write it's values to an excel file using Microsoft Jet 4.0 Oledb provider. I want to put hyperlinks into the excel, for this I write "=HYPERLINK("http://www.abc.org/x.pdf")" in DS and then write it to excel. But when the excel is opened it prefixes a ' (quote/apostrophe) before the formula and there it comes as a text, not link. Can you help me removing that quote so that my excel file has hyperlinks instead of that as text?

I need to use Microsoft Jet 4.0 provider (not excel component) as the client machine may or may not have MS-Excel installed on his machine.

Answer 1

Jet will provide data integration with Excel. You can read/write data. Not formulas or formatting or anything else.

To do what you want you'll need to output formatted data. You can do this by generating an HTML file and telling the client it's Excel which will work fine but has minor side effects. A better option would be to use an actual Excel binary writer. A lot of options here:

http://stackoverflow.com/questions/444522/c-asp-net-import-and-export-excel-what-is-the-best-library