Translator's description: Welcome to my blog: http://daidaoke.donews.net/daidaoke/
The mistake in the translation is not to point out.
My email: tangtaike@163.com
For reprint, please notify us in advance.
Load data
Because the spreadsheet component is not an application, it is very important to "get data from where to get data" and "how to save its data". However, the answer to these issues is more complicated than answers to these issues. However, good news is that the spreadsheet component can load and save data in a variety of ways, you can flexibly use these ways in the solution.
Unlike applications, components do not "have" storage devices used by containers. Save this form or document into a permanent device, as well as reloading from the permanent device, is the task of the container. Containers typically require components to save its current state in a stream or property package; then insert these data into a saved form or document. Because of this, any component is subject to varying degrees to the control of the container in which it is conveniently loaded and saved. It is not necessary to say that not all containers are the same; some containers are better than other containers. Because of this, we are very flexible in how the spreadsheet components are loaded and saved. In fact, you can load data into spreadsheet controls in 4 ways:
n Release an electronic form or area from Excel2000 by interaction.
n Copy a zone from Excel2000 to the spreadsheet control.
n When the spreadsheet control is located in the designer (such as Microsoft FrontPage 2000, Microsoft Script Editor, Microsoft Visual InterDect, and Microsoft Visual Basic), you can enter data or a new formula directly in the control.
n Specify a URL to load data, this URL returns an HTML document, where at least one HTML form is included. In addition, it is also possible to load text data (CSV) separated from one URL.
Released from Excel
Release spreadsheet or area from Excel2000 in a way, you will prompt Excel to create an HTML file, which contains a
If you want to try the feature released from the Excel2000, open the work you need to publish, select "Save As Web Page ..." from the File menu. You will see the dialog shown in Figure 2-4.
Figure 2-4 Select Save As Web Page command to display this dialog
Select the Select this selection and select "Add Interaction" this simplified check box. This way when you save, Excel generates a page containing the spreadsheet control, and a copy of the selected content instead of saving the content as a static HTML file. If you need more advanced control on the published content, you can click the "Publish" button to display the "Publishing as a web page" dialog.
You may encounter an error message when you try to import some spreadsheets into HTML. If the source spreadsheet is liable for password protection (by using tools | Protection | Protection Worksheet commands), Excel will not allow spreadsheets or any zone to the web page. Because the web page is a plain text file, anyone can open, browse, and modify it in any text editor, so if so, the security will be destroyed. When the author of the spreadsheet needs to prevent the user from modifying a certain part, the spreadsheet will often password protection. For example, the company's spending report in Excel is often password protected to prevent employees from being modified by the formula of the validity verification rule. Note that you can also lock most units by using the Protection feature so that users can only modify those units that you specify can be updated. As long as you don't use your password to protect the spreadsheet, you can publish or copy the spreadsheet to the spreadsheet component, and all the protection settings will be retained.
Copy and paste
Both Excel 2000 and spreadsheet components read and write an area of an extended HTML table format. This format extends an additional property that enables information importing information into Excel on HTML to implement additional properties and XML (Extension Markup Language) code. This means you can copy the area from the Excel2000, and paste into the spreadsheet control, or in turn, for the creation spreadsheet, and copy the data seen in the spreadsheet control to Excel to make further analysis work It is very useful for it.
When copying the paste area, you should pay attention to some problems. First, if an element in the region contains a formula, this formula references a unit in this area (in another workgroup table, or in another work, "then Excel will only copy the current Value, not copy formula. Let us carefully consider this question: If you copy a formula containing a reference area outside the unit, then the spreadsheet control cannot process this formula, so any data cannot be displayed. Therefore, any reference to the copy area outer unit will be converted to a text value equal to the value referenced when the copy is copied.
Second, the EXCEL spreadsheet, such as a data perspective, only copies of document data, without copying the structure of the data perspective (in other words, the paste can not pass through the perspective or drill To get more information). Office Web Components performs data perspective by inclusion of data perspective components. The chart also cannot be pasted root because the spreadsheet component cannot host other controls or floating graphics.
Third, the protection setting of the spreadsheet not only affects how content is released, but also affects how content is pasted into the spreadsheet component. If the source spreadsheet is protected by password, the area can still be copied, but only the text value is pasted into the spreadsheet component. If the spreadsheet is protected, it is not password, and the area will be copied normally.
Also pay attention to an interesting thing, the format that pastes into the spreadsheet component is HTML, so any applications that can copy HTML to the clipboard can also be used to enter data to the spreadsheet component. Although Excel2000 is indeed embedded in data in data to the clipboard, for example, formula and a full-precision value of a given unit. However, if other applications copy to the HTML table that does not contain additional information into the clipboard, the table will also paste into the spreadsheet component in the form of text data attached.
Enter directly in the spreadsheet component
About this method, in addition to the function of Many convenient creative spreadsheets in Excel, there is no much to say in the spreadsheet components. However, you can still enter the unit reference by selecting units when entering the formula, format information, hide, and display the title bar, toolbox, column, and row headers, and table lines through the attribute toolbox when entering the formula. In practice, the most often used is to create your spreadsheet in Excel2000, then publish and copy the contents and copy to the spreadsheet component after completion. However, if it is a simple spreadsheet, you may feel that you only connect to the input model in the spreadsheet component. Previously, not all containers can be conveniently activated in the design phase and interact. The functions directly entered directly in the spreadsheet component can be implemented only in containers that allow controls to activate and save controls.
Using spreadsheet components in Visual Basic and FrontPage
For spreadsheet components, Visual Basic and FrontPage are very excellent containers; however, some techniques can help you, making editing the process easier.
In Visual Basic, as long as you click the spreadsheet component, it will be activated interface. This will make it difficult to move the control in the form, because the result of clicking and dragging operations can only be selected. However, you can move the entire control by clicking on the title bar and dragging. If the title bar is invisible, you can display it by setting the DisplayTitleBar property to True, and then display it when it is set, and then it is hidden by setting the property to false.
When editing spreadsheet components in Visual Basic or FrontPage, you should also avoid using Autofit properties (set it to false). The use of Autofit attributes in Visual Basic is dangerous, because in this case, whether or not there is any content to the spreadsheet control, it adjusts the size to adapt to these contents so that the scroll bar is not displayed. If the content is larger than your Visual Basic's form, the control immediately adjusts the size of the edge of the form. However, if you determine that the content is smaller than the form, and the control will never get the content larger than the form, then use autofit or safe.
In FrontPage2000, the Autofit property does not work in the normal view; however, the preview view can work correctly in the web browser.
Data from the URL
The URL is the most strange, but it is also the most powerful mechanism for loading the data into the spreadsheet component. Using the Properties Toolbox or Programming Model, you can load the spreadsheet control by opening a specific URL and loads the first HTML table in the URL. CSV (textualized text) lacks information about formatting or formula, so you can only load raw data from the CSV stream. The data is retained in the file referred to in the URL, and the spreadsheet control loads this data at each initialization. Of course, this URL can conveniently point to a Microsoft's Dynamic Server Page (ASP) or a CGI program, this page or program generates an HTML table from an enterprise database or other storage system, so that you can dynamically, The latest data is loaded into the spreadsheet control.
Note that the spreadsheet component uses the security mechanism in IE to ensure that data is not loaded from a domain name other than the domain name where the home page is located (depending on the security settings in your IE). This feature prevents malicious developers from sending a web page that contains the spreadsheet control and the script running when loading. If the security feature setting is not suitable, hackers can use the current user's certificate to load sensitive data and send data to another to view it in detail for him (or her).
This feature uses the security settings set by the client in IE, so the user thinks that the site's site is "trusted", they can close the cross-domain access warning. If the site is not within the trusted site, or only the lowest security settings, the spreadsheet control will warn the user before accessing a URL from any domain name except the home page. If the URL where the original page and the load are in the same domain, the spreadsheet component does not display any warning information because this situation is considered secure. I will discuss safety in the previous chapter. The security mechanism of the spreadsheet component and the security mechanism when accessing the database (Chapter 5 will talk more) have some differences: When the URL pointing to the site is not within the range of trusted sites, it will not allow users to have The opportunity is interviewed across domain. At this time, the spreadsheet control only returns an error value and tells the user that the operation is not allowed, but in the database access, the user can choose whether to perform cross-domain access.
Unfortunately, the spreadsheet control cannot load data directly from a binary XLS file, but it can be loaded from one URL as an Excel file as described above. This allows developers to create and maintain an spreadsheet model using Excel2000. In addition, developers are allowed to load the model directly into the spreadsheet control at runtime.
Is the different sharing on the same file server different domains?
When the OWC product development cycle is close to the end, a tester submits a bug about the spreadsheet component, saying that even if the URL of obtaining data and the original HTML page are located on the same file server, the component also shows a security warning message. We are confused, because it seems to be a trusted environment, but because we use IE security programs to decide whether two URLs come from the same domain, we pushed BUG to the IE group.
The results found that the URL of the page and the URL of the data did point to the same file server, but it points to the different shares of the file server. The IE group explained that from technical this is indeed two different domains, because using a huge file server in a mechanism to provide services for different organizations in the organization, they are very ordinary things, and those have access to some People who share permissions do not have access to another shared permission.
Therefore, if you use file sharing access rather than a web server, remember security, different sharing on the same file server is considered different domains. However, this is only applicable to file sharing access, not applicable to access Web servers via HTTP.
save data
Like the load data into the spreadsheet components, it is not as simple as operating in the Excel application, and saving data from the spreadsheet control can happen in a variety of ways, which makes it difficult to explain it.
Most containers do not allow users to save forms or documents in operation. For example, a Visual Basic form is not a direct save mechanism at runtime. IE has some different: When the page is displayed, it provides a "saving as" mechanism, but it does not allow the user to change the original page from the web server. (If this is allowed, then any hacker can modify your company's homepage!) We often encounter this situation: When we demonstrate Office Web Components to customers, one of them flashed, they You can change the contents of the spreadsheet control like use a shared file, and save it to the web server. But this is impossible, because the principle of web work is not allowed - unless IE can return a new version of page to the web server, most of the server is not allowed at all.
In order to overcome these problems, we have developed four ways to save data from spreadsheet controls:
n Use tools such as FrontPage2000 to open the web page for editing, complete the modification, and save the page to the web server. IE5 provides a new "Using ... Edit" command on the File menu to quickly load the page you browse to a registered HTML editor. n Use the "Export to Excel" button on the toolbar of the spreadsheet control to export its content into Excel2000, in Excel 200 you can store it as an Excel work, or how it is originally filed Published to the web server.
n copies the contents of the spreadsheet control to the clipboard and paste it into Excel2000.
N Developers can use the HTMLDATA property to get current content in the form of text streams and submit it in the ASP page or CGI program to store it on the server. This technology will be demonstrated in Chapter 8.
Appendix: English
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 ..................
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
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 Depiccted in Figure 2-4.
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 will not 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 not 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, you don't use the spreadsheet, you can publish or copy the spreadsheet to the spreadsheet component and all the protection settings will be preserved.
Copying and pasteing
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 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 not 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 will not 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 is not 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 .................. ..
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 is not 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 does not 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 the send tria Data to another location for his or mer Examation.
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 will not 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 allowe D, WHEREAS in The Database Access Scenario, The User Can Approve The Cross-Domain Access if He or She Chooses.
Unfortunately, the Spreadsheet control can not 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 the URL it was getting data from was on the same file server as the original HTML page. We were though 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 do not 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 does not 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 is not 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 develop 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.
