Transfering data into excel

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
priyakrishnaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yapsingCommented:
0
priyakrishnaAuthor Commented:
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
yapsingCommented:
or u can open your page as an excel work sheet just by adding the following:
<%@ page contentType="application/vnd.ms-excel" %>
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

KuldeepchaturvediCommented:
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
priyakrishnaAuthor Commented:
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
KuldeepchaturvediCommented:
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
priyakrishnaAuthor Commented:
thats the whole issue...I  work in linux and I dont have IE...installed in linux OS
0
KuldeepchaturvediCommented:
then why you are trying to exort it to something that is microsoft?????????
0
priyakrishnaAuthor Commented:
we are using openoffice so excel stuff works with OpenOffice also...So  both wil be the same
0
KuldeepchaturvediCommented:
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
jimmackCommented:
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
priyakrishnaAuthor Commented:
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
KuldeepchaturvediCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimmackCommented:
Yup.  That's exactly what I meant ;-)  Thanks Kuldeepchaturvedi :-)
0
priyakrishnaAuthor Commented:
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
ZOOMAYCommented:
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
priyakrishnaAuthor Commented:
Sorry Zommay as I have alreday finished doing this I cant award points to you.Thanks a lot guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.