Excel in Java

Vladimír Oraný

Test Facilitator @ Agorapulse

@musketyr

http://spreadsheet.dsl.builders/

http://bit.ly/2xEfzau

public class Car {

    private String driver;
    private String from;
    private Date date;
    private int max;
    private int comfortable;
    private String phone;
    private List<String> passengers;

    // ...
}

SpreadsheetBuilder builder = PoiSpreadsheetBuilder.create(to);
builder.build(w ->
    w.sheet("Car Pool", s -> {
        s.row(r -> {
            r.cell("From");
            r.cell("Date");
            r.cell("Driver");
            r.cell("Max.");
            r.cell("Comf.");
            r.cell("Passengers");
        });
        for (Car car : cars) {
            s.row(r -> {
                r.cell(car.getFrom());
                r.cell(c -> {
                    c.value(car.getDate());
                    c.style(st -> st.format("ddd hh:mm"));
                });
                r.cell(car.getDriver());
                r.cell(car.getMax());
                r.cell(car.getComfortable());
                r.cell(car.getPassengers().stream().collect(joining(", ")));
            });
        }
    })
);

builder.build(w -> w.sheet("Car Pool", s -> {
    s.freeze(2, 1);
    s.row(r -> {
        r.style(st -> st.font(f -> f.style(bold)));
        r.cell(headerCell("From"));
        r.cell(headerCell("Date"));
        r.cell(headerCell("Driver"));
        r.cell(headerCell("Max."));
        r.cell(headerCell("Comf."));
        r.cell(headerCell("Passengers"));
    });

private static Configurer<CellDefinition> headerCell(final String value) {
    return c -> {
        c.width(auto);
        c.value(value);
    };
}

s.page(p -> {
    p.fit(width);
    p.orientation(portrait);
    p.paper(A4);
});
// ...
for (Car car : cars) {
    List<String> passengers = new ArrayList<>(car.getPassengers());
    if (passengers.isEmpty()) {
        passengers.add("");
    }
    s.row(r -> {
        int rowspan = passengers.size();
        r.cell(cellWithRowspan(car.getFrom(), rowspan));
        r.cell(cellWithRowspanAndFormat(car.getDate(), rowspan, "ddd hh:mm"));
        r.cell(cellWithRowspan(car.getDriver(), rowspan));
        r.cell(cellWithRowspan(car.getMax(), rowspan));
        r.cell(cellWithRowspan(car.getComfortable(), rowspan));
        r.cell(passengers.get(0));
    });

    if (passengers.size() > 1) {
        for (int i = 1; i < passengers.size(); i++) {
            final int index = i;
            s.row(r -> r.cell("F", c -> c.value(passengers.get(index))));
        }
    }
}

private static Configurer<CellDefinition> cellWithRowspan(final Object value, final int span) {
    return c -> {
        c.value(value);
        c.rowspan(span);
        c.style(st -> st.align(center, center));
    };
}

private static Configurer<CellDefinition> cellWithRowspanAndFormat(final Object value, 
    final int span, final String format) {
    
    return c -> {
        c.value(value);
        c.rowspan(span);
        c.style(st -> {
            st.align(center, center);
            st.format(format);
        });
    };
}

private static Configurer<CellDefinition> headerCellWithWidth(final String value, 
    final int width) {
    
    return c -> {
        c.width(width).cm();
        c.value(value);
    };
}

w.style("ok", st -> st.foreground(lightGreen));
w.style("at-capacity", st -> st.foreground(lightYellow));
w.style("full", st -> st.foreground(lightCoral));
w.style("center", st -> st.align(center, center));
w.style("date", st -> st.format("ddd hh:mm"));
w.style("header", st -> {
    st.font(f -> f.style(bold));
});

s.row(r -> {
    r.style(getStyle(car));
    int rowspan = passengers.size();
    r.cell(cellWithRowspanAndStyles(car.getFrom(), rowspan, "center"));
    r.cell(cellWithRowspanAndStyles(car.getDate(), rowspan, "center", "date"));
    r.cell(cellWithRowspanAndStyles(car.getDriver(), rowspan, "center"));
    r.cell(cellWithRowspanAndStyles(car.getMax(), rowspan, "center"));
    r.cell(cellWithRowspanAndStyles(car.getComfortable(), rowspan, "center"));
    r.cell(cellWithRowspanAndStyles(passengers.get(0), 1));
});

private static Configurer<CellDefinition> cellWithRowspanAndStyles(final Object value, 
    final int span, final String... styles) {
    
    return c -> {
        c.value(value);
        c.rowspan(span);
        c.styles(styles);
    };
}

SpreadsheetCriteria criteria = PoiSpreadsheetCriteria.FACTORY.forFile(file);
assertTrue(criteria.exists(w ->
    w.sheet("Car Pool", s ->
        s.row(r ->
            r.cell(c -> {
                c.value("Ladislav Prskavec");
                c.style(st ->
                   st.foreground(lightGreen)
                );
            }
)))));

void 'export with colors'() {
    when:
        File file = tmp.newFile(System.currentTimeMillis() + 'colors.xlsx')
        CarPoolToExcel.withStyles(dru.findAllByType(Car), file)
        SpreadsheetCriteria criteria = PoiSpreadsheetCriteria.FACTORY.forFile(file)
    then:
        criteria.exists {
            sheet "Car Pool", {
                row {
                    cell {
                        value 'Ladislav Prskavec'
                        style {
                            foreground lightGreen
                        }
                    }
                }
            }
        }
}