Export data to Excel in Webmatrix

• Download the sample site - 2.5 KB

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 code in depth

Web.config file

<?xml version="1.0"?>
<configuration>
  <system.web>
    <compilation debug="true">
      <assemblies>
        <add assembly="System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
      </assemblies>
    </compilation>
  </system.web>
</configuration>

The add assembly setting references the System.Data.SqlServerCe.dll assembly that is needed during the compilation of the web page (see CreateDataTable function).

CreateDataTable functions

public static DataTable CreateDataTable(string sqlCeDb, string sqlCmd)
{
    DataSet dataSet = new DataSet();
    DataTable dt = new DataTable();
    
    try {
        SqlCeConnection sqlConn= new SqlCeConnection();
        sqlConn.ConnectionString = "Data Source = " + sqlCeDb; 

        SqlCeCommand cmd = new SqlCeCommand(); 
        cmd.CommandType = CommandType.Text; 
        cmd.CommandText = sqlCmd; 
        cmd.Connection = sqlConn;

        sqlConn.Open();
        SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd);
    
        sda.Fill(dataSet);
        sqlConn.Close(); 
        
        dt = dataSet.Tables[0];
        return dt;
    }
    catch (Exception ex)
    {
        return dt;
    }      
}

The CreateDataTable function opens a connection with the SQL Server CE database and exports data as DataTable from the database.

It accepts the physical path of the SQL Server CE .sdf file as first parameter and the SQL query extracting data from the database as second parameter.

The function instantiates classes from the System.Data.SqlServerCe namespace, that must be referenced in Web.Config.

ExportToExcel function

public static int ExportToExcel(DataTable dt, string excelFile, string sheetName)
{
    // Create the connection string
    string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
        excelFile + ";Extended Properties=Excel 12.0 Xml;";
    
    int rNumb = 0;
    try
    {
        using (OleDbConnection con = new OleDbConnection(connString))
        {
            con.Open();
            
            // Build the field names string
            StringBuilder strField = new StringBuilder();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strField.Append("[" + dt.Columns[i].ColumnName + "],");
            }
            strField = strField.Remove(strField.Length - 1, 1);
            
            // Create Excel sheet
            var sqlCmd = "CREATE TABLE [" + sheetName + "] (" + strField.ToString().Replace("]", "] text") + ")";
            OleDbCommand cmd = new OleDbCommand(sqlCmd, con);
            cmd.ExecuteNonQuery();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                
                // Insert data into Excel sheet
                StringBuilder strValue = new StringBuilder();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    strValue.Append("'" + AddSingleQuotes(dt.Rows[i][j].ToString()) + "',");
                }
                strValue = strValue.Remove(strValue.Length - 1, 1);
                
                cmd.CommandText = "INSERT INTO [" + sheetName + "] (" + strField.ToString() + ") VALUES (" +
                        strValue.ToString() + ")";
                cmd.ExecuteNonQuery();
                rNumb = i + 1;
            }
            con.Close();
        }
        return rNumb;
    }
    catch (Exception ex)
    {
        return -1;
    }
}

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:

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
    excelFile + ";Extended Properties=Excel 8.0;";

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.

AddSingleQuote function

public static string AddSingleQuotes(string origText)
{
 string s = origText;
    int i = 0;
    
    while ((i = s.IndexOf("'", i)) != -1)
    {
        // Add single quote after existing
        s = s.Substring(0, i) + "'" + s.Substring(i);

        // Increment the index.
        i += 2;
    }
    return s;
}

If the text passed as value to the Insert SQL statement includes a single quote, SQL Server throws an error (Error: 105 Unclosed quotation mark after the character string ‘).

The function fixes this occurrence by escaping any single quote with the addition of a second single quote.

The sample application

To illustrate the use of my functions, I propose a simple site that extract data as Excel file from a sample database with a quite complex query.

The sample database I used is the TestDB.sdf file downloadable from the link SQL SERVER – CE – Samples Database for SQL CE 4.0.

It must be copied into the App_Data directory of the sample site and then Default.cshtml page can be launched in browser.

Obviously, the query I used could be replaced with a simpler one like

SELECT * FROM Customers