FlexSheet - Show data from Json | General Discussion | Wijmo 5

FlexSheet - Show data from Json

Posted by: ashrivastav1 on 14 September 2017, 2:54 am EST

Please login to follow topic

ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi,

I am trying to get an reference/example how to use json file as data source to show the data in the sheet.

I am using the FlexSheet. Looking forward for your help.

Tnanks
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Abhishek,

To show data from JSON file, you will need to use HTTP service for reading data from JSON file. The response returned by HTTP service, can be used as a data source to the FlexSheet.

For reference, please see the attached sample that implements the sample.

*Please install required package before running sample using npm install command and after running npm start command open http://localhost:4200 in browser window

Thanks ,

Manish Kumar Gupta

2017/02/FlexSheet_JSON_data_HTTP_service.zip
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Dear Manish,

Thanks for your help. Everything is working fine.

Can you please, also give me some suggestion how can I implement the CRUD operation by following your example.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hello Abhishek,

You can use OData service for CRUD operation.Please refer to the following demo sample;

http://demos.wijmo.com/5/Angular/OData/OData/#/products.

Please let us know, if you have any further queries.

Thanks ,

Manish Kumar Gupta
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

Thanks for providing the URL.

As I am very new on this, so for the better understanding, can you please provide something in example for the CRUD operations as you did last time.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hello Abhishek,

We are looking into this. We will be back soon.

Thanks,

Manish Kumar Gupta
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Thanks Manish in advance. I am waiting …
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST
Hi Abhishek,

Could you please share some information with us so that we can provide you appropriate solution?

Please share following information with us:

from where data is retrieved ? JSON File/ database

which service is used for fetching data from server? OData/BreezeJs/ HTTP service

how you want to perform CRUD operation

Thanks ,

Manish Kumar Gupta
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST
Hi Manish,

Please find inline response of the queries below

from where data is retrieved ? JSON File/ database

>>> In Maximum cases we will be using the JSON files. But in some cases , there is the requirement to fetch the data from MySql database.

which service is used for fetching data from server? OData/BreezeJs/ HTTP service

>>> I am trying/implemented with HTTP service. Please suggest with some running examples/code if other services are more fast.

how you want to perform CRUD operation

>>> The scenario will be like, we will show the data on the front end to the user using FlexSheet. User will be able to add/updated/delete the data. Data will come either from JSON or MySql db.

Thanks,

Abhishek
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

Is there any update for me.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi,

We are looking into this and it will take some time to create sample.

Thanks,

Manish Kumar Gupta
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

Thanks for all your support/help.

We are looking forward for your response on this.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST
Hi Abhishek,

We apologize for the delayed response.

We have created a sample by simulating the web API for using HTTP service. You will need to write your own API for interacting with Database and just change the url of web API in the hero.service.ts file.

For updating changes in Database made in FlexSheet, you will need to handle collection view collectionChanged event.

For reference, please see the attached sample that implements the same.

*Please install required package before running the sample using following command:

npm install

npm install angular-in-memory-web-api

Hope this helps you!

Sorry again for the delayed response.

Thanks ,

Manish Kumar Gupta

2017/02/FlexSheet_CRUD_Operations.zip
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Dear Manish,

Thanks to you again for this valuable help.

Manish, can you please let me know from where I can get the database connection logic.

I mean where in the script I can see the logic how the CRUD operation is working with MySQL or JSON.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Abhishek,

We used mock Web API provided by Angular to show how CRUD operation can be performed.

Here are brief code descriptions:

>>in-memory-data.service.ts file provide WEB API using InMemoryDbService from angular-in-memory-web-api

>>hero.service file provides method for using HTTP service, you can also use direct HTTP service where these methods are used.

heroesUrl property of hero.service class is used for providing Web API url. In the sample, we used WebAPI provided by Angular

>>app.component.ts file use HeroService for CRUD operation. CRUD operation performed in collectionChanged event handler.

You have to write your own logic for Database operation in RESTful API. Please refer to the following link for writing your own API.

https://www.tutorialspoint.com/restful/restful_introduction.htm

Hope this helps you !

Thanks,

Manish Kumar Gupta
ashrivastav1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

I am following the suggestions/example those you provided. But I am unable to update my json file. Also I didn’t see any option to update the database thru json or directly from ts files.

Can you please suggest on this.

Thanks,

Abhishek
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Abhishek,

We are sorry , the delay in getting back. We are still working on your query. We are trying to create WebAPI according to your requirement. It will take some more time.

Thanks,

Manish Kumar Gupta

skochan

Post Options:
Link
Copy

Posted 14 September 2017, 2:54 am EST

Hi ashrivastav1,

I am really sorry for the delay on this. It will take us some time until we get a full featured working sample across to you as it involves writing a Web Service & a Client Application.

As per your use case you would like JSON file as data source to show the data in the sheet and perform CRUD operations on the data. The CRUD operation part is a service implementation and you will need to write your own custom service to perform these operations on the JSON data that resides on your server. This implementation is independent of Wijmo FlexSheet.

Once you have defined the CRUD methods,its only a matter of calling the CRUD methods in your Angular Application to update the Service. The heart of the Client Side application resides in the Wijmo Collection View object. This object has properties and methods through which you evaluate if Records have been added,modified or deleted.

Here are the scenarios:

When Application is first loaded and Service Call is made to get Data- (Read Operation)

We need to store the JSON data returned by the service in Wijmo Collection View object, this object is bound to FlexSheet so data gets displayed on the FlexSheet

Update Operation-

When you make any changes to the FlexSheet, Wijmo Collection View object also gets modified and you can get the list of changes using the itemsEdited property of the Wijmo Collection View object. Now you need to call your service to update the data.

Add Operation-

When you add a record to the FlexSheet, Wijmo Collection View object also gets modified and you can get the list of changes using the itemsAdded property of the Wijmo Collection View object.Now you need to call your service to add the data.
Delete Operation-

When you delete a record in FlexSheet, Wijmo Collection View object also gets modified and you can get the list of changes using the itemsRemoved property of the Wijmo Collection View object.Now you need to call your service to delete the data.

Please see the code snippet for reference

@ViewChild(‘flex’) flex: wjFlexSheet.FlexSheet;
constructor(private heroService: HeroService) {
this.updateHero();
this.heroService.getHeroes()
.then(heroes => {
this.heroes = new wjCore.CollectionView(heroes); // load data (read)
console.log(this.heroes);
this.heroes.trackChanges = true;
this.heroes.collectionChanged.addHandler((s, e) => {

if (this.heroes.itemsEdited.length > 0 && this.heroes.itemsEdited[0].id !== undefined) { // check item if item edited
console.log(‘updated edit item’);
this.heroService.update(this.heroes.itemsEdited[0]).then(() => { console.log(‘updated’); }); /// call service to update
this.heroes.itemsEdited.clear();
}
if (this.heroes.itemsAdded.length > 0 && this.heroes.itemsAdded[0].name !== ”) {// check item if item added
this.heroService.create(this.heroes.itemsAdded[0].name).then(hero => {
// update collection view
this.heroService.getHeroes().then(heroes => this.heroes.sourceCollection = heroes);
//clear addedItems array
this.heroes.itemsAdded.clear();//call service to add record
console.log(‘Added new record’, this.heroes, hero);
});
}
if (this.heroes.itemsRemoved.length > 0) {
this.heroService.delete(this.heroes.itemsRemoved[0].id).then(() => {// check item deleted
//clear removedItem array
this.heroes.itemsRemoved.clear();// call service to delete
console.log(‘Deleted deleted record ‘);
});
}
});
});

}

Regards,

Abhishek Dutta

raj-ankathi
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

We are evaluating Wijmo ng2 here @Fox. We have this requirement to upload data in bulk to the SQL database and also download the data in sheets. I got to show the data on Flexsheet using the CollectionView but unable to get the sorting work (didn’t find enough details in the documentation or in C:\Wijmo\C1Wijmo-Enterprise-Eval_5.20171.293\Samples\TS\Angular2\FlexSheetExplorer sample).I know I am missing some properties or methods that need to be set for sorting to work.Attached is the error when i click on sorting.

Do you have a detailed sample on this particular requirement. What would you suggest? I am little bit confused with Flex Grid and Flex Sheet. What would be the best option to proceed on this.Prompt response is highly appreciated.

Regards,

Raj Ankathi
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Raj,

Thanks for evaluating Wijmo 5.

As we understand, you have resolved the issue regarding updating and showing data from SQL to FlexSheet. If not, you need a WebAPI to perform CRUD operations.

Thanks for reporting the sorting issue with WebAPI. We are able to replicate this issue and it requires further investigation. Hence, this issue has been escalated to the concerned team for further investigation.

As a workaround please assign an empty CollectionView to the itemsSource at declaration, sorting should work as expected.

We have created a demo sample that contains FlexGrid and FlexSheet with CRUD operation being performed on button click for each of them.

FlexSheet is an extended version of FlexSheet with some additional features of Excel. Please refer to the following blog post for getting more information on FlexSheet.

http://wijmo.com/blog/welcome-to-flexsheet/

If we have missed something or not being able to understand your query, please elaborate your requirement so that we can help you accordingly.

Thanks,

Manish Kumar Gupta

2017/05/CRUD_WebAPI_FlexGRid_FlexSheet.zip

raj-ankathi

Post Options:
Link
Copy

Posted 14 September 2017, 2:54 am EST

Hi Manish,

Thank you for the sample.Sorry if my query wasn’t clear.

The sample you provided helps in getting the data from the API and add new rows to the data instead I would like to have the users to upload the sheet and save the sheet data to the SQL Database.As you see the gif I am attaching when I click on the Export to DB button I need to get the uploaded flex sheet’s data and save as per the business Req’s. Please also provide some guidance regarding the sheet templates headers.I mean I need to leave the column headers while saving the data to DB.

importdata.component.html
<wj-sheet [rowCount]="25" [columnCount]="12" [itemsSource]="sheetData"></wj-sheet>
 <wj-sheet [rowCount]="25" [columnCount]="12"></wj-sheet>

importdata.component.ts
Load Sheet Event
load() {
        debugger;
        var flexSheet = this.flexSheet,
            fileInput = <HTMLInputElement>document.getElementById('importFile');
        if (flexSheet && fileInput.files[0]) {
            flexSheet.load(fileInput.files[0]);
        }
    }
ExportToDB Event
exportToDB() {
        debugger;
        for (let i = 0; i < this.sheetData.itemsAdded.length; i++) {
            if (this.sheetData.itemsAdded[i].name !== '') {
                //this._ps.savePersonDetails();
                console.log(this.sheetData);
                console.log("Saved");
            }
        }
    }

2017/05/test.gif

2017/05/test-1.gif

manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST
H Raj,

We are sorry for the delayed response.

Since you are exporting data to DataBase for loaded excel file, you need to upload complete itemsSource of FlexSheet.

When an Excel file is loaded into the FlexSheet, it does not create an itemsSource for the sheet. Hence, there are two ways for uploading records on the database:

By creating itemsSource while loading Excel sheet

By creating itemsSource while uploading to database

Approach 1 :

For this, you need to load sheet asynchronously and create itemsSource for each sheet available in FlexSheet. After creating itemsSource for each sheet, clear loaded sheets from FlexSheet, add boundSheet to the FlexSheet. Now, you can modify the sheet.

Approach 2 :

For this, you need to create itemsSource on export button click by using getCellData() method.

For Exporting Data to the database, you can use batch process or update database one by one record. We suggest you create an API that accepts JSON data for batch updating in the database. It requires only one call to the database.

For Approach 1, please refer to the attached sample that implements the same.

Thanks,

Manish Kumar Gupta

2017/06/FlexSheet_itemsSource_importedSheet.zip
raj-ankathi
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST
Hi Manish,

Thank you very much for the suggestions and sample.I have tested the sample you provided, it complaints as below

"wijmo.xlsx.min.js:13 Uncaught TypeError: Cannot read property 'formula' of undefined"

Once I get the ItemSource how can I pass the data to Web API since the format of the object is different than JSON

I tried the JSON.Stringify(source) ->

“{“Short Name”:“XYZ Craig”,“Status”:“Active”,“Business Unit”:“FNG 2017”,“Current Title”:“Actor”,“Original Hire Date”:“2016-01-01T08:00:00.000Z”,“Termination Date”:“15/11/2016”,“Date of Birth”:“1965-01-01T08:00:00.000Z”,“Contractual”:“No”,“Equity Participant”:“No”,“Equity Participant Id”:“Fox007”}”

How do i make name to json format from ( “Property Name”: “propertyValue” ) to ( “propertyName”: “propertyValue” )?

Could you please provide an Angular 2 sample for both the approaches.

P.S: On debugging I found the issue was with empty cell values.

Regards,

Raj
raj-ankathi
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hi Manish,

Working further, saving flex sheets data to database noticed a problem. I am not sure if it’s supposed to work that way.

If the cells of the sheet have null values it’s not considering that particular cell at all. It skips that cell, you can see that in the attached GIF.

Please suggest. Help is highly appreciated.

Best Regards,

Raj

2017/06/flexsheetJsontest1.gif
manish-gupta1
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hello Raj,

Thank you for additional information.

We have updated the sample for including the cell with null value in FlexSheet. Now, the cell with null value will not be escaped.

Please find the updated sample for the same.

We are sorry, we can not provide the sample in Angular2 to show how to update data on the server using API since for this we need to create an API for the same.

Thanks,

Manish Kumar Gupta

2017/06/FlexSheet_itemsSource_importedSheet-1.zip
raj-ankathi
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Hello Manish,

Thank you very much for the sample. I am able to get the data to save it to DB but only if I have one sheet.Let’s say I have 4 sheets I am guessing I need to initialize 4 source arrays. Please suggest how to proceed if I have multiple sheets in Flexsheet.

Regards,

Raj Ankathi
raj-ankathi
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

Sorry never mind.I am able to get the data. I had to skip the last sheet which has all the dropdowns for the lookups for DB. Now I need to work on Exporting to DB.

Thanks for all the help

Best,

Raj
ortwin
- Post Options:
- Link
  Copy
Posted 14 September 2017, 2:54 am EST

[wijmo_archive]a
zafar4sure
- Post Options:
- Link
  Copy
Posted 22 June 2018, 10:50 pm EST

Hello there,

I don’t have the perfect solution to your problem but I have a very good tutorial for Informatica Read JSON

Check it out…Hope it Helps:)

https://zappysys.com/blog/read-json-informatica-import-rest-api-json-file/
zicrovjames
- Post Options:
- Link
  Copy
Posted 12 April 2021, 11:34 pm EST

Hi,

someone suggest me a post about Informatica Read Json I hope it will help you.

https://zappysys.com/blog/read-json-informatica-import-rest-api-json-file/

Please login to reply to thread

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.