Solved

how to download the content of a web page into an Excel sheet by clicking a button?

Posted on 2004-04-14
14
1,188 Views
Last Modified: 2009-07-29
Dear experts,

On my web page, there is a button which allows user to download the content of the page into an Excel shee. I have no clue how to do it? Will Javascrip work? thanks.
0
Comment
Question by:panpanW
  • 5
  • 3
  • 2
  • +1
14 Comments
 
LVL 17

Expert Comment

by:dorward
ID: 10827618
It is most likely a server process that generates an excel spreadsheet on demand. How you would go about this depends on the capabilities of your webserver.
0
 

Author Comment

by:panpanW
ID: 10827889
thanks.
I am using tomcat server for JSP page. so, wil this become a JSP problem?
0
 
LVL 3

Expert Comment

by:xp_commander
ID: 10828095
Whoever is creating / dynamically generating your page JSP or any other process,  add ---> Content-Type:application/vnd.ms-excel for the header to the page in question and IE will launch inbuilt excel viewer to open that document, now all they have to do is save the doc. Also don't forget the extension of the page as .xls and not html.

Hope this helps.
0
 
LVL 17

Expert Comment

by:dorward
ID: 10830428
Adding that content type will not make it an excel file.

It is basically lying to the browser about the contents of the file and then depending on Excel being able to open the HTML.

IE will also not open any "inbuilt" Excel viewer, it will (you hope) handle it like any other file that claims to be an Excel spreadsheet. This might mean that it opens the browser plugin version of Excel.

If you want to create a real Excel spreadsheet, you might want to look into using these tools:

http://www.extentech.com/estore/product_detail.jsp?product_group_id=1
http://jakarta.apache.org/poi/index.html
0
 
LVL 3

Expert Comment

by:xp_commander
ID: 10835341
I really don't want to argue in this topic as it all depends on what background software application is used to generate Excel data , but here is a sample code from one of the domino lotusscript which is converting view data into Excel reports

'Sets the download to use Excel
      Print |Content-Type:application/vnd.ms-excel|      
      'Triggers the save/open prompt instead of embedding the spreadsheet in the browser.
'Set your default file name here also.
      Print |Content-Disposition:Attachment; filename="Report.xls"|
      On Error Goto errorHandler

If panpanW would be more specific on what he is exactly trying to do , I could provide him some psuedo code, etc.

Thanks
0
 

Author Comment

by:panpanW
ID: 10836136
thanks.
actually, i have a JSP application, which there is a page used for displaying data retrieved from database. our user wants to download these data into an Excel sheet. so, i plan to add a "download" button on the page to allow user to do that. is this clear?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:sandeshj
ID: 10839303
Here we go --
First in the html page, create a table no matter how
complicated it be, give an 'id' name to it. Following you
will find a very simplified version, but can be scaled on
up to however big you wish the table to be.


<table name=listTable id=listTable>
  <tr">
    <td>This will be what gets printed</td">
  </tr">
</table">


Ok, now for the javaScript:

<script language="javascript">
function fnDoCopy()
{
  textRange = document.body.createTextRange();
  textRange.moveToElementText(listTable);
  textRange.execCommand("Copy");
  window.open("outputFile.xls","printing");
}
</script>


Notice that I put for the url in the window.open()
statement, 'outputFile.xls'.  That is the name of your
pre-formatted Excel file that contains all of your
formatting.  

Now, you need to add a special 'print' button to your page
that will look more or less like this:


<input type=button value=Print onClick="fnDoCopy();">


So that when someone clicks that button, the javascript
function first copies the entire contents of
your 'listTable' table to the clipboard, and then opens a
new window that contains the Excel workbook.  All that's
left for your users to do at that point is press [CTRL]-V
and voila -- one beautiful printable page that you have
complete control over.

I hope this bit was helpfull to u, thanks.

Please inform me if there is a better way to do this...
Thanks
0
 

Author Comment

by:panpanW
ID: 10843463
thanks.
I tested your javascript and the content did not get print in the Excel page, which opens fine.Any clue?
0
 

Author Comment

by:panpanW
ID: 10843539
sorry. I miss read your post. it works!
the only thing is my user feels it is not friendly enough when they expect the content go into the file by itself, without any work from user.:(

Any other suggestion?
0
 
LVL 6

Expert Comment

by:sandeshj
ID: 10865637
yeah, there is another method in which the xsl sheet could be created, but the problem with this method is that althought its saved as an excel sheet, the internal format is totall different and cannot be processed by external programs. But the good news is that all the excel functionalities from MS excel will work fine,
here is that method....
1) create a hidden 'iframe' in the page
<iframe id="txtArea1" style="display:none"></iframe>

2) copy the following javascript...
function fnDoCopy1()
{
      var tab_text="<table><tr>";
      var textRange;
      tab   = document.getElementById('dataTable');
      for(j = 1 ; j < tab.rows.length ; j++)
      {
            if(tab.rows[j].style.display=="")
            {
                  tab_text=tab_text+tab.rows[j].innerHTML;
                  tab_text=tab_text+"</tr><tr>";
            }
      }
      tab_text=tab_text+"</tr></table>";
      txtArea1.document.open("txt/html","replace");
      txtArea1.document.write(tab_text);
      txtArea1.document.close();
      txtArea1.focus();
      sa=txtArea1.document.execCommand("SaveAs",true,"sa.xls");
      alert(sa);

}

3) create a button to call this function
<input type=button value="Save Method" onClick="fnDoCopy1();">      

4) run the script... voila a saveas window will open, here u can just save the file as <filename.xls>...

5) next when u open this file, u hava an excel sheet ready to be formatted in the way u want.

please inform me if u find a better way to achieve the functionality...

regards
Sandesh
0
 

Author Comment

by:panpanW
ID: 10901460
thanks. I tested your second method, and the data does not get saved. Could you please give me a hand on this codes?

<html>
<iframe id="txtArea1" style="display:none"></iframe>
<script>


function fnDoCopy1()
{
     var tab_text="<table><tr>";
     var textRange;
     tab   = document.getElementById('dataTable');
     for(j = 1 ; j < tab.rows.length ; j++)
     {
          if(tab.rows[j].style.display=="")
          {
               tab_text=tab_text+tab.rows[j].innerHTML;
               tab_text=tab_text+"</tr><tr>";
          }
     }
     tab_text=tab_text+"</tr></table>";
     txtArea1.document.open("txt/html","replace");
     txtArea1.document.write(tab_text);
     txtArea1.document.close();
     txtArea1.focus();
     sa=txtArea1.document.execCommand("SaveAs",true,"sa.xls");
     alert(sa);

}

</script>

<table id='dataTable'>
<tr>
<td>
test
</td>
</tr>
</table>
<input type=button value="Save Method" onClick="fnDoCopy1();">    



</html>
0
 
LVL 6

Accepted Solution

by:
sandeshj earned 50 total points
ID: 10906208
hi panpan

well... there is a small correction that is required, the for-loop should start from 0 not 1, ie if you want to start from line 0.

also the if condition 'if(tab.rows[j].style.display=="")'  is not required if your not playing with DHTML to hide and display tables.

bye.
Sandesh
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

708 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

12 Experts available now in Live!

Get 1:1 Help Now