Introduction

When I was looking for a method to upload data from the SQL Server CE database of my Web Pages site, I found a possible answer in the “Solution 1 – OLEDB” proposal of the “Exporting Data to Excel” article.

During the development I came across some small problems and finally I obtained an acceptable implementation that I will share in this article.

My solution uses the System.Data.SqlServerCe.dll, that must be referenced in Web.config file, and three functions, which I outline in the following.

The ExportToExcel function receives a DataTable as first parameter and transfers its content into a sheet (with name from the third parameter) of a new Excel file created at the path passed with the second parameter.

If the function is successful, it returns the number of exported records, otherwise it returns -1.

It derives with some modifications from the Export function from Exporting Data to Excel; in the following I highlight its points of interest.

The connection string

I have chosen to produce an Excel file in the new .xlsx file format introduced by Excel 2007, and so I have used a connection string to the Access Database Engine 2010, which exists in 32-bit and 64-bit versions and that must be downloaded from Microsoft Access Database Engine 2010 Redistributable, if it’s not present on your system yet.

Even if you want to create a traditional .xls file, you have to know that the old Microsoft OLE DB Provider for Jet is available in 32-bit version only.

So, the only solution for the web sites running on 64-bit environments is to use the Access Database Engine 2010 with a slightly different connection string:

The field names string

Two different SQL statements require a list of field name as string in the format

[field1],[field2],[field3],…,[fieldn]

The string is created by a for loop that appends to a StringBuilder object all the column names of the DataTable.

Create Excel sheet

The major simplification used by the function is that all the data from database are transferred to the Excel file as text.

This approach avoids to examine one by one the column data types of DataTable and create an Excel sheet with columns of the same source type.

So, all the columns of the Excel sheet are generated as text fields with a SQL statement that uses the field names string seen before adding to any field name “text” as data type with the use of a Replace("]", "] text") method.

Copy records from DataTable to Excel sheet

For each DataTable row a string is created appending all the row values and then the string is used together with the field names string to assemble a SQL statement that inserts the row values into the Excel sheet.

Note that the process of creating a field values string involves a call to the AddSingleQuote function to escape possible single quotes in the values.

First, thank you for this excellent piece of code. I'm a novice and yet I got it to work perfectly on my local machine. After Publishing on azurewebsites.net, the download returns -1. I'm unclear how to proceed debugging this issue? Thanks again.

"the meat from that butcher is just the dogs danglies, absolutely amazing cuts of beef." - DaveAuld (2011)"No, that is just the earthly manifestation of the Great God Retardon." - Nagy Vilmos (2011)
"It is the celestial scrotum of good luck!" - Nagy Vilmos (2011)
"But you probably have the smoothest scrotum of any grown man" - Pete O'Hanlon (2012)