poi/src/test/java/org/apache/poi/ss/util/NumberComparingSpreadsheetGenerator.java

/* ====================================================================
   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.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.NumberComparisonExamples.ComparisonExample;
import org.apache.poi.util.HexDump;

/**
 * Creates a spreadsheet that checks Excel's comparison of various IEEE double values.
 * The class {@link NumberComparisonExamples} contains specific comparison examples
 * (along with their expected results) that get encoded into rows of the spreadsheet.
 * Each example is checked with a formula (in column I) that displays either "OK" or
 * "ERROR" depending on whether actual results match those expected.
 */
public class NumberComparingSpreadsheetGenerator {

    private static final class SheetWriter {

        private final HSSFSheet _sheet;
        private int _rowIndex;

        public SheetWriter(HSSFWorkbook wb) {
            HSSFSheet sheet = wb.createSheet("Sheet1");

            writeHeaderRow(wb, sheet);
            _sheet = sheet;
            _rowIndex = 1;
        }

        void addTestRow(double a, double b, int expResult) {
            writeDataRow(_sheet, _rowIndex++, a, b, expResult);
        }
    }

    private static void writeHeaderCell(HSSFRow row, int i, String text, HSSFCellStyle style) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(new HSSFRichTextString(text));
        cell.setCellStyle(style);
    }
    static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 3600);
        sheet.setColumnWidth(3, 3600);
        sheet.setColumnWidth(4, 2400);
        sheet.setColumnWidth(5, 2400);
        sheet.setColumnWidth(6, 2400);
        sheet.setColumnWidth(7, 2400);
        sheet.setColumnWidth(8, 2400);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        writeHeaderCell(row, 0, "Raw Long Bits A", style);
        writeHeaderCell(row, 1, "Raw Long Bits B", style);
        writeHeaderCell(row, 2, "Value A", style);
        writeHeaderCell(row, 3, "Value B", style);
        writeHeaderCell(row, 4, "Exp Cmp", style);
        writeHeaderCell(row, 5, "LT", style);
        writeHeaderCell(row, 6, "EQ", style);
        writeHeaderCell(row, 7, "GT", style);
        writeHeaderCell(row, 8, "Check", style);
    }
    /**
     * Fills a spreadsheet row with one comparison example. The two numeric values are written to
     * columns C and D. Columns (F, G and H) respectively get formulas ("v0<v1", "v0=v1", "v0>v1"),
     * which will be evaluated by Excel. Column D gets the expected comparison result. Column I
     * gets a formula to check that Excel's comparison results match that predicted in column D.
     *
     * @param v0 the first value to be compared
     * @param v1 the second value to be compared
     * @param expRes expected comparison result (-1, 0, or +1)
     */
    static void writeDataRow(HSSFSheet sheet, int rowIx, double v0, double v1, int expRes) {
        HSSFRow row = sheet.createRow(rowIx);

        int rowNum = rowIx + 1;


        row.createCell(0).setCellValue(formatDoubleAsHex(v0));
        row.createCell(1).setCellValue(formatDoubleAsHex(v1));
        row.createCell(2).setCellValue(v0);
        row.createCell(3).setCellValue(v1);
        row.createCell(4).setCellValue(expRes < 0 ? "LT" : expRes > 0 ? "GT" : "EQ");
        row.createCell(5).setCellFormula("C" + rowNum + "<" + "D" + rowNum);
        row.createCell(6).setCellFormula("C" + rowNum + "=" + "D" + rowNum);
        row.createCell(7).setCellFormula("C" + rowNum + ">" + "D" + rowNum);
        // TODO - bug elsewhere in POI - something wrong with encoding of NOT() function
        String frm = "if(or(" +
            "and(E#='LT', F#      , G#=FALSE, H#=FALSE)," +
            "and(E#='EQ', F#=FALSE, G#      , H#=FALSE)," +
            "and(E#='GT', F#=FALSE, G#=FALSE, H#      )" +
            "), 'OK', 'error')"    ;
        row.createCell(8).setCellFormula(frm.replaceAll("#", String.valueOf(rowNum)).replace('\'', '"'));
    }

    private static String formatDoubleAsHex(double d) {
        long l = Double.doubleToLongBits(d);
        return HexDump.longToHex(l)+'L';
    }

    public static void main(String[] args) throws IOException {
        try (HSSFWorkbook wb = new HSSFWorkbook()) {
            SheetWriter sw = new SheetWriter(wb);
            ComparisonExample[] ces = NumberComparisonExamples.getComparisonExamples();
            for (ComparisonExample ce : ces) {
                sw.addTestRow(ce.getA(), ce.getB(), ce.getExpectedResult());
            }

            File outputFile = new File("ExcelNumberCompare.xls");
            try (FileOutputStream os = new FileOutputStream(outputFile)) {
                wb.write(os);
            }
            System.out.println("Finished writing '" + outputFile.getAbsolutePath() + "'");
        }
    }
}