Topics

Using MS Excel for Dash Board integrating with uniPaaS 1.9

Lado Wali
 

Hi Group,

Just checking if we can integrate MS Excel with uniPaaS 1.9 to display exported data for graphical interface. In normal cases we export the data to xml and open in file in Excel to create the pivot and also create graphs. Just wondering if we can create some kind of excel template and then write the data from uniPaaS to Excel. Just wondering if someone can share their ideas.

Thanks
Lado


Wes Hein
 

Lado,

1. Use DataViewToText() to get it to a csv, then use OfficeCom to open the csv and save it as a xls if excel is installed and you are ok with xls, mostly easy.
2. Still use DataViewToText() to get it to a csv then read through the file and write each line to xlsx using a dll you will need to write using openxml and closedxml, not so easy but works without excel installed.
3. Write a dll using using openxml and closedxml that allows you to write to a dntable then convert the table to excel, also not so easy but gets you a xlsx and does not require excel to be installed.

We do have a dll for option 2 but it is somewhat out of date and not really sharable as contains other features for our company as well.

Once you use XPA this becomes much easier.

Wes

Lado Wali
 

Wes,

My intention is to create the pre-design excel template with Sheet1 either Pivot or Graph based on the data on Sheet2 and if somehow we can throw the data on Sheet2 in the required cells through uniPaaS and open the Excel template from the uniPaaS program.

Anyway thanks for your time.
Lado



On Mon, May 18, 2020 at 2:10 PM Wes Hein <wes@...> wrote:
Lado,

1. Use DataViewToText() to get it to a csv, then use OfficeCom to open the csv and save it as a xls if excel is installed and you are ok with xls, mostly easy.
2. Still use DataViewToText() to get it to a csv then read through the file and write each line to xlsx using a dll you will need to write using openxml and closedxml, not so easy but works without excel installed.
3. Write a dll using using openxml and closedxml that allows you to write to a dntable then convert the table to excel, also not so easy but gets you a xlsx and does not require excel to be installed.

We do have a dll for option 2 but it is somewhat out of date and not really sharable as contains other features for our company as well.

Once you use XPA this becomes much easier.

Wes

Steven Blank
 

Lado,

I've kicked around a similar dashboard idea of late, but I wanted to leave MS Excel out of the mix.

Instead, I've been thinking of placing a plain old web browser control on my Magic form and then feeding it HTML. One can accomplish some pretty amazing stuff now with HTML5.

I have not pursued it any further, though. Sorry.

Steve Blank

On 5/18/2020 8:10 AM, Lado Wali wrote:
Hi Group,

Just checking if we can integrate MS Excel with uniPaaS 1.9 to display exported data for graphical interface. In normal cases we export the data to xml and open in file in Excel to create the pivot and also create graphs. Just wondering if we can create some kind of excel template and then write the data from uniPaaS to Excel. Just wondering if someone can share their ideas.

Thanks
Lado


Wes Hein
 

Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes

Lado Wali
 

Thanks Steve.


On Mon, May 18, 2020, 2:31 PM Steven Blank <sgblank@...> wrote:

Lado,

I've kicked around a similar dashboard idea of late, but I wanted to leave MS Excel out of the mix.

Instead, I've been thinking of placing a plain old web browser control on my Magic form and then feeding it HTML. One can accomplish some pretty amazing stuff now with HTML5.

I have not pursued it any further, though. Sorry.

Steve Blank

On 5/18/2020 8:10 AM, Lado Wali wrote:
Hi Group,

Just checking if we can integrate MS Excel with uniPaaS 1.9 to display exported data for graphical interface. In normal cases we export the data to xml and open in file in Excel to create the pivot and also create graphs. Just wondering if we can create some kind of excel template and then write the data from uniPaaS to Excel. Just wondering if someone can share their ideas.

Thanks
Lado


Lado Wali
 

Thank you Wes.


On Mon, May 18, 2020, 2:42 PM Wes Hein <wes@...> wrote:
Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes

Lado Wali
 

Thank you Wes


On Mon, May 18, 2020, 2:47 PM Lado Wali <ladowali@...> wrote:
Thank you Wes.

On Mon, May 18, 2020, 2:42 PM Wes Hein <wes@...> wrote:
Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes

Frigyesi Viktória
 


I was following the same approach: placing a browser control on a Magic form, refreshing in the needed interval. The browser is showing a diagram, which is made in Excel. You can set the save as properties of the Excel Diagram to be HTML, one form, always overwrite without prompt (some VB is needed here within form). Diagram is refreshed automatically when new data is saved into source. The security settings need some adjustment too. It works fine as a Dashboard showing high transactional volume KPIs.

Feladó: main@magicu-l.groups.io <main@magicu-l.groups.io>, meghatalmazó: Wes Hein <wes@...>
Elküldve: 2020. május 18., hétfő 20:42
Címzett: main@magicu-l.groups.io <main@magicu-l.groups.io>
Tárgy: Re: [magicu-l] Using MS Excel for Dash Board integrating with uniPaaS 1.9
 
Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes

 

I was used zoho Analytics tools for our Dash Board. A Html Merge magic trigger program generate data file and store into google drive.

The zoho data Sync utility import data scheduled interval. Very simple function to create dash board and pivot table report. 


Regards

r.a.murali  



On Tue, May 19, 2020 at 10:13 AM Frigyesi Viktória <fv@...> wrote:

I was following the same approach: placing a browser control on a Magic form, refreshing in the needed interval. The browser is showing a diagram, which is made in Excel. You can set the save as properties of the Excel Diagram to be HTML, one form, always overwrite without prompt (some VB is needed here within form). Diagram is refreshed automatically when new data is saved into source. The security settings need some adjustment too. It works fine as a Dashboard showing high transactional volume KPIs.

Feladó: main@magicu-l.groups.io <main@magicu-l.groups.io>, meghatalmazó: Wes Hein <wes@...>
Elküldve: 2020. május 18., hétfő 20:42
Címzett: main@magicu-l.groups.io <main@magicu-l.groups.io>
Tárgy: Re: [magicu-l] Using MS Excel for Dash Board integrating with uniPaaS 1.9
 
Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes

Lado Wali
 

Thanks for the ideas.

Regards,
Lado

On Tue, May 19, 2020 at 3:50 AM R A Murali <annamalaimurali@...> wrote:

I was used zoho Analytics tools for our Dash Board. A Html Merge magic trigger program generate data file and store into google drive.

The zoho data Sync utility import data scheduled interval. Very simple function to create dash board and pivot table report. 


Regards

r.a.murali  



On Tue, May 19, 2020 at 10:13 AM Frigyesi Viktória <fv@...> wrote:

I was following the same approach: placing a browser control on a Magic form, refreshing in the needed interval. The browser is showing a diagram, which is made in Excel. You can set the save as properties of the Excel Diagram to be HTML, one form, always overwrite without prompt (some VB is needed here within form). Diagram is refreshed automatically when new data is saved into source. The security settings need some adjustment too. It works fine as a Dashboard showing high transactional volume KPIs.

Feladó: main@magicu-l.groups.io <main@magicu-l.groups.io>, meghatalmazó: Wes Hein <wes@...>
Elküldve: 2020. május 18., hétfő 20:42
Címzett: main@magicu-l.groups.io <main@magicu-l.groups.io>
Tárgy: Re: [magicu-l] Using MS Excel for Dash Board integrating with uniPaaS 1.9
 
Rather than using a worksheet as the data source for the pivot table/dash board, use a different workbook.  By having your template reference a different file, you can delete and replace the data at will without messing up the dashboard

Wes