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();
}
}