diff options
| author | Yegor Kozlov <yegor@apache.org> | 2010-05-10 16:11:50 +0000 |
|---|---|---|
| committer | Yegor Kozlov <yegor@apache.org> | 2010-05-10 16:11:50 +0000 |
| commit | 90170942312c0262025f7f2af598faa642533259 (patch) | |
| tree | fad759213584c02f1517c7461d4d73fa8da01c9e | |
| parent | a1208452206129a9b23921b822688e57be3cad75 (diff) | |
| download | poi-90170942312c0262025f7f2af598faa642533259.tar.gz poi-90170942312c0262025f7f2af598faa642533259.zip | |
Enhanced SViewer to support most border types, cell formats, and conditional formatting. Added ToHtml example that converts a spreadsheet into HTML, See Bugzilla #49066
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@942809 13f79535-47bb-0310-9956-ffa450edef68
48 files changed, 4594 insertions, 9 deletions
@@ -504,6 +504,11 @@ under the License. <pathelement path="${ooxml.output.dir}"/> </classpath> </javac> + <copy todir="${examples.output.dir}"> + <fileset dir="${examples.src}"> + <include name="**/*.css"/> + </fileset> + </copy> </target> <target name="compile-ooxml" depends="compile-main,compile-scratchpad"> diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 6d8c5e58c3..4a86cee548 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ <changes> <release version="3.7-SNAPSHOT" date="2010-??-??"> + <action dev="POI-DEVELOPERS" type="add">49066 - Worksheet/cell formatting, with view and HTML converter</action> <action dev="POI-DEVELOPERS" type="fix">49020 - Workaround Excel outputting invalid XML in button definitions by not closing BR tags</action> <action dev="POI-DEVELOPERS" type="fix">49050 - Improve performance of AbstractEscherHolderRecord when there are lots of Continue Records</action> <action dev="POI-DEVELOPERS" type="fix">49194 - Correct text size limit for OOXML .xlsx files</action> diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVBorder.java b/src/examples/src/org/apache/poi/hssf/view/SVBorder.java index fb25448cb5..083b9cc18e 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVBorder.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVBorder.java @@ -16,7 +16,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.awt.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVFractionalFormat.java b/src/examples/src/org/apache/poi/hssf/view/SVFractionalFormat.java index 5512a73a71..cd6ff6ea72 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVFractionalFormat.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVFractionalFormat.java @@ -17,7 +17,7 @@ ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.text.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVRowHeader.java b/src/examples/src/org/apache/poi/hssf/view/SVRowHeader.java index fe63dfcc8a..c6db2f71a4 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVRowHeader.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVRowHeader.java @@ -18,7 +18,7 @@ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.awt.*; import javax.swing.*; diff --git a/src/examples/src/org/apache/poi/hssf/view/SVSheetTable.java b/src/examples/src/org/apache/poi/hssf/view/SVSheetTable.java new file mode 100644 index 0000000000..ed2fd8fb6d --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/SVSheetTable.java @@ -0,0 +1,241 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.hssf.view; + +import org.apache.poi.hssf.view.brush.PendingPaintings; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; + +import javax.swing.*; +import javax.swing.event.ListSelectionEvent; +import javax.swing.event.ListSelectionListener; +import javax.swing.table.*; +import javax.swing.text.JTextComponent; +import java.awt.*; +import java.awt.event.HierarchyEvent; +import java.awt.event.HierarchyListener; + +/** + * This class is a table that represents the values in a single worksheet. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class SVSheetTable extends JTable { + private final HSSFSheet sheet; + private final PendingPaintings pendingPaintings; + private FormulaDisplayListener formulaListener; + private JScrollPane scroll; + + private static final Color HEADER_BACKGROUND = new Color(235, 235, 235); + + /** + * This field is the magic number to convert from a Character width to a java + * pixel width. + * <p/> + * When the "normal" font size in a workbook changes, this effects all of the + * heights and widths. Unfortunately there is no way to retrieve this + * information, hence the MAGIC number. + * <p/> + * This number may only work for the normal style font size of Arial size 10. + */ + private static final int magicCharFactor = 7; + + private class HeaderCell extends JLabel { + private final int row; + + public HeaderCell(Object value, int row) { + super(value.toString(), CENTER); + this.row = row; + setBackground(HEADER_BACKGROUND); + setOpaque(true); + setBorder(BorderFactory.createLineBorder(Color.LIGHT_GRAY)); + setRowSelectionAllowed(false); + } + + @Override + public Dimension getPreferredSize() { + Dimension d = super.getPreferredSize(); + if (row >= 0) { + d.height = getRowHeight(row); + } + return d; + } + + @Override + public Dimension getMaximumSize() { + Dimension d = super.getMaximumSize(); + if (row >= 0) { + d.height = getRowHeight(row); + } + return d; + } + + @Override + public Dimension getMinimumSize() { + Dimension d = super.getMinimumSize(); + if (row >= 0) { + d.height = getRowHeight(row); + } + return d; + } + } + + private class HeaderCellRenderer implements TableCellRenderer { + public Component getTableCellRendererComponent(JTable table, Object value, + boolean isSelected, boolean hasFocus, int row, int column) { + + return new HeaderCell(value, row); + } + } + + private class FormulaDisplayListener implements ListSelectionListener { + private final JTextComponent formulaDisplay; + + public FormulaDisplayListener(JTextComponent formulaDisplay) { + this.formulaDisplay = formulaDisplay; + } + + public void valueChanged(ListSelectionEvent e) { + int row = getSelectedRow(); + int col = getSelectedColumn(); + if (row < 0 || col < 0) { + return; + } + + if (e.getValueIsAdjusting()) { + return; + } + + HSSFCell cell = (HSSFCell) getValueAt(row, col); + String formula = ""; + if (cell != null) { + if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { + formula = cell.getCellFormula(); + } else { + formula = cell.toString(); + } + if (formula == null) + formula = ""; + } + formulaDisplay.setText(formula); + } + } + + public SVSheetTable(HSSFSheet sheet) { + super(new SVTableModel(sheet)); + this.sheet = sheet; + + setIntercellSpacing(new Dimension(0, 0)); + setAutoResizeMode(AUTO_RESIZE_OFF); + JTableHeader header = getTableHeader(); + header.setDefaultRenderer(new HeaderCellRenderer()); + pendingPaintings = new PendingPaintings(this); + + //Set the columns the correct size + TableColumnModel columns = getColumnModel(); + for (int i = 0; i < columns.getColumnCount(); i++) { + TableColumn column = columns.getColumn(i); + int width = sheet.getColumnWidth(i); + //256 is because the width is in 256ths of a character + column.setPreferredWidth(width / 256 * magicCharFactor); + } + + Toolkit t = getToolkit(); + int res = t.getScreenResolution(); + TableModel model = getModel(); + for (int i = 0; i < model.getRowCount(); i++) { + Row row = sheet.getRow(i - sheet.getFirstRowNum()); + if (row != null) { + short h = row.getHeight(); + int height = Math.round(Math.max(1, h / (res / 70 * 20) + 3)); + System.out.printf("%d: %d (%d @ %d)%n", i, height, h, res); + setRowHeight(i, height); + } + } + + addHierarchyListener(new HierarchyListener() { + public void hierarchyChanged(HierarchyEvent e) { + if ((e.getChangeFlags() & HierarchyEvent.PARENT_CHANGED) != 0) { + Container changedParent = e.getChangedParent(); + if (changedParent instanceof JViewport) { + Container grandparent = changedParent.getParent(); + if (grandparent instanceof JScrollPane) { + JScrollPane jScrollPane = (JScrollPane) grandparent; + setupScroll(jScrollPane); + } + } + } + } + }); + } + + public void setupScroll(JScrollPane scroll) { + if (scroll == this.scroll) + return; + + this.scroll = scroll; + if (scroll == null) + return; + + SVRowHeader rowHeader = new SVRowHeader(sheet, this, 0); + scroll.setRowHeaderView(rowHeader); + scroll.setCorner(JScrollPane.UPPER_LEADING_CORNER, headerCell("?")); + } + + public void setFormulaDisplay(JTextComponent formulaDisplay) { + ListSelectionModel rowSelMod = getSelectionModel(); + ListSelectionModel colSelMod = getColumnModel().getSelectionModel(); + + if (formulaDisplay == null) { + rowSelMod.removeListSelectionListener(formulaListener); + colSelMod.removeListSelectionListener(formulaListener); + formulaListener = null; + } + + if (formulaDisplay != null) { + formulaListener = new FormulaDisplayListener(formulaDisplay); + rowSelMod.addListSelectionListener(formulaListener); + colSelMod.addListSelectionListener(formulaListener); + } + } + + public JTextComponent getFormulaDisplay() { + if (formulaListener == null) + return null; + else + return formulaListener.formulaDisplay; + } + + public Component headerCell(String text) { + return new HeaderCell(text, -1); + } + + @Override + public void paintComponent(Graphics g1) { + Graphics2D g = (Graphics2D) g1; + + pendingPaintings.clear(); + + g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, + RenderingHints.VALUE_ANTIALIAS_ON); + super.paintComponent(g); + + pendingPaintings.paint(g); + } +}
\ No newline at end of file diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableCellEditor.java b/src/examples/src/org/apache/poi/hssf/view/SVTableCellEditor.java index e7a2a5ded3..4fd79a9620 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableCellEditor.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVTableCellEditor.java @@ -16,7 +16,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.awt.*; import java.awt.event.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableCellRenderer.java b/src/examples/src/org/apache/poi/hssf/view/SVTableCellRenderer.java index 0e4873b5de..4b2e634bb3 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableCellRenderer.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVTableCellRenderer.java @@ -17,7 +17,7 @@ ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import javax.swing.*; import javax.swing.table.TableCellRenderer; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableModel.java b/src/examples/src/org/apache/poi/hssf/view/SVTableModel.java index c2f4bb31a0..170dacb697 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableModel.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVTableModel.java @@ -18,7 +18,7 @@ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.util.Iterator; import javax.swing.table.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableUtils.java b/src/examples/src/org/apache/poi/hssf/view/SVTableUtils.java index 5815ea0a59..08ac7b32e0 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SVTableUtils.java +++ b/src/examples/src/org/apache/poi/hssf/view/SVTableUtils.java @@ -16,7 +16,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.util.*; import java.awt.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SViewer.java b/src/examples/src/org/apache/poi/hssf/view/SViewer.java index a3668f6490..de2cfb1f6d 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SViewer.java +++ b/src/examples/src/org/apache/poi/hssf/view/SViewer.java @@ -18,7 +18,7 @@ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.awt.*; import java.awt.event.*; diff --git a/src/contrib/src/org/apache/poi/hssf/contrib/view/SViewerPanel.java b/src/examples/src/org/apache/poi/hssf/view/SViewerPanel.java index f4695376d0..5fe596220a 100644 --- a/src/contrib/src/org/apache/poi/hssf/contrib/view/SViewerPanel.java +++ b/src/examples/src/org/apache/poi/hssf/view/SViewerPanel.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.contrib.view; +package org.apache.poi.hssf.view; import java.awt.*; import java.awt.event.*; diff --git a/src/examples/src/org/apache/poi/hssf/view/brush/BasicBrush.java b/src/examples/src/org/apache/poi/hssf/view/brush/BasicBrush.java new file mode 100644 index 0000000000..db36b83265 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/brush/BasicBrush.java @@ -0,0 +1,72 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.hssf.view.brush; + +import java.awt.*; + +/** + * This is a basic brush that just draws the line with the given parameters. + * This is a {@link BasicStroke} object that can be used as a {@link Brush}. + * + * @author Ken Arnold, Industrious Media LLC + * @see BasicStroke + */ +public class BasicBrush extends BasicStroke implements Brush { + /** + * Creates a new basic brush with the given width. Invokes {@link + * BasicStroke#BasicStroke(float)} + * + * @param width The brush width. + * + * @see BasicStroke#BasicStroke(float) + */ + public BasicBrush(float width) { + super(width); + } + + /** + * Creates a new basic brush with the given width, cap, and join. Invokes + * {@link BasicStroke#BasicStroke(float,int,int)} + * + * @param width The brush width. + * @param cap The capping style. + * @param join The join style. + * + * @see BasicStroke#BasicStroke(float, int, int) + */ + public BasicBrush(float width, int cap, int join) { + super(width, cap, join); + } + + /** + * Creates a new basic brush with the given parameters. Invokes {@link + * BasicStroke#BasicStroke(float,int,int,float,float[],float)} with a miter + * limit of 11 (the normal default value). + * + * @param width The brush width. + * @param cap The capping style. + * @param join The join style. + * @param dashes The dash intervals. + * @param dashPos The intial dash position in the dash intervals. + * + * @see BasicStroke#BasicStroke(float, int, int, float, float[], float) + */ + public BasicBrush(float width, int cap, int join, float[] dashes, + int dashPos) { + super(width, cap, join, 11.0f, dashes, dashPos); + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/hssf/view/brush/Brush.java b/src/examples/src/org/apache/poi/hssf/view/brush/Brush.java new file mode 100644 index 0000000000..30786a635e --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/brush/Brush.java @@ -0,0 +1,14 @@ +package org.apache.poi.hssf.view.brush; + +import java.awt.*; + +/** + * This is the type you must implement to create a brush that will be used for a + * spreadsheet border. + * + * @author Ken Arnold, Industrious Media LLC + */ +public interface Brush extends Stroke { + /** Returns the width of the brush. */ + float getLineWidth(); +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/hssf/view/brush/DoubleStroke.java b/src/examples/src/org/apache/poi/hssf/view/brush/DoubleStroke.java new file mode 100644 index 0000000000..126214b8a1 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/brush/DoubleStroke.java @@ -0,0 +1,62 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.hssf.view.brush; + +import java.awt.*; + +/** + * This Stroke implementation applies a BasicStroke to a shape twice. If you + * draw with this Stroke, then instead of outlining the shape, you're outlining + * the outline of the shape. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class DoubleStroke implements Brush { + BasicStroke stroke1, stroke2; // the two strokes to use + + /** + * Creates a new double-stroke brush. This surrounds a cell with a two + * lines separated by white space between. + * + * @param width1 The width of the blank space in the middle + * @param width2 The width of the each of the two drawn strokes. + */ + public DoubleStroke(float width1, float width2) { + stroke1 = new BasicStroke(width1); // Constructor arguments specify + stroke2 = new BasicStroke(width2); // the line widths for the strokes + } + + /** + * Stroke the outline. + * + * @param s The shape in which to stroke. + * + * @return The created stroke as a new shape. + */ + public Shape createStrokedShape(Shape s) { + // Use the first stroke to create an outline of the shape + Shape outline = stroke1.createStrokedShape(s); + // Use the second stroke to create an outline of that outline. + // It is this outline of the outline that will be filled in + return stroke2.createStrokedShape(outline); + } + + /** {@inheritDoc} */ + public float getLineWidth() { + return stroke1.getLineWidth() + 2 * stroke2.getLineWidth(); + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/hssf/view/brush/PendingPaintings.java b/src/examples/src/org/apache/poi/hssf/view/brush/PendingPaintings.java new file mode 100644 index 0000000000..3b95c05ac1 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/brush/PendingPaintings.java @@ -0,0 +1,178 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.hssf.view.brush; + +import javax.swing.*; +import java.awt.*; +import java.awt.geom.*; +import java.util.ArrayList; +import java.util.List; + +/** + * This class is used to hold pending brush paintings. The model is that some + * border drawing requires drawing strokes after all the cells have been + * painted. The list of pending paintings can be put in this object during the + * initial paint of the component, and then executed at the appropriate time, + * such as at the end of the containing object's {@link + * JComponent#paintChildren(Graphics)} method. + * <p/> + * It is up to the parent component to invoke the {@link #paint(Graphics2D)} + * method of this objet at that appropriate time. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class PendingPaintings { + /** + * The name of the client property that holds this object in the parent + * component. + */ + public static final String PENDING_PAINTINGS = + PendingPaintings.class.getSimpleName(); + + private final List<Painting> paintings; + + /** A single painting description. */ + public static class Painting { + final Stroke stroke; + final Color color; + final Shape shape; + final AffineTransform transform; + + /** + * Creates a new painting description. + * + * @param stroke The stroke to paint. + * @param color The color of the stroke. + * @param shape The shape of the stroke. + * @param transform The transformation matrix to use. + */ + public Painting(Stroke stroke, Color color, Shape shape, + AffineTransform transform) { + + this.color = color; + this.shape = shape; + this.stroke = stroke; + this.transform = transform; + } + + /** + * Draw the painting. + * + * @param g The graphics object to use to draw with. + */ + public void draw(Graphics2D g) { + g.setTransform(transform); + g.setStroke(stroke); + g.setColor(color); + g.draw(shape); + } + } + + /** + * Creates a new object on the given parent. The created object will be + * stored as a client property. + * + * @param parent + */ + public PendingPaintings(JComponent parent) { + paintings = new ArrayList<Painting>(); + parent.putClientProperty(PENDING_PAINTINGS, this); + } + + /** Drops all pending paintings. */ + public void clear() { + paintings.clear(); + } + + /** + * Paints all pending paintings. Once they have been painted they are + * removed from the list of pending paintings (they aren't pending anymore, + * after all). + * + * @param g The graphics object to draw with. + */ + public void paint(Graphics2D g) { + g.setBackground(Color.CYAN); + AffineTransform origTransform = g.getTransform(); + for (Painting c : paintings) { + c.draw(g); + } + g.setTransform(origTransform); + + clear(); + } + + /** + * Adds a new pending painting to the list on the given component. This + * will find the first ancestor that has a {@link PendingPaintings} client + * property, starting with the component itself. + * + * @param c The component for which the painting is being added. + * @param g The graphics object to draw with. + * @param stroke The stroke to draw. + * @param color The color to draw with. + * @param shape The shape to stroke. + */ + public static void add(JComponent c, Graphics2D g, Stroke stroke, + Color color, Shape shape) { + + add(c, new Painting(stroke, color, shape, g.getTransform())); + } + + /** + * Adds a new pending painting to the list on the given component. This + * will find the first ancestor that has a {@link PendingPaintings} client + * property, starting with the component itself. + * + * @param c The component for which the painting is being added. + * @param newPainting The new painting. + */ + public static void add(JComponent c, Painting newPainting) { + PendingPaintings pending = pendingPaintingsFor(c); + if (pending != null) { + pending.paintings.add(newPainting); + } + } + + /** + * Returns the pending painting object for the given component, if any. This + * is retrieved from the first object found that has a {@link + * #PENDING_PAINTINGS} client property, starting with this component and + * looking up its ancestors (parent, parent's parent, etc.) + * <p/> + * This allows any descendant of a component that has a {@link + * PendingPaintings} property to add its own pending paintings. + * + * @param c The component for which the painting is being added. + * + * @return The pending painting object for that component, or <tt>null</tt> + * if there is none. + */ + public static PendingPaintings pendingPaintingsFor(JComponent c) { + for (Component parent = c; + parent != null; + parent = parent.getParent()) { + if (parent instanceof JComponent) { + JComponent jc = (JComponent) parent; + Object pd = jc.getClientProperty(PENDING_PAINTINGS); + if (pd != null) + return (PendingPaintings) pd; + } + } + return null; + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/hssf/view/brush/package.html b/src/examples/src/org/apache/poi/hssf/view/brush/package.html new file mode 100644 index 0000000000..dc4b833a66 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/view/brush/package.html @@ -0,0 +1,4 @@ +This package contains some brushes that are used when drawing borders for Excel +cells. + +@author Ken Arnold, Industrious Media LLC
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/ss/examples/html/HSSFHtmlHelper.java b/src/examples/src/org/apache/poi/ss/examples/html/HSSFHtmlHelper.java new file mode 100644 index 0000000000..1e235f929e --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/HSSFHtmlHelper.java @@ -0,0 +1,66 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.examples.html; + +import org.apache.poi.hssf.usermodel.HSSFCellStyle; +import org.apache.poi.hssf.usermodel.HSSFPalette; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.util.HSSFColor; +import org.apache.poi.ss.usermodel.CellStyle; + +import java.util.Formatter; + +/** + * Implementation of {@link HtmlHelper} for HSSF files. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class HSSFHtmlHelper implements HtmlHelper { + private final HSSFWorkbook wb; + private final HSSFPalette colors; + + private static final HSSFColor HSSF_AUTO = new HSSFColor.AUTOMATIC(); + + public HSSFHtmlHelper(HSSFWorkbook wb) { + this.wb = wb; + // If there is no custom palette, then this creates a new one that is + // a copy of the default + colors = wb.getCustomPalette(); + } + + public void colorStyles(CellStyle style, Formatter out) { + HSSFCellStyle cs = (HSSFCellStyle) style; + out.format(" /* fill pattern = %d */%n", cs.getFillPattern()); + styleColor(out, "background-color", cs.getFillForegroundColor()); + styleColor(out, "color", cs.getFont(wb).getColor()); + styleColor(out, "border-left-color", cs.getLeftBorderColor()); + styleColor(out, "border-right-color", cs.getRightBorderColor()); + styleColor(out, "border-top-color", cs.getTopBorderColor()); + styleColor(out, "border-bottom-color", cs.getBottomBorderColor()); + } + + private void styleColor(Formatter out, String attr, short index) { + HSSFColor color = colors.getColor(index); + if (index == HSSF_AUTO.getIndex() || color == null) { + out.format(" /* %s: index = %d */%n", attr, index); + } else { + short[] rgb = color.getTriplet(); + out.format(" %s: #%02x%02x%02x; /* index = %d */%n", attr, rgb[0], + rgb[1], rgb[2], index); + } + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/ss/examples/html/HtmlHelper.java b/src/examples/src/org/apache/poi/ss/examples/html/HtmlHelper.java new file mode 100644 index 0000000000..1d4c2a5b74 --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/HtmlHelper.java @@ -0,0 +1,23 @@ +package org.apache.poi.ss.examples.html; + +import org.apache.poi.ss.usermodel.CellStyle; + +import java.util.Formatter; + +/** + * This interface is used where code wants to be independent of the workbook + * formats. If you are writing such code, you can add a method to this + * interface, and then implement it for both HSSF and XSSF workbooks, letting + * the driving code stay independent of format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public interface HtmlHelper { + /** + * Outputs the appropriate CSS style for the given cell style. + * + * @param style The cell style. + * @param out The place to write the output. + */ + void colorStyles(CellStyle style, Formatter out); +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java b/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java new file mode 100644 index 0000000000..b5480cf59e --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java @@ -0,0 +1,443 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.examples.html; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFont; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.format.CellFormat; +import org.apache.poi.ss.format.CellFormatResult; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; + +import java.io.BufferedReader; +import java.io.Closeable; +import java.io.FileInputStream; +import java.io.FileWriter; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.io.PrintWriter; +import java.util.Formatter; +import java.util.HashMap; +import java.util.HashSet; +import java.util.Iterator; +import java.util.Map; +import java.util.Set; + +import static org.apache.poi.ss.usermodel.CellStyle.*; +import org.apache.poi.openxml4j.exceptions.InvalidFormatException; + +/** + * This example shows how to display a spreadsheet in HTML using the classes for + * spreadsheet display. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class ToHtml { + private final Workbook wb; + private final Appendable output; + private boolean completeHTML; + private Formatter out; + private boolean gotBounds; + private int firstColumn; + private int endColumn; + private HtmlHelper helper; + + private static final String DEFAULTS_CLASS = "excelDefaults"; + private static final String COL_HEAD_CLASS = "colHeader"; + private static final String ROW_HEAD_CLASS = "rowHeader"; + + private static final Map<Short, String> ALIGN = mapFor(ALIGN_LEFT, "left", + ALIGN_CENTER, "center", ALIGN_RIGHT, "right", ALIGN_FILL, "left", + ALIGN_JUSTIFY, "left", ALIGN_CENTER_SELECTION, "center"); + + private static final Map<Short, String> VERTICAL_ALIGN = mapFor( + VERTICAL_BOTTOM, "bottom", VERTICAL_CENTER, "middle", VERTICAL_TOP, + "top"); + + private static final Map<Short, String> BORDER = mapFor(BORDER_DASH_DOT, + "dashed 1pt", BORDER_DASH_DOT_DOT, "dashed 1pt", BORDER_DASHED, + "dashed 1pt", BORDER_DOTTED, "dotted 1pt", BORDER_DOUBLE, + "double 3pt", BORDER_HAIR, "solid 1px", BORDER_MEDIUM, "solid 2pt", + BORDER_MEDIUM_DASH_DOT, "dashed 2pt", BORDER_MEDIUM_DASH_DOT_DOT, + "dashed 2pt", BORDER_MEDIUM_DASHED, "dashed 2pt", BORDER_NONE, + "none", BORDER_SLANTED_DASH_DOT, "dashed 2pt", BORDER_THICK, + "solid 3pt", BORDER_THIN, "dashed 1pt"); + + @SuppressWarnings({"unchecked"}) + private static <K, V> Map<K, V> mapFor(Object... mapping) { + Map<K, V> map = new HashMap<K, V>(); + for (int i = 0; i < mapping.length; i += 2) { + map.put((K) mapping[i], (V) mapping[i + 1]); + } + return map; + } + + /** + * Creates a new converter to HTML for the given workbook. + * + * @param wb The workbook. + * @param output Where the HTML output will be written. + * + * @return An object for converting the workbook to HTML. + */ + public static ToHtml create(Workbook wb, Appendable output) { + return new ToHtml(wb, output); + } + + /** + * Creates a new converter to HTML for the given workbook. If the path ends + * with "<tt>.xlsx</tt>" an {@link XSSFWorkbook} will be used; otherwise + * this will use an {@link HSSFWorkbook}. + * + * @param path The file that has the workbook. + * @param output Where the HTML output will be written. + * + * @return An object for converting the workbook to HTML. + */ + public static ToHtml create(String path, Appendable output) + throws IOException { + return create(new FileInputStream(path), output); + } + + /** + * Creates a new converter to HTML for the given workbook. This attempts to + * detect whether the input is XML (so it should create an {@link + * XSSFWorkbook} or not (so it should create an {@link HSSFWorkbook}). + * + * @param in The input stream that has the workbook. + * @param output Where the HTML output will be written. + * + * @return An object for converting the workbook to HTML. + */ + public static ToHtml create(InputStream in, Appendable output) + throws IOException { + try { + Workbook wb = WorkbookFactory.create(in); + return create(wb, output); + } catch (InvalidFormatException e){ + throw new IllegalArgumentException("Cannot create workbook from stream", e); + } + } + + private ToHtml(Workbook wb, Appendable output) { + if (wb == null) + throw new NullPointerException("wb"); + if (output == null) + throw new NullPointerException("output"); + this.wb = wb; + this.output = output; + setupColorMap(); + } + + private void setupColorMap() { + if (wb instanceof HSSFWorkbook) + helper = new HSSFHtmlHelper((HSSFWorkbook) wb); + else if (wb instanceof XSSFWorkbook) + helper = new XSSFHtmlHelper((XSSFWorkbook) wb); + else + throw new IllegalArgumentException( + "unknown workbook type: " + wb.getClass().getSimpleName()); + } + + /** + * Run this class as a program + * + * @param args The command line arguments. + * + * @throws Exception Exception we don't recover from. + */ + public static void main(String[] args) throws Exception { + if(args.length < 2){ + System.err.println("usage: ToHtml inputWorkbook outputHtmlFile"); + return; + } + + ToHtml toHtml = create(args[0], new PrintWriter(new FileWriter(args[1]))); + toHtml.setCompleteHTML(true); + toHtml.printPage(); + } + + public void setCompleteHTML(boolean completeHTML) { + this.completeHTML = completeHTML; + } + + public void printPage() throws IOException { + try { + ensureOut(); + if (completeHTML) { + out.format( + "<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>%n"); + out.format("<html>%n"); + out.format("<head>%n"); + out.format("</head>%n"); + out.format("<body>%n"); + } + + print(); + + if (completeHTML) { + out.format("</body>%n"); + out.format("</html>%n"); + } + } finally { + if (out != null) + out.close(); + if (output instanceof Closeable) { + Closeable closeable = (Closeable) output; + closeable.close(); + } + } + } + + public void print() { + printInlineStyle(); + printSheets(); + } + + private void printInlineStyle() { + //out.format("<link href=\"excelStyle.css\" rel=\"stylesheet\" type=\"text/css\">%n"); + out.format("<style type=\"text/css\">%n"); + printStyles(); + out.format("</style>%n"); + } + + private void ensureOut() { + if (out == null) + out = new Formatter(output); + } + + public void printStyles() { + ensureOut(); + + // First, copy the base css + BufferedReader in = null; + try { + in = new BufferedReader(new InputStreamReader( + getClass().getResourceAsStream("excelStyle.css"))); + String line; + while ((line = in.readLine()) != null) { + out.format("%s%n", line); + } + } catch (IOException e) { + throw new IllegalStateException("Reading standard css", e); + } finally { + if (in != null) { + try { + in.close(); + } catch (IOException e) { + //noinspection ThrowFromFinallyBlock + throw new IllegalStateException("Reading standard css", e); + } + } + } + + // now add css for each used style + Set<CellStyle> seen = new HashSet<CellStyle>(); + for (int i = 0; i < wb.getNumberOfSheets(); i++) { + Sheet sheet = wb.getSheetAt(i); + Iterator<Row> rows = sheet.rowIterator(); + while (rows.hasNext()) { + Row row = rows.next(); + for (Cell cell : row) { + CellStyle style = cell.getCellStyle(); + if (!seen.contains(style)) { + printStyle(style); + seen.add(style); + } + } + } + } + } + + private void printStyle(CellStyle style) { + out.format(".%s .%s {%n", DEFAULTS_CLASS, styleName(style)); + styleContents(style); + out.format("}%n"); + } + + private void styleContents(CellStyle style) { + styleOut("text-align", style.getAlignment(), ALIGN); + styleOut("vertical-align", style.getAlignment(), VERTICAL_ALIGN); + fontStyle(style); + borderStyles(style); + helper.colorStyles(style, out); + } + + private void borderStyles(CellStyle style) { + styleOut("border-left", style.getBorderLeft(), BORDER); + styleOut("border-right", style.getBorderRight(), BORDER); + styleOut("border-top", style.getBorderTop(), BORDER); + styleOut("border-bottom", style.getBorderBottom(), BORDER); + } + + private void fontStyle(CellStyle style) { + Font font = wb.getFontAt(style.getFontIndex()); + + if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_NORMAL) + out.format(" font-weight: bold;%n"); + if (font.getItalic()) + out.format(" font-style: italic;%n"); + + int fontheight = font.getFontHeightInPoints(); + if (fontheight == 9) { + //fix for stupid ol Windows + fontheight = 10; + } + out.format(" font-size: %dpt;%n", fontheight); + + // Font color is handled with the other colors + } + + private String styleName(CellStyle style) { + if (style == null) + style = wb.getCellStyleAt((short) 0); + StringBuilder sb = new StringBuilder(); + Formatter fmt = new Formatter(sb); + fmt.format("style_%02x", style.getIndex()); + return fmt.toString(); + } + + private <K> void styleOut(String attr, K key, Map<K, String> mapping) { + String value = mapping.get(key); + if (value != null) { + out.format(" %s: %s;%n", attr, value); + } + } + + private static int ultimateCellType(Cell c) { + int type = c.getCellType(); + if (type == Cell.CELL_TYPE_FORMULA) + type = c.getCachedFormulaResultType(); + return type; + } + + private void printSheets() { + ensureOut(); + Sheet sheet = wb.getSheetAt(0); + printSheet(sheet); + } + + public void printSheet(Sheet sheet) { + ensureOut(); + out.format("<table class=%s>%n", DEFAULTS_CLASS); + printCols(sheet); + printSheetContent(sheet); + out.format("</table>%n"); + } + + private void printCols(Sheet sheet) { + out.format("<col/>%n"); + ensureColumnBounds(sheet); + for (int i = firstColumn; i < endColumn; i++) { + out.format("<col/>%n"); + } + } + + private void ensureColumnBounds(Sheet sheet) { + if (gotBounds) + return; + + Iterator<Row> iter = sheet.rowIterator(); + firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0); + endColumn = 0; + while (iter.hasNext()) { + Row row = iter.next(); + short firstCell = row.getFirstCellNum(); + if (firstCell >= 0) { + firstColumn = Math.min(firstColumn, firstCell); + endColumn = Math.max(endColumn, row.getLastCellNum()); + } + } + gotBounds = true; + } + + private void printColumnHeads() { + out.format("<thead>%n"); + out.format(" <tr class=%s>%n", COL_HEAD_CLASS); + out.format(" <th class=%s>◊</th>%n", COL_HEAD_CLASS); + //noinspection UnusedDeclaration + StringBuilder colName = new StringBuilder(); + for (int i = firstColumn; i < endColumn; i++) { + colName.setLength(0); + int cnum = i; + do { + colName.insert(0, (char) ('A' + cnum % 26)); + cnum /= 26; + } while (cnum > 0); + out.format(" <th class=%s>%s</th>%n", COL_HEAD_CLASS, colName); + } + out.format(" </tr>%n"); + out.format("</thead>%n"); + } + + private void printSheetContent(Sheet sheet) { + printColumnHeads(); + + out.format("<tbody>%n"); + Iterator<Row> rows = sheet.rowIterator(); + while (rows.hasNext()) { + Row row = rows.next(); + + out.format(" <tr>%n"); + out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, + row.getRowNum() + 1); + for (int i = firstColumn; i < endColumn; i++) { + String content = " "; + String attrs = ""; + CellStyle style = null; + if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { + Cell cell = row.getCell(i); + if (cell != null) { + style = cell.getCellStyle(); + attrs = tagStyle(cell, style); + //Set the value that is rendered for the cell + //also applies the format + CellFormat cf = CellFormat.getInstance( + style.getDataFormatString()); + CellFormatResult result = cf.apply(cell); + content = result.text; + if (content.equals("")) + content = " "; + } + } + out.format(" <td class=%s %s>%s</td>%n", styleName(style), + attrs, content); + } + out.format(" </tr>%n"); + } + out.format("</tbody>%n"); + } + + private String tagStyle(Cell cell, CellStyle style) { + if (style.getAlignment() == ALIGN_GENERAL) { + switch (ultimateCellType(cell)) { + case HSSFCell.CELL_TYPE_STRING: + return "style=\"text-align: left;\""; + case HSSFCell.CELL_TYPE_BOOLEAN: + case HSSFCell.CELL_TYPE_ERROR: + return "style=\"text-align: center;\""; + case HSSFCell.CELL_TYPE_NUMERIC: + default: + // "right" is the default + break; + } + } + return ""; + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/ss/examples/html/XSSFHtmlHelper.java b/src/examples/src/org/apache/poi/ss/examples/html/XSSFHtmlHelper.java new file mode 100644 index 0000000000..36fca3a9b9 --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/XSSFHtmlHelper.java @@ -0,0 +1,64 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.examples.html; + +import org.apache.poi.hssf.util.HSSFColor; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.xssf.usermodel.XSSFCellStyle; +import org.apache.poi.xssf.usermodel.XSSFColor; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; + +import java.util.Formatter; +import java.util.Hashtable; + +/** + * Implementation of {@link HtmlHelper} for XSSF files. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class XSSFHtmlHelper implements HtmlHelper { + private final XSSFWorkbook wb; + + private static final Hashtable colors = HSSFColor.getIndexHash(); + + public XSSFHtmlHelper(XSSFWorkbook wb) { + this.wb = wb; + } + + public void colorStyles(CellStyle style, Formatter out) { + XSSFCellStyle cs = (XSSFCellStyle) style; + styleColor(out, "background-color", cs.getFillForegroundXSSFColor()); + styleColor(out, "text-color", cs.getFont().getXSSFColor()); + } + + private void styleColor(Formatter out, String attr, XSSFColor color) { + if (color == null || color.isAuto()) + return; + + byte[] rgb = color.getRgb(); + if (rgb == null) { + return; + } + + // This is done twice -- rgba is new with CSS 3, and browser that don't + // support it will ignore the rgba specification and stick with the + // solid color, which is declared first + out.format(" %s: #%02x%02x%02x;%n", attr, rgb[0], rgb[1], rgb[2]); + out.format(" %s: rgba(0x%02x, 0x%02x, 0x%02x, 0x%02x);%n", attr, + rgb[0], rgb[1], rgb[2], rgb[3]); + } +}
\ No newline at end of file diff --git a/src/examples/src/org/apache/poi/ss/examples/html/excelStyle.css b/src/examples/src/org/apache/poi/ss/examples/html/excelStyle.css new file mode 100644 index 0000000000..e428918b30 --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/excelStyle.css @@ -0,0 +1,54 @@ +/* + * This is the default style sheet for html generated by ToHtml + * + * @author Ken Arnold, Industrious Media LLC + */ +.excelDefaults { + background-color: white; + color: black; + text-decoration: none; + direction: ltr; + text-transform: none; + text-indent: 0; + letter-spacing: 0; + word-spacing: 0; + white-space: normal; + unicode-bidi: normal; + vertical-align: 0; + background-image: none; + text-shadow: none; + list-style-image: none; + list-style-type: none; + padding: 0; + margin: 0; + border-collapse: collapse; + white-space: pre; + vertical-align: bottom; + font-style: normal; + font-family: sans-serif; + font-variant: normal; + font-weight: normal; + font-size: 10pt; + text-align: right; +} + +.excelDefaults td { + padding: 1px 5px; + border: 1px solid silver; +} + +.excelDefaults .colHeader { + background-color: silver; + font-weight: bold; + border: 1px solid black; + text-align: center; + padding: 1px 5px; +} + +.excelDefaults .rowHeader { + background-color: silver; + font-weight: bold; + border: 1px solid black; + text-align: right; + padding: 1px 5px; +} diff --git a/src/examples/src/org/apache/poi/ss/examples/html/package.html b/src/examples/src/org/apache/poi/ss/examples/html/package.html new file mode 100644 index 0000000000..b041119bb0 --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/html/package.html @@ -0,0 +1,2 @@ +This package contains an example that uses POI to convert a workbook into +an HTML representation of the data. It can use both XSSF and HSSF workbooks.
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellDateFormatter.java b/src/java/org/apache/poi/ss/format/CellDateFormatter.java new file mode 100644 index 0000000000..5007305e21 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellDateFormatter.java @@ -0,0 +1,213 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.text.AttributedCharacterIterator; +import java.text.CharacterIterator; +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.Formatter; +import java.util.regex.Matcher; + +/** + * Formats a date value. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellDateFormatter extends CellFormatter { + private boolean amPmUpper; + private boolean showM; + private boolean showAmPm; + private final DateFormat dateFmt; + private String sFmt; + + private static final long EXCEL_EPOCH_TIME; + private static final Date EXCEL_EPOCH_DATE; + + private static final CellFormatter SIMPLE_DATE = new CellDateFormatter( + "mm/d/y"); + + static { + Calendar c = Calendar.getInstance(); + c.set(1904, 0, 1, 0, 0, 0); + EXCEL_EPOCH_DATE = c.getTime(); + EXCEL_EPOCH_TIME = c.getTimeInMillis(); + } + + private class DatePartHandler implements CellFormatPart.PartHandler { + private int mStart = -1; + private int mLen; + private int hStart = -1; + private int hLen; + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case 's': + case 'S': + if (mStart >= 0) { + for (int i = 0; i < mLen; i++) + desc.setCharAt(mStart + i, 'm'); + mStart = -1; + } + return part.toLowerCase(); + + case 'h': + case 'H': + mStart = -1; + hStart = pos; + hLen = part.length(); + return part.toLowerCase(); + + case 'd': + case 'D': + mStart = -1; + if (part.length() <= 2) + return part.toLowerCase(); + else + return part.toLowerCase().replace('d', 'E'); + + case 'm': + case 'M': + mStart = pos; + mLen = part.length(); + return part.toUpperCase(); + + case 'y': + case 'Y': + mStart = -1; + if (part.length() == 3) + part = "yyyy"; + return part.toLowerCase(); + + case '0': + mStart = -1; + int sLen = part.length(); + sFmt = "%0" + (sLen + 2) + "." + sLen + "f"; + return part.replace('0', 'S'); + + case 'a': + case 'A': + case 'p': + case 'P': + if (part.length() > 1) { + // am/pm marker + mStart = -1; + showAmPm = true; + showM = Character.toLowerCase(part.charAt(1)) == 'm'; + // For some reason "am/pm" becomes AM or PM, but "a/p" becomes a or p + amPmUpper = showM || Character.isUpperCase(part.charAt(0)); + + return "a"; + } + //noinspection fallthrough + + default: + return null; + } + } + + public void finish(StringBuffer toAppendTo) { + if (hStart >= 0 && !showAmPm) { + for (int i = 0; i < hLen; i++) { + toAppendTo.setCharAt(hStart + i, 'H'); + } + } + } + } + + /** + * Creates a new date formatter with the given specification. + * + * @param format The format. + */ + public CellDateFormatter(String format) { + super(format); + DatePartHandler partHandler = new DatePartHandler(); + StringBuffer descBuf = CellFormatPart.parseFormat(format, + CellFormatType.DATE, partHandler); + partHandler.finish(descBuf); + dateFmt = new SimpleDateFormat(descBuf.toString()); + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value == null) + value = 0.0; + if (value instanceof Number) { + Number num = (Number) value; + double v = num.doubleValue(); + if (v == 0.0) + value = EXCEL_EPOCH_DATE; + else + value = new Date((long) (EXCEL_EPOCH_TIME + v)); + } + + AttributedCharacterIterator it = dateFmt.formatToCharacterIterator( + value); + boolean doneAm = false; + boolean doneMillis = false; + + it.first(); + for (char ch = it.first(); + ch != CharacterIterator.DONE; + ch = it.next()) { + if (it.getAttribute(DateFormat.Field.MILLISECOND) != null) { + if (!doneMillis) { + Date dateObj = (Date) value; + int pos = toAppendTo.length(); + Formatter formatter = new Formatter(toAppendTo); + long msecs = dateObj.getTime() % 1000; + formatter.format(LOCALE, sFmt, msecs / 1000.0); + toAppendTo.delete(pos, pos + 2); + doneMillis = true; + } + } else if (it.getAttribute(DateFormat.Field.AM_PM) != null) { + if (!doneAm) { + if (showAmPm) { + if (amPmUpper) { + toAppendTo.append(Character.toUpperCase(ch)); + if (showM) + toAppendTo.append('M'); + } else { + toAppendTo.append(Character.toLowerCase(ch)); + if (showM) + toAppendTo.append('m'); + } + } + doneAm = true; + } + } else { + toAppendTo.append(ch); + } + } + } + + /** + * {@inheritDoc} + * <p/> + * For a date, this is <tt>"mm/d/y"</tt>. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_DATE.formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java b/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java new file mode 100644 index 0000000000..07ebadf2a0 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java @@ -0,0 +1,215 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.util.ArrayList; +import java.util.Formatter; +import java.util.List; +import java.util.ListIterator; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * This class implements printing out an elapsed time format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellElapsedFormatter extends CellFormatter { + private final List<TimeSpec> specs; + private TimeSpec topmost; + private final String printfFmt; + + private static final Pattern PERCENTS = Pattern.compile("%"); + + private static final double HOUR__FACTOR = 1.0 / 24.0; + private static final double MIN__FACTOR = HOUR__FACTOR / 60.0; + private static final double SEC__FACTOR = MIN__FACTOR / 60.0; + + private static class TimeSpec { + final char type; + final int pos; + final int len; + final double factor; + double modBy; + + public TimeSpec(char type, int pos, int len, double factor) { + this.type = type; + this.pos = pos; + this.len = len; + this.factor = factor; + modBy = 0; + } + + public long valueFor(double elapsed) { + double val; + if (modBy == 0) + val = elapsed / factor; + else + val = elapsed / factor % modBy; + if (type == '0') + return Math.round(val); + else + return (long) val; + } + } + + private class ElapsedPartHandler implements CellFormatPart.PartHandler { + // This is the one class that's directly using printf, so it can't use + // the default handling for quoted strings and special characters. The + // only special character for this is '%', so we have to handle all the + // quoting in this method ourselves. + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case '[': + if (part.length() < 3) + break; + if (topmost != null) + throw new IllegalArgumentException( + "Duplicate '[' times in format"); + part = part.toLowerCase(); + int specLen = part.length() - 2; + topmost = assignSpec(part.charAt(1), pos, specLen); + return part.substring(1, 1 + specLen); + + case 'h': + case 'm': + case 's': + case '0': + part = part.toLowerCase(); + assignSpec(part.charAt(0), pos, part.length()); + return part; + + case '\n': + return "%n"; + + case '\"': + part = part.substring(1, part.length() - 1); + break; + + case '\\': + part = part.substring(1); + break; + + case '*': + if (part.length() > 1) + part = CellFormatPart.expandChar(part); + break; + + // An escape we can let it handle because it can't have a '%' + case '_': + return null; + } + // Replace ever "%" with a "%%" so we can use printf + return PERCENTS.matcher(part).replaceAll("%%"); + } + } + + /** + * Creates a elapsed time formatter. + * + * @param pattern The pattern to parse. + */ + public CellElapsedFormatter(String pattern) { + super(pattern); + + specs = new ArrayList<TimeSpec>(); + + StringBuffer desc = CellFormatPart.parseFormat(pattern, + CellFormatType.ELAPSED, new ElapsedPartHandler()); + + ListIterator<TimeSpec> it = specs.listIterator(specs.size()); + while (it.hasPrevious()) { + TimeSpec spec = it.previous(); + desc.replace(spec.pos, spec.pos + spec.len, "%0" + spec.len + "d"); + if (spec.type != topmost.type) { + spec.modBy = modFor(spec.type, spec.len); + } + } + + printfFmt = desc.toString(); + } + + private TimeSpec assignSpec(char type, int pos, int len) { + TimeSpec spec = new TimeSpec(type, pos, len, factorFor(type, len)); + specs.add(spec); + return spec; + } + + private static double factorFor(char type, int len) { + switch (type) { + case 'h': + return HOUR__FACTOR; + case 'm': + return MIN__FACTOR; + case 's': + return SEC__FACTOR; + case '0': + return SEC__FACTOR / Math.pow(10, len); + default: + throw new IllegalArgumentException( + "Uknown elapsed time spec: " + type); + } + } + + private static double modFor(char type, int len) { + switch (type) { + case 'h': + return 24; + case 'm': + return 60; + case 's': + return 60; + case '0': + return Math.pow(10, len); + default: + throw new IllegalArgumentException( + "Uknown elapsed time spec: " + type); + } + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object value) { + double elapsed = ((Number) value).doubleValue(); + + if (elapsed < 0) { + toAppendTo.append('-'); + elapsed = -elapsed; + } + + Object[] parts = new Long[specs.size()]; + for (int i = 0; i < specs.size(); i++) { + parts[i] = specs.get(i).valueFor(elapsed); + } + + Formatter formatter = new Formatter(toAppendTo); + formatter.format(printfFmt, parts); + } + + /** + * {@inheritDoc} + * <p/> + * For a date, this is <tt>"mm/d/y"</tt>. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormat.java b/src/java/org/apache/poi/ss/format/CellFormat.java new file mode 100644 index 0000000000..c7c22f004e --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormat.java @@ -0,0 +1,313 @@ +/* ==================================================================== + 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. +==================================================================== */ + +package org.apache.poi.ss.format; + +import org.apache.poi.ss.usermodel.Cell; + +import javax.swing.*; +import java.util.ArrayList; +import java.util.List; +import java.util.Map; +import java.util.WeakHashMap; +import java.util.logging.Level; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * Format a value according to the standard Excel behavior. This "standard" is + * not explicitly documented by Microsoft, so the behavior is determined by + * experimentation; see the tests. + * <p/> + * An Excel format has up to four parts, separated by semicolons. Each part + * specifies what to do with particular kinds of values, depending on the number + * of parts given: <dl> <dt>One part (example: <tt>[Green]#.##</tt>) <dd>If the + * value is a number, display according to this one part (example: green text, + * with up to two decimal points). If the value is text, display it as is. + * <dt>Two parts (example: <tt>[Green]#.##;[Red]#.##</tt>) <dd>If the value is a + * positive number or zero, display according to the first part (example: green + * text, with up to two decimal points); if it is a negative number, display + * according to the second part (example: red text, with up to two decimal + * points). If the value is text, display it as is. <dt>Three parts (example: + * <tt>[Green]#.##;[Black]#.##;[Red]#.##</tt>) <dd>If the value is a positive + * number, display according to the first part (example: green text, with up to + * two decimal points); if it is zero, display according to the second part + * (example: black text, with up to two decimal points); if it is a negative + * number, display according to the third part (example: red text, with up to + * two decimal points). If the value is text, display it as is. <dt>Four parts + * (example: <tt>[Green]#.##;[Black]#.##;[Red]#.##;[@]</tt>) <dd>If the value is + * a positive number, display according to the first part (example: green text, + * with up to two decimal points); if it is zero, display according to the + * second part (example: black text, with up to two decimal points); if it is a + * negative number, display according to the third part (example: red text, with + * up to two decimal points). If the value is text, display according to the + * fourth part (example: text in the cell's usual color, with the text value + * surround by brackets). </dl> + * <p/> + * In addition to these, there is a general format that is used when no format + * is specified. This formatting is presented by the {@link #GENERAL_FORMAT} + * object. + * + * @author Ken Arnold, Industrious Media LLC + */ +@SuppressWarnings({"Singleton"}) +public class CellFormat { + private final String format; + private final CellFormatPart posNumFmt; + private final CellFormatPart zeroNumFmt; + private final CellFormatPart negNumFmt; + private final CellFormatPart textFmt; + + private static final Pattern ONE_PART = Pattern.compile( + CellFormatPart.FORMAT_PAT.pattern() + "(;|$)", + Pattern.COMMENTS | Pattern.CASE_INSENSITIVE); + + private static final CellFormatPart DEFAULT_TEXT_FORMAT = + new CellFormatPart("@"); + + /** + * Format a value as it would be were no format specified. This is also + * used when the format specified is <tt>General</tt>. + */ + public static final CellFormat GENERAL_FORMAT = new CellFormat("General") { + @Override + public CellFormatResult apply(Object value) { + String text; + if (value == null) { + text = ""; + } else if (value instanceof Number) { + text = CellNumberFormatter.SIMPLE_NUMBER.format(value); + } else { + text = value.toString(); + } + return new CellFormatResult(true, text, null); + } + }; + + /** Maps a format string to its parsed version for efficiencies sake. */ + private static final Map<String, CellFormat> formatCache = + new WeakHashMap<String, CellFormat>(); + + /** + * Returns a {@link CellFormat} that applies the given format. Two calls + * with the same format may or may not return the same object. + * + * @param format The format. + * + * @return A {@link CellFormat} that applies the given format. + */ + public static CellFormat getInstance(String format) { + CellFormat fmt = formatCache.get(format); + if (fmt == null) { + if (format.equals("General")) + fmt = GENERAL_FORMAT; + else + fmt = new CellFormat(format); + formatCache.put(format, fmt); + } + return fmt; + } + + /** + * Creates a new object. + * + * @param format The format. + */ + private CellFormat(String format) { + this.format = format; + Matcher m = ONE_PART.matcher(format); + List<CellFormatPart> parts = new ArrayList<CellFormatPart>(); + + while (m.find()) { + try { + String valueDesc = m.group(); + + // Strip out the semicolon if it's there + if (valueDesc.endsWith(";")) + valueDesc = valueDesc.substring(0, valueDesc.length() - 1); + + parts.add(new CellFormatPart(valueDesc)); + } catch (RuntimeException e) { + CellFormatter.logger.log(Level.WARNING, + "Invalid format: " + CellFormatter.quote(m.group()), e); + parts.add(null); + } + } + + switch (parts.size()) { + case 1: + posNumFmt = zeroNumFmt = negNumFmt = parts.get(0); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 2: + posNumFmt = zeroNumFmt = parts.get(0); + negNumFmt = parts.get(1); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 3: + posNumFmt = parts.get(0); + zeroNumFmt = parts.get(1); + negNumFmt = parts.get(2); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 4: + default: + posNumFmt = parts.get(0); + zeroNumFmt = parts.get(1); + negNumFmt = parts.get(2); + textFmt = parts.get(3); + break; + } + } + + /** + * Returns the result of applying the format to the given value. If the + * value is a number (a type of {@link Number} object), the correct number + * format type is chosen; otherwise it is considered a text object. + * + * @param value The value + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(Object value) { + if (value instanceof Number) { + Number num = (Number) value; + double val = num.doubleValue(); + if (val > 0) + return posNumFmt.apply(value); + else if (val < 0) + return negNumFmt.apply(-val); + else + return zeroNumFmt.apply(value); + } else { + return textFmt.apply(value); + } + } + + /** + * Fetches the appropriate value from the cell, and returns the result of + * applying it to the appropriate format. For formula cells, the computed + * value is what is used. + * + * @param c The cell. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(Cell c) { + switch (ultimateType(c)) { + case Cell.CELL_TYPE_BLANK: + return apply(""); + case Cell.CELL_TYPE_BOOLEAN: + return apply(c.getStringCellValue()); + case Cell.CELL_TYPE_NUMERIC: + return apply(c.getNumericCellValue()); + case Cell.CELL_TYPE_STRING: + return apply(c.getStringCellValue()); + default: + return apply("?"); + } + } + + /** + * Uses the result of applying this format to the value, setting the text + * and color of a label before returning the result. + * + * @param label The label to apply to. + * @param value The value to process. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(JLabel label, Object value) { + CellFormatResult result = apply(value); + label.setText(result.text); + if (result.textColor != null) { + label.setForeground(result.textColor); + } + return result; + } + + /** + * Fetches the appropriate value from the cell, and uses the result, setting + * the text and color of a label before returning the result. + * + * @param label The label to apply to. + * @param c The cell. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(JLabel label, Cell c) { + switch (ultimateType(c)) { + case Cell.CELL_TYPE_BLANK: + return apply(label, ""); + case Cell.CELL_TYPE_BOOLEAN: + return apply(label, c.getStringCellValue()); + case Cell.CELL_TYPE_NUMERIC: + return apply(label, c.getNumericCellValue()); + case Cell.CELL_TYPE_STRING: + return apply(label, c.getStringCellValue()); + default: + return apply(label, "?"); + } + } + + /** + * Returns the ultimate cell type, following the results of formulas. If + * the cell is a {@link Cell#CELL_TYPE_FORMULA}, this returns the result of + * {@link Cell#getCachedFormulaResultType()}. Otherwise this returns the + * result of {@link Cell#getCellType()}. + * + * @param cell The cell. + * + * @return The ultimate type of this cell. + */ + public static int ultimateType(Cell cell) { + int type = cell.getCellType(); + if (type == Cell.CELL_TYPE_FORMULA) + return cell.getCachedFormulaResultType(); + else + return type; + } + + /** + * Returns <tt>true</tt> if the other object is a {@link CellFormat} object + * with the same format. + * + * @param obj The other object. + * + * @return <tt>true</tt> if the two objects are equal. + */ + @Override + public boolean equals(Object obj) { + if (this == obj) + return true; + if (obj instanceof CellFormat) { + CellFormat that = (CellFormat) obj; + return format.equals(that.format); + } + return false; + } + + /** + * Returns a hash code for the format. + * + * @return A hash code for the format. + */ + @Override + public int hashCode() { + return format.hashCode(); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatCondition.java b/src/java/org/apache/poi/ss/format/CellFormatCondition.java new file mode 100644 index 0000000000..23fd2f2e7a --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatCondition.java @@ -0,0 +1,121 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.util.HashMap; +import java.util.Map; + +/** + * This object represents a condition in a cell format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public abstract class CellFormatCondition { + private static final int LT = 0; + private static final int LE = 1; + private static final int GT = 2; + private static final int GE = 3; + private static final int EQ = 4; + private static final int NE = 5; + + private static final Map<String, Integer> TESTS; + + static { + TESTS = new HashMap<String, Integer>(); + TESTS.put("<", LT); + TESTS.put("<=", LE); + TESTS.put(">", GT); + TESTS.put(">=", GE); + TESTS.put("=", EQ); + TESTS.put("==", EQ); + TESTS.put("!=", NE); + TESTS.put("<>", NE); + } + + /** + * Returns an instance of a condition object. + * + * @param opString The operator as a string. One of <tt>"<"</tt>, + * <tt>"<="</tt>, <tt>">"</tt>, <tt>">="</tt>, + * <tt>"="</tt>, <tt>"=="</tt>, <tt>"!="</tt>, or + * <tt>"<>"</tt>. + * @param constStr The constant (such as <tt>"12"</tt>). + * + * @return A condition object for the given condition. + */ + public static CellFormatCondition getInstance(String opString, + String constStr) { + + if (!TESTS.containsKey(opString)) + throw new IllegalArgumentException("Unknown test: " + opString); + int test = TESTS.get(opString); + + final double c = Double.parseDouble(constStr); + + switch (test) { + case LT: + return new CellFormatCondition() { + public boolean pass(double value) { + return value < c; + } + }; + case LE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value <= c; + } + }; + case GT: + return new CellFormatCondition() { + public boolean pass(double value) { + return value > c; + } + }; + case GE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value >= c; + } + }; + case EQ: + return new CellFormatCondition() { + public boolean pass(double value) { + return value == c; + } + }; + case NE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value != c; + } + }; + default: + throw new IllegalArgumentException( + "Cannot create for test number " + test + "(\"" + opString + + "\")"); + } + } + + /** + * Returns <tt>true</tt> if the given value passes the constraint's test. + * + * @param value The value to compare against. + * + * @return <tt>true</tt> if the given value passes the constraint's test. + */ + public abstract boolean pass(double value); +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatPart.java b/src/java/org/apache/poi/ss/format/CellFormatPart.java new file mode 100644 index 0000000000..cedc94fe27 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatPart.java @@ -0,0 +1,494 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import org.apache.poi.hssf.util.HSSFColor; + +import javax.swing.*; +import java.awt.*; +import java.util.Map; +import java.util.TreeMap; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import static org.apache.poi.ss.format.CellFormatter.logger; +import static org.apache.poi.ss.format.CellFormatter.quote; + +/** + * Objects of this class represent a single part of a cell format expression. + * Each cell can have up to four of these for positive, zero, negative, and text + * values. + * <p/> + * Each format part can contain a color, a condition, and will always contain a + * format specification. For example <tt>"[Red][>=10]#"</tt> has a color + * (<tt>[Red]</tt>), a condition (<tt>>=10</tt>) and a format specification + * (<tt>#</tt>). + * <p/> + * This class also contains patterns for matching the subparts of format + * specification. These are used internally, but are made public in case other + * code has use for them. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellFormatPart { + private final Color color; + private CellFormatCondition condition; + private final CellFormatter format; + + private static final Map<String, Color> NAMED_COLORS; + + static { + NAMED_COLORS = new TreeMap<String, Color>( + String.CASE_INSENSITIVE_ORDER); + + Map colors = HSSFColor.getIndexHash(); + for (Object val : colors.values()) { + HSSFColor color = (HSSFColor) val; + Class type = color.getClass(); + String name = type.getSimpleName(); + if (name.equals(name.toUpperCase())) { + short[] rgb = color.getTriplet(); + Color c = new Color(rgb[0], rgb[1], rgb[2]); + NAMED_COLORS.put(name, c); + if (name.indexOf('_') > 0) + NAMED_COLORS.put(name.replace('_', ' '), c); + if (name.indexOf("_PERCENT") > 0) + NAMED_COLORS.put(name.replace("_PERCENT", "%").replace('_', + ' '), c); + } + } + } + + /** Pattern for the color part of a cell format part. */ + public static final Pattern COLOR_PAT; + /** Pattern for the condition part of a cell format part. */ + public static final Pattern CONDITION_PAT; + /** Pattern for the format specification part of a cell format part. */ + public static final Pattern SPECIFICATION_PAT; + /** Pattern for an entire cell single part. */ + public static final Pattern FORMAT_PAT; + + /** Within {@link #FORMAT_PAT}, the group number for the matched color. */ + public static final int COLOR_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the operator in the + * condition. + */ + public static final int CONDITION_OPERATOR_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the value in the + * condition. + */ + public static final int CONDITION_VALUE_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the format + * specification. + */ + public static final int SPECIFICATION_GROUP; + + static { + // A condition specification + String condition = "([<>=]=?|!=|<>) # The operator\n" + + " \\s*([0-9]+(?:\\.[0-9]*)?)\\s* # The constant to test against\n"; + + String color = + "\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)\\]"; + + // A number specification + // Note: careful that in something like ##, that the trailing comma is not caught up in the integer part + + // A part of a specification + String part = "\\\\. # Quoted single character\n" + + "|\"([^\\\\\"]|\\\\.)*\" # Quoted string of characters (handles escaped quotes like \\\") \n" + + "|_. # Space as wide as a given character\n" + + "|\\*. # Repeating fill character\n" + + "|@ # Text: cell text\n" + + "|([0?\\#](?:[0?\\#,]*)) # Number: digit + other digits and commas\n" + + "|e[-+] # Number: Scientific: Exponent\n" + + "|m{1,5} # Date: month or minute spec\n" + + "|d{1,4} # Date: day/date spec\n" + + "|y{2,4} # Date: year spec\n" + + "|h{1,2} # Date: hour spec\n" + + "|s{1,2} # Date: second spec\n" + + "|am?/pm? # Date: am/pm spec\n" + + "|\\[h{1,2}\\] # Elapsed time: hour spec\n" + + "|\\[m{1,2}\\] # Elapsed time: minute spec\n" + + "|\\[s{1,2}\\] # Elapsed time: second spec\n" + + "|[^;] # A character\n" + ""; + + String format = "(?:" + color + ")? # Text color\n" + + "(?:\\[" + condition + "\\])? # Condition\n" + + "((?:" + part + ")+) # Format spec\n"; + + int flags = Pattern.COMMENTS | Pattern.CASE_INSENSITIVE; + COLOR_PAT = Pattern.compile(color, flags); + CONDITION_PAT = Pattern.compile(condition, flags); + SPECIFICATION_PAT = Pattern.compile(part, flags); + FORMAT_PAT = Pattern.compile(format, flags); + + // Calculate the group numbers of important groups. (They shift around + // when the pattern is changed; this way we figure out the numbers by + // experimentation.) + + COLOR_GROUP = findGroup(FORMAT_PAT, "[Blue]@", "Blue"); + CONDITION_OPERATOR_GROUP = findGroup(FORMAT_PAT, "[>=1]@", ">="); + CONDITION_VALUE_GROUP = findGroup(FORMAT_PAT, "[>=1]@", "1"); + SPECIFICATION_GROUP = findGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a ?"); + } + + interface PartHandler { + String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc); + } + + /** + * Create an object to represent a format part. + * + * @param desc The string to parse. + */ + public CellFormatPart(String desc) { + Matcher m = FORMAT_PAT.matcher(desc); + if (!m.matches()) { + throw new IllegalArgumentException("Unrecognized format: " + quote( + desc)); + } + color = getColor(m); + condition = getCondition(m); + format = getFormatter(m); + } + + /** + * Returns <tt>true</tt> if this format part applies to the given value. If + * the value is a number and this is part has a condition, returns + * <tt>true</tt> only if the number passes the condition. Otherwise, this + * allways return <tt>true</tt>. + * + * @param valueObject The value to evaluate. + * + * @return <tt>true</tt> if this format part applies to the given value. + */ + public boolean applies(Object valueObject) { + if (condition == null || !(valueObject instanceof Number)) { + if (valueObject == null) + throw new NullPointerException("valueObject"); + return true; + } else { + Number num = (Number) valueObject; + return condition.pass(num.doubleValue()); + } + } + + /** + * Returns the number of the first group that is the same as the marker + * string. The search starts with group 1. + * + * @param pat The pattern to use. + * @param str The string to match against the pattern. + * @param marker The marker value to find the group of. + * + * @return The matching group number. + * + * @throws IllegalArgumentException No group matches the marker. + */ + private static int findGroup(Pattern pat, String str, String marker) { + Matcher m = pat.matcher(str); + if (!m.find()) + throw new IllegalArgumentException( + "Pattern \"" + pat.pattern() + "\" doesn't match \"" + str + + "\""); + for (int i = 1; i <= m.groupCount(); i++) { + String grp = m.group(i); + if (grp != null && grp.equals(marker)) + return i; + } + throw new IllegalArgumentException( + "\"" + marker + "\" not found in \"" + pat.pattern() + "\""); + } + + /** + * Returns the color specification from the matcher, or <tt>null</tt> if + * there is none. + * + * @param m The matcher for the format part. + * + * @return The color specification or <tt>null</tt>. + */ + private static Color getColor(Matcher m) { + String cdesc = m.group(COLOR_GROUP); + if (cdesc == null || cdesc.length() == 0) + return null; + Color c = NAMED_COLORS.get(cdesc); + if (c == null) + logger.warning("Unknown color: " + quote(cdesc)); + return c; + } + + /** + * Returns the condition specification from the matcher, or <tt>null</tt> if + * there is none. + * + * @param m The matcher for the format part. + * + * @return The condition specification or <tt>null</tt>. + */ + private CellFormatCondition getCondition(Matcher m) { + String mdesc = m.group(CONDITION_OPERATOR_GROUP); + if (mdesc == null || mdesc.length() == 0) + return null; + return CellFormatCondition.getInstance(m.group( + CONDITION_OPERATOR_GROUP), m.group(CONDITION_VALUE_GROUP)); + } + + /** + * Returns the formatter object implied by the format specification for the + * format part. + * + * @param matcher The matcher for the format part. + * + * @return The formatter. + */ + private CellFormatter getFormatter(Matcher matcher) { + String fdesc = matcher.group(SPECIFICATION_GROUP); + CellFormatType type = formatType(fdesc); + return type.formatter(fdesc); + } + + /** + * Returns the type of format. + * + * @param fdesc The format specification + * + * @return The type of format. + */ + private CellFormatType formatType(String fdesc) { + fdesc = fdesc.trim(); + if (fdesc.equals("") || fdesc.equalsIgnoreCase("General")) + return CellFormatType.GENERAL; + + Matcher m = SPECIFICATION_PAT.matcher(fdesc); + boolean couldBeDate = false; + boolean seenZero = false; + while (m.find()) { + String repl = m.group(0); + if (repl.length() > 0) { + switch (repl.charAt(0)) { + case '@': + return CellFormatType.TEXT; + case 'd': + case 'D': + case 'y': + case 'Y': + return CellFormatType.DATE; + case 'h': + case 'H': + case 'm': + case 'M': + case 's': + case 'S': + // These can be part of date, or elapsed + couldBeDate = true; + break; + case '0': + // This can be part of date, elapsed, or number + seenZero = true; + break; + case '[': + return CellFormatType.ELAPSED; + case '#': + case '?': + return CellFormatType.NUMBER; + } + } + } + + // Nothing definitive was found, so we figure out it deductively + if (couldBeDate) + return CellFormatType.DATE; + if (seenZero) + return CellFormatType.NUMBER; + return CellFormatType.TEXT; + } + + /** + * Returns a version of the original string that has any special characters + * quoted (or escaped) as appropriate for the cell format type. The format + * type object is queried to see what is special. + * + * @param repl The original string. + * @param type The format type representation object. + * + * @return A version of the string with any special characters replaced. + * + * @see CellFormatType#isSpecial(char) + */ + static String quoteSpecial(String repl, CellFormatType type) { + StringBuilder sb = new StringBuilder(); + for (int i = 0; i < repl.length(); i++) { + char ch = repl.charAt(i); + if (ch == '\'' && type.isSpecial('\'')) { + sb.append('\u0000'); + continue; + } + + boolean special = type.isSpecial(ch); + if (special) + sb.append("'"); + sb.append(ch); + if (special) + sb.append("'"); + } + return sb.toString(); + } + + /** + * Apply this format part to the given value. This returns a {@link + * CellFormatResult} object with the results. + * + * @param value The value to apply this format part to. + * + * @return A {@link CellFormatResult} object containing the results of + * applying the format to the value. + */ + public CellFormatResult apply(Object value) { + boolean applies = applies(value); + String text; + Color textColor; + if (applies) { + text = format.format(value); + textColor = color; + } else { + text = format.simpleFormat(value); + textColor = null; + } + return new CellFormatResult(applies, text, textColor); + } + + /** + * Apply this format part to the given value, applying the result to the + * given label. + * + * @param label The label + * @param value The value to apply this format part to. + * + * @return <tt>true</tt> if the + */ + public CellFormatResult apply(JLabel label, Object value) { + CellFormatResult result = apply(value); + label.setText(result.text); + if (result.textColor != null) { + label.setForeground(result.textColor); + } + return result; + } + + public static StringBuffer parseFormat(String fdesc, CellFormatType type, + PartHandler partHandler) { + + // Quoting is very awkward. In the Java classes, quoting is done + // between ' chars, with '' meaning a single ' char. The problem is that + // in Excel, it is legal to have two adjacent escaped strings. For + // example, consider the Excel format "\a\b#". The naive (and easy) + // translation into Java DecimalFormat is "'a''b'#". For the number 17, + // in Excel you would get "ab17", but in Java it would be "a'b17" -- the + // '' is in the middle of the quoted string in Java. So the trick we + // use is this: When we encounter a ' char in the Excel format, we + // output a \u0000 char into the string. Now we know that any '' in the + // output is the result of two adjacent escaped strings. So after the + // main loop, we have to do two passes: One to eliminate any '' + // sequences, to make "'a''b'" become "'ab'", and another to replace any + // \u0000 with '' to mean a quote char. Oy. + // + // For formats that don't use "'" we don't do any of this + Matcher m = SPECIFICATION_PAT.matcher(fdesc); + StringBuffer fmt = new StringBuffer(); + while (m.find()) { + String part = group(m, 0); + if (part.length() > 0) { + String repl = partHandler.handlePart(m, part, type, fmt); + if (repl == null) { + switch (part.charAt(0)) { + case '\"': + repl = quoteSpecial(part.substring(1, + part.length() - 1), type); + break; + case '\\': + repl = quoteSpecial(part.substring(1), type); + break; + case '_': + repl = " "; + break; + case '*': //!! We don't do this for real, we just put in 3 of them + repl = expandChar(part); + break; + default: + repl = part; + break; + } + } + m.appendReplacement(fmt, Matcher.quoteReplacement(repl)); + } + } + m.appendTail(fmt); + + if (type.isSpecial('\'')) { + // Now the next pass for quoted characters: Remove '' chars, making "'a''b'" into "'ab'" + int pos = 0; + while ((pos = fmt.indexOf("''", pos)) >= 0) { + fmt.delete(pos, pos + 2); + } + + // Now the final pass for quoted chars: Replace any \u0000 with '' + pos = 0; + while ((pos = fmt.indexOf("\u0000", pos)) >= 0) { + fmt.replace(pos, pos + 1, "''"); + } + } + + return fmt; + } + + /** + * Expands a character. This is only partly done, because we don't have the + * correct info. In Excel, this would be expanded to fill the rest of the + * cell, but we don't know, in general, what the "rest of the cell" is. + * + * @param part The character to be repeated is the second character in this + * string. + * + * @return The character repeated three times. + */ + static String expandChar(String part) { + String repl; + char ch = part.charAt(1); + repl = "" + ch + ch + ch; + return repl; + } + + /** + * Returns the string from the group, or <tt>""</tt> if the group is + * <tt>null</tt>. + * + * @param m The matcher. + * @param g The group number. + * + * @return The group or <tt>""</tt>. + */ + public static String group(Matcher m, int g) { + String str = m.group(g); + return (str == null ? "" : str); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatResult.java b/src/java/org/apache/poi/ss/format/CellFormatResult.java new file mode 100644 index 0000000000..3c45de3de6 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatResult.java @@ -0,0 +1,58 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.awt.*; + +/** + * This object contains the result of applying a cell format or cell format part + * to a value. + * + * @author Ken Arnold, Industrious Media LLC + * @see CellFormatPart#apply(Object) + * @see CellFormat#apply(Object) + */ +public class CellFormatResult { + /** + * This is <tt>true</tt> if no condition was given that applied to the + * value, or if the condition is satisfied. If a condition is relevant, and + * when applied the value fails the test, this is <tt>false</tt>. + */ + public final boolean applies; + + /** The resulting text. This will never be <tt>null</tt>. */ + public final String text; + + /** + * The color the format sets, or <tt>null</tt> if the format sets no color. + * This will always be <tt>null</tt> if {@link #applies} is <tt>false</tt>. + */ + public final Color textColor; + + /** + * Creates a new format result object. + * + * @param applies The value for {@link #applies}. + * @param text The value for {@link #text}. + * @param textColor The value for {@link #textColor}. + */ + public CellFormatResult(boolean applies, String text, Color textColor) { + this.applies = applies; + this.text = text; + this.textColor = (applies ? textColor : null); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatType.java b/src/java/org/apache/poi/ss/format/CellFormatType.java new file mode 100644 index 0000000000..363af1c483 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatType.java @@ -0,0 +1,74 @@ +package org.apache.poi.ss.format; + +/** + * The different kinds of formats that the formatter understands. + * + * @author Ken Arnold, Industrious Media LLC + */ +public enum CellFormatType { + + /** The general (default) format; also used for <tt>"General"</tt>. */ + GENERAL { + CellFormatter formatter(String pattern) { + return new CellGeneralFormatter(); + } + boolean isSpecial(char ch) { + return false; + } + }, + /** A numeric format. */ + NUMBER { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellNumberFormatter(pattern); + } + }, + /** A date format. */ + DATE { + boolean isSpecial(char ch) { + return ch == '\'' || (ch <= '\u007f' && Character.isLetter(ch)); + } + CellFormatter formatter(String pattern) { + return new CellDateFormatter(pattern); + } + }, + /** An elapsed time format. */ + ELAPSED { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellElapsedFormatter(pattern); + } + }, + /** A text format. */ + TEXT { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellTextFormatter(pattern); + } + }; + + /** + * Returns <tt>true</tt> if the format is special and needs to be quoted. + * + * @param ch The character to test. + * + * @return <tt>true</tt> if the format is special and needs to be quoted. + */ + abstract boolean isSpecial(char ch); + + /** + * Returns a new formatter of the appropriate type, for the given pattern. + * The pattern must be appropriate for the type. + * + * @param pattern The pattern to use. + * + * @return A new formatter of the appropriate type, for the given pattern. + */ + abstract CellFormatter formatter(String pattern); +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatter.java b/src/java/org/apache/poi/ss/format/CellFormatter.java new file mode 100644 index 0000000000..a125181200 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatter.java @@ -0,0 +1,102 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.util.Locale; +import java.util.logging.Logger; + +/** + * This is the abstract supertype for the various cell formatters. + * + * @@author Ken Arnold, Industrious Media LLC + */ +public abstract class CellFormatter { + /** The original specified format. */ + protected final String format; + + /** + * This is the locale used to get a consistent format result from which to + * work. + */ + public static final Locale LOCALE = Locale.US; + + /** + * Creates a new formatter object, storing the format in {@link #format}. + * + * @param format The format. + */ + public CellFormatter(String format) { + this.format = format; + } + + /** The logger to use in the formatting code. */ + static final Logger logger = Logger.getLogger( + CellFormatter.class.getName()); + + /** + * Format a value according the format string. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public abstract void formatValue(StringBuffer toAppendTo, Object value); + + /** + * Format a value according to the type, in the most basic way. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public abstract void simpleValue(StringBuffer toAppendTo, Object value); + + /** + * Formats the value, returning the resulting string. + * + * @param value The value to format. + * + * @return The value, formatted. + */ + public String format(Object value) { + StringBuffer sb = new StringBuffer(); + formatValue(sb, value); + return sb.toString(); + } + + /** + * Formats the value in the most basic way, returning the resulting string. + * + * @param value The value to format. + * + * @return The value, formatted. + */ + public String simpleFormat(Object value) { + StringBuffer sb = new StringBuffer(); + simpleValue(sb, value); + return sb.toString(); + } + + /** + * Returns the input string, surrounded by quotes. + * + * @param str The string to quote. + * + * @return The input string, surrounded by quotes. + */ + static String quote(String str) { + return '"' + str + '"'; + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java b/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java new file mode 100644 index 0000000000..e2adc61c0c --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java @@ -0,0 +1,84 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import java.util.Formatter; + +/** + * A formatter for the default "General" cell format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellGeneralFormatter extends CellFormatter { + /** Creates a new general formatter. */ + public CellGeneralFormatter() { + super("General"); + } + + /** + * The general style is not quite the same as any other, or any combination + * of others. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value instanceof Number) { + double val = ((Number) value).doubleValue(); + if (val == 0) { + toAppendTo.append('0'); + return; + } + + String fmt; + double exp = Math.log10(Math.abs(val)); + boolean stripZeros = true; + if (exp > 10 || exp < -9) + fmt = "%1.5E"; + else if ((long) val != val) + fmt = "%1.9f"; + else { + fmt = "%1.0f"; + stripZeros = false; + } + + Formatter formatter = new Formatter(toAppendTo); + formatter.format(LOCALE, fmt, value); + if (stripZeros) { + // strip off trailing zeros + int removeFrom; + if (fmt.endsWith("E")) + removeFrom = toAppendTo.lastIndexOf("E") - 1; + else + removeFrom = toAppendTo.length() - 1; + while (toAppendTo.charAt(removeFrom) == '0') { + toAppendTo.deleteCharAt(removeFrom--); + } + if (toAppendTo.charAt(removeFrom) == '.') { + toAppendTo.deleteCharAt(removeFrom--); + } + } + } else { + toAppendTo.append(value.toString()); + } + } + + /** Equivalent to {@link #formatValue(StringBuffer,Object)}. {@inheritDoc}. */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellNumberFormatter.java b/src/java/org/apache/poi/ss/format/CellNumberFormatter.java new file mode 100644 index 0000000000..8276afda49 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellNumberFormatter.java @@ -0,0 +1,1085 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import org.apache.poi.ss.format.CellFormatPart.PartHandler; + +import java.text.DecimalFormat; +import java.text.FieldPosition; +import java.util.BitSet; +import java.util.Collections; +import java.util.Formatter; +import java.util.Iterator; +import java.util.LinkedList; +import java.util.List; +import java.util.ListIterator; +import java.util.Set; +import java.util.TreeSet; +import java.util.regex.Matcher; + +/** + * This class implements printing out a value using a number format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellNumberFormatter extends CellFormatter { + private final String desc; + private String printfFmt; + private double scale; + private Special decimalPoint; + private Special slash; + private Special exponent; + private Special numerator; + private Special afterInteger; + private Special afterFractional; + private boolean integerCommas; + private final List<Special> specials; + private List<Special> integerSpecials; + private List<Special> fractionalSpecials; + private List<Special> numeratorSpecials; + private List<Special> denominatorSpecials; + private List<Special> exponentSpecials; + private List<Special> exponentDigitSpecials; + private int maxDenominator; + private String numeratorFmt; + private String denominatorFmt; + private boolean improperFraction; + private DecimalFormat decimalFmt; + + static final CellFormatter SIMPLE_NUMBER = new CellFormatter("General") { + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value == null) + return; + if (value instanceof Number) { + Number num = (Number) value; + if (num.doubleValue() % 1.0 == 0) + SIMPLE_INT.formatValue(toAppendTo, value); + else + SIMPLE_FLOAT.formatValue(toAppendTo, value); + } else { + CellTextFormatter.SIMPLE_TEXT.formatValue(toAppendTo, value); + } + } + + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } + }; + + private static final CellFormatter SIMPLE_INT = new CellNumberFormatter( + "#"); + private static final CellFormatter SIMPLE_FLOAT = new CellNumberFormatter( + "#.#"); + + /** + * This class is used to mark where the special characters in the format + * are, as opposed to the other characters that are simply printed. + */ + static class Special { + final char ch; + int pos; + + Special(char ch, int pos) { + this.ch = ch; + this.pos = pos; + } + + @Override + public String toString() { + return "'" + ch + "' @ " + pos; + } + } + + /** + * This class represents a single modification to a result string. The way + * this works is complicated, but so is numeric formatting. In general, for + * most formats, we use a DecimalFormat object that will put the string out + * in a known format, usually with all possible leading and trailing zeros. + * We then walk through the result and the orginal format, and note any + * modifications that need to be made. Finally, we go through and apply + * them all, dealing with overlapping modifications. + */ + static class StringMod implements Comparable<StringMod> { + final Special special; + final int op; + CharSequence toAdd; + Special end; + boolean startInclusive; + boolean endInclusive; + + public static final int BEFORE = 1; + public static final int AFTER = 2; + public static final int REPLACE = 3; + + private StringMod(Special special, CharSequence toAdd, int op) { + this.special = special; + this.toAdd = toAdd; + this.op = op; + } + + public StringMod(Special start, boolean startInclusive, Special end, + boolean endInclusive, char toAdd) { + this(start, startInclusive, end, endInclusive); + this.toAdd = toAdd + ""; + } + + public StringMod(Special start, boolean startInclusive, Special end, + boolean endInclusive) { + special = start; + this.startInclusive = startInclusive; + this.end = end; + this.endInclusive = endInclusive; + op = REPLACE; + toAdd = ""; + } + + public int compareTo(StringMod that) { + int diff = special.pos - that.special.pos; + if (diff != 0) + return diff; + else + return op - that.op; + } + + @Override + public boolean equals(Object that) { + try { + return compareTo((StringMod) that) == 0; + } catch (RuntimeException ignored) { + // NullPointerException or CastException + return false; + } + } + + @Override + public int hashCode() { + return special.hashCode() + op; + } + } + + private class NumPartHandler implements PartHandler { + private char insertSignForExponent; + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case 'e': + case 'E': + // See comment in writeScientific -- exponent handling is complex. + // (1) When parsing the format, remove the sign from after the 'e' and + // put it before the first digit of the exponent. + if (exponent == null && specials.size() > 0) { + specials.add(exponent = new Special('.', pos)); + insertSignForExponent = part.charAt(1); + return part.substring(0, 1); + } + break; + + case '0': + case '?': + case '#': + if (insertSignForExponent != '\0') { + specials.add(new Special(insertSignForExponent, pos)); + desc.append(insertSignForExponent); + insertSignForExponent = '\0'; + pos++; + } + for (int i = 0; i < part.length(); i++) { + char ch = part.charAt(i); + specials.add(new Special(ch, pos + i)); + } + break; + + case '.': + if (decimalPoint == null && specials.size() > 0) + specials.add(decimalPoint = new Special('.', pos)); + break; + + case '/': + //!! This assumes there is a numerator and a denominator, but these are actually optional + if (slash == null && specials.size() > 0) { + numerator = previousNumber(); + // If the first number in the whole format is the numerator, the + // entire number should be printed as an improper fraction + if (numerator == firstDigit(specials)) + improperFraction = true; + specials.add(slash = new Special('.', pos)); + } + break; + + case '%': + // don't need to remember because we don't need to do anything with these + scale *= 100; + break; + + default: + return null; + } + return part; + } + } + + /** + * Creates a new cell number formatter. + * + * @param format The format to parse. + */ + public CellNumberFormatter(String format) { + super(format); + + scale = 1; + + specials = new LinkedList<Special>(); + + NumPartHandler partHandler = new NumPartHandler(); + StringBuffer descBuf = CellFormatPart.parseFormat(format, + CellFormatType.NUMBER, partHandler); + + // These are inconsistent settings, so ditch 'em + if ((decimalPoint != null || exponent != null) && slash != null) { + slash = null; + numerator = null; + } + + interpretCommas(descBuf); + + int precision; + int fractionPartWidth = 0; + if (decimalPoint == null) { + precision = 0; + } else { + precision = interpretPrecision(); + fractionPartWidth = 1 + precision; + if (precision == 0) { + // This means the format has a ".", but that output should have no decimals after it. + // We just stop treating it specially + specials.remove(decimalPoint); + decimalPoint = null; + } + } + + if (precision == 0) + fractionalSpecials = Collections.emptyList(); + else + fractionalSpecials = specials.subList(specials.indexOf( + decimalPoint) + 1, fractionalEnd()); + if (exponent == null) + exponentSpecials = Collections.emptyList(); + else { + int exponentPos = specials.indexOf(exponent); + exponentSpecials = specialsFor(exponentPos, 2); + exponentDigitSpecials = specialsFor(exponentPos + 2); + } + + if (slash == null) { + numeratorSpecials = Collections.emptyList(); + denominatorSpecials = Collections.emptyList(); + } else { + if (numerator == null) + numeratorSpecials = Collections.emptyList(); + else + numeratorSpecials = specialsFor(specials.indexOf(numerator)); + + denominatorSpecials = specialsFor(specials.indexOf(slash) + 1); + if (denominatorSpecials.isEmpty()) { + // no denominator follows the slash, drop the fraction idea + numeratorSpecials = Collections.emptyList(); + } else { + maxDenominator = maxValue(denominatorSpecials); + numeratorFmt = singleNumberFormat(numeratorSpecials); + denominatorFmt = singleNumberFormat(denominatorSpecials); + } + } + + integerSpecials = specials.subList(0, integerEnd()); + + if (exponent == null) { + StringBuffer fmtBuf = new StringBuffer("%"); + + int integerPartWidth = calculateIntegerPartWidth(); + int totalWidth = integerPartWidth + fractionPartWidth; + + fmtBuf.append('0').append(totalWidth).append('.').append(precision); + + fmtBuf.append("f"); + printfFmt = fmtBuf.toString(); + } else { + StringBuffer fmtBuf = new StringBuffer(); + boolean first = true; + List<Special> specialList = integerSpecials; + if (integerSpecials.size() == 1) { + // If we don't do this, we get ".6e5" instead of "6e4" + fmtBuf.append("0"); + first = false; + } else + for (Special s : specialList) { + if (isDigitFmt(s)) { + fmtBuf.append(first ? '#' : '0'); + first = false; + } + } + if (fractionalSpecials.size() > 0) { + fmtBuf.append('.'); + for (Special s : fractionalSpecials) { + if (isDigitFmt(s)) { + if (!first) + fmtBuf.append('0'); + first = false; + } + } + } + fmtBuf.append('E'); + placeZeros(fmtBuf, exponentSpecials.subList(2, + exponentSpecials.size())); + decimalFmt = new DecimalFormat(fmtBuf.toString()); + } + + if (exponent != null) + scale = + 1; // in "e" formats,% and trailing commas have no scaling effect + + desc = descBuf.toString(); + } + + private static void placeZeros(StringBuffer sb, List<Special> specials) { + for (Special s : specials) { + if (isDigitFmt(s)) + sb.append('0'); + } + } + + private static Special firstDigit(List<Special> specials) { + for (Special s : specials) { + if (isDigitFmt(s)) + return s; + } + return null; + } + + static StringMod insertMod(Special special, CharSequence toAdd, int where) { + return new StringMod(special, toAdd, where); + } + + static StringMod deleteMod(Special start, boolean startInclusive, + Special end, boolean endInclusive) { + + return new StringMod(start, startInclusive, end, endInclusive); + } + + static StringMod replaceMod(Special start, boolean startInclusive, + Special end, boolean endInclusive, char withChar) { + + return new StringMod(start, startInclusive, end, endInclusive, + withChar); + } + + private static String singleNumberFormat(List<Special> numSpecials) { + return "%0" + numSpecials.size() + "d"; + } + + private static int maxValue(List<Special> s) { + return (int) Math.round(Math.pow(10, s.size()) - 1); + } + + private List<Special> specialsFor(int pos, int takeFirst) { + if (pos >= specials.size()) + return Collections.emptyList(); + ListIterator<Special> it = specials.listIterator(pos + takeFirst); + Special last = it.next(); + int end = pos + takeFirst; + while (it.hasNext()) { + Special s = it.next(); + if (!isDigitFmt(s) || s.pos - last.pos > 1) + break; + end++; + last = s; + } + return specials.subList(pos, end + 1); + } + + private List<Special> specialsFor(int pos) { + return specialsFor(pos, 0); + } + + private static boolean isDigitFmt(Special s) { + return s.ch == '0' || s.ch == '?' || s.ch == '#'; + } + + private Special previousNumber() { + ListIterator<Special> it = specials.listIterator(specials.size()); + while (it.hasPrevious()) { + Special s = it.previous(); + if (isDigitFmt(s)) { + Special numStart = s; + Special last = s; + while (it.hasPrevious()) { + s = it.previous(); + if (last.pos - s.pos > 1) // it has to be continuous digits + break; + if (isDigitFmt(s)) + numStart = s; + else + break; + last = s; + } + return numStart; + } + } + return null; + } + + private int calculateIntegerPartWidth() { + ListIterator<Special> it = specials.listIterator(); + int digitCount = 0; + while (it.hasNext()) { + Special s = it.next(); + //!! Handle fractions: The previous set of digits before that is the numerator, so we should stop short of that + if (s == afterInteger) + break; + else if (isDigitFmt(s)) + digitCount++; + } + return digitCount; + } + + private int interpretPrecision() { + if (decimalPoint == null) { + return -1; + } else { + int precision = 0; + ListIterator<Special> it = specials.listIterator(specials.indexOf( + decimalPoint)); + if (it.hasNext()) + it.next(); // skip over the decimal point itself + while (it.hasNext()) { + Special s = it.next(); + if (isDigitFmt(s)) + precision++; + else + break; + } + return precision; + } + } + + private void interpretCommas(StringBuffer sb) { + // In the integer part, commas at the end are scaling commas; other commas mean to show thousand-grouping commas + ListIterator<Special> it = specials.listIterator(integerEnd()); + + boolean stillScaling = true; + integerCommas = false; + while (it.hasPrevious()) { + Special s = it.previous(); + if (s.ch != ',') { + stillScaling = false; + } else { + if (stillScaling) { + scale /= 1000; + } else { + integerCommas = true; + } + } + } + + if (decimalPoint != null) { + it = specials.listIterator(fractionalEnd()); + while (it.hasPrevious()) { + Special s = it.previous(); + if (s.ch != ',') { + break; + } else { + scale /= 1000; + } + } + } + + // Now strip them out -- we only need their interpretation, not their presence + it = specials.listIterator(); + int removed = 0; + while (it.hasNext()) { + Special s = it.next(); + s.pos -= removed; + if (s.ch == ',') { + removed++; + it.remove(); + sb.deleteCharAt(s.pos); + } + } + } + + private int integerEnd() { + if (decimalPoint != null) + afterInteger = decimalPoint; + else if (exponent != null) + afterInteger = exponent; + else if (numerator != null) + afterInteger = numerator; + else + afterInteger = null; + return afterInteger == null ? specials.size() : specials.indexOf( + afterInteger); + } + + private int fractionalEnd() { + int end; + if (exponent != null) + afterFractional = exponent; + else if (numerator != null) + afterInteger = numerator; + else + afterFractional = null; + end = afterFractional == null ? specials.size() : specials.indexOf( + afterFractional); + return end; + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object valueObject) { + double value = ((Number) valueObject).doubleValue(); + value *= scale; + + // the '-' sign goes at the front, always, so we pick it out + boolean negative = value < 0; + if (negative) + value = -value; + + // Split out the fractional part if we need to print a fraction + double fractional = 0; + if (slash != null) { + if (improperFraction) { + fractional = value; + value = 0; + } else { + fractional = value % 1.0; + //noinspection SillyAssignment + value = (long) value; + } + } + + Set<StringMod> mods = new TreeSet<StringMod>(); + StringBuffer output = new StringBuffer(desc); + + if (exponent != null) { + writeScientific(value, output, mods); + } else if (improperFraction) { + writeFraction(value, null, fractional, output, mods); + } else { + StringBuffer result = new StringBuffer(); + Formatter f = new Formatter(result); + f.format(LOCALE, printfFmt, value); + + if (numerator == null) { + writeFractional(result, output); + writeInteger(result, output, integerSpecials, mods, + integerCommas); + } else { + writeFraction(value, result, fractional, output, mods); + } + } + + // Now strip out any remaining '#'s and add any pending text ... + ListIterator<Special> it = specials.listIterator(); + Iterator<StringMod> changes = mods.iterator(); + StringMod nextChange = (changes.hasNext() ? changes.next() : null); + int adjust = 0; + BitSet deletedChars = new BitSet(); // records chars already deleted + while (it.hasNext()) { + Special s = it.next(); + int adjustedPos = s.pos + adjust; + if (!deletedChars.get(s.pos) && output.charAt(adjustedPos) == '#') { + output.deleteCharAt(adjustedPos); + adjust--; + deletedChars.set(s.pos); + } + while (nextChange != null && s == nextChange.special) { + int lenBefore = output.length(); + int modPos = s.pos + adjust; + int posTweak = 0; + switch (nextChange.op) { + case StringMod.AFTER: + // ignore adding a comma after a deleted char (which was a '#') + if (nextChange.toAdd.equals(",") && deletedChars.get(s.pos)) + break; + posTweak = 1; + //noinspection fallthrough + case StringMod.BEFORE: + output.insert(modPos + posTweak, nextChange.toAdd); + break; + + case StringMod.REPLACE: + int delPos = + s.pos; // delete starting pos in original coordinates + if (!nextChange.startInclusive) { + delPos++; + modPos++; + } + + // Skip over anything already deleted + while (deletedChars.get(delPos)) { + delPos++; + modPos++; + } + + int delEndPos = + nextChange.end.pos; // delete end point in original + if (nextChange.endInclusive) + delEndPos++; + + int modEndPos = + delEndPos + adjust; // delete end point in current + + if (modPos < modEndPos) { + if (nextChange.toAdd == "") + output.delete(modPos, modEndPos); + else { + char fillCh = nextChange.toAdd.charAt(0); + for (int i = modPos; i < modEndPos; i++) + output.setCharAt(i, fillCh); + } + deletedChars.set(delPos, delEndPos); + } + break; + + default: + throw new IllegalStateException( + "Unknown op: " + nextChange.op); + } + adjust += output.length() - lenBefore; + + if (changes.hasNext()) + nextChange = changes.next(); + else + nextChange = null; + } + } + + // Finally, add it to the string + if (negative) + toAppendTo.append('-'); + toAppendTo.append(output); + } + + private void writeScientific(double value, StringBuffer output, + Set<StringMod> mods) { + + StringBuffer result = new StringBuffer(); + FieldPosition fractionPos = new FieldPosition( + DecimalFormat.FRACTION_FIELD); + decimalFmt.format(value, result, fractionPos); + writeInteger(result, output, integerSpecials, mods, integerCommas); + writeFractional(result, output); + + /* + * Exponent sign handling is complex. + * + * In DecimalFormat, you never put the sign in the format, and the sign only + * comes out of the format if it is negative. + * + * In Excel, you always say whether to always show the sign ("e+") or only + * show negative signs ("e-"). + * + * Also in Excel, where you put the sign in the format is NOT where it comes + * out in the result. In the format, the sign goes with the "e"; in the + * output it goes with the exponent value. That is, if you say "#e-|#" you + * get "1e|-5", not "1e-|5". This makes sense I suppose, but it complicates + * things. + * + * Finally, everything else in this formatting code assumes that the base of + * the result is the original format, and that starting from that situation, + * the indexes of the original special characters can be used to place the new + * characters. As just described, this is not true for the exponent's sign. + * <p/> + * So here is how we handle it: + * + * (1) When parsing the format, remove the sign from after the 'e' and put it + * before the first digit of the exponent (where it will be shown). + * + * (2) Determine the result's sign. + * + * (3) If it's missing, put the sign into the output to keep the result + * lined up with the output. (In the result, "after the 'e'" and "before the + * first digit" are the same because the result has no extra chars to be in + * the way.) + * + * (4) In the output, remove the sign if it should not be shown ("e-" was used + * and the sign is negative) or set it to the correct value. + */ + + // (2) Determine the result's sign. + int ePos = fractionPos.getEndIndex(); + int signPos = ePos + 1; + char expSignRes = result.charAt(signPos); + if (expSignRes != '-') { + // not a sign, so it's a digit, and therefore a positive exponent + expSignRes = '+'; + // (3) If it's missing, put the sign into the output to keep the result + // lined up with the output. + result.insert(signPos, '+'); + } + + // Now the result lines up like it is supposed to with the specials' indexes + ListIterator<Special> it = exponentSpecials.listIterator(1); + Special expSign = it.next(); + char expSignFmt = expSign.ch; + + // (4) In the output, remove the sign if it should not be shown or set it to + // the correct value. + if (expSignRes == '-' || expSignFmt == '+') + mods.add(replaceMod(expSign, true, expSign, true, expSignRes)); + else + mods.add(deleteMod(expSign, true, expSign, true)); + + StringBuffer exponentNum = new StringBuffer(result.substring( + signPos + 1)); + writeInteger(exponentNum, output, exponentDigitSpecials, mods, false); + } + + private void writeFraction(double value, StringBuffer result, + double fractional, StringBuffer output, Set<StringMod> mods) { + + // Figure out if we are to suppress either the integer or fractional part. + // With # the suppressed part is removed; with ? it is replaced with spaces. + if (!improperFraction) { + // If fractional part is zero, and numerator doesn't have '0', write out + // only the integer part and strip the rest. + if (fractional == 0 && !hasChar('0', numeratorSpecials)) { + writeInteger(result, output, integerSpecials, mods, false); + + Special start = integerSpecials.get(integerSpecials.size() - 1); + Special end = denominatorSpecials.get( + denominatorSpecials.size() - 1); + if (hasChar('?', integerSpecials, numeratorSpecials, + denominatorSpecials)) { + //if any format has '?', then replace the fraction with spaces + mods.add(replaceMod(start, false, end, true, ' ')); + } else { + // otherwise, remove the fraction + mods.add(deleteMod(start, false, end, true)); + } + + // That's all, just return + return; + } else { + // New we check to see if we should remove the integer part + boolean allZero = (value == 0 && fractional == 0); + boolean willShowFraction = fractional != 0 || hasChar('0', + numeratorSpecials); + boolean removeBecauseZero = allZero && (hasOnly('#', + integerSpecials) || !hasChar('0', numeratorSpecials)); + boolean removeBecauseFraction = + !allZero && value == 0 && willShowFraction && !hasChar( + '0', integerSpecials); + if (removeBecauseZero || removeBecauseFraction) { + Special start = integerSpecials.get( + integerSpecials.size() - 1); + if (hasChar('?', integerSpecials, numeratorSpecials)) { + mods.add(replaceMod(start, true, numerator, false, + ' ')); + } else { + mods.add(deleteMod(start, true, numerator, false)); + } + } else { + // Not removing the integer part -- print it out + writeInteger(result, output, integerSpecials, mods, false); + } + } + } + + // Calculate and print the actual fraction (improper or otherwise) + try { + int n; + int d; + // the "fractional % 1" captures integer values in improper fractions + if (fractional == 0 || (improperFraction && fractional % 1 == 0)) { + // 0 as a fraction is reported by excel as 0/1 + n = (int) Math.round(fractional); + d = 1; + } else { + Fraction frac = new Fraction(fractional, maxDenominator); + n = frac.getNumerator(); + d = frac.getDenominator(); + } + if (improperFraction) + n += Math.round(value * d); + writeSingleInteger(numeratorFmt, n, output, numeratorSpecials, + mods); + writeSingleInteger(denominatorFmt, d, output, denominatorSpecials, + mods); + } catch (RuntimeException ignored) { + ignored.printStackTrace(); + } + } + + private static boolean hasChar(char ch, List<Special>... numSpecials) { + for (List<Special> specials : numSpecials) { + for (Special s : specials) { + if (s.ch == ch) { + return true; + } + } + } + return false; + } + + private static boolean hasOnly(char ch, List<Special>... numSpecials) { + for (List<Special> specials : numSpecials) { + for (Special s : specials) { + if (s.ch != ch) { + return false; + } + } + } + return true; + } + + private void writeSingleInteger(String fmt, int num, StringBuffer output, + List<Special> numSpecials, Set<StringMod> mods) { + + StringBuffer sb = new StringBuffer(); + Formatter formatter = new Formatter(sb); + formatter.format(LOCALE, fmt, num); + writeInteger(sb, output, numSpecials, mods, false); + } + + private void writeInteger(StringBuffer result, StringBuffer output, + List<Special> numSpecials, Set<StringMod> mods, + boolean showCommas) { + + int pos = result.indexOf(".") - 1; + if (pos < 0) { + if (exponent != null && numSpecials == integerSpecials) + pos = result.indexOf("E") - 1; + else + pos = result.length() - 1; + } + + int strip; + for (strip = 0; strip < pos; strip++) { + char resultCh = result.charAt(strip); + if (resultCh != '0' && resultCh != ',') + break; + } + + ListIterator<Special> it = numSpecials.listIterator(numSpecials.size()); + boolean followWithComma = false; + Special lastOutputIntegerDigit = null; + int digit = 0; + while (it.hasPrevious()) { + char resultCh; + if (pos >= 0) + resultCh = result.charAt(pos); + else { + // If result is shorter than field, pretend there are leading zeros + resultCh = '0'; + } + Special s = it.previous(); + followWithComma = showCommas && digit > 0 && digit % 3 == 0; + boolean zeroStrip = false; + if (resultCh != '0' || s.ch == '0' || s.ch == '?' || pos >= strip) { + zeroStrip = s.ch == '?' && pos < strip; + output.setCharAt(s.pos, (zeroStrip ? ' ' : resultCh)); + lastOutputIntegerDigit = s; + } + if (followWithComma) { + mods.add(insertMod(s, zeroStrip ? " " : ",", StringMod.AFTER)); + followWithComma = false; + } + digit++; + --pos; + } + StringBuffer extraLeadingDigits = new StringBuffer(); + if (pos >= 0) { + // We ran out of places to put digits before we ran out of digits; put this aside so we can add it later + ++pos; // pos was decremented at the end of the loop above when the iterator was at its end + extraLeadingDigits = new StringBuffer(result.substring(0, pos)); + if (showCommas) { + while (pos > 0) { + if (digit > 0 && digit % 3 == 0) + extraLeadingDigits.insert(pos, ','); + digit++; + --pos; + } + } + mods.add(insertMod(lastOutputIntegerDigit, extraLeadingDigits, + StringMod.BEFORE)); + } + } + + private void writeFractional(StringBuffer result, StringBuffer output) { + int digit; + int strip; + ListIterator<Special> it; + if (fractionalSpecials.size() > 0) { + digit = result.indexOf(".") + 1; + if (exponent != null) + strip = result.indexOf("e") - 1; + else + strip = result.length() - 1; + while (strip > digit && result.charAt(strip) == '0') + strip--; + it = fractionalSpecials.listIterator(); + while (it.hasNext()) { + Special s = it.next(); + char resultCh = result.charAt(digit); + if (resultCh != '0' || s.ch == '0' || digit < strip) + output.setCharAt(s.pos, resultCh); + else if (s.ch == '?') { + // This is when we're in trailing zeros, and the format is '?'. We still strip out remaining '#'s later + output.setCharAt(s.pos, ' '); + } + digit++; + } + } + } + + /** + * {@inheritDoc} + * <p/> + * For a number, this is <tt>"#"</tt> for integer values, and <tt>"#.#"</tt> + * for floating-point values. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_NUMBER.formatValue(toAppendTo, value); + } + + /** + * Based on org.apache.commons.math.fraction.Fraction from Apache Commons-Math. + * YK: The only reason of having this inner class is to avoid dependency on the Commons-Math jar. + */ + private static class Fraction { + /** The denominator. */ + private final int denominator; + + /** The numerator. */ + private final int numerator; + + /** + * Create a fraction given the double value and either the maximum error + * allowed or the maximum number of denominator digits. + * + * @param value the double value to convert to a fraction. + * @param epsilon maximum error allowed. The resulting fraction is within + * <code>epsilon</code> of <code>value</code>, in absolute terms. + * @param maxDenominator maximum denominator value allowed. + * @param maxIterations maximum number of convergents + * @throws RuntimeException if the continued fraction failed to + * converge. + */ + private Fraction(double value, double epsilon, int maxDenominator, int maxIterations) + { + long overflow = Integer.MAX_VALUE; + double r0 = value; + long a0 = (long)Math.floor(r0); + if (a0 > overflow) { + throw new IllegalArgumentException("Overflow trying to convert "+value+" to fraction ("+a0+"/"+1l+")"); + } + + // check for (almost) integer arguments, which should not go + // to iterations. + if (Math.abs(a0 - value) < epsilon) { + this.numerator = (int) a0; + this.denominator = 1; + return; + } + + long p0 = 1; + long q0 = 0; + long p1 = a0; + long q1 = 1; + + long p2; + long q2; + + int n = 0; + boolean stop = false; + do { + ++n; + double r1 = 1.0 / (r0 - a0); + long a1 = (long)Math.floor(r1); + p2 = (a1 * p1) + p0; + q2 = (a1 * q1) + q0; + if ((p2 > overflow) || (q2 > overflow)) { + throw new RuntimeException("Overflow trying to convert "+value+" to fraction ("+p2+"/"+q2+")"); + } + + double convergent = (double)p2 / (double)q2; + if (n < maxIterations && Math.abs(convergent - value) > epsilon && q2 < maxDenominator) { + p0 = p1; + p1 = p2; + q0 = q1; + q1 = q2; + a0 = a1; + r0 = r1; + } else { + stop = true; + } + } while (!stop); + + if (n >= maxIterations) { + throw new RuntimeException("Unable to convert "+value+" to fraction after "+maxIterations+" iterations"); + } + + if (q2 < maxDenominator) { + this.numerator = (int) p2; + this.denominator = (int) q2; + } else { + this.numerator = (int) p1; + this.denominator = (int) q1; + } + + } + + /** + * Create a fraction given the double value and maximum denominator. + * <p> + * References: + * <ul> + * <li><a href="http://mathworld.wolfram.com/ContinuedFraction.html"> + * Continued Fraction</a> equations (11) and (22)-(26)</li> + * </ul> + * </p> + * @param value the double value to convert to a fraction. + * @param maxDenominator The maximum allowed value for denominator + * @throws RuntimeException if the continued fraction failed to + * converge + */ + public Fraction(double value, int maxDenominator) + { + this(value, 0, maxDenominator, 100); + } + + /** + * Access the denominator. + * @return the denominator. + */ + public int getDenominator() { + return denominator; + } + + /** + * Access the numerator. + * @return the numerator. + */ + public int getNumerator() { + return numerator; + } + + } + +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellTextFormatter.java b/src/java/org/apache/poi/ss/format/CellTextFormatter.java new file mode 100644 index 0000000000..ebefa9847a --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellTextFormatter.java @@ -0,0 +1,79 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import org.apache.poi.ss.format.CellFormatPart.PartHandler; + +import java.util.regex.Matcher; + +/** + * This class implements printing out text. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellTextFormatter extends CellFormatter { + private final int[] textPos; + private final String desc; + + static final CellFormatter SIMPLE_TEXT = new CellTextFormatter("@"); + + public CellTextFormatter(String format) { + super(format); + + final int[] numPlaces = new int[1]; + + desc = CellFormatPart.parseFormat(format, CellFormatType.TEXT, + new PartHandler() { + public String handlePart(Matcher m, String part, + CellFormatType type, StringBuffer desc) { + if (part.equals("@")) { + numPlaces[0]++; + return "\u0000"; + } + return null; + } + }).toString(); + + // Remember the "@" positions in last-to-first order (to make insertion easier) + textPos = new int[numPlaces[0]]; + int pos = desc.length() - 1; + for (int i = 0; i < textPos.length; i++) { + textPos[i] = desc.lastIndexOf("\u0000", pos); + pos = textPos[i] - 1; + } + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object obj) { + int start = toAppendTo.length(); + String text = obj.toString(); + toAppendTo.append(desc); + for (int i = 0; i < textPos.length; i++) { + int pos = start + textPos[i]; + toAppendTo.replace(pos, pos + 1, text); + } + } + + /** + * {@inheritDoc} + * <p/> + * For text, this is just printing the text. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_TEXT.formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/package.html b/src/java/org/apache/poi/ss/format/package.html new file mode 100644 index 0000000000..d5ab99fb60 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/package.html @@ -0,0 +1,3 @@ +<body> +This package contains classes that implement cell formatting +</body> diff --git a/src/ooxml/testcases/org/apache/poi/ss/format/TestCellFormatPart.java b/src/ooxml/testcases/org/apache/poi/ss/format/TestCellFormatPart.java new file mode 100644 index 0000000000..bd518369d2 --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/ss/format/TestCellFormatPart.java @@ -0,0 +1,126 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.xssf.XSSFITestDataProvider; + +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** Test the individual CellFormatPart types. */ +public class TestCellFormatPart extends CellFormatTestBase { + private static final Pattern NUMBER_EXTRACT_FMT = Pattern.compile( + "([-+]?[0-9]+)(\\.[0-9]+)?.*(?:(e).*?([+-]?[0-9]+))", + Pattern.CASE_INSENSITIVE); + + public TestCellFormatPart() { + super(XSSFITestDataProvider.instance); + } + + public void testGeneralFormat() throws Exception { + runFormatTests("GeneralFormatTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + int type = CellFormat.ultimateType(cell); + if (type == Cell.CELL_TYPE_BOOLEAN) + return cell.getBooleanCellValue() ? "TRUE" : "FALSE"; + else if (type == Cell.CELL_TYPE_NUMERIC) + return cell.getNumericCellValue(); + else + return cell.getStringCellValue(); + } + }); + } + + public void testNumberFormat() throws Exception { + runFormatTests("NumberFormatTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + return cell.getNumericCellValue(); + } + }); + } + + public void testNumberApproxFormat() throws Exception { + runFormatTests("NumberFormatApproxTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + return cell.getNumericCellValue(); + } + + @Override + void equivalent(String expected, String actual, + CellFormatPart format) { + double expectedVal = extractNumber(expected); + double actualVal = extractNumber(actual); + // equal within 1% + double delta = expectedVal / 100; + assertEquals("format \"" + format + "\"," + expected + " ~= " + + actual, expectedVal, actualVal, delta); + } + }); + } + + public void testDateFormat() throws Exception { + runFormatTests("DateFormatTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + return cell.getDateCellValue(); + } + }); + } + + public void testElapsedFormat() throws Exception { + runFormatTests("ElapsedFormatTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + return cell.getNumericCellValue(); + } + }); + } + + public void testTextFormat() throws Exception { + runFormatTests("TextFormatTests.xlsx", new CellValue() { + public Object getValue(Cell cell) { + if (CellFormat.ultimateType(cell) == Cell.CELL_TYPE_BOOLEAN) + return cell.getBooleanCellValue() ? "TRUE" : "FALSE"; + else + return cell.getStringCellValue(); + } + }); + } + + public void testConditions() throws Exception { + runFormatTests("FormatConditionTests.xlsx", new CellValue() { + Object getValue(Cell cell) { + return cell.getNumericCellValue(); + } + }); + } + + private double extractNumber(String str) { + Matcher m = NUMBER_EXTRACT_FMT.matcher(str); + if (!m.find()) + throw new IllegalArgumentException( + "Cannot find numer in \"" + str + "\""); + + StringBuffer sb = new StringBuffer(); + // The groups in the pattern are the parts of the number + for (int i = 1; i <= m.groupCount(); i++) { + String part = m.group(i); + if (part != null) + sb.append(part); + } + return Double.valueOf(sb.toString()); + } +}
\ No newline at end of file diff --git a/src/testcases/org/apache/poi/ss/format/CellFormatTestBase.java b/src/testcases/org/apache/poi/ss/format/CellFormatTestBase.java new file mode 100644 index 0000000000..6b5c61bc5e --- /dev/null +++ b/src/testcases/org/apache/poi/ss/format/CellFormatTestBase.java @@ -0,0 +1,293 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import junit.framework.TestCase; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.ITestDataProvider; + +import javax.swing.*; +import java.awt.*; +import java.util.*; + +import static java.awt.Color.*; +import java.io.IOException; + +/** + * This class is a base class for spreadsheet-based tests, such as are used for + * cell formatting. This reads tests from the spreadsheet, as well as reading + * flags that can be used to paramterize these tests. + * <p/> + * Each test has four parts: The expected result (column A), the format string + * (column B), the value to format (column C), and a comma-separated list of + * categores that this test falls in. Normally all tests are run, but if the + * flag "Categories" is not empty, only tests that have at least one category + * listed in "Categories" are run. + */ +@SuppressWarnings( + {"JUnitTestCaseWithNoTests", "JUnitTestClassNamingConvention"}) +public class CellFormatTestBase extends TestCase { + private final ITestDataProvider _testDataProvider; + + protected Workbook workbook; + + private String testFile; + private Map<String, String> testFlags; + private boolean tryAllColors; + private JLabel label; + + private static final String[] COLOR_NAMES = + {"Black", "Red", "Green", "Blue", "Yellow", "Cyan", "Magenta", + "White"}; + private static final Color[] COLORS = + {BLACK, RED, GREEN, BLUE, YELLOW, CYAN, MAGENTA, WHITE}; + + public static final Color TEST_COLOR = ORANGE.darker(); + + protected CellFormatTestBase(ITestDataProvider testDataProvider) { + _testDataProvider = testDataProvider; + } + + abstract static class CellValue { + abstract Object getValue(Cell cell); + + @SuppressWarnings({"UnusedDeclaration"}) + Color getColor(Cell cell) { + return TEST_COLOR; + } + + void equivalent(String expected, String actual, CellFormatPart format) { + assertEquals("format \"" + format + "\"", '"' + expected + '"', + '"' + actual + '"'); + } + } + + protected void runFormatTests(String workbookName, CellValue valueGetter) + throws IOException { + + openWorkbook(workbookName); + + readFlags(workbook); + + Set<String> runCategories = new TreeSet<String>( + String.CASE_INSENSITIVE_ORDER); + String runCategoryList = flagString("Categories", ""); + if (runCategoryList != null) { + runCategories.addAll(Arrays.asList(runCategoryList.split( + "\\s*,\\s*"))); + runCategories.remove(""); // this can be found and means nothing + } + + Sheet sheet = workbook.getSheet("Tests"); + int end = sheet.getLastRowNum(); + // Skip the header row, therefore "+ 1" + for (int r = sheet.getFirstRowNum() + 1; r <= end; r++) { + Row row = sheet.getRow(r); + if (row == null) + continue; + int cellnum = 0; + String expectedText = row.getCell(cellnum).getStringCellValue(); + String format = row.getCell(1).getStringCellValue(); + String testCategoryList = row.getCell(3).getStringCellValue(); + boolean byCategory = runByCategory(runCategories, testCategoryList); + if ((!expectedText.isEmpty() || !format.isEmpty()) && byCategory) { + Cell cell = row.getCell(2); + tryFormat(r, expectedText, format, valueGetter, cell); + } + } + } + + /** + * Open a given workbook. + * + * @param workbookName The workbook name. This is presumed to live in the + * "spreadsheets" directory under the directory named in + * the Java property "POI.testdata.path". + * + * @throws IOException + */ + protected void openWorkbook(String workbookName) + throws IOException { + workbook = _testDataProvider.openSampleWorkbook(workbookName); + workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); + testFile = workbookName; + } + + /** + * Read the flags from the workbook. Flags are on the sheet named "Flags", + * and consist of names in column A and values in column B. These are put + * into a map that can be queried later. + * + * @param wb The workbook to look in. + */ + private void readFlags(Workbook wb) { + Sheet flagSheet = wb.getSheet("Flags"); + testFlags = new TreeMap<String, String>(String.CASE_INSENSITIVE_ORDER); + if (flagSheet != null) { + int end = flagSheet.getLastRowNum(); + // Skip the header row, therefore "+ 1" + for (int r = flagSheet.getFirstRowNum() + 1; r <= end; r++) { + Row row = flagSheet.getRow(r); + if (row == null) + continue; + String flagName = row.getCell(0).getStringCellValue(); + String flagValue = row.getCell(1).getStringCellValue(); + if (flagName.length() > 0) { + testFlags.put(flagName, flagValue); + } + } + } + + tryAllColors = flagBoolean("AllColors", true); + } + + /** + * Returns <tt>true</tt> if any of the categories for this run are contained + * in the test's listed categories. + * + * @param categories The categories of tests to be run. If this is + * empty, then all tests will be run. + * @param testCategories The categories that this test is in. This is a + * comma-separated list. If <em>any</em> tests in + * this list are in <tt>categories</tt>, the test will + * be run. + * + * @return <tt>true</tt> if the test should be run. + */ + private boolean runByCategory(Set<String> categories, + String testCategories) { + + if (categories.isEmpty()) + return true; + // If there are specified categories, find out if this has one of them + for (String category : testCategories.split("\\s*,\\s*")) { + if (categories.contains(category)) { + return true; + } + } + return false; + } + + private void tryFormat(int row, String expectedText, String desc, + CellValue getter, Cell cell) { + + Object value = getter.getValue(cell); + Color testColor = getter.getColor(cell); + if (testColor == null) + testColor = TEST_COLOR; + + if (label == null) + label = new JLabel(); + label.setForeground(testColor); + label.setText("xyzzy"); + + System.out.printf("Row %d: \"%s\" -> \"%s\": expected \"%s\"", row + 1, + String.valueOf(value), desc, expectedText); + System.out.flush(); + String actualText = tryColor(desc, null, getter, value, expectedText, + testColor); + System.out.printf(", actual \"%s\")%n", actualText); + System.out.flush(); + + if (tryAllColors && testColor != TEST_COLOR) { + for (int i = 0; i < COLOR_NAMES.length; i++) { + String cname = COLOR_NAMES[i]; + tryColor(desc, cname, getter, value, expectedText, COLORS[i]); + } + } + } + + private String tryColor(String desc, String cname, CellValue getter, + Object value, String expectedText, Color expectedColor) { + + if (cname != null) + desc = "[" + cname + "]" + desc; + Color origColor = label.getForeground(); + CellFormatPart format = new CellFormatPart(desc); + if (!format.apply(label, value).applies) { + // If this doesn't apply, no color change is expected + expectedColor = origColor; + } + + String actualText = label.getText(); + Color actualColor = label.getForeground(); + getter.equivalent(expectedText, actualText, format); + assertEquals(cname == null ? "no color" : "color " + cname, + expectedColor, actualColor); + return actualText; + } + + /** + * Returns the value for the given flag. The flag has the value of + * <tt>true</tt> if the text value is <tt>"true"</tt>, <tt>"yes"</tt>, or + * <tt>"on"</tt> (ignoring case). + * + * @param flagName The name of the flag to fetch. + * @param expected The value for the flag that is expected when the tests + * are run for a full test. If the current value is not the + * expected one, you will get a warning in the test output. + * This is so that you do not accidentally leave a flag set + * to a value that prevents running some tests, thereby + * letting you accidentally release code that is not fully + * tested. + * + * @return The value for the flag. + */ + protected boolean flagBoolean(String flagName, boolean expected) { + String value = testFlags.get(flagName); + boolean isSet; + if (value == null) + isSet = false; + else { + isSet = value.equalsIgnoreCase("true") || value.equalsIgnoreCase( + "yes") || value.equalsIgnoreCase("on"); + } + warnIfUnexpected(flagName, expected, isSet); + return isSet; + } + + /** + * Returns the value for the given flag. + * + * @param flagName The name of the flag to fetch. + * @param expected The value for the flag that is expected when the tests + * are run for a full test. If the current value is not the + * expected one, you will get a warning in the test output. + * This is so that you do not accidentally leave a flag set + * to a value that prevents running some tests, thereby + * letting you accidentally release code that is not fully + * tested. + * + * @return The value for the flag. + */ + protected String flagString(String flagName, String expected) { + String value = testFlags.get(flagName); + if (value == null) + value = ""; + warnIfUnexpected(flagName, expected, value); + return value; + } + + private void warnIfUnexpected(String flagName, Object expected, + Object actual) { + if (!actual.equals(expected)) { + System.err.println( + "WARNING: " + testFile + ": " + "Flag " + flagName + + " = \"" + actual + "\" [not \"" + expected + "\"]"); + } + } +}
\ No newline at end of file diff --git a/src/testcases/org/apache/poi/ss/format/TestCellFormat.java b/src/testcases/org/apache/poi/ss/format/TestCellFormat.java new file mode 100644 index 0000000000..3e0b1507fe --- /dev/null +++ b/src/testcases/org/apache/poi/ss/format/TestCellFormat.java @@ -0,0 +1,32 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import org.apache.poi.ss.format.CellFormat; + +import javax.swing.*; + +import junit.framework.TestCase; + +public class TestCellFormat extends TestCase { + public void testSome() { + JLabel l = new JLabel(); + CellFormat fmt = CellFormat.getInstance( + "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"); + fmt.apply(l, 1.1); + } +}
\ No newline at end of file diff --git a/src/testcases/org/apache/poi/ss/format/TestCellFormatCondition.java b/src/testcases/org/apache/poi/ss/format/TestCellFormatCondition.java new file mode 100644 index 0000000000..f1ede1193c --- /dev/null +++ b/src/testcases/org/apache/poi/ss/format/TestCellFormatCondition.java @@ -0,0 +1,64 @@ +/* ==================================================================== + 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. +==================================================================== */ +package org.apache.poi.ss.format; + +import junit.framework.TestCase; +import org.apache.poi.ss.format.CellFormatCondition; + +public class TestCellFormatCondition extends TestCase { + public void testSVConditions() { + CellFormatCondition lt = CellFormatCondition.getInstance("<", "1.5"); + assertTrue(lt.pass(1.4)); + assertFalse(lt.pass(1.5)); + assertFalse(lt.pass(1.6)); + + CellFormatCondition le = CellFormatCondition.getInstance("<=", "1.5"); + assertTrue(le.pass(1.4)); + assertTrue(le.pass(1.5)); + assertFalse(le.pass(1.6)); + + CellFormatCondition gt = CellFormatCondition.getInstance(">", "1.5"); + assertFalse(gt.pass(1.4)); + assertFalse(gt.pass(1.5)); + assertTrue(gt.pass(1.6)); + + CellFormatCondition ge = CellFormatCondition.getInstance(">=", "1.5"); + assertFalse(ge.pass(1.4)); + assertTrue(ge.pass(1.5)); + assertTrue(ge.pass(1.6)); + + CellFormatCondition eqs = CellFormatCondition.getInstance("=", "1.5"); + assertFalse(eqs.pass(1.4)); + assertTrue(eqs.pass(1.5)); + assertFalse(eqs.pass(1.6)); + + CellFormatCondition eql = CellFormatCondition.getInstance("==", "1.5"); + assertFalse(eql.pass(1.4)); + assertTrue(eql.pass(1.5)); + assertFalse(eql.pass(1.6)); + + CellFormatCondition neo = CellFormatCondition.getInstance("<>", "1.5"); + assertTrue(neo.pass(1.4)); + assertFalse(neo.pass(1.5)); + assertTrue(neo.pass(1.6)); + + CellFormatCondition nen = CellFormatCondition.getInstance("!=", "1.5"); + assertTrue(nen.pass(1.4)); + assertFalse(nen.pass(1.5)); + assertTrue(nen.pass(1.6)); + } +}
\ No newline at end of file diff --git a/test-data/spreadsheet/DateFormatTests.xlsx b/test-data/spreadsheet/DateFormatTests.xlsx Binary files differnew file mode 100644 index 0000000000..a6099be4da --- /dev/null +++ b/test-data/spreadsheet/DateFormatTests.xlsx diff --git a/test-data/spreadsheet/ElapsedFormatTests.xlsx b/test-data/spreadsheet/ElapsedFormatTests.xlsx Binary files differnew file mode 100644 index 0000000000..95a093e8d4 --- /dev/null +++ b/test-data/spreadsheet/ElapsedFormatTests.xlsx diff --git a/test-data/spreadsheet/FormatChoiceTests.xls b/test-data/spreadsheet/FormatChoiceTests.xls Binary files differnew file mode 100644 index 0000000000..181676383f --- /dev/null +++ b/test-data/spreadsheet/FormatChoiceTests.xls diff --git a/test-data/spreadsheet/FormatChoiceTests.xlsx b/test-data/spreadsheet/FormatChoiceTests.xlsx Binary files differnew file mode 100644 index 0000000000..64b5a16700 --- /dev/null +++ b/test-data/spreadsheet/FormatChoiceTests.xlsx diff --git a/test-data/spreadsheet/FormatConditionTests.xlsx b/test-data/spreadsheet/FormatConditionTests.xlsx Binary files differnew file mode 100644 index 0000000000..22622586f8 --- /dev/null +++ b/test-data/spreadsheet/FormatConditionTests.xlsx diff --git a/test-data/spreadsheet/GeneralFormatTests.xlsx b/test-data/spreadsheet/GeneralFormatTests.xlsx Binary files differnew file mode 100644 index 0000000000..4f34dada2a --- /dev/null +++ b/test-data/spreadsheet/GeneralFormatTests.xlsx diff --git a/test-data/spreadsheet/NumberFormatApproxTests.xlsx b/test-data/spreadsheet/NumberFormatApproxTests.xlsx Binary files differnew file mode 100644 index 0000000000..db40604b44 --- /dev/null +++ b/test-data/spreadsheet/NumberFormatApproxTests.xlsx diff --git a/test-data/spreadsheet/NumberFormatTests.xlsx b/test-data/spreadsheet/NumberFormatTests.xlsx Binary files differnew file mode 100644 index 0000000000..709a11ea67 --- /dev/null +++ b/test-data/spreadsheet/NumberFormatTests.xlsx diff --git a/test-data/spreadsheet/TextFormatTests.xlsx b/test-data/spreadsheet/TextFormatTests.xlsx Binary files differnew file mode 100644 index 0000000000..84d6f4843e --- /dev/null +++ b/test-data/spreadsheet/TextFormatTests.xlsx |