Excel整合SharePoint打造轻量级BI解决方案-CDA数据分析师官网

热线电话：13121318867

Excel整合SharePoint打造轻量级BI解决方案

2016-04-14

Excel整合SharePoint打造轻量级BI解决方案

本文将带您走进BI世界，并向您讲述如何使SharePoint2007结合Excel提供一个轻量级的BI解决方案。这一点对于渴望使用电子表格的用户极为重要。在本文中的例子截取自一个真实的项目，在这个项目中，某个公司需要从数百个Excel文档、关系型数据库以及Word文档中选取数据，并使用SharePoint2007来展现我们对这些数据的切片和钻取的结果。

这个项目的成功非常清楚地表明BI数据并不一定来自于数据库或其他一些服务程序。它们也可以来自非常流行的管理和报表工具，如Excel。

在本文中我们将会看到在这个工程中解决一些具有挑战的问题，并探索将Excel数据导入到SharePoint2007的一些可供选择的方法。本文还提供了一些VBA代码，这些代码将帮助我们更进一步地格式化Excel数据以符合SharePoint的要求。

一、在项目中面临的挑战

经过和客户的讨论，我们得知他们的重要商业数据（就是一些涉及到100多家投资公司的财务信息）被存储在两个主要的地方：SQL Server2000数据库保存了一些用于应用程序前端的财务数据，而数百个Excel文件则直接保存了每一个公司的财务信息。

我们曾经考虑过使用SharePoint2007的商业数据目录功能，并建立可以连接到SQL Server2000的应用程序定义文件。但这种方案的主要弊病是SQL Server在统计数据时并不是100%准确。而前端的应用程序可以非常容易地建立Excel报表，而且可以让更富有经验的专家人工审核数据。因此，使用Excel验证方式更容易让人信服。所以我们的解决方案是使用SQL Server2000的导出功能将数据导出到Excel中，再使用Excel的验证功能来处理这些财务数据。

在回顾正在从被管理的公司搜集财务数据的Excel电子表格后，我们确定了一个可以编辑的模板标准，这个模板可以支持在SharePoint2007站点中所需的所有数据。因此，第二套设计方案就是用Excel作为这些被管理公司财务数据的数据源。我们可以为每一个被管理的公司（如Company A,Company B,Company C等）建立一个SharePoint2007站点，然后将这些数据处理后，汇总到中心网站（这也称为上钻）。

二、怎么将Excel导出到SharePoint2007中

接下来要讨论的是如何将数据从Excel移动SharePoint2007列表中。除了使用Excel服务外。我们还可以使用如下的方法来解决这个问题：

注：看到这也许有人会问，"什么是Excel服务呢？"，当然，这是一个很自然问题。因为微软最近一直在鼓吹它的Excel服务能力在SharePoint2007推出之后得到了非同寻常的增强。在本文的案例中，要求客户端使每一个Excel单元格和列表中的数据相对应，如果使用Excel服务就可以使数据被发布到SharePoint2007中，并通过Excel Web Access Web将这些数据提供给用户，而不是一个个单元格地将数据移植到SharePoint2007列表中。

另外，Excel服务发布系统只能在Excel2007中使用，因此，这对于客户端要求太苛刻，所以Excel服务目前还很难被广泛采用。如果我们想学习更多的关于Excel服务的知识，可以参考相关的资料。

1. 从Excel电子表格中剪切和粘贴数据到SharePoint2007列表中。

2. 使用SharePoint2007的数据导入功能将Excel的数据导出到SharePoint2007中。

3. 使Excel和SharePoint2007进行同步来导出数据。

4. 使用一个第三方的产品将数据从Excel导出到SharePoint2007中。

    在本文下面的部分将使用一些例子和屏幕截图上述的几种处理数据的方法，读者将会从中看来它们的优劣。
三、从Excel导出到SharePoint2007

    这种方法我们可以立即排除了，因为我们要面对的是成千上万的单元格，而且不止一家公司需要处理。手工去做根本是不可能的。因此，我们可以得出结论，使用这种方法将会给我们带来超大工作量和操作错误。

四、使用SharePoint2007将Excel数据导进来

    在这种方法中，SharePoint2007可以基于Excel的内容建立一个列表（可以通过使用Create命令，然后选择从定制列表部分导入Excel表来实现），但是这么做有一些缺陷。首先，这是一次性导入数据，因此，并没有更容易的方法向加入多余的行，或是当Excel变化后更新已经存在的内容（在本文的实例中Excel文件是按月更新的）。第二，SharePoint2007需要按行组织的数据，而典型的财务报表是按列组织的。如图1显示了一个典型的Excel财务报表，而图2显示了将Excel表格导入到SharePoint2007的最终结果。从这两个图可以看出，使用SharePoint2007的导入功能对于标准的财务报表并不能很好的工作，而且由于是一次性处理，因此，它也不支持表格以后的更新同步操作。

图1 一个典型的Excel财务报表

图2 将Excel数据导入到SharePoint后的结果

但幸运的是，在Excel中有一个工作区的概念。如果我们简单地将数据从一个Excel表单中复制并粘贴到一个另一个表单，在这个粘贴过程中，可以使用变换选项将行列数据互换。图3就是一个互换的结果。我们仍然需要应用一个小的变化以使SharePoint的数据看上去更朋好。我们可以从图3看到相应的变化，在图3中显示了行A的头移动了行B。如果不做这个变化，SharePoint将假设只有在行A，而其他的还是老样子。图4显示了使用这个Excel数据经过进一就处理后的结果。因此，这种方法的结论是使用互换和重新格式化功能来处理的标准财务数据可以非常有效的被导入到SharePoint2007中。

图3 使用互换功能后Excel中的数据

图4

在图5中，现在每一行是一个日期，而每一列代表一种财务值，如实际收入（Actual Revenue）或Budget/Plan Revenue。

图5 将处理后的Excel数据导入到SharePoint2007后的结果

接下来让我们看一下如图6的设置列表，这个列表显示了SharePoint2007中对导入数据的一些错误假设。如它将dates和Revenue都设置成了"Single line of text."。很明显，我们在使用这种方法导入数据时必须事先将数据格式化，才能在SharePoint2007中正确显示。如在Excel的空单元格中使其包含"0"，这样在导入SharePoint后，就可以被当成数值类型而不是文本来处理。因此，使用这种方法的结论是在将Excel数据导出之前需要对其进行必要的格式化，这样才能确保SharePoint能正确识别。

图6 SharePoint的设置对话框

五、使Excel和SharePoint2007同步

    这是我们的第三种方法。要使用这种方法，需要从微软下填鸭式Excel2007，而且还必须将文件保存成Excel2003的格式，否则无法同步。

    为了将Excel数据发布到SharePoint2007中，并使它随着数据的变化进行同步，我们必须从微软下载一个Excel2007插件。

    在安装和配置完这个插件后，我们将在Office Ribbon上的标签上找到一个"Publish and allow Sync"按钮，这个按钮可以让我们将Excel数据发布到SharePoint2007中。现在我们可以发布一个读-写列表到SharePoint2007中，但是这个操作只能在和Excel2003兼容的模式下进行。如果我们将文档保存成Excel2007的格式，就不会发生同步事件。如果我们保存成Excel2007的格式，Excel会提示我们此功能失效。

现在我们可以访问Excel2007的设计标签了，然后单击" Publish and allow Sync"按钮。为了正确发布，我们还需要输入SharePoint2007的网站名以及列表名。图8显示了将一个Excel表导出到SharePoint2007后的结果。

图8 变化后的最终结果

    为了使Excel和SharePoint2007同步，返回到Excel中，右击单元格，选择"Table, Synchronize with SharePoint."。如果我们在Excel或SharePoint2007中修改数据。但未进行同步。下一个用户进入程序时将会收到一个选项“load the pending changes.”因此，我们可以断定，使用这种方法可以使Excel和SharePoint2007列表进行双向同步。

六、使用第三方工具导出数据

    对第最后一种方法，使用一个第三方的工具来导入数据。我们也可以有很多选择。如一个叫Bamboo Solutions的公司提供了一种叫List Bulk Import的产品，它可以将数据和文档移植到SharePoint2007中，而且可以是不同的数据源，如数据库，Excel表，或是老版本的SharePoint。换句话说，这个产品完全可以实现上述方法中描述的功能。

    在List Bulk Import中，操作更象SharePoint2007，它希望数据被组织成行，而不是列。在这个产品中有一个强大的接口，可以将Excel的值映射到SharePoint列表中，然后提供选项来检查在SharePoint列表中的复本数据，并更新已经存在的数据，然后可以建立一个导入工作计划。我们还可以将导入模板保存在xml文件中以便重复使用或编辑。这个产品还可以产生一个日志文件，提供一个对导入是否成功的跟踪。我们可以查看Bamboo Solutions的网站以得到更详细的信息。从上面的描述看，使用这个工具要比使用其他方法更可靠，总之，它是一个强大的工具。

七、使用Excel宏预处理要导出的数据

从上述的讨论可以看出，标准的财务报表所组织的数据对于SharePoint列表并不朋好（这点可以从图1和图2看出）。除非公司自愿改变它们的财务报表格式（这几乎是不可能的），否则我们就需要使用某种方法来格式化这些数据。幸运的是，在Excel中提供了一种强有力的宏引擎。我们可以使用它们非常容易地格式化数据。

下面列表显示了一些需要处理的任务，不管我们选择哪种方法进行数据导出，都可以使用如下的方法进行自动格式化任务：

1. 将数据从标准的行格式转换成列格式。在List Bulk Import的最新版本已经提供了这个功能。但如果我们不想使用它或没有最新版本，就只能使用宏来做这件事了。

2. 在转换后，我们将移动一些列标题，将它们放到同一行，代码如下：

' 'Consolidate header information into a single column in those 'cases where the headers are split between two columns. ' 'Parameters: xlWS, the Worksheet object to manipulate ' strCol, the column to be edited ' PublicFunction EditHeaders(xlWS As Excel.Worksheet, _ strCol As String) As BooleanDim iRowCount As Integer Dim i As Integer Dim strRange As String DimstrHeader As String Dim xlCell As Range iRowCount =xlWS.UsedRange.Rows.Count strRange = strCol & "1:" & strCol & iRowCount ForEach xlCell In xlWS.Range(strRange) strHeader = xlCell.Value If (strHeader<> "" And xlCell.Offset(0, 1) = "") Then xlCell.Offset(0, 1).Value =strHeader End If Next xlCell End Function

3. 财务报表经常在应该包含数字的单元格包含文本。典型的例子是在单元格中包含"NA"。SharePoint在一个为数值类型的单元格中并不允许有文本，因此我们需要使用如下代码进行转换：

'Edit the cells with "NA". 'Parameters: xlWS, the Worksheet object to be edited PublicFunction EditFormulas(xlWS As Excel.Worksheet) As Boolean xlWS.Columns.Replace"NA", "" End Function

在本文中介绍了四种从Excel导出数据到SharePoint的方法。虽然本项目使用了第三方工具来完成这个任务。但这不是必须的，如果我们不需要那么复杂的功能，如不需要保留日志，也可以使用其他的方法。总之，保证数据的准确性是进行进一步商业分析的有力保证。

CDA数据分析师考试相关入口一览（建议收藏）：

▷ 想报名CDA认证考试，点击>>> “CDA报名” 了解CDA考试详情；