How can I load an xls file and refresh a pivottable using OWC?

Posted on 2007-10-02
Last Modified: 2008-01-09
We have a classic database reporting setup with SQL Server 2005, ASP .net 2 and IE 6. The reports produced are highly customised and do not neatly fall into datagrid style layouts. Additionally the reports have already been developed to use an excel pivottable embedded in the report to select and layout the data (using GETPIVOTDATA).

We want to make it possible for a user to be able to select a number of parameters from a webpage to filter the data in the report and then make the report available for download.

Can the Office Web Component (OWC) be used to load a copy of the excel template and refresh the pivottable against a stored procedure in SQL?

This works using Excel Automation, however Excel cannot be installed on the webserver; the Office Web Component (OWC) is available. Version 10 is currently installed, although 11 could be installed if necessary.

Question by:PeterMillsUK
    LVL 1

    Author Comment

    Further research suggests that this is not possible:

    The OWC cannot open XLS files, neither can it open Excel XML files as the file structure is different.
    LVL 9

    Accepted Solution


    I have never used OWC, do not have any of the components to test, & so I'm depending on what I read in the following after I did some googling to learn what OWC is about.  -

    Here is a quote -

    To show you how simple it can be to use an Excel spreadsheet with OWC, I created a simple "Loan Calculator" spreadsheet with input cells for Principal, Term, and Interest Rate, and an output cell that uses the Excel PMT() function to compute the monthly payment. We then save this workbook using the "XML Spreadsheet" option available on Excel 2002 and above. This is the only format that the OWC Spreadsheet Component can read and bring in all the formulas, etc. Do not attempt to load a regular XLS file in OWC, it can't be done, period.

    Contradicts the message board article you found.  Hope this helps.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now