Click here to Skip to main content
15,844,602 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

Can you open the file using Microsoft.Office.Interop.Excel and cycle through the sheets to rename any sheets with long names before importing?
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900