| /* ==================================================================== |
| 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. |
| * |
| * @author Josh Micich |
| */ |
| 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() + "'"); |
| } |
| } |
| } |