65.9K
CodeProject 正在变化。 阅读更多。
Home

ExcelML模板编辑器 – 使用Office 2003 SpreadsheetML功能进行数据可视化

Dan Radu
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.65/5 (8投票s)

2006 年 10 月 27 日

CPOL

5分钟阅读

viewsIcon

71894

downloadIcon

1676

本文介绍如何使用 Office 2003 SpreadsheetML 功能显示存储在 .NET 数据集中的数据。

引言

在文章 WordML 模板编辑器 – 使用 Office 2003 WordML 功能进行数据可视化 中，我们通过 Word ML 和 Word DOM 方法解决了数据可视化问题。本文提出了相同的问题，但以 MS Excel 的方式。它只是一个简单的“模板”编辑器，允许用户设计一个包含从数据库收集的数据的可视化文件。该示例使用 Northwind 数据库的相同信息：公司、订单、订单详细信息和产品列表。应用程序使用标准的 Excel 功能来公开字段、重复属性和格式说明符：Name 对象（以及 Names 集合）。在 SpreadsheetML 架构中，替代项是 ss:Names 和 ss:NamedRange 元素。

Excel template trasformation result

模板创建

与 Word 模板创建情况一样，编辑器提供对数据表和数据字段的访问，允许插入重复标记（用于包含许多记录的表）和指定格式变量。代码与 Word Templates Editor 非常相似，除了 Excel API 的使用（Workbooks、Sheets、ActiveWorkbook、ActiveCell、Names 等）。Excel 应用程序托管在用户控件（excelCtl）中，该控件位于 frmExcelControl 窗体上。

Excel Templates Editor

您可以将模板文件以标准的 XLS 或 XML 格式进行管理（创建新文件、打开、保存）。您可以将创建模板所需的任何对象插入到当前打开的 Excel 工作簿中

  • 字段，作为内部名称和活动单元格中的公式
    int tableIndex = cboTables.SelectedIndex;

if(tableIndex >= 0 && fieldIndex >= 0 && 
   excelCtl.Application != null && 
   excelCtl.Application.Workbooks.Count > 0 
   && excelCtl.Application.ActiveWorkbook != null)
{
    item = listFields.Items[fieldIndex].ToString();
    if(item.Trim() != string.Empty)
    {
        excelCtl.Application.ActiveWorkbook.Names.Add(
          cboTables.Items[tableIndex].ToString() + item, 
          cboTables.Items[tableIndex].ToString() + item, 
          missing, missing, missing, missing, missing, 
          missing, missing, missing, missing);
        excelCtl.Application.ActiveCell.Formula = 
          "=" + cboTables.Items[tableIndex].ToString() + item;
    }
}
  • 重复标记，作为内部名称和活动单元格中的公式
    int tableIndex = cboTables.SelectedIndex;
int fieldIndex = listFields.SelectedIndex;
if(tableIndex >= 0 && excelCtl.Application != null && 
   excelCtl.Application.Workbooks.Count > 0 && 
   excelCtl.Application.ActiveWorkbook != null)
{
    string tableName = cboTables.Items[tableIndex].ToString();
    object oIndex = htTablesRepeatMarksIndexes[tableName];
    int index = 0;
    if(oIndex != null)
    {
        index = int.Parse(oIndex.ToString());
    }
    index++;
    htTablesRepeatMarksIndexes[tableName] = index;
    excelCtl.Application.ActiveWorkbook.Names.Add(tableName + 
             repeatAttribute + index.ToString(), tableName + 
             repeatAttribute + index.ToString(), missing, 
             missing, missing, missing, missing, 
             missing, missing, missing, missing);
    excelCtl.Application.ActiveCell.Formula = "=" + 
             tableName + repeatAttribute + index.ToString();
    LoadRepeatMarks();
}
  • 格式说明符，仅作为内部名称
    int languageIndex = cboLanguages.SelectedIndex;
if(languageIndex >= 0 && excelCtl.Application != null && 
   excelCtl.Application.Workbooks.Count > 0 && 
   excelCtl.Application.ActiveWorkbook != null)
{
    string languageName = 
      cboLanguages.Items[languageIndex].ToString();
    object oVariableValue;
    object oVariableName;
    Excel.Name var;

    oVariableValue = (object)languageName;
    oVariableName = (object)"LanguageName";
    try
    {
        var = 
          excelCtl.Application.ActiveWorkbook.Names.Item(
          oVariableName, missing, missing);
    }
    catch
    {
        var = null;
    }
    if(var != null)
        var.Value = languageName;
    else
        excelCtl.Application.ActiveWorkbook.Names.Add(
          "LanguageName", languageName, missing, missing, 
          missing, missing, missing, missing, 
          missing, missing, missing);
}

模板可视化和 CExcelMLFiller 类

该类的使用与 Word CExWordMLFiller 类相似

CExcelMLFiller filler = new CExcelMLFiller(dsData, 
                            xmlTemplateDoc.OuterXml);
if(!filler.OperationFailed)
{
    filler.Transform();
    if(filler.OperationFailed)
    {
        foreach(string err in filler.ErrorList)
        {
            MessageBox.Show(err, "Error", 
              MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        return;
    }
    string copyFileName = Path.GetTempFileName() + ".xml";
    filler.ExcelMLDocument.Save(copyFileName);
    Process.Start(copyFileName);
}
else
{
    foreach(string err in filler.ErrorList)
    {
        MessageBox.Show(err, "Error", 
                   MessageBoxButtons.OK, 
                   MessageBoxIcon.Error);
    }
}
首先，类加载模板内容并设置格式信息（存储在内部名称中），这在构造函数中完成。接下来，Transform 方法将配置了数据字段的相应单元格中的公式替换为数据集中的数据，识别重复的记录（对于包含许多行的表）。在 ExcelML 文件中，仅添加内容非空的记录，因此工作表中的每一行都将是一个模板行。模板行集合被识别，然后，对于每个数据表，行将被填充数据和/或乘以。填充操作完成后，模板行将被删除。
try
{
    tableNode = xmlTemplateDoc.SelectSingleNode(
        "/ss:Workbook/ss:Worksheet/ss:Table", nsmgr);
    templateRowsColl = xmlTemplateDoc.SelectNodes(
        "/ss:Workbook/ss:Worksheet/ss:Table/ss:Row", nsmgr);
    foreach(DataTable dt in dsData.Tables)
    {
        TransformTemplateRows(dt);
    }
    int i = templateRowsColl.Count - 1;
    while(i >= 0)
    {
        XmlNode repeatNode = 
          templateRowsColl[i].SelectSingleNode(
          "ss:Cell[contains(@ss:Formula, '" + 
          repeatAttribute + "')]", nsmgr);
        bool bIsRepeat = repeatNode != null;
        if(bIsRepeat)
        {
            tableNode.RemoveChild(templateRowsColl[i]);
        }
        i--;
    }
    ((XmlElement)tableNode).RemoveAttribute("ss:ExpandedRowCount");
}
catch(Exception ex)
{
    while(ex != null)
    {
        errorList.Add(ex.Message);
        ex = ex.InnerException;
    }
    bOperationFailed = true;
}
TransformTemplateRows 以 DataTable 对象作为参数，并为每个模板行检查该行是重复的还是非重复的。如果是重复的，则该行会为数据表中的每个 DataRow 乘以，否则仅用数据填充。
foreach(XmlNode rowNode in templateRowsColl)
{
    bool bIsRepeat;
    XmlNode repeatNode = 
      rowNode.SelectSingleNode("ss:Cell[contains" + 
      "(@ss:Formula, '=" + dt.TableName + 
      repeatAttribute + "')]", nsmgr);
    bIsRepeat = repeatNode != null;
    string templateFieldName;
    if(bIsRepeat)
    {
        tableNode = rowNode.ParentNode;
        foreach(DataRow dr in dt.Rows)
        {
            XmlNode newRowNode = rowNode.Clone();
            ((XmlElement)newRowNode).RemoveAttribute("ss:Index");
            repeatNode = newRowNode.SelectSingleNode(
              "ss:Cell[contains(@ss:Formula, '=" + 
              dt.TableName + repeatAttribute + "')]", nsmgr);
            ((XmlElement)repeatNode).RemoveAttribute("ss:Formula");
            repeatNode.FirstChild.InnerText = string.Empty;
            tableNode.InsertBefore(newRowNode, rowNode);
            for(int i = 0; i < dr.ItemArray.Length; i++)
            {
                templateFieldName = dt.TableName + 
                  dt.Columns[i].ColumnName;
                ReplaceFieldData(newRowNode, 
                  templateFieldName, dr[i].ToString(), 
                  dt.Columns[i].DataType);
            }
        }
    }
    else
    {
        if(dt.Rows.Count > 0)
        {
            DataRow firstRow = dt.Rows[0];
            for(int i = 0; i < firstRow.ItemArray.Length; i++)
            {
                templateFieldName = dt.TableName + 
                         dt.Columns[i].ColumnName;
                ReplaceFieldData(rowNode, templateFieldName, 
                 firstRow[i].ToString(), dt.Columns[i].DataType);
            }
        }
    }
}
ReplaceFieldData 将数据添加到行节点并尝试对其进行格式化（如果提供了格式信息）。
errorList = new ArrayList();
bOperationFailed = false;
XmlNode dataNode;
XmlNodeList oColl;
oColl = baseNode.SelectNodes("ss:Cell[@ss:Formula='=" + 
                              fieldName + "']", nsmgr);
foreach(XmlNode fieldNode in oColl)
{
    dataNode = fieldNode.SelectSingleNode("ss:Data", nsmgr);
    if(dataNode == null)
    {
        errorList.Add("The field data is selected " + 
          "from the fields definition data source " + 
          "or merge document is corrupted!");
        bOperationFailed = true;
        return ;
    }
    ((XmlElement)fieldNode).RemoveAttribute("ss:Formula");
    if(colType == typeof(DateTime))
    {
        if(dateTimeFormat != null)
        {
            DateTime dt = DateTime.Parse(data);
            dataNode.InnerText = dt.ToString(dateTimeFormat);
        }
        else
        {
            dataNode.InnerText = data;
        }
        //((XmlElement)dataNode).SetAttribute("ss:Type", "DateTime");
    }
    else if(colType == typeof(int)
        || colType == typeof(short)
        || colType == typeof(long)
        )
    {
        if(numberFormat != null)
        {
            int i = int.Parse(data);
            dataNode.InnerText = i.ToString(numberFormat);
        }
        else
        {
            dataNode.InnerText = data;
        }
        ((XmlElement)dataNode).SetAttribute("ss:Type", "Number");
    }
    else if(colType == typeof(decimal)
        || colType == typeof(float)
        || colType == typeof(double)
        )
    {
        if(numberFormat != null)
        {
            decimal d = decimal.Parse(data);
            dataNode.InnerText = d.ToString("N", numberFormat);
        }
        else
        {
            dataNode.InnerText = data;
        }
        ((XmlElement)dataNode).SetAttribute("ss:Type", "Number");
    }
    else
    {
        dataNode.InnerText = data;
        ((XmlElement)dataNode).SetAttribute("ss:Type", "String");
    }
}

模板可视化和 CExcelXLSFiller 类

用于实例化和使用此类​​的代码与 CWordDOCFiller 类相似。

string templateFileName = Application.StartupPath + @"\Templates\Order.xls";
string copyFileName = Path.GetTempFileName() + ".xls";
File.Copy(templateFileName, copyFileName, true);

CExcelXLSFiller filler = new CExcelXLSFiller(dsData, copyFileName);
if(!filler.OperationFailed)
{
    filler.Transform();
    if(filler.OperationFailed)
    {
        foreach(string err in filler.ErrorList)
        {
            MessageBox.Show(err, "Error", 
              MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        return;
    }
}
else
{
    foreach(string err in filler.ErrorList)
    {
        MessageBox.Show(err, "Error", 
          MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}
在构造函数中，可以使用相同的操作（Excel 应用程序实例化、模板副本打开、格式信息加载）。
Tranform 方法获取工作簿中第一个工作表的引用，并设置工作表中初始完成的范围以及该范围的初始公式数组。加载过程中可以通过复制模板行来修改范围。我们必须循环遍历行和单元格，并检查范围是否已更改。此方法调用另外两个方法：TransformRepeatRows（用于需要复制的行）和 TransformRow（用于单个行）。
try
{
    oSheet = (Excel.Worksheet)oWorkbook.Worksheets[1];
    Excel.Range rng = 
      (Excel.Range)oSheet.get_Range("A1", missing);
    rng = rng.SpecialCells(
      Excel.XlCellType.xlCellTypeLastCell, missing);
    string address = rng.get_Address(false, false, 
      Excel.XlReferenceStyle.xlA1, missing, missing);
    Excel.Range oRng = oSheet.get_Range("A1", address);
    Excel.Range rowRng;
    int rowCount = oRng.Rows.Count;
    int colCount = oRng.Columns.Count;
    string formula = string.Empty;
    object[,] arrFormula = (object[,])oRng.Formula;
    bool bIsRepeat = false;
    int i = 1;
    int indexRows = 0;
    int indexRepeatFormula = -1;
    while(i <= rowCount)
    {
        bIsRepeat = false;
        for(int j = 1; j <= colCount; j++)
        {
            formula = arrFormula[i, j].ToString();
            if(formula.IndexOf(repeatAttribute) != -1)
            {
                bIsRepeat = true;
                indexRepeatFormula = j;
                break;
            }
        }
        if(bIsRepeat)
        {
            TransformRepeatRows(i, colCount, formula, 
                  indexRepeatFormula, out indexRows);
            i += indexRows;
            rowRng = oSheet.get_Range("A" + i.ToString(), 
                                      missing).EntireRow;
            rowRng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        }
        else
        {
            TransformRow(i, colCount);
            i++;
        }
        if(rowCount != oRng.Rows.Count)
        {
            rowCount = oRng.Rows.Count;
            arrFormula = (object[,])oRng.Formula;
        }
    }
    oApp.Visible = true;
}
catch(Exception ex)
{
    while(ex != null)
    {
        errorList.Add(ex.Message);
        ex = ex.InnerException;
    }
    bOperationFailed = true;
}
工作表中的范围使用 oSheet 对象的 get_Range 方法找到。使用 Range 对象的 get_Address 方法获取地址，并使用 Range 对象的 SpecialCells 方法找到最后一个键入的单元格。TransformRepeatRows 方法复制指定的模板行并添加相应表中的数据。整个行的引用在范围中由 EntireRow 属性给出。模板行首先通过简单的插入进行复制，然后通过 Copy 方法复制。
indexRows = 0;
string sName = formula.Replace("=", string.Empty);
sName = sName.Substring(0, sName.IndexOf(repeatAttribute));
sName.Replace(repeatAttribute, string.Empty);
Excel.Range oNewRow, oCell;
int newRowIndex = rowIndex;
Excel.Range oRow = oSheet.get_Range("A" + 
        newRowIndex.ToString(), missing);
char colLetter;
foreach(DataTable dt in dsData.Tables)
{
    if(sName.StartsWith(dt.TableName))
    {
        sName = sName.Replace(dt.TableName, string.Empty);
        foreach(DataRow dr in dt.Rows)
        {
            indexRows++;
            oRow = oSheet.get_Range("A" + 
                   newRowIndex.ToString(), 
                   missing).EntireRow;
            oNewRow = oSheet.get_Range("A" + 
                      (newRowIndex + 1).ToString(), 
                      missing).EntireRow;
            oNewRow.Insert(
              Excel.XlInsertShiftDirection.xlShiftDown, missing);
            oRow.Copy(oSheet.get_Range("A" + 
              (newRowIndex + 1).ToString(), missing));
            colLetter = (char)(65 + indexRepeatFormula - 1);
            oCell = oSheet.get_Range(colLetter.ToString() + 
              newRowIndex.ToString(), missing);
            oCell.Formula = string.Empty;

            for(int j = 1; j <= colCount; j++)
            {
                if(j != indexRepeatFormula)
                {
                    ReplaceFieldData(newRowIndex, j, dr);
                }
            }

            newRowIndex++;
        }
        break;
    }
}
TransformRow 方法用于使用从 DataTable 对象的第一行提取的数据来填充模板行。
DataRow firstRow;
foreach(DataTable dt in dsData.Tables)
{
    if(dt.Rows.Count > 0)
    {
        firstRow = dt.Rows[0];
        for(int j = 1; j <= colCount; j++)
        {
            ReplaceFieldData(rowIndex, j, firstRow);
        }
    }
}
ReplaceFieldData 方法将数据添加到行中的单元格，并在可用时使用格式信息。
char colLetter = (char)(65 + colIndex - 1);
Excel.Range oCell = oSheet.get_Range(colLetter.ToString() + 
                    rowIndex.ToString(), missing);
string fieldName = oCell.Formula.ToString();
string tableName = dr.Table.TableName;
Type colType = System.Type.Missing.GetType();
string data;
if(fieldName.Trim() != string.Empty && 
   fieldName.StartsWith("=" + tableName))
{
    fieldName = fieldName.Replace("=", string.Empty);
    fieldName = fieldName.Substring(
                  fieldName.IndexOf(tableName) + 
                  tableName.Length);
    oCell.Formula = string.Empty;
    data = dr[fieldName].ToString();
    colType = dr.Table.Columns[fieldName].DataType;
    if(colType == typeof(DateTime))
    {
        if(dateTimeFormat != null)
        {
            DateTime dt = DateTime.Parse(data);
            oCell.Value2 = dt.ToString(dateTimeFormat);
        }
        else
        {
            oCell.Value2 = data;
        }
    }
    else if(colType == typeof(int)
        || colType == typeof(short)
        || colType == typeof(long)
        )
    {
        if(numberFormat != null)
        {
            int i = int.Parse(data);
            oCell.Value2 = i.ToString(numberFormat);
        }
        else
        {
            oCell.Value2 = data;
        }
    }
    else if(colType == typeof(decimal)
        || colType == typeof(float)
        || colType == typeof(double)
        )
    {
        if(numberFormat != null)
        {
            decimal d = decimal.Parse(data);
            oCell.Value2 = d.ToString("N", numberFormat);
        }
        else
        {
            oCell.Value2 = data;
        }
    }
    else
    {
        oCell.Value2 = data;
    }
}

使用应用程序

解决方案 ExcelDataSetTemplateEditor.root 包含四个项目。

  • ExcelControl – 托管应用程序的用户控件；
  • ExcelDataSetTemplateEditor – 主编辑器项目；
  • NorthwindDA – Northwind 数据库的数据访问组件；
  • Test – 用于可视化指定订单和订单详细信息以及按字母顺序排列的产品列表的测试应用程序。

Templates 文件夹中的模板 Order.xml 和 Order.xls 是 Test 应用程序所需的模板。编译项目时，这些模板会被复制到应用程序启动路径中类似的文件夹中。

结论

MS Office 以非托管代码开发，而且似乎我们不太可能很快拥有“托管”Office。XML 替代方案对程序员访问 Office 文档非常有帮助。在下一个 Office 2007 中，标准文档格式已更改（Word 为 .docx，Excel 为 .xlsx）。它们将只是包含 XML 文件、图片和其他二进制文件的 .zip 存档。zip 包包含相互关联且存储在不同文件夹中的部分。正如预期的那样，XML 格式是 Word 文档的 WordML，Excel 的 SpreadsheetML。 .NET Framework 3.0（在 System.IO.Packaging 中）提供的 API 仅与 Package 和 PackagePart 对象相关，负责打包/解包以及在包中添加部分。没有访问原子元素的 API，因此本文介绍的模板使用者（使用 XML DOM 和 Office XML Schema）将是处理此格式的好方法。

© . All rights reserved.