Creating Excel Files with # in Column Name

Question

I'm having problem creating Excel files using Jet. When I create a table and give it a Column name as

CreateTable [Sheet1] ([ColumnName#] String)

It replaces the header column with

ColumnName.

Is there a way I can make excel give the column headers a name with out any conflict in what characters I can have in it? Are there any escape characters that I can use in the column names?

Is there a cheap(~$50)/free .NET library that would give me better control over the Excel file that would allow me to create both XLS and XLSX files with out having excel installed?

Basically what I'm trying to accomplish is having a DataTable get dumped into an Excel File and have the Column names appear just as they do in the in the DateTable.

Answer 1

Use SpreadSheet XML (XMLSS). Lots of support for it including numerous sample code, documentation, and libraries. The best feature is easy to use.

Answer 2

Have you tried NPOI?

NPOI