Export to Spreadsheet

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Sunday, March 18, 2012PrintSubscribe
Export to Spreadsheet

Code On Time web applications offer the Export to Spreadsheet action. The feature is available in all grid views of your application by default.

'Export to Spreadsheet' action in Code On Time web applications

Select this option and you will be prompted to download a file. The following screen shot shows the prompt displayed by Internet Explorer 9 at the bottom of the web browser window.

Prompt displayed in IE9 in response to 'Export to Spreadsheet' action in Code On Time web application

The file has the “*.iqy” extension. This file format is recognized and supported by Microsoft Excel.

If you save the file to the hard drive and open it in the Notepad then you will see a cryptic-looking URL that points to your web application.

If you choose Open option instead then Microsoft Excel will start if you have it installed on your computer. You will see a prompt similar to the one in the next picture.

The prompt to download '*.iqy' file displayed by Microsoft Excel

Microsoft Excel wants to let you know that it is going to download the content, which is not located on your computer. You have to authorize that by clicking on Enable button. Nothing dangerous is coming your way. The content is an XML file produced by your application in response to the URL in the “*.iqy” file.

XML content produced in response to the URL specified in '*.iqy' file

Click Enable and you will see the prompt shown next.

This time Microsoft Excel contacts your web application. Your application needs to know the identity of the user and requests this information from Excel. That is why you are seeing the prompt.

The prompt to enter the application user id/password displayed in response to 'Export to Spreadsheet' action in Code On Time web application

Enter the identity of the user registered in the membership database. For example,enter admin/admin123% or user/user123% – these are two default users accounts created automatically in the baseline application.

Click OK button and you will see the data from your web application display in Microsoft Excel.

Application data exported to Microsoft Excel

Select Insert tab on the ribbon and choose to insert the PivotTable.

'Insert PivotTable' option in Microsoft Excel

Dialog Create PivotTable is displayed.

'Create PivotTable' dialog in Microsoft Excel

Click OK to insert a new sheet with the pivot table.

Empty pivot table created in Microsoft Excel

The pivot table PivotTable1 is empty. Notice the list of fields on the right-hand side of the window. All visible fields from the Orders screen are listed there.

Drag Customer Company Name field to the “Values” box and you will see the total number of orders in the database.

Total number of orders in the application database

Drag Employee Last Name to the “Row Labels” box and you will see that the most product employee is Ms. Peacock. She has placed 156 orders.

The most productive employee in the pivot table

Drag Ship Via Company Name field to “Column Labels” box and you will see the breakdown of orders by employees and shippers delivering the goods to customers.

Breakdown of orders by employees and shipping companies

Numbers don’t always tell the story.

Choose PivotChart item on the right-hand side of the Options ribbon. Select 3-D Cylinder chart and click OK button.

'Insert Chart' dialog in Microsoft Excel

We can now clearly see that Ms. Peacock is heavily utilizing the shipping company United Package. If this is the most expensive shipping option then we should call Ms. Peacock and advise to switch to another freight operator. If this this is the least expensive option then a bonus is due in recognition of an employee thinking about the business bottom line.

Ms. Peacock is heavily utilizing the shipping company 'United Package'

You can save this spreadsheet to the hard drive.  Open the spreadsheet a week a later and choose Data | Refresh All and you will be prompted to re-enter user name and password – the new data feed will be produced by your application and the charts and cross tables will automatically refresh.

'Data | Refresh All' option in Excel will contact your web application and retreive a fresh data set, which will result in updated charts and cross tables.

You web application created with Code On Time becomes an information hub of your company. Business users can do ad hoc data analysis with the tool they know best. The business decision makers don’t even need to sign in the web application to know what’s going on. Microsoft Excel charts and pivot table swill let them know everything they need.