Angular and Spring Boot Excel Export / Download

Hi Folks,

After a long I got some time to write new article. This article shows how to download or export data to excel file from Angular. The project stack is Angular 7 (PrimeNG) and Spring Boot. Let’s start with UI first –

In .html file declared a button to download the excel file

<div class="col-md-2">
     <p-button label="Extract & Download Excel" (onClick)="exportCampaign($event)" [disabled]="isShow" id="btnXlsRpt"></p-button>
</div>

After clicking on button the function exportCampaign from .js got called as below

exportCampaign($event) {
    $event.stopPropagation();
    $event.preventDefault();
    this.blockedDocument = true;
    this.d = new Date();
    this.year = this.d.getFullYear();
    this.month = this.d.getMonth() + 1;
    this.day = this.d.getDate();
    this.d.getUTCFullYear();
    
   var result= this.mainService.exportCampaignData(this.obj.cmpNameToSearch);
   let fileName="Campaign-"+this.obj.cmpNameToSearch+"_"+this.day + "_" + this.month + "_" + this.year +".xlsx";
   result.subscribe(
    (success: any) => {
      var blob = new Blob([success._body], { type: 'application/vnd.ms-excel' });

      if (window.navigator && window.navigator.msSaveOrOpenBlob) {
        window.navigator.msSaveOrOpenBlob(blob, fileName);
        this.blockedDocument = false;
      } else {
        var a = document.createElement('a');
        a.href = URL.createObjectURL(blob);
        a.download = fileName;
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
        this.blockedDocument = false;
      }
    },
    err => {
      alert("Error while downloading. File Not Found on the Server");
      this.blockedDocument = false;
    }
  );
}
  1. The call goes to Angular service method
    exportCampaignData(cName : String) {
    return this.http.post(this.apiurl+’/api/cvs/exportExcel’, cName, this.options);
    }

Before we jump to Spring side backend logic, we need to configure following pre-requisites
— include the following Apache POI dependency in pom.xml

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

  1. Then the call goes back to the Spring Controller class, notice the Return type of method is ResponseEntity
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
@ResponseBody
public ResponseEntity<?> exportExcel(HttpServletRequest request, HttpServletResponse response, @RequestBody String campaignName) throws Exception {
		
	XSSFWorkbook workbook = null;
	byte[] contentReturn=null;
	/* Here I got the object structure (pulling it from DAO layer) that I want to be export as part of Excel. */
	vehicleLastSeenByCampaignReport.setCvsSummary(cvsSummary);
	try{
		/* Logic to Export Excel */
		LocalDateTime localDate = LocalDateTime.now();
		String fileName = "CampaignID-" + campaignName + "-" + localDate.toString() + ".xlsx";
		response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
				
		OutputStream out;
workbook = (XSSFWorkbook) exportToFileService.exportToExcel(vehicleLastSeenByCampaignReport);
		out = response.getOutputStream();
		workbook.write(out);
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);
		contentReturn = baos.toByteArray();
		/* Export Excel logic end */
			
		} catch (Exception ecx) {
			VehicleLastSeenByCampaignReport vehicleCampaignReport = new VehicleLastSeenByCampaignReport();
			vehicleCampaignReport.setErrorMessage("Campaign Not Found");
			return new ResponseEntity<byte[]>(null, null, HttpStatus.BAD_REQUEST);
		}finally {
			if (null != workbook) {
				try {
					workbook.close();
				} catch (IOException eio) {
					logger.error("Error Occurred while exporting to XLS ", eio);
				}
			}
		}
		
		HttpHeaders headers = new HttpHeaders();
		headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
		return new ResponseEntity<byte[]>(contentReturn, headers, HttpStatus.OK);
	}

Call goes the service method i.e. export to excel

 public Object exportToExcel(VehicleLastSeenByCampaignReport vehicleLastSeenByCampaignReport) {
		String[] columns = setUpExcelColumns(CVSConstants.EXPORT_DATA_CMP);
		Workbook workbook = CVSUtils.writeExcelFile(vehicleLastSeenByCampaignReport, columns, summaryArray);
		return workbook;
}
  1. In following code I created a 2 sheets inside single excel file.
public static Workbook writeExcelFile(VehicleLastSeenByCampaignReport vehicleLastSeenByCampaignReport, String[] columns, String[] summaryColumns) {

		Workbook workbook = new XSSFWorkbook();
		int rowNum = 0;
		// Create a Font for styling header cells
		Font headerFont = setHeaderFont(workbook);

		// Create a CellStyle with the font
		CellStyle headerCellStyle = setHeaderCellStyle(workbook, headerFont);
		
		// Create a CellStyle for CampaignSummary mian object with Grey Color
		CellStyle styleGrey = workbook.createCellStyle();
		styleGrey.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		styleGrey.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
		
		Sheet cmpSummarySheet = workbook.createSheet(CVSConstants.CMP_SUMMARY_SHEET);
		Sheet cmpDetailsSheet = workbook.createSheet(CVSConstants.CMP_DETAILS_SHEET);
		
		Row headerRowcmpSummary = cmpSummarySheet.createRow(0);
		Row headerRowCmpDetails = cmpDetailsSheet.createRow(0);
		
		applyHeaderCellStyle(Arrays.copyOfRange(summaryColumns, 0, (summaryColumns.length)), headerCellStyle, headerRowcmpSummary);
		applyHeaderCellStyle(Arrays.copyOfRange(columns, 0, (columns.length) ), headerCellStyle, headerRowCmpDetails);
		
		//Create the 'cmp summary sheet'
		defineCmpSummary(vehicleLastSeenByCampaignReport, rowNum, cmpSummarySheet, summaryColumns);
		
		//Create the 'cmp details sheet'
		defineCmpDetails(vehicleLastSeenByCampaignReport, rowNum, cmpDetailsSheet, columns, styleGrey);
		
		return workbook;
		
	}
	
	private static void defineCmpSummary(VehicleLastSeenByCampaignReport vehicleLastSeenByCampaignReport, int rowNum, Sheet cmpSummarySheet, String[] summaryCols) {
		Row vehDetailsRow = cmpSummarySheet.createRow(++rowNum);
		CVSSummary cvsSummary = vehicleLastSeenByCampaignReport.getCvsSummary();
		int columnNum = 0;
		
		vehDetailsRow.createCell(columnNum).setCellValue(cvsSummary.getDomain());
		vehDetailsRow.createCell(++columnNum).setCellValue(cvsSummary.getSearchQuery());
		vehDetailsRow.createCell(++columnNum).setCellValue(cvsSummary.getTotalCampaigns());
		vehDetailsRow.createCell(++columnNum).setCellValue(cvsSummary.getCampaignsValidated());		
		vehDetailsRow.createCell(++columnNum).setCellValue(cvsSummary.getCampaignsSkipped());
		
		for (int i = 0; i < summaryCols.length; i++) {
			cmpSummarySheet.autoSizeColumn(i);
		}
	}

Few important api used in code are

workbook => this is an important object that populates content of Excel file and return it to subsequent callers


autoSizeColumn => used to autoSize Columns width based on content of columns

That’s All Folks. Straight forward self explementary code. Do let me know in comments in case of any doubts.

9 thoughts on “Angular and Spring Boot Excel Export / Download

  1. return this.http.post(this.apiurl+’/api/cvs/exportExcel’, cName, this.options);
    What is the value for this.options?
    we are getting exception due to this.

    Like

  2. Hi, backend is able to send the byte[] however angular front end is not able to read it. It throws an error at http.js line 1929 where it says, // Attempt the parse. If it fails, a parse error should be delivered to the user.
    body = body !== ” ? JSON.parse(body) : null;. Here, body is a random byte array character where the error is Unexpected token P in JSON at position 0.

    Like

  3. URL.createObjectURL() this method is deprecated and also removed from some browsers. Is there any alternative for this??

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s