Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transfering data into excel

Posted on 2003-12-11
17
Medium Priority
?
4,264 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month5 days, 20 hours left to enroll

773 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