package com.zyom.struts.ats.action;
import java.io.File;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import jxl.Workbook;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DownloadAction;
import org.apache.struts.actions.DownloadAction.ResourceStreamInfo;
import org.apache.struts.actions.DownloadAction.StreamInfo;
import com.zyom.reuse.util.DateHandler;
import com.zyom.struts.form.CmForm;
import com.zyom.struts.form.DemandFGForm;
import com.zyom.webclient.util.DBHelper;
import com.zyom.webclient.util.AtsHelper;
import com.zyom.webclient.util.DemandSupplyItem;
import com.zyom.webclient.util.MozartCostUtils;
import com.zyom.struts.form.MapCustomerForm;
public class RegionCustUserMapXlAction extends DownloadAction {
protected StreamInfo getStreamInfo(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
Date currentDatetime = new Date(System.currentTimeMillis());
SimpleDateFormat formatter = new SimpleDateFormat("MM-dd-yyyy-HH-mm");
String myDate = formatter.format(currentDatetime);
String myDate1 = formatter.format(currentDatetime);
String fileName = "RegCustUsrMap_"+myDate+".xls";
String contentType = "application/vnd.ms-excel";
ServletContext servletCtx = this.servlet.getServletContext();
try{
String path = servletCtx.getRealPath(fileName);
WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
HttpSession session = request.getSession(true);
List cust_list=new ArrayList();
cust_list=(ArrayList)session.getAttribute("cust_list");
jxl.write.Number num;
Calendar stDate = Calendar.getInstance();
Calendar endDate = Calendar.getInstance();
String temp = "";
SimpleDateFormat formatter1 = new SimpleDateFormat("MMM-dd,yyyy");
WritableSheet sheet = workbook.createSheet("RegCustUsrMap_"+myDate1, 1);
WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat arial10format = new WritableCellFormat (arial10font);
// sheet.mergeCells(0,0,2,0);
WritableFont times16font = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, true);
WritableCellFormat times16format = new WritableCellFormat (times16font);
times16format.setAlignment(jxl.format.Alignment.CENTRE);
// times16format.setBackground(Colour.BLUE);
times16format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM,Colour.BLACK);
WritableFont datafont = new WritableFont(WritableFont.ARIAL , 10);
WritableFont datafont1 = new WritableFont(WritableFont.ARIAL , 10);
WritableFont datafont2 = new WritableFont(WritableFont.ARIAL , 10);
datafont1.setColour(Colour.BLUE);
datafont2.setColour(Colour.RED);
datafont.setColour(Colour.BLACK);
WritableCellFormat dataformat = new WritableCellFormat (datafont2);
WritableCellFormat dataformatApproved = new WritableCellFormat (datafont);
WritableCellFormat dataformatnotApproved = new WritableCellFormat (datafont1);
WritableCellFormat dataformatApproved1 = new WritableCellFormat (datafont);
dataformat.setAlignment(jxl.format.Alignment.LEFT);
dataformatApproved.setAlignment(jxl.format.Alignment.LEFT);
dataformatnotApproved.setAlignment(jxl.format.Alignment.LEFT);
dataformatApproved.setBackground(jxl.format.Colour.YELLOW);
dataformatApproved1.setAlignment(jxl.format.Alignment.LEFT);
dataformatApproved1.setBackground(jxl.format.Colour.BRIGHT_GREEN);
// times16format.setBackground(Colour.BLUE);
dataformatApproved.setBorder(jxl.format.Border.ALL , jxl.format.BorderLineStyle.MEDIUM_DASHED ,Colour.BLACK);
dataformatApproved1.setBorder(jxl.format.Border.ALL , jxl.format.BorderLineStyle.MEDIUM_DASHED ,Colour.BLACK);
Label label = new Label(0, 0, "Region Customer User Map",arial10format);
sheet.addCell(label);
label=new Label(0,2,"As of: ",arial10format);
sheet.addCell(label);
label=new Label(1,2,DateHandler.getTodaysDate(),arial10format);
sheet.addCell(label);
int i=0;
int j=3;
j++;
label = new Label(i, j, "Sales Region" ,times16format);
sheet.addCell(label);
i++;
label = new Label(i, j, "ROLES MAPPED AT REGION LEVEL (ALL EXCEPT SALES ID)" ,times16format);
sheet.addCell(label);
i++;
label = new Label(i, j, "Customer" ,times16format);
sheet.addCell(label);
i++;
label = new Label(i, j, "Country" ,times16format);
sheet.addCell(label);
i++;
label = new Label(i, j, "Sales Id" ,times16format);
sheet.addCell(label);
i++;
while(endDate.after(stDate)){
label = new Label(i, j, formatter1.format(new java.util.Date(stDate.getTimeInMillis())) ,times16format);
sheet.addCell(label);
i++;
stDate.add(Calendar.DATE, 7);
}
for (int count = 0; count < cust_list.size(); count++) {
MapCustomerForm mapCustomerForm=new MapCustomerForm();
mapCustomerForm = (MapCustomerForm) cust_list.get(count);
j++;
i=0;
label = new Label(i, j, mapCustomerForm.getRegion(),arial10format);
sheet.addCell(label);
i++;
label = new Label(i, j, mapCustomerForm.getUser_names(),arial10format);
sheet.addCell(label);
i++;
label = new Label(i, j,mapCustomerForm.getCustomer(),arial10format);
sheet.addCell(label);
i++;
label = new Label(i, j, mapCustomerForm.getLocation(),arial10format);
sheet.addCell(label);
i++;
label = new Label(i, j,mapCustomerForm.getSalesid(),arial10format);
sheet.addCell(label);
i++;
}
j=j+2;
workbook.write();
workbook.close();
}catch(Exception e){
System.out.print(e);
}
/*response.setHeader("Content-disposition", "attachment;filename="+ fileName );
final java.io.OutputStream os = response.getOutputStream();
os.flush();
os.close();*/
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","inline;fileName=" + fileName);
final java.io.OutputStream os = response.getOutputStream();
os.flush();
return new ResourceStreamInfo(contentType, servletCtx, fileName);
}
}
Hi to all,
i want to export some data to an excel file all it's functionality working but after downloading the excel file i am trying to open it is saying like "the file you are trying to open .xls is in a different format than specified by the file extension"please any one help me how to solve this one.....
this is my error log...........
Servlet.service() for servlet action threw exception: java.lang.IllegalStateException: Cannot forward after response has been committed
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:417) [:6.1.0.Final]
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:407) [:6.1.0.Final]
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1063) [:1.2.7]
at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:386) [:1.2.7]
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:229) [:1.2.7]
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194) [:1.2.7]
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) [:1.2.7]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [:1.0.0.Final]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [:1.0.0.Final]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:324) [:6.1.0.Final]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242) [:6.1.0.Final]
at com.zyom.security.LoginMonitorFilter.doFilter(LoginMonitorFilter.java:67) [:]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274) [:6.1.0.Final]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242) [:6.1.0.Final]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [:6.1.0.Final]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [:6.1.0.Final]
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:181) [:6.1.0.Final]
at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.event(CatalinaContext.java:285) [:1.1.0.Final]
at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.invoke(CatalinaContext.java:261) [:1.1.0.Final]
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:88) [:6.1.0.Final]
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:100) [:6.1.0.Final]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:159) [:6.1.0.Final]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [:6.1.0.Final]
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158) [:6.1.0.Final]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [:6.1.0.Final]
at org.jboss.web.tomcat.service.request.ActiveRequestResponseCacheValve.invoke(ActiveRequestResponseCacheValve.java:53) [:6.1.0.Final]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362) [:6.1.0.Final]
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [:6.1.0.Final]
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:654) [:6.1.0.Final]
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:951) [:6.1.0.Final]
at java.lang.Thread.run(Thread.java:619) [:1.6.0_13]
