How to save/export the spreadsheet, including formatting and styling, to clients (and not server).

[Mario]

I have an Angular application that uses Syncfusion's Angular Spreadsheet to display data retrieved from a database through REST services.

The backend app for which the Angular app connects to is running on a Tomcat instance at port 8080.

I want the users of the application to be able to save the displayed spreadsheet into an Excel file (xlsx or xls) in their local machines (and not in the server) by either:

1.   Using the "File/Save as" menu or by
2.   Clicking a button that will invoke the method saveToExcel()

1. How should I configure/specify the saveUrl when I am running the app on Angular's Development Server which is listening on port 4200?

In development mode, I run the application using a proxy-configuration since the Angular app connects to a backend app which is running on a Tomcat instance at port 8080.

This is how I start the server in development mode:

    ng serve --proxy-config=proxy.conf.json
    
    // proxy.config.json
    {
        "/api": {
            "target": "http://localhost:8080/myapp",
            "secure": false,
            "logLevel": "debug",
            "pathRewrite": {
                "^/api": ""
            }
        }
    }

2. How should I configure/specify the saveUrl when I deploy the app on a Tomcat instance running on port 8080.

    This is the structure of the deployment folder

    TOMCAT_INSTANCE/webapps/myapp ... compiled Angular files here

    TOMCAT_INSTANCE/webapps/myapp/WEB-INF/ ... compiled Java/SpringFramework files and folders here

3. What are the requirements so that the spreadsheet can be exported to Excel including the format and styles?

Below is my code.

@Component({

  selector: 'app-back-order-eta1010-list-panel',
  templateUrl: './back-order-eta1010-list-panel.component.html',
  styleUrls: ['./back-order-eta1010-list-panel.component.scss']
})
export class BackOrderEta1010ListPanelComponent implements OnInit, AfterViewInit {
  @ViewChild('spreadsheet') public spreadsheet!: SpreadsheetComponent

  public dealerCode: string = '';
  public pageNo = environment.PAGE;
  public start = environment.START;
  public limit = 100;

  orders: Order[] = [];
  public spreadsheetData: BackOrderEta[] = [];

  headerRowsCount: number = 2;
  startCellIndex:  number = 3;
  lastRowNo: number = 0;
  startDataCell: string = "A3"; //`A${this.startCellIndex}`;

  public colHeader: CellStyleModel = { fontSize:'14', fontWeight: 'bold', fontFamily: 'Arial', textAlign: 'center', verticalAlign: 'middle' };
  public subHeader: CellStyleModel = { fontSize:'10', fontWeight: 'bold', fontFamily: 'Arial', textAlign: 'center', verticalAlign: 'middle' };

  public dspTxtLA: CellStyleModel = { fontSize:'12', fontWeight: 'normal', fontFamily: 'Arial', textAlign: 'left',   verticalAlign: 'middle', textIndent: '4px' };
  public dspTxtRA: CellStyleModel = { fontSize:'12', fontWeight: 'normal', fontFamily: 'Arial', textAlign: 'right',  verticalAlign: 'middle', textIndent: '4px' };

  public rangeAJ = 'A3:'  // Dealer - Item Description
  public rangeKM = 'K3:'  // Qty
  public rangeNO = 'N3:'
  public rangeRO = 'A3:';

  // Number format for amounts
  numberFormat = '##,###,##0.00';

  freezePane: number = 2;

  // property names of spreadsheet data
  public propNames: string[] = [];
  public modifiedRowIndices: Set<number> = new Set<number>();

  constructor(private service: OrderService, private authService: AuthService) {}

  ngOnInit() {
    this.dealerCode = this.authService.getLoginUser().dealerCode;
    let page: number = 1;
    let start: number = 1;
    let limit: number = 50;

    let orderFrm = new Order();
    orderFrm.status = OrderStatus.BACK_ORDER;

    this.service.searchPaosBackOrders(orderFrm, this.dealerCode, this.pageNo, this.start, this.limit)
                .subscribe({
                  next: (data: BackOrderEta[]) => {
                    this.spreadsheetData = data;
                    this.lastRowNo = data.length + this.headerRowsCount;    // add column header rows (2)
                  },
                  error: (err) => {
                    console.error(err);
                  }
                });
  }

  onCreated() {
    // console.log(`onCreated1:lastRowNo:  ${this.lastRowNo}`);
    this.formatSpreadsheet();
  }

  ngAfterViewInit(): void {
    // console.log(`AfterViewInit1:lastRowNo...${this.lastRowNo}`);
    setTimeout(() => {
      this.propNames = Object.keys(this.spreadsheetData[0]);
      this.formatSpreadsheet();
    }, 3000);
  }

  searchBackOrders(orderFrm: Order, dealerCode: string, padeNo: number, start: number, limit: number) {

    this.service.searchPaosBackOrders(orderFrm, this.dealerCode, this.pageNo, this.start, this.limit)
                .subscribe({
                  next: (data: BackOrderEta[]) => {
                    this.spreadsheetData = data;
                    this.lastRowNo = data.length + this.headerRowsCount; // add column header rows (2)
                    setTimeout(() => {
                      this.formatSpreadsheet();
                    }, 1000);
                  },
                  error: (err) => {
                    console.error(err);
                  }
                });
  }

  private formatSpreadsheet() {
    if (this.spreadsheet && this.spreadsheet.sheets && this.spreadsheet.sheets[0]) {
      this.rangeAJ = `A${this.startCellIndex}:J${this.lastRowNo}`;
      this.spreadsheet.cellFormat(this.dspTxtLA, this.rangeAJ);

      this.rangeNO = `N${this.startCellIndex}:O${this.lastRowNo}`;
      this.spreadsheet.cellFormat(this.dspTxtLA, this.rangeNO);

      this.rangeKM = `K${this.startCellIndex}:M${this.lastRowNo}`;
      this.spreadsheet.cellFormat(this.dspTxtRA, this.rangeKM);
      this.spreadsheet.numberFormat('#,##0', this.rangeKM);

      // protect order and invoice info columns
      this.rangeRO = `A${this.startCellIndex}:M${this.lastRowNo}`;
      this.spreadsheet.setRangeReadOnly(true, this.rangeRO, this.spreadsheet.activeSheetIndex);
    }
  }

  onBeforeSave(e: BeforeSaveEventArgs) {
    e.isFullPost = false;
    e.needBlobData = false;
    console.log("onBeforeSave", e);
  }

  onCellHover(e: any) {
    // console.log("CellHover: ", e);
  }

  onCellClick(e: any) {
    // console.log("CellClick: ", e);
  }

  onCellEdit(e: any) {
    // console.log("CellEdit: ", e);
  }

  onCellSave(e: any): void {
    console.log("CellSave: ", e);
    const rangeIndexes = getRangeIndexes(e.address);
    const rowIndex = rangeIndexes[0];  // data rows starts after headers
    const colIndex = rangeIndexes[1];

    const dataRowNo = rowIndex  - this.headerRowsCount;
    const propName = this.propNames[colIndex];

    if(this.spreadsheetData[rowIndex] && propName) {
      if(e.value) {
        if(e.value !== e.oldValue) {
          this.modifiedRowIndices.add(dataRowNo);
          this.spreadsheetData[dataRowNo][propName] = e.value;
          if(propName === 'eta') {
            // update spreadsheet data model
            this.spreadsheetData[dataRowNo].oldEta = e.oldValue;
            // update spreadsheet state (oldEta)
            this.spreadsheet.updateCell({value: e.oldValue}, `M${rowIndex+1}`);
          }
        }
      }
    }
  }

  private getModifiedRows(): BackOrderEta[] {
    const modifiedEtas: BackOrderEta[] = [];
    this.modifiedRowIndices.forEach(idx => {
      if(this.spreadsheetData[idx]) {
        modifiedEtas.push(this.spreadsheetData[idx]);
      }
    });
    return modifiedEtas;
  }

  public saveEtas() {
    const updatedOrderItems: OrderItem[] = [];

    this.getModifiedRows().forEach(boEta => {
      let item = new OrderItem();
      item.id         = boEta.id;
      item.orderNo    = boEta.orderNo;
      item.dealerCode = boEta.dealerCode;
      item.itemNo     = boEta.itemNo;
      item.partNo     = boEta.partNo;
      item.model      = boEta.model;
      item.qty        = boEta.qty;
      item.fillQty    = boEta.fillQty;
      item.oldEta     = boEta.oldEta;
      item.eta        = boEta.eta;
      updatedOrderItems.push(item);

      console.log(
        `Id: ${boEta.id} Dealer: ${boEta.dealerCode}-${boEta.dealerName},
         order: ${boEta.orderDate}  ${boEta.orderNo},
         item# ${boEta.itemNo} ${boEta.partNo}: bo: ${boEta.boQty},
         eta: ${boEta.oldEta} / ${boEta.eta}`
      );
    });

    this.dealerCode = this.authService.getLoginUser().dealerCode;
    this.service.updateEtas(updatedOrderItems, this.dealerCode)
                .subscribe((response: { success: boolean, data: any, recordCount: number }) => {
                  // console.log(`Etas updated successfully? ${response.success}`);
                  if(response.success) {

                  }
                });
  }

  saveToExcel() {

  }
} // end


<ejs-spreadsheet #spreadsheet
    allowSave="true"
    saveUrl="http://localhost:8380/myapp"
    [height]="1000"
    [showFormulaBar]="false"
    (created)=onCreated()
    (beforeSave)="onBeforeSave($event)"
    (cellHover)=onCellHover($event)
    (cellClick)=onCellClick($event)
    (cellEdit)=onCellEdit($event)
    (cellSave)=onCellSave($event)>
    <e-sheets>
      <e-sheet name="BackOrders" [frozenRows]="freezePane" [frozenColumns]="freezePane" selectedRange="C1" [rowCount]="100">
        <e-ranges>
          <e-range [dataSource]="spreadsheetData" startCell="A3" [showFieldAsHeader]="false"></e-range>
        </e-ranges>

        <e-rows>
          <!-- Header Rows -->
          <e-row>
            <e-cells>
              <e-cell [index]="0"  [value]="'Dealers'"            [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="1"  [value]="'Customer Account'"  [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="2"  [value]="'PO Date'"           [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="3"  [value]="'SO No'"             [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="4"  [value]="'Tran Type'"         [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="5"  [value]="'PO No'"             [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="5"  [value]="'Status'"            [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="6"  [value]="'Model'"             [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="7"  [value]="'Part Number'"       [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="8"  [value]="'Item Description'"  [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="9"  [value]="'QUANTITY'"          [colSpan]="3" [style]="colHeader"></e-cell>
              <e-cell [index]="10" [value]="'Old ETA'"           [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="11" [value]="'New ETA'"           [rowSpan]="2" [style]="colHeader"></e-cell>
              <e-cell [index]="12" [value]="''"                                [style]="colHeader"></e-cell>
              <e-cell [index]="13" [value]="''"                                [style]="colHeader"></e-cell>
            </e-cells>
          </e-row>

          <!-- SubHeader Rows -->
          <e-row>
            <e-cells>
              <e-cell [index]="2"  [value]="'Ordered'"                        [style]="subHeader"></e-cell>
              <e-cell [index]="3"  [value]="'Filled'"                         [style]="subHeader"></e-cell>
              <e-cell [index]="4"  [value]="'B/O'"                            [style]="subHeader"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
        <e-columns>
          <e-column [width]=60></e-column>      <!-- dealer code -->
          <e-column [width]=180></e-column>     <!-- dealer name -->
          <e-column [width]=80></e-column>      <!-- order date -->
          <e-column [width]=100></e-column>     <!-- so no. -->
          <e-column [width]=80></e-column>      <!-- order type -->
          <e-column [width]=100></e-column>     <!-- order no. -->
          <e-column [width]=140></e-column>     <!-- status -->
          <e-column [width]=180></e-column>     <!-- model -->
          <e-column [width]=110></e-column>     <!-- partNo -->
          <e-column [width]=280></e-column>     <!-- item desc -->
          <e-column [width]=80></e-column>      <!-- ordered qty -->
          <e-column [width]=80></e-column>      <!-- order filled -->
          <e-column [width]=80></e-column>      <!-- backorder-->
          <e-column [width]=260></e-column>     <!-- eta -->
          <e-column [width]=260></e-column>     <!-- old eta -->
          <e-column [width]=80 [hidden]="true"></e-column>      <!-- id -->
          <e-column [width]=80 [hidden]="true"></e-column>      <!-- itemNo -->
        </e-columns>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>

Attachment: ordereta_2047be1b.png

[Mario]

As currently coded, when Save as is selected from the File menu, the attached (SaveAs.png) popup screen is displayed.

After clicking the SAVE button, the attached Ok (Ok.png) popup window is momentarily displayed but closes immediately without any Excel file being saved.

Attachment: Ok_6cc2e5af.png

[Dinakar Manickam]

Hi Mario,

Thank you for sharing the screenshot and code snippets. Based on the information provided, we understand that you are attempting to implement the Save service using Java. If so, we regret to let you know that currently, the Spreadsheet component does not support implementing the Open and Save services with Java.

However, you can save the spreadsheet as an Excel file either through the File > Save As option in the File menu or programmatically using the component's save method.

To assist you further, we’ve prepared a sample that demonstrates how to trigger the save functionality using a button click. Please refer to the code snippet, sample link, and video demonstration provided below.

Code Snippet:

[app.component.html]:

 

<button class="e-btn" id="save-btn" (click)="onSave()">Save</button>

    <ejs-spreadsheet #default [openUrl]="openUrl" [saveUrl]="saveUrl" (created)="created()">

[app.component.ts]:  

 

export class AppComponent {

    constructor() {

        

    }

    @ViewChild('default')

    public spreadsheetObj: SpreadsheetComponent;

    public openUrl = 'https://services.syncfusion.com/angular/production/api/spreadsheet/open';

    public saveUrl = 'https://services.syncfusion.com/angular/production/api/spreadsheet/save';

    

    onSave() {

        this.spreadsheetObj.save({

            url: this.saveUrl,

            fileName: 'Worksheet',

            saveType: 'Xlsx',

          });

    }

}

 

Sample Link: Gwm9hegl (forked) - StackBlitz

Video Demonstration: Please refer the below attachment.

For more information regarding Save action you can refer the UG link mentioned below.

UG Link: Open save in Angular Spreadsheet component | Syncfusion

Additionally we would like to inform you that we have implemented the Open and Save (server-side) functionality in ASP.NET Core and ASP.NET MVC using the Syncfusion XLSIO library. This library handles file operations on the server. Thus, when you open a file, the XLSIO library reads and converts it to a spreadsheet-compatible workbook in JSON format. Similarly, when you save the spreadsheet, we process the workbook JSON into an Excel model using the XLSIO library. Rest assured, your data is not stored on our server during these actions, ensuring it remains safe and secure.

For local service you need ASP.NET Core project as a backend for Open/Save functionality in your client application.

Local service available in below GitHub location also:

https://github.com/SyncfusionExamples/EJ2-Spreadsheet-WebServices/

Steps to Launch the Local Service:

1. Clone the local service sample from the above repository (WebAPI).
2. Open the WebAPI.sln file in the WebAPI folder.
3. Right click on the Dependencies folder inside WebAPI.
4. Then, click the Manage Nuget Packages.
5. In Browse, search Syncfusion.EJ2.Spreadsheet.AspNet.Core package and install the latest package.
6. If already, the package exists, remove the package, and re-install it to get the latest package with its necessary dependent packages will get downloaded.
7. Now, build the solution and run in a local host.

For your convenience, we have attached the open and save code snippets in local service below.

 
Code snippet:

//Open method

[HttpPost]

[Route("Open")]

public IActionResult Open([FromForm]IFormCollection openRequest)

{

    OpenRequest open = new OpenRequest();

    open.File = openRequest.Files[0];

return Content(Workbook.Open(open));

}

//Save method

[HttpPost]

[Route("Save")]

public IActionResult Save([FromForm]SaveSettings saveSettings)

{

return Workbook.Save(saveSettings);

}

Updating Open and Save URLs in the Client-Side Sample:

After, launching the local service, you need to update the open and save URL in the Client-Side sample like in the below code snippet,

openUrl: 'https://localhost:{port number}/api/spreadsheet/open'

saveUrl: 'https://localhost:{port number}/api/spreadsheet/save'

 

Example:

openUrl: 'https://localhost:44354/api/spreadsheet/open'

saveUrl: 'https://localhost:44354/api/spreadsheet/save'

Note: Ensure the local service is running before executing the client-side sample.

Registering the License Key:

To activate the Syncfusion components in the shared WebAPI application, register your license key in Program.cs:

[Program.cs]

Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("Your Syncfusion license key");

Create ASP.NET Core project and generate the license based on the below KB link.

https://www.syncfusion.com/kb/8976/how-to-generate-license-key-for-essential-studio-products

And include the licensed key in your project startup file as like in the below documentation link.

https://help.syncfusion.com/common/essential-studio/licensing/license-key

Running the Spreadsheet Service as a Docker Image

You can also host and run our Spreadsheet-oriented service as a Docker image. Utilizing a Docker image necessitates only a simple and basic Docker environment with minimal commands to effortlessly host and run our service.

You can utilize the below service and use it for Spreadsheet Open and Save services, by pulling the spreadsheet docker image and following the steps from the URL mentioned below.

Spreadsheet docker image: https://hub.docker.com/r/syncfusion/spreadsheet-server

For more information regarding the docker deployment, please refer the below documentation,

https://ej2.syncfusion.com/react/documentation/spreadsheet/docker-deployment

However, our XlsIO has already logged a feature request to implement a Java server library, and it is planned to be included in one of our upcoming releases. Once the Java server library is implemented by the XlsIO, we will be able to support the Open and Save services in the Spreadsheet component.

In the meantime, you can track the status of this feature using the following link from our feedback portal:
Feedback Portal Link: https://www.syncfusion.com/feedback/13225/xlsio-java-library

At the beginning of each release cycle, we review all open feature requests and prioritize implementation based on several factors, including product roadmap, technical feasibility, and customer demand. Once there is a confirmed timeline for this feature, the feedback item will be updated to Scheduled status with the estimated release details.

We appreciate your patience and understanding while we work toward supporting this functionality.

Best Regards,
Dinakar M

Attachment: VideoF196787_3d77124c.zip