|
Hi there,
I'm trying to read .xls file using OleDb connection. I have two .xls files generated from SAP (actually these are Unicode text files saved as .xls) with two different names. When I execute my code I can only open one file, the second one gives an error:
System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'sheetName$'. Make sure the object exists and that you spell its name and the path name correctly.
Both files contains only one sheet which has the same name as the file itself. I've checked the spelling several times and even changed it manually but it doesn't work on one file. Even when I saved the file as proper Excel file. Here's the code I'm using:
string fileNameWithExt;
string fileNameWithoutExt;
int fileNameLength = 0;
int cutExtension = 4;
fileNameWithExt = openFileDialog1.SafeFileName;
fileNameLength = fileNameWithExt.Length;
fileNameWithoutExt = fileNameWithExt.Remove(fileNameLength - cutExtension);
OleDbConnection aConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\" + fileNameWithExt + ";Extended Properties=Excel 8.0");
aConnection.Open();
DataTable sampleDataTable = new DataTable();
dataGridView1.DataSource = null;
sampleDataTable.Clear();
sampleDataTable.Columns.Clear();
OleDbDataAdapter oleDbCommand = new OleDbDataAdapter("Select * from ["+fileNameWithoutExt+"$]", aConnection);
oleDbCommand.Fill(sampleDataTable);
I hope somebody can point me in the right direction as I checked plenty of posts and know that something is wrong with the sheet name but can find the issue.
Regards,
|
|
|
|
|
some questions:
1. are you having both xls files open at the same time?
2. are you closing aConnection when done with each file?
3. what is the full and exact content of fileNameWithExt in both cases?
4. in exactly which line does the exception occur?
|
|
|
|
|
Hi,
1. No, just one file is being opened at the time
2. No, I'm not closing the connection I am a newbie, so I'm just learning but I think it's not the case as when I run my code for the first time and trying to open the "failing" file it can't be opened anyway
3. Full content of the fileNameWithExt contains a file name with extension i.e. someXlsFile.xls. As you can see in the code it use openFileDialog property SafeFileName:
fileNameWithExt = openFileDialog1.SafeFileName;
My files names are: wipasianew.xls (can be opened) and wipasia.xls (cannot be opened)
4. Exception occurs in this line:
oleDbCommand.Fill(sampleDataTable);
Hope you'll have any ideas. Thanks, Darius
|
|
|
|
|
Hi,
my guess is you have a problem in path names, as I already hinted at in 3. what is the full and exact content of fileNameWithExt in both cases? which you did not answer accurately.
I ran a successful test with a full file spec in the connection string (and no constant C:\ ).
|
|
|
|
|
Hi Luc,
I have copied the file which I'm able to open to several locations on my hard drive (like c drive, my documents etc.) and the program opened the file without any problems, however fileNameWithExt uses openFileDialog SafeFileName property which doesn't contain the path I hope now I answered your question
In your trial have you used your own code?
Cheers, Darius
|
|
|
|
|
Hi again,
I just changed the code and tried it. But now I'm getting an exception "System.Data.OleDb.OleDbException: External table is not in the expected format". It's in the line which opens an OleDb connection.
Could you please advise on this one?
Regards, D
|
|
|
|
|
It's me again. I think the problem is that my files are Unicode text with .xls extension. Is there any way to open such files?
Darius
|
|
|
|
|
Hi,
1.
if you reply to yourself, you get an e-mail notification and I don't.
2.
who would be so smart as to create text files with .xls extension?
those cannot be opened by database code at all.
assuming you have Excel installed, if double-clicking fails, so will your app.
|
|
|
|
|
Hi,
1. Ok, didn't know
2. As I've written in my first post these are files generated by SAP application. I'm trying to automate what I'm doing at my job by my application that's why I want to use those. Actually Excel recognizes these files and opens them normally, but when you trying to save the file then you are asked whether you want to save the file as Unicode text (but with .xls extension) and then you get a message that some features may not be supported. Such files can be opened in OpenOffice as well as I tried it.
Any advice?
Regards, Darius
|
|
|
|
|
I have a windows form. I set the "window state" property to "maximize". But in this form i have 2 controls: a picture box and a windows media player tool. They are placed side by side.
How can i set the height and width of the picture box tool and windows media player tool, so tat their size (width and height)is 1/2 the size of the form, after the form is maximized....
plz help------
|
|
|
|
|
Ever thought of working for NASA? Because this is ground breaking stuff.
Here's a little hint:
this.pictureBox1.Width = this.ClientSize.Width / 2;
and
this.Resize += new EventHandler(ResizeForm);<br />
private void ResizeForm(object obj, EventArgs e)
My failometer is detecting vast quantities of FAIL!
|
|
|
|
|
A second grade astronaut might consider a TableLayoutPanel, I haven't used it so far.
|
|
|
|
|
hi everyone iam a C# programmer iam not profrssional
i want to make a program like MS paint , i will use C#
i need this program
i tried to make but when i draw any thing on the picture box all what i drawed cleared when i move the form
please reply me
thanks
Mohammed Mahmoud
|
|
|
|
|
Hi,
A PictureBox is just good enough for displaying an image, no more, no less.
If you want to draw onto something, chose a Panel.
There are several CP articles about Paint apps; and there is paint.net[^]
|
|
|
|
|
thanks mr Luc Pattyn
iam use a panel too, but again when i drawing any thing and move the form or when i minimize it the drawing is clear
please excuse me my english langauge is not good
thanks and i wish u reply me
|
|
|
|
|
Here is my standard advise on painting:
there are several steps to correctly draw something so it becomes visible on the screen:
1.
decide upon what object you want to draw; it normally is a Control (e.g. a Panel) or a
Form itself. I prefer to add a Panel to a Form, then draw on the Panel.
2.
create some variables (Rectangle, struct, class, whatever) that hold the parameters of
your drawing. For a rectangle that could be top and left coordinate, and width+height,
or just a Rectangle. etc.
3.
create a Paint handler (either add your own paint handler to the Paint event, or
override the OnPaint method) for that Panel, and do all your drawing in there,
using the Graphics class and your variables.
4.
when you want to change things, modify the variables and call Panel.Invalidate() or
one of its overloads (for selective invalidation).
5.
If you want to animate things, perform the move (step 4) inside the Tick handler
of a Windows.Forms.Timer
BTW: if you need to create some objects (Fonts, Pens, Brushes, ...) either keep them
alive in class members (hence create them only once); or create them inside the Paint
handler and don't forget to call Dispose() on them.
I trust the CP articles do it this way...
|
|
|
|
|
thanks
but all what i need that when click on a button the program draw a ecllipse on the drawing area on a point that i will enter it on a text box
that is what i need
can you help me on it
thanks for your interested
|
|
|
|
|
thanks
but all what i need that when click on a button the program draw a ecllipse on the drawing area on a point that i will enter it on a text box
that is what i need
can you help me on it
thanks for your interested
thanks
|
|
|
|
|
Here is my standard advise on painting, no matter what exactly you want to paint or draw:
there are several steps to correctly draw something so it becomes visible on the screen:
1.
decide upon what object you want to draw; it normally is a Control (e.g. a Panel) or a
Form itself. I prefer to add a Panel to a Form, then draw on the Panel.
2.
create some variables (Rectangle, struct, class, whatever) that hold the parameters of
your drawing. For a rectangle that could be top and left coordinate, and width+height,
or just a Rectangle. etc.
3.
create a Paint handler (either add your own paint handler to the Paint event, or
override the OnPaint method) for that Panel, and do all your drawing in there,
using the Graphics class and your variables.
4.
when you want to change things, modify the variables and call Panel.Invalidate() or
one of its overloads (for selective invalidation).
5.
If you want to animate things, perform the move (step 4) inside the Tick handler
of a Windows.Forms.Timer
BTW: if you need to create some objects (Fonts, Pens, Brushes, ...) either keep them
alive in class members (hence create them only once); or create them inside the Paint
handler and don't forget to call Dispose() on them.
|
|
|
|
|
ok mr Luc Pattyn
but iam try what u say but unuseful
please i want you to send me a code to draw a ecllipse in a panel or if u can send me a project of this
sorry iam tired u , but u r a very good person
thanks for ur advice
|
|
|
|
|
private int x = 0;
private int y = 0;
private int width = 200;
private int height = 100;
private Pen pen=Pens.Black;
public myForm() {
InitializeComponents();
Panel p=new Panel();
Panel.Bounds=...;
Controls.Add(p);
p.Paint+=paintHandler;
...
}
private void paintHandler(object sender, PaintEventArgs e) {
Graphics g=e.Graphics;
g.DrawEllipse(pen, x, y, width, height);
}
That is all you are going to get.
Read some articles, or better yet buy and study a book on C#.
|
|
|
|
|
thanks mr Luc Pattyn
ur code is very good but please i want to click a button to do this
thanks
|
|
|
|
|
This button[^] does everything you want.
|
|
|
|
|
ok but when i put the code in the button event handler the same thing hapened to me the drawings cleared
|
|
|
|
|
It is clear that you do not have the skill or knowledge to attempt this simple task.
May I suggest doing something easier, start with the basics, such as a loop counting to 10?
My failometer is detecting vast quantities of FAIL!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
