4 ms·

This sounds an incredibly useful capability to have. May I ask which data connector did you used to pull data, and how did you made it available in that format

by sdrinf 9y ago

This sounds an incredibly useful capability to have. May I ask which data connector did you used to pull data, and how did you made it available in that format from the app side?

emidln 9y ago
Excel has a feature for pulling data stored in HTML tables into a sheet called "Web Queries"[0]. It also has a feature for automatically building these in the form of .iqy files. When I worked at Abbott Labs, it was a big deal to be able to offer export-to-Excel in a way that Excel could refresh automatically (or manually). This made it a breeze since we could register an .iqy serializer for a dataset and just give a download link to it in place of a CSV, JSON, or XML file. [0] https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba https://support.office.com/en-us/article/Get-external-data-f...
- btilly 9y ago
  That was exactly how I did it. The only major downside was that I had to be sure to keep the html consistent, and couldn't require login. Since it was a system for internal use, that was deemed acceptable.
cm2187 9y ago
If you can code, I suggest creating a custom addin with something like ExcelDNA. You offer an excel function to load the report in memory based on a given date, name or whatever. And a few functions to query that loaded report, that take as parameter the value returned by the load function to identify the report. So that gives the user the ability to query for a certain date, or sum for a date range, or to get a breakdown by some meta data. Being able to load multiple reports allows them to do deltas. And switching to a newer version of the report just takes changing the argument of the load function and press F9. Worth also giving the users sample spreadsheet, or having a way to export the full report with all these formulas in Excel so that they can learn by example. The only thing to watch for is memory clean up. You need somehow to have a way to unload all reports otherwise your memory blows up over time.

by sdrinf 9y ago

This sounds an incredibly useful capability to have. May I ask which data connector did you used to pull data, and how did you made it available in that format from the app side?

emidln 9y ago

Excel has a feature for pulling data stored in HTML tables into a sheet called "Web Queries"[0]. It also has a feature for automatically building these in the form of .iqy files. When I worked at Abbott Labs, it was a big deal to be able to offer export-to-Excel in a way that Excel could refresh automatically (or manually). This made it a breeze since we could register an .iqy serializer for a dataset and just give a download link to it in place of a CSV, JSON, or XML file. [0] https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba https://support.office.com/en-us/article/Get-external-data-f...

btilly 9y ago
That was exactly how I did it. The only major downside was that I had to be sure to keep the html consistent, and couldn't require login. Since it was a system for internal use, that was deemed acceptable.

cm2187 9y ago

If you can code, I suggest creating a custom addin with something like ExcelDNA. You offer an excel function to load the report in memory based on a given date, name or whatever. And a few functions to query that loaded report, that take as parameter the value returned by the load function to identify the report. So that gives the user the ability to query for a certain date, or sum for a date range, or to get a breakdown by some meta data. Being able to load multiple reports allows them to do deltas. And switching to a newer version of the report just takes changing the argument of the load function and press F9. Worth also giving the users sample spreadsheet, or having a way to export the full report with all these formulas in Excel so that they can learn by example. The only thing to watch for is memory clean up. You need somehow to have a way to unload all reports otherwise your memory blows up over time.