Export dataGridView to xls

[sambos]

Hello,

I'm writing a windows form program (C++/CLI) and need a bit of guidance...

I have a dataGridView full of data which I would like to EXPORT to an excel file.

The dataGridView is actually populated using OleDB but I can't seem to figure out how to do the export.

At this stage, I'm not fussy about formatting. Just a simple copy and paste of information.

Is there anyone out there willing to provide me an example code with some explanation notes?

Cheers,

[Thomas1965]

One way is to use the CSV format and import it in Excel.

Here are some articles about Excel and OleDB, but in C#. Maybe you can translate the code or put in a seperate dll. http://www.codeproject.com/search.aspx?q=oledb+excel

[sambos]

Hi Thomas1965,

Thanks for the reply and link.

I have had a good go at this but I'm still having troubles. With your help, I've gotten further than before so thanks for your advice.

This is the error I'm getting:

An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: Format of the initialization string does not conform to specification starting at index 54.

This is the code I'm using (note, I haven't used my dataGridView - just trying to make a simple xlsx file:

String^ connectionString = "Microsoft.ACE.OLEDB.12.0; Data Source = C:\\test.xlsx; Extended Properties\"Excel 12.0 XML;HDR=YES\"";

OleDbConnection^ conn = gcnew OleDbConnection(connectionString);

conn->Open();
OleDbCommand^ cmd = gcnew OleDbCommand();
cmd->Connection = conn;

cmd->CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
cmd->ExecuteNonQuery();

conn->Close();

Thoughts / Advice?

[Thomas1965]

You forgot the provide in your connection string. Try this:
String^ connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\test.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=YES\"";

[sambos]

Thomas1965,

Once again, you've helped me deluxe!

Yep, your correction worked fine. Now I just need to work out how to transfer what's in my dataGridView to the xlsx file.

Also, I notice that if click my export button after the file has been created, it produces this error message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Table 'table1' already exists.

Cheers for your help... Would you mind if I sent you a message if I get stuck?

[Thomas1965]

Yes you can send me a message if you want. Would be good if you could upload the project on Dropbox or sth. similar.