jxl 操作excel - ITeye问答

0 0

jxl 操作excel0

做一个excel导出功能，实在不想贴代码真的看不出问题。谢谢各位了

protected ModelAndView handleRequestInternal(HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        OutputStream io = null;
        InputStream in = null;
        OutputStream outter = null;
        File datafile = null;
        try {
            String fileName = "信息";
            datafile = File.createTempFile(fileName + Calendar.getInstance().getTimeInMillis(),
                    DataListExportUtil.EXTENSION_XLS);

            io = new FileOutputStream(datafile);
            response.setContentType("application/msexcel");
            String filename = new String(fileName
                    .getBytes("GBK"), "ISO8859_1").trim()+".xls";
            response.setHeader("Content-Disposition", "attachment"
                    + ";filename=" + filename);



            excelSupport.generateAgentPersonExcelModule(request, response,"AgentCert", io);


            outter = new BufferedOutputStream(response.getOutputStream());
            in = new BufferedInputStream(new FileInputStream(datafile
                    .getPath()));
            byte[] buf = new byte[1024];
            while ((in.read(buf)) > 0) {
                outter.write(buf);
            }
            outter.flush();



        } catch (Exception e) {
       // logger.error(e);
        }finally {
            if(io != null){
                try {
                    io.close();
                } catch (IOException ex) {
                 //   logger.info("io.close() fail");
                }
            }
            // 删除文件
            if (datafile != null) {
                datafile.delete();
            }
            if (in != null) {
                try {
                    in.close();
                } catch (IOException ex) {
                //    logger.info("in.close() fail");
                }
            }
            if (outter != null) {
                try {
                    outter.close();
                } catch (IOException ex) {
                  // logger.info("outter.close() fail");
                }
            }
        }
        return null;
    }

public void generateAgentPersonExcelModule(HttpServletRequest request,
            HttpServletResponse res, String classType, OutputStream outter)throws WebException{
        WritableWorkbook book = null;

      // logger.info("start create excel module : " + classType);
        try {
            book = Workbook.createWorkbook(outter);
        } catch (IOException e) {
         // logger.error("create excel module error",e);
            throw new WebException("create excel module error",e);
        }



        if("AgentCert".equals(classType)){
            createSalesGroupPersonModule(book, request);
        }



    }



    private void createSalesGroupPersonModule(WritableWorkbook book,HttpServletRequest request)throws WebException{
        WritableSheet sheet = book.createSheet("信息", 0);
        List titleStringList = new ArrayList();
        String [] baseTitleStrings = new String [] {"代理人编码","代理证号(许可证号)","身份证号","姓名","性别(只能填,男或女)","展业证号","联系电话","联系人姓名","联系人性别(只能填,男或女)","联系人身份证号"};
        Label label0;
        Collections.addAll(titleStringList, baseTitleStrings);

        int col = 0;
        for(Iterator iter = titleStringList.iterator();iter.hasNext();){
            String titleString = (String)iter.next();
            label0 = new jxl.write.Label(col++, 0, titleString);
            WritableFont wf = new WritableFont(WritableFont.ARIAL);
            try {
                wf.setBoldStyle(WritableFont.BOLD);
            } catch (WriteException e) {
             //   logger.error("set bold font error :" + titleString,e);
                throw new WebException("set bold font error :" + titleString,e);
            }
            WritableCellFormat wcFmt = new WritableCellFormat(wf);
            try {
                wcFmt.setAlignment(Alignment.CENTRE);
                wcFmt.setBackground(Colour.GRAY_25);
            } catch (WriteException e) {
              // logger.error("set format center error :" + titleString,e);
                throw new WebException("set format center error :" + titleString,e);
            }
            label0.setCellFormat(wcFmt);
            try {
                sheet.addCell(label0);
            } catch (RowsExceededException e) {
               // logger.error("add cell "+ titleString +" error", e);
                throw new WebException("add cell "+ titleString +" error", e);
            } catch (WriteException e) {
               // logger.error("add cell " + titleString + " error", e);
                throw new WebException("add cell "+ titleString +" error", e);
            }
        }

        if(sheet.getColumns() >2){
            CellView cellView = new CellView();
            WritableCellFormat currencyFormat = new WritableCellFormat(new NumberFormat("###,##0.00"));
            cellView.setFormat(currencyFormat);
            cellView.setSize(6000);
            for(int i = 2;i<sheet.getColumns();i++){
                sheet.setColumnView(i, cellView);
            }
        }

        try {
            book.write();
        } catch (IOException e) {
           // logger.error("write book error",e);
            throw new WebException("set bold font error :",e);
        }
        try {
            book.close();
        } catch (WriteException e) {
           // logger.error("close book error",e);
            throw new WebException("set bold font error :",e);
        } catch (IOException e) {
          // logger.error("close book error",e);
            throw new WebException("set bold font error :",e);
        }
    }


当我下载excel 完成时，点击打开这个excel
然后就提示您尝试打开的格式与文件拓展名制定的格式不一致
点击继续结果里面没有内容只有sheet 有值，这是什么原因导致的，

Java Jxl

2012年9月19日 11:45

liuyuting
2
0 0 7

3个答案按时间排序按投票排序

0 0

POI3.8很好很强大！

2012年9月19日 13:54

sniciq
25
0 0 1

添加评论

0 0

代码多了眼花
送你个OK的连接，你在用的时候需要改下构造excel行列的数据源就ok
http://www.blogjava.net/yutian727/archive/2007/04/26/113719.html

2012年9月19日 12:29

demojava
2207
1 3 134

添加评论

0 0

1.哥们你excel的 ContentType有问题
response.setContentType("application/vnd.ms-excel");
2. 设置编码格式
response.setContentType("application/x-download");//设置下载类型，防止浏览器不识别例如：火狐
response.setContentType("application/vnd.ms-excel;charset=UTF-8");//charset=UTF-8 或者GB2312
response.setHeader("Content-Disposition", "attachment;filename="
+ new String((filename).getBytes("gb2312"), "ISO8859-1"));

2012年9月19日 12:23

demojava
2207
1 3 134