lunes, 23 de abril de 2012

Excel desde HTML

Excel's Web query technology adds some custom table data cell (TD) attributes that can enhance the spreadsheet. One of these attributes is Filter. Placing Filter inside a causes drop-down filters to appear in the column headers of the spreadsheet. Let's place a FILTER=ALL inside the tags in the column header: ProductID Product Name Sales Person Date Closed Sale Amount You would now be able to filter down the results by using drop downs. For instance, you could drop down the Sales Person filter and view the results for a specific sales person. The filtering is all client side (inside Excel) and the results are instantaneous. Excel simply hides the rows that don't meet the filter criteria. Another custom attribute that can be added to the HTML table is Formula. When Formula is used inside a , any Excel formula can be used inside the cell. An example would be for summary cells, where you want to show averages and the like: Again, this can modified using ASP scripting. If you are tracking the number of rows you are writing to the HTML table, you can dynamically write Excel formulas: The majority of styles used in Microsoft Internet Explorer's (IE's) cascading style sheet (CSS) will be converted properly by Excel. However, pay special attention to the number format. By default, Excel imports all data into the General format. If you need a specific, precise Excel number format, you need to add a style attribute on the ASP side. This style attribute takes the form of vnd.ms-excel.numberformat:xxx. To ensure that a cell uses a precision of 2, your tag would look like the following: ><%= rsSales ("SalesAmount")%> For more detailed information on these HTML extensions, please consult the Connectivity Kit. Once the data is resting safely in Excel, there are an infinite amount of possibilities for further data manipulation. You can link the cells from the resulting HTML into other worksheets, workbooks, Word documents, and charts. For a good example of this, experiment with Pcquote.xls, which comes with the Connectivity Kit. Excel even integrates with the IE security model, and will display an authentication dialog for ASPs protected by IIS. Just like IE, Excel will cache the user name and password until that instance of Excel is shut down. Excel 2000 has added some new wrinkles to Web queries. The basic premise is the same, but 2000 makes it easier and faster to customize a Web query. Once developers learn the Excel 97 version, they will really come to appreciate these changes in Excel 2000. Microsoft has made a strong case for simple, Web-based Excel reporting solutions. The real value comes from ASP and the use of dynamic variables. This type of solution frees up the business analyst from time-consuming data manipulation and takes away a degree of human error. And, best of all, the implementation of Web queries is most assuredly rapid development.