Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

How do i open an excel application with the data ported to it using a servlet program ?

  Asked By: Leon    Date: Jun 07    Category: Java    Views: 912
  

How do i open an excel application with the data ported to it using
a servlet program ? I am using MySql as Database and Tomcat as
server. I am Developing a web application wherein i want to generate
reports in excel sheet.

Pls help me ?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jimmie Ramirez     Answered On: Jun 07

I hope this will help  u...

ExcelFunctions.js

function BuildSheet(book,module, tariffcategory, calcbasis,serviceadjcalc)
{


if(module=='supplier1' || module=='supplier2')
{
sheetcnt =2;
base=book.Sheets("sheet1");
base.Name="Base";

service=book.Sheets("sheet2");
service.Name="Service";


if(module=='supplier1')
{
//add other sheets only if it is agent tariff

closed1=book.Sheets("Sheet3");
closed1.Name="Closed";

mins=book.Worksheets.add();
mins.Name="Mins";

adhoc=book.Worksheets.add();
adhoc.Name="Ad-hoc";

special=book.Worksheets.add();
special.Name="Special";
//arrange the sheets in required order. move() moves the parameter
sheet to just before sheet  on which the fn is called
closed1.Move(special);
service.Move(closed1);
sheetcnt=book.Worksheets.Count;
}
base.Move(service);
}
else
{
job=book.Worksheets.add();
job.Name="Job";

mins=book.Worksheets.add();
mins.Name="Mins";

plev=book.Worksheets.add();
plev.Name="P-Lev";


ptype=book.Worksheets.add();
ptype.Name="P-Type";

adhoc=book.Worksheets.add();
adhoc.Name="Ad-hoc";

special=book.Worksheets.add();
special.Name="Special";

closed1=book.Worksheets.add();
closed1.Name="Closed";

service=book.Sheets("sheet2");
service.Name="Service";

otype=book.Sheets("sheet3");
otype.Name="O-Type";

base=book.Sheets("sheet1");
base.Name="Base";
sheetcnt = book.Worksheets.Count;

otype.Move(closed1);
service.Move(otype);
base.Move(service);

}
for( i=1;i<=sheetcnt;i++)
{
book.sheets(i).range("A1").columnwidth=18;
book.sheets(i).range("B1").columnwidth=6;
book.sheets(i).range("c1").columnwidth=16;
book.sheets(i).range("d1").columnwidth=14;
book.sheets(i).range("f1").columnwidth=14;
book.sheets(i).range("e1").columnwidth=13;

book.sheets(i).range("A4","l5").borderaround(1,3,1,2);

if(module=='customer')
{
book.sheets(i).range("A1")="Customer";
}
else if ( module =='subby' || module == 'supplier' )
{
book.sheets(i).range("A1")="Supplier";
}

book.sheets(i).range("A2")="Charging Method";
book.sheets(i).range("A3")="Cube/Weight";
book.sheets(i).range("A4")="From";
book.sheets(i).range("A5")="To";
book.sheets(i).range("H1")="Effective:";
book.sheets(i).range("H2")="Depot";

book.sheets(i).range("j4")="Charges";
book.sheets(i).range("i5")="Base";
book.sheets(i).range("j5")="+/-";
book.sheets(i).range("k5")="Per";
book.sheets(i).range("l5")="Min";


if(book.sheets(i).name=="Base")
{
if(tariffcategory=='tonmile')
{
book.sheets(i).range("b5")="Radial Mile Band(s)";
}
else
{
book.sheets(i).range("f4")="Collections";
book.sheets(i).range("f5")="Deliveries";
}

if ( !(tariffcategory == 'tonmile' && calcbasis=='2') )
{//Except for tonmile calcbasis '1' tariffs
book.sheets(i).range("E4")="Unit Of";
book.sheets(i).range("e5")="Charge";
}
book.sheets(i).range("g4")="Charge Band(s)";
book.sheets(i).range("g5")="From";
book.sheets(i).range("h5")="To";
book.sheets(i).range("j5")="Plus";
}
else if(book.sheets(i).name=="Service")
{
book.sheets(i).range("b5")="Service Level";
book.sheets(i).range("E4")="Unit Of";
book.sheets(i).range("e5")="Charge";
if(serviceadjcalc=='1')
{
book.sheets(i).range("E4")=" ";
book.sheets(i).range("e5")=" ";
}
book.sheets(i).range("f4")="Collections";
book.sheets(i).range("f5")="Deliveries";
book.sheets(i).range("g4")="Quantity Band(s)";
book.sheets(i).range("g5")="From";
book.sheets(i).range("h5")="To";
}
else if(book.sheets(i).name=="O-Type")
{
book.sheets(i).range("E4")="Unit Of";
book.sheets(i).range("e5")="Charge";
book.sheets(i).range("g4")="Quantity Band(s)";
book.sheets(i).range("b5")="Order Type";
book.sheets(i).range("g5")="From";
book.sheets(i).range("h5")="To";
}
else if(book.sheets(i).name=="closed")
{
book.sheets(i).range("b5")="Closed";
}
else if(book.sheets(i).name=="Special")
{
book.sheets(i).range("b5")="Special Requirements";
book.sheets(i).range("g5")="From";
book.sheets(i).range("h5")="To";
book.sheets(i).range("g4")="Quantity Band(s)";
}
else if(book.sheets(i).name=="Ad-hoc")
{
book.sheets(i).range("c5")="Reason";
}
else if(book.sheets(i).name=="P-Type")
{
book.sheets(i).range("b5")="Product Type";
}
else if(book.sheets(i).name=="P-Code")
{
book.sheets(i).range("b5")="Product Code";
}
else if(book.sheets(i).name=="P-Lev")
{
book.sheets(i).range("E4")="Unit Of";
book.sheets(i).range("e5")="Charge";
book.sheets(i).range("g4")="Quantity Band(s)";
book.sheets(i).range("b5")="Packing Level";
book.sheets(i).range("g5")="From";
book.sheets(i).range("h5")="To";
}
else if(book.sheets(i).name=="Mins")
{
book.sheets(i).range("b5")="Service Level";
book.sheets(i).range("i5")="";
}
else if(book.sheets(i).name=="Job")
{
book.sheets(i).range("i5")="";
}
}
}




function FromExcel(module,targetpage,tariffcategory, aspid ,customer_id,
from_area,depotid,charge_structure,effectivedate)
{

if(document.tariff.file.value=="")
{
alert("Enter the file to be uploaded");
}
else
{
var oxcel = new ActiveXObject("Excel.Application");
var book = oxcel.Workbooks.add(document.tariff.file.value);
document.writeln("<html><head>")
document.writeln("</head>")
document.writeln("<body><form name='tariff' action='" + targetpage + "'
method='post' >");
for( i=1;i<=book.Worksheets.Count;i++)
{
document.write("<input type='Hidden' name='"+book.sheets(i).Name+"'
value='");
var values="";
var rowflag=true;
var rowval="";
var row=6;
var blank=0;

while(rowflag && blank<3)
{
var values="";
for(k=1;k<=18;k++)
//this loop goes thru every cell for row and sets some flags used to
decide whether to process the row or not
{
var tempval= book.sheets(i).cells(row,k).value;//read cell by cell
var blaflag=false;
var flag=false;
if(tempval==null)
{
tempval="";
}
//Ignore only service-area rows. Ignore empty rows upto 3 and then
terminate
if(k==1)
{ //if it is first cell
tempval = ""+tempval;
if(tempval.indexOf("***")!=-1)
{
rowflag=false;//if it is sheet terminator ***
break;
}
if(tempval!="")
{
flag=true;//has some data  other than ***
}
}
else if(tempval!="")
{
blaflag=true;//even if one cell has some data, set flag and continue
break;
}
}
if(!flag && !blaflag)//it is blank row
{
blank++;
continue;
}
if(flag && !blaflag)//it is service-area row
{
continue;
}

for(j=1;j<=18;j++)
{
var cellval= book.sheets(i).cells(row,j).value;
if(cellval==null)
{
cellval="";
}
values=values+cellval+",";
}//for
rowval=rowval+"@"+ row+"???"+values;
row++;
}//while
document.write(rowval.substring(1)+"'>");
} //for

document.write("<script
language='javascript'>document.tariff.submit();<\/script>");
oxcel.workbooks(1).close();
document.write("</form></body></html>");
document.close();
}
}//fun

EXCEL.JSP

<html>
<head>
<SCRIPT LANGUAGE="JavaScript" src="ExcelFunctions.js"></script>
<script language="javascript" >

var oExcel = new ActiveXObject("Excel.Application");
function ExportarExcel()
{
var book = oExcel.Workbooks.add();
//Creates Worksheet and populates it with static header data
<%

String searchid="";
if(tariffcategory==null)
tariffcategory="normal";
if(module==null)
module="customer";
//To store names of all sheets in Properties object
Properties p=new Properties();
p.put("10","base");
p.put("20","service");
if( module.equalsIgnoreCase("supplier") )
{
depotid=tarifftype;
if( tarifftype.equalsIgnoreCase("1") )
{
p.put("21","closed1");
p.put("26","special");
p.put("27","adhoc");
p.put("30","mins");
}
}
else
{
p.put("21","closed1");
p.put("23","ptype");
p.put("24","plev");
p.put("25","otype");
p.put("26","special");
p.put("27","adhoc");
p.put("30","mins");
p.put("40","job");
}

if(module.equalsIgnoreCase("supplier"))
{
%>


BuildSheet(book,'<%=module+tarifftype%>','<%=tariffcategory%>','<%=results.g
etProperty("calcbasis_code")%>','<%=results.getProperty("service_adj_calc")%
>');
<%
for( i=1;i<=book.Worksheets.Count;i++)
{


book.sheets(i).range("b1")="<%=((results.getProperty("search_name")==null)?"
":results.getProperty("search_name"))%>";

book.sheets(i).range("b2")="<%=results.getProperty("chargestructure")%>"+"(<
%=results.getProperty("calcbasis")%>)" ;
book.sheets(i).range("b3")="<%=results.getProperty("cube")
%>:<%=results.getProperty("weight") %>";
book.sheets(i).range("i2")="<%=results.getProperty("depot_name")%>" ;
book.sheets(i).range("b4")=" <%=results.getProperty("from_area_name")%>
";
book.sheets(i).range("I1")="<%=effe_date %>";
}
<%
//loop thru sheet names and populate details if any
String currdate=results.getProperty("currdate");
//Hashtable htcodei = getCodeI( aspid, currdate);

Enumeration e=p.keys();
while( e.hasMoreElements() )//Loop for all sheets
{
ResultSet rs=stmt.executeQuery("Select * from tab");
%>
rownum=6;
<%
while(rs.next())
{
%>
<%=p.getProperty(val)%>.cells(rownum,1)="<%=rs.getString(1) %>";
<%=p.getProperty(val)%>.cells(rownum,2)="<%=rs.getString(2) %>";
<%=p.getProperty(val)%>.cells(rownum,3)="<%=rs.getString(3) %>";
<%=p.getProperty(val)%>.cells(rownum,4)="<%=rs.getString(4) %>";
<%=p.getProperty(val)%>.cells(rownum,5)="<%=rs.getString(5) %>";
rownum=rownum+1;
<%
}
}
%>
oExcel.Visible = true;// Gets the Excel Object and actives it
oExcel.UserControl =true; //Let the user the control of the application
}

</script>
</head>
<link rel="stylesheet" href="../Stylesheet/fonts.css" type="text/css">
<body class="pagebody" onload=ExportarExcel();>
<FORM name="tariff">

</form>
</body>
</html>

 




Tagged: