Ni! Ni! Ni! Ni! Ni!

Just another WordPress.com weblog

Export SQL Data into Excel Programmatically November 27, 2006

Filed under: C# — Jason @ 8:16 pm

Getting data from one data source into an Excel spreadsheet may not be as hard as it first seems. Your initial thoughts take you down the road of using the Excel COM/ActiveX Objects which is simple enough although results in lengthy code.

This approach is just fine if you have Excel installed on the same server as the application. But what if you don’t have Excel installed on the same server? What then? Bring in Microsoft OleDb and Jet 4.0.

I’ve read many guides that walked me through the process of reading from an Excel file using OleDb. The reading of an excel file and it’s data is relatively straight forward. On the other hand if you want to write to Excel then it can become a little tricky. As the saying goes “there’s more than one way to skin a cat” just as there are several ways of getting data into Excel. Below I shall cover two methods which are fairly simple and straight forward.

Method 1 – I assume this code to be placed within a class and called from another application or GUI:
Define an SQL query to create a table that will become the Excel worksheet. We also need a query that will insert data into the worksheet


OleDbConnection connection;
private string sqlCreate = @"CREATE TABLE ExportedData ([ID] VARCHAR(15), [Name] VARCHAR(40))";
private string sqlInsert = @"INSERT INTO ExportedData ([ID], [Name]) VALUES (@ID, @Name)";

Next up, a simple method to create a connection to an Excel workbook (NOTE: The workbook must pre-exist). Before opening, it may be an idea to have your program take a copy of the Excel file to operate on, thus leaving the original as a template.


private void OpenConnection()
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\WorkBook.xls; Extended Properties=Excel 8.0;";
try
{
connection = new OleDbConnection(connectionString);
}
catch (OleDbException e)
{
...
}
}

Using the SQL defined earlier, we can create the table within the Excel Workbook. What this essentially does is creates a new Worksheet within the Excel file, naming it accordingly and having it adopt your schema as per the CREATE TABLE statement.


private void CreateTable()
{
if (connection == null)
{
OpenConnection();
}

OleDbCommand cmd = new OleDbCommand(sqlCreate, connection);

try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
... Handle exception
}
finally
{
cmd.Dispose ();
connection.Close();
}
}OleDbCommand cmd = new OleDbCommand(sqlCreate, connection);

try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
... Handle exception
}
finally
{
cmd.Dispose ();
connection.Close();
}
}

Now that we’ve successfully create an Excel file with a worksheet that represents our data schema we can then go about pushing data into Excel. To do this you must once again call up the OleDb connection and then proceed with inserting your data. In the event of the data being user.


public void InsertIntoExcelTable(DataSet newData)
{
foreach (System.Data.DataRow r in newData.Tables[0].Rows)
{
r["Extracted"] = System.DateTime.Now; // update the row!
}

if (this.connection == null)
this.OpenConnection();

adapter = new OleDbDataAdapter();
adapter.UpdateCommand = new OleDbCommand(sqlInsert);
adapter.UpdateCommand.Connection = connection;

// At this point the Data needs to be somehow cleansed to ensure that we don’t have any blank fields otherwise it will fall over
adapter.UpdateCommand.Parameters.Add(“@param1”, OleDbType.VarWChar, 15, “ID”);
adapter.UpdateCommand.Parameters.Add(“@param2”, OleDbType.VarWChar, 40, “Name”);

try
{
adapter.Update(newData, “ListData”);
}
catch (OleDbException e)
{
throw e;
}

adapter.Dispose();
this.connection.Close();
}
SQL Server Data is passed into this method in the form of a DataSet. Note how each row of the DataSet has a field updated to set the ‘modified’ flag on the row. This is crucial to the export to XML.

Having passed into the method a DataSet of SQL Server results, we create a new OleDbDataAdapter using the SqlInsert SQL Statement. You must then declare the parameters within the query and assign them values. These parameter declarations are what link the parameters in the SQL Query with values in the dataset. So basically what we are doing is creating mapped links between the DataSet fields and the Excel fields.

Once we’re ready to go we simply call the Update method of the DataAdapter in order to have the DataSet data sent to the Excel file.

There you have it – it’s that simple!

Method 2 – SQL
Another simple, straight forward method of getting SQL Data into Excel Programmatically is by using a connection string in an SQL Query like this:

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\WorkBook.xls;Extended Properties=Excel 8.0')...Sheet1$ SELECT firstname, lastname FROM [Database].[dbo].[Table]

And of course this can be executed in .Net code programmatically. NOTE: This method requires that you already have a workbook set up with the fields/columns already created, names of which match SQL Server column names, in order for the Excel file to be populated

Advertisements