SomeDeveloperPerson

The example on "http://support.microsoft.com/kb/306023" provides examples on how to populate an excel spreadsheet from a database.

For the ADONET example, the code replaces the rows within an existing spreadsheet with new rows, however, the example does not connect to a database. Instead it manually places values into the spread sheet. Can you give me pointers on how to populate the rows in the same manner as the example, but using data from a database


This is the C# code:

private void Use_ADONET()
{
// Establish a connection to the data source.
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
"Book7.xls;Extended Properties=Excel 8.0;");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandType
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Bill', 'Brown')";

objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();


// Close the connection.
objConn.Close();


}



Re: .NET Framework Data Access and Storage Populate Excel Spreadsheet

VMazur

First you need to retrieve data from the database into DataSet/DataTable and described in next KB article. Then you would need to loop through the rows in a DataTable and insert them using the code you posted. Another way is to use DataAdapter that will do basically same staff.

http://support.microsoft.com/kb/314145/en-us






Re: .NET Framework Data Access and Storage Populate Excel Spreadsheet

Fox-Jazz

Not to diss microsoft on this one but DISS.

I use aspose (have bought it) for the purpose of populating excel spreadsheets. Microsoft stuff was really bulky, and unreliable.

I asked them about these problems (mostly has to do with number vs alphanumeric) if I remeber correctly they have a real problem deciding which datatype it is.

It's like they use the first row to determin the datatype, and makes many records null where they should be valid values.

anyway, that is my suggestion.