Solved

Transfering data into excel

Posted on 2003-12-11
17
4,250 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The question appears often enough, how do I transfer my data from my old server to the new server while preserving file shares, share permissions, and NTFS permisions.  Here are my tips for handling such a transfer.
Read about the importance of offline branding in the digital age.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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