We have put a lot of effort into making sure that the web pages generated by SpreadsheetConverter are small in size, perform fast calculations, and use as little memory as possible. This provides the end-users with quick download times and good interactivity. A web page, created from a spreadsheet with 5-10 input cells and 5-10 calculated values, will be between 8-20 Kb in size and will be downloaded by a modem user within a few seconds. For a user with a broadband connection, the same web page will be downloaded instantly.
That means that within seconds, the end-user sitting at his browser can start using your spreadsheet calculations.
Our investigations show that typically you can have up to 200 input cells and 200 calculated values, and the recalculation will be instant. That means that it isn’t the recalculation that is the bottleneck, but instead how to present all of this information to the end-user in a single web page.
200 + 200 is of course an average value, it depends on the complexity of the formulas and the built-in functions that are used. Also, rendering speed (the time it takes to draw an HTML-page in the browser window) and the speed of JavaScript differs a lot between different browsers.
(These timings were done 2002)
The solution is to reduce the number of calculated values shown on the web page.
Very often 80% of the time is spent updating the values the user sees on the web page, and only 20% is spent on the actual calculations. By hiding large parts of the spreadsheet, these values will not be visible on the web page and no time is wasted on updating them. Even if you hide cells, the calculations in them will be used if they are needed for the values visible on the web page.
Since recalculations are almost instant, we would like to give as much feedback to the end-user as possible.
Version 2: If you do not want automatic recalculation, just remove “checked” from the checkbox called ‘automatic_recalc’ in the SpreadsheetConverter wizard.
Version 3 and 4: Set Automatic Recalculation to disable in the wizard.
SpreadsheetConverter is as scalable as any static HTML-page. That means that if your current web server can support hundreds or thousands of simultaneous users, SpreadsheetConverter will also be as scalable. SpreadsheetConverter does all calculations on the client, the server is not involved. Once the web page is downloaded, the server is not needed. A web page created with SpreadsheetConverter is typically only 15-25 Kilobytes, i.e. the same size of any HTML-page.
No, SpreadsheetConverter only uses the JavaScript-engine that is included in all modern browsers.
There is nothing to download for the browser except a standard web page.
Yes, but is not recommended. Internal arrays will get unnecessary large and the conversion takes longer time.
We do not recommend more than 40,000 active cells. After that the generated code gets rather large and slow.
A few tips: LOOKUP-operations should always work with ordered search
Make the ranges as small as possible, A1:A100 is better then A1:A1000, and A:A is worst
Make your lookup ranges only contain numbers if possible, use #N/A or 0 for error entries, not strings like ‘Error’
Have as few in and out-fields as possible. The fields takes long time to update
Speed differs a lot between browsers, IE6 is the fastest and IE4 and NS4 are the slowest.
We are optimizing the code generated in the next versions, so one alternative is to start with ASP, and retest the speed again in a few months and maybe switch to HTML.
By enabling compression, the amount of data you download will be 1/10. All modern browser support compression.
http://www.schroepl.net/projekte/mod_gzip/index.htm
When you have the range ordered, looking through 800 rows only requires 10 comparisons (since 210 = 1024 which is larger than 800). The technique used is called binary search.
If you do sequential search, you have to make 400 comparisons in average (800/2=400).
Thus, for larger tables, ordered lookup will be 100 times faster.
Server-based calculations used to be faster, however since 2010, modern browsers like Firefox 3, Google Chrome, Apple Safari are as fast.
Older browsers like IE7 and IE8 are much slower in processing JavaScript, however, with IE9, Microsoft has at last delivered a competitive browser. IE9 will be released spring-2011.
If you know your users are using a modern browser, you can select “Automatic recalculation” in the SpreadsheetConverter wizard to do a recalculation after each field.
If the loading is the slow part, there is not much SpreadsheetConverter can do before the page is loaded. You however can configure your webserver to compress the data, this is both supported by IIS and Apache and by all modern browsers. It will reduce the amount of data sent to one 5th. (I do not understand why this isn’t the standard, since it is automatically disabled when the browser is old. And network is much more important and expensive than a cpu-cycle. The compression method is either deflate or gzip.)
Read more at
http://www.fogcreek.com/FogBugz/docs/50/Articles/EnablingHTTPCompression.html
http://www.microsoft.com/resources/documentation/iis/6/all/proddocs/en-us/comp_cust.mspx
Apache: http://www.schroepl.net/projekte/mod_gzip/index.htm
We say that the limit is about 10000-40000 cells for the Pro Ed.
However, as some have noticed, it works for even larger workbooks. However, the conversion takes time and the pages get very big.
The most important thing you can do if you convert very large workbooks it to make sure that you do not mix numbers and text in calculations. There are 2 common sources:
1. Do not include the header in the ranges used by lookup-operations like VLOOKUP. Since the header is text, but often the values are numbers. So if the date in the table starts on row 2, and row 1 is the header, refer to A2:B200.
2. do not have formulas like =IF(A1=0,””,A1*10). Just have A1*10 or =IF(A1=0,0,A1*10) [Often I just replace “” by 0 in all cells.]
3. Always make lookup ranges as small as possible. Do not use A:A, use A2:A200. Do not include blank lines in lookup ranges. If you have big vlookups, make sure that the data is ordered. Then, lookup will go much faster.
4. Reduce the size of the user interface, ask the user less, show the user less. Show as few worksheets as possible, showing a value takes much more resources than just calculting it as an intermediate value.
5. For each sheet, press Ctrl- End, hide columns ABOVE and to the LEFT of this position that you do not want the user to see. Do not hide all the way down to IV65536 (or XFD1048576)
6. Instead of deep nested IF, use VLOOKUP or similar
Step 1-4 above also affects recalculation speed.
Click on Download to install and test this Excel add-in for Windows.
Click on Upload to let us convert a spreadsheet for you for free.
