使用 ActiveX 创建数据透视表和图表

• 下载源代码 - 3.55 KB

引言

我们（开发者）大多数人在应用程序中使用“生成 Excel”功能。这允许我们为任何报表生成 Excel 副本。我们大多数人也熟悉“数据透视表”和“数据透视分析图”。 MS Excel 的此功能允许最终用户通过尝试和观察输入（来自可用集合）的变化来制定和可视化。此功能可以通过使用“Interop.Excel”DLL 轻松实现在 ASP.NET 中。但是，要使用该 DLL，您需要在服务器上安装“MS Office”。如果服务器上未安装“MS Office”怎么办？或者，如果服务器是共享服务器，并且上面驻留有多个应用程序？安装“MS Office”可能不可行。在这种情况下，在客户端生成数据透视表和数据透视分析图似乎是更好的方法。这种方法不仅可以提高性能，因为 Excel 和数据透视表是在客户端生成的，而且还可以轻松实现。

背景

本文介绍如何生成包含客户端数据透视表和图表的 Excel。

使用代码

在 DataGrid 中显示报表

我们首先需要将数据的内容显示到报表中。为此，我们只需将 DataGrid 添加到网页，然后将数据（来自 DataTable）绑定到 DataGrid。在此之后，我们需要将数据的所有内容保存到“Javascript 数组”中，以便可以在客户端获取和操作所有数据。此外，建议将行数和列数保存在隐藏变量中。请注意，我们并非总是需要将列名保存在隐藏变量中，但当我编写代码时，我使用了同一个页面为大约十四个报表生成 Excel，以及数据透视表和数据透视分析图。您可以轻松删除列名的代码并将其硬编码。此外，我们还需要将标题和方向保存在隐藏变量中，我们将在后面的部分中看到它们的详细信息。

Dim strBldrForPivot As New StringBuilder("<script_ language="JavaScript">" & _
    vbCrLf & "var PivotList = new Array(" & tempTable.Rows.Count & ");" & vbCrLf)
Dim jobRow As DataRow
Dim jobCol As DataColumn
Dim iCnt = 0
Dim iCol = 0
For Each jobRow In tempTable.Rows
 iCol = 0
 strBldrForPivot.Append("PivotList[" & iCnt & "] = _
    new Array(" & tempTable.Columns.Count & ");" & vbCrLf)
 For Each jobCol In tempTable.Columns
     If jobRow.Item(iCol) Is DBNull.Value Or _
        jobRow.Item(iCol) Is Nothing Then

         strBldrForPivot.Append("PivotList[" & iCnt & "][" & _
            iCol & "]= """ & " " & """;" & vbCrLf)
     ElseIf jobRow.Item(iCol).GetType Is GetType(String) Then
         If jobRow.Item(iCol) = "" Then
             strBldrForPivot.Append("PivotList[" & iCnt & "][" & _
               iCol & "]= """ & " " & """;" & vbCrLf)
         Else
            strBldrForPivot.Append("PivotList["& iCnt & "][" & iCol & "] = _
              """ & CType(jobRow.Item(iCol),String).Trim & """;" & vbCrLf)
         End If
     Else
         strBldrForPivot.Append("PivotList[" & iCnt & "][" & _
           iCol & "] = """ & CType(jobRow.Item(iCol), String).Trim & """;" & vbCrLf)
     End If
         'strBldrForPivot.Append("PivotList[" & iCnt &"][" & iCol & _
         '  "] = """ & CType(jobRow.Item(iCol),String).Replace("\", "\\") & _
         '  """;" & vbCrLf)
     iCol += 1
 Next
 iCnt += 1
Next
strBldrForPivot.Append("</script>")
Page.RegisterStartupScript("PivotListing", strBldrForPivot.ToString)

数据绑定到 DataGrid，如下所示

Dim tempTable As DataTable
tempTable = GetDataTable()
DataGrid1.DataSource = tempTable
DataGrid1.DataBind()

行数、列数、标题和方向被分配给隐藏变量，如下所示

hdnRowCount.Value = tempTable.Rows.Count.ToString
hdnColCount.Value = tempTable.Columns.Count.ToString
For iCount As Integer = 0 To tempTable.Rows.Count
     If iCount = 0 Then
        hndName1.Value = "Name"
        hndName2.Value = "Age"
     Else
        hndName1.Value = hndName1.Value + 
       "$" + CStr(tempTable.Rows(iCount -1).Item("Name"))
        hndName2.Value = hndName2.Value + 
       "$" + CStr(tempTable.Rows(iCount - 1).Item("Age"))
     End If
Next

在上述代码段中，我硬编码了值，但在项目中，我从存储过程获取了这些值；这不仅使应用程序可以从一个地方更改，而且使应用程序更易于维护。您会注意到调用了一个名为“GetDataTable”的函数，该函数用于传递数据，我们可以在该函数中调用存储过程。在这里，我为此分配了示例数据

Private Function GetDataTable() As DataTable
    Dim dt = New DataTable
    dt.Columns.Add("Name", Type.GetType("System.String"))
    dt.Columns.Add("Age", Type.GetType("System.Int16"))

    Dim dr As DataRow

    dr = dt.NewRow()
    dr("Name") = "Madhav"
    dr("Age") = 10
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "Vasudeo"
    dr("Age") = 20
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "Vinayak"
    dr("Age") = 30
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "Gopal"
    dr("Age") = 12
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "Ramesh"
    dr("Age") = 18
    dt.Rows.Add(dr)

    Return dt

End Function

在客户端生成 Excel 工作表

我们需要在客户端使用“ActiveX”创建一个 Excel 实例，然后分配源工作表和目标工作表。源工作表是用于填充数据的临时工作表，而数据透视表将在第二个工作表或目标工作表中生成，通过提供源工作表的引用。

var ExlApp = new ActiveXObject("Excel.Application");
ExlApp.Visible = false;
ExlApp.DisplayAlerts = false;

var WorkBook = ExlApp.Workbooks.Add();
var Sheet = WorkBook.ActiveSheet;
var Sheet2 = WorkBook.Worksheets(2);

执行此操作后，我们将复制所有数据到临时工作表或源工作表，如下所示

for(j=0; j < parseInt(colCount);j++)
{
  for(i=0; i < parseInt(rowCount) + 1; i++)
  {
     letterCount =  CharIndex + parseInt(i + 1);
     if(i == 0)
     {
        Sheet.Range(letterCount).Value = arrHead[j];
     }
     else
     {
        Sheet.Range(letterCount).Value = PivotList[i - 1][j]; 
     }
  }
  CharIndex = incrementAlphabet(CharIndex);
}

函数“incrementAlphabet”仅用于 Excel 中的列从“A”开始递增字母。该函数如下

function incrementAlphabet(alphaIncrement)
{
 indexOfLetter = alphabets.search(alphaIncrement);
 if (indexOfLetter+1 < alphabets.length)
 {
   return(alphabets.charAt(indexOfLetter+1));
 }
 else
 {
   return(alphaIncrement);
 }
}

更简单的方法是直接复制 DataGrid 的“InnerHTML”，然后粘贴到源工作表中。可以如下所示完成

var objToExport = document.getElementById('DataGrid1'); 
var sourceHTML = objToExport.outerHTML;
WorkBook.HTMLProject.HTMLProjectItems("Sheet2").Text = sHTML;

生成数据透视缓存并创建数据透视表

一旦所有数据都复制到源工作表，我们需要将其缓存到“数据透视缓存”以生成“数据透视表”。数据缓存后，我们可以创建数据透视表。创建“数据透视表”后，我们将“标题”及其相应的方向分配给“数据透视表”。“标题”的方向包括“行字段”、“数据字段”、“列字段”、“页字段”或“隐藏字段”。

var ptr = WorkBook.PivotCaches()
var pc = ptr.Add(1, "Sheet1!R1C1:R" + rowCountTemp+ "C" + colCount)
pc.CreatePivotTable("Sheet2!R1C1", "PivotTable1");

with(WorkBook.Worksheets(2).PivotTables("PivotTable1"))
{   
    for(i=0; i< parseInt(colCount);i++)
    {
        with(PivotFields(arrHead[i]))
        {
            Orientation = arrOrnt[i];
            Name=arrHead[i];
            a = Name;
            tempString = Name;
        }
    }
}

我们有以下 Orientation 值

• 隐藏 – 0
• 行字段 – 1
• 列字段 – 2
• 页字段 - 3
• 数据字段 – 4

在数据字段中，我们可以分配各种函数，例如“Sum”、“Average”、“Min”、“Maximun”等。“Sum”是默认函数，而“Count”的值为 – 4112。探索此的最佳方法是创建一个宏并设置断点，然后在即时窗口中尝试各种组合。

生成图表

生成“数据透视表”后，我们需要为“数据透视表”的“图表对象”提供引用，以便“表”和“图表”保持同步。我们还向图表对象提供其他维度以及数据源，如下所示。

var rang = Sheet.Range("A1:C" + rowCount); 
var rang2 = Sheet2.Range("A1:C" + rowCount);
var ch1 = Sheet2.ChartObjects.Add(rang2.Left + rang2.Width, rang2.Top, 350, 220); 
ch1.Chart.ChartType = -4120; 
ch1.Chart.SetSourceData(rang2, 2);

删除源工作表并释放 Excel 对象

一旦创建了“数据透视表”和“图表”，我们就需要删除源工作表。此外，还需要使“Excel 对象”对用户可见和可编辑。我们还需要使用所有项填充“数据透视表”，并使其可供最终用户使用，这将是程序的最后一步。

WorkBook.Worksheets(2).Name = _
  "Pivot_Table_Report"; WorkBook.Worksheets(1).Delete(); 
WorkBook.Worksheets(2).Delete(); 
WorkBook.HTMLProject.RefreshDocument();
ExlApp.UserControl = false; 
ExlApp.Visible = true;

我们使用以下命令使用所有项填充“数据透视表”

WorkBook.Worksheets(2).PivotTables("PivotTable1").DisplayImmediateItems = true;

请注意，在“MS Office 2007”中，以下行可能会引发错误，因此我们需要将其添加到 try – catch 块中，以防某些最终用户使用“MS Office 2007”。

WorkBook.HTMLProject.RefreshDocument();

高亮显示标题

有很多种方法可以高亮显示“标题”，但我选择了一种更简单的方法。在这里，我们只需在电子表格中搜索“文本”/“标题”并更改其背景和前景色。

WorkBook.Worksheets(2).Cells.Find(tempString).Interior.ColorIndex = 10;
WorkBook.Worksheets(2).Cells.Find(tempString).Font.ColorIndex = 6;

在 Web 浏览器中启用 ActiveX 组件

优点

在所有众所周知的客户端生成“数据透视表”的方法中，如 OWC、嵌入 Excel 宏以及其他几种方法，此方法要好得多。宏有很多限制；此外，OWC 组件在开发人员中并不流行。