Converters

All converter types are in cloud.baldilorenzo.sheetsmith.convert.

Role of converters

Every non-null field value is turned into a CellValue by a converter before being written. Converters produce values, never formatting: the display format of a cell always comes from the styles and the application defaults.

Built-in converters

Registered type	Covers	Cell written
CharSequence	String, StringBuilder, StringBuffer, any CharSequence	text, via toString()
Character	char, Character	text
Number	byte, short, int, long, float, double, their wrappers, BigDecimal, BigInteger, AtomicInteger, AtomicLong, any Number	number, via doubleValue()
Boolean	boolean, Boolean	Excel boolean (TRUE / FALSE)
Enum	every enum	text: the constant name (name(), not toString())
LocalDate	LocalDate	Excel date
LocalDateTime	LocalDateTime	Excel date with time

Built-in converters are found through the type hierarchy, so subclasses and implementations of the registered types are covered.

Types that need a converter (otherwise V-10), among the most common: java.util.Date, java.sql.Date, java.sql.Timestamp, Calendar, Instant, OffsetDateTime, ZonedDateTime, LocalTime, OffsetTime, YearMonth, Year, Duration, Period, UUID, URI, URL, Path, File, Currency, Locale, Optional, OptionalInt and the other optionals, collections, maps, arrays, Object, and any type of the application (value objects, nested objects). Types with a time zone are excluded on purpose: Excel has no time zones, and an implicit conversion would hide a choice that belongs to the application.

The contract

@FunctionalInterface
public interface CellConverter<T> {
    CellValue convert(T value, ConversionContext context);
}

• The value is never null: a null value produces an empty cell that keeps its style, without calling the converter.
• The result must never be null. Return CellValue.blank() for an empty cell; a null result causes a SheetsmithGenerationException.
• Any RuntimeException thrown by the converter is wrapped in a SheetsmithGenerationException that names the sheet, the row and the field, with the original exception as cause.
• A generator uses one instance of each converter for all its calls, possibly from several threads at the same time: implementations must be thread-safe, ideally stateless.

CellValue

A sealed interface with six forms, created with factory methods:

Factory	Form	Written as
CellValue.text(String)	CellValue.Text	A text cell, never interpreted: leading zeros are kept and a text that looks like a number or a formula stays text. Longer than 32,767 characters fails the generation. Null throws NullPointerException.
CellValue.number(double)	CellValue.Numeric	A number cell. Excel stores numbers as 64-bit floating point values with 15 significant digits. NaN becomes the error #NUM!, infinity the error #DIV/0!.
CellValue.bool(boolean)	CellValue.Bool	An Excel boolean, shown as TRUE or FALSE.
CellValue.date(LocalDate)	CellValue.Date	An Excel date; receives the default date format when no format is set. Null throws.
CellValue.dateTime(LocalDateTime)	CellValue.DateTime	An Excel date with time; receives the default date-time format when no format is set. Null throws.
CellValue.blank()	CellValue.Blank	An empty cell that keeps its resolved style. Singleton.

Because CellValue is sealed, converters cannot write anything else, cannot reach the underlying POI cell and cannot bypass the style system.

ConversionContext

Passed to every call, it tells where the value is being written. Converters can use it to adapt the value or to build error messages.

Method	Returns
String sheetName()	the sheet name, as given in SheetData
int rowIndex()	the 1-based data row, in list order
String fieldName()	the field name, as declared in the sheet class
Class<?> sourceType()	the sheet class
Class<?> valueType()	the declared field type; for a primitive field, the primitive type, even though the value is passed boxed

It is an interface so that methods can be added in future versions without breaking existing converters.

Field converters and application converters

There are two ways to provide a converter.

	Field converter	Application converter
Declared with	@ExcelColumn(converter = X.class)	Sheetsmith.Builder.converter(Type.class, instance), or a Spring bean
Applies to	that column only	every column of that type or of its subtypes, in every sheet class
Provided as	a class, created by the converter factory	an instance
Can be parameterised	through constructor injection only (Spring or a custom factory)	freely, it is an instance you build
Validation	V-11 (compatible type), V-12 (creatable)	duplicates rejected by the builder (IllegalArgumentException) or at Spring startup

How field converters are created.

• One instance per converter class and per generator, created the first time a sheet class declaring it is validated or generated, then reused by every column that declares it. A creation that fails is not remembered: it is attempted again, and reported again as V-12, at the next call.
• Without Spring (default factory): the converter class must be public, with a public no-argument constructor. A nested converter class must be public static.
• With a custom factory (Builder.converterFactory): the factory decides. A factory that throws or returns null makes the declaring class violate V-12.
• With the Spring Boot auto-configuration: if exactly one bean of the converter class exists, that bean is used; otherwise (no bean, or several) a new instance is created with dependency injection (AutowireCapableBeanFactory.createBean): its constructor can receive beans and @Value properties, without the converter becoming a bean.

Compatibility check (V-11). The type handled by a field converter is read from its generic declaration (implements CellConverter<Money>, also through superclasses and intermediate interfaces). The field type, boxed if primitive, must be assignable to it: a CellConverter<Number> is valid on an Integer or int field, a CellConverter<String> on an Integer field is not. When the handled type cannot be determined (for example a generic converter class MyConverter<T> implements CellConverter<T>), the check is skipped, and a mismatch shows up at runtime as a ClassCastException wrapped in a SheetsmithGenerationException.

Resolution order

The converter of a column is chosen once, from the declared type of the field, primitive types being looked up as their wrappers:

1. the field converter, when declared;
2. the application converter registered for the exact type;
3. the application converter registered for the closest superclass;
4. the application converter registered for an implemented interface, the closest first, by breadth-first distance through the type hierarchy;
5. the built-in converters, looked up with the same rules 2 to 4.

Consequences:

• Built-in converters can be replaced by registering an application converter for the same type (for example Boolean written as “Yes”/“No”).
• An application converter for a supertype wins over a built-in converter for the exact type. A converter registered for Object therefore applies to every column without a field converter, String and numbers included. Register converters for the narrowest type that makes sense.
• When two interfaces at the same distance both have an application converter, the resolution is ambiguous and violates V-10. Declare a field converter, or register a converter for the exact type.
• The declared type matters, not the runtime type of the value: a field declared as Object needs a converter even if it always contains strings.

Examples

UUID as text

public class UuidAsText implements CellConverter<UUID> {
    @Override
    public CellValue convert(UUID value, ConversionContext context) {
        return CellValue.text(value.toString());
    }
}

Instant in a given time zone (application converter, parameterised)

public final class InstantConverter implements CellConverter<Instant> {

    private final ZoneId zone;

    public InstantConverter(ZoneId zone) {
        this.zone = zone;
    }

    @Override
    public CellValue convert(Instant value, ConversionContext context) {
        return CellValue.dateTime(LocalDateTime.ofInstant(value, zone));
    }
}

Sheetsmith sheetsmith = Sheetsmith.builder()
        .converter(Instant.class, new InstantConverter(ZoneId.of("Europe/Rome")))
        .build();

OffsetDateTime and ZonedDateTime, keeping the local time of the value

Sheetsmith.builder()
        .converter(OffsetDateTime.class, (value, context) -> CellValue.dateTime(value.toLocalDateTime()))
        .converter(ZonedDateTime.class, (value, context) -> CellValue.dateTime(value.toLocalDateTime()))
        .build();

Legacy java.util.Date (also covers java.sql.Date and java.sql.Timestamp, which extend it)

public final class LegacyDateConverter implements CellConverter<java.util.Date> {
    private final ZoneId zone;
    public LegacyDateConverter(ZoneId zone) { this.zone = zone; }
    @Override
    public CellValue convert(java.util.Date value, ConversionContext context) {
        return CellValue.dateTime(LocalDateTime.ofInstant(value.toInstant(), zone));
    }
}

java.sql.Date.toInstant() throws UnsupportedOperationException. If java.sql.Date values are possible, register a dedicated converter for java.sql.Date (closer superclass, so it wins) that uses toLocalDate().

Enum labels instead of constant names

public interface Labelled {
    String label();
}

public enum OrderStatus implements Labelled {
    OPEN("Open"), SHIPPED("Shipped"), CANCELLED("Cancelled");
    private final String label;
    OrderStatus(String label) { this.label = label; }
    public String label() { return label; }
}

// Application converter for every enum implementing Labelled.
// For such enums the interface is checked before the built-in Enum converter.
Sheetsmith.builder().converter(Labelled.class, (value, context) -> CellValue.text(value.label())).build();

This works because application converters, interfaces included, are searched before built-in converters.

Boolean as Yes / No (replaces the built-in behaviour for every boolean column)

Sheetsmith.builder().converter(Boolean.class, (value, context) -> CellValue.text(value ? "Yes" : "No")).build();

Money value object as a number (format from the style)

public record Money(BigDecimal amount, Currency currency) { }

public final class MoneyConverter implements CellConverter<Money> {
    @Override
    public CellValue convert(Money value, ConversionContext context) {
        return CellValue.number(value.amount().doubleValue());
    }
}

@ExcelColumn(header = "Total", order = 40, format = "#,##0.00", converter = MoneyConverter.class) Money total;

Identifier longer than 15 digits as text

public final class LongAsText implements CellConverter<Long> {
    @Override
    public CellValue convert(Long value, ConversionContext context) {
        return CellValue.text(Long.toString(value));
    }
}

@ExcelColumn(header = "Card reference", order = 10, converter = LongAsText.class) long reference;

Non-finite numbers as empty cells

public final class FiniteOrBlank implements CellConverter<Double> {
    @Override
    public CellValue convert(Double value, ConversionContext context) {
        return Double.isFinite(value) ? CellValue.number(value) : CellValue.blank();
    }
}

Optional, collections, durations

public final class OptionalTextConverter implements CellConverter<Optional<?>> {
    @Override
    public CellValue convert(Optional<?> value, ConversionContext context) {
        return value.map(v -> CellValue.text(v.toString())).orElse(CellValue.blank());
    }
}

public final class TagsConverter implements CellConverter<List<?>> {
    @Override
    public CellValue convert(List<?> value, ConversionContext context) {
        return CellValue.text(value.stream().map(String::valueOf).collect(Collectors.joining(", ")));
    }
}

public final class DurationInHours implements CellConverter<Duration> {
    @Override
    public CellValue convert(Duration value, ConversionContext context) {
        return CellValue.number(value.toMinutes() / 60.0);
    }
}

Using the context in an error

public final class StrictPercentage implements CellConverter<BigDecimal> {
    @Override
    public CellValue convert(BigDecimal value, ConversionContext context) {
        if (value.signum() < 0 || value.compareTo(BigDecimal.ONE) > 0) {
            throw new IllegalArgumentException("percentage out of range in " + context.sourceType().getSimpleName()
                    + "." + context.fieldName() + ": " + value);
        }
        return CellValue.number(value.doubleValue());
    }
}

The exception surfaces as a SheetsmithGenerationException with sheet, row and field, and with the IllegalArgumentException as cause.

CellConverterFactory

public interface CellConverterFactory {
    <C extends CellConverter<?>> C create(Class<C> converterClass);
}

Creates field converters. Implement it to obtain converters from a dependency injection container other than Spring (CDI, Guice, Dagger, a service locator):

Sheetsmith sheetsmith = Sheetsmith.builder()
        .converterFactory(new CellConverterFactory() {
            @Override
            public <C extends CellConverter<?>> C create(Class<C> type) {
                return injector.getInstance(type);
            }
        })
        .build();

The factory is called once per converter class and generator. A RuntimeException or a null result is reported as V-12. Spring Boot applications do not need a custom factory: the auto-configuration installs SpringConverterFactory.

CellConverter.None

The marker used as the default of @ExcelColumn.converter, meaning “no field converter”. It is never instantiated or invoked; leave the attribute unset instead of writing it.