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]

The big clue is in the message "The file you are trying to open .xls is in a different format than specified by the file extension." The filename should appear, not just the extension. Open the file you want to import into a text editor. Is it actually a delimited text file instead of an Excel workbook?

no it is an excel file with .xls extension i need to open this file only in excel format,text editor not working for this.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.