How to Generate an Excel File and Download it in Vaadin Web App

To generate and download an Excel file in a Vaadin web application (e.g., Vaadin 23+ with Java backend), you can use Apache POI to create the Excel file and Vaadin’s StreamResource to serve it for download.

✅ Steps:

1. Add Apache POI to your pom.xml:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.4.1</version>
</dependency>

2. Create a Vaadin download button that generates the Excel file on demand:

import com.vaadin.flow.component.button.Button;
import com.vaadin.flow.component.html.Anchor;
import com.vaadin.flow.server.StreamResource;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.HSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;

public class ExcelDownloadView extends VerticalLayout {

    public ExcelDownloadView() {
        StreamResource resource = new StreamResource("example.xlsx", this::createExcelStream);

        Anchor downloadLink = new Anchor(resource, "");
        downloadLink.getElement().setAttribute("download", true);

        Button downloadButton = new Button("Download Excel");
        downloadLink.add(downloadButton);

        add(downloadLink);
    }

    private InputStream createExcelStream() {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Data");

            Row header = sheet.createRow(0);
            header.createCell(0).setCellValue("Name");
            header.createCell(1).setCellValue("Age");

            Row row = sheet.createRow(1);
            row.createCell(0).setCellValue("Alice");
            row.createCell(1).setCellValue(30);

            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            return new ByteArrayInputStream(out.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
            return new ByteArrayInputStream(new byte[0]); // return empty stream on failure
        }
    }
}

📝 Notes:

• The download starts when the button is clicked.
• You can dynamically change the file content by updating the StreamResource supplier.
• The file is generated in memory, so no temporary files are written to disk.
• You could use variety of APIs depending on the Excel File you want. Here is more info about it https://www.baeldung.com/java-apache-poi-workbook-evaluation

Processing the Downloaded File

If you are like me – toy are using Open Office.

In OpenOffice Calc, to sum a range of cells while ignoring blank cells, you can simply use the standard SUM function — because it automatically ignores blank cells.

Example:

If you want to sum the range A1:A10, use:

=SUM(A1:A10)

This will sum only the cells that contain numbers and ignore blank or empty cells automatically.

---

However, if you’re seeing “blank” cells that are not really empty (e.g. they contain formulas returning an empty string like ""), then SUM will still work because "" is not treated as a number and won’t be summed.

But if you want to sum only the numeric values and explicitly filter them, you could use an array formula:

Array formula (if needed):

=SUM(IF(ISNUMBER(A1:A10); A1:A10; 0))

• After typing this, press Ctrl+Shift+Enter (not just Enter) to enter it as an array formula.
• This checks if each cell is a number and sums only those.

How to tell open office to take the . as decimal separator

To set . (dot) as the decimal separator in OpenOffice Calc, follow these steps:

---

✅ Method 1: Change Locale Settings (Recommended)

1. Go to the menu: Tools → Options.
2. In the left panel, expand Language Settings → click Languages.
3. Under Formats, set Locale to one that uses . as a decimal separator (e.g., English (USA) or English (UK)).
4. Click OK.

This changes the default number format, including the decimal separator, to match that locale.

---

✅ Method 2: Override Decimal Separator

If you want to keep your current locale but still use .:

1. Again, go to Tools → Options.
2. Go to Language Settings → Languages.
3. Uncheck “Same as locale” under Decimal separator key.
4. In the same section, ensure the dot (.) key will now be interpreted as the decimal separator regardless of your locale.
5. Click OK.

---

These changes will affect how numbers are entered, displayed, and interpreted in formulas.

---

✅ Method 3: Actually Write a Double value from java and not a String

This way you let the POI API to handle the value type and you don’t need to do anythign manually.

I’ve used stuff from this article in my Time Tracker Web App: https://programtom.com/dev/product/time-tracker-vaadin-web-app/