tinmegali
6/30/2018 - 4:28 PM

Downloading Excel files with Spring Boot and Angular 5

angular-instructions.md
content_copyfile_download
  • Rendered
  • Source

Angular 5

Make sure that FileSaver.js is istalled

Service

    getSheetParticipantes() {
        const headers = new HttpHeaders();
        headers.append('Accept', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        return this.http.get(
            `${this.downloadUrl}participantes`,
            { headers: headers, responseType: 'blob' as 'json'})
            .toPromise();
    }

Component

// import FileSaver
import { saveAs } from 'file-saver/FileSaver';

    public downloadSheet() {
        this.dashService.getSheetParticipantes()
            .then((response) => this.saveToFileSystem(response));
    }

    private saveToFileSystem(response) {
        const blob = new Blob([response],
            { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
        saveAs(blob, 'linha_do_tempo-participantes.xls');
    }

Component HTML

  <a rel="nofollow" (click)="downloadSheet()">
        Download File
  </a>
ExcelBuilder.java
content_copyfile_download
package br.com.pixinside.arcelor.linhadotempo.futuro.util;

import br.com.pixinside.arcelor.linhadotempo.futuro.dto.SimpleParticipanteDTO;
import br.com.pixinside.arcelor.linhadotempo.futuro.web.rest.DashboardResource;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.Map;

/**
 * This class builds an Excel spreadsheet document using Apache POI library.
 */
@Component
public class ExcelBuilder {

    private final Logger log = LoggerFactory.getLogger(ExcelBuilder.class);

    private final String PATH = "files/";
    private final String FILE_NAME = "linha_do_tempo-relatorio";


    public File sheetParticipantes(Map<String, Object> model) throws IOException, InvalidFormatException {

        @SuppressWarnings("unchecked")
        List<SimpleParticipanteDTO> participantes =
            (List<SimpleParticipanteDTO>) model.get("participantes");

        // create excel xls sheet
        Workbook workbook = new HSSFWorkbook();
        SetSheetParticipantes(workbook, participantes);

        //lets write the excel data to file now
        checkAndCreateDir();
        String fileName = PATH + FILE_NAME + ".xls";
        FileOutputStream fos = new FileOutputStream(fileName);
        workbook.write(fos);
        fos.close();
        log.info("File {} write successfully", FILE_NAME);

        File file = new File(fileName);
        if (file.exists()) return file;
        else throw new IOException(String.format("Problems creating file %s", fileName));
    }

    private void checkAndCreateDir() throws IOException {
        File dir = new File(PATH);
        if (!dir.exists()) {
            Path path = Paths.get(PATH);
            Files.createDirectories(path);
        }
    }

    private void SetSheetParticipantes(Workbook workbook, List<SimpleParticipanteDTO> participantes) {
        // create excel xls sheet
        Sheet sheet = workbook.createSheet("Participantes");

        // create header row
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Nome");
        header.createCell(2).setCellValue("Email");
        header.createCell(3).setCellValue("Matricula");

        // Create data cells
        int rowCount = 1;
        for (SimpleParticipanteDTO participante : participantes){
            Row participanteRow = sheet.createRow(rowCount++);
            participanteRow.createCell(0).setCellValue(participante.getId());
            participanteRow.createCell(1).setCellValue(participante.getNome());
            participanteRow.createCell(2).setCellValue(participante.getEmail());
            participanteRow.createCell(3).setCellValue(participante.getMatricula());
        }
    }
}
spring-instruction.md
content_copyfile_download
  • Rendered
  • Source

Spring

Creating xls file

Use Apache Poi to create a xls as a file or stream. See and example in ExcelBuilder.java.

Serve file as HttpEntity<byte[]>

    CONTENT_XLS = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    
    // getting xls from ExcelBuilder
    public String urlSheetParticipantes() {
        try {
            List<SimpleParticipanteDTO> participantes = participanteRepository.findAllSimplified();
            Map<String, Object> data = new HashMap<>();
            data.put("participantes", participantes);
            File file = excelBuilder.sheetParticipantes( data );
            return file.getPath();
        } catch (IOException | InvalidFormatException e) {
            log.error("Error creating sheet 'Participantes'");
            e.printStackTrace();
            return null;
        }
    }
    
    // serving file
    @GetMapping(value = "/files/participantes")
    public HttpEntity<byte[]> getSheet()
    {
        try {
            String filePath = dashboardService.urlSheetParticipantes();
            File file = new File(filePath);

            byte[] documentContent = FileUtils.readFileToByteArray(file);
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.parseMediaType(CONTENT_XLS));
            headers.set(HttpHeaders.CONTENT_DISPOSITION,
                "inline; filename=\"linha_do_tempo-participantes.xls\"");
            headers.setContentLength(documentContent.length);
            return new ResponseEntity<>(documentContent, headers, HttpStatus.OK);
        } catch ( IOException e ) {
            e.printStackTrace();
            log.error("Error getting 'Participantes' sheet.");
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

Testing

    @Test
    public void testUrlSheetParticipante() throws Exception {
        repository.save(participante);
        String url = dashboardService.urlSheetParticipantes();
        Assert.assertNotNull(url);
        Assert.assertEquals(url, "files/linha_do_tempo-relatorio.xls");
        File file = new File(url);
        Assert.assertTrue(file.exists());
    }
instalation.md
content_copyfile_download
  • Rendered
  • Source

Instalation

Dependencies for Java

Add Apache Poi to build.gradle

    // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
    compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'

Angular 5

Install FileSaver.js

$ npm install --save file-saver