I want to create a complex row and column form using spreadsheet. Can you help me with that?

[Vaishali Jain]

Hi,

I have attached a pdf and I want to create spreadsheet for page no. 20. in the attached pdf. Please help me with that. I need it in angular. 

Please also provide a format in that will be needed to create this type of structure.

Waiting for the response!

Thanks,

Vaishali

Attachment: fry_b824a753.rar

[Vaishali Jain]

Hi,

I have attached an excel and I want to create exact spreadsheet for this. Please help me with that. I need it in angular. 

Please also provide a format in that will be needed to create this type of structure.

Please ignore above post.

Waiting for the response!

Thanks,

Vaishali

Attachment: 9C_Mock_up_f89cdf7.zip

[Babu Periyasamy]

Hi Vaishali Jain,

We have checked your reported query with reference to the attached excel file. We would like to let you know that we have an import and export actions support in our Spreadsheet. Using that, you can import the excel files and edit the file and then export the edited file as an excel file.

And we have prepared the video demonstration in which we have imported your shared excel file in our Spreadsheet component and attached below along with the checked sample,

Sample link: https://stackblitz.com/edit/angular-hfehaw-hrf1ug?file=package.json,src%2Fapp.component.ts

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Import_excel_in_Spreadsheet-1091751656

Shapes in Spreadsheet:

In your shared excel file, we are able to see that you have used shapes. Currently, we don’t have support to insert shapes in our Spreadsheet component. Instead of that, you can utilize the template support in our Spreadsheet. Using the template property, you can render other components inside the cell. And while changing any value inside the template component, we can update the value in our respective cell using the updateCell method.

For your convenience, we have prepared the sample in which we have rendered the Autocomplete component inside the Spreadsheet cell using the ng-template tag and attached the sample below along with the code snippet and video demonstration for your reference,

Code snippet:

app.component.html

<ejs-spreadsheet #default (created)="created()">

        <e-sheets>

          <e-sheet name="Car Sales Report">

            <e-ranges>

              <e-range [dataSource]="data"></e-range>

              <e-range address="A2:A6">

                <ng-template #template>

                  <ejs-autocomplete

                    #remote

                    [dataSource]="autoCompleteData"

                    filterType="StartsWith"

                    [fields]="remoteFields"

                    [query]="query"

                    [placeholder]="remoteWaterMark"

                    sortOrder="Ascending"

                    (change)="onChange($event)"

                  ></ejs-autocomplete>

                </ng-template>

              </e-range>

            </e-ranges>

          </e-sheet>

        </e-sheets>

      </ejs-spreadsheet>

app.component.ts

public autoCompleteData: DataManager = new DataManager({

      url: 'https://services.syncfusion.com/angular/production/api/Employees',

      adaptor: new WebApiAdaptor(),

      crossDomain: true,

    });

 

    public query: Query = new Query()

      .select(['FirstName', 'EmployeeID'])

      .take(10)

      .requiresCount();

    // Map the remote data column to fields property.

    public remoteFields: Object = { value: 'FirstName' };

    // Set the placeholder to AutoComplete input.

    public remoteWaterMark: string = 'e.g. Andrew Fuller';

 

    created() {

     this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },

        'A1:F1');

      // Set the row height to the cells.

      this.spreadsheetObj.setRowsHeight(38, ['2:6']);

    }

 

 

    // Bind the change event.

    onChange(args: ChangeEventArgs): void {

      let value: string = args.value.toString();

      // Get the active sheet model.

      let activeSheet: SheetModel = this.spreadsheetObj.getActiveSheet();

      //Get the active cell.

      let activeCell: string = activeSheet.activeCell;

      // Update the cell model.

      this.spreadsheetObj.updateCell({value: value}, activeCell);

    }

Sample link: https://stackblitz.com/edit/angular-mfdw9t-srkcvq?file=src%2Fapp.component.ts

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Template_in_Spreadsheet250525159

For more information, please refer the below demo and KB links,

Demo link: https://ej2.syncfusion.com/angular/demos/#/material3/spreadsheet/cell-template

KB link: https://support.syncfusion.com/kb/article/14818/how-to-bind-remote-data-for-autocomplete-rendered-cells-in-an-angular-spreadsheet

But, while exporting the with the template component inside the cell, the component inside the cell will not get exported and only the value saved in that cell will be exported.

Overflow in Spreadsheet:

In your shared excel file, we are able to see there is text overflow in cells like B11, C12, etc., And Currently, we don’t have text overflow support in our Spreadsheet component. However, we have already confirmed this as an improvement and logged it as a feature. It will be available in any of our upcoming releases. You can communicate and track the status of the feature using the below link from our feedback portal.

Feedback link for tracking purposes: https://www.syncfusion.com/feedback/21592/add-allowoverflow-to-cell

Unfortunately, we are unable to implement this feature immediately. Since we have some more bug fixes and features that are prior than this feature is in queue scheduled for upcoming releases, we can’t commit this feature at present. We usually have an interval of at least three months between the releases.

At the planning stage for every release cycle, we review all the open features once again and finalize features for implementation based on specific parameters including product vision, technological feasibility, and customer interest. Once we have anything definite to share about these features implementation, we will move the feedback to scheduled status with the tentative release timeline. We appreciate your patience until then.

For more information regarding the Spreadsheet properties and method, please refer the below links,

Demo link: https://ej2.syncfusion.com/angular/demos/#/material3/spreadsheet/default

Documentation link: https://ej2.syncfusion.com/angular/documentation/spreadsheet/overview

API reference: https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/

Kindly, check the above information in your end. And if it does not meet your requirement or if we misunderstood your requirement, please share the detailed description of your requirement along with the screenshot or video demonstration. Based on that, we will check and provide you the better solution quickly.

[Vaishali Jain]

Hi,

Thanks for the help. I appreciate this.

but I want to show the excel data in spreadsheet with static content and only amount column should be editable where user can enter the values.

I don't want to import the excel directly. I want that data should come from API or from static JSON.

Waiting for your another response! Thanks in advance

Thanks,

Vaishali Jain

[Babu Periyasamy]

Hi Vaishali Jain,

Thanks for the update.

We have checked your reported query and below mentioned the details,

Unlock particular range of cells:

In our Spreadsheet component, currently we don’t have any inbuilt method to select a range of cells and lock or unlock the particular cells. However, it is possible to achieve your requirement using the lockCells method to unlock the particular range of cells after protecting the sheet. For your convenience, we have prepared the sample and below attached the code snippet for your reference,

Code snippet:

created() {

        let protectSetting: ProtectSettingsModel = {

            selectCells: true,

            formatCells: false,

            formatRows: false,

            formatColumns: false,

            insertLink: false,

        };

        let sheetName: string = this.spreadsheetObj.getActiveSheet().name;

        //Protect the active sheet.

        this.spreadsheetObj.protectSheet(sheetName, protectSetting);

        this.spreadsheetObj.lockCells('A1:A20', false); // To unlock the A1:A20 cells.

    }

In the above sample, we have protected the active sheet using the protectSheet method in the created event and then set the lock cells property to false using the lockCells method for the particular range of cells to remain editable while other cells are not able to edit.

Open as JSON:

In our Spreadsheet component, we have support to open the specified JSON object of Excel file using the openFromJson method and also, we can save the Spreadsheet as the JSON object using the saveAsJson method.

For your convenience, we have prepared the sample in which we have saved the Spreadsheet as JSON on one button click and, on another button click, we have opened the saved JSON. Below attached the code snippet for your reference,

Code snippet:

openFromJson() {

        this.spreadsheetObj.openFromJson({ file: this.jsonData.jsonObject })

    }

    saveAsJson() {

        this.spreadsheetObj.saveAsJson().then((result) => {

            this.jsonData = result;

            console.log(result);

        });

    }

And we have prepared the video demonstration for the above 2 cases and attached below along with the prepared sample for your reference,

Sample link: https://stackblitz.com/edit/angular-kyc9o4-wlhsde?file=src%2Fapp.component.ts

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Lock_cell_and_Json2124408275

Additionally, if you want to load the data source into the Spreadsheet, you can set the data to the dataSource property of Range model of Spreadsheet.

For more information, please refer the below documentation link,

https://ej2.syncfusion.com/angular/documentation/spreadsheet/data-binding

Kindly, check the above details and get back to us for further clarifications.

[Vaishali Jain]

hi,

Thanks for answering!

Need some more help. How to by default open the particular excel file on the spreadsheet and want to get the data of it so, that I can use this data to create spreadsheet by my own without importing the excel file.

Thanks,

Vaishali Jain

[Babu Periyasamy]

Hi Vaishali Jain,

Thanks for your update.

We have checked your reported query and suspect that you want to load the excel file initially in the Spreadsheet component. This can be achieved by fetching the Excel file from a location using the fetch call, converting it into a Blob, and then converting this Blob into a file. Finally, you can open the file in the Spreadsheet using the open method in the created event.

For your convenience, we have prepared the sample in which we have loaded the external file in the created event, and you can also load your excel file by fetching the file from the desired location and attached below along with the code snippet and video demonstration for your reference,

Code snippet:

created() {

        // fetch the remote url

        // You can fetch your own file.

        fetch(https://cdn.syncfusion.com/scripts/spreadsheet/Sample.xlsx)

          .then((response) => {

            response.blob().then((fileBlob) => { // convert the excel file to blob

            let file = new File([fileBlob], "Sample.xlsx"); //convert the blob into file

            this.spreadsheetObj!.open({ file: file }); // open the file into Spreadsheet

            })

          })

    }

Sample link: https://stackblitz.com/edit/angular-64ffvl-yof6yu?file=src%2Fapp.component.ts

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Open_file_initially1358211282

For more information, please refer the below documentation link,

https://ej2.syncfusion.com/angular/documentation/spreadsheet/open-save#open-an-external-url-excel-file-while-initial-load

Kindly, check the above details and get back to us for further clarifications.

[Vaishali Jain]

Hi,

Thanks for resolving my query! I have one more query related to this.

In the excel I have merged some columns on fetching this excel why merged columns are not displaying as merged.

Why these formatting's are not preserved?

Why it's not fetching on spreadsheet exactly like excel.

Thanks,

Vaishali

[Babu Periyasamy]

Hi Vaishali Jain,

We have checked your reported query based on the provided details. And to check your reported issue, we have prepared the local angular sample and also created a dummy excel file with merged cells in it.

And then, we have checked opening the created excel file initially by using the fetch call in the created event and fetch the file in the assets folder of the sample and open it in the Spreadsheet using the open method. And the file is properly imported with merged cells, and we are not able to replicate your reported issue in our end.

For your convenience, we have prepared the video demonstration and attached below along with the prepared sample and created excel file for your reference,

Sample link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/my-app1621779314

Prepared excel file: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Merged_cell_sample-1722854777

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Merged_cell_excel-2127231936

Kindly, check the above details. And if you are still facing the issue, please share the below details from your end,

1. If possible, please share the excel file that you are facing issue with dummy data which will be more helpful for us to validate.
2. If you have done any customizations while opening the excel file, please share those details along with the customization code blocks.
3. Share the Spreadsheet package version and angular CLI version that you are using.
4. Share how you are opening your excel file using UI interaction or open public method.
5. Share complete spreadsheet component rendering code snippet with customization codes.

Please share the above requested details. Based on that, we will check and provide you the better solution quickly.

[Vaishali Jain]

Hi,

Thanks. It's working.