Data Exporting to MS-Excel in Webmatrix

Hey Guys! I 've researched on new topic ..

This solution uses the System.Data.SqlServerCe.dll, should be referenced in Web.config file, and also three more functions

Here in Web.config file you should write this code

<?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>

There is an add assembly setting that references the System.Data.SqlServerCe.dll assembly which is needed during the compilation of the web page eg: for further you should see CreateDataTable function.

Here the CreateDataTable function

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;

    }     

}

This 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.

Here the 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:

 Collapse | Copy Code

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

 Collapse | Copy Code

[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.

Add Single Quote 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.

Now 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 compact Edition 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