Automated Excel sheet with too long sheetname and oledb problem

Question

0.00/5 (No votes)

See more:

Hi,

i got a problem with a machine generated excel file. I have no chance to alter any settings for the export and have to live with the files.

The problem: the sheetname is longer than the allowed size of excel. When i try to import the excel data via oledb i get an error 22 exception of the oledbdataprovider.
when i cut the sheetname to 30 chars (like the visible sheetname in excel), oledb does not found the sheet. If i rename the sheetname in excel and save the file, i can run the import with oledb flawlessly.

i cant use excel as i need to write a service that runs on a server and runs imports / calculations with no excel installed on the machine.

Any ideas if its possible to read or import that files from C#? Maybe its possible to manipulate the sheetname? The files are plain old xls files btw. So no xlsx with xml or whatever.

thanks and any help is appreciated..

Bjoern

Posted 4-Oct-11 3:10am

bjoernisemann

Add a Solution

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)

ARBebopKid · Answer 1 · 2011-10-04T05:03:00

Solution 1

Can you open the file using Microsoft.Office.Interop.Excel and cycle through the sheets to rename any sheets with long names before importing?

Posted 4-Oct-11 5:03am

ARBebopKid

Comments

Simon Bang Terkildsen 4-Oct-11 12:29pm

OP wrote:
Hi, well thats exactly what I do not want to do: interop. The software must run on a servermachine without having office installed. Of course it would be possible to do it that way but it is the "worst case" scenario if i did not find an "officeless" ;-) solution.

The scenario is the following: We have a machine which runs in the laboratory that creates these excel files with statistics. I need to program a solution that runs as service and automatically imports these excel files to our oracle-database without any userinteraction. My idea: file- or directorywatcher and the import- and parse- functions as service on one of our applicationservers without office ;-)

Simon Bang Terkildsen 4-Oct-11 12:35pm

I understand very well that you do not want to use office interop, has been a source of much headache for me, and the need for excel to be installed is a drawback and it's not possible to do with a "clean" windows service, as you cannot use office interop in a windows service like you cannot use any UI.

However I think it's your only option if you cannot control how the excel files, specifically the naming of the worksheets.

Simon Bang Terkildsen 4-Oct-11 12:35pm

My 5 for this suggestion

ARBebopKid 4-Oct-11 16:07pm

I found this non-microsoft tool that might work: http://code.google.com/p/npoi/

There may be others as well.