数据集、XML 架构和 Excel - 我的天啊！

George Zabanah

4.66/5 (15投票s)

2008年3月19日

CPOL

5分钟阅读

146609

3510

本文演示了 DataSet 惊人的转换能力，通过通用的 XML 架构、XML 和 Excel 读/写（所有这些都使用 XmlBrowser 进行丰富的 XML 渲染）。

引言

本文将以您可能不熟悉的方式探讨 DataSet 的强大功能。这里将揭示 DataSet 的两个重要（且非常有帮助）的方面。

使用 OleDbConnection 可以非常轻松地将 DataSet 写入 Excel 和从 Excel 读取。（注意：我在数据驱动的单元测试中使用了这些功能。）
DataSet（或 XML 文档）与通用的 XML 架构（非 DataSet XML 架构）结合使用，可以用于组织/重组 XML 数据。

背景

我记得 DAO（数据访问对象）时代和 ADO Recordset 时代。我还记得当 Microsoft 推出另一个操作数据的库时，我曾持怀疑态度。那是在 .NET 最初推出的时候。当我开始深入研究 DataSet 的内部机制时，我的怀疑很快就消退了。 Microsoft 通过 DataSet 推出了一个非常强大的工具（即使它没有像我要卖给您的那样被宣传）。

几年前，我为一家全球公司设计/开发了一个企业管理报告系统，该系统需要 MSMQ 驱动的 Office 服务器端自动化。根据 Microsoft 的说法，这是一项不推荐的任务——但它仍然实现了（通过宏等）。

然后，我发现了使用 OleDbConnection 读写 Excel 的好处。这意味着我可以将执行时间限制为仅限于运行宏来操作数据所需的时间（我不再需要 Excel 来写入 Excel）。这大大提高了效率，并减少了出错的风险，因为 Excel 实例的使用时间大大缩短了。

我还记得曾经必须使用 XmlWriter 或 SQL Server 中的 FOR XML 子句来构建 XML 数据。使用 ADO.NET 的 Relations 集合来构建格式化的 XML？ - 也已成为过去。但是，那是我的青葱岁月，当我发现我可以将 XML 架构读入 DataSet 并从中输出格式化的 XML 时，我欣喜若狂！

如果这还不足以引起您的兴趣，那么（在附带的代码中）是否可以采用一种格式的 XML 文档，并将其加载到具有另一种格式的 XML 架构的 DataSet 中？谁能想到？DataSet 作为转换引擎 - 你在开玩笑吧？（我没有！）

现在，您可能会说 .NET 已经提供了通过强类型数据集来组织 XML 的能力。但是，问题在于您不一定想要 .NET DataSet XML 架构。我曾参与过一些项目，其中用于开发 XML 架构的工具不是 VS2005。承认吧，大多数时候，您需要使用通用的 XML 架构。开发团队不一定负责 XML 架构。他们不拥有 XML 架构。毕竟，最佳实践要求我们将数据的表示与数据的检索分离开来。那么，我们如何在这里实现最佳实践呢？

通过放宽约束，我们可以使用 XML 架构来构建数据，然后使用 XML 架构来验证数据。一举两得——也就是说。销售宣传告一段落——让我们看看我指的是什么。

使用代码

该项目有两个主要模块。

名称	描述
`DataSetMethods`	此类有三个方法： `gatherMetaData` - 用于收集 `DataSet` 内的表/列信息和关系。 `setupDataSet` - 接受一个通用的 XML 架构并使其准备好用于 `DataSet`。 `LoadXmlData` - 用于将 XML 加载到已应用目标 XML 架构的 `DataSet` 中。
`ExcelFunctions`	此类包含两个重载的 `ReadFromExcel`（读取到 `DataSet`）和两个重载的 `WriteToExcel`（从 `DataSet` 写入）。

DataSetMethods 代码

/// <summary />
/// This method gathers Table\Column information from a DataSet.
/// It also gathers Relation information from a DataSet.
/// </summary />

public static DataSet gatherMetaData(DataSet ds)
{
    XmlDocument xd = new XmlDocument();
    xd.Load(@"Metadata Xsd\MetaData.xsd");
    DataSet metaData = setupDataSet(xd, new DataSet(),false);

    for (int i = 0; i < ds.Tables.Count; i++)
    {
        for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
        {
            DataRow dr = metaData.Tables["Table"].NewRow();
            dr["Name"] = ds.Tables[i].TableName;
            dr["Column"] = ds.Tables[i].Columns[j].ColumnName;
            metaData.Tables["Table"].Rows.Add(dr);
        }
    }

    for (int i = 0; i < ds.Relations.Count; i++)
    {
        for (int j = 0; j < ds.Relations[i].ParentColumns.Length; j++)
        {
            for (int k = 0; k < ds.Relations[i].ChildColumns.Length; k++)
            {
                DataRow dr = metaData.Tables["Relations"].NewRow();
                dr["ParentTable"] = ds.Relations[i].ParentTable;
                dr["ParentColumn"] = ds.Relations[i].ParentColumns[j].ColumnName;
                dr["ChildTable"] = ds.Relations[i].ChildTable;
                dr["ChildColumn"] = ds.Relations[i].ChildColumns[j].ColumnName;
                metaData.Tables["Relations"].Rows.Add(dr);
            }
        }
    }
    return metaData;
}

/// <summary />
/// Set up a dataset with an Xml Schema that is DataSet friendly
/// </summary />
public static DataSet setupDataSet(XmlDocument xmlSchema, DataSet ds, bool isDataSet)
{
    XmlAttribute msdataAttribute = null;
    XmlNode schemaNode = null;
    StringReader sr = null;
    XmlReader xr = null;
    try
    {
        if (isDataSet)
        {
            msdataAttribute = xmlSchema.CreateAttribute("xmlns:msdata",
                "http://www.w3.org/2000/xmlns/");

            msdataAttribute.Value = "urn:schemas-microsoft-com:xml-msdata";
            schemaNode = xmlSchema.SelectSingleNode(
                "descendant::*[local-name() = 'schema']");

            schemaNode.Attributes.Append(msdataAttribute);
            
            msdataAttribute = xmlSchema.CreateAttribute("msdata:IsDataSet",
                "urn:schemas-microsoft-com:xml-msdata");
            msdataAttribute.Value = "true";

            schemaNode.SelectSingleNode("*[local-name() = 'element']"
                ).Attributes.Append(msdataAttribute);
        }
        sr = new StringReader(xmlSchema.OuterXml);
        xr = XmlReader.Create(sr);
        ds.ReadXmlSchema(xr);
        ds.EnforceConstraints = false;
    }
    finally
    {
        msdataAttribute = null;
        schemaNode = null;
        sr = null;
        xr = null;
    }
    return ds;
}

/// <summary />
/// Load a Dataset (with XmlSchema applied) with data from an XmlDocument
/// </summary />
public static DataSet LoadXmlData(XmlDocument xmlData, DataSet ds)
{
    DataSet tempds = null;
    StringReader sr = null;
    XmlReader xmlDataReader = null;
    try
    {
        tempds = new DataSet();
        sr = new StringReader(xmlData.OuterXml);
        xmlDataReader = XmlReader.Create(sr);
        tempds.ReadXml(xmlDataReader);
        ds.Merge(tempds, false, MissingSchemaAction.Ignore);
    }
    finally
    {
        tempds = null;
        sr = null;
        xmlDataReader = null;
    }
    return ds;
}

ExcelFunctions 代码

#region Constants

/// <summary />
/// string to use for setting up connection string to Excel
/// </summary />
private const string _excelConnectionString = 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";User ID=" + 
    "Admin;Password=;Extended Properties=\"Excel 8.0;HDR=YES\";";

/// <summary />
/// select statement to read from Excel
/// </summary />
private const string _excelSelect = "select * from [{0}]";

/// <summary />
/// tablename column for DataRow
/// </summary />
private const string _tableName = "TABLE_NAME";

/// <summary />
/// CREATE TABLE Template
/// </summary />
private const string _tableCreate = "CREATE TABLE [{0}] (";

/// <summary />
/// COLUMN Template for CREATE TABLE
/// </summary />
private const string _tableColumn = "[{0}] {1}{2}";
#endregion

#region Private Methods
/// <summary />
/// Very simple function to specify Excel DataType mapping.
/// </summary />
private static string getColumnType(DataColumn dc)
{
    string columnType = "TEXT";
    switch (dc.DataType.ToString())
    {
        case "System.Int64" :
        case "System.Double":
        case "System.Int32" :
            columnType = "NUMERIC";
            break;
        default:
            columnType = "TEXT";
            break;
    }
    return columnType;
}

#endregion

#region Public Methods

#region WriteToExcel(DataSet ds)

/// <summary />
/// Write data from a dataset to a new filename.
/// </summary />
public static void WriteToExcel(DataSet ds)
{
    WriteToExcel(ds,ds.DataSetName + ".xls",false);
}
#endregion

#region WriteToExcel(DataSet ds, String fileName, bool append)
/// <summary />
/// Write data from a dataset to a filename.
/// This method can either create a new file or append to
/// an existing Excel file. If append is specified and file does
/// not exist, the file will be created.
/// </summary />

public static void WriteToExcel(DataSet ds, String fileName, bool append)
{
    string excelConnectionString = string.Format(_excelConnectionString, fileName);
    OleDbConnection excelFile = null;
    OleDbCommand excelCmd = null;
    OleDbDataAdapter excelDataAdapter = null;
    OleDbCommandBuilder excelCommandBuilder = null;
    StringBuilder sb = null;
    try
    {
        GC.Collect();
        if (File.Exists(fileName) && !append) File.Delete(fileName);
        excelFile = new OleDbConnection(excelConnectionString);
        excelFile.Open();
        // write each DataTable to Excel Spreadsheet
        foreach (DataTable dt in ds.Tables)
        {
            // file does not exist or we don't want to append
            if (!File.Exists(fileName) || !append)
            {
                // build the CREATE TABLE statement
                sb = new StringBuilder();
                sb.AppendFormat(_tableCreate, dt.TableName);
                foreach (DataColumn dc in ds.Tables[dt.TableName].Columns)
                {
                    sb.AppendFormat(_tableColumn, dc.ColumnName, 
                        getColumnType(dc)
                        , (dc.Ordinal == dt.Columns.Count - 1 ?
                        ")" : ","));
                }
                excelCmd = new OleDbCommand(sb.ToString(), excelFile);
                excelCmd.ExecuteNonQuery();
            }
            // use the command builder to generate insert
            // command for DataSet Update to work
            excelDataAdapter = 
              new OleDbDataAdapter(string.Format(_excelSelect,dt.TableName), excelFile);
            excelCommandBuilder = new OleDbCommandBuilder(excelDataAdapter);
            excelCommandBuilder.QuotePrefix = "[";
            excelCommandBuilder.QuoteSuffix = "]";
            try
            {
                excelDataAdapter.InsertCommand = 
                       excelCommandBuilder.GetInsertCommand();
                excelDataAdapter.Update(ds, dt.TableName);
            }catch {}
        }
    }
    finally
    {
        sb = null;
        if(excelDataAdapter != null) excelDataAdapter.Dispose();
        excelDataAdapter = null;
        excelCommandBuilder = null;
        if(excelCmd != null) excelCmd.Dispose();
        excelCmd = null;
        if (excelFile != null)
        {
            excelFile.Close();
            excelFile.Dispose();
        }
        excelFile = null;
    }
}

#endregion

#region ReadFromExcel(string fileName)

/// <summary />
/// Read from an Excel file into a new DataSet
/// </summary />
public static DataSet ReadFromExcel(string fileName)
{
    return ReadFromExcel(fileName, new DataSet());
}
#endregion

#region ReadFromExcel(string fileName, DataSet ds)
/// <summary />
/// Read from an Excel file into an existing DataSet
/// </summary />
public static DataSet ReadFromExcel(string fileName, DataSet ds)
{
    string excelConnectionString = string.Format(_excelConnectionString, fileName);
    OleDbConnection excelFile = null;
    DataTable schemaTable;
    OleDbDataAdapter excelDataAdapter = null;
    try
    {
        excelFile = new OleDbConnection(excelConnectionString);
        excelFile.Open();
        schemaTable = excelFile.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
        new object[] { null, null, null, "TABLE" });
        // Read each DataTable (i.e. Excel Spreadsheet) into the DataSet
        foreach (DataRow dr in schemaTable.Rows)
        {
            excelDataAdapter = new OleDbDataAdapter(dr[_tableName].ToString(), excelFile);
            excelDataAdapter.SelectCommand.CommandType = CommandType.TableDirect;
            excelDataAdapter.AcceptChangesDuringFill = false;
            string table = dr[_tableName].ToString().Replace("$", 
                           string.Empty).Replace("'", string.Empty);
            if (dr[_tableName].ToString().Contains("$"))
                excelDataAdapter.Fill(ds, table);
        }
        excelFile.Close();
    }
    finally
    {
        if(excelDataAdapter != null) excelDataAdapter.Dispose();
        excelDataAdapter = null;
        schemaTable = null;
        if (excelFile != null)
        {
            excelFile.Close();
            excelFile.Dispose();
        }
        excelFile = null;
    }
    return ds;
}
#endregion

#endregion

关注点

此项目使用了我的 XmlBrowser 控件。您可以在另一篇 CodeProject 文章此处找到它。我最初也在几年前的博客上写过 Excel 函数。我对其代码进行了一些修复，使其更好。在多次覆盖同一个 Excel 文件时（大约 10 次或更多次），它偶尔仍然会出现问题。您可以在此处找到我的原始版本。

使用应用程序

当您从下拉列表中选择一个 XML 架构（然后单击“Render”按钮，再单击“Save”按钮）时，它将创建一个名为 MetaData.xls 的文件。该文件包含您确定 DataSet 需要哪些列才能进行整形的所有信息。单击“Save Data”按钮会将输出的 DataSet 保存到名为 OutputData.xls 的文件中。您必须单击“Render”按钮才能看到转换结果。

历史

2008 年 3 月 19 日

初始发布。

2008 年 3 月 20 日

将元数据写入 DataGrid/Excel 文件的操作分离出来。当用户单击“Get”按钮时执行此操作。添加了一个用于 XML 文件的下拉列表。如果用户选择一个 XML 文件，则使用该文件作为数据源，而不是从 Excel 读取。

2008 年 3 月 26 日

添加了一个选项卡控件，包含三个选项卡：输入 XML、目标架构和输出 XML，以便用户可以看到转换正在做什么。添加了一个“Render”按钮，以便用户在从下拉列表中选择输入 XML 和 XML 架构后可以单击此按钮。向 setupDataSet 方法添加了 isDataSet 参数。添加了“Statistics”表来捕获 DataTable 名称和 DataTable 行数。

2008 年 3 月 27 日

添加了“Save Data”按钮，将结果 DataSet 保存到 OutputData.xls。创建了一个名为 Excel Files 的项目文件夹，以便用户可以添加多个 Excel 文件进行渲染。对 Excel 读写函数进行了微小的修复，以处理特殊字符。

2008 年 4 月 4 日

修改了 XmlBrowser 控件，并包含在此库的新版本（2.0）中，该版本需要 SAXON。这是一个具有真正酷功能的备用解析器。

2008 年 4 月 7 日

添加了Vanilla XML 选项，它使用 Microsoft 解析器可以非常快速地渲染大型 XML 文档。不渲染命名空间或 CDATA 节点。

2008 年 4 月 14 日

修改了 XmlRender，使用 Microsoft 解析器可以非常快速地渲染命名空间和 CDATA 节点。允许在仅选择 XML 架构时保存一个空的 data.xls（这允许用户构建一个 Excel 数据源，与 XML 架构一起使用来测试转换）。

数据集、XML 架构和 Excel - 我的天啊！

引言

背景

使用代码

关注点

使用应用程序

历史

参考文献