在Webmatrix中导出数据到Excel

• 下载示例网站 - 2.5 KB

引言

当我在寻找一种从我的 Web Pages 站点的 SQL Server CE 数据库上传数据的方法时，我在 “导出数据到 Excel” 这篇文章的 “方案 1 – OLEDB” 提议中找到了一个可能的答案。

在开发过程中，我遇到了一些小问题，最终我获得了一个可以接受的实现，我将在本文中分享它。

我的解决方案使用了 System.Data.SqlServerCe.dll，它必须在 Web.config 文件中被引用，以及三个函数，我将在下面概述。

代码深入

Web.config 文件

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

add assembly 设置引用了 System.Data.SqlServerCe.dll 程序集，该程序集在网页编译期间是必需的（参见 CreateDataTable 函数）。

CreateDataTable 函数

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;
    }      
}

CreateDataTable 函数打开与 SQL Server CE 数据库的连接，并将数据作为 DataTable 从数据库导出。

它接受 SQL Server CE .sdf 文件的物理路径作为第一个参数，以及从数据库中提取数据的 SQL 查询作为第二个参数。

该函数实例化 System.Data.SqlServerCe 命名空间中的类，该命名空间必须在 Web.Config 中被引用。

ExportToExcel 函数

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;
    }
}

ExportToExcel 函数接收一个 DataTable 作为第一个参数，并将其内容传输到新 Excel 文件的工作表（工作表名称来自第三个参数）中，该 Excel 文件创建在通过第二个参数传递的路径中。

如果函数成功，它返回导出的记录数，否则返回 -1。

它从导出数据到 Excel 的导出函数中进行了一些修改；在下面，我重点介绍它的要点。

连接字符串

我选择生成 Excel 2007 引入的新 .xlsx 文件格式的 Excel 文件，因此我使用了 Access Database Engine 2010 的连接字符串，它有 32 位和 64 位版本，如果您的系统上还没有，必须从 Microsoft Access Database Engine 2010 Redistributable 下载。

即使您想创建一个传统的 .xls 文件，您也必须知道旧的 Microsoft OLE DB Provider for Jet 只有 32 位版本。

因此，对于在 64 位环境中运行的网站，唯一的解决方案是使用 Access Database Engine 2010，并使用稍微不同的连接字符串

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

字段名称字符串

两个不同的 SQL 语句需要一个字段名称列表，格式如下

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

该字符串由一个 for 循环创建，该循环将 DataTable 的所有列名附加到一个 StringBuilder 对象。

创建 Excel 工作表

该函数使用的主要简化方法是将数据库中的所有数据作为文本传输到 Excel 文件。

这种方法避免了逐个检查 DataTable 的列数据类型，并创建具有相同源类型列的 Excel 工作表。

因此，Excel 工作表的所有列都生成为文本字段，SQL 语句使用之前看到的字段名称字符串，并通过使用 Replace("]", "] text") 方法将“text”作为数据类型添加到任何字段名称中。

将记录从 DataTable 复制到 Excel 工作表

对于每个 DataTable 行，都会创建一个字符串，附加所有行值，然后将该字符串与字段名称字符串一起使用，以组装一个 SQL 语句，将行值插入到 Excel 工作表中。

请注意，创建字段值字符串的过程涉及调用 AddSingleQuote 函数来转义值中可能存在的单引号。

AddSingleQuote 函数

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;
}

如果作为值传递给 Insert SQL 语句的文本包含单引号，SQL Server 会抛出一个错误（错误：105 未封闭的字符字符串后的引号 ‘）。

该函数通过添加第二个单引号来转义任何单引号来修复此问题。

示例应用程序

为了说明我的函数的使用，我提出了一个简单的站点，该站点从一个示例数据库中提取数据作为 Excel 文件，并使用一个相当复杂的查询。

我使用的示例数据库是 TestDB.sdf 文件，可以从链接 SQL SERVER – CE – Samples Database for SQL CE 4.0. 下载。

必须将其复制到示例站点的 App_Data 目录中，然后可以在浏览器中启动 Default.cshtml 页面。

显然，我使用的查询可以替换为更简单的查询，例如

SELECT * FROM Customers