diff options
Diffstat (limited to 'src/documentation/content/xdocs/components/spreadsheet')
17 files changed, 8044 insertions, 0 deletions
diff --git a/src/documentation/content/xdocs/components/spreadsheet/chart.xml b/src/documentation/content/xdocs/components/spreadsheet/chart.xml new file mode 100644 index 0000000000..8e4194af9a --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/chart.xml @@ -0,0 +1,1532 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Chart record information</title> + <authors> + <person email="user@poi.apache.org" name="Glen Stampoultzis" id="GS"/> + </authors> + </header> + <body> + <section><title>Introduction</title> + <p> + This document is intended as a work in progress for describing + our current understanding of how the chart records are + written to produce a valid chart. + </p> + </section> + <section><title>Bar chart</title> + <p> + The following records detail the records written for a + 'simple' bar chart. + </p> + <source> + + ============================================ + rectype = 0xec, recsize = 0xc8 + -BEGIN DUMP--------------------------------- + 00000000 0F 00 02 F0 C0 00 00 00 10 00 08 F0 08 00 00 00 ................ + 00000010 02 00 00 00 02 04 00 00 0F 00 03 F0 A8 00 00 00 ................ + 00000020 0F 00 04 F0 28 00 00 00 01 00 09 F0 10 00 00 00 ....(........... + 00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000040 02 00 0A F0 08 00 00 00 00 04 00 00 05 00 00 00 ................ + 00000050 0F 00 04 F0 70 00 00 00 92 0C 0A F0 08 00 00 00 ....p........... + 00000060 02 04 00 00 00 0A 00 00 93 00 0B F0 36 00 00 00 ............6... + 00000070 7F 00 04 01 04 01 BF 00 08 00 08 00 81 01 4E 00 ..............N. + 00000080 00 08 83 01 4D 00 00 08 BF 01 10 00 11 00 C0 01 ....M........... + 00000090 4D 00 00 08 FF 01 08 00 08 00 3F 02 00 00 02 00 M.........?..... + 000000A0 BF 03 00 00 08 00 00 00 10 F0 12 00 00 00 00 00 ................ + 000000B0 04 00 C0 02 0A 00 F4 00 0E 00 66 01 20 00 E9 00 ..........f. ... + 000000C0 00 00 11 F0 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0xec, size =200 + [UNKNOWN RECORD:ec] + .id = ec + [/UNKNOWN RECORD] + + ============================================ + rectype = 0x5d, recsize = 0x1a + -BEGIN DUMP--------------------------------- + 00000000 15 00 12 00 05 00 02 00 11 60 00 00 00 00 B8 03 .........`...... + 00000010 87 03 00 00 00 00 00 00 00 00 .......... + -END DUMP----------------------------------- + recordid = 0x5d, size =26 + [UNKNOWN RECORD:5d] + .id = 5d + [/UNKNOWN RECORD] + + ============================================ + rectype = 0x809, recsize = 0x10 + -BEGIN DUMP--------------------------------- + 00000000 00 06 20 00 FE 1C CD 07 C9 40 00 00 06 01 00 00 .. ......@...... + -END DUMP----------------------------------- + recordid = 0x809, size =16 + [BOF RECORD] + .version = 600 + .type = 20 + .build = 1cfe + .buildyear = 1997 + .history = 40c9 + .requiredversion = 106 + [/BOF RECORD] + + ============================================ + rectype = 0x14, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x14, size =0 + [HEADER] + .length = 0 + .header = null + [/HEADER] + + ============================================ + rectype = 0x15, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x15, size =0 + [FOOTER] + .footerlen = 0 + .footer = null + [/FOOTER] + + ============================================ + rectype = 0x83, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x83, size =2 + [HCENTER] + .hcenter = false + [/HCENTER] + + ============================================ + rectype = 0x84, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x84, size =2 + [VCENTER] + .vcenter = false + [/VCENTER] + + ============================================ + rectype = 0xa1, recsize = 0x22 + -BEGIN DUMP--------------------------------- + 00000000 00 00 12 00 01 00 01 00 01 00 04 00 00 00 B8 03 ................ + 00000010 00 00 00 00 00 00 E0 3F 00 00 00 00 00 00 E0 3F .......?.......? + 00000020 0F 00 .. + -END DUMP----------------------------------- + recordid = 0xa1, size =34 + [PRINTSETUP] + .papersize = 0 + .scale = 18 + .pagestart = 1 + .fitwidth = 1 + .fitheight = 1 + .options = 4 + .ltor = false + .landscape = false + .valid = true + .mono = false + .draft = false + .notes = false + .noOrientat = false + .usepage = false + .hresolution = 0 + .vresolution = 952 + .headermargin = 0.5 + .footermargin = 0.5 + .copies = 15 + [/PRINTSETUP] + + <!-- Comment to avoid forrest bug --> + ============================================ + rectype = 0x33, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 03 00 .. + -END DUMP----------------------------------- + recordid = 0x33, size =2 + [UNKNOWN RECORD:33] + .id = 33 + [/UNKNOWN RECORD] + + ============================================ + rectype = 0x1060, recsize = 0xa + -BEGIN DUMP--------------------------------- + 00000000 A0 23 08 16 C8 00 00 00 05 00 .#........ + -END DUMP----------------------------------- + recordid = 0x1060, size =10 + [FBI] + .xBasis = 0x23A0 (9120 ) + .yBasis = 0x1608 (5640 ) + .heightBasis = 0x00C8 (200 ) + .scale = 0x0000 (0 ) + .indexToFontTable = 0x0005 (5 ) + [/FBI] + + ============================================ + rectype = 0x1060, recsize = 0xa + -BEGIN DUMP--------------------------------- + 00000000 A0 23 08 16 C8 00 01 00 06 00 .#........ + -END DUMP----------------------------------- + recordid = 0x1060, size =10 + [FBI] + .xBasis = 0x23A0 (9120 ) + .yBasis = 0x1608 (5640 ) + .heightBasis = 0x00C8 (200 ) + .scale = 0x0001 (1 ) + .indexToFontTable = 0x0006 (6 ) + [/FBI] + + ============================================ + rectype = 0x12, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x12, size =2 + [PROTECT] + .rowheight = 0 + [/PROTECT] + + ============================================ + Offset 0xf22 (3874) + rectype = 0x1001, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x1001, size =2 + [UNITS] + .units = 0x0000 (0 ) + [/UNITS] + + ============================================ + Offset 0xf28 (3880) + rectype = 0x1002, recsize = 0x10 + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 00 00 58 66 D0 01 40 66 22 01 ........Xf..@f". + -END DUMP----------------------------------- + recordid = 0x1002, size =16 + [CHART] + .x = 0x00000000 (0 ) + .y = 0x00000000 (0 ) + .width = 0x01D06658 (30434904 ) + .height = 0x01226640 (19031616 ) + [/CHART] + + ============================================ + Offset 0xf3c (3900) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0xf40 (3904) + rectype = 0xa0, recsize = 0x4 + -BEGIN DUMP--------------------------------- + 00000000 01 00 01 00 .... + -END DUMP----------------------------------- + recordid = 0xa0, size =4 + [SCL] + .numerator = 0x0001 (1 ) + .denominator = 0x0001 (1 ) + [/SCL] + + <!-- Comment to avoid forrest bug --> + ============================================ + Offset 0xf48 (3912) + rectype = 0x1064, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 00 00 01 00 00 00 01 00 ........ + -END DUMP----------------------------------- + recordid = 0x1064, size =8 + [PLOTGROWTH] + .horizontalScale = 0x00010000 (65536 ) + .verticalScale = 0x00010000 (65536 ) + [/PLOTGROWTH] + + ============================================ + Offset 0xf54 (3924) + rectype = 0x1032, recsize = 0x4 + -BEGIN DUMP--------------------------------- + 00000000 00 00 02 00 .... + -END DUMP----------------------------------- + recordid = 0x1032, size =4 + [FRAME] + .borderType = 0x0000 (0 ) + .options = 0x0002 (2 ) + .autoSize = false + .autoPosition = true + [/FRAME] + + ============================================ + Offset 0xf5c (3932) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0xf60 (3936) + rectype = 0x1007, recsize = 0xc + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 FF FF 09 00 4D 00 ..........M. + -END DUMP----------------------------------- + recordid = 0x1007, size =12 + [LINEFORMAT] + .lineColor = 0x00000000 (0 ) + .linePattern = 0x0000 (0 ) + .weight = 0xFFFF (-1 ) + .format = 0x0009 (9 ) + .auto = true + .drawTicks = false + .unknown = false + .colourPaletteIndex = 0x004D (77 ) + [/LINEFORMAT] + + ============================================ + Offset 0xf70 (3952) + rectype = 0x100a, recsize = 0x10 + -BEGIN DUMP--------------------------------- + 00000000 FF FF FF 00 00 00 00 00 01 00 01 00 4E 00 4D 00 ............N.M. + -END DUMP----------------------------------- + recordid = 0x100a, size =16 + [AREAFORMAT] + .foregroundColor = 0x00FFFFFF (16777215 ) + .backgroundColor = 0x00000000 (0 ) + .pattern = 0x0001 (1 ) + .formatFlags = 0x0001 (1 ) + .automatic = true + .invert = false + .forecolorIndex = 0x004E (78 ) + .backcolorIndex = 0x004D (77 ) + [/AREAFORMAT] + + ============================================ + Offset 0xf84 (3972) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0xf88 (3976) + rectype = 0x1003, recsize = 0xc + -BEGIN DUMP--------------------------------- + 00000000 01 00 01 00 20 00 1F 00 01 00 00 00 .... ....... + -END DUMP----------------------------------- + recordid = 0x1003, size =12 + [SERIES] + .categoryDataType = 0x0001 (1 ) + .valuesDataType = 0x0001 (1 ) + .numCategories = 0x0020 (32 ) + .numValues = 0x001F (31 ) + .bubbleSeriesType = 0x0001 (1 ) + .numBubbleValues = 0x0000 (0 ) + [/SERIES] + + ============================================ + Offset 0xf98 (3992) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + <!-- Comment to avoid forrest bug --> + ============================================ + Offset 0xf9c (3996) + rectype = 0x1051, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 00 01 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1051, size =8 + [AI] + .linkType = 0x00 (0 ) + .referenceType = 0x01 (1 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@1ee3914 ) + [/AI] + + ============================================ + Offset 0xfa8 (4008) + rectype = 0x1051, recsize = 0x13 + -BEGIN DUMP--------------------------------- + 00000000 01 02 00 00 00 00 0B 00 3B 00 00 00 00 1E 00 01 ........;....... + 00000010 00 01 00 ... + -END DUMP----------------------------------- + recordid = 0x1051, size =19 + [AI] + .linkType = 0x01 (1 ) + .referenceType = 0x02 (2 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@e5855a ) + [/AI] + + ============================================ + Offset 0xfbf (4031) + rectype = 0x1051, recsize = 0x13 + -BEGIN DUMP--------------------------------- + 00000000 02 02 00 00 69 01 0B 00 3B 00 00 00 00 1F 00 00 ....i...;....... + 00000010 00 00 00 ... + -END DUMP----------------------------------- + recordid = 0x1051, size =19 + [AI] + .linkType = 0x02 (2 ) + .referenceType = 0x02 (2 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0169 (361 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@95fd19 ) + [/AI] + + ============================================ + Offset 0xfd6 (4054) + rectype = 0x1051, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 03 01 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1051, size =8 + [AI] + .linkType = 0x03 (3 ) + .referenceType = 0x01 (1 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@11b9fb1 ) + [/AI] + + ============================================ + Offset 0xfe2 (4066) + rectype = 0x1006, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 FF FF 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1006, size =8 + [DATAFORMAT] + .pointNumber = 0xFFFF (-1 ) + .seriesIndex = 0x0000 (0 ) + .seriesNumber = 0x0000 (0 ) + .formatFlags = 0x0000 (0 ) + .useExcel4Colors = false + [/DATAFORMAT] + + ============================================ + Offset 0xfee (4078) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0xff2 (4082) + rectype = 0x105f, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x105f, size =2 + [UNKNOWN RECORD] + .id = 105f + [/UNKNOWN RECORD] + + ============================================ + Offset 0xff8 (4088) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0xffc (4092) + rectype = 0x1045, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 00 00 .. + -END DUMP----------------------------------- + recordid = 0x1045, size =2 + [SeriesToChartGroup] + .chartGroupIndex = 0x0000 (0 ) + [/SeriesToChartGroup] + + ============================================ + Offset 0x1002 (4098) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x1006 (4102) + rectype = 0x1044, recsize = 0x4 + -BEGIN DUMP--------------------------------- + 00000000 0A 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x1044, size =4 + [SHTPROPS] + .flags = 0x000A (10 ) + .chartTypeManuallyFormatted = false + .plotVisibleOnly = true + .doNotSizeWithWindow = false + .defaultPlotDimensions = true + .autoPlotArea = false + .empty = 0x00 (0 ) + [/SHTPROPS] + + ============================================ + Offset 0x100e (4110) + rectype = 0x1024, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 02 00 .. + -END DUMP----------------------------------- + recordid = 0x1024, size =2 + [DEFAULTTEXT] + .categoryDataType = 0x0002 (2 ) + [/DEFAULTTEXT] + + ============================================ + Offset 0x1014 (4116) + rectype = 0x1025, recsize = 0x20 + -BEGIN DUMP--------------------------------- + 00000000 02 02 01 00 00 00 00 00 DB FF FF FF C4 FF FF FF ................ + 00000010 00 00 00 00 00 00 00 00 B1 00 4D 00 50 2B 00 00 ..........M.P+.. + -END DUMP----------------------------------- + recordid = 0x1025, size =32 + [TEXT] + .horizontalAlignment = 0x02 (2 ) + .verticalAlignment = 0x02 (2 ) + .displayMode = 0x0001 (1 ) + .rgbColor = 0x00000000 (0 ) + .x = 0xFFFFFFDB (-37 ) + .y = 0xFFFFFFC4 (-60 ) + .width = 0x00000000 (0 ) + .height = 0x00000000 (0 ) + .options1 = 0x00B1 (177 ) + .autoColor = true + .showKey = false + .showValue = false + .vertical = false + .autoGeneratedText = true + .generated = true + .autoLabelDeleted = false + .autoBackground = true + .rotation = 0 + .showCategoryLabelAsPercentage = false + .showValueAsPercentage = false + .showBubbleSizes = false + .showLabel = false + .indexOfColorValue = 0x004D (77 ) + .options2 = 0x2B50 (11088 ) + .dataLabelPlacement = 0 + .textRotation = 0x0000 (0 ) + [/TEXT] + + ============================================ + Offset 0x1038 (4152) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + <!-- Comment to avoid forrest bug --> + ============================================ + Offset 0x103c (4156) + rectype = 0x104f, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 02 00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x104f, size =20 + [UNKNOWN RECORD] + .id = 104f + [/UNKNOWN RECORD] + + ============================================ + Offset 0x1054 (4180) + rectype = 0x1026, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 05 00 .. + -END DUMP----------------------------------- + recordid = 0x1026, size =2 + [FONTX] + .fontIndex = 0x0005 (5 ) + [/FONTX] + + ============================================ + Offset 0x105a (4186) + rectype = 0x1051, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 00 01 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1051, size =8 + [AI] + .linkType = 0x00 (0 ) + .referenceType = 0x01 (1 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@913fe2 ) + [/AI] + + ============================================ + Offset 0x1066 (4198) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x106a (4202) + rectype = 0x1024, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 03 00 .. + -END DUMP----------------------------------- + recordid = 0x1024, size =2 + [DEFAULTTEXT] + .categoryDataType = 0x0003 (3 ) + [/DEFAULTTEXT] + + ============================================ + Offset 0x1070 (4208) + rectype = 0x1025, recsize = 0x20 + -BEGIN DUMP--------------------------------- + 00000000 02 02 01 00 00 00 00 00 DB FF FF FF C4 FF FF FF ................ + 00000010 00 00 00 00 00 00 00 00 B1 00 4D 00 50 2B 00 00 ..........M.P+.. + -END DUMP----------------------------------- + recordid = 0x1025, size =32 + [TEXT] + .horizontalAlignment = 0x02 (2 ) + .verticalAlignment = 0x02 (2 ) + .displayMode = 0x0001 (1 ) + .rgbColor = 0x00000000 (0 ) + .x = 0xFFFFFFDB (-37 ) + .y = 0xFFFFFFC4 (-60 ) + .width = 0x00000000 (0 ) + .height = 0x00000000 (0 ) + .options1 = 0x00B1 (177 ) + .autoColor = true + .showKey = false + .showValue = false + .vertical = false + .autoGeneratedText = true + .generated = true + .autoLabelDeleted = false + .autoBackground = true + .rotation = 0 + .showCategoryLabelAsPercentage = false + .showValueAsPercentage = false + .showBubbleSizes = false + .showLabel = false + .indexOfColorValue = 0x004D (77 ) + .options2 = 0x2B50 (11088 ) + .dataLabelPlacement = 0 + .textRotation = 0x0000 (0 ) + [/TEXT] + + ============================================ + Offset 0x1094 (4244) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x1098 (4248) + rectype = 0x104f, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 02 00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x104f, size =20 + [UNKNOWN RECORD] + .id = 104f + [/UNKNOWN RECORD] + + ============================================ + Offset 0x10b0 (4272) + rectype = 0x1026, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 06 00 .. + -END DUMP----------------------------------- + recordid = 0x1026, size =2 + [FONTX] + .fontIndex = 0x0006 (6 ) + [/FONTX] + + ============================================ + Offset 0x10b6 (4278) + rectype = 0x1051, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 00 01 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1051, size =8 + [AI] + .linkType = 0x00 (0 ) + .referenceType = 0x01 (1 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@1f934ad ) + [/AI] + + ============================================ + Offset 0x10c2 (4290) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x10c6 (4294) + rectype = 0x1046, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 01 00 .. + -END DUMP----------------------------------- + recordid = 0x1046, size =2 + [AXISUSED] + .numAxis = 0x0001 (1 ) + [/AXISUSED] + + ============================================ + Offset 0x10cc (4300) + rectype = 0x1041, recsize = 0x12 + -BEGIN DUMP--------------------------------- + 00000000 00 00 DF 01 00 00 DD 00 00 00 B3 0B 00 00 56 0B ..............V. + 00000010 00 00 .. + -END DUMP----------------------------------- + recordid = 0x1041, size =18 + [AXISPARENT] + .axisType = 0x0000 (0 ) + .x = 0x000001DF (479 ) + .y = 0x000000DD (221 ) + .width = 0x00000BB3 (2995 ) + .height = 0x00000B56 (2902 ) + [/AXISPARENT] + + ============================================ + Offset 0x10e2 (4322) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x10e6 (4326) + rectype = 0x104f, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 02 00 02 00 3A 00 00 00 5E 00 00 00 58 0D 00 00 ....:...^...X... + 00000010 E5 0E 00 00 .... + -END DUMP----------------------------------- + recordid = 0x104f, size =20 + [UNKNOWN RECORD] + .id = 104f + [/UNKNOWN RECORD] + + ============================================ + Offset 0x10fe (4350) + rectype = 0x101d, recsize = 0x12 + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 .. + -END DUMP----------------------------------- + recordid = 0x101d, size =18 + [AXIS] + .axisType = 0x0000 (0 ) + .reserved1 = 0x00000000 (0 ) + .reserved2 = 0x00000000 (0 ) + .reserved3 = 0x00000000 (0 ) + .reserved4 = 0x00000000 (0 ) + [/AXIS] + + ============================================ + Offset 0x1114 (4372) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x1118 (4376) + rectype = 0x1020, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 01 00 01 00 01 00 01 00 ........ + -END DUMP----------------------------------- + recordid = 0x1020, size =8 + [CATSERRANGE] + .crossingPoint = 0x0001 (1 ) + .labelFrequency = 0x0001 (1 ) + .tickMarkFrequency = 0x0001 (1 ) + .options = 0x0001 (1 ) + .valueAxisCrossing = true + .crossesFarRight = false + .reversed = false + [/CATSERRANGE] + + ============================================ + Offset 0x1124 (4388) + rectype = 0x1062, recsize = 0x12 + -BEGIN DUMP--------------------------------- + 00000000 1C 90 39 90 02 00 00 00 01 00 00 00 00 00 1C 90 ..9............. + 00000010 FF 00 .. + -END DUMP----------------------------------- + recordid = 0x1062, size =18 + [AXCEXT] + .minimumCategory = 0x901C (-28644 ) + .maximumCategory = 0x9039 (-28615 ) + .majorUnitValue = 0x0002 (2 ) + .majorUnit = 0x0000 (0 ) + .minorUnitValue = 0x0001 (1 ) + .minorUnit = 0x0000 (0 ) + .baseUnit = 0x0000 (0 ) + .crossingPoint = 0x901C (-28644 ) + .options = 0x00FF (255 ) + .defaultMinimum = true + .defaultMaximum = true + .defaultMajor = true + .defaultMinorUnit = true + .isDate = true + .defaultBase = true + .defaultCross = true + .defaultDateSettings = true + [/AXCEXT] + + ============================================ + Offset 0x113a (4410) + rectype = 0x101e, recsize = 0x1e + -BEGIN DUMP--------------------------------- + 00000000 02 00 03 01 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 00 00 00 00 23 00 4D 00 2D 00 ........#.M.-. + -END DUMP----------------------------------- + recordid = 0x101e, size =30 + [TICK] + .majorTickType = 0x02 (2 ) + .minorTickType = 0x00 (0 ) + .labelPosition = 0x03 (3 ) + .background = 0x01 (1 ) + .labelColorRgb = 0x00000000 (0 ) + .zero1 = 0x0000 (0 ) + .zero2 = 0x0000 (0 ) + .options = 0x0023 (35 ) + .autoTextColor = true + .autoTextBackground = true + .rotation = 0 + .autorotate = true + .tickColor = 0x004D (77 ) + .zero3 = 0x002D (45 ) + [/TICK] + + ============================================ + Offset 0x115c (4444) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x1160 (4448) + rectype = 0x101d, recsize = 0x12 + -BEGIN DUMP--------------------------------- + 00000000 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 .. + -END DUMP----------------------------------- + recordid = 0x101d, size =18 + [AXIS] + .axisType = 0x0001 (1 ) + .reserved1 = 0x00000000 (0 ) + .reserved2 = 0x00000000 (0 ) + .reserved3 = 0x00000000 (0 ) + .reserved4 = 0x00000000 (0 ) + [/AXIS] + + <!-- Comment to avoid forrest bug --> + ============================================ + Offset 0x1176 (4470) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x117a (4474) + rectype = 0x101f, recsize = 0x2a + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000020 00 00 00 00 00 00 00 00 1F 01 .......... + -END DUMP----------------------------------- + recordid = 0x101f, size =42 + [VALUERANGE] + .minimumAxisValue = (0.0 ) + .maximumAxisValue = (0.0 ) + .majorIncrement = (0.0 ) + .minorIncrement = (0.0 ) + .categoryAxisCross = (0.0 ) + .options = 0x011F (287 ) + .automaticMinimum = true + .automaticMaximum = true + .automaticMajor = true + .automaticMinor = true + .automaticCategoryCrossing = true + .logarithmicScale = false + .valuesInReverse = false + .crossCategoryAxisAtMaximum = false + .reserved = true + [/VALUERANGE] + + ============================================ + Offset 0x11a8 (4520) + rectype = 0x101e, recsize = 0x1e + -BEGIN DUMP--------------------------------- + 00000000 02 00 03 01 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 00 00 00 00 23 00 4D 00 00 00 ........#.M... + -END DUMP----------------------------------- + recordid = 0x101e, size =30 + [TICK] + .majorTickType = 0x02 (2 ) + .minorTickType = 0x00 (0 ) + .labelPosition = 0x03 (3 ) + .background = 0x01 (1 ) + .labelColorRgb = 0x00000000 (0 ) + .zero1 = 0x0000 (0 ) + .zero2 = 0x0000 (0 ) + .options = 0x0023 (35 ) + .autoTextColor = true + .autoTextBackground = true + .rotation = 0 + .autorotate = true + .tickColor = 0x004D (77 ) + .zero3 = 0x0000 (0 ) + [/TICK] + + ============================================ + Offset 0x11ca (4554) + rectype = 0x1021, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 01 00 .. + -END DUMP----------------------------------- + recordid = 0x1021, size =2 + [AXISLINEFORMAT] + .axisType = 0x0001 (1 ) + [/AXISLINEFORMAT] + + ============================================ + Offset 0x11d0 (4560) + rectype = 0x1007, recsize = 0xc + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 FF FF 09 00 4D 00 ..........M. + -END DUMP----------------------------------- + recordid = 0x1007, size =12 + [LINEFORMAT] + .lineColor = 0x00000000 (0 ) + .linePattern = 0x0000 (0 ) + .weight = 0xFFFF (-1 ) + .format = 0x0009 (9 ) + .auto = true + .drawTicks = false + .unknown = false + .colourPaletteIndex = 0x004D (77 ) + [/LINEFORMAT] + + ============================================ + Offset 0x11e0 (4576) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x11e4 (4580) + rectype = 0x1035, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1035, size =0 + [PLOTAREA] + [/PLOTAREA] + + ============================================ + Offset 0x11e8 (4584) + rectype = 0x1032, recsize = 0x4 + -BEGIN DUMP--------------------------------- + 00000000 00 00 03 00 .... + -END DUMP----------------------------------- + recordid = 0x1032, size =4 + [FRAME] + .borderType = 0x0000 (0 ) + .options = 0x0003 (3 ) + .autoSize = true + .autoPosition = true + [/FRAME] + + ============================================ + Offset 0x11f0 (4592) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x11f4 (4596) + rectype = 0x1007, recsize = 0xc + -BEGIN DUMP--------------------------------- + 00000000 80 80 80 00 00 00 00 00 00 00 17 00 ............ + -END DUMP----------------------------------- + recordid = 0x1007, size =12 + [LINEFORMAT] + .lineColor = 0x00808080 (8421504 ) + .linePattern = 0x0000 (0 ) + .weight = 0x0000 (0 ) + .format = 0x0000 (0 ) + .auto = false + .drawTicks = false + .unknown = false + .colourPaletteIndex = 0x0017 (23 ) + [/LINEFORMAT] + + ============================================ + Offset 0x1204 (4612) + rectype = 0x100a, recsize = 0x10 + -BEGIN DUMP--------------------------------- + 00000000 C0 C0 C0 00 00 00 00 00 01 00 00 00 16 00 4F 00 ..............O. + -END DUMP----------------------------------- + recordid = 0x100a, size =16 + [AREAFORMAT] + .foregroundColor = 0x00C0C0C0 (12632256 ) + .backgroundColor = 0x00000000 (0 ) + .pattern = 0x0001 (1 ) + .formatFlags = 0x0000 (0 ) + .automatic = false + .invert = false + .forecolorIndex = 0x0016 (22 ) + .backcolorIndex = 0x004F (79 ) + [/AREAFORMAT] + + ============================================ + Offset 0x1218 (4632) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x121c (4636) + rectype = 0x1014, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x1014, size =20 + [CHARTFORMAT] + .xPosition = 0 + .yPosition = 0 + .width = 0 + .height = 0 + .grBit = 0 + [/CHARTFORMAT] + + ============================================ + Offset 0x1234 (4660) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x1238 (4664) + rectype = 0x1017, recsize = 0x6 + -BEGIN DUMP--------------------------------- + 00000000 00 00 96 00 00 00 ...... + -END DUMP----------------------------------- + recordid = 0x1017, size =6 + [BAR] + .barSpace = 0x0000 (0 ) + .categorySpace = 0x0096 (150 ) + .formatFlags = 0x0000 (0 ) + .horizontal = false + .stacked = false + .displayAsPercentage = false + .shadow = false + [/BAR] + + ============================================ + Offset 0x1242 (4674) + rectype = 0x1022, recsize = 0xa + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 00 00 00 00 0F 00 .......... + -END DUMP----------------------------------- + recordid = 0x1022, size =10 + [UNKNOWN RECORD] + .id = 1022 + [/UNKNOWN RECORD] + + ============================================ + Offset 0x1250 (4688) + rectype = 0x1015, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 D6 0D 00 00 1E 06 00 00 B5 01 00 00 D5 00 00 00 ................ + 00000010 03 01 1F 00 .... + -END DUMP----------------------------------- + recordid = 0x1015, size =20 + [LEGEND] + .xAxisUpperLeft = 0x00000DD6 (3542 ) + .yAxisUpperLeft = 0x0000061E (1566 ) + .xSize = 0x000001B5 (437 ) + .ySize = 0x000000D5 (213 ) + .type = 0x03 (3 ) + .spacing = 0x01 (1 ) + .options = 0x001F (31 ) + .autoPosition = true + .autoSeries = true + .autoXPositioning = true + .autoYPositioning = true + .vertical = true + .dataTable = false + [/LEGEND] + + ============================================ + Offset 0x1268 (4712) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x126c (4716) + rectype = 0x104f, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 05 00 02 00 D6 0D 00 00 1E 06 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x104f, size =20 + [UNKNOWN RECORD] + .id = 104f + [/UNKNOWN RECORD] + + ============================================ + Offset 0x1284 (4740) + rectype = 0x1025, recsize = 0x20 + -BEGIN DUMP--------------------------------- + 00000000 02 02 01 00 00 00 00 00 DB FF FF FF C4 FF FF FF ................ + 00000010 00 00 00 00 00 00 00 00 B1 00 4D 00 70 37 00 00 ..........M.p7.. + -END DUMP----------------------------------- + recordid = 0x1025, size =32 + [TEXT] + .horizontalAlignment = 0x02 (2 ) + .verticalAlignment = 0x02 (2 ) + .displayMode = 0x0001 (1 ) + .rgbColor = 0x00000000 (0 ) + .x = 0xFFFFFFDB (-37 ) + .y = 0xFFFFFFC4 (-60 ) + .width = 0x00000000 (0 ) + .height = 0x00000000 (0 ) + .options1 = 0x00B1 (177 ) + .autoColor = true + .showKey = false + .showValue = false + .vertical = false + .autoGeneratedText = true + .generated = true + .autoLabelDeleted = false + .autoBackground = true + .rotation = 0 + .showCategoryLabelAsPercentage = false + .showValueAsPercentage = false + .showBubbleSizes = false + .showLabel = false + .indexOfColorValue = 0x004D (77 ) + .options2 = 0x3770 (14192 ) + .dataLabelPlacement = 0 + .textRotation = 0x0000 (0 ) + [/TEXT] + + ============================================ + Offset 0x12a8 (4776) + rectype = 0x1033, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1033, size =0 + [BEGIN] + [/BEGIN] + + ============================================ + Offset 0x12ac (4780) + rectype = 0x104f, recsize = 0x14 + -BEGIN DUMP--------------------------------- + 00000000 02 00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ + 00000010 00 00 00 00 .... + -END DUMP----------------------------------- + recordid = 0x104f, size =20 + [UNKNOWN RECORD] + .id = 104f + [/UNKNOWN RECORD] + + ============================================ + Offset 0x12c4 (4804) + rectype = 0x1051, recsize = 0x8 + -BEGIN DUMP--------------------------------- + 00000000 00 01 00 00 00 00 00 00 ........ + -END DUMP----------------------------------- + recordid = 0x1051, size =8 + [AI] + .linkType = 0x00 (0 ) + .referenceType = 0x01 (1 ) + .options = 0x0000 (0 ) + .customNumberFormat = false + .indexNumberFmtRecord = 0x0000 (0 ) + .formulaOfLink = (org.apache.poi.hssf.record.LinkedDataFormulaField@1d05c81 ) + [/AI] + + ============================================ + Offset 0x12d0 (4816) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + <!-- Comment to avoid forrest bug --> + ============================================ + Offset 0x12d4 (4820) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x12d8 (4824) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x12dc (4828) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + Offset 0x12e0 (4832) + rectype = 0x1034, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0x1034, size =0 + [END] + [/END] + + ============================================ + rectype = 0x200, recsize = 0xe + -BEGIN DUMP--------------------------------- + 00000000 00 00 00 00 1F 00 00 00 00 00 01 00 00 00 .............. + -END DUMP----------------------------------- + recordid = 0x200, size =14 + [DIMENSIONS] + .firstrow = 0 + .lastrow = 1f + .firstcol = 0 + .lastcol = 1 + .zero = 0 + [/DIMENSIONS] + + ============================================ + rectype = 0x1065, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 02 00 .. + -END DUMP----------------------------------- + recordid = 0x1065, size =2 + [SINDEX] + .index = 0x0002 (2 ) + [/SINDEX] + + ============================================ + rectype = 0x1065, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 01 00 .. + -END DUMP----------------------------------- + recordid = 0x1065, size =2 + [SINDEX] + .index = 0x0001 (1 ) + [/SINDEX] + + ============================================ + rectype = 0x1065, recsize = 0x2 + -BEGIN DUMP--------------------------------- + 00000000 03 00 .. + -END DUMP----------------------------------- + recordid = 0x1065, size =2 + [SINDEX] + .index = 0x0003 (3 ) + [/SINDEX] + + ============================================ + rectype = 0xa, recsize = 0x0 + -BEGIN DUMP--------------------------------- + **NO RECORD DATA** + -END DUMP----------------------------------- + recordid = 0xa, size =0 + [EOF] + [/EOF] + + + </source> + <p> + The next section breaks those records down into an easier + to read format: + </p> + <source> +[UNKNOWN RECORD:ec] +[UNKNOWN RECORD:5d] +[BOF RECORD] + [HEADER] + [FOOTER] + [HCENTER] + [VCENTER] + [PRINTSETUP] + [UNKNOWN RECORD:33] + [FBI] + [FBI] + [PROTECT] + [UNITS] + [CHART] + [BEGIN] + [SCL] // zoom magnification + [PLOTGROWTH] // font scaling + [FRAME] // border around text + [BEGIN] // default line and area format + [LINEFORMAT] + [AREAFORMAT] + [END] + [SERIES] // start of series + [BEGIN] + [AI] // LINK_TYPE_TITLE_OR_TEXT + [AI] // LINK_TYPE_VALUES + [AI] // LINK_TYPE_CATEGORIES + [AI] // ?? + [DATAFORMAT] // Formatting applies to series? + [BEGIN] // ?? + [UNKNOWN RECORD] + [END] + [SeriesToChartGroup] // Used to support > 1 chart? + [END] + [SHTPROPS] // Some defaults for how chart is displayed. + [DEFAULTTEXT] // Describes the characteristics of the next + // record + [TEXT] // Details of the text that follows in the + // next section + [BEGIN] + [UNKNOWN RECORD] // POS record... looks like I missed this one. + // docs seem to indicate it's better to use + // defaults... + [FONTX] // index to font record. + [AI] // link to text? seems to be linking to nothing + [END] + [DEFAULTTEXT] // contains a category type of 3 which is not + // documented (sigh). + [TEXT] // defines position, color etc for text on chart. + [BEGIN] + [UNKNOWN RECORD] // Another pos record + [FONTX] // font + [AI] // reference type is DIRECT (not sure what this + // means) + [END] + [AXISUSED] // number of axis on the chart. + [AXISPARENT] // axis size and location + [BEGIN] // beginning of axis details + [UNKNOWN RECORD] // Another pos record. + [AXIS] // Category axis + [BEGIN] + [CATSERRANGE] // defines tick marks and other stuff + [AXCEXT] // unit information + [TICK] // tick formating characteristics + [END] + [AXIS] // Value axis + [BEGIN] + [VALUERANGE] // defines tick marks and other stuff + [TICK] // tick formating characteristics + [AXISLINEFORMAT] // major grid line axis format + [LINEFORMAT] // what do the lines look like? + [END] + [PLOTAREA] // marks that the frame following belongs + // to the frame. + [FRAME] // border + [BEGIN] + [LINEFORMAT] // border line + [AREAFORMAT] // border area + [END] + [CHARTFORMAT] // marks a chart group + [BEGIN] + [BAR] // indicates a bar chart + [UNKNOWN RECORD] // apparently this record is ignoreable + [LEGEND] // positioning for the legend + [BEGIN] + [UNKNOWN RECORD] // another position record. + [TEXT] // details of the text that follows + // in the next section + [BEGIN] + [UNKNOWN RECORD] // yet another pos record + [AI] // another link (of type direct) + [END] + [END] + [END] + [END] + [END] + [DIMENSIONS] + [SINDEX] + [SINDEX] + [SINDEX] +[EOF] + </source> + <p> + Just a quick note on some of the unknown records: + </p> + <ul> + <li>EC: MSODRAWING - A Microsoft drawing record. (Need to + track down where this is documented).</li> + <li>5D: OBJ: Description of a drawing object. (This is going to + be a PITA to implement).</li> + <li>33: Not documented. :-(</li> + <li>105f: Not documented. :-(</li> + <li>104f: POS: Position record (should be able to safely leave this out).</li> + <li>1022: CHARTFORMATLINK: Can be left out.</li> + </ul> + <p> + It is currently suspected that many of those records could be + left out when generating a bar chart from scratch. The way + we will be proceeding with this is to write code that generates + most of these records and then start removing them to see + how this effects the chart in excel. + </p> + </section> + <section><title>Inserting the Chart into the Workbook</title> + <ul> + <li> + Unknown record (sid=00eb) is inserted before the SST + record. + </li> + </ul> + <source> + ============================================ + rectype = 0xeb, recsize = 0x5a + -BEGIN DUMP--------------------------------- + 00000000 0F 00 00 F0 52 00 00 00 00 00 06 F0 18 00 00 00 ....R........... + 00000010 01 08 00 00 02 00 00 00 02 00 00 00 01 00 00 00 ................ + 00000020 01 00 00 00 03 00 00 00 33 00 0B F0 12 00 00 00 ........3....... + 00000030 BF 00 08 00 08 00 81 01 09 00 00 08 C0 01 40 00 ..............@. + 00000040 00 08 40 00 1E F1 10 00 00 00 0D 00 00 08 0C 00 ..@............. + 00000050 00 08 17 00 00 08 F7 00 00 10 .......... + -END DUMP----------------------------------- + recordid = 0xeb, size =90 + [UNKNOWN RECORD:eb] + .id = eb + [/UNKNOWN RECORD] + + ============================================ + </source> + <ul> + <li> + Any extra font records are inserted as needed + </li> + <li> + Chart records inserted after DBCell records. + </li> + </ul> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/converting.xml b/src/documentation/content/xdocs/components/spreadsheet/converting.xml new file mode 100644 index 0000000000..0700cc4e43 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/converting.xml @@ -0,0 +1,232 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</title> + <authors> + <person email="nick@apache.org" name="Nick Burch" id="NB"/> + </authors> + </header> + <body> +<section><title>Things that have to be changed when upgrading to POI 3.5</title> + <p>Wherever possible, we have tried to ensure that you can use your + existing POI code with POI 3.5 without requiring any changes. However, + Java doesn't always make that easy, and unfortunately there are a + few changes that may be required for some users.</p> + <section><title>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</title> + <p>Annoyingly, java will not let you access a static inner class via + a child of the parent one. So, all references to + <em>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</em> + will need to be changed to + <em>org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue</em> + </p> + </section> + <section><title>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</title> + <p>Annoyingly, java will not let you access a static inner class via + a child of the parent one. So, all references to + <em>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</em> + will need to be changed to + <em>org.apache.poi.ss.usermodel.Row.MissingCellPolicy</em> + </p> + </section> + <section><title>DDF and org.apache.poi.hssf.record.RecordFormatException</title> + <p>Previously, record level errors within DDF would throw an + exception from the hssf class hierarchy. Now, record level errors + within DDF will throw a more general RecordFormatException, + <em>org.apache.poi.util.RecordFormatException</em></p> + <p>In addition, org.apache.poi.hssf.record.RecordFormatException + has been changed to inherit from the new + <em>org.apache.poi.util.RecordFormatException</em>, so you may + wish to change catches of the hssf version to the new util version. + </p> + </section> + </section> + <section><title>Converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</title> + + <section><title>Why change?</title> + <p>If you have existing HSSF usermodel code that works just + fine, and you don't want to use the new OOXML XSSF support, + then you probably don't need to. Your existing HSSF only code + will continue to work just fine.</p> + <p>However, if you want to be able to work with both HSSF for + your .xls files, and also XSSF for .xslx files, then you will + need to make some slight tweaks to your code.</p> + </section> + <section><title>org.apache.poi.ss.usermodel</title> + <p>The new SS usermodel (org.apache.poi.ss.usermodel) is very + heavily based on the old HSSF usermodel + (org.apache.poi.hssf.usermodel). The main difference is that + the package name and class names have been tweaked to remove + HSSF from them. Otherwise, the new SS Usermodel interfaces + should provide the same functionality.</p> + </section> + <section><title>Constructors</title> + <p>Calling the empty HSSFWorkbook remains as the way to + create a new, empty Workbook object. To open an existing + Workbook, you should now call WorkbookFactory.create(inp).</p> + <p>For all other cases when you would have called a + Usermodel constructor, such as 'new HSSFRichTextString()' or + 'new HSSFDataFormat', you should instead use a CreationHelper. + There's a method on the Workbook to get a CreationHelper, and + the CreationHelper will then handle constructing new objects + for you.</p> + </section> + <section><title>Other Code</title> + <p>For all other code, generally change a reference from + org.apache.poi.hssf.usermodel.HSSFFoo to a reference to + org.apache.poi.ss.usermodel.Foo. Method signatures should + otherwise remain the same, and it should all then work for + both XSSF and HSSF.</p> + </section> + </section> + <section><title>Worked Examples</title> + <section><title>Old HSSF Code</title> +<source><![CDATA[ +// import org.apache.poi.hssf.usermodel.*; + +HSSFWorkbook wb = new HSSFWorkbook(); +// create a new sheet +HSSFSheet s = wb.createSheet(); +// declare a row object reference +HSSFRow r = null; +// declare a cell object reference +HSSFCell c = null; +// create 2 cell styles +HSSFCellStyle cs = wb.createCellStyle(); +HSSFCellStyle cs2 = wb.createCellStyle(); +HSSFDataFormat df = wb.createDataFormat(); + +// create 2 fonts objects +HSSFFont f = wb.createFont(); +HSSFFont f2 = wb.createFont(); + +// Set font 1 to 12 point type, blue and bold +f.setFontHeightInPoints((short) 12); +f.setColor( HSSFColor.RED.index ); +f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + +// Set font 2 to 10 point type, red and bold +f2.setFontHeightInPoints((short) 10); +f2.setColor( HSSFFont.RED.index ); +f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + +// Set cell style and formatting +cs.setFont(f); +cs.setDataFormat(df.getFormat("#,##0.0")); + +// Set the other cell style and formatting +cs2.setBorderBottom(cs2.BORDER_THIN); +cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); +cs2.setFont(f2); + + +// Define a few rows +for(short rownum = (short)0; rownum < 30; rownum++) { + HSSFRow r = s.createRow(rownum); + for(short cellnum = (short)0; cellnum < 10; cellnum += 2) { + HSSFCell c = r.createCell(cellnum); + HSSFCell c2 = r.createCell(cellnum+1); + + c.setCellValue((double)rownum + (cellnum/10)); + c2.setCellValue(new HSSFRichTextString("Hello! " + cellnum); + } +} + +// Save +FileOutputStream out = new FileOutputStream("workbook.xls"); +wb.write(out); +out.close(); + ]]></source> + </section> + <section><title>New, generic SS Usermodel Code</title> +<source><![CDATA[ +// import org.apache.poi.ss.usermodel.*; + +Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; +for(int i=0; i<wbs.length; i++) { + Workbook wb = wbs[i]; + CreationHelper createHelper = wb.getCreationHelper(); + + // create a new sheet + Sheet s = wb.createSheet(); + // declare a row object reference + Row r = null; + // declare a cell object reference + Cell c = null; + // create 2 cell styles + CellStyle cs = wb.createCellStyle(); + CellStyle cs2 = wb.createCellStyle(); + DataFormat df = wb.createDataFormat(); + + // create 2 fonts objects + Font f = wb.createFont(); + Font f2 = wb.createFont(); + + // Set font 1 to 12 point type, blue and bold + f.setFontHeightInPoints((short) 12); + f.setColor( IndexedColors.RED.getIndex() ); + f.setBoldweight(Font.BOLDWEIGHT_BOLD); + + // Set font 2 to 10 point type, red and bold + f2.setFontHeightInPoints((short) 10); + f2.setColor( IndexedColors.RED.getIndex() ); + f2.setBoldweight(Font.BOLDWEIGHT_BOLD); + + // Set cell style and formatting + cs.setFont(f); + cs.setDataFormat(df.getFormat("#,##0.0")); + + // Set the other cell style and formatting + cs2.setBorderBottom(cs2.BORDER_THIN); + cs2.setDataFormat(df.getFormat("text")); + cs2.setFont(f2); + + + // Define a few rows + for(int rownum = 0; rownum < 30; rownum++) { + Row r = s.createRow(rownum); + for(int cellnum = 0; cellnum < 10; cellnum += 2) { + Cell c = r.createCell(cellnum); + Cell c2 = r.createCell(cellnum+1); + + c.setCellValue((double)rownum + (cellnum/10)); + c2.setCellValue( + createHelper.createRichTextString("Hello! " + cellnum) + ); + } + } + + // Save + String filename = "workbook.xls"; + if(wb instanceof XSSFWorkbook) { + filename = filename + "x"; + } + + FileOutputStream out = new FileOutputStream(filename); + wb.write(out); + out.close(); +} + ]]></source> + </section> +</section> +</body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/diagram1.xml b/src/documentation/content/xdocs/components/spreadsheet/diagram1.xml new file mode 100644 index 0000000000..438da0e7c8 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/diagram1.xml @@ -0,0 +1,40 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>HSSF</title> + <subtitle>Overview</subtitle> + <authors> + <person name="Andrew C. Oliver" email="acoliver@apache.org"/> + <person name="Nicola Ken Barozzi" email="barozzi@nicolaken.com"/> + </authors> + </header> + + <body> + <section> + <title>Usermodel Class Diagram by Matthew Young</title> + <p> + <img src="images/usermodel.gif" alt="Usermodel"/> + </p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/diagrams.xml b/src/documentation/content/xdocs/components/spreadsheet/diagrams.xml new file mode 100644 index 0000000000..208cabfa6f --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/diagrams.xml @@ -0,0 +1,56 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>HSSF</title> + <subtitle>Overview</subtitle> + <authors> + <person name="Andrew C. Oliver" email="acoliver@apache.org"/> + <person name="Nicola Ken Barozzi" email="barozzi@nicolaken.com"/> + </authors> + </header> + + <body> + <section><title>Overview</title> + <p> + This section is intended for diagrams (UML/etc) that help + explain HSSF. + </p> + <ul> + <li> + <a href="diagram1.html">HSSF usermodel class diagram</a> - + by Matthew Young (myoung at westernasset dot com) + </li> + </ul> + <p> + Have more? Add a new "bug" to the bug database with [DOCUMENTATION] + prefacing the description and a link to the file on an http server + somewhere. If you don't have your own webserver, then you can email it + to (acoliver at apache dot org) provided its < 5MB. Diagrams should be + in some format that can be read at least on Linux and Windows. Diagrams + that can be edited are preferable, but lets face it, there aren't too + many good affordable UML tools yet! And no they don't HAVE to be UML... + just useful. + </p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/eval-devguide.xml b/src/documentation/content/xdocs/components/spreadsheet/eval-devguide.xml new file mode 100644 index 0000000000..2d49b0aa09 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/eval-devguide.xml @@ -0,0 +1,591 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Developing Formula Evaluation</title> + <authors> + <person email="amoweb@yahoo.com" name="Amol Deshmukh" id="AD"/> + <person email="yegor@apache.org" name="Yegor Kozlov" id="YK"/> + </authors> + </header> + <body> + <section><title>Introduction</title> + <p> + This document is for developers wishing to contribute to the + FormulaEvaluator API functionality. + </p> + <p> + When evaluating workbooks you may encounter an <code>org.apache.poi.ss.formula.eval.NotImplementedException</code> + which indicates that a function is not (yet) supported by POI. Is there a workaround? + Yes, the POI framework makes it easy to add implementation of new functions. Prior to POI-3.8 + you had to checkout the source code from svn and make a custom build with your function implementation. + Since POI-3.8 you can register new functions in run-time. + </p> + <p> + Currently, contribution is desired for implementing the standard MS + Excel functions. Placeholder classes for these have been created, + contributors only need to insert implementation for the + individual <code>evaluate()</code> methods that do the actual evaluation. + </p> + </section> + <section><title>Overview of FormulaEvaluator </title> + <p> + Briefly, a formula string (along with the sheet and workbook that + form the context in which the formula is evaluated) is first parsed + into Reverse Polish Notation (RPN) tokens using the <code>FormulaParser</code> class. + (If you don't know what RPN tokens are, now is a good time to + read <a href="http://www-stone.ch.cam.ac.uk/documentation/rrf/rpn.html"> + Anthony Stone's description of RPN</a>.) + </p> + <section><title> The big picture</title> + <p> + RPN tokens are mapped to <code>Eval</code> classes. (The class hierarchy for the <code>Eval</code>s + is best understood if you view it in a class diagram + viewer.) Depending on the type of RPN token (also called <code>Ptg</code>s + henceforth since that is what the <code>FormulaParser</code> calls the classes), a + specific type of <code>Eval</code> wrapper is constructed to wrap the RPN token and + is pushed on the stack, unless the <code>Ptg</code> is an <code>OperationPtg</code>. If it is an + <code>OperationPtg</code>, an <code>OperationEval</code> instance is created for the specific + type of <code>OperationPtg</code>. And depending on how many operands it takes, + that many <code>Eval</code>s are popped of the stack and passed in an array to + the <code>OperationEval</code> instance's evaluate method which returns an <code>Eval</code> + of subtype <code>ValueEval</code>. Thus an operation in the formula is evaluated. + </p> + <note> An <code>Eval</code> is of subinterface <code>ValueEval</code> or <code>OperationEval</code>. + Operands are always <code>ValueEval</code>s, and operations are always <code>OperationEval</code>s.</note> + <p> + <code>OperationEval.evaluate(Eval[])</code> returns an <code>Eval</code> which is supposed + to be an instance of one of the implementations of + <code>ValueEval</code>. The <code>ValueEval</code> resulting from <code>evaluate()</code> is pushed on the + stack and the next RPN token is evaluated. This continues until + eventually there are no more RPN tokens, at which point, if the formula + string was correctly parsed, there should be just one <code>Eval</code> on the + stack — which contains the result of evaluating the formula. + </p> + <p> + Two special <code>Ptg</code>s — <code>AreaPtg</code> and <code>ReferencePtg</code> — + are handled a little differently, but the code should be self + explanatory for that. Very briefly, the cells included in <code>AreaPtg</code> and + <code>RefPtg</code> are examined and their values are populated in individual + <code>ValueEval</code> objects which are set into the implementations of + <code>AreaEval</code> and <code>RefEval</code>. + </p> + <p> + <code>OperationEval</code>s for the standard operators have been implemented and tested. + </p> + </section> + </section> + <section><title>What functions are supported?</title> + <p> + As of release 5.2.0, POI implements 202 built-in functions, + see <a href="#appendixA">Appendix A</a> for the list of supported functions with an implementation. + You can programmatically list supported / unsupported functions using the following helper methods: + </p> +<source>import org.apache.poi.ss.formula.ss.formula.WorkbookEvaluator; + +// list of functions that POI can evaluate +Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames(); + +// list of functions that are not supported by POI +Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames(); +</source> + <section><title>I need a function that isn't supported!</title> + <p> + If you need a function that POI doesn't currently support, you have two options. + You can create the function yourself, and have your program add it to POI at + run-time. Doing this will help you get the function you need as soon as possible. + The other option is to create the function yourself, and build it into the POI library, + possibly contributing the code to the POI project. Doing this will help you get the + function you need, but you'll have to build POI from source yourself. And if you + contribute the code, you'll help others who need the function in the future, because + it will already be supported in the next release of POI. The two options require + almost identical code, but the process of deploying the function is different. + If your function is a User Defined Function, you'll always take the run-time option, + as POI doesn't distribute UDFs. + </p> + <p> + In the sections ahead, we'll implement the Excel <code>SQRTPI()</code> function, first + at run-time, and then we'll show how change it to a library-based implementation. + </p> + </section> + </section> + <section><title>Two base interfaces to start your implementation</title> + <p> + All Excel formula function classes implement either the + <code>org.apache.poi.hssf.record.formula.functions.Function</code> or the + <code>org.apache.poi.hssf.record.formula.functions.FreeRefFunction</code> interface. + <code>Function</code> is a common interface for the functions defined in the Binary Excel File Format (BIFF8): these are "classic" Excel functions like <code>SUM</code>, <code>COUNT</code>, <code>LOOKUP</code>, <em>etc</em>. + <code>FreeRefFunction</code> is a common interface for the functions from the Excel Analysis ToolPak, for User Defined Functions that you create, + and for Excel built-in functions that have been defined since BIFF8 was defined. + In the future these two interfaces are expected be unified into one, but for now you have to start your implementation from two slightly different roots. + </p> + + <section><title>Which interface to start from?</title> + <p> + You are about to implement a function and don't know which interface to start from: <code>Function</code> or <code>FreeRefFunction</code>. + You should use <code>Function</code> if the function is part of the Excel BIFF8 + definition, and <code>FreeRefFunction</code> for a function that is part of the Excel Analysis ToolPak, was added to Excel after BIFF8, or that you are creating yourself. + </p> + <p> + You can check the list of Analysis ToolPak functions defined in <code>org.apache.poi.ss.formula.atp.AnalysisToolPak.createFunctionsMap()</code> + to see if the function is part of the Analysis ToolPak. + The list of BIFF8 functions is defined as a text file, in the + <code>src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt</code> file. + </p> + <p> + You can also use the following code to check which base class your function should implement, if it is not a User Defined function (UDFs must implement <code>FreeRefFunction</code>): + </p> +<source>import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak; + +if (!AnalysisToolPak.isATPFunction(functionName)){ + // the function must implement org.apache.poi.hssf.record.formula.functions.Function +} else { + // the function must implement org.apache.poi.hssf.record.formula.functions.FreeRefFunction +} +</source> + </section> + </section> + <section><title>Implementing a function.</title> + <p> + Here is the fun part: let's walk through the implementation of the Excel function <code>SQRTPI()</code>, + which POI doesn not currently support. + </p> + <p> + <code>AnalysisToolPak.isATPFunction("SQRTPI")</code> returns true, so this is an Analysis ToolPak function. + Thus the base interface must be <code>FreeRefFunction</code>. The same would be true if we were implementing + a UDF. + </p> + <p> + Because we're taking the run-time deployment option, we'll create this new function in a source + file in our own program. Our function will return an <code>Eval</code> that is either + it's proper result, or an <code>ErrorEval</code> that describes the error. All that work + is done in the function's <code>evaluate()</code> method: + </p> +<source>package ...; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +public final class SqrtPi implements FreeRefFunction { + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + ValueEval arg0 = args[0]; + int srcRowIndex = ec.getRowIndex(); + int srcColumnIndex = ec.getColumnIndex(); + try { + // Retrieves a single value from a variety of different argument types according to standard + // Excel rules. Does not perform any type conversion. + ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); + + // Applies some conversion rules if the supplied value is not already a number. + // Throws EvaluationException(#VALUE!) if the supplied parameter is not a number + double arg = OperandResolver.coerceValueToDouble(ve); + + // this where all the heavy-lifting happens + double result = Math.sqrt(arg*Math.PI); + + // Excel uses the error code #NUM! instead of IEEE NaN and Infinity, + // so when a numeric function evaluates to Double.NaN or Double.Infinity, + // be sure to translate the result to the appropriate error code + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + + return new NumberEval(result); + } catch (EvaluationException e){ + return e.getErrorEval(); + } + } +} +</source> + <p> + If our function had been one of the BIFF8 Excel built-ins, it would have been based on + the <code>Function</code> interface instead. + There are sub-interfaces of <code>Function</code> that make life easier when implementing numeric functions + or functions + with a small, fixed number of arguments: + </p> + <ul> + <li><code>org.apache.poi.hssf.record.formula.functions.NumericFunction</code></li> + <li><code>org.apache.poi.hssf.record.formula.functions.Fixed0ArgFunction</code></li> + <li><code>org.apache.poi.hssf.record.formula.functions.Fixed1ArgFunction</code></li> + <li><code>org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction</code></li> + <li><code>org.apache.poi.hssf.record.formula.functions.Fixed3ArgFunction</code></li> + <li><code>org.apache.poi.hssf.record.formula.functions.Fixed4ArgFunction</code></li> + </ul> + <p> + Since <code>SQRTPI()</code> takes exactly one argument, we would start our implementation from + <code>Fixed1ArgFunction</code>. The differences for a BIFF8 <code>Fixed1ArgFunction</code> + are pretty small: + </p> +<source>package ...; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.Fixed1ArgFunction; + +public final class SqrtPi extends Fixed1ArgFunction { + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + try { + ... + } +} +</source> + <p> + Now when the implementation is ready we need to register it with the formula evaluator. + This is the same no matter which kind of function we're creating. We simply add the + following line to the program that is using POI: + </p> +<source>WorkbookEvaluator.registerFunction("SQRTPI", SqrtPi); +</source> + <p> + Voila! The formula evaluator now recognizes <code>SQRTPI()</code>! + </p> + <section><title>Moving the function into the library</title> + <p> + If we choose instead to implement our function as part of the POI + library, the code is nearly identical. All POI functions + are part of one of two Java packages: <code>org.apache.poi.ss.formula.functions</code> + for BIFF8 Excel built-in functions, and <code>org.apache.poi.ss.formula.atp</code> + for Analysis ToolPak functions. The function still needs to implement the + appropriate base class, just as before. To implement our <code>SQRTPI()</code> + function in the POI library, we need to move the source code to + <code>poi/src/main/java/org/apache/poi/ss/formula/atp/SqrtPi.java</code> in + the POI source code, change the <code>package</code> statement, and add a + singleton instance: + </p> +<source>package org.apache.poi.ss.formula.atp; +... +public final class SqrtPi implements FreeRefFunction { + + public static final FreeRefFunction instance = new SqrtPi(); + + private SqrtPi() { + // Enforce singleton + } + ... +} +</source> + <p> + If our function had been one of the BIFF8 Excel built-ins, we would instead have moved + the source code to + <code>poi/src/main/java/org/apache/poi/ss/formula/functions/SqrtPi.java</code> in + the POI source code, and changed the <code>package</code> statement to: + </p> +<source>package org.apache.poi.ss.formula.functions; +</source> + <p> + POI library functions are registered differently from run-time-deployed functions. + Again, the techniques differ for the two types of library functions (remembering + that POI never releases the third type, UDFs). + For our Analysis ToolPak function, we have to update the list of functions in + <code>org.apache.poi.ss.formula.atp.AnalysisToolPak.createFunctionsMap()</code>: + </p> +<source>... +private Map<String, FreeRefFunction> createFunctionsMap() { + Map<String, FreeRefFunction> m = new HashMap<>(114); + ... + r(m, "SQRTPI", SqrtPi.instance); + ... +} +... +</source> + <p> + If our function had been one of the BIFF8 Excel built-ins, + the registration instead would require updating an entry in the formula-function table, + <code>poi/src/main/resources/org/apache/poi/ss/formula/function/functionMetadata.txt</code>: + </p> +<source>... +#Columns: (index, name, minParams, maxParams, returnClass, paramClasses, isVolatile, hasFootnote ) +... +359 SQRTPI 1 1 V V +... +</source> + <p> + and also updating the list of function implementation list in + <code>org.apache.poi.ss.formula.eval.FunctionEval.produceFunctions()</code>: + </p> +<source>... +private static Function[] produceFunctions() { + ... + retval[359] = new SqrtPi(); + ... +} +... +</source> + </section> + <section><title>Floating Point Arithmetic in Excel</title> + <p> + Excel uses the IEEE Standard for Double Precision Floating Point numbers + except two cases where it does not adhere to IEEE 754: + </p> + <ol> + <li>Positive and Negative Infinities: Infinities occur when you divide by 0. + Excel does not support infinities, rather, it gives a #DIV/0! error in these cases. + </li> + <li>Not-a-Number (NaN): NaN is used to represent invalid operations + (such as infinity/infinity, infinity-infinity, or the square root of -1). + NaNs allow a program to continue past an invalid operation. + Excel instead immediately generates an error such as #NUM! or #DIV/0!. + </li> + </ol> + <p> + Be aware of these two cases when saving results of your scientific calculations in Excel: + “where are my Infinities and NaNs? They are gone!” + </p> + </section> + <section><title>Testing Framework</title> + <p> + Automated testing of the implemented Function is easy. + The source code for this is in the file: <code>org.apache.poi.hssf.record.formula.GenericFormulaTestCase.java</code>. + This class has a reference to the test xls file (not <em>a</em> test xls, <em>the</em> test xls :) ) + which may need to be changed for your environment. Once you do that, in the test xls, + locate the entry for the function that you have implemented and enter different tests + in a cell in the FORMULA row. Then copy the "value of" the formula that you entered in the + cell just below it (this is easily done in excel as: + [copy the formula cell] > [go to cell below] > Edit > Paste Special > Values > "ok"). + You can enter multiple such formulas and paste their values in the cell below and the + test framework will automatically test if the formula evaluation matches the expected + value (Again, hard to put in words, so if you will, please take time to quickly look + at the code and the currently entered tests in the patch attachment "FormulaEvalTestData.xls" + file). + </p> + <note>This style of testing appears to have been abandoned. This section needs to be completely rewritten.</note> + </section> + </section> + <anchor id="appendixA"/> + <section> + <title>Appendix A — Functions supported by POI</title> + <p> + Functions supported by POI (as of v5.2.0 release) + </p> +<source>ABS +ACOS +ACOSH +ADDRESS +AND +AREAS +ASIN +ASINH +ATAN +ATAN2 +ATANH +AVEDEV +AVERAGE +AVERAGEIFS +BIN2DEC +CEILING +CHAR +CHOOSE +CLEAN +CODE +COLUMN +COLUMNS +COMBIN +COMPLEX +CONCAT +CONCATENATE +COS +COSH +COUNT +COUNTA +COUNTBLANK +COUNTIF +COUNTIFS +DATE +DATEVALUE +DAY +DAYS360 +DEC2BIN +DEC2HEX +DEGREES +DELTA +DEVSQ +DGET +DMAX +DMIN +DOLLAR +DSUM +EDATE +EOMONTH +ERROR.TYPE +EVEN +EXACT +EXP +FACT +FACTDOUBLE +FALSE +FIND +FIXED +FLOOR +FREQUENCY +FV +GEOMEAN +HEX2DEC +HLOOKUP +HOUR +HYPERLINK +IF +IFERROR +IFNA +IFS +IMAGINARY +IMREAL +INDEX +INDIRECT +INT +INTERCEPT +IPMT +IRR +ISBLANK +ISERR +ISERROR +ISEVEN +ISLOGICAL +ISNA +ISNONTEXT +ISNUMBER +ISODD +ISREF +ISTEXT +LARGE +LEFT +LEN +LN +LOG +LOG10 +LOOKUP +LOWER +MATCH +MAX +MAXA +MAXIFS +MDETERM +MEDIAN +MID +MIN +MINA +MINIFS +MINUTE +MINVERSE +MIRR +MMULT +MOD +MODE +MONTH +MROUND +NA +NETWORKDAYS +NOT +NOW +NPER +NPV +OCT2DEC +ODD +OFFSET +OR +PERCENTILE +PERCENTRANK +PERCENTRANK.EXC +PERCENTRANK.INC +PI +PMT +POISSON +POWER +PPMT +PRODUCT +PROPER +PV +QUOTIENT +RADIANS +RAND +RANDBETWEEN +RANK +RATE +REPLACE +REPT +RIGHT +ROMAN +ROUND +ROUNDDOWN +ROUNDUP +ROW +ROWS +SEARCH +SECOND +SIGN +SIN +SINGLE +SINH +SLOPE +SMALL +SQRT +STDEV +SUBSTITUTE +SUBTOTAL +SUM +SUMIF +SUMIFS +SUMPRODUCT +SUMSQ +SUMX2MY2 +SUMX2PY2 +SUMXMY2 +SWITCH +T +T.DIST +T.DIST.2T +T.DIST.RT +TAN +TANH +TDIST +TEXT +TEXTJOIN +TIME +TIMEVALUE +TODAY +TRANSPOSE +TREND +TRIM +TRUE +TRUNC +UPPER +VALUE +VAR +VARP +VLOOKUP +WEEKDAY +WEEKNUM +WORKDAY +XLOOKUP +XMATCH +YEAR +YEARFRAC</source> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/eval.xml b/src/documentation/content/xdocs/components/spreadsheet/eval.xml new file mode 100644 index 0000000000..aee0c38008 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/eval.xml @@ -0,0 +1,410 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Formula Evaluation</title> + </header> + <body> + <section><title>Introduction</title> + <p>The POI formula evaluation code enables you to calculate the result of + formulas in Excels sheets read-in, or created in POI. This document explains + how to use the API to evaluate your formulas. + </p> + </section> + + <anchor id="WhyEvaluate"/> + <section><title>Why do I need to evaluate formulas?</title> + <p>The Excel file format (both .xls and .xlsx) stores a "cached" result for + every formula along with the formula itself. This means that when the file + is opened, it can be quickly displayed, without needing to spend a long + time calculating all of the formula results. It also means that when reading + a file through Apache POI, the result is quickly available to you too! + </p> + <p>After making changes with Apache POI to either Formula Cells themselves, + or those that they depend on, you should normally perform a Formula + Evaluation to have these "cached" results updated. This is normally done + after all changes have been performed, but before you write the file out. + If you don't do this, there's a good chance that when you open the file in + Excel, until you go to the cell and hit enter or F9, you will either see + the old value or '#VALUE!' for the cell. (Sometimes Excel will notice + itself, and trigger a recalculation on load, but unless you know you are + using volatile functions it's generally best to trigger a <a href="#recalculation">Recalulation</a> + through POI) + </p> + </section> + + <anchor id="Status"/> + <section><title>Status</title> + <p>The code currently provides implementations for all the arithmatic operators. + It also provides implementations for approx. 140 built in + functions in Excel. The framework however makes it easy to add + implementation of new functions. See the <a href="eval-devguide.html"> Formula + evaluation development guide</a> and <a href="../../apidocs/dev/org/apache/poi/hssf/record/formula/functions/package-summary.html">javadocs</a> + for details. </p> + <p> Both HSSFWorkbook and XSSFWorkbook are supported, so you can + evaluate formulas on both .xls and .xlsx files.</p> + <p> User-defined functions are <a href="user-defined-functions.html">supported</a>, + but must be rewritten in Java and registered with the macro-enabled workbook in order to be evaluated. + </p> + </section> + <section><title>User API How-TO</title> + <p>The following code demonstrates how to use the FormulaEvaluator + in the context of other POI excel reading code. + </p> + <p>There are several ways in which you can use the FormulaEvalutator API.</p> + + <anchor id="Evaluate"/> + <section><title>Using FormulaEvaluator.<strong>evaluate</strong>(Cell cell)</title> + <p>This evaluates a given cell, and returns the new value, + without affecting the cell</p> + <source> +FileInputStream fis = new FileInputStream("c:/temp/test.xls"); +Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls") +Sheet sheet = wb.getSheetAt(0); +FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + +// suppose your formula is in B3 +CellReference cellReference = new CellReference("B3"); +Row row = sheet.getRow(cellReference.getRow()); +Cell cell = row.getCell(cellReference.getCol()); + +CellValue cellValue = evaluator.evaluate(cell); + +switch (cellValue.getCellType()) { + case Cell.CELL_TYPE_BOOLEAN: + System.out.println(cellValue.getBooleanValue()); + break; + case Cell.CELL_TYPE_NUMERIC: + System.out.println(cellValue.getNumberValue()); + break; + case Cell.CELL_TYPE_STRING: + System.out.println(cellValue.getStringValue()); + break; + case Cell.CELL_TYPE_BLANK: + break; + case Cell.CELL_TYPE_ERROR: + break; + + // CELL_TYPE_FORMULA will never happen + case Cell.CELL_TYPE_FORMULA: + break; +} + </source> + <p>Thus using the retrieved value (of type + FormulaEvaluator.CellValue - a nested class) returned + by FormulaEvaluator is similar to using a Cell object + containing the value of the formula evaluation. CellValue is + a simple value object and does not maintain reference + to the original cell. + </p> + </section> + + <anchor id="EvaluateFormulaCell"/> + <section><title>Using FormulaEvaluator.<strong>evaluateFormulaCell</strong>(Cell cell)</title> + <p><strong>evaluateFormulaCell</strong>(Cell cell) + will check to see if the supplied cell is a formula cell. + If it isn't, then no changes will be made to it. If it is, + then the formula is evaluated. The value for the formula + is saved alongside it, to be displayed in excel. The + formula remains in the cell, just with a new value</p> + <p>The return of the function is the type of the + formula result, such as Cell.CELL_TYPE_BOOLEAN</p> + <source> +FileInputStream fis = new FileInputStream("/somepath/test.xls"); +Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") +Sheet sheet = wb.getSheetAt(0); +FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + +// suppose your formula is in B3 +CellReference cellReference = new CellReference("B3"); +Row row = sheet.getRow(cellReference.getRow()); +Cell cell = row.getCell(cellReference.getCol()); + +if (cell!=null) { + switch (evaluator.evaluateFormulaCell(cell)) { + case Cell.CELL_TYPE_BOOLEAN: + System.out.println(cell.getBooleanCellValue()); + break; + case Cell.CELL_TYPE_NUMERIC: + System.out.println(cell.getNumericCellValue()); + break; + case Cell.CELL_TYPE_STRING: + System.out.println(cell.getStringCellValue()); + break; + case Cell.CELL_TYPE_BLANK: + break; + case Cell.CELL_TYPE_ERROR: + System.out.println(cell.getErrorCellValue()); + break; + + // CELL_TYPE_FORMULA will never occur + case Cell.CELL_TYPE_FORMULA: + break; + } +} + </source> + </section> + + <anchor id="EvaluateInCell"/> + <section><title>Using FormulaEvaluator.<strong>evaluateInCell</strong>(Cell cell)</title> + <p><strong>evaluateInCell</strong>(Cell cell) will check to + see if the supplied cell is a formula cell. If it isn't, + then no changes will be made to it. If it is, then the + formula is evaluated, and the new value saved into the cell, + in place of the old formula.</p> + <source> +FileInputStream fis = new FileInputStream("/somepath/test.xls"); +Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") +Sheet sheet = wb.getSheetAt(0); +FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + +// suppose your formula is in B3 +CellReference cellReference = new CellReference("B3"); +Row row = sheet.getRow(cellReference.getRow()); +Cell cell = row.getCell(cellReference.getCol()); + +if (cell!=null) { + switch (evaluator.<strong>evaluateInCell</strong>(cell).getCellType()) { + case Cell.CELL_TYPE_BOOLEAN: + System.out.println(cell.getBooleanCellValue()); + break; + case Cell.CELL_TYPE_NUMERIC: + System.out.println(cell.getNumericCellValue()); + break; + case Cell.CELL_TYPE_STRING: + System.out.println(cell.getStringCellValue()); + break; + case Cell.CELL_TYPE_BLANK: + break; + case Cell.CELL_TYPE_ERROR: + System.out.println(cell.getErrorCellValue()); + break; + + // CELL_TYPE_FORMULA will never occur + case Cell.CELL_TYPE_FORMULA: + break; + } +} + + </source> + </section> + + <anchor id="EvaluateAll"/> + <section><title>Re-calculating all formulas in a Workbook</title> + <source> +FileInputStream fis = new FileInputStream("/somepath/test.xls"); +Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") +FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); +for (Sheet sheet : wb) { + for (Row r : sheet) { + for (Cell c : r) { + if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { + evaluator.evaluateFormulaCell(c); + } + } + } +} + </source> + + <p>Alternately, if you know which of HSSF or XSSF you're working + with, then you can call the static + <strong>evaluateAllFormulaCells</strong> method on the appropriate + HSSFFormulaEvaluator or XSSFFormulaEvaluator class.</p> + </section> + </section> + + <anchor id="recalculation"/> + <section><title>Recalculation of Formulas</title> + <p> + In certain cases you may want to force Excel to re-calculate formulas when the workbook is opened. + Consider the following example: + </p> + <p> + Open Excel and create a new workbook. On the first sheet set A1=1, B1=1, C1=A1+B1. + Excel automatically calculates formulas and the value in C1 is 2. So far so good. + </p> + <p> + Now modify the workbook with POI: + </p> + <source> + Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); + + Sheet sh = wb.getSheetAt(0); + sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 + + FileOutputStream out = new FileOutputStream("workbook2.xls"); + wb.write(out); + out.close(); + </source> + <p> + Now open workbook2.xls in Excel and the value in C1 is still 2 while you expected 3. Wrong? No! + The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them. + It is not an issue when you are creating new workbooks from scratch, but important to remember when you are modifing + existing workbooks with formulas. This can be done in two ways: + </p> + <p> + 1. Re-evaluate formulas with POI's FormulaEvaluator: + </p> + <source> + Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); + + Sheet sh = wb.getSheetAt(0); + sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 + + wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); + </source> + <p> + 2. Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened: + </p> + <source> + Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); + + Sheet sh = wb.getSheetAt(0); + sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 + + wb.setForceFormulaRecalculation(true); + </source> + </section> + + <anchor id="external"/> + <section><title>External (Cross-Workbook) references</title> + <p>It is possible for a formula in an Excel spreadsheet to + refer to a Named Range or Cell in a different workbook. + These cross-workbook references are normally called <em>External + References</em>. These are formulas which look something like:</p> + <source> + =SUM([Finances.xlsx]Numbers!D10:D25) + =SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25) + =SUM([Finances.xlsx]Range20) + </source> + <p>If you don't have access to these other workbooks, then you + should call + <a href="../../apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</a> + to tell the Formula Evaluator to skip evaluating any external + references it can't look up.</p> + <p>In order for POI to be able to evaluate external references, it + needs access to the workbooks in question. As these don't necessarily + have the same names on your system as in the workbook, you need to + give POI a map of external references to open workbooks, through + the + <a href="../../apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks(java.util.Map)">setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)</a> + method. You should normally do something like:</p> + <source> +// Create a FormulaEvaluator to use +FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); + +// Track the workbook references +Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>(); +// Add this workbook +workbooks.put("report.xlsx", mainWorkbookEvaluator); +// Add two others +workbooks.put("input.xls", WorkbookFactory.create("C:\\temp\\input22.xls").getCreationHelper().createFormulaEvaluator()); +workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator()); + +// Attach them +mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks); + +// Evaluate +mainWorkbookEvaluator.evaluateAll(); + </source> + </section> + + <anchor id="Performance"/> + <section><title>Performance Notes</title> + <ul> + <li>Generally you should have to create only one FormulaEvaluator + instance per Workbook. The FormulaEvaluator will cache + evaluations of dependent cells, so if you have multiple + formulas all depending on a cell then subsequent evaluations + will be faster. + </li> + <li>You should normally perform all of your updates to cells, + before triggering the evaluation, rather than doing one + cell at a time. By waiting until all the updates/sets are + performed, you'll be able to take best advantage of the caching + for complex formulas. + </li> + <li>If you do end up making changes to cells part way through + evaluation, you should call <em>notifySetFormula</em> or + <em>notifyUpdateCell</em> to trigger suitable cache clearance. + Alternately, you could instantiate a new FormulaEvaluator, + which will start with empty caches. + </li> + <li>Also note that FormulaEvaluator maintains a reference to + the sheet and workbook, so ensure that the evaluator instance + is available for garbage collection when you are done with it + (in other words don't maintain long lived reference to + FormulaEvaluator if you don't really need to - unless + all references to the sheet and workbook are removed, these + don't get garbage collected and continue to occupy potentially + large amounts of memory). + </li> + <li>CellValue instances however do not maintain reference to the + Cell or the sheet or workbook, so these can be long-lived + objects without any adverse effect on performance. + </li> + </ul> + </section> + <section><title>Formula Evaluation Debugging</title> + <p>POI is not perfect and you may stumble across formula evaluation problems (Java exceptions + or just different results) in your special use case. To support an easy detailed analysis, a special + logging of the full evaluation is provided.</p> + <p>POI 5.1.0 and above uses <a href="https://logging.apache.org/log4j/2.x/">Log4J 2.x</a> as a logging framework. Try to set up a logging + configuration that lets you see the info and other log messages.</p> + <p>Example use:</p> + <source> + // open your file + Workbook wb = new HSSFWorkbook(new FileInputStream("foobar.xls")); + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + + // get your cell + Cell cell = wb.getSheet(0).getRow(0).getCell(0); // just a dummy example + + // perform debug output for the next evaluate-call only + evaluator.setDebugEvaluationOutputForNextEval(true); + evaluator.evaluateFormulaCell(cell); + evaluator.evaluateFormulaCell(cell); // no logging performed for this next evaluate-call + </source> + <p>The special Logger called "POI.FormulaEval" is used (useful if you use the CommonsLogger and a detailed logging configuration). + The used log levels are WARN and INFO (for detailed parameter info and results) - the level are so high to allow this + special logging without being disturbed by the bunch of DEBUG log entries from other classes.</p> + </section> + + <anchor id="sxssf"/> + <section><title>Formula Evaluation and SXSSF</title> + <p>For versions before 3.13 final, no formula evaluation is possible with + SXSSF.</p> + <p>If you are using POI 3.13 final or newer, formula evaluation is possible with SXSSF, + but with some caveats.</p> + <p>The biggest restriction is that, since evaluating a cell needs that cell in memory + and any others it depends on, only pure-function formulas and formulas referencing + nearby cells can be evaluated with SXSSF. If a formula references a cell that hasn't + yet been written, or one which has already been flushed to disk, then it won't be + possible to evaluate it.</p> + <p>Because of this, a call to <em>wb.getCreationHelper().createFormulaEvaluator().evaluateAll();</em> + will very rarely work on SXSSF, as it's very rare that all the cells wil be available + and in memory at any time! Instead, it is suggested to evaluate formula cells just + after writing them, or shortly after when cells they depend on are added. Just make + sure that all cells needing or needed for evaluation are inside the window.</p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/examples.xml b/src/documentation/content/xdocs/components/spreadsheet/examples.xml new file mode 100644 index 0000000000..87feff0b59 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/examples.xml @@ -0,0 +1,274 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>HSSF and XSSF Examples</title> + <authors> + <person id="YK" name="Yegor Kozlov" email="user@poi.apache.org"/> + </authors> + </header> + <body> + <section><title>HSSF and XSSF common examples</title> + <p>Apache POI comes with a number of examples that demonstrate how you + can use the POI API to create documents from "real life". + The examples below based on common XSSF-HSSF interfaces so that you + can generate either *.xls or *.xlsx output just by setting a + command-line argument: + </p> + <source> + BusinessPlan -xls + or + BusinessPlan -xlsx + </source> + <p>All sample source is available in <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/">SVN</a></p> + <p>In addition, there are a handful of + <a href="#hssf-only">HSSF only</a> and + <a href="#xssf-only">XSSF only</a> examples as well. + </p> + + <section><title>Available Examples</title> + <p> + The following examples are available: + </p> + <ul> + <li><a href="#ss-common">Common HSSF and XSSF</a><ul> + <li><a href="#business-plan">Business Plan</a></li> + <li><a href="#calendar">Calendar</a></li> + <li><a href="#loan-calculator">Loan Calculator</a></li> + <li><a href="#timesheet">Timesheet</a></li> + <li><a href="#conditional-formats">Conditional Formats</a></li> + <li><a href="#common-formulas">Formula Examples</a></li> + <li><a href="#add-dimensioned-image">Add Dimensioned Image</a></li> + <li><a href="#aligned-cells">Aligned Cells</a></li> + <li><a href="#cell-style-details">Cell Style Details</a></li> + <li><a href="#linked-dropdown">Linked Dropdown Lists</a></li> + <li><a href="#performance-test">Common SS Performance Test</a></li> + <li><a href="#to-html">To HTML</a></li> + <li><a href="#to-csv">To CSV</a></li> + </ul></li> + <li><a href="#hssf-only">HSSF-Only</a></li> + <li><a href="#xssf-only">XSSF-Only</a></li> + </ul> + </section> + + <anchor id="ss-common" /> + <anchor id="business-plan" /> + <section><title>Business Plan</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/BusinessPlan.java">BusinessPlan</a> + application creates a sample business plan with three phases, weekly iterations and time highlighting. Demonstrates advanced cell formatting + (number and date formats, alignments, fills, borders) and various settings for organizing data in a sheet (freezed panes, grouped rows). + </p> + <figure src="images/businessplan.jpg" alt="business plan demo"/> + </section> + + <anchor id="calendar" /> + <section><title>Calendar</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/CalendarDemo.java">Calendar</a> + demo creates a multi sheet calendar. Each month is on a separate sheet. + </p> + <figure src="images/calendar.jpg" alt="calendar demo"/> + </section> + + <anchor id="loan-calculator" /> + <section><title>Loan Calculator</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/LoanCalculator.java">LoanCalculator</a> + demo creates a simple loan calculator. Demonstrates advance usage of cell formulas and named ranges. + </p> + <figure src="images/loancalc.jpg" alt="loan calculator demo"/> + </section> + + <anchor id="timesheet" /> + <section><title>Timesheet</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/TimesheetDemo.java">Timesheet</a> + demo creates a weekly timesheet with automatic calculation of total hours. Demonstrates advance usage of cell formulas. + </p> + <figure src="images/timesheet.jpg" alt="timesheet demo"/> + </section> + + <anchor id="conditional-formats" /> + <section><title>Conditional Formats</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/ConditionalFormats.java">ConditionalFormats</a> + demo is a collection of short examples showing what you can do with Excel conditional formatting in POI: + </p> + <ul> + <li>Highlight cells based on their values</li> + <li>Highlight a range of cells based on a formula</li> + <li>Hide errors</li> + <li>Hide the duplicate values</li> + <li>Highlight duplicate entries in a column</li> + <li>Highlight items that are in a list on the worksheet</li> + <li>Highlight payments that are due in the next thirty days</li> + <li>Shade alternating rows on the worksheet</li> + <li>Shade bands of rows on the worksheet</li> + </ul> + </section> + + <anchor id="common-formulas" /> + <section><title>Formula Examples</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula/CalculateMortgage.java">CalculateMortgage</a> + example demonstrates a simple user-defined function to calculate + principal and interest.</p> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula/CheckFunctionsSupported.java">CheckFunctionsSupported</a> + example shows how to test what functions and formulas aren't + supported from a given file.</p> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula/SettingExternalFunction.java">SettingExternalFunction</a> + example demonstrates how to use externally provided (third-party) + formula add-ins.</p> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula/UserDefinedFunctionExample.java">UserDefinedFunctionExample</a> + example demonstrates how to invoke a User Defined Function for a + given Workbook instance using POI's UDFFinder implementation.</p> + </section> + + <anchor id="add-dimensioned-image" /> + <section><title>Add Dimensioned Image</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/AddDimensionedImage.java">AddDimensionedImage</a> + example demonstrates how to add an image to a worksheet and set that + images size to a specific number of millimetres irrespective of the + width of the columns or height of the rows.</p> + </section> + + <anchor id="aligned-cells" /> + <section><title>Aligned Cells</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/AligningCells.java">AligningCells</a> + example demonstrates how various alignment options work.</p> + </section> + + <anchor id="cell-style-details" /> + <section><title>Cell Style Details</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/CellStyleDetails.java">CellStyleDetails</a> + example demonstrates how to read excel styles for cells.</p> + </section> + + <anchor id="linked-dropdown" /> + <section><title>Linked Dropdown Lists</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/LinkedDropDownLists.java">LinkedDropDownLists</a> + example demonstrates one technique that may be used to create linked + or dependent drop down lists.</p> + </section> + + <anchor id="performance-test" /> + <section><title>Common SS Performance Test</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/SSPerformanceTest.java">SSPerformanceTest</a> + example provides a way to create simple example files of varying + sizes, and to calculate how long they take. Useful for benchmarking + your system, and to also test if slow performance is due to Apache + POI itself or to your own code.</p> + </section> + + <anchor id="to-html" /> + <section><title>ToHtml</title> + <p> The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/html/ToHtml.java">ToHtml</a> + example shows how to display a spreadsheet in HTML using the classes for spreadsheet display. + </p> + </section> + + <anchor id="to-csv" /> + <section><title>ToCSV</title> + <p>The <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/ToCSV.java">ToCSV</a> + example demonstrates <em>one</em> way to convert an Excel spreadsheet into a CSV file. + </p> + </section> + </section> + + <anchor id="hssf-only" /> + <section><title>HSSF-only Examples</title> + <p>All the HSSF-only examples can be found in + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/">SVN</a></p> + <ul> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/CellComments.java">CellComments</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/HyperlinkFormula.java">HyperlinkFormula</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/EventExample.java">EventExample</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/OfficeDrawingWithGraphics.java">OfficeDrawingWithGraphics</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/CreateDateCells.java">CreateDateCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/NewWorkbook.java">NewWorkbook</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/EmeddedObjects.java">EmeddedObjects</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/Hyperlinks.java">Hyperlinks</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/OfficeDrawing.java">OfficeDrawing</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/HSSFReadWrite.java">HSSFReadWrite</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/NewSheet.java">NewSheet</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/SplitAndFreezePanes.java">SplitAndFreezePanes</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/InCellLists.java">InCellLists</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/RepeatingRowsAndColumns.java">RepeatingRowsAndColumns</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/MergedCells.java">MergedCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/CellTypes.java">CellTypes</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/ZoomSheet.java">ZoomSheet</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/ReadWriteWorkbook.java">ReadWriteWorkbook</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/CreateCells.java">CreateCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/Alignment.java">Alignment</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/FrillsAndFills.java">FrillsAndFills</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/AddDimensionedImage.java">AddDimensionedImage</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/Borders.java">Borders</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/NewLinesInCells.java">NewLinesInCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/WorkingWithFonts.java">WorkingWithFonts</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/BigExample.java">BigExample</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/Outlines.java">Outlines</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java">XLS2CSVmra</a></li> + </ul> + </section> + + <anchor id="xssf-only" /> + <section><title>XSSF-only Examples</title> + <p>All the XSSF-only examples can be found in + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/">SVN</a></p> + <ul> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CellComments.java">CellComments</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/HeadersAndFooters.java">HeadersAndFooters</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CreateUserDefinedDataFormats.java">CreateUserDefinedDataFormats</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CreatePivotTable.java">CreatePivotTable</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CreatePivotTable2.java">CreatePivotTable2</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/FillsAndColors.java">FillsAndColors</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkingWithBorders.java">WorkingWithBorders</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/BigGridDemo.java">BigGridDemo</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CreateTable.java">CreateTable</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CalendarDemo.java">CalendarDemo</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/AligningCells.java">AligningCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/SplitAndFreezePanes.java">SplitAndFreezePanes</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkingWithPageSetup.java">WorkingWithPageSetup</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkingWithPictures.java">WorkingWithPictures</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/MergingCells.java">MergingCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CustomXMLMapping.java">CustomXMLMapping</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/SelectedSheet.java">SelectedSheet</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/EmbeddedObjects.java">EmbeddedObjects</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkbookProperties.java">WorkbookProperties</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/NewLinesInCells.java">NewLinesInCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/Outlining.java">Outlining</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/CreateCell.java">CreateCell</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/IterateCells.java">IterateCells</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/BarChart.java">BarChart</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/BarAndLineChart.java">BarAndLineChart</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/LineChart.java">LineChart</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/ScatterChart.java">ScatterChart</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkingWithFonts.java">WorkingWithFonts</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/HyperlinkExample.java">HyperlinkExample</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/ShiftRows.java">ShiftRows</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/WorkingWithRichText.java">WorkingWithRichText</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/FitSheetToOnePage.java">FitSheetToOnePage</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/streaming/HybridStreaming.java">HybridStreaming</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/streaming/Outlining.java">Outlining (SXSSF output)</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/streaming/DeferredGeneration.java">DeferredGeneration (SXSSF output)</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/streaming/SavePasswordProtectedXlsx.java">SavePasswordProtectedXlsx (SXSSF output)</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java">XLSX2CSV (streaming read)</a></li> + <li><a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/FromHowTo.java">FromHowTo (streaming read)</a></li> + </ul> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/excelant.xml b/src/documentation/content/xdocs/components/spreadsheet/excelant.xml new file mode 100644 index 0000000000..01e03c3213 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/excelant.xml @@ -0,0 +1,317 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>ExcelAnt - Ant Tasks for Validating Excel Spreadsheets</title> + <authors> + <person email="jon@loquatic.com" name="Jon Svede" id="JDS"/> + <person email="brian.bush@nrel.gov" name="Brian Bush" id="BWB"/> + </authors> + </header> + <body> + <section><title>ExcelAnt - Ant Tasks for Validating Excel Spreadsheets</title> + + <section><title>Introduction</title> + <p>ExcelAnt is a set of Ant tasks that make it possible to verify or test + a workbook without having to write Java code. Of course, the tasks themselves + are written in Java, but to use this framework you only need to know a little + bit about Ant.</p> + <p>This document covers the basic usage and set up of ExcelAnt.</p> + <p>This document will assume basic familiarity with Ant and Ant build files.</p> + </section> + <section><title>Setup</title> + <p>To start with ExcelAnt, you'll need to have the POI 3.8 or higher jar files. If you test only .xls +workbooks then you need to have the following jars in your path:</p> + <ul> + <li>poi-excelant-$version-YYYYDDMM.jar</li> + <li>poi-$version-YYYYDDMM.jar</li> + <li>poi-ooxml-$version-YYYYDDMM.jar</li> + </ul> + <p> If you evaluate .xlsx workbooks then you need to add these: </p> + <ul> + <li>poi-ooxml-lite-$version-YYYYDDMM.jar</li> + <li>xmlbeans.jar</li> + </ul> + <p>For example, if you have these jars in a lib/ dir in your project, your build.xml + might look like this:</p> +<source><![CDATA[ +<property name="lib.dir" value="lib" /> + +<path id="excelant.path"> + <pathelement location="${lib.dir}/poi-excelant-3.8-beta1-20101230.jar" /> + <pathelement location="${lib.dir}/poi-3.8-beta1-20101230.jar" /> + <pathelement location="${lib.dir}/poi-ooxml-3.8-beta1-20101230.jar" /> +</path> +]]></source> + <p>Next, you'll need to define the Ant tasks. There are several ways to use ExcelAnt:</p> + +<ul><li>The traditional way:</li></ul> +<source><![CDATA[ + <typedef resource="org/apache/poi/ss/excelant/antlib.xml" classpathref="excelant.path" /> +]]></source> +<p> + Where excelant.path refers to the classpath with POI jars. + Using this approach the provided extensions will live in the default namespace. Note that the default task/typenames (evaluate, test) may be too generic and should either be explicitly overridden or used with a namespace. +</p> +<ul><li>Similar, but assigning a namespace URI:</li></ul> +<source><![CDATA[ +<project name="excelant-demo" xmlns:poi="antlib:org.apache.poi.ss.excelant"> + + <typedef resource="org/apache/poi/ss/excelant/antlib.xml" + classpathref="excelant.classpath" + uri="antlib:org.apache.poi.ss.excelant"/> + + <target name="test-nofile"> + <poi:excelant> + + </poi:excelant> + </target> +</project> +]]></source> + </section> + + <section><title>A Simple Example</title> + <p>The simplest example of using Excel is the ability to validate that POI is giving you back + the value you expect it to. Does this mean that POI is inaccurate? Hardly. There are cases + where POI is unable to evaluate cells for a variety of reasons. If you need to write code + to integrate a worksheet into an app, you may want to know that it's going to work before + you actually try to write that code. ExcelAnt helps with that.</p> + + <p>Consider the <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/excelant/simple-mortgage-calculation.xls">mortgage-calculation.xls</a> + file found in the Examples (link broken / file is missing). This sheet is shown below:</p> + + <figure src="images/simple-xls-with-function.jpg" alt="mortgage calculation spreadsheet"/> + + <p>This sheet calculates the principal and interest payment for a mortgage based + on the amount of the loan, term and rate. To write a simple ExcelAnt test you + need to tell ExcelAnt about the file like this:</p> +<source><![CDATA[ +<property name="xls.file" value="" /> + +<target name="simpleTest"> + <excelant fileName="${xls.file}"> + <test name="checkValue" showFailureDetail="true"> + <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="790.7936" precision="1.0e-4" /> + </test> + </excelant> +</target> +]]></source> + + + <p>This code sets up ExcelAnt to access the file defined in the ant property + xls.file. Then it creates a 'test' named 'checkValue'. Finally it tries + to evaluate the B4 on the sheet named 'MortgageCalculator'. There are some assumptions + here that are worth explaining. For starters, ExcelAnt is focused on the testing + numerically oriented sheets. The <evaluate> task is actually evaluating the + cell as a formula using a FormulaEvaluator instance from POI. Therefore it will fail + if you point it to a cell that doesn't contain a formula or a test a plain old number.</p> + + <p>Having said all that, here is what the output looks like:</p> + +<source><![CDATA[ +simpleTest: + [excelant] ExcelAnt version 0.4.0 Copyright 2011 + [excelant] Using input file: resources/excelant.xls + [excelant] 1/1 tests passed. +BUILD SUCCESSFUL +Total time: 391 milliseconds +]]></source> + + </section> + + <section><title>Setting Values into a Cell</title> + <p>So now we know that at a minimum POI can use our sheet to calculate the existing value. + This is an important point: in many cases sheets have dependencies, i.e., cells they reference. + As is often the case, these cells may have dependencies, which may have dependencies, etc. + The point is that sometimes a dependent cell may get adjusted by a macro or a function + and it may be that POI doesn't have the capabilities to do the same thing. This test + verifies that we can rely on POI to retrieve the default value, based on the stored values + of the sheet. Now we want to know if we can manipulate those dependencies and verify + the output.</p> + + <p>To verify that we can manipulate cell values, we need a way in ExcelAnt to set a value. + This is provided by the following task types:</p> + <ul> + <li>setDouble() - sets the specified cell as a double.</li> + <li>setFormula() - sets the specified cell as a formula.</li> + <li>setString() = sets the specified cell as a String.</li> + </ul> + + <p>For the purposes of this example we'll use the <setDouble> task. Let's + start with a $240,000, 30 year loan at 11% (let's pretend it's like 1984). Here + is how we will set that up:</p> + +<source><![CDATA[ + <setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="2285.576149" precision="1.0e-4" /> +]]></source> + + <p>Don't forget that we're verifying the behavior so you need to put all this + into the sheet. That is how I got the result of $2,285 and change. So save your + changes and run it; you should get the following: </p> + +<source><![CDATA[ +Buildfile: C:\opt\eclipse\workspaces\excelant\excelant.examples\build.xml +simpleTest: + [excelant] ExcelAnt version 0.4.0 Copyright 2011 + [excelant] Using input file: resources/excelant.xls + [excelant] 1/1 tests passed. +BUILD SUCCESSFUL +Total time: 406 milliseconds +]]></source> + +</section> + + <section><title>Getting More Details</title> + + <p>This is great, it's working! However, suppose you want to see a little more detail. The + ExcelAnt tasks leverage the Ant logging so you can add the -verbose and -debug flags to + the Ant command line to get more detail. Try adding -verbose. Here is what + you should see:</p> + +<source><![CDATA[ +simpleTest: + [excelant] ExcelAnt version 0.4.0 Copyright 2011 + [excelant] Using input file: resources/excelant.xls + [evaluate] test precision = 1.0E-4 global precision = 0.0 + [evaluate] Using evaluate precision of 1.0E-4 + [excelant] 1/1 tests passed. +BUILD SUCCESSFUL +Total time: 406 milliseconds +]]></source> + + + <p>We see a little more detail. Notice that we see that there is a setting for global precision. + Up until now we've been setting the precision on each evaluate that we call. This + is obviously useful but it gets cumbersome. It would be better if there were a way + that we could specify a global precision - and there is. There is a <precision> + tag that you can specify as a child of the <excelant> tag. Let's go back to + our original task we set up earlier and modify it:</p> + +<source><![CDATA[ +<property name="xls.file" value="" /> + +<target name="simpleTest"> + <excelant fileName="${xls.file}"> + <precision value="1.0e-3"/> + <test name="checkValue" showFailureDetail="true"> + <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="790.7936" /> + </test> + </excelant> +</target> +]]></source> + + <p>In this example we have set the global precision to 1.0e-3. This means that + in the absence of something more stringent, all tests in the task will use + the global precision. We can still override this by specifying the + precision attribute of all of our <evaluate> task. Let's first run + this task with the global precision and the -verbose flag:</p> + +<source><![CDATA[ +simpleTest: +[excelant] ExcelAnt version 0.4.0 Copyright 2011 +[excelant] Using input file: resources/excelant.xls +[excelant] setting precision for the test checkValue + [test] setting globalPrecision to 0.0010 in the evaluator +[evaluate] test precision = 0.0 global precision = 0.0010 +[evaluate] Using global precision of 0.0010 +[excelant] 1/1 tests passed. +]]></source> + + + <p>As the output clearly shows, the test itself has no precision but there is + the global precision. Additionally, it tells us we're going to use that + more stringent global value. Now suppose that for this test we want + to use a more stringent precision, say 1.0e-4. We can do that by adding + the precision attribute back to the <evaluate> task:</p> + +<source><![CDATA[ +<excelant fileName="${xls.file}"> + <precision value="1.0e-3"/> + <test name="checkValue" showFailureDetail="true"> + <setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="2285.576149" precision="1.0e-4" /> + </test> +</excelant> +]]></source> + + + <p>Now when you re-run this test with the verbose flag you will see that + your test ran and passed with the higher precision:</p> +<source><![CDATA[ +simpleTest: + [excelant] ExcelAnt version 0.4.0 Copyright 2011 + [excelant] Using input file: resources/excelant.xls + [excelant] setting precision for the test checkValue + [test] setting globalPrecision to 0.0010 in the evaluator + [evaluate] test precision = 1.0E-4 global precision = 0.0010 + [evaluate] Using evaluate precision of 1.0E-4 over the global precision of 0.0010 + [excelant] 1/1 tests passed. +BUILD SUCCESSFUL +Total time: 390 milliseconds +]]></source> + </section> + + <section><title>Leveraging User Defined Functions</title> + <p>POI has an excellent feature (besides ExcelAnt) called <a href="user-defined-functions.html">User Defined Functions</a>, + that allows you to write Java code that will be used in place of custom VB + code or macros is a spreadsheet. If you have read the documentation and written + your own FreeRefFunction implmentations, ExcelAnt can make use of this code. + For each <excelant> task you define you can nest a <udf> tag + which allows you to specify the function alias and the class name.</p> + + <p>Consider the previous example of the mortgage calculator. What if, instead + of being a formula in a cell, it was a function defined in a VB macro? As luck + would have it, we already have an example of this in the examples from the + User Defined Functions example, so let's use that. In the example spreadsheet + there is a tab for MortgageCalculatorFunction, which will use. If you look in + cell B4, you see that rather than a messy cell based formula, there is only the function + call. Let's not get bogged down in the function/Java implementation, as these + are covered in the User Defined Function documentation. Let's just add + a new target and test to our existing build file:</p> +<source><![CDATA[ + <target name="functionTest"> + <excelant fileName="${xls.file}"> + <udf functionAlias="calculatePayment" class="org.apache.poi.ss.examples.formula.CalculateMortgage"/> + <precision value="1.0e-3"/> + <test name="checkValue" showFailureDetail="true"> + <setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <evaluate showDelta="true" cell="'MortgageCalculatorFunction'!$B$4" expectedValue="2285.576149" precision="1.0e-4" /> + </test> + </excelant> + </target> +]]></source> + + <p>So if you look at this carefully it looks the same as the previous examples. We + still use the global precision, we're still setting values, and we still want + to evaluate a cell. The only real differences are the sheet name and the + addition of the function.</p> + </section> + </section> +</body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/formula.xml b/src/documentation/content/xdocs/components/spreadsheet/formula.xml new file mode 100644 index 0000000000..3e2ed30647 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/formula.xml @@ -0,0 +1,120 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Formula Support</title> + <authors> + <person email="avik@apache.org" name="Avik Sengupta" id="AS"/> + </authors> + </header> + <body> + <section><title>Introduction</title> + <p> + This document describes the current state of formula support in POI. + The information in this document currently applies to the 3.13 version of POI. + Since this area is a work in progress, this document will be updated with new + features as and when they are added. + </p> + + </section> + <section><title>The basics</title> + <p> + In org.apache.poi.ss.usermodel.Cell + <strong> setCellFormula("formulaString") </strong> is used to add a + formula to a sheet, and <strong> getCellFormula() </strong> is used to retrieve + the string representation of a formula. + </p> + <p> + We aim to support the complete excel grammar for formulas. Thus, the string that + you pass in to the <em> setCellFormula </em> call should be what you expect to + type into excel. Also, note that you should NOT add a "=" to the front of the string. + </p> + <p> + Please note that localized versions of Excel allow to enter localized + function-names. However internally Excel stores the English names and thus POI + only supports these and not the localized ones. Also note that only commas may be + used to separate arguments, as per the Excel English style, alternate delimeters + used in other localizations are not supported. + </p> + </section> + <section><title>Supported Features</title> + <ul> + <li>References: single cell & area, 2D & 3D, relative & absolute</li> + <li>Literals: number, text, boolean, error and array</li> + <li>Operators: arithmetic and logical, some region operators</li> + <li>Built-in functions: over 350 recognised, 280 evaluatable</li> + <li>Add-in functions: 24 from Analysis Toolpack</li> + <li>Array Formulas: via Sheet.setArrayFormula() and Sheet.removeArrayFormula()</li> + <li>Region operators: union, intersection</li> + </ul> + </section> + <section><title>Not yet supported</title> + <ul> + <li>Manipulating table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")</li> + <li>Parsing of previously uncalled add-in functions</li> + <li>Preservation of whitespace in formulas (when POI manipulates them)</li> + </ul> + </section> + + <section><title>Supported Functions</title> + <p>To get the list of formula functions that POI supports, you need to + call some code!</p> + <p>The methods you need are available on + <a href="../../apidocs/dev/org/apache/poi/ss/formula/eval/FunctionEval.html">org.apache.poi.ss.formula.eval.FunctionEval</a>. + To find which functions your copy of Apache POI supports, use + <a href="../../apidocs/dev/org/apache/poi/ss/formula/eval/FunctionEval.html#getSupportedFunctionNames()">getSupportedFunctionNames()</a> + to get a list of the implemented function names. For the list of functions that + POI knows the name of, but doesn't currently implement, use + <a href="../../apidocs/dev/org/apache/poi/ss/formula/eval/FunctionEval.html#getNotSupportedFunctionNames()">getNotSupportedFunctionNames()</a> + </p> + </section> + + <section><title>Internals</title> + <p> + Formulas in Excel are stored as sequences of tokens in Reverse Polish Notation order. The + <a href="https://sc.openoffice.org/excelfileformat.pdf">open office XLS spec</a> is the best + documentation you will find for the format. + </p> + + <p> + The tokens used by excel are modeled as individual *Ptg classes in the <strong> + org.apache.poi.hssf.record.formula</strong> package. + </p> + <p> + The task of parsing a formula string into an array of RPN ordered tokens is done by the <strong> + org.apache.poi.ss.formula.FormulaParser</strong> class. This class implements a hand + written recursive descent parser. + </p> + <p> + Formula tokens in Excel are stored in one of three possible <em> operand classes </em>: + Reference, Value and Array. Based on the location of a token, its class can change + in complicated and undocumented ways. While we have support for most cases, we + are not sure if we have covered all bases (since there is no documentation for this area.) + We would therefore like you to report any + occurrence of #VALUE! in a cell upon opening a POI generated workbook in excel. (Check that + typing the formula into Excel directly gives a valid result.) + </p> + <p>Check out the <a href="site:javadocs">javadocs </a> for details. + </p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/hacking-hssf.xml b/src/documentation/content/xdocs/components/spreadsheet/hacking-hssf.xml new file mode 100644 index 0000000000..784aafbf22 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/hacking-hssf.xml @@ -0,0 +1,89 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Hacking HSSF</title> + <authors> + <person email="user@poi.apache.org" name="Glen Stampoultzis" id="GJS"/> + <person email="acoliver@apache.org" name="Andrew Oliver" id="AO"/> + </authors> + </header> + <body> + <section><title>Where Can I Find Documentation on Feature X</title> + <p> + You might find the + 'Excel 97 Developer's Kit' (out of print, Microsoft Press, no + restrictive covenants, available on Amazon.com) helpful for + understanding the file format. + </p> + <p> + Also useful is the <a href="https://sc.openoffice.org/excelfileformat.pdf">open office XLS spec</a>. We + are collaborating with the maintainer of the spec so if you think you can add something to their + document just send through your changes. + </p> + </section> + <section><title>Help, I Can't Find Feature X Documented Anywhere</title> + <ol> + <li> + Look at OpenOffice.org or Gnumeric sources if its implemented there. + </li> + <li> + Use org.apache.poi.hssf.dev.BiffViewer to view the structure of the + file. Experiment by adding one criteria entry at a time. See what it + does to the structure, infer behavior and structure from it. Using the + unix diff command (or get cygwin from www.cygwin.com for windows) you + can figure out a lot very quickly. Unimplemented records show up as + 'UNKNOWN' and prints a hex dump. + </li> + </ol> + </section> + <section><title>Low-level Record Generation</title> + <p> + Low level records can be time consuming to created. We created a record + generator to help generate some of the simpler tasks. + </p> + <p> + We use XML + descriptors to generate the Java code (which sure beats the heck out of + the PERL scripts originally used ;-) for low level records. The + generator is kinda alpha-ish right now and could use some enhancement, + so you may find that to be about 1/2 of the work. Notice this is in + org.apache.poi.hssf.record.definitions. + </p> + </section> + <section><title>Important Notice</title> + <p>One thing to note: If you are making a large code contribution we need to ensure + any participants in this process have never + signed a "Non Disclosure Agreement" with Microsoft, and have not + received any information covered by such an agreement. If they have + they'll not be able to participate in the POI project. For large contributions we + may ask you to sign an agreement.</p> + </section> + <section><title>What Can I Work On?</title> + <p>Ask in the dev mailing list for advice.</p> + </section> + <section><title>What Else Should I Know?</title> + <p>Make sure you <a href="site:guidelines">read the contributing section</a> + as it contains more generation information about contributing to POI in general.</p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/how-to.xml b/src/documentation/content/xdocs/components/spreadsheet/how-to.xml new file mode 100644 index 0000000000..17582e914c --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/how-to.xml @@ -0,0 +1,884 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>The New Halloween Document</title> + <authors> + <person email="acoliver2@users.sourceforge.net" name="Andrew C. Oliver" id="AO"/> + <person email="user@poi.apache.org" name="Glen Stampoultzis" id="GJS"/> + <person email="nick@apache.org" name="Nick Burch" id="NB"/> + <person email="sergeikozello@mail.ru" name="Sergei Kozello" id="SK"/> + </authors> + </header> + <body> + <section><title>How to use the HSSF API</title> + + <section><title>Capabilities</title> + <p>This release of the how-to outlines functionality for the + current svn trunk. + Those looking for information on previous releases should + look in the documentation distributed with that release.</p> + <p> + HSSF allows numeric, string, date or formula cell values to be written to + or read from an XLS file. Also + in this release is row and column sizing, cell styling (bold, + italics, borders,etc), and support for both built-in and user + defined data formats. Also available is + an event-based API for reading XLS files. + It differs greatly from the read/write API + and is intended for intermediate developers who need a smaller + memory footprint. + </p> + </section> + <section><title>Different APIs</title> + <p>There are a few different ways to access the HSSF API. These + have different characteristics, so you should read up on + all to select the best for you.</p> + <ul> + <li><a href="#user_api">User API (HSSF and XSSF)</a></li> + <li><a href="#event_api">Event API (HSSF Only)</a></li> + <li><a href="#record_aware_event_api">Event API with extensions to be Record Aware (HSSF Only)</a></li> + <li><a href="#xssf_sax_api">XSSF and SAX (Event API)</a></li> + <li><a href="#sxssf">SXSSF (Streaming User API)</a></li> + <li><a href="#low_level_api">Low Level API</a></li> + </ul> + </section> + </section> + <section><title>General Use</title> + <anchor id="user_api" /> + <section><title>User API (HSSF and XSSF)</title> + <section><title>Writing a new file</title> + + <p>The high level API (package: org.apache.poi.ss.usermodel) + is what most people should use. Usage is very simple. + </p> + <p>Workbooks are created by creating an instance of + org.apache.poi.ss.usermodel.Workbook. Either create + a concrete class directly + (org.apache.poi.hssf.usermodel.HSSFWorkbook or + org.apache.poi.xssf.usermodel.XSSFWorkbook), or use + the handy factory class + org.apache.poi.ss.usermodel.WorkbookFactory. + </p> + <p>Sheets are created by calling createSheet() from an existing + instance of Workbook, the created sheet is automatically added in + sequence to the workbook. Sheets do not in themselves have a sheet + name (the tab at the bottom); you set + the name associated with a sheet by calling + Workbook.setSheetName(sheetindex,"SheetName",encoding). + For HSSF, the name may be in 8bit format + (HSSFWorkbook.ENCODING_COMPRESSED_UNICODE) + or Unicode (HSSFWorkbook.ENCODING_UTF_16). Default + encoding for HSSF is 8bit per char. For XSSF, the name + is automatically handled as unicode. + </p> + <p>Rows are created by calling createRow(rowNumber) from an existing + instance of Sheet. Only rows that have cell values should be + added to the sheet. To set the row's height, you just call + setRowHeight(height) on the row object. The height must be given in + twips, or 1/20th of a point. If you prefer, there is also a + setRowHeightInPoints method. + </p> + <p>Cells are created by calling createCell(column, type) from an + existing Row. Only cells that have values should be added to the + row. Cells should have their cell type set to either + Cell.CELL_TYPE_NUMERIC or Cell.CELL_TYPE_STRING depending on + whether they contain a numeric or textual value. Cells must also have + a value set. Set the value by calling setCellValue with either a + String or double as a parameter. Individual cells do not have a + width; you must call setColumnWidth(colindex, width) (use units of + 1/256th of a character) on the Sheet object. (You can't do it on + an individual basis in the GUI either).</p> + <p>Cells are styled with CellStyle objects which in turn contain + a reference to an Font object. These are created via the + Workbook object by calling createCellStyle() and createFont(). + Once you create the object you must set its parameters (colors, + borders, etc). To set a font for an CellStyle call + setFont(fontobj). + </p> + <p>Once you have generated your workbook, you can write it out by + calling write(outputStream) from your instance of Workbook, passing + it an OutputStream (for instance, a FileOutputStream or + ServletOutputStream). You must close the OutputStream yourself. HSSF + does not close it for you. + </p> + <p>Here is some example code (excerpted and adapted from + org.apache.poi.hssf.dev.HSSF test class):</p> +<source><![CDATA[ +short rownum; + +// create a new file +FileOutputStream out = new FileOutputStream("workbook.xls"); +// create a new workbook +Workbook wb = new HSSFWorkbook(); +// create a new sheet +Sheet s = wb.createSheet(); +// declare a row object reference +Row r = null; +// declare a cell object reference +Cell c = null; +// create 3 cell styles +CellStyle cs = wb.createCellStyle(); +CellStyle cs2 = wb.createCellStyle(); +CellStyle cs3 = wb.createCellStyle(); +DataFormat df = wb.createDataFormat(); +// create 2 fonts objects +Font f = wb.createFont(); +Font f2 = wb.createFont(); + +//set font 1 to 12 point type +f.setFontHeightInPoints((short) 12); +//make it blue +f.setColor( (short)0xc ); +// make it bold +//arial is the default font +f.setBoldweight(Font.BOLDWEIGHT_BOLD); + +//set font 2 to 10 point type +f2.setFontHeightInPoints((short) 10); +//make it red +f2.setColor( (short)Font.COLOR_RED ); +//make it bold +f2.setBoldweight(Font.BOLDWEIGHT_BOLD); + +f2.setStrikeout( true ); + +//set cell stlye +cs.setFont(f); +//set the cell format +cs.setDataFormat(df.getFormat("#,##0.0")); + +//set a thin border +cs2.setBorderBottom(cs2.BORDER_THIN); +//fill w fg fill color +cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); +//set the cell format to text see DataFormat for a full list +cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); + +// set the font +cs2.setFont(f2); + +// set the sheet name in Unicode +wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430" ); +// in case of plain ascii +// wb.setSheetName(0, "HSSF Test"); +// create a sheet with 30 rows (0-29) +int rownum; +for (rownum = (short) 0; rownum < 30; rownum++) +{ + // create a row + r = s.createRow(rownum); + // on every other row + if ((rownum % 2) == 0) + { + // make the row height bigger (in twips - 1/20 of a point) + r.setHeight((short) 0x249); + } + + //r.setRowNum(( short ) rownum); + // create 10 cells (0-9) (the += 2 becomes apparent later + for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) + { + // create a numeric cell + c = r.createCell(cellnum); + // do some goofy math to demonstrate decimals + c.setCellValue(rownum * 10000 + cellnum + + (((double) rownum / 1000) + + ((double) cellnum / 10000))); + + String cellValue; + + // create a string cell (see why += 2 in the + c = r.createCell((short) (cellnum + 1)); + + // on every other row + if ((rownum % 2) == 0) + { + // set this cell to the first cell style we defined + c.setCellStyle(cs); + // set the cell's string value to "Test" + c.setCellValue( "Test" ); + } + else + { + c.setCellStyle(cs2); + // set the cell's string value to "\u0422\u0435\u0441\u0442" + c.setCellValue( "\u0422\u0435\u0441\u0442" ); + } + + + // make this column a bit wider + s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); + } +} + +//draw a thick black border on the row at the bottom using BLANKS +// advance 2 rows +rownum++; +rownum++; + +r = s.createRow(rownum); + +// define the third style to be the default +// except with a thick black border at the bottom +cs3.setBorderBottom(cs3.BORDER_THICK); + +//create 50 cells +for (short cellnum = (short) 0; cellnum < 50; cellnum++) +{ + //create a blank type cell (no value) + c = r.createCell(cellnum); + // set it to the thick black border style + c.setCellStyle(cs3); +} + +//end draw thick black border + + +// demonstrate adding/naming and deleting a sheet +// create a sheet, set its title then delete it +s = wb.createSheet(); +wb.setSheetName(1, "DeletedSheet"); +wb.removeSheetAt(1); +//end deleted sheet + +// write the workbook to the output stream +// close our file (don't blow out our file handles +wb.write(out); +out.close(); + ]]></source> + </section> + <section><title>Reading or modifying an existing file</title> + +<p>Reading in a file is equally simple. To read in a file, create a +new instance of org.apache.poi.poifs.Filesystem, passing in an open InputStream, such as a FileInputStream +for your XLS, to the constructor. Construct a new instance of +org.apache.poi.hssf.usermodel.HSSFWorkbook passing the +Filesystem instance to the constructor. From there you have access to +all of the high level model objects through their assessor methods +(workbook.getSheet(sheetNum), sheet.getRow(rownum), etc). +</p> +<p>Modifying the file you have read in is simple. You retrieve the +object via an assessor method, remove it via a parent object's remove +method (sheet.removeRow(hssfrow)) and create objects just as you +would if creating a new xls. When you are done modifying cells just +call workbook.write(outputstream) just as you did above.</p> +<p>An example of this can be seen in +<a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/usermodel/HSSFReadWrite.java">org.apache.poi.hssf.usermodel.examples.HSSFReadWrite</a>.</p> + </section> + </section> + + <anchor id="event_api" /> + <section><title>Event API (HSSF Only)</title> + + <p>The event API is newer than the User API. It is intended for intermediate + developers who are willing to learn a little bit of the low level API + structures. Its relatively simple to use, but requires a basic + understanding of the parts of an Excel file (or willingness to + learn). The advantage provided is that you can read an XLS with a + relatively small memory footprint. + </p> + <p>One important thing to note with the basic Event API is that it + triggers events only for things actually stored within the file. + With the XLS file format, it is quite common for things that + have yet to be edited to simply not exist in the file. This means + there may well be apparent "gaps" in the record stream, which + you either need to work around, or use the + <a href="#record_aware_event_api">Record Aware</a> extension + to the Event API.</p> + <p>To use this API you construct an instance of + org.apache.poi.hssf.eventmodel.HSSFRequest. Register a class you + create that supports the + org.apache.poi.hssf.eventmodel.HSSFListener interface using the + HSSFRequest.addListener(yourlistener, recordsid). The recordsid + should be a static reference number (such as BOFRecord.sid) contained + in the classes in org.apache.poi.hssf.record. The trick is you + have to know what these records are. Alternatively you can call + HSSFRequest.addListenerForAllRecords(mylistener). In order to learn + about these records you can either read all of the javadoc in the + org.apache.poi.hssf.record package or you can just hack up a + copy of org.apache.poi.hssf.dev.EFHSSF and adapt it to your + needs. TODO: better documentation on records.</p> + <p>Once you've registered your listeners in the HSSFRequest object + you can construct an instance of + org.apache.poi.poifs.filesystem.FileSystem (see POIFS howto) and + pass it your XLS file inputstream. You can either pass this, along + with the request you constructed, to an instance of HSSFEventFactory + via the HSSFEventFactory.processWorkbookEvents(request, Filesystem) + method, or you can get an instance of DocumentInputStream from + Filesystem.createDocumentInputStream("Workbook") and pass + it to HSSFEventFactory.processEvents(request, inputStream). Once you + make this call, the listeners that you constructed receive calls to + their processRecord(Record) methods with each Record they are + registered to listen for until the file has been completely read. + </p> + <p>A code excerpt from org.apache.poi.hssf.dev.EFHSSF (which is + in CVS or the source distribution) is reprinted below with excessive + comments:</p> +<source><![CDATA[ +/** + * This example shows how to use the event API for reading a file. + */ +public class EventExample + implements HSSFListener +{ + private SSTRecord sstrec; + + /** + * This method listens for incoming records and handles them as required. + * @param record The record that was found while reading. + */ + public void processRecord(Record record) + { + switch (record.getSid()) + { + // the BOFRecord can represent either the beginning of a sheet or the workbook + case BOFRecord.sid: + BOFRecord bof = (BOFRecord) record; + if (bof.getType() == bof.TYPE_WORKBOOK) + { + System.out.println("Encountered workbook"); + // assigned to the class level member + } else if (bof.getType() == bof.TYPE_WORKSHEET) + { + System.out.println("Encountered sheet reference"); + } + break; + case BoundSheetRecord.sid: + BoundSheetRecord bsr = (BoundSheetRecord) record; + System.out.println("New sheet named: " + bsr.getSheetname()); + break; + case RowRecord.sid: + RowRecord rowrec = (RowRecord) record; + System.out.println("Row found, first column at " + + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol()); + break; + case NumberRecord.sid: + NumberRecord numrec = (NumberRecord) record; + System.out.println("Cell found with value " + numrec.getValue() + + " at row " + numrec.getRow() + " and column " + numrec.getColumn()); + break; + // SSTRecords store an array of unique strings used in Excel. + case SSTRecord.sid: + sstrec = (SSTRecord) record; + for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) + { + System.out.println("String table value " + k + " = " + sstrec.getString(k)); + } + break; + case LabelSSTRecord.sid: + LabelSSTRecord lrec = (LabelSSTRecord) record; + System.out.println("String cell found with value " + + sstrec.getString(lrec.getSSTIndex())); + break; + } + } + + /** + * Read an excel file and spit out what we find. + * + * @param args Expect one argument that is the file to read. + * @throws IOException When there is an error processing the file. + */ + public static void main(String[] args) throws IOException + { + // create a new file input stream with the input file specified + // at the command line + FileInputStream fin = new FileInputStream(args[0]); + // create a new org.apache.poi.poifs.filesystem.Filesystem + POIFSFileSystem poifs = new POIFSFileSystem(fin); + // get the Workbook (excel part) stream in a InputStream + InputStream din = poifs.createDocumentInputStream("Workbook"); + // construct out HSSFRequest object + HSSFRequest req = new HSSFRequest(); + // lazy listen for ALL records with the listener shown above + req.addListenerForAllRecords(new EventExample()); + // create our event factory + HSSFEventFactory factory = new HSSFEventFactory(); + // process our events based on the document input stream + factory.processEvents(req, din); + // once all the events are processed close our file input stream + fin.close(); + // and our document input stream (don't want to leak these!) + din.close(); + System.out.println("done."); + } +} +]]></source> + </section> + + <anchor id="record_aware_event_api" /> + <section><title>Record Aware Event API (HSSF Only)</title> +<p> +This is an extension to the normal +<a href="#event_api">Event API</a>. With this, your listener +will be called with extra, dummy records. These dummy records should +alert you to records which aren't present in the file (eg cells that have +yet to be edited), and allow you to handle these. +</p> +<p> +There are three dummy records that your HSSFListener will be called with: +</p> +<ul> + <li>org.apache.poi.hssf.eventusermodel.dummyrecord.MissingRowDummyRecord + <br /> + This is called during the row record phase (which typically occurs before + the cell records), and indicates that the row record for the given + row is not present in the file.</li> + <li>org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord + <br /> + This is called during the cell record phase. It is called when a cell + record is encountered which leaves a gap between it an the previous one. + You can get multiple of these, before the real cell record.</li> + <li>org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord + <br /> + This is called after the last cell of a given row. It indicates that there + are no more cells for the row, and also tells you how many cells you have + had. For a row with no cells, this will be the only record you get.</li> +</ul> +<p> +To use the Record Aware Event API, you should create an +org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener, and pass +it your HSSFListener. Then, register the MissingRecordAwareHSSFListener +to the event model, and start that as normal. +</p> +<p> +One example use for this API is to write a CSV outputter, which always +outputs a minimum number of columns, even where the file doesn't contain +some of the rows or cells. It can be found at +<code>/poi-examples/src/main/java/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java</code>, +and may be called on the command line, or from within your own code. +The latest version is always available from +<a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/hssf/eventusermodel/">git</a>. +</p> +<p> +<em>In POI versions before 3.0.3, this code lived in the scratchpad section. + If you're using one of these older versions of POI, you will either + need to include the scratchpad jar on your classpath, or build from a</em> + <a href="site:git">git checkout</a>. +</p> + </section> + + <anchor id="xssf_sax_api"/> + <section><title>XSSF and SAX (Event API)</title> + + <p>If memory footprint is an issue, then for XSSF, you can get at + the underlying XML data, and process it yourself. This is intended + for intermediate developers who are willing to learn a little bit of + low level structure of .xlsx files, and who are happy processing + XML in java. Its relatively simple to use, but requires a basic + understanding of the file structure. The advantage provided is that + you can read a XLSX file with a relatively small memory footprint. + </p> + <p>One important thing to note with the basic Event API is that it + triggers events only for things actually stored within the file. + With the XLSX file format, it is quite common for things that + have yet to be edited to simply not exist in the file. This means + there may well be apparent "gaps" in the record stream, which + you need to work around.</p> + <p>To use this API you construct an instance of + org.apache.poi.xssf.eventmodel.XSSFReader. This will optionally + provide a nice interface on the shared strings table, and the styles. + It provides methods to get the raw xml data from the rest of the + file, which you will then pass to SAX.</p> + <p>This example shows how to get at a single known sheet, or at + all sheets in the file. It is based on the example in + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/FromHowTo.java">svn + poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/FromHowTo.java</a></p> +<source><![CDATA[ +import java.io.InputStream; +import java.util.Iterator; + +import org.apache.poi.util.XMLHelper; +import org.apache.poi.openxml4j.opc.OPCPackage; +import org.apache.poi.xssf.eventusermodel.XSSFReader; +import org.apache.poi.xssf.model.SharedStringsTable; +import org.xml.sax.Attributes; +import org.xml.sax.ContentHandler; +import org.xml.sax.InputSource; +import org.xml.sax.SAXException; +import org.xml.sax.XMLReader; +import org.xml.sax.helpers.DefaultHandler; + +import javax.xml.parsers.ParserConfigurationException; + +public class ExampleEventUserModel { + public void processOneSheet(String filename) throws Exception { + OPCPackage pkg = OPCPackage.open(filename); + XSSFReader r = new XSSFReader( pkg ); + SharedStringsTable sst = r.getSharedStringsTable(); + + XMLReader parser = fetchSheetParser(sst); + + // To look up the Sheet Name / Sheet Order / rID, + // you need to process the core Workbook stream. + // Normally it's of the form rId# or rSheet# + InputStream sheet2 = r.getSheet("rId2"); + InputSource sheetSource = new InputSource(sheet2); + parser.parse(sheetSource); + sheet2.close(); + } + + public void processAllSheets(String filename) throws Exception { + OPCPackage pkg = OPCPackage.open(filename); + XSSFReader r = new XSSFReader( pkg ); + SharedStringsTable sst = r.getSharedStringsTable(); + + XMLReader parser = fetchSheetParser(sst); + + Iterator<InputStream> sheets = r.getSheetsData(); + while(sheets.hasNext()) { + System.out.println("Processing new sheet:\n"); + InputStream sheet = sheets.next(); + InputSource sheetSource = new InputSource(sheet); + parser.parse(sheetSource); + sheet.close(); + System.out.println(""); + } + } + + public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException, ParserConfigurationException { + XMLReader parser = XMLHelper.newXMLReader(); + ContentHandler handler = new SheetHandler(sst); + parser.setContentHandler(handler); + return parser; + } + + /** + * See org.xml.sax.helpers.DefaultHandler javadocs + */ + private static class SheetHandler extends DefaultHandler { + private SharedStringsTable sst; + private String lastContents; + private boolean nextIsString; + + private SheetHandler(SharedStringsTable sst) { + this.sst = sst; + } + + public void startElement(String uri, String localName, String name, + Attributes attributes) throws SAXException { + // c => cell + if(name.equals("c")) { + // Print the cell reference + System.out.print(attributes.getValue("r") + " - "); + // Figure out if the value is an index in the SST + String cellType = attributes.getValue("t"); + if(cellType != null && cellType.equals("s")) { + nextIsString = true; + } else { + nextIsString = false; + } + } + // Clear contents cache + lastContents = ""; + } + + public void endElement(String uri, String localName, String name) + throws SAXException { + // Process the last contents as required. + // Do now, as characters() may be called more than once + if(nextIsString) { + int idx = Integer.parseInt(lastContents); + lastContents = sst.getItemAt(idx).getString(); + nextIsString = false; + } + + // v => contents of a cell + // Output after we've seen the string contents + if(name.equals("v")) { + System.out.println(lastContents); + } + } + + public void characters(char[] ch, int start, int length) { + lastContents += new String(ch, start, length); + } + } + + public static void main(String[] args) throws Exception { + ExampleEventUserModel example = new ExampleEventUserModel(); + example.processOneSheet(args[0]); + example.processAllSheets(args[0]); + } +} +]]></source> + <p> + For a fuller example, including support for fetching number formatting + information and applying it to numeric cells (eg to format dates or + percentages), please see + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java">the XLSX2CSV example in svn</a> + </p> + <p>An example is also <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/streaming/HybridStreaming.java">provided</a> + showing how to combine the user API and the SAX API by doing a streaming parse + of larger worksheets and a traditional user-model parse of the rest of a workbook.</p> + </section> + <anchor id="sxssf"/> + <section><title>SXSSF (Streaming Usermodel API)</title> + <p> + SXSSF (package: org.apache.poi.xssf.streaming) is an API-compatible streaming extension of XSSF to be used when + very large spreadsheets have to be produced, and heap space is limited. + SXSSF achieves its low memory footprint by limiting access to the rows that + are within a sliding window, while XSSF gives access to all rows in the + document. Older rows that are no longer in the window become inaccessible, + as they are written to the disk. + </p> + <p> + You can specify the window size at workbook construction time via <em>new SXSSFWorkbook(int windowSize)</em> + or you can set it per-sheet via <em>SXSSFSheet#setRandomAccessWindowSize(int windowSize)</em> + </p> + <p> + When a new row is created via createRow() and the total number + of unflushed records would exceed the specified window size, then the + row with the lowest index value is flushed and cannot be accessed + via getRow() anymore. + </p> + <p> + The default window size is <em>100</em> and defined by SXSSFWorkbook.DEFAULT_WINDOW_SIZE. + </p> + <p> + A windowSize of -1 indicates unlimited access. In this case all + records that have not been flushed by a call to flushRows() are available + for random access. + </p> + <p> + Note that SXSSF allocates temporary files that you <strong>must</strong> always clean up explicitly, by calling the dispose method. + </p> + <p> + SXSSFWorkbook defaults to using inline strings instead of a shared strings + table. This is very efficient, since no document content needs to be kept in + memory, but is also known to produce documents that are incompatible with + some clients. With shared strings enabled all unique strings in the document + has to be kept in memory. Depending on your document content this could use + a lot more resources than with shared strings disabled. + </p> + <p> + Please note that there are still things that still may consume a large + amount of memory based on which features you are using, e.g. merged regions, + hyperlinks, comments, ... are still only stored in memory and thus may require a lot of + memory if used extensively. + </p> + <p> + Carefully review your memory budget and compatibility needs before deciding + whether to enable shared strings or not. + </p> + <p> The example below writes a sheet with a window of 100 rows. When the row count reaches 101, + the row with rownum=0 is flushed to disk and removed from memory, when rownum reaches 102 then the row with rownum=1 is flushed, etc. + </p> + + +<source><![CDATA[ +import junit.framework.Assert; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.streaming.SXSSFWorkbook; + + public static void main(String[] args) throws Throwable { + SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk + Sheet sh = wb.createSheet(); + for(int rownum = 0; rownum < 1000; rownum++){ + Row row = sh.createRow(rownum); + for(int cellnum = 0; cellnum < 10; cellnum++){ + Cell cell = row.createCell(cellnum); + String address = new CellReference(cell).formatAsString(); + cell.setCellValue(address); + } + + } + + // Rows with rownum < 900 are flushed and not accessible + for(int rownum = 0; rownum < 900; rownum++){ + Assert.assertNull(sh.getRow(rownum)); + } + + // ther last 100 rows are still in memory + for(int rownum = 900; rownum < 1000; rownum++){ + Assert.assertNotNull(sh.getRow(rownum)); + } + + FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx"); + wb.write(out); + out.close(); + + // dispose of temporary files backing this workbook on disk + wb.dispose(); + } + + +]]></source> +<p>The next example turns off auto-flushing (windowSize=-1) and the code manually controls how portions of data are written to disk</p> +<source><![CDATA[ +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.streaming.SXSSFWorkbook; + + public static void main(String[] args) throws Throwable { + SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory + Sheet sh = wb.createSheet(); + for(int rownum = 0; rownum < 1000; rownum++){ + Row row = sh.createRow(rownum); + for(int cellnum = 0; cellnum < 10; cellnum++){ + Cell cell = row.createCell(cellnum); + String address = new CellReference(cell).formatAsString(); + cell.setCellValue(address); + } + + // manually control how rows are flushed to disk + if(rownum % 100 == 0) { + ((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others + + // ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0), + // this method flushes all rows + } + + } + + FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx"); + wb.write(out); + out.close(); + + // dispose of temporary files backing this workbook on disk + wb.dispose(); + } + + +]]></source> +<p>SXSSF flushes sheet data in temporary files (a temp file per sheet) and the size of these temporary files +can grow to a very large value. For example, for a 20 MB csv data the size of the temp xml becomes more than a gigabyte. +If the size of the temp files is an issue, you can tell SXSSF to use gzip compression: +</p> +<source><![CDATA[ + SXSSFWorkbook wb = new SXSSFWorkbook(); + wb.setCompressTempFiles(true); // temp files will be gzipped + +]]></source> + </section> + + <anchor id="low_level_api" /> + <section><title>Low Level APIs</title> + +<p>The low level API is not much to look at. It consists of lots of +"Records" in the org.apache.poi.hssf.record.* package, +and set of helper classes in org.apache.poi.hssf.model.*. The +record classes are consistent with the low level binary structures +inside a BIFF8 file (which is embedded in a POIFS file system). You +probably need the book: "Microsoft Excel 97 Developer's Kit" +from Microsoft Press in order to understand how these fit together +(out of print but easily obtainable from Amazon's used books). In +order to gain a good understanding of how to use the low level APIs +should view the source in org.apache.poi.hssf.usermodel.* and +the classes in org.apache.poi.hssf.model.*. You should read the +documentation for the POIFS libraries as well.</p> + </section> + <section><title>Generating XLS from XML</title> +<p>If you wish to generate an XLS file from some XML, it is possible to +write your own XML processing code, then use the User API to write out +the document.</p> +<p>The other option is to use <a href="https://cocoon.apache.org/">Cocoon</a>. +In Cocoon, there is the <a href="https://cocoon.apache.org/2.1/userdocs/xls-serializer.html">HSSF Serializer</a>, +which takes in XML (in the gnumeric format), and outputs an XLS file for you.</p> + </section> + <section><title>HSSF Class/Test Application</title> + +<p>The HSSF application is nothing more than a test for the high +level API (and indirectly the low level support). The main body of +its code is repeated above. To run it: +</p> +<ul> + <li>download the poi-alpha build and untar it (tar xvzf + tarball.tar.gz) + </li> + <li>set up your classpath as follows: + <code>export HSSFDIR={wherever you put HSSF's jar files} +export LOG4JDIR={wherever you put LOG4J's jar files} +export CLASSPATH=$CLASSPATH:$HSSFDIR/hssf.jar:$HSSFDIR/poi-poifs.jar:$HSSFDIR/poi-util.jar:$LOG4JDIR/log4j.jar</code> + </li><li>type: + <code>java org.apache.poi.hssf.dev.HSSF ~/myxls.xls write</code></li> +</ul> +<p></p> +<p>This should generate a test sheet in your home directory called <code>"myxls.xls"</code>. </p> +<ul> + <li>Type: + <code>java org.apache.poi.hssf.dev.HSSF ~/input.xls output.xls</code> + <br/> + <br/> +This is the read/write/modify test. It reads in the spreadsheet, modifies a cell, and writes it back out. +Failing this test is not necessarily a bad thing. If HSSF tries to modify a non-existant sheet then this will +most likely fail. No big deal. </li> +</ul> + </section> + <section><title>HSSF Developer's Tools</title> + +<p>HSSF has a number of tools useful for developers to debug/develop +stuff using HSSF (and more generally XLS files). We've already +discussed the app for testing HSSF read/write/modify capabilities; +now we'll talk a bit about BiffViewer. Early on in the development of +HSSF, it was decided that knowing what was in a record, what was +wrong with it, etc. was virtually impossible with the available +tools. So we developed BiffViewer. You can find it at +org.apache.poi.hssf.dev.BiffViewer. It performs two basic +functions and a derivative. +</p> +<p>The first is "biffview". To do this you run it (assumes +you have everything setup in your classpath and that you know what +you're doing enough to be thinking about this) with an xls file as a +parameter. It will give you a listing of all understood records with +their data and a list of not-yet-understood records with no data +(because it doesn't know how to interpret them). This listing is +useful for several things. First, you can look at the values and SEE +what is wrong in quasi-English. Second, you can send the output to a +file and compare it. +</p> +<p>The second function is "big freakin dump", just pass a +file and a second argument matching "bfd" exactly. This +will just make a big hexdump of the file. +</p> +<p>Lastly, there is "mixed" mode which does the same as +regular biffview, only it includes hex dumps of certain records +intertwined. To use that just pass a file with a second argument +matching "on" exactly.</p> +<p>In the next release cycle we'll also have something called a +FormulaViewer. The class is already there, but its not very useful +yet. When it does something, we'll document it.</p> + + </section> + <section><title>What's Next?</title> + +<p>Further effort on HSSF is going to focus on the following major areas: </p> +<ul> +<li>Performance: POI currently uses a lot of memory for large sheets.</li> +<li>Charts: This is a hard problem, with very little documentation.</li> +</ul> +<p><a href="site:guidelines"> So jump in! </a> </p> + + </section> + +</section> +</body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/index.xml b/src/documentation/content/xdocs/components/spreadsheet/index.xml new file mode 100644 index 0000000000..ec262b554c --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/index.xml @@ -0,0 +1,119 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>POI-HSSF and POI-XSSF/SXSSF - Java API To Access Microsoft Excel Format Files</title> + <subtitle>Overview</subtitle> + <authors> + <person name="Andrew C. Oliver" email="acoliver@apache.org"/> + <person name="Nicola Ken Barozzi" email="barozzi@nicolaken.com"/> + </authors> + </header> + + <body> + <section> + <title>Overview</title> + + <p>HSSF is the POI Project's pure Java implementation of the + Excel '97(-2007) file format. XSSF is the POI Project's pure + Java implementation of the Excel 2007 OOXML (.xlsx) file + format.</p> + <p>HSSF and XSSF provides ways to read spreadsheets create, + modify, read and write XLS spreadsheets. They provide: + </p> + <ul> + <li>low level structures for those with special needs</li> + <li>an eventmodel api for efficient read-only access</li> + <li>a full usermodel api for creating, reading and modifying XLS files</li> + </ul> + <p>For people converting from pure HSSF usermodel, who wish + to use the joint SS Usermodel for HSSF and XSSF support, then + see the <a href="converting.html">ss usermodel converting + guide</a>. + </p> + <p> + An alternate way of generating a spreadsheet is via the <a href="https://cocoon.apache.org">Cocoon</a> serializer (yet you'll still be using HSSF indirectly). + With Cocoon you can serialize any XML datasource (which might be a ESQL page outputting in SQL for instance) by simply + applying the stylesheet and designating the serializer. + </p> + <p> + If you're merely reading spreadsheet data, then use the + eventmodel api in either the org.apache.poi.hssf.eventusermodel + package, or the org.apache.poi.xssf.eventusermodel package, depending + on your file format. + </p> + <p> + If you're modifying spreadsheet data then use the usermodel api. You + can also generate spreadsheets this way. + </p> + <p> + Note that the usermodel system has a higher memory footprint than + the low level eventusermodel, but has the major advantage of being + much simpler to work with. Also please be aware that as the new + XSSF supported Excel 2007 OOXML (.xlsx) files are XML based, + the memory footprint for processing them is higher than for the + older HSSF supported (.xls) binary files. + </p> + + + + </section> + +<section> +<title>SXSSF (Since POI 3.8 beta3)</title> +<p>Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF.</p> +<p> +SXSSF is an API-compatible streaming extension of XSSF to be used when +very large spreadsheets have to be produced, and heap space is limited. +SXSSF achieves its low memory footprint by limiting access to the rows that +are within a sliding window, while XSSF gives access to all rows in the +document. Older rows that are no longer in the window become inaccessible, +as they are written to the disk. +</p> +<p> +In auto-flush mode the size of the access window can be specified, to hold a certain number of rows in memory. +When that value is reached, the creation of an additional row causes the row with the lowest index to to be +removed from the access window and written to disk. Or, the window size can be set to grow dynamically; +it can be trimmed periodically by an explicit call to flushRows(int keepRows) as needed. +</p> +<p> +Due to the streaming nature of the implementation, there are the following +limitations when compared to XSSF: +</p> + <ul> + <li>Only a limited number of rows are accessible at a point in time.</li> + <li>Sheet.clone() is not supported.</li> + <li>Formula evaluation is not supported</li> + </ul> + + <p> See more details at <a href="how-to.html#sxssf">SXSSF How-To</a></p> + +<p>The table below synopsizes the comparative features of POI's Spreadsheet API:</p> + <p><em>Spreadsheet API Feature Summary</em></p> + + <p> + <img src="images/ss-features.png" alt="Spreadsheet API Feature Summary"/> + </p> +</section> + + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/limitations.xml b/src/documentation/content/xdocs/components/spreadsheet/limitations.xml new file mode 100644 index 0000000000..ce9f8afc56 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/limitations.xml @@ -0,0 +1,99 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Apache POI™ - HSSF and XSSF Limitations</title> + <authors> + <person email="user@poi.apache.org" name="Glen Stampoultzis" id="GJS"/> + </authors> + </header> + <body> + <section><title>Current HSSF / XSSF main limitations</title> + <p> + The intent of this document is to outline some of the known limitations of the + POI HSSF and XSSF APIs. It is not intended to be complete list of every bug + or missing feature of HSSF or XSSF, rather it's purpose is to provide a broad + feel for some of the functionality that is missing or broken. + </p> + <ul> + <li> + File sizes/Memory usage<br/><br/> + <ul> + <li> + There are some inherent limits in the Excel file formats. These are defined in class + <a href="../../apidocs/dev/org/apache/poi/ss/SpreadsheetVersion.html">SpreadsheetVersion</a>. + As long as you have enough main-memory, you should be able to handle files up to these limits. For huge files + using the default POI classes you will likely need a very large amount of memory. + <br/> + <br/> + There are ways to overcome the main-memory limitations if needed: + <br/> + <ul> + <li> + For writing very huge files, there is <a href="site:spreadsheet">SXSSFWorkbook</a> + which allows to do a streaming write of data out to files (with certain limitations on what you can do as only + parts of the file are held in memory). + </li> + <li> + For reading very huge files, take a look at the sample + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java">XLSX2CSV</a> + which shows how you can read a file in streaming fashion (again with some limitations on what information you + can read out of the file, but there are ways to get at most of it if necessary). + </li> + </ul> + </li> + </ul> + </li> + <li> + Charts<br/><br/> + <ul> + <li> + HSSF has some limited support for creating a handful of very simple Chart types, + but largely this isn't supported. HSSF (largely) doesn't support changing Charts. + You can however create a chart in Excel using Named ranges, modify the chart data + values using HSSF and write a new spreadsheet out. This is possible because POI + attempts to keep existing records intact as far as possible.<br/> + </li> + <li> + XSSF has only limited chart support including making some simple changes + and adding at least some line and scatter charts, see the examples <em>LineChart</em> + and <em>ScatterChart</em>.<br/><br/> + </li> + </ul> + </li> + <li> + Macros<br/><br/> + Macros can not be created. The are currently no plans to support macros. + However, reading and re-writing files containing macros will safely preserve + the macros. Recent versions of Apache POI support extracting the macro data + via <a href="../../apidocs/dev/org/apache/poi/poifs/macros/VBAMacroExtractor.html">VBAMacroExtractor</a> + and <a href="../../apidocs/dev/org/apache/poi/poifs/macros/VBAMacroReader.html">VBAMacroReader</a><br/><br/> + </li> + <li> + Pivot Tables<br/><br/> + HSSF doesn't have support for reading or creating Pivot tables. XSSF has limited + support for creating Pivot Tables, and very limited read/change support. + </li> + </ul> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/quick-guide.xml b/src/documentation/content/xdocs/components/spreadsheet/quick-guide.xml new file mode 100644 index 0000000000..3ef09310b7 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/quick-guide.xml @@ -0,0 +1,2455 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Busy Developers' Guide to HSSF and XSSF Features</title> + </header> + <body> + <section><title>Busy Developers' Guide to Features</title> + <p> + Want to use HSSF and XSSF read and write spreadsheets in a hurry? This + guide is for you. If you're after more in-depth coverage of the HSSF and + XSSF user-APIs, please consult the <a href="how-to.html">HOWTO</a> + guide as it contains actual descriptions of how to use this stuff. + </p> + <section><title>Index of Features</title> + <ul> + <li><a href="#NewWorkbook">How to create a new workbook</a></li> + <li><a href="#NewSheet">How to create a sheet</a></li> + <li><a href="#CreateCells">How to create cells</a></li> + <li><a href="#CreateDateCells">How to create date cells</a></li> + <li><a href="#CellTypes">Working with different types of cells</a></li> + <li><a href="#Iterator">Iterate over rows and cells</a></li> + <li><a href="#CellContents">Getting the cell contents</a></li> + <li><a href="#TextExtraction">Text Extraction</a></li> + <li><a href="#FileInputStream">Files vs InputStreams</a></li> + <li><a href="#Alignment">Aligning cells</a></li> + <li><a href="#Borders">Working with borders</a></li> + <li><a href="#FillsAndFrills">Fills and color</a></li> + <li><a href="#MergedCells">Merging cells</a></li> + <li><a href="#WorkingWithFonts">Working with fonts</a></li> + <li><a href="#CustomColors">Custom colors</a></li> + <li><a href="#ReadWriteWorkbook">Reading and writing</a></li> + <li><a href="#NewLinesInCells">Use newlines in cells.</a></li> + <li><a href="#DataFormats">Create user defined data formats</a></li> + <li><a href="#FitTo">Fit Sheet to One Page</a></li> + <li><a href="#PrintArea2">Set print area for a sheet</a></li> + <li><a href="#FooterPageNumbers">Set page numbers on the footer of a sheet</a></li> + <li><a href="#ShiftRows">Shift rows</a></li> + <li><a href="#SelectSheet">Set a sheet as selected</a></li> + <li><a href="#Zoom">Set the zoom magnification for a sheet</a></li> + <li><a href="#Splits">Create split and freeze panes</a></li> + <li><a href="#Repeating">Repeating rows and columns</a></li> + <li><a href="#HeaderFooter">Headers and Footers</a></li> + <li><a href="#XSSFHeaderFooter">XSSF enhancement for Headers and Footers</a></li> + <li><a href="#DrawingShapes">Drawing Shapes</a></li> + <li><a href="#StylingShapes">Styling Shapes</a></li> + <li><a href="#Graphics2d">Shapes and Graphics2d</a></li> + <li><a href="#Outlining">Outlining</a></li> + <li><a href="#Images">Images</a></li> + <li><a href="#NamedRanges">Named Ranges and Named Cells</a></li> + <li><a href="#CellComments">How to set cell comments</a></li> + <li><a href="#Autofit">How to adjust column width to fit the contents</a></li> + <li><a href="#Hyperlinks">Hyperlinks</a></li> + <li><a href="#Validation">Data Validations</a></li> + <li><a href="#Embedded">Embedded Objects</a></li> + <li><a href="#Autofilter">Autofilters</a></li> + <li><a href="#ConditionalFormatting">Conditional Formatting</a></li> + <li><a href="#Hiding">Hiding and Un-Hiding Rows</a></li> + <li><a href="#CellProperties">Setting Cell Properties</a></li> + <li><a href="#DrawingBorders">Drawing Borders</a></li> + <li><a href="#PivotTable">Create a Pivot Table</a></li> + <li><a href="#RichText">Cells with multiple styles</a></li> + </ul> + </section> + <section><title>Features</title> + <anchor id="NewWorkbook"/> + <section><title>New Workbook</title> + <source> + Workbook wb = new HSSFWorkbook(); + ... + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + Workbook wb = new XSSFWorkbook(); + ... + try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="NewSheet"/> + <section><title>New Sheet</title> + <source> + Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet1 = wb.createSheet("new sheet"); + Sheet sheet2 = wb.createSheet("second sheet"); + + // Note that sheet name is Excel must not exceed 31 characters + // and must not contain any of the any of the following characters: + // 0x0000 + // 0x0003 + // colon (:) + // backslash (\) + // asterisk (*) + // question mark (?) + // forward slash (/) + // opening square bracket ([) + // closing square bracket (]) + + // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} + // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') + String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales " + Sheet sheet3 = wb.createSheet(safeName); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="CreateCells"/> + <section><title>Creating Cells</title> + <source> + Workbook wb = new HSSFWorkbook(); + //Workbook wb = new XSSFWorkbook(); + CreationHelper createHelper = wb.getCreationHelper(); + Sheet sheet = wb.createSheet("new sheet"); + + // Create a row and put some cells in it. Rows are 0 based. + Row row = sheet.createRow(0); + // Create a cell and put a value in it. + Cell cell = row.createCell(0); + cell.setCellValue(1); + + // Or do it on one line. + row.createCell(1).setCellValue(1.2); + row.createCell(2).setCellValue( + createHelper.createRichTextString("This is a string")); + row.createCell(3).setCellValue(true); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="CreateDateCells"/> + <section><title>Creating Date Cells</title> + <source> + Workbook wb = new HSSFWorkbook(); + //Workbook wb = new XSSFWorkbook(); + CreationHelper createHelper = wb.getCreationHelper(); + Sheet sheet = wb.createSheet("new sheet"); + + // Create a row and put some cells in it. Rows are 0 based. + Row row = sheet.createRow(0); + + // Create a cell and put a date value in it. The first cell is not styled + // as a date. + Cell cell = row.createCell(0); + cell.setCellValue(new Date()); + + // we style the second cell as a date (and time). It is important to + // create a new cell style from the workbook otherwise you can end up + // modifying the built in style and effecting not only this cell but other cells. + CellStyle cellStyle = wb.createCellStyle(); + cellStyle.setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + cell = row.createCell(1); + cell.setCellValue(new Date()); + cell.setCellStyle(cellStyle); + + //you can also set date as java.util.Calendar + cell = row.createCell(2); + cell.setCellValue(Calendar.getInstance()); + cell.setCellStyle(cellStyle); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="CellTypes"/> + <section><title>Working with different types of cells</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + Row row = sheet.createRow(2); + row.createCell(0).setCellValue(1.1); + row.createCell(1).setCellValue(new Date()); + row.createCell(2).setCellValue(Calendar.getInstance()); + row.createCell(3).setCellValue("a string"); + row.createCell(4).setCellValue(true); + row.createCell(5).setCellType(CellType.ERROR); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + + <anchor id="FileInputStream"/> + <section><title>Files vs InputStreams</title> + <p>When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx + XSSFWorkbook, the Workbook can be loaded from either a <em>File</em> + or an <em>InputStream</em>. Using a <em>File</em> object allows for + lower memory consumption, while an <em>InputStream</em> requires more + memory as it has to buffer the whole file.</p> + <p>If using <em>WorkbookFactory</em>, it's very easy to use one or + the other:</p> + <source> + // Use a file + Workbook wb = WorkbookFactory.create(new File("MyExcel.xls")); + + // Use an InputStream, needs more memory + Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx")); + </source> + <p>If using <em>HSSFWorkbook</em> or <em>XSSFWorkbook</em> directly, + you should generally go through <em>POIFSFileSystem</em> or + <em>OPCPackage</em>, to have full control of the lifecycle (including + closing the file when done):</p> + <source> + // HSSFWorkbook, File + POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls")); + HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true); + .... + fs.close(); + + // HSSFWorkbook, InputStream, needs more memory + POIFSFileSystem fs = new POIFSFileSystem(myInputStream); + HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true); + + // XSSFWorkbook, File + OPCPackage pkg = OPCPackage.open(new File("file.xlsx")); + XSSFWorkbook wb = new XSSFWorkbook(pkg); + .... + pkg.close(); + + // XSSFWorkbook, InputStream, needs more memory + OPCPackage pkg = OPCPackage.open(myInputStream); + XSSFWorkbook wb = new XSSFWorkbook(pkg); + .... + pkg.close(); + </source> + </section> + + <anchor id="Alignment"/> + <section><title>Demonstrates various alignment options</title> + <source> + public static void main(String[] args) throws Exception { + Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); + + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(2); + row.setHeightInPoints(30); + + createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM); + createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM); + createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER); + createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER); + createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY); + createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP); + createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) { + wb.write(fileOut); + } + + wb.close(); + } + + /** + * Creates a cell and aligns it a certain way. + * + * @param wb the workbook + * @param row the row to create the cell in + * @param column the column number to create the cell in + * @param halign the horizontal alignment for the cell. + * @param valign the vertical alignment for the cell. + */ + private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) { + Cell cell = row.createCell(column); + cell.setCellValue("Align It"); + CellStyle cellStyle = wb.createCellStyle(); + cellStyle.setAlignment(halign); + cellStyle.setVerticalAlignment(valign); + cell.setCellStyle(cellStyle); + } + </source> + </section> + <anchor id="Borders"/> + <section><title>Working with borders</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + + // Create a row and put some cells in it. Rows are 0 based. + Row row = sheet.createRow(1); + + // Create a cell and put a value in it. + Cell cell = row.createCell(1); + cell.setCellValue(4); + + // Style the cell with borders all around. + CellStyle style = wb.createCellStyle(); + style.setBorderBottom(BorderStyle.THIN); + style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); + style.setBorderLeft(BorderStyle.THIN); + style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); + style.setBorderRight(BorderStyle.THIN); + style.setRightBorderColor(IndexedColors.BLUE.getIndex()); + style.setBorderTop(BorderStyle.MEDIUM_DASHED); + style.setTopBorderColor(IndexedColors.BLACK.getIndex()); + cell.setCellStyle(style); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="Iterator"/> + <section><title>Iterate over rows and cells</title> + <p>Sometimes, you'd like to just iterate over all the sheets in + a workbook, all the rows in a sheet, or all the cells in a row. + This is possible with a simple for loop.</p> + <p>These iterators are available by calling <em>workbook.sheetIterator()</em>, + <em>sheet.rowIterator()</em>, and <em>row.cellIterator()</em>, or + implicitly using a for-each loop. + Note that a rowIterator and cellIterator iterate over rows or + cells that have been created, skipping empty rows and cells.</p> + + <source> + for (Sheet sheet : wb ) { + for (Row row : sheet) { + for (Cell cell : row) { + // Do something here + } + } + } + </source> + </section> + <section><title>Iterate over cells, with control of missing / blank cells</title> + <p>In some cases, when iterating, you need full control over how + missing or blank rows and cells are treated, and you need to ensure + you visit every cell and not just those defined in the file. (The + CellIterator will only return the cells defined in the file, which + is largely those with values or stylings, but it depends on Excel).</p> + <p>In cases such as these, you should fetch the first and last column + information for a row, then call <em>getCell(int, MissingCellPolicy)</em> + to fetch the cell. Use a + <a href="../../apidocs/dev/org/apache/poi/ss/usermodel/Row.MissingCellPolicy.html">MissingCellPolicy</a> + to control how blank or null cells are handled.</p> + <source> + // Decide which rows to process + int rowStart = Math.min(15, sheet.getFirstRowNum()); + int rowEnd = Math.max(1400, sheet.getLastRowNum()); + + for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { + Row r = sheet.getRow(rowNum); + if (r == null) { + // This whole row is empty + // Handle it as needed + continue; + } + + int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT); + + for (int cn = 0; cn < lastColumn; cn++) { + Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL); + if (c == null) { + // The spreadsheet is empty in this cell + } else { + // Do something useful with the cell's contents + } + } + } + </source> + </section> + + <anchor id="CellContents"/> + <section><title>Getting the cell contents</title> + <p>To get the contents of a cell, you first need to + know what kind of cell it is (asking a string cell + for its numeric contents will get you a + NumberFormatException for example). So, you will + want to switch on the cell's type, and then call + the appropriate getter for that cell.</p> + <p>In the code below, we loop over every cell + in one sheet, print out the cell's reference + (eg A3), and then the cell's contents.</p> + <source> + // import org.apache.poi.ss.usermodel.*; + + DataFormatter formatter = new DataFormatter(); + Sheet sheet1 = wb.getSheetAt(0); + for (Row row : sheet1) { + for (Cell cell : row) { + CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); + System.out.print(cellRef.formatAsString()); + System.out.print(" - "); + + // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) + String text = formatter.formatCellValue(cell); + System.out.println(text); + + // Alternatively, get the value and format it yourself + switch (cell.getCellType()) { + case CellType.STRING: + System.out.println(cell.getRichStringCellValue().getString()); + break; + case CellType.NUMERIC: + if (DateUtil.isCellDateFormatted(cell)) { + System.out.println(cell.getDateCellValue()); + } else { + System.out.println(cell.getNumericCellValue()); + } + break; + case CellType.BOOLEAN: + System.out.println(cell.getBooleanCellValue()); + break; + case CellType.FORMULA: + System.out.println(cell.getCellFormula()); + break; + case CellType.BLANK: + System.out.println(); + break; + default: + System.out.println(); + } + } + } + </source> + </section> + + <anchor id="TextExtraction"/> + <section><title>Text Extraction</title> + <p>For most text extraction requirements, the standard + ExcelExtractor class should provide all you need.</p> + <source> + try (InputStream inp = new FileInputStream("workbook.xls")) { + HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); + ExcelExtractor extractor = new ExcelExtractor(wb); + + extractor.setFormulasNotResults(true); + extractor.setIncludeSheetNames(false); + String text = extractor.getText(); + wb.close(); + } + </source> + <p>For very fancy text extraction, XLS to CSV etc, + take a look at + <em>/poi-examples/src/main/java/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java</em> + </p> + </section> + <anchor id="FillsAndFrills"/> + <section><title>Fills and colors</title> + <source> + Workbook wb = new XSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + + // Create a row and put some cells in it. Rows are 0 based. + Row row = sheet.createRow(1); + + // Aqua background + CellStyle style = wb.createCellStyle(); + style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); + style.setFillPattern(FillPatternType.BIG_SPOTS); + Cell cell = row.createCell(1); + cell.setCellValue("X"); + cell.setCellStyle(style); + + // Orange "foreground", foreground being the fill foreground not the font color. + style = wb.createCellStyle(); + style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); + style.setFillPattern(FillPatternType.SOLID_FOREGROUND); + cell = row.createCell(2); + cell.setCellValue("X"); + cell.setCellStyle(style); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="MergedCells"/> + <section><title>Merging cells</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + + Row row = sheet.createRow(1); + Cell cell = row.createCell(1); + cell.setCellValue("This is a test of merging"); + + sheet.addMergedRegion(new CellRangeAddress( + 1, //first row (0-based) + 1, //last row (0-based) + 1, //first column (0-based) + 2 //last column (0-based) + )); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="WorkingWithFonts"/> + <section><title>Working with fonts</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + + // Create a row and put some cells in it. Rows are 0 based. + Row row = sheet.createRow(1); + + // Create a new font and alter it. + Font font = wb.createFont(); + font.setFontHeightInPoints((short)24); + font.setFontName("Courier New"); + font.setItalic(true); + font.setStrikeout(true); + + // Fonts are set into a style so create a new one to use. + CellStyle style = wb.createCellStyle(); + style.setFont(font); + + // Create a cell and put a value in it. + Cell cell = row.createCell(1); + cell.setCellValue("This is a test of fonts"); + cell.setCellStyle(style); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> +<p> + Note, the maximum number of unique fonts in a workbook is limited to 32767. You should re-use fonts in your applications instead of + creating a font for each cell. +Examples: +</p> +<p><strong>Wrong:</strong></p> +<source> + for (int i = 0; i < 10000; i++) { + Row row = sheet.createRow(i); + Cell cell = row.createCell(0); + + CellStyle style = workbook.createCellStyle(); + Font font = workbook.createFont(); + font.setBoldweight(Font.BOLDWEIGHT_BOLD); + style.setFont(font); + cell.setCellStyle(style); + } +</source> +<p><strong>Correct:</strong></p> +<source> + CellStyle style = workbook.createCellStyle(); + Font font = workbook.createFont(); + font.setBoldweight(Font.BOLDWEIGHT_BOLD); + style.setFont(font); + for (int i = 0; i < 10000; i++) { + Row row = sheet.createRow(i); + Cell cell = row.createCell(0); + cell.setCellStyle(style); + } +</source> + + </section> + <anchor id="CustomColors"/> + <section><title>Custom colors</title> + <p><strong>HSSF:</strong></p> + <source> + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell(0); + cell.setCellValue("Default Palette"); + + //apply some colors from the standard palette, + // as in the previous examples. + //we'll use red text on a lime background + + HSSFCellStyle style = wb.createCellStyle(); + style.setFillForegroundColor(HSSFColor.LIME.index); + style.setFillPattern(FillPatternType.SOLID_FOREGROUND); + + HSSFFont font = wb.createFont(); + font.setColor(HSSFColor.RED.index); + style.setFont(font); + + cell.setCellStyle(style); + + //save with the default palette + try (OutputStream out = new FileOutputStream("default_palette.xls")) { + wb.write(out); + } + + //now, let's replace RED and LIME in the palette + // with a more attractive combination + // (lovingly borrowed from freebsd.org) + + cell.setCellValue("Modified Palette"); + + //creating a custom palette for the workbook + HSSFPalette palette = wb.getCustomPalette(); + + //replacing the standard red with freebsd.org red + palette.setColorAtIndex(HSSFColor.RED.index, + (byte) 153, //RGB red (0-255) + (byte) 0, //RGB green + (byte) 0 //RGB blue + ); + //replacing lime with freebsd.org gold + palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); + + //save with the modified palette + // note that wherever we have previously used RED or LIME, the + // new colors magically appear + try (out = new FileOutputStream("modified_palette.xls")) { + wb.write(out); + } + </source> + <p><strong>XSSF:</strong></p> + <source> + XSSFWorkbook wb = new XSSFWorkbook(); + XSSFSheet sheet = wb.createSheet(); + XSSFRow row = sheet.createRow(0); + XSSFCell cell = row.createCell( 0); + cell.setCellValue("custom XSSF colors"); + + XSSFCellStyle style1 = wb.createCellStyle(); + style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap())); + style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); + </source> + </section> + <anchor id="ReadWriteWorkbook"/> + <section><title>Reading and Rewriting Workbooks</title> + <source> + try (InputStream inp = new FileInputStream("workbook.xls")) { + //InputStream inp = new FileInputStream("workbook.xlsx"); + + Workbook wb = WorkbookFactory.create(inp); + Sheet sheet = wb.getSheetAt(0); + Row row = sheet.getRow(2); + Cell cell = row.getCell(3); + if (cell == null) + cell = row.createCell(3); + cell.setCellType(CellType.STRING); + cell.setCellValue("a test"); + + // Write the output to a file + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + } + </source> + </section> + <anchor id="NewLinesInCells"/> + <section><title>Using newlines in cells</title> + <source> + Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); + Sheet sheet = wb.createSheet(); + + Row row = sheet.createRow(2); + Cell cell = row.createCell(2); + cell.setCellValue("Use \n with word wrap on to create a new line"); + + //to enable newlines you need set a cell styles with wrap=true + CellStyle cs = wb.createCellStyle(); + cs.setWrapText(true); + cell.setCellStyle(cs); + + //increase row height to accommodate two lines of text + row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); + + //adjust column width to fit the content + sheet.autoSizeColumn(2); + + try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="DataFormats"/> + <section><title>Data Formats</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("format sheet"); + CellStyle style; + DataFormat format = wb.createDataFormat(); + Row row; + Cell cell; + int rowNum = 0; + int colNum = 0; + + row = sheet.createRow(rowNum++); + cell = row.createCell(colNum); + cell.setCellValue(11111.25); + style = wb.createCellStyle(); + style.setDataFormat(format.getFormat("0.0")); + cell.setCellStyle(style); + + row = sheet.createRow(rowNum++); + cell = row.createCell(colNum); + cell.setCellValue(11111.25); + style = wb.createCellStyle(); + style.setDataFormat(format.getFormat("#,##0.0000")); + cell.setCellStyle(style); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="FitTo"/> + <section><title>Fit Sheet to One Page</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("format sheet"); + PrintSetup ps = sheet.getPrintSetup(); + + sheet.setAutobreaks(true); + + ps.setFitHeight((short)1); + ps.setFitWidth((short)1); + + + // Create various cells and rows for spreadsheet. + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + <anchor id="PrintArea2"/> + <section><title>Set Print Area</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("Sheet1"); + //sets the print area for the first sheet + wb.setPrintArea(0, "$A$1:$C$2"); + + //Alternatively: + wb.setPrintArea( + 0, //sheet index + 0, //start column + 1, //end column + 0, //start row + 0 //end row + ); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + + <anchor id="FooterPageNumbers"/> + <section><title>Set Page Numbers on Footer</title> + <source> + Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet = wb.createSheet("format sheet"); + Footer footer = sheet.getFooter(); + + footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() ); + + + + // Create various cells and rows for spreadsheet. + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + + wb.close(); + </source> + </section> + + <anchor id="ConvenienceFunctions"/> + <section><title>Using the Convenience Functions</title> + <p> + The convenience functions provide + utility features such as setting borders around merged + regions and changing style attributes without explicitly + creating new styles. + </p> + <source> + Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook() + Sheet sheet1 = wb.createSheet( "new sheet" ); + + // Create a merged region + Row row = sheet1.createRow( 1 ); + Row row2 = sheet1.createRow( 2 ); + Cell cell = row.createCell( 1 ); + cell.setCellValue( "This is a test of merging" ); + CellRangeAddress region = CellRangeAddress.valueOf("B2:E5"); + sheet1.addMergedRegion( region ); + + // Set the border and border colors. + RegionUtil.setBorderBottom( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); + RegionUtil.setBorderTop( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); + RegionUtil.setBorderLeft( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); + RegionUtil.setBorderRight( BorderStyle.MEDIUM_DASHED, region, sheet1, wb ); + RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); + RegionUtil.setTopBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); + RegionUtil.setLeftBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); + RegionUtil.setRightBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb); + + // Shows some usages of HSSFCellUtil + CellStyle style = wb.createCellStyle(); + style.setIndention((short)4); + CellUtil.createCell(row, 8, "This is the value of the cell", style); + Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell"); + CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER); + + // Write out the workbook + try (OutputStream fileOut = new FileOutputStream( "workbook.xls" )) { + wb.write( fileOut ); + } + + wb.close(); + </source> + </section> + + <anchor id="ShiftRows"/> + <section><title>Shift rows up or down on a sheet</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("row sheet"); + + // Create various cells and rows for spreadsheet. + + // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) + sheet.shiftRows(5, 10, -5); + + </source> + </section> + + <anchor id="SelectSheet"/> + <section><title>Set a sheet as selected</title> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("row sheet"); + sheet.setSelected(true); + + </source> + </section> + + <anchor id="Zoom"/> + <section><title>Set the zoom magnification</title> + <p> + The zoom is expressed as a fraction. For example to + express a zoom of 75% use 3 for the numerator and + 4 for the denominator. + </p> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet1 = wb.createSheet("new sheet"); + sheet1.setZoom(75); // 75 percent magnification + </source> + </section> + + <anchor id="Splits"/> + <section><title>Splits and freeze panes</title> + <p> + There are two types of panes you can create; freeze panes and split panes. + </p> + <p> + A freeze pane is split by columns and rows. You create + a freeze pane using the following mechanism: + </p> + <p> + sheet1.createFreezePane( 3, 2, 3, 2 ); + </p> + <p> + The first two parameters are the columns and rows you + wish to split by. The second two parameters indicate + the cells that are visible in the bottom right quadrant. + </p> + <p> + + Split panes appear differently. The split area is + divided into four separate work area's. The split + occurs at the pixel level and the user is able to + adjust the split by dragging it to a new position. + </p> + <p> + + Split panes are created with the following call: + </p> + <p> + sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); + </p> + <p> + + The first parameter is the x position of the split. + This is in 1/20th of a point. A point in this case + seems to equate to a pixel. The second parameter is + the y position of the split. Again in 1/20th of a point. + </p> + <p> + The last parameter indicates which pane currently has + the focus. This will be one of Sheet.PANE_LOWER_LEFT, + PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT. + </p> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet1 = wb.createSheet("new sheet"); + Sheet sheet2 = wb.createSheet("second sheet"); + Sheet sheet3 = wb.createSheet("third sheet"); + Sheet sheet4 = wb.createSheet("fourth sheet"); + + // Freeze just one row + sheet1.createFreezePane( 0, 1, 0, 1 ); + // Freeze just one column + sheet2.createFreezePane( 1, 0, 1, 0 ); + // Freeze the columns and rows (forget about scrolling position of the lower right quadrant). + sheet3.createFreezePane( 2, 2 ); + // Create a split with the lower left side being the active quadrant + sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + + <anchor id="Repeating"/> + <section><title>Repeating rows and columns</title> + <p> + It's possible to set up repeating rows and columns in + your printouts by using the setRepeatingRows() and + setRepeatingColumns() methods in the Sheet class. + </p> + <p> + These methods expect a CellRangeAddress parameter + which specifies the range for the rows or columns to + repeat. + For setRepeatingRows(), it should specify a range of + rows to repeat, with the column part spanning all + columns. + For setRepeatingColumns(), it should specify a range of + columns to repeat, with the row part spanning all + rows. + If the parameter is null, the repeating rows or columns + will be removed. + </p> + <source> + Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet1 = wb.createSheet("Sheet1"); + Sheet sheet2 = wb.createSheet("Sheet2"); + + // Set the rows to repeat from row 4 to 5 on the first sheet. + sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); + // Set the columns to repeat from column A to C on the second sheet + sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="HeaderFooter"/> + <section><title>Headers and Footers</title> + <p> + Example is for headers but applies directly to footers. + </p> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet("new sheet"); + + Header header = sheet.getHeader(); + header.setCenter("Center Header"); + header.setLeft("Left Header"); + header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); + + try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { + wb.write(fileOut); + } + </source> + </section> + <anchor id="XSSFHeaderFooter"/> + <section><title>XSSF Enhancement for Headers and Footers</title> + <p> + Example is for headers but applies directly to footers. Note, the above example for + basic headers and footers applies to XSSF Workbooks as well as HSSF Workbooks. The HSSFHeader + stuff does not work for XSSF Workbooks. + </p> + <p> + XSSF has the ability to handle First page headers and footers, as well as Even/Odd + headers and footers. All Header/Footer Property flags can be handled in XSSF as well. + The odd header and footer is the default header and footer. It is displayed on all + pages that do not display either a first page header or an even page header. That is, + if the Even header/footer does not exist, then the odd header/footer is displayed on + even pages. If the first page header/footer does not exist, then the odd header/footer + is displayed on the first page. If the even/odd property is not set, that is the same as + the even header/footer not existing. If the first page property does not exist, that is + the same as the first page header/footer not existing. + </p> + <source> + Workbook wb = new XSSFWorkbook(); + XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet"); + + // Create a first page header + Header header = sheet.getFirstHeader(); + header.setCenter("Center First Page Header"); + header.setLeft("Left First Page Header"); + header.setRight("Right First Page Header"); + + // Create an even page header + Header header2 = sheet.getEvenHeader(); + header2.setCenter("Center Even Page Header"); + header2.setLeft("Left Even Page Header"); + header2.setRight("Right Even Page Header"); + + // Create an odd page header + Header header3 = sheet.getOddHeader(); + header3.setCenter("Center Odd Page Header"); + header3.setLeft("Left Odd Page Header"); + header3.setRight("Right Odd Page Header"); + + // Set/Remove Header properties + XSSFHeaderProperties prop = sheet.getHeaderFooterProperties(); + prop.setAlignWithMargins(); + prop.scaleWithDoc(); + prop.removeDifferentFirstPage(); // This does not remove first page headers or footers + prop.removeDifferentEvenOdd(); // This does not remove even headers or footers + + try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { + wb.write(fileOut); + } + </source> + </section> + + <anchor id="DrawingShapes"/> + <section><title>Drawing Shapes</title> + <p> + POI supports drawing shapes using the Microsoft Office + drawing tools. Shapes on a sheet are organized in a + hierarchy of groups and and shapes. The top-most shape + is the patriarch. This is not visible on the sheet + at all. To start drawing you need to call <code>createPatriarch</code> + on the <code>HSSFSheet</code> class. This has the + effect erasing any other shape information stored + in that sheet. By default POI will leave shape + records alone in the sheet unless you make a call to + this method. + </p> + <p> + To create a shape you have to go through the following + steps: + </p> + <ol> + <li>Create the patriarch.</li> + <li>Create an anchor to position the shape on the sheet.</li> + <li>Ask the patriarch to create the shape.</li> + <li>Set the shape type (line, oval, rectangle etc...)</li> + <li>Set any other style details concerning the shape. (eg: + line thickness, etc...)</li> + </ol> + <source> + HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); + a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 ); + HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); + shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); + </source> + <p> + Text boxes are created using a different call: + </p> + <source> + HSSFTextbox textbox1 = patriarch.createTextbox( + new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2)); + textbox1.setString(new HSSFRichTextString("This is a test") ); + </source> + <p> + It's possible to use different fonts to style parts of + the text in the textbox. Here's how: + </p> + <source> + HSSFFont font = wb.createFont(); + font.setItalic(true); + font.setUnderline(HSSFFont.U_DOUBLE); + HSSFRichTextString string = new HSSFRichTextString("Woo!!!"); + string.applyFont(2,5,font); + textbox.setString(string ); + </source> + <p> + Just as can be done manually using Excel, it is possible + to group shapes together. This is done by calling + <code>createGroup()</code> and then creating the shapes + using those groups. + </p> + <p> + It's also possible to create groups within groups. + </p> + <warning>Any group you create should contain at least two + other shapes or subgroups.</warning> + <p> + Here's how to create a shape group: + </p> + <source> + // Create a shape group. + HSSFShapeGroup group = patriarch.createGroup( + new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2)); + + // Create a couple of lines in the group. + HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3,3,500,500)); + shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); + ( (HSSFChildAnchor) shape1.getAnchor() ).setAnchor(3,3,500,500); + HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor(1,200,400,600)); + shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); + </source> + <p> + If you're being observant you'll noticed that the shapes + that are added to the group use a new type of anchor: + the <code>HSSFChildAnchor</code>. What happens is that + the created group has its own coordinate space for + shapes that are placed into it. POI defaults this to + (0,0,1023,255) but you are able to change it as desired. + Here's how: + </p> + <source> + myGroup.setCoordinates(10,10,20,20); // top-left, bottom-right + </source> + <p> + If you create a group within a group it's also going + to have its own coordinate space. + </p> + </section> + + <anchor id="StylingShapes"/> + <section><title>Styling Shapes</title> + <p> + By default shapes can look a little plain. It's possible + to apply different styles to the shapes however. The + sorts of things that can currently be done are: + </p> + <ul> + <li>Change the fill color.</li> + <li>Make a shape with no fill color.</li> + <li>Change the thickness of the lines.</li> + <li>Change the style of the lines. Eg: dashed, dotted.</li> + <li>Change the line color.</li> + </ul> + <p> + Here's an examples of how this is done: + </p> + <source> + HSSFSimpleShape s = patriarch.createSimpleShape(a); + s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL); + s.setLineStyleColor(10,10,10); + s.setFillColor(90,10,200); + s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3); + s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS); + </source> + </section> + <anchor id="Graphics2d"/> + <section><title>Shapes and Graphics2d</title> + <p> + While the native POI shape drawing commands are the + recommended way to draw shapes in a shape it's sometimes + desirable to use a standard API for compatibility with + external libraries. With this in mind we created some + wrappers for <code>Graphics</code> and <code>Graphics2d</code>. + </p> + <warning> + It's important to not however before continuing that + <code>Graphics2d</code> is a poor match to the capabilities + of the Microsoft Office drawing commands. The older + <code>Graphics</code> class offers a closer match but is + still a square peg in a round hole. + </warning> + <p> + All Graphics commands are issued into an <code>HSSFShapeGroup</code>. + Here's how it's done: + </p> + <source> + a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 ); + group = patriarch.createGroup( a ); + group.setCoordinates( 0, 0, 80 * 4 , 12 * 23 ); + float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / (float)Math.abs(group.getY2() - group.getY1()); + g = new EscherGraphics( group, wb, Color.black, verticalPointsPerPixel ); + g2d = new EscherGraphics2d( g ); + drawChemicalStructure( g2d ); + </source> + <p> + The first thing we do is create the group and set its coordinates + to match what we plan to draw. Next we calculate a reasonable + fontSizeMultiplier then create the EscherGraphics object. + Since what we really want is a <code>Graphics2d</code> + object we create an EscherGraphics2d object and pass in + the graphics object we created. Finally we call a routine + that draws into the EscherGraphics2d object. + </p> + <p> + The vertical points per pixel deserves some more explanation. + One of the difficulties in converting Graphics calls + into escher drawing calls is that Excel does not have + the concept of absolute pixel positions. It measures + its cell widths in 'characters' and the cell heights in points. + Unfortunately it's not defined exactly what type of character it's + measuring. Presumably this is due to the fact that the Excel will be + using different fonts on different platforms or even within the same + platform. + </p> + <p> + Because of this constraint we've had to implement the concept of a + verticalPointsPerPixel. This the amount the font should be scaled by when + you issue commands such as drawString(). To calculate this value + use the follow formula: + </p> + <source> + multipler = groupHeightInPoints / heightOfGroup + </source> + <p> + The height of the group is calculated fairly simply by calculating the + difference between the y coordinates of the bounding box of the shape. The + height of the group can be calculated by using a convenience called + <code>HSSFClientAnchor.getAnchorHeightInPoints()</code>. + </p> + <p> + Many of the functions supported by the graphics classes + are not complete. Here's some of the functions that are known + to work. + </p> + <ul> + <li>fillRect()</li> + <li>fillOval()</li> + <li>drawString()</li> + <li>drawOval()</li> + <li>drawLine()</li> + <li>clearRect()</li> + </ul> + <p> + Functions that are not supported will return and log a message + using the POI logging infrastructure (disabled by default). + </p> + </section> + <anchor id="Outlining"/> + <section> + <title>Outlining</title> + <p> + Outlines are great for grouping sections of information + together and can be added easily to columns and rows + using the POI API. Here's how: + </p> + <source> + Workbook wb = new HSSFWorkbook(); + Sheet sheet1 = wb.createSheet("new sheet"); + + sheet1.groupRow( 5, 14 ); + sheet1.groupRow( 7, 14 ); + sheet1.groupRow( 16, 19 ); + + sheet1.groupColumn( 4, 7 ); + sheet1.groupColumn( 9, 12 ); + sheet1.groupColumn( 10, 11 ); + + try (OutputStream fileOut = new FileOutputStream(filename)) { + wb.write(fileOut); + } + </source> + <p> + To collapse (or expand) an outline use the following calls: + </p> + <source> + sheet1.setRowGroupCollapsed( 7, true ); + sheet1.setColumnGroupCollapsed( 4, true ); + </source> + <p> + The row/column you choose should contain an already + created group. It can be anywhere within the group. + </p> + </section> + </section> + </section> + <anchor id="Images"/> + <section> + <title>Images</title> + <p> + Images are part of the drawing support. To add an image just + call <code>createPicture()</code> on the drawing patriarch. + At the time of writing the following types are supported: + </p> + <ul> + <li>PNG</li> + <li>JPG</li> + <li>DIB</li> + </ul> + <p> + It should be noted that any existing drawings may be erased + once you add an image to a sheet. + </p> + <source> + //create a new workbook + Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); + + //add picture data to this workbook. + InputStream is = new FileInputStream("image1.jpeg"); + byte[] bytes = IOUtils.toByteArray(is); + int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); + is.close(); + + CreationHelper helper = wb.getCreationHelper(); + + //create sheet + Sheet sheet = wb.createSheet(); + + // Create the drawing patriarch. This is the top level container for all shapes. + Drawing drawing = sheet.createDrawingPatriarch(); + + //add a picture shape + ClientAnchor anchor = helper.createClientAnchor(); + //set top-left corner of the picture, + //subsequent call of Picture#resize() will operate relative to it + anchor.setCol1(3); + anchor.setRow1(2); + Picture pict = drawing.createPicture(anchor, pictureIdx); + + //auto-size picture relative to its top-left corner + pict.resize(); + + //save workbook + String file = "picture.xls"; + if(wb instanceof XSSFWorkbook) file += "x"; + try (OutputStream fileOut = new FileOutputStream(file)) { + wb.write(fileOut); + } + </source> + <warning> + Picture.resize() works only for JPEG and PNG. Other formats are not yet supported. + </warning> + <p>Reading images from a workbook:</p> + <source> + + List lst = workbook.getAllPictures(); + for (Iterator it = lst.iterator(); it.hasNext(); ) { + PictureData pict = (PictureData)it.next(); + String ext = pict.suggestFileExtension(); + byte[] data = pict.getData(); + if (ext.equals("jpeg")){ + try (OutputStream out = new FileOutputStream("pict.jpg")) { + out.write(data); + } + } + } + </source> + </section> + <anchor id="NamedRanges"/> + <section> + <title>Named Ranges and Named Cells</title> + <p> + Named Range is a way to refer to a group of cells by a name. Named Cell is a + degenerate case of Named Range in that the 'group of cells' contains exactly one + cell. You can create as well as refer to cells in a workbook by their named range. + When working with Named Ranges, the classes <code>org.apache.poi.ss.util.CellReference</code> + and <code>org.apache.poi.ss.util.AreaReference</code> are used. + </p> + <p> + Note: Using relative values like 'A1:B1' can lead to unexpected moving of + the cell that the name points to when working with the workbook in Microsoft Excel, + usually using absolute references like '$A$1:$B$1' avoids this, see also + <a href="https://superuser.com/a/1031047/126954">this discussion</a>. + </p> + <p> + Creating Named Range / Named Cell + </p> + <source> + // setup code + String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet(sname); + sheet.createRow(0).createCell(0).setCellValue(cvalue); + + // 1. create named range for a single cell using areareference + Name namedCell = wb.createName(); + namedCell.setNameName(cname + "1"); + String reference = sname+"!$A$1:$A$1"; // area reference + namedCell.setRefersToFormula(reference); + + // 2. create named range for a single cell using cellreference + Name namedCel2 = wb.createName(); + namedCel2.setNameName(cname + "2"); + reference = sname+"!$A$1"; // cell reference + namedCel2.setRefersToFormula(reference); + + // 3. create named range for an area using AreaReference + Name namedCel3 = wb.createName(); + namedCel3.setNameName(cname + "3"); + reference = sname+"!$A$1:$C$5"; // area reference + namedCel3.setRefersToFormula(reference); + + // 4. create named formula + Name namedCel4 = wb.createName(); + namedCel4.setNameName("my_sum"); + namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)"); + </source> + <p> + Reading from Named Range / Named Cell + </p> + <source> + // setup code + String cname = "TestName"; + Workbook wb = getMyWorkbook(); // retrieve workbook + + // retrieve the named range + int namedCellIdx = wb.getNameIndex(cellName); + Name aNamedCell = wb.getNameAt(namedCellIdx); + + // retrieve the cell at the named range and test its contents + AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); + CellReference[] crefs = aref.getAllReferencedCells(); + for (int i=0; i<crefs.length; i++) { + Sheet s = wb.getSheet(crefs[i].getSheetName()); + Row r = sheet.getRow(crefs[i].getRow()); + Cell c = r.getCell(crefs[i].getCol()); + // extract the cell contents based on cell type etc. + } + </source> + <p> + Reading from non-contiguous Named Ranges + </p> + <source> + // Setup code + String cname = "TestName"; + Workbook wb = getMyWorkbook(); // retrieve workbook + + // Retrieve the named range + // Will be something like "$C$10,$D$12:$D$14"; + int namedCellIdx = wb.getNameIndex(cellName); + Name aNamedCell = wb.getNameAt(namedCellIdx); + + // Retrieve the cell at the named range and test its contents + // Will get back one AreaReference for C10, and + // another for D12 to D14 + AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula()); + for (int i=0; i<arefs.length; i++) { + // Only get the corners of the Area + // (use arefs[i].getAllReferencedCells() to get all cells) + CellReference[] crefs = arefs[i].getCells(); + for (int j=0; j<crefs.length; j++) { + // Check it turns into real stuff + Sheet s = wb.getSheet(crefs[j].getSheetName()); + Row r = s.getRow(crefs[j].getRow()); + Cell c = r.getCell(crefs[j].getCol()); + // Do something with this corner cell + } + } + </source> + <p> + Note, when a cell is deleted, Excel does not delete the + attached named range. As result, workbook can contain + named ranges that point to cells that no longer exist. + You should check the validity of a reference before + constructing AreaReference + </p> + <source> + if(name.isDeleted()){ + //named range points to a deleted cell. + } else { + AreaReference ref = new AreaReference(name.getRefersToFormula()); + } + </source> + </section> + <anchor id="CellComments"/> + <section><title>Cell Comments - HSSF and XSSF</title> + <p> + A comment is a rich text note that is attached to & + associated with a cell, separate from other cell content. + Comment content is stored separate from the cell, and is displayed in a drawing object (like a text box) + that is separate from, but associated with, a cell + </p> + <source> + Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); + + CreationHelper factory = wb.getCreationHelper(); + + Sheet sheet = wb.createSheet(); + + Row row = sheet.createRow(3); + Cell cell = row.createCell(5); + cell.setCellValue("F4"); + + Drawing drawing = sheet.createDrawingPatriarch(); + + // When the comment box is visible, have it show in a 1x3 space + ClientAnchor anchor = factory.createClientAnchor(); + anchor.setCol1(cell.getColumnIndex()); + anchor.setCol2(cell.getColumnIndex()+1); + anchor.setRow1(row.getRowNum()); + anchor.setRow2(row.getRowNum()+3); + + // Create the comment and set the text+author + Comment comment = drawing.createCellComment(anchor); + RichTextString str = factory.createRichTextString("Hello, World!"); + comment.setString(str); + comment.setAuthor("Apache POI"); + + // Assign the comment to the cell + cell.setCellComment(comment); + + String fname = "comment-xssf.xls"; + if(wb instanceof XSSFWorkbook) fname += "x"; + try (OutputStream out = new FileOutputStream(fname)) { + wb.write(out); + } + + wb.close(); + </source> + <p> + Reading cell comments + </p> + <source> + Cell cell = sheet.get(3).getColumn(1); + Comment comment = cell.getCellComment(); + if (comment != null) { + RichTextString str = comment.getString(); + String author = comment.getAuthor(); + } + // alternatively you can retrieve cell comments by (row, column) + comment = sheet.getCellComment(3, 1); + </source> + + <p>To get all the comments on a sheet:</p> + <source> + Map<CellAddress, Comment> comments = sheet.getCellComments(); + Comment commentA1 = comments.get(new CellAddress(0, 0)); + Comment commentB1 = comments.get(new CellAddress(0, 1)); + for (Entry<CellAddress, ? extends Comment> e : comments.entrySet()) { + CellAddress loc = e.getKey(); + Comment comment = e.getValue(); + System.out.println("Comment at " + loc + ": " + + "[" + comment.getAuthor() + "] " + comment.getString().getString()); + } + </source> + </section> + + <anchor id="Autofit"/> + <section><title>Adjust column width to fit the contents</title> + <source> + Sheet sheet = workbook.getSheetAt(0); + sheet.autoSizeColumn(0); //adjust width of the first column + sheet.autoSizeColumn(1); //adjust width of the second column + </source> + <p> + For SXSSFWorkbooks only, because the random access window is likely to exclude most of the rows + in the worksheet, which are needed for computing the best-fit width of a column, the columns must + be tracked for auto-sizing prior to flushing any rows. + </p> + <source> + SXSSFWorkbook workbook = new SXSSFWorkbook(); + SXSSFSheet sheet = workbook.createSheet(); + sheet.trackColumnForAutoSizing(0); + sheet.trackColumnForAutoSizing(1); + // If you have a Collection of column indices, see SXSSFSheet#trackColumnForAutoSizing(Collection<Integer>) + // or roll your own for-loop. + // Alternatively, use SXSSFSheet#trackAllColumnsForAutoSizing() if the columns that will be auto-sized aren't + // known in advance or you are upgrading existing code and are trying to minimize changes. Keep in mind + // that tracking all columns will require more memory and CPU cycles, as the best-fit width is calculated + // on all tracked columns on every row that is flushed. + + // create some cells + for (int r=0; r < 10; r++) { + Row row = sheet.createRow(r); + for (int c; c < 10; c++) { + Cell cell = row.createCell(c); + cell.setCellValue("Cell " + c.getAddress().formatAsString()); + } + } + + // Auto-size the columns. + sheet.autoSizeColumn(0); + sheet.autoSizeColumn(1); + </source> + <p> + Note, that Sheet#autoSizeColumn() does not evaluate formula cells, + the width of formula cells is calculated based on the cached formula result. + If your workbook has many formulas then it is a good idea to evaluate them before auto-sizing. + </p> + <warning> + To calculate column width Sheet.autoSizeColumn uses Java2D classes + that throw exception if graphical environment is not available. In case if graphical environment + is not available, you must tell Java that you are running in headless mode and + set the following system property: <code> java.awt.headless=true </code>. + You should also ensure that the fonts you use in your workbook are + available to Java. + </warning> + </section> + <anchor id="Hyperlinks"/> + <section><title>How to read hyperlinks</title> + <source> + Sheet sheet = workbook.getSheetAt(0); + + Cell cell = sheet.getRow(0).getCell(0); + Hyperlink link = cell.getHyperlink(); + if(link != null){ + System.out.println(link.getAddress()); + } + </source> + </section> + <section><title>How to create hyperlinks</title> + <source> + Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); + CreationHelper createHelper = wb.getCreationHelper(); + + //cell style for hyperlinks + //by default hyperlinks are blue and underlined + CellStyle hlink_style = wb.createCellStyle(); + Font hlink_font = wb.createFont(); + hlink_font.setUnderline(Font.U_SINGLE); + hlink_font.setColor(IndexedColors.BLUE.getIndex()); + hlink_style.setFont(hlink_font); + + Cell cell; + Sheet sheet = wb.createSheet("Hyperlinks"); + //URL + cell = sheet.createRow(0).createCell(0); + cell.setCellValue("URL Link"); + + Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL); + link.setAddress("https://poi.apache.org/"); + cell.setHyperlink(link); + cell.setCellStyle(hlink_style); + + //link to a file in the current directory + cell = sheet.createRow(1).createCell(0); + cell.setCellValue("File Link"); + link = createHelper.createHyperlink(HyperlinkType.FILE); + link.setAddress("link1.xls"); + cell.setHyperlink(link); + cell.setCellStyle(hlink_style); + + //e-mail link + cell = sheet.createRow(2).createCell(0); + cell.setCellValue("Email Link"); + link = createHelper.createHyperlink(HyperlinkType.EMAIL); + //note, if subject contains white spaces, make sure they are url-encoded + link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); + cell.setHyperlink(link); + cell.setCellStyle(hlink_style); + + //link to a place in this workbook + + //create a target sheet and cell + Sheet sheet2 = wb.createSheet("Target Sheet"); + sheet2.createRow(0).createCell(0).setCellValue("Target Cell"); + + cell = sheet.createRow(3).createCell(0); + cell.setCellValue("Worksheet Link"); + Hyperlink link2 = createHelper.createHyperlink(HyperlinkType.DOCUMENT); + link2.setAddress("'Target Sheet'!A1"); + cell.setHyperlink(link2); + cell.setCellStyle(hlink_style); + + try (OutputStream out = new FileOutputStream("hyperinks.xlsx")) { + wb.write(out); + } + + wb.close(); + </source> + </section> + <anchor id="Validation"/> + <section><title>Data Validations</title> + <p> + As of version 3.8, POI has slightly different syntax to work with data validations with .xls and .xlsx formats. + </p> + <section> + <title>hssf.usermodel (binary .xls format)</title> + <p><strong>Check the value a user enters into a cell against one or more predefined value(s).</strong></p> + <p>The following code will limit the value the user can enter into cell A1 to one of three integer values, 10, 20 or 30.</p> + <source> + HSSFWorkbook workbook = new HSSFWorkbook(); + HSSFSheet sheet = workbook.createSheet("Data Validation"); + CellRangeAddressList addressList = new CellRangeAddressList( + 0, 0, 0, 0); + DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( + new String[]{"10", "20", "30"}); + DataValidation dataValidation = new HSSFDataValidation + (addressList, dvConstraint); + dataValidation.setSuppressDropDownArrow(true); + sheet.addValidationData(dataValidation); + </source> + <p><strong> Drop Down Lists:</strong></p> + <p>This code will do the same but offer the user a drop down list to select a value from.</p> + <source> + HSSFWorkbook workbook = new HSSFWorkbook(); + HSSFSheet sheet = workbook.createSheet("Data Validation"); + CellRangeAddressList addressList = new CellRangeAddressList( + 0, 0, 0, 0); + DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( + new String[]{"10", "20", "30"}); + DataValidation dataValidation = new HSSFDataValidation + (addressList, dvConstraint); + dataValidation.setSuppressDropDownArrow(false); + sheet.addValidationData(dataValidation); + </source> + <p><strong>Messages On Error:</strong></p> + <p>To create a message box that will be shown to the user if the value they enter is invalid.</p> + <source> + dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP); + dataValidation.createErrorBox("Box Title", "Message Text"); + </source> + <p>Replace 'Box Title' with the text you wish to display in the message box's title bar + and 'Message Text' with the text of your error message.</p> + <p><strong>Prompts:</strong></p> + <p>To create a prompt that the user will see when the cell containing the data validation receives focus</p> + <source> + dataValidation.createPromptBox("Title", "Message Text"); + dataValidation.setShowPromptBox(true); + </source> + <p>The text encapsulated in the first parameter passed to the createPromptBox() method will appear emboldened + and as a title to the prompt whilst the second will be displayed as the text of the message. + The createExplicitListConstraint() method can be passed and array of String(s) containing interger, floating point, dates or text values.</p> + + <p><strong>Further Data Validations:</strong></p> + <p>To obtain a validation that would check the value entered was, for example, an integer between 10 and 100, + use the DVConstraint.createNumericConstraint(int, int, String, String) factory method.</p> + <source> + dvConstraint = DVConstraint.createNumericConstraint( + DVConstraint.ValidationType.INTEGER, + DVConstraint.OperatorType.BETWEEN, "10", "100"); + </source> + <p>Look at the javadoc for the other validation and operator types; also note that not all validation + types are supported for this method. The values passed to the two String parameters can be formulas; the '=' symbol is used to denote a formula</p> + <source> + dvConstraint = DVConstraint.createNumericConstraint( + DVConstraint.ValidationType.INTEGER, + DVConstraint.OperatorType.BETWEEN, "=SUM(A1:A3)", "100"); + </source> + <p>It is not possible to create a drop down list if the createNumericConstraint() method is called, + the setSuppressDropDownArrow(false) method call will simply be ignored.</p> + <p>Date and time constraints can be created by calling the createDateConstraint(int, String, String, String) + or the createTimeConstraint(int, String, String). Both are very similar to the above and are explained in the javadoc. </p> + <p><strong>Creating Data Validations From Spreadsheet Cells.</strong></p> + <p>The contents of specific cells can be used to provide the values for the data validation + and the DVConstraint.createFormulaListConstraint(String) method supports this. + To specify that the values come from a contiguous range of cells do either of the following:</p> + <source> + dvConstraint = DVConstraint.createFormulaListConstraint("$A$1:$A$3"); + </source> + <p>or</p> + <source> + Name namedRange = workbook.createName(); + namedRange.setNameName("list1"); + namedRange.setRefersToFormula("$A$1:$A$3"); + dvConstraint = DVConstraint.createFormulaListConstraint("list1"); + </source> + <p>and in both cases the user will be able to select from a drop down list containing the values from cells A1, A2 and A3.</p> + <p>The data does not have to be as the data validation. To select the data from a different sheet however, the sheet + must be given a name when created and that name should be used in the formula. So assuming the existence of a sheet named 'Data Sheet' this will work:</p> + <source> + Name namedRange = workbook.createName(); + namedRange.setNameName("list1"); + namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$3"); + dvConstraint = DVConstraint.createFormulaListConstraint("list1"); + </source> + <p>as will this:</p> + <source> + dvConstraint = DVConstraint.createFormulaListConstraint("'Data Sheet'!$A$1:$A$3"); + </source> + <p>whilst this will not:</p> + <source> + Name namedRange = workbook.createName(); + namedRange.setNameName("list1"); + namedRange.setRefersToFormula("'Sheet1'!$A$1:$A$3"); + dvConstraint = DVConstraint.createFormulaListConstraint("list1"); + </source><p>and nor will this:</p><source> + dvConstraint = DVConstraint.createFormulaListConstraint("'Sheet1'!$A$1:$A$3"); + </source> + </section> + <section> + <title>xssf.usermodel (.xlsx format)</title> +<p> +Data validations work similarly when you are creating an xml based, SpreadsheetML, +workbook file; but there are differences. Explicit casts are required, for example, +in a few places as much of the support for data validations in the xssf stream was +built into the unifying ss stream, of which more later. Other differences are +noted with comments in the code. +</p> + +<p><strong>Check the value the user enters into a cell against one or more predefined value(s).</strong></p> +<source> + XSSFWorkbook workbook = new XSSFWorkbook(); + XSSFSheet sheet = workbook.createSheet("Data Validation"); + XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); + XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"}); + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); + XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation( + dvConstraint, addressList); + + // Here the boolean value false is passed to the setSuppressDropDownArrow() + // method. In the hssf.usermodel examples above, the value passed to this + // method is true. + validation.setSuppressDropDownArrow(false); + + // Note this extra method call. If this method call is omitted, or if the + // boolean value false is passed, then Excel will not validate the value the + // user enters into the cell. + validation.setShowErrorBox(true); + sheet.addValidationData(validation); +</source> + +<p><strong>Drop Down Lists:</strong></p> +<p>This code will do the same but offer the user a drop down list to select a value from.</p> +<source> + XSSFWorkbook workbook = new XSSFWorkbook(); + XSSFSheet sheet = workbook.createSheet("Data Validation"); + XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); + XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"}); + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); + XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation( + dvConstraint, addressList); + validation.setShowErrorBox(true); + sheet.addValidationData(validation); +</source> +<p>Note that the call to the setSuppressDropDowmArrow() method can either be simply excluded or replaced with:</p> +<source> + validation.setSuppressDropDownArrow(true); +</source> + +<p><strong>Prompts and Error Messages:</strong></p> +<p> +These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above. +</p> + +<p><strong>Further Data Validations:</strong></p> +<p> +To obtain a validation that would check the value entered was, for example, +an integer between 10 and 100, use the XSSFDataValidationHelper(s) createNumericConstraint(int, int, String, String) factory method. +</p> +<source> + + XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createNumericConstraint( + XSSFDataValidationConstraint.ValidationType.INTEGER, + XSSFDataValidationConstraint.OperatorType.BETWEEN, + "10", "100"); +</source> +<p> +The values passed to the final two String parameters can be formulas; the '=' symbol is used to denote a formula. +Thus, the following would create a validation the allows values only if they fall between the results of summing two cell ranges +</p> +<source> + XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createNumericConstraint( + XSSFDataValidationConstraint.ValidationType.INTEGER, + XSSFDataValidationConstraint.OperatorType.BETWEEN, + "=SUM(A1:A10)", "=SUM(B24:B27)"); +</source> +<p> +It is not possible to create a drop down list if the createNumericConstraint() method is called, +the setSuppressDropDownArrow(true) method call will simply be ignored. +</p> +<p> +Please check the javadoc for other constraint types as examples for those will not be included here. +There are, for example, methods defined on the XSSFDataValidationHelper class allowing you to create +the following types of constraint; date, time, decimal, integer, numeric, formula, text length and custom constraints. +</p> +<p><strong>Creating Data Validations From Spread Sheet Cells:</strong></p> +<p> +One other type of constraint not mentioned above is the formula list constraint. +It allows you to create a validation that takes it value(s) from a range of cells. This code +</p> +<source> +XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createFormulaListConstraint("$A$1:$F$1"); +</source> + +<p> +would create a validation that took it's values from cells in the range A1 to F1. +</p> +<p> +The usefulness of this technique can be extended if you use named ranges like this; +</p> + +<source> + XSSFName name = workbook.createName(); + name.setNameName("data"); + name.setRefersToFormula("$B$1:$F$1"); + XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); + XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) + dvHelper.createFormulaListConstraint("data"); + CellRangeAddressList addressList = new CellRangeAddressList( + 0, 0, 0, 0); + XSSFDataValidation validation = (XSSFDataValidation) + dvHelper.createValidation(dvConstraint, addressList); + validation.setSuppressDropDownArrow(true); + validation.setShowErrorBox(true); + sheet.addValidationData(validation); +</source> +<p> +OpenOffice Calc has slightly different rules with regard to the scope of names. +Excel supports both Workbook and Sheet scope for a name but Calc does not, it seems only to support Sheet scope for a name. +Thus it is often best to fully qualify the name for the region or area something like this; +</p> +<source> + XSSFName name = workbook.createName(); + name.setNameName("data"); + name.setRefersToFormula("'Data Validation'!$B$1:$F$1"); + .... +</source> +<p> +This does open a further, interesting opportunity however and that is to place all of the data for the validation(s) into named ranges of cells on a hidden sheet within the workbook. These ranges can then be explicitly identified in the setRefersToFormula() method argument. +</p> + </section> + <section><title>ss.usermodel</title> +<p> +The classes within the ss.usermodel package allow developers to create code that can be used +to generate both binary (.xls) and SpreadsheetML (.xlsx) workbooks. +</p> +<p> +The techniques used to create data validations share much in common with the xssf.usermodel examples above. +As a result just one or two examples will be presented here. +</p> +<p><strong>Check the value the user enters into a cell against one or more predefined value(s).</strong></p> +<source> + Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook + Sheet sheet = workbook.createSheet("Data Validation"); + DataValidationHelper dvHelper = sheet.getDataValidationHelper(); + DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint( + new String[]{"13", "23", "33"}); + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); + DataValidation validation = dvHelper.createValidation( + dvConstraint, addressList); + // Note the check on the actual type of the DataValidation object. + // If it is an instance of the XSSFDataValidation class then the + // boolean value 'false' must be passed to the setSuppressDropDownArrow() + // method and an explicit call made to the setShowErrorBox() method. + if(validation instanceof XSSFDataValidation) { + validation.setSuppressDropDownArrow(false); + validation.setShowErrorBox(true); + } + else { + // If the Datavalidation contains an instance of the HSSFDataValidation + // class then 'true' should be passed to the setSuppressDropDownArrow() + // method and the call to setShowErrorBox() is not necessary. + validation.setSuppressDropDownArrow(true); + } + sheet.addValidationData(validation); +</source> + +<p><strong>Drop Down Lists:</strong></p> + +<p>This code will do the same but offer the user a drop down list to select a value from.</p> + +<source> + Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook + Sheet sheet = workbook.createSheet("Data Validation"); + DataValidationHelper dvHelper = sheet.getDataValidationHelper(); + DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint( + new String[]{"13", "23", "33"}); + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); + DataValidation validation = dvHelper.createValidation( + dvConstraint, addressList); + // Note the check on the actual type of the DataValidation object. + // If it is an instance of the XSSFDataValidation class then the + // boolean value 'false' must be passed to the setSuppressDropDownArrow() + // method and an explicit call made to the setShowErrorBox() method. + if(validation instanceof XSSFDataValidation) { + validation.setSuppressDropDownArrow(true); + validation.setShowErrorBox(true); + } + else { + // If the Datavalidation contains an instance of the HSSFDataValidation + // class then 'true' should be passed to the setSuppressDropDownArrow() + // method and the call to setShowErrorBox() is not necessary. + validation.setSuppressDropDownArrow(false); + } + sheet.addValidationData(validation); +</source> + +<p><strong>Prompts and Error Messages:</strong></p> +<p> +These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above. +</p> +<p> +As the differences between the ss.usermodel and xssf.usermodel examples are small - +restricted largely to the way the DataValidationHelper is obtained, the lack of any +need to explicitly cast data types and the small difference in behaviour between +the hssf and xssf interpretation of the setSuppressDropDowmArrow() method, +no further examples will be included in this section. +</p> +<p><strong>Advanced Data Validations.</strong></p> +<p><strong>Dependent Drop Down Lists.</strong></p> +<p> +In some cases, it may be necessary to present to the user a sheet which contains more than one drop down list. +Further, the choice the user makes in one drop down list may affect the options that are presented to them in +the second or subsequent drop down lists. One technique that may be used to implement this behaviour will now be explained. +</p> +<p> +There are two keys to the technique; one is to use named areas or regions of cells to hold the data for the drop down lists, +the second is to use the INDIRECT() function to convert between the name and the actual addresses of the cells. +In the example section there is a complete working example- called LinkedDropDownLists.java - +that demonstrates how to create linked or dependent drop down lists. Only the more relevant points are explained here. +</p> +<p> +To create two drop down lists where the options shown in the second depend upon the selection made in the first, +begin by creating a named region of cells to hold all of the data for populating the first drop down list. +Next, create a data validation that will look to this named area for its data, something like this; +</p> +<source> + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); + DataValidationHelper dvHelper = sheet.getDataValidationHelper(); + DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint( + "CHOICES"); + DataValidation validation = dvHelper.createValidation( + dvConstraint, addressList); + sheet.addValidationData(validation); +</source> +<p> +Note that the name of the area - in the example above it is 'CHOICES' - +is simply passed to the createFormulaListConstraint() method. This is sufficient +to cause Excel to populate the drop down list with data from that named region. +</p> +<p> +Next, for each of the options the user could select in the first drop down list, +create a matching named region of cells. The name of that region should match the +text the user could select in the first drop down list. Note, in the example, +all upper case letters are used in the names of the regions of cells. +</p> + +<p> +Now, very similar code can be used to create a second, linked, drop down list; +</p> + +<source> + CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1); + DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint( + "INDIRECT(UPPER($A$1))"); + DataValidation validation = dvHelper.createValidation( + dvConstraint, addressList); + sheet.addValidationData(validation); +</source> + +<p> +The key here is in the following Excel function - INDIRECT(UPPER($A$1)) - which is used to populate the second, +linked, drop down list. Working from the inner-most pair of brackets, it instructs Excel to look +at the contents of cell A1, to convert what it reads there into upper case – as upper case letters are used +in the names of each region - and then convert this name into the addresses of those cells that contain +the data to populate another drop down list. +</p> + </section> + </section> + <anchor id="Embedded"/> + <section><title>Embedded Objects</title> + <p>It is possible to perform more detailed processing of an embedded Excel, Word or PowerPoint document, + or to work with any other type of embedded object.</p> + <p><strong>HSSF:</strong></p> + <source> + POIFSFileSystem fs = new POIFSFileSystem(new File("excel_with_embedded.xls")); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) { + //the OLE2 Class Name of the object + String oleName = obj.getOLE2ClassName(); + if (oleName.equals("Worksheet")) { + DirectoryNode dn = (DirectoryNode) obj.getDirectory(); + HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, false); + //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets()); + } else if (oleName.equals("Document")) { + DirectoryNode dn = (DirectoryNode) obj.getDirectory(); + HWPFDocument embeddedWordDocument = new HWPFDocument(dn); + //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text()); + } else if (oleName.equals("Presentation")) { + DirectoryNode dn = (DirectoryNode) obj.getDirectory(); + SlideShow<?,?> embeddedPowerPointDocument = new HSLFSlideShow(dn); + //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length); + } else { + if(obj.hasDirectoryEntry()){ + // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is + DirectoryNode dn = (DirectoryNode) obj.getDirectory(); + for (Entry entry : dn) { + //System.out.println(oleName + "." + entry.getName()); + } + } else { + // There is no DirectoryEntry + // Recover the object's data from the HSSFObjectData instance. + byte[] objectData = obj.getObjectData(); + } + } + } + </source> + <p><strong>XSSF:</strong></p> + <source> + XSSFWorkbook workbook = new XSSFWorkbook("excel_with_embeded.xlsx"); + for (PackagePart pPart : workbook.getAllEmbeddedParts()) { + String contentType = pPart.getContentType(); + // Excel Workbook - either binary or OpenXML + if (contentType.equals("application/vnd.ms-excel")) { + HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream()); + } + // Excel Workbook - OpenXML file format + else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) { + OPCPackage docPackage = OPCPackage.open(pPart.getInputStream()); + XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(docPackage); + } + // Word Document - binary (OLE2CDF) file format + else if (contentType.equals("application/msword")) { + HWPFDocument document = new HWPFDocument(pPart.getInputStream()); + } + // Word Document - OpenXML file format + else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) { + OPCPackage docPackage = OPCPackage.open(pPart.getInputStream()); + XWPFDocument document = new XWPFDocument(docPackage); + } + // PowerPoint Document - binary file format + else if (contentType.equals("application/vnd.ms-powerpoint")) { + HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream()); + } + // PowerPoint Document - OpenXML file format + else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) { + OPCPackage docPackage = OPCPackage.open(pPart.getInputStream()); + XSLFSlideShow slideShow = new XSLFSlideShow(docPackage); + } + // Any other type of embedded object. + else { + System.out.println("Unknown Embedded Document: " + contentType); + InputStream inputStream = pPart.getInputStream(); + } + } + </source> + </section> + <anchor id="Autofilter"/> + <p>(Since POI-3.7)</p> + <section><title>Autofilters</title> + <source> + Workbook wb = new HSSFWorkbook(); //or new XSSFWorkbook(); + Sheet sheet = wb.createSheet(); + sheet.setAutoFilter(CellRangeAddress.valueOf("C5:F200")); + </source> + </section> + <anchor id="ConditionalFormatting"/> + <section><title>Conditional Formatting</title> + <source> + Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet = workbook.createSheet(); + + SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); + + ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0"); + FontFormatting fontFmt = rule1.createFontFormatting(); + fontFmt.setFontStyle(true, false); + fontFmt.setFontColorIndex(IndexedColors.DARK_RED.index); + + BorderFormatting bordFmt = rule1.createBorderFormatting(); + bordFmt.setBorderBottom(BorderStyle.THIN); + bordFmt.setBorderTop(BorderStyle.THICK); + bordFmt.setBorderLeft(BorderStyle.DASHED); + bordFmt.setBorderRight(BorderStyle.DOTTED); + + PatternFormatting patternFmt = rule1.createPatternFormatting(); + patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index); + + ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "-10", "10"); + ConditionalFormattingRule [] cfRules = + { + rule1, rule2 + }; + + CellRangeAddress[] regions = { + CellRangeAddress.valueOf("A3:A5") + }; + + sheetCF.addConditionalFormatting(regions, cfRules); + </source> + <p> See more examples on Excel conditional formatting in + <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/ConditionalFormats.java">ConditionalFormats.java</a> + </p> + + </section> + <anchor id="Hiding"/> + <section><title>Hiding and Un-Hiding Rows</title> + <p> + Using Excel, it is possible to hide a row on a worksheet by selecting that row (or rows), + right clicking once on the right hand mouse button and selecting 'Hide' from the pop-up menu that appears. + </p> + <p> + To emulate this using POI, simply call the setZeroHeight() method on an instance of either + XSSFRow or HSSFRow (the method is defined on the ss.usermodel.Row interface that both classes implement), like this: + </p> + <source> + Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook() + Sheet sheet = workbook.createSheet(0); + Row row = workbook.createRow(0); + row.setZeroHeight(); + </source> + <p> + If the file were saved away to disc now, then the first row on the first sheet would not be visible. + </p> + <p> + Using Excel, it is possible to unhide previously hidden rows by selecting the row above and the row below + the one that is hidden and then pressing and holding down the Ctrl key, the Shift and the pressing + the number 9 before releasing them all. + </p> + <p> + To emulate this behaviour using POI do something like this: + </p> + <source> + Workbook workbook = WorkbookFactory.create(new File(.......)); + Sheet = workbook.getSheetAt(0); + Iterator<Row> row Iter = sheet.iterator(); + while(rowIter.hasNext()) { + Row row = rowIter.next(); + if(row.getZeroHeight()) { + row.setZeroHeight(false); + } + } + </source> + <p> + If the file were saved away to disc now, any previously hidden rows on the first sheet of the workbook would now be visible. + </p> + <p> + The example illustrates two features. Firstly, that it is possible to unhide a row simply by calling the setZeroHeight() + method and passing the boolean value 'false'. Secondly, it illustrates how to test whether a row is hidden or not. + Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise. + </p> + </section> + <anchor id="CellProperties"/> + <section><title>Setting Cell Properties</title> + <p> + Sometimes it is easier or more efficient to create a spreadsheet with basic styles and then apply special styles to certain cells + such as drawing borders around a range of cells or setting fills for a region. CellUtil.setCellProperties lets you do that without creating + a bunch of unnecessary intermediate styles in your spreadsheet. + </p> + <p> + Properties are created as a Map and applied to a cell in the following manner. + </p> + <source> + Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook() + Sheet sheet = workbook.createSheet("Sheet1"); + Map<String, Object> properties = new HashMap<String, Object>(); + + // border around a cell + properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM); + properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM); + properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM); + properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM); + + // Give it a color (RED) + properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex()); + + // Apply the borders to the cell at B2 + Row row = sheet.createRow(1); + Cell cell = row.createCell(1); + CellUtil.setCellStyleProperties(cell, properties); + + // Apply the borders to a 3x3 region starting at D4 + for (int ix=3; ix <= 5; ix++) { + row = sheet.createRow(ix); + for (int iy = 3; iy <= 5; iy++) { + cell = row.createCell(iy); + CellUtil.setCellStyleProperties(cell, properties); + } + } + </source> + + <p> + NOTE: This does not replace the properties of the cell, it merges the properties you have put into the Map with the + cell's existing style properties. If a property already exists, it is replaced with the new property. If a property does not + exist, it is added. This method will not remove CellStyle properties. + </p> + </section> + <anchor id="DrawingBorders"/> + <section> + <title>Drawing Borders</title> + <p> + In Excel, you can apply a set of borders on an entire workbook region at the press of a button. The PropertyTemplate + object simulates this with methods and constants defined to allow drawing top, bottom, left, right, horizontal, + vertical, inside, outside, or all borders around a range of cells. Additional methods allow for applying colors + to the borders. + </p> + <p> + It works like this: you create a PropertyTemplate object which is a container for the borders you wish to apply to a + sheet. Then you add borders and colors to the PropertyTemplate, and finally apply it to whichever sheets you need + that set of borders on. You can create multiple PropertyTemplate objects and apply them to a single sheet, or you can + apply the same PropertyTemplate object to multiple sheets. It is just like a preprinted form. + </p> + <p> + Enums: + </p> + <dl> + <dt>BorderStyle</dt> + <dd> + Defines the look of the border, is it thick or thin, solid or dashed, single or double. + This enum replaces the CellStyle.BORDER_XXXXX constants which have been deprecated. The PropertyTemplate will not + support the older style BORDER_XXXXX constants. A special value of BorderStyle.NONE will remove the border from + a Cell once it is applied. + </dd> + <dt>BorderExtent</dt> + <dd> + Describes the portion of the region that the BorderStyle will apply to. For example, TOP, BOTTOM, INSIDE, or OUTSIDE. + A special value of BorderExtent.NONE will remove the border from the PropertyTemplate. When the template is applied, + no change will be made to a cell border where no border properties exist in the PropertyTemplate. + </dd> + </dl> + <source> + // draw borders (three 3x3 grids) + PropertyTemplate pt = new PropertyTemplate(); + // #1) these borders will all be medium in default color + pt.drawBorders(new CellRangeAddress(1, 3, 1, 3), + BorderStyle.MEDIUM, BorderExtent.ALL); + // #2) these cells will have medium outside borders and thin inside borders + pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), + BorderStyle.MEDIUM, BorderExtent.OUTSIDE); + pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), BorderStyle.THIN, + BorderExtent.INSIDE); + // #3) these cells will all be medium weight with different colors for the + // outside, inside horizontal, and inside vertical borders. The center + // cell will have no borders. + pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), + BorderStyle.MEDIUM, IndexedColors.RED.getIndex(), + BorderExtent.OUTSIDE); + pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), + BorderStyle.MEDIUM, IndexedColors.BLUE.getIndex(), + BorderExtent.INSIDE_VERTICAL); + pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), + BorderStyle.MEDIUM, IndexedColors.GREEN.getIndex(), + BorderExtent.INSIDE_HORIZONTAL); + pt.drawBorders(new CellRangeAddress(10, 10, 2, 2), + BorderStyle.NONE, + BorderExtent.ALL); + + // apply borders to sheet + Workbook wb = new XSSFWorkbook(); + Sheet sh = wb.createSheet("Sheet1"); + pt.applyBorders(sh); + </source> + <p> + NOTE: The last pt.drawBorders() call removes the borders from the range by using BorderStyle.NONE. Like + setCellStyleProperties, the applyBorders method merges the properties of a cell style, so existing borders + are changed only if they are replaced by something else, or removed only if they are replaced by + BorderStyle.NONE. To remove a color from a border, use IndexedColor.AUTOMATIC.getIndex(). + </p> + <p>Additionally, to remove a border or color from the PropertyTemplate object, use BorderExtent.NONE.</p> + <p> + This does not work with diagonal borders yet. + </p> + </section> + <anchor id="PivotTable"/> + <section><title>Creating a Pivot Table</title> + <p> + Pivot Tables are a powerful feature of spreadsheet files. You can create a pivot table with the following piece of code. + </p> + <source> + XSSFWorkbook wb = new XSSFWorkbook(); + XSSFSheet sheet = wb.createSheet(); + + //Create some data to build the pivot table on + setCellData(sheet); + + XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5")); + //Configure the pivot table + //Use first column as row label + pivotTable.addRowLabel(0); + //Sum up the second column + pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1); + //Set the third column as filter + pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2); + //Add filter on forth column + pivotTable.addReportFilter(3); + </source> + </section> + <anchor id="RichText"/> + <section><title>Cells with multiple styles (Rich Text Strings)</title> + <p> + To apply a single set of text formatting (colour, style, font etc) + to a cell, you should create a + <a href="/../apidocs/dev/org/apache/poi/ss/usermodel/CellStyle.html">CellStyle</a> + for the workbook, then apply to the cells. + </p> + <source> + // HSSF Example + HSSFCell hssfCell = row.createCell(idx); + //rich text consists of two runs + HSSFRichTextString richString = new HSSFRichTextString( "Hello, World!" ); + richString.applyFont( 0, 6, font1 ); + richString.applyFont( 6, 13, font2 ); + hssfCell.setCellValue( richString ); + + + // XSSF Example + XSSFCell cell = row.createCell(1); + XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); + + XSSFFont font1 = wb.createFont(); + font1.setBold(true); + font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); + rt.applyFont(0, 10, font1); + + XSSFFont font2 = wb.createFont(); + font2.setItalic(true); + font2.setUnderline(XSSFFont.U_DOUBLE); + font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); + rt.applyFont(10, 19, font2); + + XSSFFont font3 = wb.createFont(); + font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); + rt.append(" Jumped over the lazy dog", font3); + + cell.setCellValue(rt); + </source> + <p> + To apply different formatting to different parts of a cell, you + need to use + <a href="../../apidocs/dev/org/apache/poi/ss/usermodel/RichTextString.html">RichTextString</a>, + which permits styling of parts of the text within the cell. + </p> + <p> + There are some slight differences between HSSF and XSSF, especially + around font colours (the two formats store colours quite differently + internally), refer to the + <a href="../../apidocs/dev/org/apache/poi/hssf/usermodel/HSSFRichTextString.html">HSSF Rich Text String</a> + and + <a href="../../apidocs/dev/org/apache/poi/xssf/usermodel/XSSFRichTextString.html">XSSF Rich Text String</a> + javadocs for more details. + </p> + </section> + </body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/record-generator.xml b/src/documentation/content/xdocs/components/spreadsheet/record-generator.xml new file mode 100644 index 0000000000..39f328bc78 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/record-generator.xml @@ -0,0 +1,212 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>Record Generator HOWTO</title> + <authors> + <person email="user@poi.apache.org" name="Glen Stampoultzis" id="glens"/> + <person email="acoliver@apache.org" name="Andrew C. Oliver" id="acoliver"/> + </authors> + </header> + <body> + <section><title>How to Use the Record Generator</title> + + <section><title>History</title> + <p> + The record generator was born from frustration with translating + the Excel records to Java classes. Doing this manually is a time + consuming process. It's also very easy to make mistakes. + </p> + <p> + A utility was needed to take the definition of what a + record looked like and do all the boring and repetitive work. + </p> + </section> + + <section><title>Capabilities</title> + <p> + The record generator takes XML as input and produces the following + output: + </p> + <ul> + <li>A Java file capable of decoding and encoding the record.</li> + <li>A test class that provides a fill-in-the-blanks implementation + of a test case for ensuring the record operates as + designed.</li> + </ul> + </section> + <section><title>Usage</title> + <p> + The record generator is invoked as an Ant target + (generate-records). It goes through looking for all files in + <code>src/records/definitions</code> ending with _record.xml. + It then creates two files; the Java record definition and the + Java test case template. + </p> + <p> + The records themselves have the following general layout: + </p> + <source><![CDATA[ +<record id="0x1032" name="Frame" package="org.apache.poi.hssf.record" + excel-record-id="FRAME"> + <description>The frame record indicates whether there is a border + around the displayed text of a chart.</description> + <author>Glen Stampoultzis (glens at apache.org)</author> + <fields> + <field type="int" size="2" name="border type"> + <const name="regular" value="0" description="regular rectangle or no border"/> + <const name="shadow" value="1" description="rectangle with shadow"/> + </field> + <field type="int" size="2" name="options"> + <bit number="0" name="auto size" + description="excel calculates the size automatically if true"/> + <bit number="1" name="auto position" + description="excel calculates the position automatically"/> + </field> + </fields> +</record> + ]]></source> + <p> + The following table details the allowable types and sizes for + the fields. + </p> + <table> + <tr> + <th>Type</th> + <th>Size</th> + <th>Java Type</th> + </tr> + <tr> + <td>int</td> + <td>1</td> + <td>byte</td> + </tr> + <tr> + <td>int</td> + <td>2</td> + <td>short</td> + </tr> + <tr> + <td>int</td> + <td>4</td> + <td>int</td> + </tr> + <tr> + <td>int</td> + <td>8</td> + <td>long</td> + </tr> + <tr> + <td>int</td> + <td>varword</td> + <td>array of shorts</td> + </tr> + <tr> + <td>bits</td> + <td>1</td> + <td>A byte comprising of a bits (defined by the bit element) + </td> + </tr> + <tr> + <td>bits</td> + <td>2</td> + <td>An short comprising of a bits</td> + </tr> + <tr> + <td>bits</td> + <td>4</td> + <td>A int comprising of a bits</td> + </tr> + <tr> + <td>float</td> + <td>8</td> + <td>double</td> + </tr> + <tr> + <td>hbstring</td> + <td>java expression</td> + <td>String</td> + </tr> + </table> + <p> + The Java records are regenerated each time the record generator is + run, however the test stubs are only created if the test stub does + not already exist. What this means is that you may change test + stubs but not the generated records. + </p> + </section> + <section><title>Custom Field Types</title> + <p> + Occasionally the builtin types are not enough. More control + over the encoding and decoding of the streams is required. This + can be achieved using a custom type. + </p> + <p> + A custom type lets you escape to java to define the way in which + the field encodes and decodes. To code a custom type you + declare your field like this: + </p> + <source><![CDATA[ + <field type="custom:org.apache.poi.hssf.record.LinkedDataFormulaField" + size="var" name="formula of link" description="formula"/> + ]]></source> + <p> + Where the class name specified after <code>custom:</code> is a + class implementing the interface <code>CustomField</code>. + </p> + <p> + You can then implement the encoding yourself. + </p> + </section> + <section><title>How it Works</title> + <p> + The record generation works by taking an XML file and styling it + using XSLT. Given that XSLT is a little limited in some ways it was + necessary to add a little Java code to the mix. + </p> + <p> + See record.xsl, record_test.xsl, FieldIterator.java, + RecordUtil.java, RecordGenerator.java + </p> + <p> + There is a corresponding "type" generator for HWPF. + See the HWPF documentation for details. + </p> + </section> + <section><title>Limitations</title> + <p> + The record generator does not handle all possible record types and + goes not intend to perform this function. When dealing with a + non-standard record sometimes the cost-benefit of coding the + record by hand will be greater than attempting modify the + generator. The main point of the record generator is to save + time, so keep that in mind. + </p> + <p> + Currently the XSL file that generates the record calls out to + Java objects. The Java code for the record generation is + currently quite messy with minimal comments. + </p> + </section> +</section> +</body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/use-case.xml b/src/documentation/content/xdocs/components/spreadsheet/use-case.xml new file mode 100644 index 0000000000..6c8ed246e7 --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/use-case.xml @@ -0,0 +1,200 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>HSSF Use Cases</title> + <authors> + <person email="marc.johnson@yahoo.com" name="Marc Johnson" id="MJ"/> + </authors> + </header> + <body> + <section><title>HSSF Use Cases</title> + <section><title>Use Case 1: Read existing HSSF</title> + +<p><strong>Primary Actor:</strong> HSSF client</p> +<p><strong>Scope:</strong> HSSF</p> +<p><strong>Level:</strong> Summary</p> +<p><strong>Stakeholders and Interests:</strong></p> +<ul> + <li>HSSF client- wants to read content + of HSSF file</li> + <li>HSSF - understands HSSF file</li> + <li>POIFS - understands underlying POI + file system</li> +</ul> +<p><strong>Precondition:</strong> None</p> +<p><strong>Minimal Guarantee:</strong> None</p> +<p><strong>Main Success Guarantee:</strong></p> +<ol> + <li>HSSF client requests HSSF to read + a HSSF file, providing an InputStream + containing HSSF file in question.</li> + <li>HSSF requests POIFS to read the HSSF + file, passing the InputStream + object to POIFS (POIFS use case 1, read existing file system)</li> + <li>HSSF reads the "Workbook" + file (use case 4, read workbook entry)</li> +</ol> +<p><strong>Extensions:</strong></p> +<p>2a. Exceptions +thrown by POIFS will be passed on to the HSSF client.</p> +</section> + <section><title>Use Case 2: Write HSSF file</title> + +<p><strong>Primary Actor:</strong> HSSF client</p> +<p><strong>Scope:</strong> HSSF</p> +<p><strong>Level:</strong> Summary</p> +<p><strong>Stakeholders and Interests:</strong></p> +<ul> + <li>HSSF client- wants to write file + out.</li> + <li>HSSF - knows how to write file + out.</li> + <li>POIFS - knows how to write file + system out.</li> +</ul> +<p><strong>Precondition:</strong></p> +<ul> + <li>File has been + read (use case 1, read existing HSSF file) and subsequently modified + or file has been created (use case 3, create HSSF file)</li> +</ul> +<p><strong>Minimal Guarantee:</strong> None</p> +<p><strong>Main Success Guarantee:</strong></p> +<ol> + <li>HSSF client + provides an OutputStream to + write the file to.</li> + <li>HSSF writes + the "Workbook" to its associated POIFS file system (use case + 5, write workbook entry)</li> + <li>HSSF + requests POIFS to write its file system out, using the OutputStream + obtained from the HSSF client (POIFS use case 2, write file system).</li> +</ol> +<p><strong>Extensions:</strong></p> +<p>3a. Exceptions +from POIFS are passed to the HSSF client.</p> + +</section> + <section><title>Use Case 3:Create HSSF file</title> + +<p><strong>Primary Actor:</strong> HSSF client</p> +<p><strong>Scope:</strong> HSSF</p> +<p> +<strong>Level:</strong> Summary</p> +<p><strong>Stakeholders and Interests:</strong></p> +<ul> + <li>HSSF client- wants to create a new + file.</li> + <li>HSSF - knows how to create a new + file.</li> + <li>POIFS - knows how to create a new + file system.</li> +</ul> +<p><strong>Precondition:</strong></p> +<p><strong>Minimal Guarantee:</strong> None</p> +<p><strong>Main Success Guarantee:</strong></p> +<ol> + <li>HSSF requests + POIFS to create a new file system (POIFS use case 3, create new file + system)</li> +</ol> +<p><strong>Extensions:</strong> +None</p> + +</section> + <section><title>Use Case 4: Read workbook entry</title> +<p><strong>Primary Actor:</strong> HSSF</p> +<p><strong>Scope:</strong> HSSF</p> +<p> +<strong>Level:</strong> Summary</p> +<p><strong>Stakeholders and Interests:</strong></p> +<ul> + <li>HSSF - knows how to read the + workbook entry</li> + <li>POIFS - knows how to manage the file + system.</li> +</ul> +<p><strong>Precondition:</strong></p> +<ul> + <li>The file + system has been read (use case 1, read existing HSSF file) or has + been created and written to (use case 3, create HSSF file system; + use case 5, write workbook entry).</li> +</ul> +<p><strong>Minimal +Guarantee:</strong> None</p> +<p><strong>Main Success Guarantee:</strong></p> +<ol> + <li> + HSSF requests POIFS for the "Workbook" file</li> + <li>POIFS returns + an InputStream for the file.</li> + <li>HSSF reads + from the InputStream provided by POIFS</li> + <li>HSSF closes + the InputStream provided by POIFS</li> +</ol> +<p><strong>Extensions:</strong></p> +<p>3a. Exceptions +thrown by POIFS will be passed on</p> +</section> + <section><title>Use Case 5: Write workbook entry</title> + + +<p><strong>Primary Actor:</strong> HSSF</p> +<p><strong>Scope:</strong> HSSF</p> +<p> +<strong>Level:</strong> Summary</p> +<p><strong>Stakeholders and Interests:</strong></p> +<ul> + <li>HSSF - knows how to manage the + write the workbook entry.</li> + <li>POIFS - knows how to manage the file + system.</li> +</ul> +<p><strong>Precondition:</strong> +</p> +<ul> + <li>Either an existing HSSF file has + been read (use case 1, read existing HSSF file) or an HSSF file has + been created (use case 3, create HSSF file).</li> +</ul> +<p><strong>Minimal Guarantee:</strong> None</p> +<p><strong>Main Success Guarantee:</strong></p> +<ol> + <li>HSSF + checks the POIFS file system directory for the "Workbook" + file (POIFS use case 8, read file system directory)</li> + <li>If "Workbook" is in the directory, HSSF requests POIFS to + replace it with the new workbook entry (POIFS use case 4, replace file + in file system). Otherwise, HSSF requests POIFS to write the new + workbook file, with the name "Workbook" (POIFS use case 6, + write new file to file system)</li> +</ol> +<p><strong>Extensions:</strong>None</p> +</section> + +</section> +</body> +</document> diff --git a/src/documentation/content/xdocs/components/spreadsheet/user-defined-functions.xml b/src/documentation/content/xdocs/components/spreadsheet/user-defined-functions.xml new file mode 100644 index 0000000000..1d5819b88b --- /dev/null +++ b/src/documentation/content/xdocs/components/spreadsheet/user-defined-functions.xml @@ -0,0 +1,414 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== +--> +<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "document-v20.dtd"> + +<document> + <header> + <title>User Defined Functions</title> + <authors> + <person email="jon@loquatic.com" name="Jon Svede" id="JDS"/> + <person email="brian.bush@nrel.gov" name="Brian Bush" id="BWB"/> + </authors> + </header> + <body> + <section><title>How to Create and Use User Defined Functions</title> + + <section><title>Description</title> + <p>This document describes the User Defined Functions within POI. + User defined functions allow you to take code that is written in VBA + and re-write in Java and use within POI. Consider the following example.</p> + </section> + <section><title>An Example</title> + <p>Suppose you are given a spreadsheet that can calculate the principal and interest + payments for a mortgage. The user enters the principal loan amount, the interest rate + and the term of the loan. The Excel spreadsheet does the rest.</p> + <p> + <img src="images/simple-xls-with-function.jpg" alt="mortgage calculation spreadsheet"/> + </p> + <p>When you actually look at the workbook you discover that rather than having + the formula in a cell it has been written as VBA function. You review the + function and determine that it could be written in Java:</p> + <p> + <img src="images/calculatePayment.jpg" alt="VBA code"/> + </p> + <p>If we write a small program to try to evaluate this cell, we'll fail. Consider this source code:</p> + <source><![CDATA[ +import java.io.File ; +import java.io.FileInputStream ; +import java.io.FileNotFoundException ; +import java.io.IOException ; + +import org.apache.poi.openxml4j.exceptions.InvalidFormatException ; +import org.apache.poi.ss.formula.functions.FreeRefFunction ; +import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ; +import org.apache.poi.ss.formula.udf.DefaultUDFFinder ; +import org.apache.poi.ss.formula.udf.UDFFinder ; +import org.apache.poi.ss.usermodel.Cell ; +import org.apache.poi.ss.usermodel.CellValue ; +import org.apache.poi.ss.usermodel.Row ; +import org.apache.poi.ss.usermodel.Sheet ; +import org.apache.poi.ss.usermodel.Workbook ; +import org.apache.poi.ss.usermodel.WorkbookFactory ; +import org.apache.poi.ss.util.CellReference ; + +public class Evaluator { + + + + public static void main( String[] args ) { + + System.out.println( "fileName: " + args[0] ) ; + System.out.println( "cell: " + args[1] ) ; + + File workbookFile = new File( args[0] ) ; + + try { + FileInputStream fis = new FileInputStream(workbookFile); + Workbook workbook = WorkbookFactory.create(fis); + + FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); + + CellReference cr = new CellReference( args[1] ) ; + String sheetName = cr.getSheetName() ; + Sheet sheet = workbook.getSheet( sheetName ) ; + int rowIdx = cr.getRow() ; + int colIdx = cr.getCol() ; + Row row = sheet.getRow( rowIdx ) ; + Cell cell = row.getCell( colIdx ) ; + + CellValue value = evaluator.evaluate( cell ) ; + + System.out.println("returns value: " + value ) ; + + + } catch( FileNotFoundException e ) { + e.printStackTrace(); + } catch( InvalidFormatException e ) { + e.printStackTrace(); + } catch( IOException e ) { + e.printStackTrace(); + } + } +} + +]]></source> + <p>If you run this code, you're likely to get the following error:</p> + + <source><![CDATA[ +Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!B4 + at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321) + at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288) + at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221) + at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320) + at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182) + at poi.tests.Evaluator.main(Evaluator.java:61) +Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: calculatePayment + at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59) + at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129) + at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456) + at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279) + ... 4 more + +]]></source> + + <p>How would we make it so POI can use this sheet?</p> + </section> + + <section><title>Defining Your Function</title> + <p>To 'convert' this code to Java and make it available to POI you need to implement + a FreeRefFunction instance. FreeRefFunction is an interface in the org.apache.poi.ss.formula.functions + package. This interface defines one method, evaluate(ValueEval[] args, OperationEvaluationContext ec), + which is how you will receive the argument values from POI.</p> + <p>The evaluate() method as defined above is where you will convert the ValueEval instances to the + proper number types. The following code snippet shows you how to get your values:</p> + + <source><![CDATA[ +public class CalculateMortgage implements FreeRefFunction { + +@Override +public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) { + if (args.length != 3) { + return ErrorEval.VALUE_INVALID; + } + + double principal, rate, years, result; + try { + ValueEval v1 = OperandResolver.getSingleValue( args[0], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v2 = OperandResolver.getSingleValue( args[1], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v3 = OperandResolver.getSingleValue( args[2], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + + principal = OperandResolver.coerceValueToDouble( v1 ) ; + rate = OperandResolver.coerceValueToDouble( v2 ) ; + years = OperandResolver.coerceValueToDouble( v3 ) ; + ]]></source> + + <p>The first thing we do is check the number of arguments being passed since there is no sense + in attempting to go further if you are missing critical information.</p> + <p>Next we declare our variables, in our case we need variables for:</p> + <ul> + <li>principal - the amount of the loan</li> + <li>rate - the interest rate as a decimal</li> + <li>years - the length of the loan in years</li> + <li>result - the result of the calculation</li> + </ul> + <p>Next, we use the OperandResolver to convert the ValueEval instances to doubles, though not directly. + First we start by getting discreet values. Using the OperandResolver.getSingleValue() method + we retrieve each of the values passed in by the cell in the spreadsheet. Next, we use the + OperandResolver again to convert the ValueEval instances to doubles, in this case. This + class has other methods of coercion for getting Strings, ints and booleans. Now that we've + got our primitive values we can move on to calculating the value.</p> + <p>As shown previously, we have the VBA source. We need to add code to our class to calculate + the payment. To do this you could simply add it to the method we've already created but I've + chosen to add it as its own method. Add the following method: </p> + <source><![CDATA[ +public double calculateMortgagePayment( double p, double r, double y ) { + + double i = r / 12 ; + double n = y * 12 ; + + double principalAndInterest = + p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ; + + return principalAndInterest ; +} + ]]></source> + <p>The biggest change necessary is related to the exponents; Java doesn't have a notation for this + so we had to add calls to Math.pow(). Now we need to add this call to our previous method:</p> + <source><![CDATA[ + result = calculateMortgagePayment( principal, rate, years ) ; + ]]></source> + <p>Having done that, the last things we need to do are to check to make sure we didn't get a bad result and, + if not, we need to return the value. Add the following code to the class:</p> + <source><![CDATA[ +private void checkValue(double result) throws EvaluationException { + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } +} + ]]></source> + <p>Then add a line of code to our evaluate method to call this new static method, complete our try/catch and return the value:</p> + <source><![CDATA[ + checkValue(result); + + } catch (EvaluationException e) { + e.printStackTrace() ; + return e.getErrorEval(); + } + + return new NumberEval( result ) ; + ]]></source> + + <p>So the whole class would be as follows:</p> + + <source><![CDATA[ +import org.apache.poi.ss.formula.OperationEvaluationContext ; +import org.apache.poi.ss.formula.eval.ErrorEval ; +import org.apache.poi.ss.formula.eval.EvaluationException ; +import org.apache.poi.ss.formula.eval.NumberEval ; +import org.apache.poi.ss.formula.eval.OperandResolver ; +import org.apache.poi.ss.formula.eval.ValueEval ; +import org.apache.poi.ss.formula.functions.FreeRefFunction ; + +/** + * A simple function to calculate principal and interest. + * + * @author Jon Svede + * + */ +public class CalculateMortgage implements FreeRefFunction { + + @Override + public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) { + if (args.length != 3) { + return ErrorEval.VALUE_INVALID; + } + + double principal, rate, years, result; + try { + ValueEval v1 = OperandResolver.getSingleValue( args[0], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v2 = OperandResolver.getSingleValue( args[1], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v3 = OperandResolver.getSingleValue( args[2], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + + principal = OperandResolver.coerceValueToDouble( v1 ) ; + rate = OperandResolver.coerceValueToDouble( v2 ) ; + years = OperandResolver.coerceValueToDouble( v3 ) ; + + result = calculateMortgagePayment( principal, rate, years ) ; + + checkValue(result); + + } catch (EvaluationException e) { + e.printStackTrace() ; + return e.getErrorEval(); + } + + return new NumberEval( result ) ; + } + + public double calculateMortgagePayment( double p, double r, double y ) { + double i = r / 12 ; + double n = y * 12 ; + + //M = P [ i(1 + i)n ] / [ (1 + i)n - 1] + double principalAndInterest = + p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ; + + return principalAndInterest ; + } + + /** + * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases + * + * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt> + */ + static final void checkValue(double result) throws EvaluationException { + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } + +} + + ]]></source> + + <p>Great! Now we need to go back to our original program that failed to evaluate our cell and add code that will allow it run our new Java code.</p> + + </section> + + <section><title>Registering Your Function</title> + <p>Now we need to register our function in the Workbook, so that the Formula Evaluator can resolve the name "calculatePayment" +and map it to the actual implementation (CalculateMortgage). This is done using the UDFFinder object. +The UDFFinder manages FreeRefFunctions which are our analogy for the VBA code. We need to create a UDFFinder. There are + a few things we need to know in order to do this:</p> + <ul> + <li>The name of the function in the VBA code (in our case it is calculatePayment)</li> + <li>The Class name of our FreeRefFunction</li> + </ul> + <p>UDFFinder is actually an interface, so we need to use an actual implementation of this interface. Therefore we use the org.apache.poi.ss.formula.udf.DefaultUDFFinder class. If you refer to the Javadocs you'll see that this class expects to get two arrays, one + containing the alias and the other containing an instance of the class that will represent that alias. In our case our alias will be calculatePayment + and our class instance will be of the CalculateMortgage type. This class needs to be available at compile and runtime. Be sure to keep these arrays + well organized because you'll run into problems if these arrays are of different sizes or the alias aren't in the same relative position in their respective + arrays. Add the following code:</p> + <source><![CDATA[ +String[] functionNames = { "calculatePayment" } ; +FreeRefFunction[] functionImpls = { new CalculateMortgage() } ; + +UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ; +UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ; + ]]></source> + <p>Now we have our UDFFinder instance and we've created the AggregatingUDFFinder instance. The last step is to pass this to our Workbook:</p> + + <source><![CDATA[ +workbook.addToolPack(udfToolpack); + ]]></source> + <p>So now the whole class will look like this:</p> + <source><![CDATA[ +import java.io.File ; +import java.io.FileInputStream ; +import java.io.FileNotFoundException ; +import java.io.IOException ; + +import org.apache.poi.openxml4j.exceptions.InvalidFormatException ; +import org.apache.poi.ss.formula.functions.FreeRefFunction ; +import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ; +import org.apache.poi.ss.formula.udf.DefaultUDFFinder ; +import org.apache.poi.ss.formula.udf.UDFFinder ; +import org.apache.poi.ss.usermodel.Cell ; +import org.apache.poi.ss.usermodel.CellValue ; +import org.apache.poi.ss.usermodel.Row ; +import org.apache.poi.ss.usermodel.Sheet ; +import org.apache.poi.ss.usermodel.Workbook ; +import org.apache.poi.ss.usermodel.WorkbookFactory ; +import org.apache.poi.ss.util.CellReference ; + +public class Evaluator { + + public static void main( String[] args ) { + + System.out.println( "fileName: " + args[0] ) ; + System.out.println( "cell: " + args[1] ) ; + + File workbookFile = new File( args[0] ) ; + + try { + FileInputStream fis = new FileInputStream(workbookFile); + Workbook workbook = WorkbookFactory.create(fis); + + String[] functionNames = { "calculatePayment" } ; + FreeRefFunction[] functionImpls = { new CalculateMortgage() } ; + + UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ; + UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ; + + workbook.addToolPack(udfToolpack); + + FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); + + CellReference cr = new CellReference( args[1] ) ; + String sheetName = cr.getSheetName() ; + Sheet sheet = workbook.getSheet( sheetName ) ; + int rowIdx = cr.getRow() ; + int colIdx = cr.getCol() ; + Row row = sheet.getRow( rowIdx ) ; + Cell cell = row.getCell( colIdx ) ; + + CellValue value = evaluator.evaluate( cell ) ; + + System.out.println("returns value: " + value ) ; + + + } catch( FileNotFoundException e ) { + e.printStackTrace(); + } catch( InvalidFormatException e ) { + e.printStackTrace(); + } catch( IOException e ) { + e.printStackTrace(); + } + } +} + + ]]></source> + <p>Now that our evaluator is aware of the UDFFinder which in turn is aware of our FreeRefFunction, we're ready to re-run our example:</p> + <source>Evaluator mortgage-calculation.xls Sheet1!B4</source> + <p>which prints the following output in the console:</p> + <source><![CDATA[ +fileName: mortgage-calculation.xls +cell: Sheet1!B4 +returns value: org.apache.poi.ss.usermodel.CellValue [790.7936267415464] + ]]></source> + <p>That is it! Now you can create Java code and register it, allowing your POI based appliction to run spreadsheets that previously were inaccessible.</p> + <p>This example can be found in the <a href="https://github.com/apache/poi/tree/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula">poi-examples/src/main/java/org/apache/poi/examples/ss/formula</a> folder in the source.</p> + </section> + </section> +</body> +</document> + |