JCite With Excel
This document shows how JCite can cite examples from within Microsoft Excel spreadsheets.
Note
You need to enable the Excel citing plugin for this to work. See the notes about running JCite for details.
Citing An Entire Sheet
Using JCite, you can very easily cite the entire first sheet of an Excel workbook file:
| A | B | C | D | E | F | G | |
| 1 | Inputs | ||||||
| 2 | Orders for last 3 months | Total | Date | DaysBack | Weight | Value | |
| 3 | 450 (OrderTotal) |
8/10/06 (OrderDate) |
97 =B$11-C3 |
-8% =(90.0-D3)/90.0 |
-35 =B3*E3 |
||
| 4 | 1320 | 8/29/06 | 78 =B$11-C4 |
13% =(90.0-D4)/90.0 |
176 =B4*E4 |
||
| 5 | 1540 | 9/24/06 | 52 =B$11-C5 |
42% =(90.0-D5)/90.0 |
650.222 =B5*E5 |
||
| 6 | 60 | 10/8/06 | 38 =B$11-C6 |
58% =(90.0-D6)/90.0 |
34.667 =B6*E6 |
||
| 7 | 5870 | 11/5/06 | 10 =B$11-C7 |
89% =(90.0-D7)/90.0 |
5217.778 =B7*E7 |
||
| 8 | |||||||
| 9 | Intermediates | ||||||
| 10 | Total Value | 6043.667 =SUM(F3:F7) |
|||||
| 11 | CurrentDate; TODAY() really | 11/15/06 | |||||
| 12 | > 0 ? | true =B10>0.0 |
|||||
| 13 | < 1'000'000 ? | true =B10<1000000.0 |
|||||
| 14 | |||||||
| 15 | Outputs | ||||||
| 16 | Rating | 3 =MATCH(B10,C16:G16) (Rating) |
0 | 2000 | 5000 | 7500 | 10000 |
| 17 | Plausible | true =AND(B12:B13) |
B3:F7 (OrdersForLastThreeMonths)
B2:F2 (OrdersHeader)
This was produced by the following JCite instruction in the source HTML document:
[xc:EntireSheet.xls]
The part EntireSheet.xls tells JCite what Excel file to cite from. Note how names denoting single cells are shown directly in the cell, while named ranges are colored and their names listed at the bottom.
Citing Ranges
You can also cite only a single range instead of an entire sheet:
| B | C | D | E | F | |
| 3 | 450 (OrderTotal) |
8/10/06 (OrderDate) |
97 =B$11-C3 |
-8% =(90.0-D3)/90.0 |
-35 =B3*E3 |
| 4 | 1320 | 8/29/06 | 78 =B$11-C4 |
13% =(90.0-D4)/90.0 |
176 =B4*E4 |
| 5 | 1540 | 9/24/06 | 52 =B$11-C5 |
42% =(90.0-D5)/90.0 |
650.222 =B5*E5 |
| 6 | 60 | 10/8/06 | 38 =B$11-C6 |
58% =(90.0-D6)/90.0 |
34.667 =B6*E6 |
| 7 | 5870 | 11/5/06 | 10 =B$11-C7 |
89% =(90.0-D7)/90.0 |
5217.778 =B7*E7 |
Here’s the instruction for this:
[xc:EntireSheet.xls:OrdersForLastThreeMonths]
Note that the named range OrdersHeader is not listed because it does not intersect the shown range.
Instead of just a single one, you can cite multiple ranges:
| B | C | D | E | F | |
| 2 | Total | Date | DaysBack | Weight | Value |
| 3 | 450 (OrderTotal) |
8/10/06 (OrderDate) |
97 =B$11-C3 |
-8% =(90.0-D3)/90.0 |
-35 =B3*E3 |
| 4 | 1320 | 8/29/06 | 78 =B$11-C4 |
13% =(90.0-D4)/90.0 |
176 =B4*E4 |
| 5 | 1540 | 9/24/06 | 52 =B$11-C5 |
42% =(90.0-D5)/90.0 |
650.222 =B5*E5 |
| 6 | 60 | 10/8/06 | 38 =B$11-C6 |
58% =(90.0-D6)/90.0 |
34.667 =B6*E6 |
| 7 | 5870 | 11/5/06 | 10 =B$11-C7 |
89% =(90.0-D7)/90.0 |
5217.778 =B7*E7 |
| 16 | 3 =MATCH(B10,C16:G16) |
with an instruction such as:
[xc:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths,Rating]
If you want the ranges to be identified still, suffix them with a + sign:
| B | C | D | E | F | |
| 2 | Total | Date | DaysBack | Weight | Value |
| 3 | 450 (OrderTotal) |
8/10/06 (OrderDate) |
97 =B$11-C3 |
-8% =(90.0-D3)/90.0 |
-35 =B3*E3 |
| 4 | 1320 | 8/29/06 | 78 =B$11-C4 |
13% =(90.0-D4)/90.0 |
176 =B4*E4 |
| 5 | 1540 | 9/24/06 | 52 =B$11-C5 |
42% =(90.0-D5)/90.0 |
650.222 =B5*E5 |
| 6 | 60 | 10/8/06 | 38 =B$11-C6 |
58% =(90.0-D6)/90.0 |
34.667 =B6*E6 |
| 7 | 5870 | 11/5/06 | 10 =B$11-C7 |
89% =(90.0-D7)/90.0 |
5217.778 =B7*E7 |
| 16 | 3 =MATCH(B10,C16:G16) (Rating) |
B3:F7 (OrdersForLastThreeMonths)
with an instruction such as:
[xc:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths+,Rating+]
Citing Formulas Only
In the examples so far, JCite cited cell formulas beneath their saved values. You can make JCite omit the values to make the formulas more prominent:
| B | C | D | E | F | |
| 2 | Total | Date | DaysBack | Weight | Value |
| 3 | 450 (OrderTotal) |
8/10/06 (OrderDate) |
=B$11-C3 | =(90.0-D3)/90.0 | =B3*E3 |
| 4 | 1320 | 8/29/06 | =B$11-C4 | =(90.0-D4)/90.0 | =B4*E4 |
| 5 | 1540 | 9/24/06 | =B$11-C5 | =(90.0-D5)/90.0 | =B5*E5 |
| 6 | 60 | 10/8/06 | =B$11-C6 | =(90.0-D6)/90.0 | =B6*E6 |
| 7 | 5870 | 11/5/06 | =B$11-C7 | =(90.0-D7)/90.0 | =B7*E7 |
| 16 | =MATCH(B10,C16:G16) (Rating) |
B3:F7 (OrdersForLastThreeMonths)
with an instruction such as:
[xc:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths+,Rating+;formulas-only]
The option fo is shorthand for formulas-only.
Style Sheet
The formatting of the generated output is controlled by a CSS style sheet.