Hi,
User will select No.Of MessageId's in one
jsp and submit to the next jsp, as per user selected message id's another jsp will create xls sheet(data came from database),kept in one folder at the server and i nedd to download the created excel sheets, i have written download program using response.setheader(); in same jsp
Problem is:
i can able to download only one xls sheet remaining are NOT getting download, i want to estimate the time to download one file and then i can stop the next line of the code , how to download a multiple files in a loop of user selected message id's
Below is the code to creating xls sheet and downloading file written in jsp
String dataArray =request.getParameter("textField");
dataArray =dataArray. replaceAll(","," ");
StringTokenizer st = new StringTokenizer(dataArray);
while (st.hasMoreTokens())
{
dataArray= st.nextToken();
out.print(dataArray);
String cifId=dataArray;
String DM_MSG_DESCR = null;
String DM_MSG_ID = null;
String DM_REM = null;
String MSG_LVL_NO = null;
String ENTRY_MD = null;
int msgLvl=0;
//ArrayList For DM_MSGTBLDTLS
ArrayList Table_Name =new ArrayList();
ArrayList Table_Full_Name =new ArrayList();
ArrayList Table_Description =new ArrayList();
ArrayList Table_Type =new ArrayList();
ArrayList Table_DM_Remarks =new ArrayList();
//ArrayList For DM_msgcoldtls
ArrayList Col_CifID =new ArrayList();
ArrayList Col_TableName =new ArrayList();
ArrayList Col_ColumnName =new ArrayList();
ArrayList Col_ColumnFullName =new ArrayList();
ArrayList Col_DataType =new ArrayList();
ArrayList Col_Null =new ArrayList();
ArrayList Col_ColumnDescription =new ArrayList();
ArrayList Col_FixedDomainValues =new ArrayList();
ArrayList Col_DmRemarks =new ArrayList();
ArrayList Col_ReferenceColumnName =new ArrayList();
ArrayList Col_ReferenceTableName =new ArrayList();
// Object onj = new Object();
try
{
Database objDatabase = new Database();
// connecting Database
Connection conn =objDatabase.getConnection();
PreparedStatement statement = conn.prepareStatement("select * from DM_MSGHDR where DM_MSG_ID= '"+cifId+"'");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
{
DM_MSG_ID= resultSet.getString("DM_MSG_ID");
DM_MSG_DESCR = resultSet.getString("DM_MSG_DESCR");
DM_REM=resultSet.getString("DM_REM");
ENTRY_MD = resultSet.getString("ENTRY_MD");
DM_REM = resultSet.getString("DM_REM");
msgLvl = resultSet.getInt("MSG_LVL_NO");
}
resultSet.close();
conn.close();
Integer iVar = new Integer(msgLvl);
MSG_LVL_NO = iVar.toString();
WritableWorkbook workBook = Workbook.createWorkbook(new File("C:\\Download-back\\"+MSG_LVL_NO+"_"+cifId+".xls"));
WritableSheet sheet = workBook.createSheet("HEADER",0);
WritableSheet sheet1= workBook.createSheet("DETAILS",1);
WritableFont font = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
WritableCellFormat fontFormat = new WritableCellFormat (font);
fontFormat.setBackground(Colour.PALE_BLUE);
fontFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
fontFormat.setOrientation(Orientation.HORIZONTAL);
fontFormat.setWrap(true);
WritableCellFormat fontFormat1 = new WritableCellFormat (font);
fontFormat1.setBackground(Colour.LIGHT_TURQUOISE);
fontFormat1.setBorder(Border.ALL,BorderLineStyle.THIN);
fontFormat1.setOrientation(Orientation.HORIZONTAL);
fontFormat1.setWrap(true);
WritableCellFormat fontFormat2 = new WritableCellFormat (font);
fontFormat2.setBackground(Colour.LAVENDER);
fontFormat2.setBorder(Border.ALL,BorderLineStyle.THIN);
fontFormat2.setOrientation(Orientation.HORIZONTAL);
fontFormat2.setWrap(true);
WritableCellFormat fontFormat3 = new WritableCellFormat (font);
fontFormat3.setBackground(Colour.LIGHT_GREEN);
fontFormat3.setBorder(Border.ALL,BorderLineStyle.THIN);
fontFormat3.setOrientation(Orientation.HORIZONTAL);
fontFormat3.setWrap(true);
WritableFont arialFont = new WritableFont(WritableFont.ARIAL, 8);
WritableCellFormat arialFormat = new WritableCellFormat (arialFont);
arialFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
arialFormat.setOrientation(Orientation.HORIZONTAL);
arialFormat.setWrap(true);
Label label = new Label(0, 0, "CIF ID", fontFormat);
sheet.addCell(label);
Label label_CifId = new Label(1, 0 , DM_MSG_ID, arialFormat);
sheet.addCell(label_CifId);
sheet.setColumnView(0,20);
sheet.setColumnView(1,40);
sheet.setColumnView(2,30);
sheet.setColumnView(3,30);
sheet.setColumnView(4,30);
sheet.setRowView(1,1500, false);
Label label1 = new Label(0, 1, "CIF Description",fontFormat);
sheet.addCell(label1);
Label label_DM_MSG_DESCR = new Label(1, 1 , DM_MSG_DESCR, arialFormat);
sheet.addCell(label_DM_MSG_DESCR);
Label label3 =new Label(0, 2, "CIF DM Remarks",fontFormat);
sheet.addCell(label3);
Label label_DM_REM = new Label(1, 2 , DM_REM,arialFormat);
sheet.addCell(label_DM_REM);
Label label4 =new Label(2 , 0,"CIF Type", fontFormat);
sheet.addCell(label4);
Label label_ENTRY_MD = new Label(3 , 0 , ENTRY_MD, arialFormat);
sheet.addCell(label_ENTRY_MD);
Label label5 = new Label(2, 1, "Sequence #", fontFormat);
sheet.addCell(label5);
Label label_Sequence = new Label(3, 1, MSG_LVL_NO, arialFormat );
sheet.addCell(label_Sequence);
Label label6 = new Label(0, 4, "Table Name", fontFormat1);
sheet.addCell(label6);
Label label7 = new Label(1, 4, "Table Full Name", fontFormat1);
sheet.addCell(label7);
Label label8 = new Label(2, 4, "Table Description", fontFormat1);
sheet.addCell(label8);
Label label9 = new Label(3, 4, "Table Type", fontFormat1);
sheet.addCell(label9);
Label label10 = new Label(4, 4, "Table DM Remarks", fontFormat1);
sheet.addCell(label10);
//Adding Label in DETAIL Sheet
Label label11 = new Label(0, 0, "CIF Id", fontFormat1);
sheet1.addCell(label11);
Label label12 = new Label(1, 0, "Table Name", fontFormat1);
sheet1.addCell(label12);
Label label13 = new Label(2, 0, "Column Name", fontFormat1);
sheet1.addCell(label13);
Label label14 = new Label(3, 0, "Column Full Name", fontFormat1);
sheet1.addCell(label14);
Label label15 = new Label(4, 0, "Data Types", fontFormat1);
sheet1.addCell(label15);
Label label16 = new Label(5, 0, "Null ?", fontFormat1);
sheet1.addCell(label16);
Label label17 = new Label(6, 0, "Column Description", fontFormat1);
sheet1.addCell(label17);
Label label18 = new Label(7, 0, "Fixed Domain Values", fontFormat1);
sheet1.addCell(label18);
Label label19 = new Label(8, 0, "DM Remarks", fontFormat1);
sheet1.addCell(label19);
Label label20 = new Label(9, 0, "Reference Column Name", fontFormat1);
sheet1.addCell(label20);
Label label21 = new Label(10, 0, "Reference Table Name", fontFormat1);
sheet1.addCell(label21);
sheet1.setColumnView(0,15);
sheet1.setColumnView(1,30);
sheet1.setColumnView(2,15);
sheet1.setColumnView(3,15);
sheet1.setColumnView(4,15);
sheet1.setColumnView(5,15);
sheet1.setColumnView(6,30);
sheet1.setColumnView(7,15);
sheet1.setColumnView(8,15);
sheet1.setColumnView(9,15);
sheet1.setColumnView(10,15);
// Connecting to the DM_MSGTBLDTLS
try
{
Connection connTbls =objDatabase.getConnection();
PreparedStatement statementTbls = connTbls.prepareStatement("select * from DM_MSGTBLDTLS where DM_MSG_ID= '"+DM_MSG_ID+"' order by TBL_LVL_NO");
ResultSet resultSetTbls = statementTbls.executeQuery();
//Feching Data from Resultset
while (resultSetTbls.next())
{
String TBL_NM= resultSetTbls.getString("TBL_NM");
String TBL_FULL_NM = resultSetTbls.getString("TBL_FULL_NM");
String TBL_DESCR = resultSetTbls.getString("TBL_DESCR");
String TBL_TYPE = resultSetTbls.getString("TBL_TYPE");
String TBL_DM_REM = resultSetTbls.getString("DM_REM");
//Adding to Array List
Table_Name.add(TBL_NM);
Table_Full_Name.add(TBL_FULL_NM);
Table_Description.add(TBL_DESCR);
Table_Type.add(TBL_TYPE);
Table_DM_Remarks.add(TBL_DM_REM);
}
resultSetTbls.close();
connTbls.close();
}catch(Exception f)
{
f.printStackTrace();
}
//Creating Label andding Label and Format
//Getting Data From ArrayList
int x=5;
for(int i=0;i<Table_Name.size();i++)
{
Object Obj = Table_Name.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(0, x, str, arialFormat);
sheet.addCell(label_tbl);
// sheet.setRowView(x,1000,false);
x=x+1;
}
Label label_tab = new Label(0, x, "", fontFormat2);
sheet.addCell(label_tab);
x=5;
for(int i=0;i<Table_Full_Name.size();i++)
{
String str = null;
Object Obj = Table_Full_Name.get(i);
//String str = Obj.toString();
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_Table_Full = new Label(1, x, str, arialFormat);
sheet.addCell(label_Table_Full);
x++;
}
Label label_Table_Full1 = new Label(1, x, "", fontFormat2);
sheet.addCell(label_Table_Full1);
x=5;
for(int i=0;i<Table_Description.size();i++)
{
String str = null;
Object Obj = Table_Description.get(i);
//String str =Obj.toString();
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_Table_des = new Label(2, x, str, arialFormat);
sheet.addCell(label_Table_des);
x++;
}
Label label_Table_Des1 = new Label(2, x, "", fontFormat2);
sheet.addCell(label_Table_Des1);
x=5;
for(int i=0;i<Table_Type.size();i++)
{
String str = null;
Object Obj = Table_Type.get(i);
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_Table_Type = new Label(3, x, str, arialFormat);
sheet.addCell(label_Table_Type);
x++;
}
Label label_Table_Type1 = new Label(3, x, "", fontFormat2);
sheet.addCell(label_Table_Type1);
x=5;
for(int i=0;i<Table_DM_Remarks.size();i++)
{
String str=null;
Object Obj = Table_DM_Remarks.get(i);
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_Table_DM = new Label(4, x, str, arialFormat);
sheet.addCell(label_Table_DM);
x++;
}
Label label_Table_DM1 = new Label(4, x, "", fontFormat2);
sheet.addCell(label_Table_DM1);
//DataBase Connection For DM_MSGCOLDTLS
try
{
Connection connColdts =objDatabase.getConnection();
// PreparedStatement statementColdts = connColdts.prepareStatement("select * from DM_MSGCOLDTLS where DM_MSG_ID= '"+DM_MSG_ID+"' and HIDE_FLG='N' order By col_seq_no ");
PreparedStatement statementColdts = connColdts.prepareStatement("select col.* from DM_MSGTBLDTLS tbl, dm_msgcoldtls col where col.DM_MSG_ID='"+DM_MSG_ID+"'and col.HIDE_FLG='N' and tbl.dm_msg_id = col.dm_msg_id and tbl.tbl_nm = col.tbl_nm order by tbl.tbl_lvl_no, col.col_seq_no");
ResultSet resultSetColdts = statementColdts.executeQuery();
//Feching Data from Resultset
while(resultSetColdts.next())
{
String Col_CIf= resultSetColdts.getString("DM_MSG_ID");
String Col_Table_name = resultSetColdts.getString("TBL_NM");
String Col_Name = resultSetColdts.getString("COL_NM");
String Col_Full_Name = resultSetColdts.getString("COL_FULL_NM");
String Col_Data_Type = resultSetColdts.getString("DATA_TYPE");
String Col_Null_Ind= resultSetColdts.getString("NULL_IND");
String Col_Desc = resultSetColdts.getString("COL_DESCR");
String Col_Fixed = resultSetColdts.getString("VALD_VAL");
String Col_Dm_Remarks = resultSetColdts.getString("DM_REM");
String Col_Reference = resultSetColdts.getString("REF_COL_NM");
String Col_Table_Name = resultSetColdts.getString("REF_TBL_NM");
//Adding to the ArrayList
Col_CifID.add(Col_CIf);
Col_TableName.add(Col_Table_name);
Col_ColumnName.add(Col_Name);
Col_ColumnFullName.add(Col_Full_Name);
Col_DataType.add(Col_Data_Type);
Col_Null.add(Col_Null_Ind);
Col_ColumnDescription.add(Col_Desc);
Col_FixedDomainValues.add(Col_Fixed);
Col_DmRemarks.add(Col_Dm_Remarks);
Col_ReferenceColumnName.add(Col_Reference);
Col_ReferenceTableName.add(Col_Table_Name);
}
//Forloop of DM_MSGCOLDTLS
x=1;
for(int i=0;i<Col_CifID.size();i++)
{
Object Obj = Col_CifID.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(0, x, str, arialFormat);
sheet1.addCell(label_tbl);
// sheet1.setRowView(x,700,false);
x=x+1;
}
Label label_tbl1 = new Label(0, x, "", fontFormat2);
sheet1.addCell(label_tbl1);
x=1;
for(int i=0;i<Col_ColumnName.size();i++)
{
Object Obj = Col_ColumnName.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(2, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl3 = new Label(2, x, "", fontFormat2);
sheet1.addCell(label_tbl3);
x=1;
for(int i=0;i<Col_ColumnFullName.size();i++)
{
Object Obj = Col_ColumnFullName.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(3, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl4 = new Label(3, x, "", fontFormat2);
sheet1.addCell(label_tbl4);
x=1;
for(int i=0;i<Col_DataType.size();i++)
{
Object Obj = Col_DataType.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(4, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl5 = new Label(4, x, "", fontFormat2);
sheet1.addCell(label_tbl5);
x=1;
for(int i=0;i<Col_Null.size();i++)
{
Object Obj = Col_Null.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(5, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl6 = new Label(5, x, "", fontFormat2);
sheet1.addCell(label_tbl6);
x=1;
for(int i=0;i<Col_ColumnDescription.size();i++)
{
Object Obj = Col_ColumnDescription.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(6, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl7 = new Label(6, x, "", fontFormat2);
sheet1.addCell(label_tbl7);
x=1;
for(int i=0;i<Col_FixedDomainValues.size();i++)
{
Object Obj = Col_FixedDomainValues.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(7, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl8 = new Label(7, x, "", fontFormat2);
sheet1.addCell(label_tbl8);
x=1;
for(int i=0;i<Col_DmRemarks.size();i++)
{
Object Obj = Col_DmRemarks.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(8, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl9 = new Label(8, x, "", fontFormat2);
sheet1.addCell(label_tbl9);
x=1;
for(int i=0;i<Col_ReferenceColumnName.size();i++)
{
Object Obj = Col_ReferenceColumnName.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(9, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl10 = new Label(9, x, "", fontFormat2);
sheet1.addCell(label_tbl10);
x=1;
for(int i=0;i<Col_ReferenceTableName.size();i++)
{
Object Obj = Col_ReferenceTableName.get(i);
String str = null;
if(Obj==null)
{
str="";
}
else
{
str = Obj.toString();
str=str.trim();
}
Label label_tbl = new Label(10, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
}
Label label_tbl11 = new Label(10, x, "", fontFormat2);
sheet1.addCell(label_tbl11);
x=1;
int y=1;
for(int i=0;i<Col_TableName.size();i++)
{
Object Obj =Col_TableName.get(i);
String str = Obj.toString();
str=str.trim();
Label label_tbl = new Label(1, x, str, arialFormat);
sheet1.addCell(label_tbl);
x=x+1;
if(y<Col_TableName.size())
{
if(!(Col_TableName.get(i)).equals(Col_TableName.get(y)))
{
sheet1.insertRow(x);
Label label_tbl2 = new Label(0, x, "", fontFormat3);
sheet1.addCell(label_tbl2);
Label label_table1 = new Label(1, x, "", fontFormat3);
sheet1.addCell(label_table1);
Label label_table2 = new Label(2, x, "", fontFormat3);
sheet1.addCell(label_table2);
Label label_table3 = new Label(3, x, "", fontFormat3);
sheet1.addCell(label_table3);
Label label_table4 = new Label(4, x, "", fontFormat3);
sheet1.addCell(label_table4);
Label label_table5 = new Label(5, x, "", fontFormat3);
sheet1.addCell(label_table5);
Label label_table6 = new Label(6, x, "", fontFormat3);
sheet1.addCell(label_table6);
Label label_table7 = new Label(7, x, "", fontFormat3);
sheet1.addCell(label_table7);
Label label_table8 = new Label(8, x, "", fontFormat3);
sheet1.addCell(label_table8);
Label label_table9 = new Label(9, x, "", fontFormat2);
sheet1.addCell(label_table9);
Label label_table10 = new Label(10, x, "", fontFormat2);
sheet1.addCell(label_table10);
x=x+1;
}
y++;
}
}
Label label_tbl2 = new Label(1, x, "", fontFormat2);
sheet1.addCell(label_tbl2);
}catch(Exception r) {r.printStackTrace(); }
workBook.write();
workBook.close();
System.out.println("Datata Array "+dataArray);
// java.net.URL url=new java.net.URL("file://./"+"Download-back/"+MSG_LVL_NO+"_"+cifId+".xls");
response.setContentType( "application/vnd.ms-excel" );
// response.setHeader("Content-Disposition","inline; filename=\""+MSG_LVL_NO+"_"+cifId+".xls\"");
response.setHeader("Content-Disposition","attachment; filename=\""+MSG_LVL_NO+"_"+cifId+".xls\"");
response.setHeader("cache-control", "no-cache");
//response.setHeader("Location","c:\\file\\"+MSG_LVL_NO+"_"+cifId+".xls\"");
response.setHeader("Pragma","no-cache");
java.net.URL url=new java.net.URL("file://./"+"Download-back/"+MSG_LVL_NO+"_"+cifId+".xls");
File file=new File(url.getPath());
System.out.println("Path:"+file.getPath());
System.out.print("file exist:"+file.exists());
java.io.FileInputStream fileInputStream =new java.io.FileInputStream(file);
BufferedInputStream bin=new BufferedInputStream(fileInputStream);
int i;
byte[] b=new byte[10];
ServletOutputStream sosStream= response.getOutputStream();
while ((i=bin.read())!=-1)
{
sosStream.write(i);;
}
sosStream.flush();
sosStream.close();
bin.close();
fileInputStream.close();
}
}catch(Exception e){)
[ July 19, 2005: Message edited by: anilellendula kumar ]
