Solved

Transfering data into excel

Posted on 2003-12-11
17
4,247 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 60 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 40 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

21 Experts available now in Live!

Get 1:1 Help Now