User’s guide to
SpreadsheetConverter

Framtidsforum I & M AB
Uppsala
Sweden

Copyright © 2004 Framtidsforum I&M AB. All rights reserved.

This document contains proprietary information that is protected by copyright. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system, or translated into another language, without prior written consent of Framtidsforum I & M AB, Uppsala, Sweden.

The information in this document is subject to change without notice. Framtidsforum makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. Framtidsforum shall not be liable for errors contained herein nor for incidental or consequential damages in connection with the furnishing, performance or use of this material.

Contents

Quick start guide. 5

How do I download SpreadsheetConverter?. 5

How do I install SpreadsheetConverter?. 5

How do I convert a spreadsheet to HTML?. 5

How do I add an SpreadsheetConverter web page to my web site?. 6

How do I uninstall SpreadsheetConverter?. 7

Introduction. 8

SpreadsheetConverter: key features. 8

Benefits of using SpreadsheetConverter. 9

Client- and server-based solutions. 10

The different versions of SpreadsheetConverter. 10

Installation. 11

Prerequisite software. 11

First-time installation. 11

Installing updates. 14

Uninstalling the program.. 15

The SpreadsheetConverter server environment 17

Using SpreadsheetConverter. 18

Spreadsheet conversion wizard. 18

Select output format 21

Select cells. 22

Presentation and Interaction. 24

Select location. 31

Verify the generated web page. 34

The SpreadsheetConverter menu. 35

Tips, tricks and recommendations. 36

Adding a converted spreadsheet to your web site. 37

Creating an interactive form as a web page. 39

Creating better user interfaces. 42

Cell-based controls versus floating controls. 42

Tools to simplify advanced input controls. 43

Advanced topics. 49

Support for regional options and languages. 49

Inserting a spreadsheet on an existing web page. 50

Troubleshooting. 51

Technical requirements. 51

Known issues. 51

Error messages. 52

Support 54

Appendix 1: Conversion details. 55

Cell contents. 55

Referencing cells. 55

Page formatting. 55

Cell formatting. 56

Other Excel features. 57

Appendix 2: Supported functions. 58

 

Quick start guide

How do I download SpreadsheetConverter?

  1. Point your browser to www.spreadsheetconverter.com.
  2. Follow the links in the left-hand column to the download location for the appropriate version of SpreadsheetConverter.
  3. When you get to the download page, the download process should start automatically. If it doesn’t, click on the Download Now! button.
  4. Select Open or Run to execute the installation program immediately after download.

How do I install SpreadsheetConverter?

  1. If you selected Open or Run during download, the installation process should start automatically. Otherwise use the Windows Explorer to navigate to the folder containing the downloaded file and double-click on the file name, e.g. xlew_latest.exe.
  2. Carefully read through the license agreement, then click “I Agree” to indicate that you accept its terms. If you don’t, press “Cancel” to terminate the installation.
  3. The installation program suggests a default location for the program files. Press “Install” to accept the default location.
  4. When the Excel Add‑in is installed into Excel, you must approve the use of a potentially insecure macro. This process is slightly different depending on your Excel version and security setting. Ensure that you check the checkbox “Always trust macros from this source” if available, then press “Enable Macros” to approve the installation.
  5. When the installation of SpreadsheetConverter is completed, press “Close” to close the installation program.
  6. If you intend to install SpreadsheetConverter to Java, you should also install the Java 2 Platform, Standard Edition, Software Development Toolkit (J2SE SDK) version 1.3.1 or later on the same PC. The J2SE SDK is available for download at http://java.sun.com/downloads/.

How do I convert a spreadsheet to HTML?

  1. Ensure that Excel is started.
  2. If you intend to create a server-based solution, ensure that the prerequisite server environment is set up using the instructions in the section The SpreadsheetConverter server environment below.
  3. Open the spreadsheet you want to convert.
  4. Verify that the SpreadsheetConverter menu is available in the menu bar.
  5. In the SpreadsheetConverter menu, click “Convert”.
  6. Press “Next” to start the SpreadsheetConverter wizard.
  7. If the “Select output format” page is included in the wizard, select “Pure HTML/JavaScript”.
  8. On the “Select Cells” page, select “Automatic detection” as the selection method. For “Which worksheets?”, select “Selected”. Press “Next” to advance to the next page of the wizard.
  9. On the “Presentation and Interaction” page, just press “Next” to advance to the next page of the wizard.
  10. On the “Select location” page, verify the folder location for the web page that will be created by SpreadsheetConverter. Press the “…” button if you want to change the defaults. Press “Next” to advance to the next page of the wizard.
  11. Press “Finish” to begin the conversion process.
  12. After conversion, a “Messages and Errors” window appears. In most cases there are only informational messages. For troubleshooting, go to the Troubleshooting section of this manual.
  13. In most cases, the generated web page is automatically opened in your default web browser. Verify the results of the conversion. If you are unhappy with the results you should consider modifying the original spreadsheet in Excel before you attempt to modify the web page.

How do I add an SpreadsheetConverter web page to my web site?

  1. Ensure that the file is physically included in your web site, i.e. that it is transferred to the same server as the other web pages in the web site using file transfer, a site management utility or a content management system. This guarantees that the page is physically accessible when requested by one of the users of the web site.
  2. Link the page to the rest of your site. You will need at least one link to the page so that users can navigate to it. If your web site has a menu or similar navigation structure you may want to insert it also on the SpreadsheetConverter page.
  3. Give the SpreadsheetConverter page the same look-and feel as the rest of your site. Open the SpreadsheetConverter page in your favorite web page editor and format it so that it looks like the other pages on your web site.

Note: SpreadsheetConverter is shareware, i.e. “try before you buy”. Publishing on the web is not included in the free functionality. You must have a license for SpreadsheetConverter before you publish converted spreadsheets on the web.

How do I uninstall SpreadsheetConverter?

  1. Open the “Add/remove programs” control panel (Start > Settings > Control Panel > Add/Remove Programs).
  2. Locate “SpreadsheetConverter (remove only)” in the list of programs.
  3. Press “Change/Remove”.
  4. Press “Uninstall” to confirm the uninstall operation.

Introduction

The VisiCalc spreadsheet was one of the first killer applications for personal computers. The reason for this is obvious; you didn’t need to be a programmer to use the enormous functionality in a spreadsheet. The nature of “what you see is what you get” calculations made it easy for both individuals and businesses to create powerful, yet easy-to-use spreadsheets.

SpreadsheetConverter was developed with this same WYSIWYG philosophy in mind. SpreadsheetConverter converts Microsoft Excel spreadsheets into web pages that look and function just like the original spreadsheet. For details on the conversion process, see Appendix 1: Conversion details.

Excel's own "save as HTML" allows you to create a non-interactive web page. Excel then creates a static table with all the values you see in Excel. In non-interactive web pages created by Excel, cell values cannot be changed.

If you instead select to save your spreadsheet with interactivity, you will be able to change the contents of the cells, and the values will be recalculated. Microsoft does this by using a very large ActiveX component (2MB in size). The ActiveX component only supports certain client environments (mainly Windows) and may only be used in clients with a license for the Microsoft Office package.

In contrast, SpreadsheetConverter creates an interactive web page by using embedded JavaScript instructions to do the calculations. Web pages created by SpreadsheetConverter run on any browser, on any OS, and without extra run-time licenses.

SpreadsheetConverter: key features

When developing SpreadsheetConverter we have focused on the following:

Benefits of using SpreadsheetConverter

Using SpreadsheetConverter will save you time and money and your customers will receive increased service and better quality. Other key benefits are:


Client- and server-based solutions

SpreadsheetConverter can convert spreadsheets to standalone programming code that runs either in the browser or in the server.

·         In a client-based solution, the programming code is stored in the web page and executed by the user’s web browser. Formulas and constant values are visible in the web page source code. Recalculation can occur automatically whenever the user changes a cell. The client-based solution offers better interactivity for small and medium sized spreadsheets. Client-based solutions are also more scalable and reduce the network traffic, since the server only has to send the web page once.

·         In a server-based solution, the programming code is stored and run on the web server. Formulas and constant values are not visible to the user. Recalculation must be requested manually by pressing a button after a group of cells have been updated. Server-based solutions simplify advanced integration with back-end systems, e.g. databases. Server-based solution offers increased security, since the calculations are done on the server, and better performance for large spreadsheets.

The different versions of SpreadsheetConverter

SpreadsheetConverter is currently available in three versions:

The server-based solutions require a much more complicated operating environment than the client-based solution. We recommend that you use SpreadsheetConverter to HTML unless you are certain you need a server solution.

Installation

Prerequisite software

First-time installation

Download the SpreadsheetConverter program file

Download the appropriate version of SpreadsheetConverter using the instructions in the Quick start guide above.

Start the installation

If you selected Run or Open during download, the installation process should start automatically. Otherwise use the Windows Explorer to navigate to the folder containing the downloaded file and double-click on the file name, e.g. xlew_latest.exe.

Approve the license agreement

Carefully read through the license agreement, then click “I Agree” to indicate that you accept its terms. If you don’t, press “Cancel” to terminate the installation.

Select program folder

The installation program suggests a default location for the program files. Press “Install” to accept the default location.

Under special circumstances you may want to install the program in a different location than the default. If you are upgrading a previous installation, you should either uninstall the old version first, or install the new version at the same location as the old version. Press the Browse button to select another installation folder.

Enable macros

When the Excel Add‑in is installed into Excel, you must approve the use of a potentially insecure macro. This process is slightly different depending on your Excel version and security setting.

The security warning above appears when the Excel security level is set to High. Ensure that you check the checkbox “Always trust macros from this source” if available, then press “Enable Macros” to approve the installation.

Note: If you decide to disable macros for security purposes, SpreadsheetConverter will not be installed into Excel and cannot be used.

Finish the installation

When the installation of SpreadsheetConverter into Excel is completed, Press OK.

Locate the window for the SpreadsheetConverter installation program (it should look like the example below).

Press the “Close” button to end the installation program.

Installing updates

To upgrade to later versions of SpreadsheetConverter, simply repeat the installation process described above using the updated program file. You don’t need to uninstall the old version before you install the new one. The installation program will automatically replace old files and remove redundant files. Note: version 3 does not replace version 2. To uninstall version 2, follow the instructions in Uninstalling the program below.

Uninstalling the program

1.      Open the “Add/remove programs” control panel (Start > Settings > Control Panel > Add/Remove Programs).

2.      Select “SpreadsheetConverter Version 3 (remove only)” in the list of programs.

3.      Press “Change/Remove”.

4.      Press “Uninstall” to confirm the uninstall operation.

Consider the remaining registry information

The uninstallation of SpreadsheetConverter will not remove information it may have saved in the Windows registry, e.g. the registration key, user settings etc. If you reinstall SpreadsheetConverter later, it will reuse the old registration key and settings. This is in conformance with Microsoft recommendations. The remaining registry information occupies a negligible amount of hard disk space. Please contact support@spreadsheetconverter.com if you need instructions on how to remove the redundant registry entries.

Note: The fact that the SpreadsheetConverter registration key remains in a computer does not override the license agreement. If you have a one-user license, you may install and use SpreadsheetConverter on only one computer at a time.

The SpreadsheetConverter server environment

In a server-based solution, the SpreadsheetConverter converter generates programming code to be run on Microsoft’s Internet Information Server (IIS) or on a web server that supports JSP pages and Java Beans (e.g. Tomcat). Describing the complete server installation in detail is beyond the scope of this document. Below is a short summary of the most important issues.

SpreadsheetConverter to ASP and ASP.NET

To use SpreadsheetConverter with ASP.NET requires the Microsoft .NET framework. If you plan to install IIS, uninstall the .NET-framework first, then install IIS using the instructions below, then install the .NET framework again.

SpreadsheetConverter to ASP and ASP.NET requires IIS, which is provided with all current versions of Windows except Windows XP Home. IIS is not always installed during Windows installation. To install IIS manually, open the “Add/Remove Programs” control panel and select “Add/Remove Windows Components”. Tick the checkbox next to IIS in the list and press “Next” to proceed through the installation wizard.

SpreadsheetConverter to Java

The Java version of SpreadsheetConverter requires the following:

·         The Java 2 Platform, Standard Edition, Software Development Toolkit (J2SE SDK) version 1.3.1 or later, available for download at http://java.sun.com/downloads/.

Tomcat issues

When you convert a spreadsheet a second time, the converted files will be copied to the Tomcat server environment. However, Tomcat does not automatically reload the class-file, so the web page will not work.

There are two circumventions to this problem:

Since this is rather time-consuming, we recommend that you use a client-based solution to get the output exactly the way you want it before you convert the spreadsheet for a server-based solution.’

Using SpreadsheetConverter

Spreadsheet conversion wizard

Open the spreadsheet and start the conversion

Ensure that Excel is started.

If you intend to create a server-based solution, ensure that the prerequisite server environment is set up using the instructions in the section The SpreadsheetConverter server environment above.

Open the spreadsheet you want to convert.

If you have previously attempted to convert this spreadsheet, you may have found that it required manual designation of input cells. In this case, you should add a suitable fill color to all input cells before starting the SpreadsheetConverter wizard.

Verify that the SpreadsheetConverter menu is available in the Excel menu bar. If it isn’t, press the Esc key on the keyboard to ensure that any context-specific menus are deactivated. Excel automatically disables the menu while you are editing a cell.

The SpreadsheetConverter menu has the following entries

In the SpreadsheetConverter menu, click “Convert”.

Register your copy of SpreadsheetConverter

If you have already registered your copy of SpreadsheetConverter, skip this section and proceed to Start the SpreadsheetConverter wizard below. If you haven’t yet registered your copy of the program, a window similar to the one below will appear.

If you haven't registered the program, SpreadsheetConverter works in trial mode. The only restrictions of the trial version are:

As soon as you register the program, all these restrictions are removed (there is no need to reinstall the program). Once you’ve purchased a license, there are no additional costs and there are no royalties to pay when you distribute the resulting web pages.

If you want to order a registration key for SpreadsheetConverter, click “Order a license online”.

If you have a registration key and want to register this copy of SpreadsheetConverter, click “Enter registration key”.

Copy the license key from the e‑mail or web page where you got it and paste it into the first cell of the “license key” field. Enter the e‑mail address in the “Email address” field that you used when purchasing the license key. Press OK to register your copy of SpreadsheetConverter.

Press “OK” in the “License key accepted” window.

Start the SpreadsheetConverter wizard

Press “Next” to start the SpreadsheetConverter wizard.

Select output format

This step is only present in the multi-format versions of SpreadsheetConverter, and allows you to select one of the following (some choices may not be available in your particular version of SpreadsheetConverter):

Select cells

Select which sheets to include in the web page

The “Select Cells” dialog allows you to select which sheet tabs in the workbook to make available in the resulting web page. Each additional tab may make the converted web page bigger or slower, so don’t create more sheet tabs than necessary. To make only the currently active worksheet visible in the web page, select “Selected sheets(s)” for the best performance.

If you want to create sheet tabs for a subset of the worksheets in the workbook, do the following:

Select the input cells

You may also select the spreadsheet cells that are to be editable by the user. In most cases, SpreadsheetConverter will be able to determine this automatically by looking at the contents of each cell. If this is the first time you are converting this particular spreadsheet to html, try using automatic detection first. Select “Automatic detection” as the selection method, then press “Next” to advance to the next page of the wizard. Skip the rest of this section for now and continue with Presentation and Interaction below.

If you wish to prevent certain fields from modification by the user, or if you wish to add extra input fields that may not be obvious to SpreadsheetConverter (e.g. for extra lines in address fields), you need to use one of the two methods that SpreadsheetConverter uses for manual designation of input cells. This is also necessary when you create forms using SpreadsheetConverter. See http://www.spreadsheetconverter.com/article/articleview/35/1/1 for more information.

If your worksheet uses Excel’s built‑in protection mechanism, the input cells have already been explicitly “unlocked” by the creator of the spreadsheet. In this case, SpreadsheetConverter can use this information to determine which cells to use for input. You need to unlock the worksheet before conversion and select the “Unlocked cells are input cells” option on the “Select cells” page in SpreadsheetConverter. (For more information on how to protect cells and worksheets in Excel, use the help index in Microsoft Excel).

You can also use fill coloring to explicitly “open” cells for input from the user. With this method you assign a special background color to just the input cells. During conversion, SpreadsheetConverter locks all cells except the cells with the selected fill color.

To apply the selected fill color to each cell, set the Format > Cells > Patterns > Cell shading from the Excel menu bar or use the “Formatting” toolbar’s  icon. Ensure that you apply the selected fill color to all the cells that should be open for input (but not to any other cells).

Save the spreadsheet and then run the SpreadsheetConverter wizard again. In the “Select cells” window, select “Using coloring”, then press the “…” button to select the fill color that you have already applied to all the input fields.

If you don’t want the special fill color for input fields to appear in the converted web page check the “Ignore background color…” check box.

Press “OK” when you have selected the correct color.

Press “Next” to advance to the next page of the wizard.

Presentation and Interaction

Formatting in Excel vs. formatting by browsers

One of the many strengths of the spreadsheet concept is the easy handling of rows and columns. When creating a spreadsheet you can tailor the exact appearance of each row and column, including height, width, borders and headings. There are also numerous ways to format individual cells, including how numbers are displayed, what fonts are used and what colors are used for text and background.

When spreadsheets are converted into web format, they enter the realm of competing web browsers. Unfortunately, no browser is identical to the other, and a web page will inherently appear somewhat different in each browser. This is part of the natural behavior of the world-wide web.

When SpreadsheetConverter converts a spreadsheet to html, it is forced to make certain compromises when it comes to formatting. When compared to the original spreadsheet in Excel, the web page created by SpreadsheetConverter will not be an identical twin regardless of which browser you use to view it. In fact, it will look slightly different in every browser, and often different between versions of the same browser. Once again, this is a natural consequence of entering the web environment. In most cases the result is good enough.

The Presentation and Interaction page

The “Presentation and Interaction” page of the SpreadsheetConverter wizard allows you to control some of the compromises made during conversion. Press the “Configure layout and recalculation…” button if you want to modify the appearance of the converted web page. To configure the toolbar and submission options in the web version of the spreadsheet, press the “Configure toolbar and submit…” button.

Settings for spreadsheet layout

Dynamic layout

SpreadsheetConverter can attempt to give the rows and columns in the web version of the spreadsheet approximately the same height and width as in Excel, or allow the browser to calculate the column width automatically. If you select Fixed layout, SpreadsheetConverter toces the browser to use the exact layout of the spreadsheet to the converted web page. Overriding the browser’s own automatic formatting in this way may cause unexpected results, e.g. that input fields might become too small. In this case you may need to adjust the formatting options in the Excel version of the spreadsheet before you convert it to web format.

Fixed layout is only supported by Internet Explorer.

Borders around input cells

This setting controls the appearance of input cells, i.e. the cells in the spreadsheet that the user may modify. Use the setting that you think produces the best result.

Automatic Recalculation

To provide correct information, the value of each cell in the web format spread­sheet must be recalculated when changes have been made to one or more of the input cells open to the user. In most cases, it is recommended that this recalculation occur automatically when the user moves to the next input field.

However, if the spreadsheet is large, recalculation may be time-consuming and distracting. It this case, it may be better to perform the recalculation when the user explicitly requests it by pressing a button. The main disadvantage of manual recalculation—apart from the manual intervention required—is that it may be easy to forget to recalculate the values in the spreadsheet. In this case, the user may inadvertently base decisions on values that are incorrect. We strongly recommend that you design each spreadsheet so that automatic recalculation is possible, e.g. by separating content into separate worksheets.

By default, SpreadsheetConverter recalculates worksheets automatically unless they are so big that recalculation between cell modifications would cause an undesired delay in data entry. Use the Automatic Recalculation setting to adjust the way SpreadsheetConverter recalculates cell values:

Automatic recalculation is only available for client-based solutions. When creating server-based pages, this setting is ignored.

Press “OK” to save the advanced layout settings.

Configuring the toolbar

SpreadsheetConverter can insert a toolbar in the converted web page. The toolbar may include buttons for manual recalculation, printing etc, and sheet tabs for navigation between sheets in the workbook.

The toolbar and its location can be configured in the SpreadsheetConverter wizard.

The toolbar can be inserted above the spreadsheet, below it or both.

Select one of the available themes to modify the appearance of the button bar.

Select which buttons you want to include in the toolbar. Modify the default text for each button, if necessary.

Press “Next” to advance to the next page of the wizard.

Submit parameters

When you create a form, you want to be able to collect the data. The browser cannot email the form data to you directly, but it can use server functionality on the network to convert the form contents into as an email. The standard solutions for this conversion are typically rather hard to configure, and your email address may need to be included in the web page, inviting spam.

To avoid these drawbacks, SpreadsheetConverter provides a built-in solution which is easy to configure and doesn’t expose your email address. The submitted data is sent to one of our servers, which converts the form fields into an email and sends it directly to you. To use this service, you need a receiver code provided by us. When forms are submitted using this receiver code, the form fields are sent by email to a predefined email address.

The Configure Submit window

To configure the submit parameters, start the SpreadsheetConverter wizard, advance to the Presentation and Interaction page, and click ”Configure toolbar and submit...”. Then click ”Configure submit...” to open the Submit parameters window.

To request a receiver code, press the ”Request free receiver code” button and type the email address for the completed forms.

Your trial receiver code is sent by email to this email address.

Select the free or advanced service and enter the receiver code into the corresponding text box in the Submit parameters window. If you use the free service, it is very important to name the cells. See Naming cells

In the future, there will be a minimal monthly charge for using the advanced service to send the data to you. Before then, the following functionality will be added:

We will notify you when development is complete. Until then, the advanced service is free. For more information about pricing of the Advanced service see http://www.spreadsheetconverter.com

Using your own script for SpreadsheetConverter toms

If you prefer to use your own script to process the updated html form, enter the URL to that script in the URL field. The script will be sent the following parameters:

where (name1) is the name assigned to the first input cell in the form and (value1) is the value in that cell. If a worksheet contains two input fields, “name” and “release”, the parameters sent to the script will be:

?xl_version=3.0.0&name=exceleverywhere&release=3.0.0

Data is encoded according to the http post standards, e.g. a space is represented as “%20”. Please contact our support department if you need help on how to decode the query string of an http post operation.

If a cell hasn’t been assigned a name, SpreadsheetConverter will refer to it using a four-part code:

The first cell in the first sheet is referred to as “p1A1”.

Press OK to configure the submit parameters.

Select location

Pure HTML/JavaScript

If you are using SpreadsheetConverter to HTML or have selected the “Pure HTML/JavaScript” as your output format, the Select location page looks like this:

Verify the folder location for the web page that will be created by SpreadsheetConverter.

The web page is saved in two formats:

When you publish the web page on a web server, you must include all files in the folder created by SpreadsheetConverter. To avoid file name conflicts, we recommend that you upload the entire folder and link to the page inside the folder. If you want to send the web page to someone else, remember to send the zip file to ensure that all required files are included.

Press the “…” button if you want to change the default location. Navigate to the proper folder and press OK.

Press “Next” to advance to the next page of the wizard.

JavaBeans for calculation, JSP with JavaScript for GUI

If you are using SpreadsheetConverter to Java and are creating a server-based solution, the Select location page looks like this:

First, SpreadsheetConverter creates the JSP file, the actual web page that you will open in your web browser to view the spreadsheet on the web. The JSP file is used to access the Java programming logic. The JSP file must be accessible by the web server. You can either create the file in your local file system and move it to the server, or place the file directly on the server if you have file-sharing access to its disks. In the example, the JSP file is placed on the “Meg” server at \\Meg\Meg_E\Program_Files\Apache_Group\Tomcat_4.1\webapps\examples\jsp. To change the location of the JSP file, press the “…” button, navigate to the proper folder and press OK. You may of course run the web server in your local PC. In that case the JSP file will typically be located in a folder on your C: disk.

Second, SpreadsheetConverter creates the Class file, which contains the Java source code created from your spreadsheet. The class file is also available for including into projects. The class file must be accessible by the web server. You can either create the file in your local file system and move it to the server, or place the file directly on the server if you have file-sharing access to its disks. In the example, the class file is placed on the “Meg” server at  \\Meg\Meg_E\Program_Files\Apache_Group\Tomcat_4.1\webapps\examples\WEB-INF\classes. To change the location of the class file, press the “…” button, navigate to the proper folder and press OK. You may of course run the web server in your local PC. In that case the class file will typically be located in a folder on your C: disk.

Third, you can specify a folder for the temporary Java file. If you don’t, a default location will be used. Observe the usage restrictions for the source file in the SpreadsheetConverter End-User License Agreement.

Fourth, you need to specify a Java package name for some web servers (e.g. Tomcat). We suggest you use the package name “com.xlew”.

When conversion is complete, SpreadsheetConverter can automatically open the converted web page in your browser. Enter the http address to the folder containing the JSP page in the URL used to open resulting JSP page field.

Press “Next” to advance to the next page of the wizard.

ASP and ASP.NET

If you are using SpreadsheetConverter to ASP and ASP.NET and are creating a server-based solution, the Select location page looks like this:

First, SpreadsheetConverter creates the ASP or ASPX file, the actual web page that you will open in your web browser to view the spreadsheet on the web. The ASP file is stored on the server and contains all the required programming logic. The ASP file must be accessible by the IIS web server. You can either create the file in your local file system and move it to the server, or place the file directly on the server if you have file-sharing access to its disks. In the example, the ASP file is placed on the Meg server at \\Meg\Meg_E\www\prices. To change the location of the ASP file, press the “…” button, navigate to the proper folder and press OK. You may of course run the web server in your local PC. In that case the ASP file will typically be located in a folder on your C: disk.

When conversion is complete, SpreadsheetConverter can automatically open the converted web page in your browser. Enter the http address to the folder containing the ASP page in the URL used to open resulting ASP(X) page field.

Press “Next” to advance to the next page of the wizard.

Verify the generated web page

Press “Finish” to begin the conversion process.

After conversion, a “Messages and Errors” window appears. In most cases there are only informational messages. If there are warnings or errors, turn to the section on Error messages in the Troubleshooting chapter.

In most cases, the generated web page is automatically opened in your default web browser. Verify the results of the conversion. SpreadsheetConverter provides numerous options that may help you tailor the conversion process and its results. In many cases, it’s easier to change the settings in SpreadsheetConverter and rerun the conversion than to manually modify the generated web page.

The SpreadsheetConverter menu

During installation, SpreadsheetConverter installs itself as a separate menu in Excel:

The menu can be used to initiate the following:

Tips, tricks and recommendations

Use cell formats for numbers and dates

When Excel formats a cell containing a number or a date, it uses a default display format unless you’ve selected a specific number or date format for the cell. However, Excel’s defaults may not be suitable when the spreadsheet is converted to web format and viewed through a web browser. If a cell looks strange in your browser, e.g. has too many or too few decimals, just select a number or date format for the cell in Excel using Format > Cell > Number and it will look the same on the web page.

Use drop down lists instead of text input

If you want your users to be able to select from a list of given values in a drop­down menu, use Data > Validation > List in Excel to create the menu before you convert the spreadsheet to web format.

Use list boxes

If you want your users to be able to select a single value from a list of given values in a list box, use Data > Validation > List in Excel to create the menu and let it span over several merged rows.

Use checkboxes

If you want a checkbox in a cell, use the values “true” or “false” (or the equiva­lent in the language supported by your Excel version). SpreadsheetConverter will automatically present the cell as a checkbox.

Use multi-line input boxes

An input cell which spans over several merged rows will become a multi-line input box on the web page. This field can be scrolled.

Use charts to present data graphically

If you place a pie, bar, column, line or scatter chart in a worksheet, SpreadsheetConverter will include the chart on the web page. To add a chart to a spread­sheet, use Insert > Chart… in the Excel menu. To modify the appearance of the chart in Excel, click on the chart and use the Chart menu.

During conversion, SpreadsheetConverter reads the chart definitions in the spread­sheet and creates a Flash version of the chart. As in Excel, the Flash version of the chart is dynamic and immediately reflects changes made to the data in the work­sheet.

Avoid horizontal layouts

Spreadsheets are often designed with a horizontal layout, e.g. with a lot of sideways scrolling from column to column. This kind of design is rarely used on the web. For the most web-like appearance, consider designing your spreadsheets so that they don’t require horizontal scrolling.

Adding a converted spreadsheet to your web site

Basic steps

The tools you are using, e.g. your content management system and the editors and other support programs you use, have a strong influence on the steps required to publish a web page. However, there are three basic operations you always need to perform, regardless of your toolset:

Move the page to the web site

Ensure that the file is physically included in your web site, i.e. that it is located within the same folder structure or content management system as the other web pages in the web site. This guarantees that the page is physically accessible when requested by one of the users of the web site.

Insert the page into the site

Link the page to the rest of your site. You will need at least one link to the page so that users can navigate to it. If your web site has a menu or similar navigation structure you may want to insert it also on the SpreadsheetConverter page.

Adapt the formatting of the page (optional)

Give the SpreadsheetConverter page the same look-and feel as the rest of your site. Open the SpreadsheetConverter page in your favorite web page editor and format it so that it looks like the other pages on your web site.

Contents of an SpreadsheetConverter folder

When you publish the web page on a web server, you must include all files in the folder created by SpreadsheetConverter. To avoid file name conflicts, we recommend that you upload the entire folder and link to the page inside the folder.

In the example above, a “mortgage.xls” spreadsheet has been converted to a “mortgage.htm” web page, which SpreadsheetConverter has embedded in a “mortgage” folder that also contains a diagram and an image file.

The “Powered by SpreadsheetConverter” image link

All web pages created by SpreadsheetConverter contain a small 334-byte image link with the text “Powered by SpreadsheetConverter”. An easy way to remove this image without modifying the html is to replace the image file with a different image file with the same name.

Adding an SpreadsheetConverter page to a FrontPage site

Here’s a step‑by‑step instruction on how to insert a web page created by SpreadsheetConverter into a FrontPage web.

Now, your page should appear like the other pages in your web site and the navigation structure should include the new page.

Creating an interactive form as a web page

Basic steps

Microsoft Excel together with SpreadsheetConverter provides an easy way to create interactive forms on the web, and subsequent updates can be performed very quickly.

Some examples of forms that you can create using Excel are: expense reports, online reports, surveys, order forms, registration forms, reservation forms, sweepstakes, employment applications and product registrations.

It is very easy to publish an interactive form on the web using SpreadsheetConverter. Just modify the spreadsheet, start the SpreadsheetConverter wizard and only seconds later an interactive web page is created and ready to be published on your web site.

Using basic forms support in the web server environment, incoming data can be stored on the server in a flat file or database. If the volume of data is low, you can also have each completed form sent by email to a predefined email address.

Regardless of your toolset, four basic steps are required to create interactive forms with SpreadsheetConverter:

Create the form using Excel

The row/column layout of Excel makes it very simple to create forms. For an order form, one column could be used for quantity and one for unit price. In a third “item price” column you may automatically want to multiply unit price by quantity. At the bottom of the “item price” column you may want a cell holding the “total price”.

When converting forms, SpreadsheetConverter may have difficulties determining which cells to use for input. To avoid errors, you should designate all input cells manually, e.g. using cell coloring (see Select the input cells above).

When a web user has completed the form and the data is sent back to you, the form fields are returned one by one in text format. For readability purposes, you may want each field in the response to be clearly labeled. Otherwise, SpreadsheetConverter identifies each input field using its row and column number in the spreadsheet. To assign an explanatory name to each input field, select the cells one-by-one in Excel and use Insert > Name > Define.

Convert the form to web format using SpreadsheetConverter

SpreadsheetConverter identifies the input and output cells, and converts the Excel sheet to web format. Every time the user advances from one form field to the next, cells like “item price” and “total price” are recalculated with the latest information.

Establish the link between form and server

The SpreadsheetConverter toolbar includes an optional Submit button for simple implementation of intelligent web forms with SpreadsheetConverter. The Submit function can send the submitted form contents to you in an email. Since the generated web page is a standard html form, you can also use a standard html forms processor on one of your own network servers. See Submit parameters above for details.

Publish the form on the web server

Once the html for the interactive web page has been created and tested, it must be placed on the server and made available for users of the web site. This process is described in the section Adding a converted spreadsheet to your web site above.

Creating a web form using FrontPage

To create a web form to be used by a web server that supports the FrontPage Server Extensions, e.g. Microsoft Internet Information Server or the Apache server, follow these steps:

  1. Create the spreadsheet using Excel.
  2. Name the input and output cells in Excel using Insert > Name (this step is not mandatory, but makes the result easier to understand).
  3. Configure submit on the Presentation and Interaction page of the SpreadsheetConverter wizard and select ‘I have prepared my own webserver to accept submits’. Leave the URL field empty; Frontpage will fill in the necessary information.
  4. Convert the spreadsheet to a web page using SpreadsheetConverter.
  5. Open the resulting web page in FrontPage.
  6. Place the cursor somewhere near the input fields, click the right mouse button and then select 'Form properties'.
  7. Select the handling of the form that is appropriate to your needs. The minimum you must do is to select 'Send to' and enter an email address.
  8. Publish the web page onto your web server using the instructions in section Adding an SpreadsheetConverter page to a FrontPage site above.

Creating better user interfaces

SpreadsheetConverter supports drop-down menus, radio buttons, checkboxes, listboxes, multi-line input fields.

Cell-based controls versus floating controls

If you want to ask the user for a number, you can say that a cell is an input cell, and you will get an input field on the web page. We call this input cells cell-based, since it’s location is determined by a cell.

On the other hand, you can create a drop-down menu by selecting a combo box from Insert-Toolbar-Forms. The control will place itself ontop of the worksheet.  We call such a control a floating control.

Most controls can both be constructed as a floating control and as a cell-based control. The are two exceptions: plain input cells for text and numbers, which always are cell-based, and radio-buttons which always are floating.

Floating controls always needs a linked cell. The selected value will be stored in this cell.

 

 

Cell-based

Floating

Input cell for text or number

Mark a cell as input cell

N/A

Checkbox

Mark a cell as input cell and set it to TRUE or FALSE

Select a checkbox from View-Toolbar-Forms and set the linked cell

Drop-down menu

Create a list using Data-Validation-List

Select a combo box from View-Toolbar-Forms and set the linked cell

Radio buttons

N/A

First create a frame using View-Toolbar-Forms, then fill it with radio buttons from View-Toolbar-Forms. Make sure that all radio buttons are completely inside the frame. Set the linked cell of each radio button to the same cell. Note that frame will not be shown on the webpage, but it is necessary for grouping the radio buttons

List box

Merge the cells on two or more rows and create a list using Data-Validation-List

Select a list box from View-Toolbar-Forms and set the linked cell. The selection type must be set to single.

Multi-line input cell

Merge the cells on two or more rows and make them input cells.

N/A

Which should I prefer: cell-based or free-floating?

It is a matter of taste. The web page will look the same. The advantage of the free-floating is that the spreadsheet will look more like the final web page. The advantage with cell-based is that it is easier to create and maintain many controls.

Also, free-floating controls might require you to select Layout=Fixed in order to get the placement correct on the web page.

Tools to simplify advanced input controls

You can create controls like radio buttons, drop-down menus as described above. In order to make it simpler, we have added some commands to help you.

Mark input cells…

Assuming that the cell contains a constant value or is empty, then the cell can be identified as an input cell in three separate ways:

  1. It is used by a formula and you have selected Automatic detection of input cells. (See Select the input cells)
  2. You have set the background color of the cell and selected Select input cells using coloring. (See Select the input cells)
  3. The cell is unlocked and you have selected Unlocked cells are input cells. (See Select the input cells)

Setting the background color of a cell or unlocking it can be done using the Excel command Format-Cell, or you can use Mark input cells…

First select the cell or cells that you want to be input cells, and then select Mark input cells… from the SpreadsheetConverter menu.

The cells C2,D2 andE2 will be unlocked.

If you select Color the input cells, they will be colored using the same color you selected in Select input cells using coloring. (See Select the input cells)

If you select Merge and left-justify the cells, the cells will become one single large input cell, otherwise, there will be three input cells next to each other.

Create one of several options…

Before you select the command Insert one of several options you have to select a cell in your worksheet. The control will be placed here. That cell should be empty.

If you select the cell F3, select Insert one of several options you, enter Always, Maybe, Never and select ‘Try to avoid free-floating Excel controls’, the wizard will place a drop down list in cell F3.

 

Select cell F8 and select Insert one of several options again. Select Radio buttons, unselect Try to avoid free-floating Excel controls and set the linked cell cell to F4.

The final result will be a drop-down menu in F3, three radio buttons in cell F8 and downwards. The cell F4 will contain 1, 2 or 3 depending on which radio button is selected.

Create Boolean control…

This wizard creates checkboxes and radio buttons with 2 alternatives.

Naming cells

If you use the free service to handle submits, naming cells is very important. Unless you have named the input and output cells, the names will look like p1A1, which means Sheet1!A1. This will make the email hard to understand.

You can either name a cell using Insert-Name-Define in Excel, or by selecting the cell to be named and enter the name into the little box in the upper-left corner.

In the following screenshot we selected cell F4 and entered the name how_often into the naming box.

In order for names to be used by SpreadsheetConverter, the name should only refer to a single cell and only contain letters, digits and underscore. The name must start with a letter. Other names will be ignored.

Advanced topics

This section mostly contains information suitable for an advanced Excel user or web programmer. If you don’t belong to either category, just move on to the next chapter.

Support for regional options and languages

Basically, SpreadsheetConverter tomats numbers and dates just as they are formatted on your local computer.

Regional number formats

There are many ways to format a number. In the US, the decimal separator is a point and commas are used as thousand separators, e.g. 1,234.56 is a valid number. In contrast, most European countries use comma as the decimal separator and either space or point as thousand separators, e.g. 1 234,56 is a valid number.

When converting a spreadsheet to a web page, SpreadsheetConverter tomats numbers in your spreadsheet as defined by the cell format (Format > Cells >Number).

You cannot change the decimal or thousand separators in Excel, however; it must be done using the regional options for the whole system. If you want to create a web page to be used by someone familiar with a different format, e.g. a Swede, you have to open the regional options control panel and temporarily change to another region (e.g. Sweden). Once you’ve created the web page, restore your original regional options. The numeric formats used in the web page remain unchanged (e.g. still applicable to Sweden).

SpreadsheetConverter supports most currency formats, but currency symbols such as $, £, or € are not included. If necessary, put the $, £, or € into the cell immediately preceding or following the number.

Regional date formats

There are also many ways to format a date. In the US, dates are in the Month/Day/Year format, e.g. 5/04/04 is May 4, 2004. In contrast, most European countries write dates as Day-Month-Year with various separators, e.g. 04.05.04 for the same date. Some countries even use the Year-Month-Date format.

When converting a spreadsheet to a web page, SpreadsheetConverter basically formats dates as defined by the cell format (Format > Cells >Number). For input cells, the following restrictions apply:

  1. The ordering of month, day and year is fixed. If you create the web page using US settings, only the month-day-year order is supported. If you create the web page using European settings, both day-month-year and year-month-day are allowed, but not the US format.
  2. Input cells may only use numeric date formats. You can use ”04/05/2004” but not ”4 May 2004”.

Recommendation: to avoid confusion with an international audience, create three cells with drop-down lists: one to select a day, one to select a month and one to select a year. Then use the three values to create the date internally in Excel using the DATE function. There is an example of this on the SpreadsheetConverter web site at http://www.xleverywhere.com/manual/date_dropdown_list.htm.

National characters

SpreadsheetConverter supports Unicode for national characters.

Inserting a spreadsheet on an existing web page

If you ever need to insert a spreadsheet on an existing page in your web, just follow the steps below.

  1. Create the spreadsheet in Excel.
  2. Convert the spreadsheet to a web page using SpreadsheetConverter.
  3. Open the resulting web page in your html editor.
  4. Using your html editor, also open the existing page where the spreadsheet is to be inserted.
  5. In the <head> … </head> section of the SpreadsheetConverter file, locate the section beginning with <!-- Header start --> and ending with <!-- Header end --> and copy it (including the leading and trailing comment) to the end of the <head> … </head> section of the existing page.
  6. In the <body> … </body> section of the SpreadsheetConverter file, locate the section beginning with <!-- Body start --> and ending with <!-- Body end --> and copy it to where you want the spreadsheet to appear in the <body> … </body> section of the existing page.
  7. (optional) Copy the onload-event from <body into your <body-tag. Typically the onload-event looks like this:
    <body onload="initial_update();document.formc.pA1A.focus();">
    It initializes the form and places the cursor at the first field.

 

Troubleshooting

Technical requirements

Requirements for running SpreadsheetConverter to create web pages:

* SpreadsheetConverter also works with many localized versions of Microsoft Excel. Download the trial version and try it out. If the trial works, the full version will also work.

Requirements for the end user accessing the resulting web pages:

Known issues

Support for Excel spreadsheet features

SpreadsheetConverter supports a major subset of the features available in an Excel spreadsheet, listed in Appendix 1: Conversion details. An updated list of currently supported spreadsheet features is available at