Programming MS Office 2000 Web Components第二章第一节（第三部分）
译者说明：欢迎访问我的Blog: http://daidaoke.donews.net/daidaoke/
译文中的错误望不吝指出。
我的Email：tangtaike@163.com
如需转载，请事先通知。
装 载 数 据
因为电子表格组件不是应用程序，所以关于”它从何处获得数据”和”如何保存它的数据”的问题就显得十分关键。但是，电子表格组件针对这些问题的答案比起应用程序针对这些问题的答案要复杂的多。不过好消息是电子表格组件可以通过各种方式加载和保存数据，您可以在解决方案中灵活的使用这些方式。
与应用程序不同，组件不”拥有”容器所使用的存储设备。将这个窗体或文档保存到永久性的设备中，以及从永久性设备中重新装载的工作，都是容器的任务。容器通常会先要求组件将它当前的状态保存在一个流或属性包(property bag)中；然后将这些数据插入到正在保存的窗体或文档当中。正因如此，任何组件在方便的装载和保存数据时，都要不同程度的受到它所在的容器的支配。不必说，并不是所有的容器都一样；一些容器要比其它的容器出色。因为认识到这一点，在电子表格组件如何装载和保存数据方面，我们设计的很灵活。实际上，您可以通过4种方式将数据装载到电子表格控件中：
n 通过交互从Excel2000中发布一个电子表格或区域。
n 从Excel2000向电子表格控件中拷贝一个区域。
n 当电子表格控件位于设计器(例如Microsoft FrontPage 2000，Microsoft Script Editor，Microsoft Visual InterDev，以及Microsoft Visual Basic)中时，可以直接在控件中输入数据或一组新的公式。
n 指定一个URL来装载数据，这个URL会返回一个HTML文档，其中至少包含一个HTML表格。此外，也可以从一个URL处装载以逗号分隔的文本数据(CSV)。
从Excel中发布
从Excel2000中通过交互的方式发布电子表格或区域，会提示Excel创建一个HTML文件，其中为电子表格组件包含了一个<object>的标签。Excel将所选择的电子表格或区域的内容拷贝到HTML页面文件中，作为<object>标签的一个参数，因此一旦数据被发布，它就不再引用原来的电子表格。然而，您可以方便地从Excel中重新发布内容，因为Excel会在页面文件中使用新的内容代替以前的内容，同时保留您对页面其它部分所作的修改。
如果想要尝试一下从Excel2000中发布的功能，请打开您需要发布的工作薄，从”文件”菜单上选择”另存为Web页面…”的命令。您就会看到图2－4所示的对话框。
图2－4 选择”另存为Web页面”命令，来显示这个对话框
选中”选择”这个选择项，并选择”添加交互”这个简化复选框。这样当您保存时，Excel就会生成一个包含了电子表格控件的页面，以及所选择内容的一个拷贝，而不是将内容保存为一个静态的HTML文件。如果需要对发布的内容进行更高级的控制，可以点击”发布”按钮，以显示”发布为Web页”的对话框。
当试图将某些电子表格导入HTML中时，您可能会遇到错误信息。如果源电子表格是受口令保护的(通过使用工具|保护|保护工作表命令)，Excel就不会允许将电子表格或任何区域发布到web页面中。因为web页面是纯文本的文件，任何人都可以在任何文本编辑器中打开，浏览和修改它，所以如果允许这样作的话，就会破坏安全性。当电子表格的作者需要防止用户修改某部分时，电子表格就会常常被密码保护。例如，Excel中的公司开支报告就常常被密码保护，以防止员工不能修改有效性验证规则的公式。
请注意，您还是可以通过使用”保护”功能来锁定大多数的单元，以使用户只能修改那些您指定可以更新的单元。只要您不使用口令来保护电子表格，您就可以将电子表格发布或者拷贝到电子表格组件中，而且所有的保护设置都会被保留。
拷贝和粘贴
Excel 2000和电子表格组件都会读写一种扩展的HTML表格格式的区域，这种格式在HTML上扩展了能够实现专门将信息导入到Excel中的功能的附加属性和XML(扩展标记语言)代码。这就意味着您可以从Excel2000中拷贝区域，并粘贴到电子表格控件中，或者反之，对于创建电子表格，和将电子表格控件中所看到的数据拷贝到Excel中以作进一步的分析的工作来说，这都是很有用的。
当拷贝粘贴区域时，您应该注意一些问题。首先，如果区域中的一个元素包含一个公式，而这个公式引用了一个不在这个区域中的单元（在另一个工组表，或在另一个工作薄中），那么Excel会只拷贝该单元的当前值，而不会拷贝公式。让我们认真考虑这个问题：如果您将一个包含了拷贝区域外单元的引用的公式拷入电子表格控件中，那么电子表格控件就无法处理这个公式，因此也就不能显示任何数据。所以任何对拷贝区域外单元的引用都会被转换为与被拷贝时所引用的值相等的一个文本值。
第二，Excel电子表格中更高级的结构，例如数据透视表，只会拷贝文本数据的单元，而不会拷贝数据透视表的结构(换句话说，粘贴后的数据不能再通过透视或钻取来获得更多的信息)。Office Web components是通过包含的数据透视组件来完成数据透视功能的。图表也根部不能被粘贴，因为电子表格组件不能寄宿其它的控件或浮动的图形。
第三，电子表格的保护设置不仅影响内容如何被发布，还会影响内容怎样被粘贴到电子表格组件中。如果源电子表格是受密码保护的，那么区域还是能被拷贝粘贴，但是只有文本值会被粘贴到电子表格组件中。而如果电子表格是受保护的，但不是通过口令，那么区域会被正常拷贝。
还要注意一件有趣的事，粘贴到电子表格组件中的格式是HTML，因此任何能够将HTML的表格拷贝到剪贴板中的应用程序也能被用来向电子表格组件中输入数据。虽然Excel2000确实在拷贝到剪贴板上的数据中嵌入了其它的信息，例如公式和一个给定单元的全精度值。但是，如果其它应用程序将不包含额外信息的HTML表格拷贝到剪贴板中，表格仍然会以附带格式的文本数据的形式粘贴到电子表格组件中。
直接在电子表格组件中输入
关于这个方法，除了您会发现Excel中很多方便的创作电子表格的功能，在电子表格组件中已不存在之外，没有太多可说的。不过，您仍然可以在输入公式时通过选择单元来快速输入单元引用，通过属性工具箱来格式化信息，隐藏和显示标题栏、工具箱、列和行标头、以及表格线。在实际中，常常采用的方法是在Excel2000中建立您的电子表格，然后在完成后将内容发布和拷贝到电子表格组件中。不过如果是简单的电子表格，您可能会觉得在电子表格组件中只接输入模型更方便。
之前提到过，不是所有的容器都可以在设计阶段方便的激活控件并进行交互的。只有在那些允许控件激活并能保存控件的内容的容器中，才能够实现直接在电子表格组件中录入的功能。
在Visual Basic和FrontPage中使用电子表格组件
对于电子表格组件来说，Visual Basic和FrontPage都是非常优秀的容器；不过，一些技巧可以帮助您，使得编辑的过程更加轻松。
在Visual Basic中，只要您单击电子表格组件，它就会被界面激活。这会使得很难在窗体中移动控件，因为单击和拖动操作的结果可以只是选择了一些单元的区域。不过，您可以通过单击标题栏并拖动来移动整个控件。如果标题栏不可见，可以通过设置DisplayTitleBar属性为true，来临时的显示它，然后再通过设置该属性为false来隐藏它。
在Visual Basic或FrontPage中编辑电子表格组件时，您也应该避免使用AutoFit属性(将它设置为False)。在Visual Basic中使用AutoFit属性是危险的，因为在这种情况下，无论加载了什么内容到电子表格控件中，它都会调整大小来适应这些内容，以便不显示滚动条。如果内容比您的Visual Basic的窗体大的话，控件会立刻调整窗体边缘的大小。不过，如果您确定内容比窗体要小，并且控件绝不会获得比窗体大的内容，那么使用AutoFit还是安全的。
在FrontPage2000中，正常视图下AutoFit属性不会起作用；不过，预览视图下，它可以在web浏览器中正确的工作。
从URL处装载数据
对于将数据装载到电子表格组件中来说，URL是最奇怪，但也是最强大的机制。使用属性工具箱或编程模型，您可以让电子表格控件通过打开一个特定的URL，并加载在该URL处查找到的第一个HTML表格的方式来加载内容。CSV(以逗号间隔的文本)缺乏格式化或公式的信息，因此，您只能从CSV流中加载原始数据。数据是保留在URL所指的文件中的，电子表格控件会在每次初始化时加载这些数据。当然，这个URL可以方便地指向一个Microsoft的动态服务器页面(ASP)或者是一个CGI程序，这个页面或程序从企业数据库或其它的存储系统中动态的生成HTML表格，这样就使得您能够将动态，最新的数据加载到电子表格控件中。
请注意电子表格组件使用IE中的安全机制来保证不会从除首页所在的域名之外的域名中加载数据 (根据您IE中的安全设置)。这个特性防止恶意的开发者向您发送一个包含了电子表格控件及在页面加载时就运行的脚本的web页面。如果安全特性设置的不合适，黑客就可以使用当前使用者的证书来加载敏感数据，并将数据发送至另一个地方，以供他(或她)详细查看。
这个特性使用客户端在IE中设置的安全设置，因此如果用户认为产生页面的站点是”可信的”，他们可以关闭跨域的访问警告。如果站点不在可信任的站点范围内，或者只设置了最低的安全设置，那么电子表格控件在从除首页所在的域名之外的任何域名访问一个URL之前，会警告用户。如果原始页面和加载时所在的URL位于同一个域名中，电子表格组件不会显示任何警告信息，因为这种情况被认为是安全的。
我会在前面的章节中较多的讨论安全性。电子表格组件的安全机制与访问数据库时的安全机制(第五章将会更多的谈到)有一些不同：当URL指向的站点不在被信任的站点范围内时，它是不会允许用户有机会进行跨域访问的。这时电子表格控件仅仅返回一个错误值，并告诉用户该操作不被允许，然而在数据库访问的环境中，用户可以选择是否执行跨域访问。
很遗憾，电子表格控件不能直接从一个二进制XLS文件中装载数据，但是它能如上面所述那样，从一个URL装载存为HTML格式的Excel文件。这就允许开发者使用Excel2000创建和维护一个电子表格模型。此外，还允许开发者在运行时直接将模型载入到电子表格控件中。
在同一个文件服务器上的不同共享是不同的域吗？
在OWC产品开发周期接近尾声时，一个测试人员提交了一个关于电子表格组件的bug，说即使获得数据的URL和原始的HTML页面位于同一个文件服务器上时，组件也显示了一个安全警告消息。我们很困惑，因为这似乎确实应该是一个被信任的环境，不过因为我们使用IE的安全程序来决定是否两个URL是来自于同一个域，所以我们将BUG推给了IE小组。
结果发现页面的URL和数据的URL确实指向同一个文件服务器，但是是指向那个文件服务器中不同的共享的。IE小组解释说，从技术上讲这确实是两个不同的域，因为在一个机构中使用一个巨大的文件服务器来为机构中不同的组织提供服务是很普通的事，而那些有访问某个共享权限的人，并不一定有访问另一个共享的权限。
因此，如果您使用文件共享访问而不是web服务器，记住就安全性而言，同一个文件服务器上不同的共享被看作是不同的域。然而，这只适用于文件共享访问，而不适用于通过HTTP访问web服务器。
保存数据
就象将数据装载到电子表格组件中，并不象在Excel应用程序中操作那么简单一样，从电子表格控件中保存数据可以以各种方式发生，这使得很难解释它。
大多数容器不允许用户在运行状态下保存窗体或者文档。例如，一个Visual Basic窗体在运行时是没有直接的保存机制的。IE有一些不同：在显示页面时，它提供了一个”另存为”的机制，但是它不允许用户改变来自web服务器的原始页面。(如果允许这样的话，那么任何黑客就都能够修改您公司的主页了！) 我们常常遇到这种情况：当我们向客户展示Office Web Components时，他们最先闪现出的假设之一就是，他们能象使用一个共享文件一样，改变电子表格控件的内容，并将它重新保存到web服务器上。但是这是不可能的，因为Web的工作的原理不允许这样做――除非IE能够给web服务器返回一个新版本的页面，而绝大部分的服务器是根本不允许这样的。
为了克服这些问题，我们开发了4种从电子表格控件保存数据的方法：
n 使用例如FrontPage2000这样的工具打开web页面进行编辑，完成修改并将页面保存到web服务器上。IE5在文件菜单上提供了一个新的”使用…编辑”命令，可以快速的将您浏览的页面载入到一个登记的HTML编辑器中。
n 使用电子表格控件的工具条上的”导出到Excel”按钮，快速的将它的内容导出到Excel2000中，在Excel200中您就可以将它存储为一个Excel工作薄或者将它以原始文件的方式发布到web服务器。
n 将电子表格控件的内容拷贝到剪贴板上，并将它粘贴到Excel2000中。
n 开发人员可以使用HTMLData属性来以文本流的形式获得当前内容，并在ASP页面或CGI程序中提交它，从而将它存储在服务器上。在第八章将会演示这个技术。
附录：英文原文
Loading Data Since the Spreadsheet component is not an application, the questions "Where does it get data from?" and "How can I save its data?" are crucial. Yet the answers to these questions are much more complicated than they would be for an application. The good news is that the Spreadsheet component can load and save data in a variety of ways that you can use creatively in your solutions.
Unlike an application, a component does not "own" the storage mechanism used by the container. It is the container's responsibility to save the entire form or document into a persistence mechanism and to reload it. A container usually asks the component to save its current state into a stream or property bag; the container then inserts the data into the middle of the form or document it is saving. Because of this, any component is somewhat at the mercy of its container to make loading and saving data easy. Needless to say, not all containers are created equal; some do a better job than others. Recognizing this, we designed the Spreadsheet component to be flexible in how it loads and saves data. In fact, you've got four ways to get data into the Spreadsheet control:
Publish a spreadsheet or range from Excel 2000 with interactivity. Copy a range from Excel 2000, and paste it into the Spreadsheet control. Type data or a new set of formulas directly into the Spreadsheet control while it's in a designer such as Microsoft FrontPage 2000, Microsoft Script Editor, Microsoft Visual InterDev, or Microsoft Visual Basic. Specify a URL to load data from, one that returns an HTML document with at least one HTML table in it. Alternatively, you can load Comma Separated Values (CSV) data from a URL. Publishing from Excel Publishing a spreadsheet or range from Excel 2000 with interactivity will prompt Excel to create an HTML file with an <object> tag for the Spreadsheet component. Excel copies the content of the selected spreadsheet or range into the HTML page as a parameter to the <object> tag, so once the data is published, it no longer refers to the source spreadsheet. However, you can easily republish content from Excel, and Excel will replace the previous content with the new content, preserving any other changes you made to the surrounding page.
To try publishing from Excel 2000, open your favorite workbook and choose the Save As Web Page command from the File menu. You will then see the dialog box depicted in Figure 2-4.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 2-4. Choose Save As Web Page to display this dialog box.
Choose the Selection option, and check the Add Interactivity check box. When you save, instead of saving the content as static HTML, Excel will write a page containing the Spreadsheet control and a copy of the selected content. For more advanced control over what is published, click the Publish button to display the Publish As Web Page dialog box.
You might encounter an error message when attempting to publish certain spreadsheets to HTML. If the source spreadsheet is protected with a password (using the Tools|Protection|Protect Sheet command), Excel won't let you publish the spreadsheet or any range on it to a web page. Doing so is a breach of security since a web page is only plain text that anyone can open, view, and modify in any text editor. Spreadsheets are often protected with passwords when authors want to keep users from modifying certain parts. For example, a company expense report created in Excel is typically password protected so that employees can't adjust the formulas or validation rules.
Note that you can still use protection to lock most cells and let users change only the cells you've designated as updateable. As long as you don't use a password when protecting the spreadsheet, you can publish or copy the spreadsheet to the Spreadsheet component and all the protection settings will be preserved.
Copying and Pasting Excel 2000 and the Spreadsheet component are both capable of reading and writing ranges in HTML table format, augmented with extra attributes and XML (Extensible Markup Language) code that conveys information specific to Excel. This means you can copy ranges from Excel 2000 and paste them into the Spreadsheet control and vice versa, which is useful for both authoring spreadsheets and copying data seen in a Spreadsheet control to Excel for further analysis.
You should note a few things when copying and pasting ranges. First, if a cell in the range has a formula referring to a cell that's not in the range, that's on another worksheet, or that's in another workbook, Excel will simply copy the current value for that cell but not the formula. Consider the problem for a moment: If you paste a formula referring to a cell that is outside the range of the copied cell into the Spreadsheet control, the Spreadsheet control has no way to resolve that reference and can't show any data. So any reference to a cell outside the copied range turns into a literal value that equals the value of the reference when it was copied.
Second, a more advanced structure in an Excel spreadsheet, such as a PivotTable, will paste only as literal data cells and not as a PivotTable structure. (In other words, you won't be able to pivot or drill to more detail.) The Office Web Components do, however, include the PivotTable component for performing PivotTable functionality. Charts will not paste at all since the Spreadsheet component isn't capable of hosting other controls or floating images.
Third, spreadsheet protection settings affect not only how the content gets published but also how it gets pasted into the Spreadsheet control. If the source spreadsheet is password protected, the range will still copy and paste, but only literal values will be pasted into the Spreadsheet component. If the spreadsheet is protected, but not with a password, the range will paste normally.
It's also interesting to note that the format of data pasted into the Spreadsheet component is HTML, so any application that can copy an HTML table to the clipboard can be used to get data into the Spreadsheet control. Excel 2000 does embed other information in the clipboard data, such as the formula and full-precision value for a given cell. However, if another application copies an HTML table to the clipboard without this extra information, the table will still be pasted into the Spreadsheet control as literal data with formatting.
Typing Directly into the Spreadsheet Component There isn't much to say about this approach, except that you will find many of the features that make authoring spreadsheets easy in Excel missing in the Spreadsheet component. Nevertheless, you can still select cells while entering formulas to quickly enter cell references, set formatting information through the Property Toolbox, and hide or show various elements such as the title bar, toolbar, column and row headings, and gridlines. It's much more practical to author your spreadsheets in Excel 2000 and then publish or copy the content into the Spreadsheet component when done. But for simple spreadsheets, you might find it easier to type the model directly into the Spreadsheet control.
As noted earlier, not all containers make it easy to activate controls at design time and interact with them. Typing directly into the Spreadsheet component will work only in containers that allow controls to activate and that persist the control's content when saving.
Using the Spreadsheet Component in Visual Basic and FrontPage
Both Visual Basic and FrontPage make excellent containers for the Spreadsheet component; however, a few tips can help make the editing experience much easier.
In Visual Basic, the Spreadsheet control will be UI active as soon as you click it. This makes it hard to move the control around the form since clicking and dragging will simply select ranges of cells. However, you can move the entire control by clicking the title bar and then dragging the control. If the title bar isn't visible, set the DisplayTitleBar property to True to temporarily show it, and then set it to False to hide it.
When editing the Spreadsheet control in Visual Basic or FrontPage, you should also avoid using the AutoFit property (leave it set to False). Using AutoFit in Visual Basic is dangerous because the Spreadsheet control will resize to fit whatever content is loaded into it without scroll bars. If that content is larger than your Visual Basic form, the control will resize right off the edge of the form. However, if you know that the content will be smaller than the form and that it will never receive larger content, it's safe to use AutoFit.
In FrontPage 2000, the AutoFit behavior will not work in the Normal view; however, it will work correctly in the Preview view or in the web browser.
Loading from a URL A URL is the most curious yet powerful mechanism for loading data into the Spreadsheet component. Using the Property Toolbox or the programming model, you can tell the Spreadsheet control to load its content by opening a specific URL and loading the first HTML table encountered. Alternatively, you can load CSV data returned from a URL. However, CSV lacks any formatting or formula information, so you will load only raw data values from a CSV stream. The data remains stored in the file the URL points to, and the Spreadsheet control loads the data every time it is initialized. Of course, this URL could easily point to a Microsoft Active Server Pages (ASP) page or a CGI program that generates HTML tables from an enterprise database or another storage system on the fly, enabling you to load the Spreadsheet control with dynamic, up-to-the-minute data.
Note that the Spreadsheet component uses the security mechanism in Internet Explorer to make sure that it doesn't load data from a domain other than the one where the first page originated (depending on your Internet Explorer security settings). This feature prevents malicious developers from sending you a web page containing the Spreadsheet control and the script that ran as the page loaded. If the security feature is not in place, a hacker can use your credentials to load sensitive data and then send that data to another location for his or her examination.
This feature uses the security settings the client specifies in Internet Explorer, so users can turn off cross-domain access warnings if they think the site the page originated from is "trusted." If the site is not in the trusted sites zone or has anything but the lowest security setting, the Spreadsheet control will warn the user before accessing a URL from any domain other than the one the original page came from. If the original page and the URL from which to load come from the same domain, the Spreadsheet component won't show any warnings because the situation is considered safe.
I'll discuss security a bit more in the chapters ahead. The Spreadsheet component's security mechanism is slightly different from security used when accessing databases (more on this in Chapter 5): it will never give the user the opportunity to permit cross-domain access when the URL refers to a site not in the trusted sites zone. The Spreadsheet control merely returns an error and says that it's not allowed, whereas in the database access scenario, the user can approve the cross-domain access if he or she chooses.
Unfortunately, the Spreadsheet control cannot load data directly from a binary XLS file, but it can load from an Excel file saved in HTML format by loading from a URL as described above. This allows developers building a spreadsheet model to use Excel 2000 for creation and maintenance. Plus, it lets developers load that model directly into the Spreadsheet control at runtime.
Is a Different Share on the Same File Server a Different Domain?
Near the end of the OWC product cycle, a tester posted a bug against the Spreadsheet component that said it was showing the security warning message even though the URL it was getting data from was on the same file server as the original HTML page. We were perplexed since it did seem that this should be a trusted scenario, but because we used Internet Explorer's security code to determine whether two URLs were from the same domain, we pushed the bug over to the Internet Explorer team.
It turns out that the page's URL and the data's URL were indeed pointing to the same file server but were pointing to different shares on that file server. The Internet Explorer team explained that technically these are two different domains since it's common to use mammoth file servers for many different groups in an organization, and those that have permissions to one share might not have the same permissions on the other share.
So if you are using file share access instead of a web server, keep in mind that different shares on the same file server are different domains as far as security is concerned. However, this applies only to file share access, not HTTP access to a web server.
Saving Data Just as loading data into the Spreadsheet component is not as straightforward as in the Excel application, saving data from the Spreadsheet control can take place in various ways, making it difficult to explain.
Most containers don't allow the user to save the form or document while it is in a runtime state. For example, a Visual Basic form has no direct saving mechanism when it is running. Internet Explorer is slightly different: it offers a "save as" mechanism for running pages, but it doesn't allow users to alter the original page from the web server. (If that were possible, any hacker could alter your company's home page!) Often, when we show the Office Web Components to customers, one of their first assumptions is that they can change the content of a Spreadsheet control and resave it to the web server like a shared file. Because of the way the Web works, this isn't possible—unless Internet Explorer can return a new version of the page to the web server, which most servers wouldn't allow anyway.
To overcome these issues, we developed four ways to save data from the Spreadsheet control:
Use a tool such as FrontPage 2000 to open the web page for editing, make changes, and save the page to the web server. Internet Explorer 5 offers a new Edit With command on the File menu that quickly loads a page you're viewing into a registered HTML editor. Use the Export To Excel toolbar button on the Spreadsheet control to quickly export its content to Excel 2000, where you can save it in an Excel workbook or republish it to the original file on the web server. Copy the content of the Spreadsheet control to the clipboard, and paste it into Excel 2000. As a developer, use the HTMLData property to retrieve the current content as a text stream and post it to an ASP page or a CGI program that saves it on the server. I'll demonstrate this technique in Chapter 8.
