dataset exporting

[Techno]

how is it possible to export a dataset to an excel file? This is using WinForms, not ASP.NET - I know its much easier to do in ASP.NET

I would like to avoid the solution of using a COM interop (however the solution to it would be appreciated for my knowledge on how to open/close/insert data into a workbook/worksheet) but use OleDbConnection if at all possible

Thanks

[RobDog888]

Using the Excel Object Model from my Office Dev FAQ -

Automate Excel with C# (Early binding and Late binding examples) (Posts #2 and #3)

[Techno]

awesome, good job and many thanks. I would also still like to here other methods too but no doubt I will learn heck of a lot from that article!

[RobDog888]

Thanks. I would have posted the other method but it would take me a bit to write one up. So I figured the link would be good for now.

I have the main index link in my signature with many VB.NET and C# examples for Office apps.


Ps, I dont think I have ever seen a Late binding C# code example around anywhere before.

[Techno]

first time for everything :P
I get errors, well, COM exception when creating a workbook/worksheet

Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154.

I do have Office 2003 installed with Excel and added the Excel COM component also as a reference.

I just want to take data from the dataset and "shove" it into an excel worksheet, being it via OleDb or COM - preferably OleDb

[jmcilhinney]

You can use ADO.NET with Excel files via OleDb, which negates the need for Excel to be installed. You obviously don't have all the Excel functionality that you do via Automation but if it's just getting data in and out of workbooks then ADO.NET is probably easier anyway.

[Techno]

but how would I go about actually doing it if I have a dataset?
I have:
dataset with records
a worksheet file (book1.xls)

I know how to use ADO.NET to connect to and execute commands BUT how would I pretty much code it to take all data from dataset and insert it into the file?

I know I require an OleDbCommand and an Insert statement but would I have to iterate through each column, get the names, and add the @param as values, then set the datasource/sourcecolumn and execute the query?

[jmcilhinney]

I guess I should qualify what I said previously, because I don't know for sure but I doubt that ADO.NET can create XLS files. Assumimg that you have an XLS file you may be able to create worksheets with CREATE TABLE statements, although, again, I'm not sure. You would have to add column headers using CREATE COLUMN statements I would expect, or you can specify that the columns don't have headers using the connection string and just refer to the columns by the generic names F1, F2, etc. www.connectionstrings.com has the connection string format and the basic syntax for referring to a worksheet or cell range as a table. I should specify that I haven't done anything more than basic data access this way, so I don't know exactly how capable the mechanism is.

[Techno]

sure, many thanks. I tried doing it the way I had thought you meant but I get an error "@theID has no default Value"

OleDbCommand theOleDbCommand = new OleDbCommand("INSERT INTO [Sheet1$] (", this.theOleDbConnection);
            string theParams = String.Empty;

            foreach (DataColumn curColumn in this.theDataSet.Tables[0].Columns)
            {
                if (curColumn.Ordinal < this.theDataSet.Tables[0].Columns.Count - 1)
                {
                    theOleDbCommand.CommandText += curColumn.ColumnName + ",";

                    OleDbParameter theParam = new OleDbParameter();
                    theParam.SourceColumn = curColumn.ColumnName;
                    theParam.ParameterName = "@the" + curColumn.ColumnName;
                    theOleDbCommand.Parameters.Add(theParam);
                }
                else
                {
                    theOleDbCommand.CommandText += curColumn.ColumnName;

                    OleDbParameter theParam = new OleDbParameter();
                    theParam.SourceColumn = curColumn.ColumnName;
                    theParam.ParameterName = "@the" + curColumn.ColumnName;
                    theOleDbCommand.Parameters.Add(theParam);

                }
            }

            theOleDbCommand.CommandText += ") VALUES (" + theParams + ")";

            theOleDbCommand.Connection.Open();
            theOleDbCommand.ExecuteNonQuery();
            theOleDbCommand.Connection.Close();

yes I know it's a bad way..... lol.

[mendhak]

You've created a string called theParams, but you haven't assigned it any values. If you want your SP to take you seriously, you should give it corresponding values.

[Techno]

forget theparams string lol I'm not using it at all.... instead im just directly giving and creating the OleDbParameter....

[mendhak]

Yes, but if you don't supply a value to it, the stored procedure will look for any default values specified in the CREATE PROCEDURE... and you obviously haven't specified one.

So you need to give those parameters a value.

[Techno]

hmmmmm
im not using a SPROC - im trying to write data to an excel sheet.
I thought I would be "mapping" the source column so it can get the values from there during insertion?

[mendhak]

Ah, the Sproc was an oversight on my part, but you are inserting values into the excel sheet. Right?

[Techno]

that is what I want to do.
I have a dataset filled with data. I now want to insert this data into an excel spreadsheet without the need for using the COM approach, if at all possible

[mendhak]

I don't think you can escape COM. You can use ADO.NET but that in turn uses COM objects to talk to Excel. However, considering that the .NET framework will be on the user's machine, there shouldn't really be a problem there. Why the COM-aversion?

[Techno]

I just don't understand it! I need to learn it no doubt, just thought there would be a simpler way of using it via ADO.NET

[mendhak]

Look here
http://support.microsoft.com/kb/316934/

Understanding that COM is used in the background helps, but it's not entirely necessary.