Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transfering data into excel

Posted on 2003-12-11
17
Medium Priority
?
4,261 Views
Last Modified: 2007-12-19
Hi,
I want some help in  exporting a HTML page to excel format.My JSP contains charts and table data but I want only the table data to be exported on the click of a button.
Can someone help me in this.
Thanks
0
Comment
Question by:priyakrishna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 1

Expert Comment

by:yapsing
ID: 9926430
0
 

Author Comment

by:priyakrishna
ID: 9926474
I cant use anyof the third party  tools I read that I have to use response.setContentType("application/vnd.ms-excel"). but this opens the html file in Excel format with all the jsp code.But I want to save the table data(values/column labels) on the click of the button.
Kindly help me.
0
 
LVL 1

Expert Comment

by:yapsing
ID: 9926475
or u can open your page as an excel work sheet just by adding the following:
<%@ page contentType="application/vnd.ms-excel" %>
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9926509
Well if your code contains table as well as charts etc... you will have to use client side script to move to a excel sheet.
Code will look something like this:
function viewExcel()
{
 var xls = new ActiveXObject ( "Excel.Application" );

 xls.visible = true;

 var newBook = xls.Workbooks.Add

 newBook.Worksheets.Add

 newBook.Worksheets(1).Activate

 newBook.Worksheets(1).Name="whatever";

 xls.ActiveWorkBook.ActiveSheet.PageSetup.Orientation = 2;
 xls.ActiveWorkBook.ActiveSheet.PageSetup.PaperSize = 5;

 newBook.Worksheets(1).Range("F1:I1000").NumberFormat = "$.00";
 newBook.Worksheets(1).Range("A1:I1000").HorizontalAlignment = -4131;

 newBook.Worksheets(1).Columns("A").columnwidth=10;
 newBook.Worksheets(1).Columns("A").WrapText = true;
 newBook.Worksheets(1).Columns("B").columnwidth=10;
 newBook.Worksheets(1).Columns("B").WrapText = true;
 newBook.Worksheets(1).Columns("C").columnwidth=5;
 newBook.Worksheets(1).Columns("C").WrapText = true;
 newBook.Worksheets(1).Columns("D").columnwidth=10;
 newBook.Worksheets(1).Columns("D").WrapText = true;
 newBook.Worksheets(1).Columns("E").columnwidth=5;
 newBook.Worksheets(1).Columns("E").WrapText = true;
 newBook.Worksheets(1).Columns("F").columnwidth=15;
 newBook.Worksheets(1).Columns("F").WrapText = true;
 newBook.Worksheets(1).Columns("G").columnwidth=15;
 newBook.Worksheets(1).Columns("G").WrapText = true;
 newBook.Worksheets(1).Columns("H").columnwidth=15;
 newBook.Worksheets(1).Columns("H").WrapText = true;
 newBook.Worksheets(1).Columns("I").columnwidth=15;
 newBook.Worksheets(1).Columns("I").WrapText = true;
 newBook.Worksheets(1).Cells(1,1).Interior.ColorIndex="15";
 newBook.Worksheets(1).Cells(1,1).value="Activity Date";
 newBook.Worksheets(1).Cells(1,2).value="Trans Eff Date";
 newBook.Worksheets(1).Cells(1,3).value="Inst";
 newBook.Worksheets(1).Cells(1,4).value="Type Activity";
 newBook.Worksheets(1).Cells(1,5).value="Trans Code";
 newBook.Worksheets(1).Cells(1,6).value="Policy Premium Amt";
 newBook.Worksheets(1).Cells(1,7).value="Installment Premium Amt";
 newBook.Worksheets(1).Cells(1,8).value="Billed Amt";
 newBook.Worksheets(1).Cells(1,9).value="Inst Pd Amt";
//This all is dependent on you if you want to set them up or not
        for (int i = 2; i <= yourtable.rows.length();i++)
 {
  for (int r = 1; r <= yourtable.rows[0].cells.length();; r++)
  {
   String excelData = excelArray[r][i];
   newBook.Worksheets(1).Cells(i,r).value=yourtable.rows[i].cells[r].innerText;
  }
 }
 //xls.ActiveWorkbook.Save (); // Do this if the user can just exit and you want to keep in saved.



window.close();

}

Here I am not taking any gaurntee on syntax correctness but the idea should work.






0
 

Author Comment

by:priyakrishna
ID: 9926742
Thanx but I have to dynamically get the data  that is displayed on the report into the Excel file and
var xls = new ActiveXObject ( "Excel.Application" ); - doesnt do anything  in my system
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9926850
as far as dynamic nature goes, that should not be a problem, I have coded it to be simple but you can make all header informations to be dynamic instead of hardcoded as they are in my sample script....
as far as new ActiveObject not doing anything..... check your IE settings to see if it is allowing to run activex components ...
0
 

Author Comment

by:priyakrishna
ID: 9927242
thats the whole issue...I  work in linux and I dont have IE...installed in linux OS
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9927370
then why you are trying to exort it to something that is microsoft?????????
0
 

Author Comment

by:priyakrishna
ID: 9927436
we are using openoffice so excel stuff works with OpenOffice also...So  both wil be the same
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9927521
well then your solution will have to be a server side solution.........

try doing following...

break your HTML page in iframes... and then put your table in one of the iframe... you can set the response.contentType("applciation/vnd.ms-excel")  and put rest of the stuff in another iframe...
this will require you to break your jsp into two different jsps which will be called as frame locations for your framed HTML...

thats the only way I can think of as of now...
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9927614
You just need another JSP, very similar to the one you already have, but only including the table.  When the user clicks the button, open this page and the content type of application/vnd.ms-excel should do the trick.

Alternatively, you might be able to modify the existing JSP.  You would just need to add some tests around the charts (and the button) to see if the page was called using the button itself.  If it was, don't include the charts.
0
 

Author Comment

by:priyakrishna
ID: 9927832
but if I use this  response.contentType("applciation/vnd.ms-excel")  its automatically opening in the  Excel file and one more thing is its opening the html data but what I want is thevalues/columns  taht are in the table.I'm able to open the  jsp file in excel format but it opens in the browser itslef...instead I want it on the click of a button when I kept this piece of code on the click of button nothing is happening...
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 180 total points
ID: 9927873
what you are doing on the click of that button ?/
as per jimmack suggestion this is what you can do...

inside the jsp... when the button is clicked

<%
 //this can be checked by checking the request.getParameter("buttonName") and seeing if it comes out and not null
if (button clicked)
{
response.setContent("application/vnd-ms-excel")
//code which creates table only and no charts and anything else
}
else
{
//whole jsp code as it should be appearing in normal cases...
}

your button should be of type submit.
Hope this helps
0
 
LVL 15

Assisted Solution

by:jimmack
jimmack earned 120 total points
ID: 9927913
Yup.  That's exactly what I meant ;-)  Thanks Kuldeepchaturvedi :-)
0
 

Author Comment

by:priyakrishna
ID: 9928373
hey I am doing the same but nothing is happening
      String test1=request.getParameter("test");
      if(test1!=null)
      {
            response.setContentType("application/vnd.ms-excel");//its  coming here.
      }
      else
      {
            System.out.println("hey there");
      }
      %>
      <input type="button" name="ty" onClick="javascript:viewExcel()">
      <input type="hidden" name="test" value="<%=test1%>">
do I need to pass any gfilename or anything
0
 

Expert Comment

by:ZOOMAY
ID: 9984221
Hey Sorry I wasnt working on this...actually whatever u have said is right..but if I write any java code in between the <TABLE> tag its not workig..but anyways as u said I have called a seperate JSP wherein I have written

<HTML>
<HEAD>
<TITLE>Testing</TITLE>
</HEAD>
<BODY>
<CENTER>
<H2>Test</H2>
<TABLE BORDER=1>
<TR><TH></TH><TH>Java<TH>23
<TR><TH>C++<TD>33<TD>33
<TR><TH>C<TD>33<TD>33
<TR><TH>XML<TD>33<TD>33
</TABLE>
</CENTER>
<%
String format ="test";
if ((format != null) && (format.equals("test")))
{
      response.setContentType("application/vnd.ms-excel");
      response.setHeader("Content-disposition", "attachment; filename=export.xls");
}
%>
</BODY>
</HTML>

ist working fine..Thanx for the help..
0
 

Author Comment

by:priyakrishna
ID: 9984240
Sorry Zommay as I have alreday finished doing this I cant award points to you.Thanks a lot guys.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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