Importing Data from Excel

package bookshelf.imports;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ProblemDetail;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
@RequestMapping("/api/books")
public class BookImportController {

    static final MediaType XLSX =
            MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    private final BookImportService service;
    private final ErrorWorkbookWriter errorWriter = new ErrorWorkbookWriter();
    private final Path uploadDir;

    public BookImportController(BookImportService service, Path uploadDir) {
        this.service = service;
        this.uploadDir = uploadDir; // a private directory, outside the web root
    }

    @PostMapping(path = "/import", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseEntity<?> importBooks(@RequestPart("file") MultipartFile file,
                                         @RequestParam(defaultValue = "ALL_OR_NOTHING") ImportMode mode,
                                         @RequestParam(defaultValue = "json") String format) throws IOException {
        if (file.isEmpty()) {
            return ResponseEntity.badRequest().body(ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, "No file."));
        }
        Path stored = Files.createTempFile(uploadDir, "upload-", ".xlsx"); // never the client's file name
        try {
            file.transferTo(stored);
            // validate type and size here: see "Security & Untrusted Files" (UploadValidator)
            ImportReport report = service.importFile(stored, file.getOriginalFilename(), mode);

            HttpStatus status = switch (report.status()) {
                case "IMPORTED", "PARTIALLY_IMPORTED" -> HttpStatus.OK;
                default -> HttpStatus.UNPROCESSABLE_ENTITY;
            };
            // a sheet-level error (rowsRead 0) answers JSON: the sheet may be over the row cap, so do not open it again
            if ("xlsx".equals(format) && !report.errors().isEmpty() && report.rowsRead() > 0) {
                ByteArrayOutputStream out = new ByteArrayOutputStream();
                errorWriter.write(stored, report.errors(), out);
                return ResponseEntity.status(status)
                        .contentType(XLSX)
                        .header(HttpHeaders.CONTENT_DISPOSITION,
                                ContentDisposition.attachment().filename("books-import-errors.xlsx").build().toString())
                        .header("X-Import-Status", report.status())
                        .body(out.toByteArray());
            }
            return ResponseEntity.status(status).body(report);
        } catch (InvalidFormatException e) {
            return ResponseEntity.unprocessableEntity()
                    .body(ProblemDetail.forStatusAndDetail(HttpStatus.UNPROCESSABLE_ENTITY, "Not a valid .xlsx file."));
        } finally {
            Files.deleteIfExists(stored);
        }
    }

    @ExceptionHandler(UnreadableWorkbookException.class)
    public ProblemDetail unreadable(UnreadableWorkbookException e) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.UNPROCESSABLE_ENTITY, e.getMessage());
    }

    @ExceptionHandler(AlreadyImportedException.class)
    public ProblemDetail alreadyImported(AlreadyImportedException e) {
        ProblemDetail problem = ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT, e.getMessage());
        problem.setProperty("batchId", e.batchId());
        return problem;
    }
}

package app;

import bookshelf.imports.*;
import jakarta.validation.Validator;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Import;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.support.TransactionTemplate;

@SpringBootApplication
@Import(BookImportController.class)
public class BookshelfApplication {

    public static void main(String[] args) {
        SpringApplication.run(BookshelfApplication.class, args);
    }

    @Bean
    BookRowMapper bookRowMapper(Validator validator) {
        return new BookRowMapper(validator);
    }

    @Bean
    BookSheetReader bookSheetReader(BookRowMapper mapper) {
        return new BookSheetReader(mapper);
    }

    @Bean
    BookRepository bookRepository(JdbcTemplate jdbc) {
        return new BookRepository(jdbc);
    }

    @Bean
    BookImportService bookImportService(BookSheetReader reader, BookRepository repository, TransactionTemplate tx) {
        return new BookImportService(reader, repository, tx);
    }

    @Bean
    Path uploadDir(@Value("${bookshelf.upload-dir}") Path dir) throws IOException {
        return Files.createDirectories(dir);
    }
}

spring.servlet.multipart.max-file-size=10MB
spring.servlet.multipart.max-request-size=12MB
spring.servlet.multipart.file-size-threshold=1MB
bookshelf.upload-dir=${java.io.tmpdir}/bookshelf-uploads

$ head -c 12000000 /dev/zero > big.xlsx
$ curl -s -o /dev/null -w 'HTTP %{http_code}\n' -F "file=@big.xlsx" http://localhost:8765/api/books/import
HTTP 413

package bookshelf.imports;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.MultipartConfig;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.servlet.http.Part;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;

@WebServlet("/servlet/books/import")
@MultipartConfig(
        fileSizeThreshold = 1024 * 1024,      // above 1 MiB the container spools the part to disk
        maxFileSize = 10L * 1024 * 1024,      // one part, 10 MiB
        maxRequestSize = 12L * 1024 * 1024)   // the whole request, 12 MiB
public class BookImportServlet extends HttpServlet {

    private BookImportService service;
    private Path uploadDir;

    @Override
    public void init() {
        // the container calls the no-arg constructor, so take the collaborators from the context
        service = (BookImportService) getServletContext().getAttribute(BookImportService.class.getName());
        uploadDir = (Path) getServletContext().getAttribute("bookshelf.uploadDir");
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Part part = request.getPart("file");          // IllegalStateException when a size limit is exceeded
        if (part == null || part.getSize() == 0) {
            response.sendError(HttpServletResponse.SC_BAD_REQUEST, "No file.");
            return;
        }
        Path stored = Files.createTempFile(uploadDir, "upload-", ".xlsx");
        try (InputStream in = part.getInputStream()) {
            Files.copy(in, stored, java.nio.file.StandardCopyOption.REPLACE_EXISTING);
            ImportReport report = service.importFile(stored, part.getSubmittedFileName(), ImportMode.ALL_OR_NOTHING);
            response.setStatus(report.status().equals("REJECTED") ? 422 : 200);
            response.setContentType("application/json");
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.getWriter().write(ReportJson.toJson(report));
        } catch (AlreadyImportedException e) {
            response.sendError(HttpServletResponse.SC_CONFLICT, e.getMessage());
        } finally {
            Files.deleteIfExists(stored);
            part.delete(); // release the container's own temporary copy
        }
    }
}

package bookshelf.imports;

import jakarta.inject.Inject;
import jakarta.ws.rs.Consumes;
import jakarta.ws.rs.DefaultValue;
import jakarta.ws.rs.POST;
import jakarta.ws.rs.Path;
import jakarta.ws.rs.Produces;
import jakarta.ws.rs.QueryParam;
import jakarta.ws.rs.core.MediaType;
import jakarta.ws.rs.core.Response;
import java.io.IOException;
import org.jboss.resteasy.reactive.RestForm;
import org.jboss.resteasy.reactive.multipart.FileUpload;

@Path("/api/books/import")
public class BookImportResource {

    @Inject
    BookImportService service;

    @POST
    @Consumes(MediaType.MULTIPART_FORM_DATA)
    @Produces(MediaType.APPLICATION_JSON)
    public Response upload(@RestForm("file") FileUpload file,
                           @QueryParam("mode") @DefaultValue("ALL_OR_NOTHING") ImportMode mode) throws IOException {
        // Quarkus has already spooled the part to quarkus.http.body.uploads-directory and deletes it after the request
        ImportReport report = service.importFile(file.uploadedFile(), file.fileName(), mode);
        int status = report.status().equals("REJECTED") ? 422 : 200;
        return Response.status(status).entity(report).build();
    }
}

quarkus.http.limits.max-body-size=12M
quarkus.http.body.uploads-directory=/var/lib/bookshelf/uploads
quarkus.http.body.delete-uploaded-files-on-end=true

package bookshelf.imports;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaError;

/**
 * Turns any cell into canonical text, or null when it is blank. Dates become ISO-8601 whatever the cell format shows,
 * numbers become plain decimals (never "1.0E7"), formulas yield their cached result.
 */
public final class CellText {

    /** A cell holding an Excel error value such as #DIV/0!. */
    public static final class CellErrorException extends Exception {
        public CellErrorException(String message) {
            super(message);
        }
    }

    private final DataFormatter formatter = new DataFormatter(java.util.Locale.ROOT);

    public String of(Cell cell) throws CellErrorException {
        if (cell == null) {
            return null;
        }
        CellType type = cell.getCellType() == CellType.FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType();
        switch (type) {
            case BLANK:
                return null;
            case STRING:
                return blankToNull(cell.getStringCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    LocalDateTime value = cell.getLocalDateTimeCellValue();
                    return value.toLocalTime().equals(java.time.LocalTime.MIDNIGHT)
                            ? value.toLocalDate().toString() : value.toString();
                }
                return BigDecimal.valueOf(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
            case ERROR:
                throw new CellErrorException("the cell contains the error " + FormulaError.forInt(cell.getErrorCellValue()).getString());
            default:
                return blankToNull(formatter.formatCellValue(cell));
        }
    }

    private static String blankToNull(String text) {
        if (text == null) {
            return null;
        }
        String stripped = text.replace(' ', ' ').strip();
        return stripped.isEmpty() ? null : stripped;
    }
}

package bookshelf.imports;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/** Small helpers for the cases that make a spreadsheet different from a table. */
public final class Sheets {

    private Sheets() {
    }

    /** True when the row is missing, has no cells, or has only blank / whitespace-only cells. */
    public static boolean isBlank(Row row, CellText text) {
        if (row == null) {
            return true;
        }
        for (Cell cell : row) {
            try {
                if (text.of(cell) != null) {
                    return false;
                }
            } catch (CellText.CellErrorException e) {
                return false; // an error value is content, not blank
            }
        }
        return true;
    }

    /**
     * A merged region stores its value only in the top-left cell; the other cells read as blank. Returns the cell that
     * really holds the value for (row, column), or the cell itself when it is not part of a merged region.
     */
    public static Cell valueCell(Sheet sheet, Row row, int column) {
        Cell own = row.getCell(column);
        if (own != null && own.getCellType() != org.apache.poi.ss.usermodel.CellType.BLANK) {
            return own;
        }
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(row.getRowNum(), column)) {
                Row top = sheet.getRow(region.getFirstRow());
                return top == null ? null : top.getCell(region.getFirstColumn());
            }
        }
        return own;
    }
}

package bookshelf.imports;

import java.sql.Date;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Optional;
import java.util.Set;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.core.PreparedStatementCreator;

public class BookRepository {

    private static final int BATCH_SIZE = 500;

    private final JdbcTemplate jdbc;
    private final NamedParameterJdbcTemplate named;

    public BookRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
        this.named = new NamedParameterJdbcTemplate(jdbc);
    }

    public Optional<Long> findBatchByHash(String sha256) {
        List<Long> ids = jdbc.queryForList("select id from import_batch where file_sha256 = ?", Long.class, sha256);
        return ids.stream().findFirst();
    }

    /** The unique index on file_sha256 is the real guard against two concurrent uploads of the same file. */
    public long insertBatch(String sha256, String fileName, int rows) {
        KeyHolder keys = new GeneratedKeyHolder();
        PreparedStatementCreator creator = con -> {
            var ps = con.prepareStatement(
                    "insert into import_batch(file_sha256, file_name, rows_imported, imported_at) values (?, ?, ?, ?)",
                    new String[] {"id"});
            ps.setString(1, sha256);
            ps.setString(2, fileName);
            ps.setInt(3, rows);
            ps.setTimestamp(4, Timestamp.from(Instant.now()));
            return ps;
        };
        jdbc.update(creator, keys); // throws DuplicateKeyException when the file was imported before
        return keys.getKey().longValue();
    }

    /** ISBNs that are already in the table, looked up in chunks to keep the IN list short. */
    public Set<String> existingIsbns(Collection<String> isbns) {
        Set<String> found = new HashSet<>();
        List<String> all = new ArrayList<>(isbns);
        for (int from = 0; from < all.size(); from += BATCH_SIZE) {
            List<String> chunk = all.subList(from, Math.min(from + BATCH_SIZE, all.size()));
            found.addAll(named.queryForList("select isbn from book where isbn in (:isbns)",
                    new MapSqlParameterSource("isbns", chunk), String.class));
        }
        return found;
    }

    public void insertAll(long batchId, List<BookRow> books) {
        jdbc.batchUpdate(
                "insert into book(isbn, title, author, published_on, price, copies, batch_id) values (?, ?, ?, ?, ?, ?, ?)",
                books, BATCH_SIZE, (ps, book) -> {
                    ps.setString(1, book.isbn());
                    ps.setString(2, book.title());
                    ps.setString(3, book.author());
                    ps.setDate(4, Date.valueOf(book.publishedOn()));
                    ps.setBigDecimal(5, book.price());
                    ps.setInt(6, book.copies());
                    ps.setLong(7, batchId);
                });
    }
}

create table import_batch (
    id            bigint generated always as identity primary key,
    file_sha256   char(64) not null unique,
    file_name     varchar(255) not null,
    rows_imported int not null,
    imported_at   timestamp not null
);

create table book (
    isbn         char(13) primary key,
    title        varchar(200) not null,
    author       varchar(120) not null,
    published_on date not null,
    price        decimal(8, 2) not null,
    copies       int not null,
    batch_id     bigint not null references import_batch (id)
);

package bookshelf.imports;

import java.util.List;

/** What the client gets back, as JSON or as the annotated workbook. */
public record ImportReport(String fileName, String status, int rowsRead, int rowsImported, int rowsRejected,
                           Long batchId, List<RowError> errors) {
}

{
  "fileName": "books-import.xlsx",
  "status": "REJECTED",
  "rowsRead": 8,
  "rowsImported": 0,
  "rowsRejected": 8,
  "batchId": null,
  "errors": [
    { "row": 4, "column": "ISBN", "message": "must be exactly 13 digits" },
    { "row": 4, "column": "TITLE", "message": "must not be blank" },
    { "row": 4, "column": "PUBLISHED_ON", "message": "must be a date in the past or in the present" },
    { "row": 4, "column": "PRICE", "message": "must be greater than or equal to 0.00" },
    { "row": 4, "column": "COPIES", "message": "'2.5' is not a whole number" },
    { "row": 5, "column": "PUBLISHED_ON", "message": "'last spring' is not a date (use a real Excel date or yyyy-MM-dd)" },
    { "row": 5, "column": "PRICE", "message": "'abc' is not a number" },
    { "row": 7, "column": "ISBN", "message": "duplicate of row 2 in this file" },
    { "row": 10, "column": "PRICE", "message": "the cell contains the error +#DIV/0!+" }
  ]
}

package bookshelf.imports;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.StandardCopyOption;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.stream.Collectors;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/** Writes an annotated copy of the uploaded workbook: red cells, a comment on each, and an "Import errors" column. */
public final class ErrorWorkbookWriter {

    public static final String ERRORS_HEADER = "Import errors";

    public void write(Path uploaded, List<RowError> errors, OutputStream out)
            throws IOException, InvalidFormatException {
        // XSSFWorkbook(File) saves its changes back into that file on close(), so work on a private copy
        Path copy = Files.createTempFile(uploaded.getParent(), "annotated-", ".xlsx");
        try {
            Files.copy(uploaded, copy, StandardCopyOption.REPLACE_EXISTING);
            annotate(copy, errors, out);
        } finally {
            Files.deleteIfExists(copy);
        }
    }

    private void annotate(Path copy, List<RowError> errors, OutputStream out)
            throws IOException, InvalidFormatException {
        try (XSSFWorkbook workbook = new XSSFWorkbook(copy.toFile())) {
            Sheet sheet = workbook.getSheetAt(0);
            CreationHelper factory = workbook.getCreationHelper();
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            Row header = sheet.getRow(sheet.getFirstRowNum());
            int errorColumn = Math.max(header.getLastCellNum(), 0);

            Map<Integer, BookColumn> headerColumns = new HashMap<>();
            for (Cell cell : header) {
                BookColumn.forHeader(cell.getStringCellValue()).ifPresent(c -> headerColumns.put(cell.getColumnIndex(), c));
            }
            Map<BookColumn, Integer> indexOf = new HashMap<>();
            headerColumns.forEach((index, column) -> indexOf.put(column, index));

            Font bold = workbook.createFont();
            bold.setBold(true);
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.cloneStyleFrom(header.getCell(0).getCellStyle());
            headerStyle.setFont(bold);
            Cell errorHeader = header.createCell(errorColumn);
            errorHeader.setCellValue(ERRORS_HEADER);
            errorHeader.setCellStyle(headerStyle);

            CellStyle wrap = workbook.createCellStyle();
            wrap.setWrapText(true);
            Map<Short, CellStyle> redByOriginal = new HashMap<>(); // one new style per original style, not per cell

            Map<Integer, List<RowError>> byRow = errors.stream().collect(
                    Collectors.groupingBy(RowError::row, TreeMap::new, Collectors.toList()));
            for (Map.Entry<Integer, List<RowError>> entry : byRow.entrySet()) {
                int rowIndex = entry.getKey() - 1;
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                Cell summary = row.createCell(errorColumn);
                summary.setCellValue(entry.getValue().stream()
                        .map(e -> (e.column() == null ? "" : e.column().header() + ": ") + e.message())
                        .collect(Collectors.joining("\n")));
                summary.setCellStyle(wrap);

                for (RowError error : entry.getValue()) {
                    Integer columnIndex = error.column() == null ? null : indexOf.get(error.column());
                    if (columnIndex == null) {
                        continue; // a row-level or sheet-level message lives only in the Import errors column
                    }
                    Cell cell = row.getCell(columnIndex);
                    if (cell == null) {
                        cell = row.createCell(columnIndex);
                    }
                    CellStyle original = cell.getCellStyle();
                    cell.setCellStyle(redByOriginal.computeIfAbsent(original.getIndex(), key -> {
                        CellStyle red = workbook.createCellStyle();
                        red.cloneStyleFrom(original);
                        red.setFillForegroundColor(IndexedColors.ROSE.getIndex());
                        red.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        return red;
                    }));
                    if (cell.getCellComment() != null) {
                        cell.removeCellComment(); // the upload already had a comment here; only one is allowed per cell
                    }
                    ClientAnchor anchor = factory.createClientAnchor();
                    anchor.setCol1(columnIndex); // in XSSF the anchor's first corner is also the cell that owns the comment
                    anchor.setCol2(columnIndex + 3);
                    anchor.setRow1(rowIndex);
                    anchor.setRow2(rowIndex + 3);
                    Comment comment = drawing.createCellComment(anchor);
                    comment.setString(factory.createRichTextString(error.message()));
                    comment.setAuthor("Bookshelf import");
                    cell.setCellComment(comment);
                }
            }
            sheet.setColumnWidth(errorColumn, 60 * 256);
            workbook.write(out);
        }
    }
}

$ curl -s -D - -F "file=@books-import.xlsx" "http://localhost:8765/api/books/import?mode=PARTIAL&format=xlsx" -o books-import-errors.xlsx
HTTP/1.1 200
Content-Disposition: attachment; filename="books-import-errors.xlsx"
X-Import-Status: PARTIALLY_IMPORTED
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Length: 6314

<comment ref="A4" authorId="1"><text><t>must be exactly 13 digits</t></text></comment>
<comment ref="B4" authorId="1"><text><t>must not be blank</t></text></comment>
<comment ref="D4" authorId="1"><text><t>must be a date in the past or in the present</t></text></comment>