JExcel的使用demo

下面方法中有Excel格式的生成，以及下载写法。

import jxl.Workbook;
import jxl.format.Border;

import jxl.format.BorderLineStyle;

import jxl.format.Colour;
import jxl.format.UnderlineStyle;

import jxl.write.Label;
import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;
import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

private String downloadFileName = "归档文件目录.xls";//下载文件名称

    public String getDownloadFileName() {
        try {
            this.downloadFileName = new String(downloadFileName.getBytes("GBK"),"ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return this.downloadFileName;
    }

    public void setDownloadFileName(String downloadFileName) {
        this.downloadFileName = downloadFileName;
    }
    
    /**
     * filestream 下载文件流
     * 
     * getFilestream 得到下载文件流方法
     * setFilestream 设置下载文件流方法
     */
    private BufferedInputStream filestream;//文件流

    public BufferedInputStream getFilestream() {
        try {
            String uploadPath = WAFConfigure.getProperty("UploadFile_Path2");
            filestream = new BufferedInputStream(new FileInputStream(uploadPath + "/" + downloadFileName));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return this.filestream;
    }

    public void setFilestream(BufferedInputStream filestream) {
        this.filestream = filestream;
    }
    
    /**
     * 文件下载的action
     * 
     * uploadPath 文件的存储路径
     * downloadFileName 获取的文件名称
     * filestream 返回的文件流
     */
    public String downLoad() {
        creatExcel();
        try {
            // 设置返回的url值
            String url = "SUCCESS";
            // 从Property文件中获取图片的存储路径
            String uploadPath = WAFConfigure.getProperty("UploadFile_Path2");
            File folder = new File(uploadPath + "/" + downloadFileName);
            // 判断文件是否存在
            if (folder.exists()) {
                // 文件存在时创建下载文件的输出流
                filestream = new BufferedInputStream(new FileInputStream(
                        uploadPath + "/" + downloadFileName));
                byte[] buffer = new byte[filestream.available()];
                filestream.read(buffer);
                if (filestream == null) {
                    url = ERROR;
                } else {
                    url = SUCCESS;
                }
            } else {
                String noticeInfo = "文件已删除";
                url = "nosession";
            }
            return url;
        } catch (Exception ex) {
            ex.printStackTrace();
            return ERROR;
        } 
    }
    
    
    //生成导出的Excel文件
    public void creatExcel() {
        String path = WAFConfigure.getProperty("UploadFile_Path2");
        try {
            WritableWorkbook book = Workbook.createWorkbook(new File(path+"/"+this.downloadFileName));
            WritableSheet sheet = book.createSheet("第一页", 0);
            /*********表头样式初始化***********/
            //表头样式1
            WritableCellFormat tcellFormat1 = new WritableCellFormat();
            tcellFormat1.setAlignment(jxl.format.Alignment.CENTRE);//设置水平居中               
            tcellFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//设置垂直居中             
            tcellFormat1.setWrap(true);//设置自动换行             
            tcellFormat1.setFont(new WritableFont(WritableFont.createFont("楷体_GB2312"),26,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); //设置显示的字体样式，字体，字号，是否粗体，字体颜色 
            //表头样式2
            WritableCellFormat tcellFormat2 = new WritableCellFormat();
            tcellFormat2.setAlignment(jxl.format.Alignment.LEFT);            
            tcellFormat2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            tcellFormat2.setWrap(true);       
            tcellFormat2.setFont(new WritableFont(WritableFont.createFont("楷体_GB2312"),18,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK));
            //表头样式3
            WritableCellFormat tcellFormat3 = new WritableCellFormat();
            tcellFormat3.setAlignment(jxl.format.Alignment.CENTRE);          
            tcellFormat3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            tcellFormat3.setWrap(true);         
            tcellFormat3.setFont(new WritableFont(WritableFont.createFont("楷体_GB2312"),14,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            tcellFormat3.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //件号样式
            WritableCellFormat bcellFormat0 = new WritableCellFormat();
            bcellFormat0.setAlignment(jxl.format.Alignment.CENTRE);          
            bcellFormat0.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            bcellFormat0.setWrap(true);         
            bcellFormat0.setFont(new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat0.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //责任者样式
            WritableCellFormat bcellFormat1 = new WritableCellFormat();
            bcellFormat1.setAlignment(jxl.format.Alignment.CENTRE);          
            bcellFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            bcellFormat1.setWrap(true);         
            bcellFormat1.setFont(new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //文号
            WritableCellFormat bcellFormat2 = new WritableCellFormat();
            bcellFormat2.setAlignment(jxl.format.Alignment.CENTRE);          
            bcellFormat2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            bcellFormat2.setWrap(true);         
            bcellFormat2.setFont(new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //题名
            WritableCellFormat bcellFormat3 = new WritableCellFormat();       
            bcellFormat3.setVerticalAlignment(jxl.format.VerticalAlignment.BOTTOM);      
            bcellFormat3.setWrap(true);         
            bcellFormat3.setFont(new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat3.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //日期,页数
            WritableCellFormat bcellFormat4 = new WritableCellFormat();
            bcellFormat4.setAlignment(jxl.format.Alignment.CENTRE);          
            bcellFormat4.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            bcellFormat4.setWrap(true);         
            bcellFormat4.setFont(new WritableFont(WritableFont.createFont("宋体"),13,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat4.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //备注
            WritableCellFormat bcellFormat5 = new WritableCellFormat();
            bcellFormat5.setAlignment(jxl.format.Alignment.CENTRE);          
            bcellFormat5.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
            bcellFormat5.setWrap(true);         
            bcellFormat5.setFont(new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); 
            bcellFormat5.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            /*********表头部分***********/
            //表头1
            sheet.mergeCells(0, 0, 6, 0);//合并单元格
            sheet.setRowView(0, 1000);//设置行高
            sheet.addCell(new Label(0, 0, "归 档 文 件 目 录", tcellFormat1));//要插入的单元格内容,第一个是列，第二个是行，第三个是值，第四个是样式
            
            //表头2
            sheet.mergeCells(0, 1, 3, 1);
            sheet.mergeCells(4, 1, 6, 1);
            sheet.setRowView(1, 650);
            sheet.addCell(new Label(0, 1, "机构（问题）：" + this.problem, tcellFormat2));
            sheet.addCell(new Label(4, 1, "期限：" + this.qixian, tcellFormat2));
            
            //表头3
            sheet.setRowView(2, 650);
            sheet.setColumnView(1, 10);//列宽设置
            sheet.setColumnView(4, 7);//列宽设置
            sheet.setColumnView(5, 7);//
            sheet.setColumnView(3, 39);
            
            sheet.addCell(new Label(0, 2, "件号", tcellFormat3));
            sheet.addCell(new Label(1, 2, "责任者", tcellFormat3));
            sheet.addCell(new Label(2, 2, "文号", tcellFormat3));
            sheet.addCell(new Label(3, 2, "题        名", tcellFormat3));
            sheet.addCell(new Label(4, 2, "日期", tcellFormat3));
            sheet.addCell(new Label(5, 2, "页数", tcellFormat3));
            sheet.addCell(new Label(6, 2, "备注", tcellFormat3));
            
            /*********表格主体部分***********/
            /*测试用
            for(int i = 3; i < 30; i++) {
                sheet.setRowView(i, 650);//设置行高                    
                sheet.addCell(new Label(0, i, "30", bcellFormat0));
                sheet.addCell(new Label(1, i, "北京市爱莲舞蹈学校", bcellFormat1));
                sheet.addCell(new Label(2, i, "京兴政办通〔2008〕05号", bcellFormat2));
                sheet.addCell(new Label(3, i, "北京市大兴区人民政府办公室关于2", bcellFormat3));
                sheet.addCell(new Label(4, i, "20081028", bcellFormat4));
                sheet.addCell(new Label(5, i, "8", bcellFormat4));
                sheet.addCell(new Label(6, i, "发文（京兴政函[2012]31号）", bcellFormat5));
            }*/
            
            ArrayList arrayList = new ArrayList();
            if(listSearch() != null && !listSearch().equals("")) {
                arrayList = (ArrayList)listSearch();
            }
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
            int length = listSearch().size()+3;
            //取得实体的游标值
            int getNumber = 0;
            for(int i = 3; i < length; i++) {
                Archives archives = (Archives)arrayList.get(getNumber);
                String tm = archives.getTiming();
                int tmlength = tm.length();
                //一行只能有16个长度
                int bs = tmlength/16; //计算出16的倍数
                int ys = 1%16;        //计算出余数
                //如果有余数，最后一行截取的起始下标
                int end = 0;
                //题名小于16的长度
                if(bs == 0) {
                    sheet.setRowView(i, 650);//设置行高
                    sheet.addCell(new Label(0, i, archives.getSbjh(), bcellFormat0));
                    sheet.addCell(new Label(1, i, archives.getZeren(), bcellFormat1));
                    sheet.addCell(new Label(2, i, archives.getWenhao(), bcellFormat2));
                    sheet.addCell(new Label(3, i, archives.getTiming(), bcellFormat3));
                    String time = dateFormat.format(archives.getTime());
                    sheet.addCell(new Label(4, i, time, bcellFormat4));
                    sheet.addCell(new Label(5, i, archives.getPage(), bcellFormat4));
                    sheet.addCell(new Label(6, i, archives.getBeizhu(), bcellFormat5));
                //题名大于16的长度
                }else {
                    for(int j = 1; j <=bs; j++) {
                        sheet.setRowView(i, 650);//设置行高
                        sheet.addCell(new Label(0, i, archives.getSbjh(), bcellFormat0));
                        sheet.addCell(new Label(1, i, archives.getZeren(), bcellFormat1));
                        sheet.addCell(new Label(2, i, archives.getWenhao(), bcellFormat2));
                        //循环取得截取的字符串，长度为16
                        sheet.addCell(new Label(3, i, tm.substring((j-1)*16, j*16), bcellFormat3));
                        sheet.addCell(new Label(4, i, null, bcellFormat4));
                        sheet.addCell(new Label(5, i, null, bcellFormat4));
                        sheet.addCell(new Label(6, i, archives.getBeizhu(), bcellFormat5));    
                        if(j != 1) {
                            sheet.setRowView(i, 650);//设置行高
                            sheet.addCell(new Label(0, i, null, bcellFormat0));
                            sheet.addCell(new Label(1, i, null, bcellFormat1));
                            sheet.addCell(new Label(2, i, null, bcellFormat2));
                            sheet.addCell(new Label(3, i, tm.substring((j-1)*16, j*16), bcellFormat3));
                            //没有余数时控制时间、页数在最后一行显示
                            String page = null;
                            String showtime = null;
                            if(j == bs && ys == 0) {
                                page = archives.getPage();
                                showtime = dateFormat.format(archives.getTime());
                            }
                            sheet.addCell(new Label(4, i, showtime, bcellFormat4));
                            sheet.addCell(new Label(5, i, page, bcellFormat4));
                            sheet.addCell(new Label(6, i, null, bcellFormat5));
                        }
                        end = j*16;
                        length++;
                        i++;
                    }
                    //有余数时
                    if(ys != 0){
                        sheet.setRowView(i, 650);//设置行高
                        sheet.addCell(new Label(0, i, null, bcellFormat0));
                        sheet.addCell(new Label(1, i, null, bcellFormat1));
                        sheet.addCell(new Label(2, i, null, bcellFormat2));
                        sheet.addCell(new Label(3, i, tm.substring(end), bcellFormat3));
                        String time = dateFormat.format(archives.getTime());
                        sheet.addCell(new Label(4, i, time, bcellFormat4));
                        sheet.addCell(new Label(5, i, archives.getPage(), bcellFormat4));
                        sheet.addCell(new Label(6, i, null, bcellFormat5));
                    }
                }
                getNumber++;
            }                                                             
            //开始执行写入操作  
            book.write(); 
            //关闭流  
            book.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

 

转载于:https://www.cnblogs.com/is1988/archive/2012/10/08/2714880.html