Having a problem creating table in excel with OLEDB in C#

Question

0.00/5 (No votes)

See more:

I'm extending written by an contract developer (who is no longer accessible) to create and write tables of data to Excel worksheets successfully but with one hitch - when the table name has an ending digit, the worksheet names are prepended with an underscore (e.g., "_SAT1"). The code writes over 10K records with 25 columns of data to Excel accurately (and quickly!).

Most of the table names are static so the initial phrase of the create statement is specified as in:

C#

sqlCmd = "create table SVR (" + sqlColumns + ")";

When the table name has a trailing digit, an underscore appears in the worksheet name. when the table name is represented by a string variable or a constant - the reason being the table name needs to include an integer like my example above.

Happy to post more of the code or answer questions. Just be warned however, I'm a FORTRAN programmer swimming in the deep end of the C# pool so....

Ted

What I have tried:

I've tried the following permutations:

C#

sqlCmd = "create table " + sheetName.Trim() + " (" + sqlColumns + ")";

Where

C#

sheetName.Trim() == "SAT1"

Other trys:

C#

sqlCmd = "create table [" + sheetName.Trim() + "] (" + sqlColumns + ")";
sqlCmd = "create table [SAT1] (" + sqlColumns + ")";

All result in a worksheet name of "_SAT1".

Posted 17-Jul-24 11:49am

tlillys

Add a Solution

Comments

PIEBALDconsult 3 days ago

For the most part, I write CSV rather than directly to Excel.

Having said that, I have written directly to Excel, but it can be problematic.

One thing to bear in mind, though it might not matter to what you're doing, is that a worksheet may contain any number of logical tables, there is no 1:1 relationship between them.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2024-07-17T17:52:00

Unfortunately, this appears to be a well known issue with the driver you are using. There are numerous accounts, on the internet, of people facing this issue. Rather than using a number, you could use the text representation; so you would write Sheet Two, instead of Sheet 2.

If you can't do this, you are left with these choices as far as I can see

Live with the fact that the sheet name starts with an underscore
Use a library to write the data into the spreadsheet instead of the OLEDB driver, which could end up being quite a rewrite
Use a library to open up the spreadsheet and rewrite the sheet name to remove the leading underscore.

[EDIT]I have just seen a suggestion to change the connection string to use the IMEX settings as this may control how the name is created.

C#

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";