Package output

Source Code of output.ExcelFileFormulas$BasicSpreadsheetLayout

package output;

import java.io.Closeable;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Map.Entry;

import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.annotations.VisibleForTesting;

import ra.Result;
import ra.Utils;
import core.Column;
import core.ColumnLabels;
import core.Formulas;

/**
* Represents an Excel spreadsheet file and the process of populating it
* with the results of AlgebraToSpreadsheet.
*
* Do not forget to {@link #close} any instance - otherwise the file will not be written.
*/
public class ExcelFileFormulas implements Closeable {

    private final OutputStream out;
    private final XSSFWorkbook wb;

    /**
     * For use only from tests.
     */
    @VisibleForTesting
    public XSSFWorkbook getWorkbookForTestPurposes() {
        return wb;
    }

    private final Sheet s;

    private boolean fileClosed;

    /**
     * Choose a {@link OutputStream} to save the spreadsheet to.
     * Insert queries with {@link #addResult(Formulas, ColumnLabels)},
     * then {@link #close} this instance to make sure the file is actually written.
     *
     * @param outputStream
     */
    public ExcelFileFormulas(OutputStream outputStream) {
        out = outputStream;
        fileClosed = false;

        // create a new workbook
        wb = new XSSFWorkbook();
        // create a new sheet
        s = wb.createSheet();
        s.createRow(0);
        s.createRow(1);

        wb.setSheetName(0, "RelAlg result");
    }

    /**
     * Add formulas and column labels to workbook
     * User must eventually call {@link #close} on this instance to make sure the file is actually written.
     *
     * @throws FormulaParseException
     * @throws IOException the file is not open
     * @return this
     */
    public ExcelFileFormulas addResult(Result result, SpreadsheetLayout layout)
            throws IOException, FormulaParseException {
        if (fileClosed) {
            throw new IOException("Cannot write Formulas while in closed state!");
        }

        layout.populateSheet(this, result);

        return this;
    }

    private void addAllCommentsToOneCell(Drawing drawing, CreationHelper factory, Cell c, String... texts) {
        ClientAnchor anchor = factory.createClientAnchor();
//        System.out.println("a"+c.toString());
//        System.out.println("a"+c.getColumnIndex());
//        System.out.println("a"+c.getRowIndex());
//        System.out.println("_____________________"+c);

        int commentLines = 0;
        for (int i = 0; i < texts.length; ++i) {
            commentLines += 1;
            commentLines += texts[i].length() - texts[i].replace("\n","").length();
        }

        anchor.setCol1(c.getColumnIndex());
        anchor.setCol2(c.getColumnIndex() + 7);
        anchor.setRow1(c.getRowIndex());
        anchor.setRow2(c.getRowIndex() + commentLines);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        StringBuilder sb = new StringBuilder();
        String separator = "";
        for (String str : texts) {
            sb.append(separator);
            sb.append(str);
            separator="\n";
        }
        RichTextString str = factory.createRichTextString(sb.toString());
        comment.setString(str);
        comment.setVisible(Boolean.FALSE);
        comment.setAuthor("AlgebraToSpreadsheet");

        // Assign the comment to the cell
        c.setCellComment(comment);
    }

    public static interface SpreadsheetLayout {
        /**
         * Populate the sheet object.
         * Does not clear the sheet, if it has been populated before.
         * Thus this method should be called only once on a single instance of ExcelFileFormulas.
         */
        void populateSheet(ExcelFileFormulas eff, Result result);
    }

  /** all intermediate results' columns are visible */
    public static final SpreadsheetLayout BASIC_LAYOUT  = new BasicSpreadsheetLayout();

  /** intermediate results' columns are hidden; the =NA() cells are conditionally formatted to be less visible */
    public static final SpreadsheetLayout QUIET_LAYOUT = new QuietSpreadsheetLayout();

    private static final class BasicSpreadsheetLayout implements SpreadsheetLayout {

        @Override
        public void populateSheet(ExcelFileFormulas eff, Result result) {
            Formulas forms = result.getFormulas();
            ColumnLabels labels = result.getLabels();
            Sheet s = eff.s;
            Cell c;

            Row[] row = new Row[]{s.getRow(0), s.getRow(1)};

            for (Entry<core.Cell, String> entry : forms.entrySet()) {
                c = row[entry.getKey().getRow() - 1].createCell(entry.getKey().getColumn() - 1);
                c.setCellType(XSSFCell.CELL_TYPE_FORMULA);
                c.setCellFormula(entry.getValue());
//                System.out.println(c.getColumnIndex()+"-"+c.getRowIndex());
//                System.out.println(entry.getValue());
//                c.setCellValue(entry.getValue());
            }

            Drawing drawing = s.createDrawingPatriarch();
            CreationHelper factory = eff.wb.getCreationHelper();
            for (Column col : labels) {
//                System.out.println("label="+(c.getNumber()-1));
//                System.out.println( Arrays.toString(labels.get(c).toArray(new String[0])));
//                System.out.println(r[0]);
//                System.out.println(r[0].getCell(c.getNumber()-1));
                eff.addAllCommentsToOneCell(drawing, factory, row[0].getCell(col.getNumber()-1), labels.get(col).toArray(new String[0]));
            }
        }
    }

    private static final class QuietSpreadsheetLayout implements SpreadsheetLayout {

        private ColumnLabels labels;
        private Sheet s;
        private Row[] rows;
        private ExcelFileFormulas eff;
        private Result result;

        /**
         * @return index, counting from 0
         */
        private int firstResultColumn() {
            return result.getResultRelation().getFirstCol().getNumber() - 1;
        }

        private Column firstIntermediateColV;
        private Column maxColV;

        /* TODO what if there are no columns to be written ?
         * minCol, maxCol would be null. TODO should not dereference null*/
        private Column firstIntermediateColumn() {
            if (firstIntermediateColV == null) {
                for (Column col : labels) {
                    if (firstIntermediateColV == null || firstIntermediateColV.getNumber() > col.getNumber()) {
                        lookForEvidenceItsIntermediate: for (String label : labels.get(col)) {
                            if (label.contains("is a")) {
                                firstIntermediateColV = col;
                                break lookForEvidenceItsIntermediate;
                            }
                        }
                    }
                }
            }
            return firstIntermediateColV;
        }

        private Column maxCol() {
            if (maxColV == null)
            {
                for (Column col : labels) {
                    if (maxColV == null || maxColV.getNumber() < col.getNumber()) {
                        maxColV = col;
                    }
                }
            }
            return maxColV;
        }

        @Override
        public void populateSheet(ExcelFileFormulas eff, Result result) {
            this.labels = result.getLabels();
            this.result = result;
            this.eff = eff;
            this.s = eff.s;
            this.rows = new Row[] { s.getRow(0), s.getRow(1) };
            this.firstIntermediateColV = null;
            this.maxColV = null;

            writeCellFormulas();
            addCommentsToColumns();
            addBorderToTheLeftOfResults();
            doNotDisplayIntermediateResults();
            applyColorsThatConcealTheEmptyRows();
        }

        private void writeCellFormulas() {
            Formulas forms = result.getFormulas();
            for (Entry<core.Cell, String> entry : forms.entrySet()) {
                Row row = rows[entry.getKey().getRow()-1];
                int columnNo = entry.getKey().getColumn() - 1;
                Cell c = row.getCell(columnNo);
                if (c == null) {
                    c = row.createCell(columnNo);
                }
                c.setCellType(XSSFCell.CELL_TYPE_FORMULA);
                c.setCellFormula(entry.getValue());
            }
        }

        private void addCommentsToColumns() {
            Drawing drawing = s.createDrawingPatriarch();
            CreationHelper factory = eff.wb.getCreationHelper();

            for (Column col : labels) {
                int columnIndex = col.getNumber() - 1;

                eff.addAllCommentsToOneCell(drawing, factory, rows[0].getCell(columnIndex), labels.get(col).toArray(new String[0]));
            }
        }

        private void addBorderToTheLeftOfResults() {
            /* add border between the input and the result relations */
            CellStyle style = eff.wb.createCellStyle();
            style.setBorderLeft(CellStyle.BORDER_THIN);
            if (firstResultColumn() != 0) {
                rows[0].getCell(firstResultColumn()).setCellStyle(style);
                rows[1].getCell(firstResultColumn()).setCellStyle(style);
            }
        }

        private void doNotDisplayIntermediateResults() {
            /* group the intermediate values' columns */
            if (firstIntermediateColumn() != null && firstResultColumn() >= 1)
            {
                // TODO fix bug in a different way than avoiding the branch
                final int fromColumn = firstIntermediateColumn().getNumber()-1;
                final int toColumn = firstResultColumn() - 1;
                if (toColumn >= fromColumn) {
                    s.groupColumn(fromColumn, toColumn);
                    s.setColumnGroupCollapsed(fromColumn, true);
                }
            }
        }

        private void applyColorsThatConcealTheEmptyRows() {
            /* display the NA() values in light gray so they are less visible */
            SheetConditionalFormatting cf = s.getSheetConditionalFormatting();
            ConditionalFormattingRule rule = cf.createConditionalFormattingRule("ISNA(A1)");
            FontFormatting fontFmt = rule.createFontFormatting();
            fontFmt.setFontColorIndex(IndexedColors.GREY_25_PERCENT.index);
            String range;
            if (maxCol() != null) {
                range = String.format("A1:%s1048576", maxCol().toString(Utils.getCaf()));
            } else {
                // TODO fix in a different way
                range = "A1:ZZ1048576";
            }
            CellRangeAddress[] dataRange = { CellRangeAddress.valueOf(range) };
            cf.addConditionalFormatting(dataRange, rule);
        }

    }

    /**
     * Writes the spreadsheet and closes the file.
     *
     * @side writes the spreadsheet to the file (the file will not be written if {@link #close} is not called}
     * @side closes the {@link FileOutputStream} this instance writes to
     */
    @Override
    public void close() throws IOException {
        if (fileClosed) {
            return ;
        }
        fileClosed = true;

        // TODO maybe XSSFFormulaEvaluator.evaluateAllFormulaCells(wb) -- so the users gets a spreadsheet with some cached results
        wb.write(out);
        out.close();
    }

}
TOP

Related Classes of output.ExcelFileFormulas$BasicSpreadsheetLayout

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.