Exporting an Excel file from JSON with customized formatting and automatic cell adjustment using FileSaver.js

Question

Exporting an Excel file from JSON with customized formatting and automatic cell adjustment using FileSaver.js

I have successfully implemented functions to export JSON data to Excel using the code below:

  public exportAsExcelFile(json: any[], excelFileName: string) :Promise<Object> {

    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    return (this.saveAsExcelFile(excelBuffer, excelFileName));

  }

  private saveAsExcelFile(buffer: any, fileName: string):Promise<Object>  {
    const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});
    return await FileSaver.saveAs(data, fileName + '-' + new  Date().toString()+ EXCEL_EXTENSION);
 }

However, I am struggling with formatting the exported Excel file. Specifically, I want to include bold headers and ensure that the cells autofit.

angular typescript filesaver.js

Answer 1

Answer №1

If you're using the js-xlsx library, achieving certain features may require switching to the PRO version, which comes at a high cost.

The community version has its limitations. For enhanced performance, additional styling options, and dedicated support, we recommend upgrading to our PRO version.

Alternatively, you can utilize the ExcelJS library for similar functionalities without the need for expensive upgrades.

 const workbook = new ExcelJS.Workbook();
 workbook.creator = 'Your company name';
 workbook.created = new Date();

 const worksheet = workbook.addWorksheet('Sheet1');

To specify column widths:

 worksheet.columns = [
     { width: 15 },
     { width: 30 },
     { width: 10 }
 ];

You can make headers bold with the following code:

worksheet.getRow(1).font = { bold: true };

Customize cell styles like borders and fonts:

worksheet.getCell('A1').border = {
                    top: ...,
                    left: ...,
                    bottom: ...,
                    right: ...
                };
worksheet.getCell('A1').font = ...;

Lastly, export your file:

import {saveAs} from 'file-saver';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';

...
workbook.xlsx.writeBuffer()
             .then((file: any) => {
                const blob = new Blob([file], { type: EXCEL_TYPE });
                saveAs(blob, `fileName.xlsx`);
             });

For more styling options, refer to this link.

Answer 2

If you're using the js-xlsx library, achieving certain features may require switching to the PRO version, which comes at a high cost.

The community version has its limitations. For enhanced performance, additional styling options, and dedicated support, we recommend upgrading to our PRO version.

Alternatively, you can utilize the ExcelJS library for similar functionalities without the need for expensive upgrades.

 const workbook = new ExcelJS.Workbook();
 workbook.creator = 'Your company name';
 workbook.created = new Date();

 const worksheet = workbook.addWorksheet('Sheet1');

To specify column widths:

 worksheet.columns = [
     { width: 15 },
     { width: 30 },
     { width: 10 }
 ];

You can make headers bold with the following code:

worksheet.getRow(1).font = { bold: true };

Customize cell styles like borders and fonts:

worksheet.getCell('A1').border = {
                    top: ...,
                    left: ...,
                    bottom: ...,
                    right: ...
                };
worksheet.getCell('A1').font = ...;

Lastly, export your file:

import {saveAs} from 'file-saver';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';

...
workbook.xlsx.writeBuffer()
             .then((file: any) => {
                const blob = new Blob([file], { type: EXCEL_TYPE });
                saveAs(blob, `fileName.xlsx`);
             });

For more styling options, refer to this link.

Exporting an Excel file from JSON with customized formatting and automatic cell adjustment using FileSaver.js

Answer №1

Similar questions

Methods for maintaining accuracy when updating a user's follower count using Node.js with MongoDB

Troubleshooting ASP.NET Web API: Resolving Access-Control-Allow-Origin HTTP 500 Error During File Upload

Tips for incorporating the Sanitize library into Angular 6:

Incorporate JavaScript to trigger a click event on a mat-option when the Enter

When trying to gather multiple parameters using @Param in a NestJS controller, the retrieved values turn out

Can Enums be nested in Typescript?

Can one create attribute-selector components in a seamless and concealed manner?

Ensure that the function parameter only accepts keys that are present in the specified object

Building React Typescript Components with Froala Editor Plugins

Setting up a reverse proxy for Karma during Angular testing is essential for ensuring that

The pivotal Angular universal service

What is the best way to divide two ranges that are intersecting?

Can union types be utilized in destructuring with useSelector?

Setting the initial state for your ngrx store application is a crucial step in ensuring the

The Firebase.update operation in AngularFire2 encountered an error due to a failure in snapshot

How can we effectively divide NGXS state into manageable sections while still allowing them to interact seamlessly with one another?

Guide on assigning json array values to multiple accordion forms in Angular 6

Unable to invoke extension method on parent class in TypeScript

Tips for deploying an Angular 2+ application on the OpenShift platform

Navigate smoothly in Angular 4 with Spring Boot without the need for hash codes in the URL routing