Package io.github.dornol.excelkit.excel

Class ExcelTemplateWriter

java.lang.Object
io.github.dornol.excelkit.excel.ExcelTemplateWriter
All Implemented Interfaces:
AutoCloseable
public class ExcelTemplateWriter extends Object implements AutoCloseable
Fills data into an existing Excel template (.xlsx) while preserving formatting, images, charts, and merged regions.

Supports two write modes that can be combined:

  • Cell-level writes — fill individual cells by reference (e.g., "B3")
  • List streaming — write tabular data starting from a given row

Important: All writes must proceed in top-to-bottom row order. SXSSFWorkbook flushes rows from memory, so previously written rows cannot be revisited. 


 try (ExcelTemplateWriter writer = new ExcelTemplateWriter(templateStream)) {
     writer.cell("B3", clientName)
           .cell("B4", LocalDate.now());

     writer.<Item>list(5)
           .column("A", Item::getName)
           .column("B", Item::getQty)
           .column("C", Item::getAmount)
           .afterData(ctx -> {
               ctx.getSheet().createRow(ctx.getCurrentRow())
                  .createCell(0).setCellValue("Total: " + total);
               return ctx.getCurrentRow() + 1;
           })
           .write(itemStream);

     writer.finish().write(outputStream);
 }
Since:
0.8.2

  • Constructor Details

    • ExcelTemplateWriter

      public ExcelTemplateWriter(InputStream templateStream) throws IOException
      Opens a template from the given input stream.
      Parameters:
      templateStream - the .xlsx template file stream
      Throws:
      IOException - if the template cannot be read

    • ExcelTemplateWriter

      public ExcelTemplateWriter(InputStream templateStream, int rowAccessWindowSize) throws IOException
      Opens a template from the given input stream with a custom row access window size.
      Parameters:
      templateStream - the .xlsx template file stream
      rowAccessWindowSize - number of rows kept in memory by SXSSFWorkbook
      Throws:
      IOException - if the template cannot be read

  • Method Details

    • sheet

      public ExcelTemplateWriter sheet(int index)
      Selects the active sheet by index for subsequent cell(java.lang.String, java.lang.Object) calls.
      Parameters:
      index - 0-based sheet index
      Returns:
      this writer for chaining

    • sheet

      public ExcelTemplateWriter sheet(String name)
      Selects the active sheet by name for subsequent cell(java.lang.String, java.lang.Object) calls.
      Parameters:
      name - the sheet name
      Returns:
      this writer for chaining

    • cell

      public ExcelTemplateWriter cell(String cellRef, @Nullable Object value)
      Writes a value to the specified cell on the active sheet.

      The value type is auto-detected: String, Number, Boolean, LocalDate, LocalDateTime, LocalTime, or null (blank cell).

      Parameters:
      cellRef - Excel-notation cell reference (e.g., "B3", "AA10")
      value - the value to write
      Returns:
      this writer for chaining

    • cell

      public ExcelTemplateWriter cell(String cellRef, @Nullable Object value, org.apache.poi.ss.usermodel.CellStyle style)
      Writes a value with a custom style to the specified cell.
      Parameters:
      cellRef - Excel-notation cell reference
      value - the value to write
      style - the cell style to apply
      Returns:
      this writer for chaining

    • cell

      public ExcelTemplateWriter cell(int row, int col, @Nullable Object value)
      Writes a value to the specified cell by row and column index.
      Parameters:
      row - 0-based row index
      col - 0-based column index
      value - the value to write
      Returns:
      this writer for chaining

    • cell

      public ExcelTemplateWriter cell(int row, int col, @Nullable Object value, @Nullable org.apache.poi.ss.usermodel.CellStyle style)
      Writes a value with a custom style to the specified cell by row and column index.
      Parameters:
      row - 0-based row index
      col - 0-based column index
      value - the value to write
      style - the cell style to apply (null to keep existing)
      Returns:
      this writer for chaining

    • list

      public <T> TemplateListWriter<T> list(int startRow)
      Creates a list writer for streaming tabular data starting at the given row on the active sheet.

      The template's existing column headers (if any) are preserved. Data rows are written starting from startRow.

      Type Parameters:
      T - the row data type
      Parameters:
      startRow - 0-based row index where data writing begins
      Returns:
      a TemplateListWriter for configuring columns and writing data

    • list

      public <T> TemplateListWriter<T> list(int sheetIndex, int startRow)
      Creates a list writer for streaming tabular data into a specific sheet.
      Type Parameters:
      T - the row data type
      Parameters:
      sheetIndex - 0-based sheet index
      startRow - 0-based row index where data writing begins
      Returns:
      a TemplateListWriter for configuring columns and writing data

    • finish

      public ExcelHandler finish()
      Finishes the template and returns an ExcelHandler for output.

      After calling this method, no more writes are allowed.

      Returns:
      ExcelHandler wrapping the workbook

    • close

      public void close()
      Closes the underlying workbook if it has not been finished.
      Specified by:
      close in interface AutoCloseable