Post reply

Export to Formatted Excel file

  • March 4, 2005 at 1:35 am

    #61663

    Please - I'm so stoked with this requirement, hopefully someone can help me.

    I have to export data to an already formatted excel spreadsheet which contains a pivot chart. The data to be exported will differ depending on the filtering options by the person who executes the stored procedure (from ASP page).

    Any help will be much appreciated

  • March 4, 2005 at 6:17 am

    #545438

    A little more info on your problem....

  • March 4, 2005 at 1:03 pm

    #545564

    without knowing specifics this may not apply, but, I have found it easier to use excel to pull the data rather than try and push it.

    please tell us more of the details

  • March 4, 2005 at 3:56 pm

    #545597

    Ok, here is some more information....

    The user will filter data on an ASP web form:

    - Select a date range (From & To)

    - Select People to view report on (from a pre-populated list box)

    When submitted, it will execute and pass parameters to a stored procedure (using the IUSR account assigned to public on SQL Server) to output the results. The results from SQL server need to be dumped into an excel template file that resides on the web server. This excel template contains a pivot chart and other pre-formatted excel functions that are Business Requirements.

    I've tried doing a "Pull" from excel, but the data needs to be filtered first and there are permission problems restricting non-sql server accounts. Using the ASP web form allows us to execute the stored procedure using the anomynous account (IUSR) which is a trusted account in SQL server.

    After the dump, I want to provide the user with the option to save to their PC.

    Hopefully I've given more to go by. Thanks for your time.

  • March 5, 2005 at 6:55 am

    #545621

    Yay.... I've solved this problem by using ADO with the Microsoft Jet OLE DB 4.0 Provider to read and write data into Excel.

    The following article guided me: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3BQ278973

    The demo uses an MS Access DB, but I modified my ASP pages to execute a stored procedure on SQL Server.

    Thanks to those that tried to assist

  • March 7, 2005 at 2:47 pm

    #545790

    Hi,

    I just posted  a similar question today about reports output to Excel "Cross-Tab Report Strategies" then I saw this postings. I downloaded examples from the article, got ASP example, modified it to use from the plain VBScript, tested if I can modify one field and it worked. What I don't understand is: the update syntax as follows:

        oConn.Execute "Insert into Last_Name (F1) Values ('Davolio')"

     I do understand that Last_Name is a named cell. In the example EmpData.xls it points to the cell D46 if you click on it or output to CSV file. At the same time the named interval for First_Name points to =EmployeeData!$F$5 when you do Insert ->Name->Define  or if you just click on the cell celection drop-down on the toolbar and select Last_Name. The same with all other cells.

    What is you opinion on that? Where is the value actually stored?

    Yelena

     

    Regards,Yelena Varsha

  • March 8, 2005 at 2:06 am

    #545825

    Hi Yelena,

    "Insert into Last_Name" --->

    In Excel, "Last_Name" is referred to like a name of a table in a DB and the attributes are the defined cell range. In this case, just the one cell. This can be a wider range though, like in the ProductsTemplate.xls

    (F1)

    The same as saying "Insert into Field 1 of the defined name (the range of Last_Name)". 

    In excel, you define the name range to be one cell above where the data is to be inserted because the OLE DB provider uses this to guess the format of the cells.

    Cheers!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply