Hi Can anyone help me please with the following problem This is Raymond PostingH

[rn@warner-it.com]

I am trying to read an excel file .xlsx and show mit in a grid before I edit it and then write to access table. When I try the Microsoft 12.0 oledb driver I get several error messages. I cannot get it to work can someone please help me. use the oledb driver for the latest excel files, Below the connection code and the read code to display in a grid with error message. is " Failure creating File" and the datatgrid is empty because it cannot rrad the file, can someone please help or give me a workaround.

// XLSX - Excel 2007, 2010, 2012, 2013
*props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
*props["Extended Properties"] = "Excel 12.0 XML";
*props["Data Source"] = "C:MyExcel.xlsx";

ConnectString2:= "provider=Microsoft.ACE.OLEDB.12.0; data source=C:PDIExcel_Tabellen"+gFilename+"; Extended Properties=Excel 12.0 XML"

oConn2 := OleDbConnection{connectString2}
METHOD MaterialListeLesen() AS VOID
LOCAL cKey AS STRING
LOCAL cSelect AS STRING
LOCAL oWin1, oWin2,oWin3 AS DrillWin3

gSelect := "select * from [Sheet1$]"


messagebox.show( gSelect)



oWin1 := DrillWin3{}
oWin1:GridTitlecolor(color.YellowGreen)
oWin1:SetTitle("Anzahl Stunden für = "+gUsername,color.YellowGreen,"")
oWin1:Text := "Anzahl Stunden für = "+gUsername
*oWin1:SetTitle("Umsatzhistorie nach Kunde",color.LightBlue,"")
oWin1:InitDGVManM(gSelect,gFilename)
oWin1:SetDoubleClickMode(3)
oWin1:ReSetDGV(1, "")
oWin1:show()

RETURN

[Sherlock]

Both XLSX and DOCX is a ZIP file container with XML [ and other stuff] in a directory structure you can read.

You can edit an XML with your changes and close the ZIP file.

You do not need the OLEDB to do this.

[rn@warner-it.com]

Hi Sherlock,
If this is the case can you please show me an example as to how I can do this the way I want to using sql to read the data and then show it in a workable datatgrid.
Pl,ease show me in a example code if possible I am still not clear as to how to access the Excel file.


Thanks
Raymond

[rn@warner-it.com]

Hi Sherlock,
So you understand my intentions this is what I want to do with my programe here is a example using the oledb driver but it does not work in X# on my computer.

Maybe Chris can help regarding this problem ????

Below a example document this working in C#

Read Write Excel file with OLEDB in C# (without Interop)
Are you looking for a way to Read/Write Excel file without Interop com? Want to Read both XLS and XLSX format? Then read this article - it will really help you Read or Write Excel files using OLEDB.

Background
In earlier days when I was new to programming, I used to read/write Excel file using Interop object, but it is unmanaged and heavy entity and due to its 'HELL' performance, I desperately needed some good alternative to Interop. I have gone through OLEDB, it performs very well for reading and writing Excel files.

Using Code
Before start Reading/Writing from/in Excel file, we need to connect to OLEDB using connection string, here OLEDB will act as Bridge between your program and EXCEL.

Bridge between C# and EXCEL

Rows and columns of Excel sheet can be directly imported to data-set using OLEDB, no need to open Excel file using INTROP EXCEL object.

Let's start with the code.

// Connect EXCEL sheet with OLEDB using connection string
// if the File extension is .XLS using below connection string
//In following sample 'szFilePath' is the variable for filePath
szConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
"Data Source='" + szFilePath +
"';Extended Properties="Excel 8.0;HDR=YES;"";

// if the File extension is .XLSX using below connection string
szConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
"Data Source='" + szFilePath +
"';Extended Properties="Excel 12.0;HDR=YES;"";
In the above connection string:

Provider is OLEDB provider for Excel file, e.g., Jet.OLEDB.4.0 is for XLS file and ACE.OLEDB.12.0 for XLSX file
Data Source is the file path of Excel file to be read
Connection string also contains 'Extended Properties' like Excel driver version, HDR Yes/No if source Excel file contains first row as header
After connection to EXCEL file, we need to fire Query to retrieve records from sheet1.

Accessing Excel Tables
There are a couple of ways to refer to an Excel table:

Using sheet name: With the help of sheet name, you can refer to Excel data, you need to use '$' with sheet name,
e.g. Select * from [Sheet1$]

Using Range: We can use Range to read Excel tables. It should have specific address to read,
e.g. Select * from [Sheet1$B1:D10]

**Here $ indicates the EXCEL table/sheet already exists in workbook, if you want to create a New workbook/sheet, then do not use $, look at the sample below:

// Connect EXCEL sheet with OLEDB using connection string
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
("select * from [Sheet1$]", conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
dataGridView1.DataSource = excelDataSet.Tables[0];
}

//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
// with the help of data adaptor we can load records in dataset

//write data in EXCEL sheet (Insert data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange)
VALUES ('DEC','40','60','80');";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//exception here
}
finally
{
conn.Close();
conn.Dispose();
}
}

//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//exception here
}
finally
{
conn.Close();
conn.Dispose();
}
}
*OLEDB does not support DELETE query.

[FFF]

Raymond,
sorry to say, but how should anyone know how to help you, if you present such "non" info?
* Can you connect to the excel file at all?
* You write "Failure creating File" - but also: "want to read xlsx, edit, write to access". Where do you try to create a File?
* what are "several errors"
etc. etc.

As a famous man wrote: "Show us the code" - better, write a little sample show how you try up to the first error...
EDIT: Raymond beat me to it but a link to the cited article might help... ah, found it : https://www.codingame.com/playgrounds/9 ... ut-interop

[rn@warner-it.com]

Hi Whoever is your name I can only see FFF
quite simple firstly I will only post something if I myself cannot find the reson why it is not working . the answer to you statement of not giving enough information is that I am trying to read any excel file with a .XLSX ending the one i chose is Artikel.xlsx in the c:PDiArtikel,xlsx and All I want to do is to read this file with a SQL statement
similar code I posted as an example that I found on the Internet using c# to do the same thing using the microsoft 12.0 oledb driver. I have coded it as it should be and I have found that it does not work with the Microsoft 12.0 driver I get this error . So if you do not beleive me please do the same with your X# and tell me if it works ??
the error I get which is an error I do not understand " Failure creating File" becausde I am not cfreating any file all I am doing is to read the excdel file and show it in a Datagrid.
Please Somebody with a little bit of insighjt have a look at wahtn I am describing and try it out yourself.

Chris Pygras can you please Help ??????????

Thanks
Raymond

[Chris]

I am sorry Raymond, unfortunately I am not at all familiar with that stuff. Maybe someone else can step in and help though!

[rn@warner-it.com]

Hi Chris,
Ok I understand I find it strange that it supposedly works in C# with the Microsoft 12.0 Oledb driver to read xlsx files but it only brings errors in XSharp and errors that do not make any sense because I am not writing a file I am only putting it in a Data grid. With the same code I have I have it working with the Microsoft 4.0 driver reading a xls file this works and I can see the data in the grid. So the problem is that the Microsoft Oledb driver 12.0 is for some reason not working in XSharp. The first time I tried this I got the error that the driver is not registered on my local machine this was then resolved after reinstalling the driver no I get the error "Failure creating File"
I cannot believe that I am the only person that is using this method to read a excel file and project to a editable data grid:
Below the Info that Xlsx can only be read using the Microsoft 12.0 driver

Provider is OLEDB provider for Excel file, e.g., Jet.OLEDB.4.0 is for XLS file and ACE.OLEDB.12.0 for XLSX file

Thanks regards
Raymond
Below info to Driver
Data Source is the file path of Excel file to be read
Connection string also contains 'Extended Properties' like Excel driver version, HDR Yes/No if source Excel file contains first row as header
After connection to EXCEL file, we need to fire Query to retrieve records from sheet1.


If someone has another idea or way of doing this please share your example code for this.
Im hope someone is able to help

[lumberjack]

Raymond,

Ok I understand I find it strange that it supposedly works in C# with the Microsoft 12.0 Oledb driver to read xlsx files but it only brings errors in XSharp

Your X# code looks totally different from the c# code, have you tried to translate it line for line to see where it goes wrong? You can always use ILSPY with the X# plugin to translate. It is difficult to say where to problem is from what you have provided.

It might be that you actually have another issue. A shot in the dark, xlsx files are just a .zip file. It might be that the driver tries to unzip it to disk and not have the necessary permission to create those "temp" files in the folder it tries to do it....

[rn@warner-it.com]

Hi Johan,
I have been on this for a few days and it is a nightmare I have tried all alternatives like I said earlier when I use the 4.0 driver I can read a xls excel file without any problems into a working data grid so the code is in order and I am sure that it is not the code its something else. I just wish that someone will try the same on their machine and see if it works for them in XSharp with the Microsoft 12.0 Oledb driver.

Yes Johan I have checked it so many times.

Raymond