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.