excel 导出 （带加密）

试了好几种excell导出，jxcell导出加密是成功的。poi的加密只支持2003版的。

参考地址：

http://blog.csdn.net/opzoonzhuzhengke/article/details/7255309

http://blog.csdn.net/opzoonzhuzhengke/article/details/7256870

http://blog.csdn.net/opzoonzhuzhengke/article/details/7257869

http://blog.csdn.net/opzoonzhuzhengke/article/details/7263392

http://blog.csdn.net/opzoonzhuzhengke/article/category/1076971

http://blog.csdn.net/opzoonzhuzhengke/article/details/7265050

http://blog.csdn.net/opzoonzhuzhengke/article/details/7265225

 

1.引入依赖包  pom.xml

              <dependency>
		  <groupId>org.juxinli.jxcell</groupId>
		  <artifactId>jxcell</artifactId>
		  <version>V1.0.0.01</version>
		</dependency>

 

2. 实现Tool_jxcell

   

package com.chengshu.tool;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.apache.log4j.Logger;

import com.jxcell.CellException;
import com.jxcell.CellFormat;
import com.jxcell.ConditionFormat;
import com.jxcell.View;

/**
 * @author 张彦
 * @email: [e-mail]
 * @date 创建时间：2016年7月14日 下午5:05:07
 * @version 1.0
 */
public class Tool_jxcell {
    private static final Logger LOG = Logger.getLogger(Tool_jxcell.class.getName());

    /**
     * 读取excel，并进行加密
     * 
     * @param url
     *            excel文件路径 例：D:\\word.xls
     * @param pwd
     *            加密密码
     * @throws Exception 
     */
    public static void encrypt(String path, String pwd) throws Exception {
        View view = new View();
        try {
            view.setText(0, 0, "机构");
            view.setText(0, 1, "aa贷");
            view.setText(0, 2, "时间区间");
            view.setText(0, 3, "从"+SimpleDateFormat.getInstance().format(Calendar.getInstance().getTime())+"到"+SimpleDateFormat.getInstance().format(Calendar.getInstance().getTime()));
            view.setText(1, 0, "下载日期");
            view.setText(1, 1, SimpleDateFormat.getInstance().format(Calendar.getInstance().getTime()));
            view.setText(1, 2, "评分区间");
            view.setText(1, 3, "80-90");
            view.setText(2, 0, "");
            view.setText(2, 1, "");
            view.setColWidth(2, 36*256);
            view.setText(2, 0, "概述");
            CellFormat cfmt = view.getCellFormat();
            view.setSelection(2, 0, 4, 0); //从第几行开始合并，从第几列开始合并，合并到第几行，合并到第几列 
            cfmt = view.getCellFormat();  
            cfmt.setMergeCells(true);  
            view.setCellFormat(cfmt);  
            view.setText(2, 1, "具体内容放在这里，房价大幅降低萨芬的萨芬。。。。。。。。");
            view.setSelection(2, 1, 4, 9); //从第几行开始合并，从第几列开始合并，合并到第几行，合并到第几列 
            cfmt = view.getCellFormat();  
            cfmt.setMergeCells(true);  
            view.setCellFormat(cfmt);  
            for(int i=0;i<100; i++){
                for(int j = 0 ; j<10 ;j++){
                    view.setText(i+4, j, i+4+","+j);
                }
            }
            //设置宽和高
//            view.setText(3,1,"你倒是出来呀 你到是~~~~~");           
//            view.setColWidth(3, 36*256);  
//            view.setRowHeight(3, 120*20);  
// 
//            //倾斜
//            CellFormat rangeStyle = view.getCellFormat();  
//            rangeStyle.setOrientation((short)45);  
//            view.setCellFormat(rangeStyle);  
//            
//          //multi text selection format  
//            view.setTextSelection(0, 6);  
//            cfmt.setFontItalic(true);  
//            cfmt.setFontColor(Color.BLUE.getRGB());  
//            view.setCellFormat(cfmt);  
//            
//            view.setTextSelection(7, 10);  
//            cfmt = view.getCellFormat();  
//            cfmt.setFontBold(true);  
//            cfmt.setFontSize(16*20);  
//            view.setCellFormat(cfmt); 
//            
//            view.setTextSelection(11, 13);  
//            cfmt = view.getCellFormat();  
//            cfmt.setFontUnderline(CellFormat.UnderlineSingle);  
//            cfmt.setFontColor(Color.GREEN.getRGB());  
//            view.setCellFormat(cfmt);  
//            
//            String text = "Hello, you are welcome!";  
//            view.setTextSelection(14, text.length()-1);  
//            cfmt = view.getCellFormat();  
//            cfmt.setFontSize(14*20);  
//            view.setCellFormat(cfmt);  
            
            view.write(path, pwd);
            
        } catch (CellException e) {
            LOG.error("加密失败！", e);
        } catch (IOException e) {
            LOG.error("写入excell错误！", e);
        }
    }

    /**
     * excel 解密
     * 
     * @return void
     * @author lifq
     * @date 2015-3-13 下午02:15:49
     */
    public static void decrypt(String url, String pwd) {
        View view = new View();
        // read the encrypted excel file
        try {
            view.read(url, pwd);
        } catch (IOException e) {
            LOG.error("读取excell错误！", e);
        } catch (CellException e) {
            LOG.error("解密失败！", e);
        }
    }

    private static void test() {
        View m_view = new View();
        try {
            // m_view.getLock();
            ConditionFormat condfmt[] = new ConditionFormat[3];
            condfmt[0] = m_view.CreateConditionFormat();
            condfmt[1] = m_view.CreateConditionFormat();
            condfmt[2] = m_view.CreateConditionFormat();

            // Condition #1
            CellFormat cf = condfmt[0].getCellFormat();
            // condfmt[0].setType(ConditionFormat.TypeFormula);
            // condfmt[0].setFormula1("and(iseven(row()), $D1 > 1000)", 0, 0);
            cf.setFontColor(0x0D8427);
            cf.setPattern((short) 1);
            cf.setPatternFG(0x0D8427);
            condfmt[0].setCellFormat(cf);

            // Condition #2
            // condfmt[1].setType(ConditionFormat.TypeFormula);
            // cf.setFontColor(0xffffff);
            condfmt[1].setCellFormat(cf);

            // Condition #3
            // condfmt[2].setType(ConditionFormat.TypeCell);
            // condfmt[2].setFormula1("500", 0, 0);
            // condfmt[2].setOperator(ConditionFormat.OperatorGreaterThan);
            // cf=condfmt[2].getCellFormat();
            // cf.setFontColor(0xff0000);
            // condfmt[2].setCellFormat(cf);

            // Select the range and apply conditional formatting
            m_view.setSelection(0, 0, 39, 3);
            m_view.setConditionalFormats(condfmt);

            m_view.write("./sonditionFormats.xls");
            com.jxcell.designer.Designer.newDesigner(m_view);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            m_view.releaseLock();
        }
    }

    public static void main(String args[]) {
        // 下面1与2 两个方法请分开执行，可以看到效果
        //
        // 1. 把g:\\test.xls 添加打开密码123
         try {
            Tool_jxcell.encrypt("E:\\test.xls", "123");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 2. 把g:\\test.xls 密码123 去除
        // Tool_jxcell.decrypt("E:\\test.xls", "111");
        // test();
//        test1();
    }

    public static void test1() {
        try {
            View m_view = new View();
            try {
                m_view.getLock();
                ConditionFormat condfmt[] = new ConditionFormat[1];
                condfmt[0] = m_view.CreateConditionFormat();

                // Condition #1
                CellFormat cf = condfmt[0].getCellFormat();
                condfmt[0].setType(ConditionFormat.TypeCell);
//                condfmt[0].setFormula1("and(iseven(row()), $D1 > 1000)", 0, 0);
                cf.setFontColor(0x00ff00); //白
                cf.setPattern((short) 1);
                cf.setPatternFG(0x99ccff); //蓝色
                condfmt[0].setCellFormat(cf);

                // Condition #2
//                condfmt[1].setType(ConditionFormat.TypeFormula);
//                condfmt[1].setFormula1("iseven($A1)", 0, 0);
//                cf.setFontColor(0xffffff);//黑色
//                condfmt[1].setCellFormat(cf);

                // Condition #3
//                condfmt[2].setType(ConditionFormat.TypeCell);
//                condfmt[2].setFormula1("500", 0, 0);
//                condfmt[2].setOperator(ConditionFormat.OperatorGreaterThan);
//                cf = condfmt[2].getCellFormat();
//                cf.setFontColor(0xff0000); //红色
//                condfmt[2].setCellFormat(cf);

                // Select the range and apply conditional formatting
                m_view.setSelection(0, 0, 39, 3);
                m_view.setConditionalFormats(condfmt);

                m_view.write("E:\\conditionFormats.xls");
                com.jxcell.designer.Designer.newDesigner(m_view);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                m_view.releaseLock();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

3.调用导出并下载

package com.chengshu.controller;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.chengshu.tool.Tool_jxcell;

/** 
 * @author  张彦
 * @email: [e-mail]
 * @date 创建时间：2016年7月21日 下午6:19:28 
 * @version 1.0  
 */
@RestController
@RequestMapping(value = "/download")
public class DownLoadExcell {
    private static final Logger LOG = Logger.getLogger(DownLoadExcell.class.getName());
    
    @RequestMapping(value = "/monthlyData")
    public void download(HttpServletRequest request,HttpServletResponse response){
        String filePath = request.getServletContext().getRealPath("/").concat("/tmp/");
        System.out.println("filePath "+filePath);
        String fileName= "text.xls";
        try {
            Tool_jxcell.encrypt(filePath.concat(fileName), "123");
        } catch (Exception e1) {
            LOG.error("导出加密excell失败",e1);
        }
        try {
            File file = new File(filePath.concat(fileName));//根据文件路径获得File文件
            //设置文件类型
            response.setContentType("application/msexcel;charset=UTF-8");
            //文件名
            response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes(), "UTF-8"));
            response.setContentLength((int) file.length());
            
            byte[] buffer = new byte[4096];// 缓冲区
            BufferedOutputStream output = null;
            BufferedInputStream input = null;
            try {
                output = new BufferedOutputStream(response.getOutputStream());
                input = new BufferedInputStream(new FileInputStream(file));
                int n = -1;
                //遍历，开始下载
                while ((n = input.read(buffer, 0, 4096)) > -1) {
                    output.write(buffer, 0, n);
                }
                output.flush();   //不可少
                response.flushBuffer();//不可少
            } catch (Exception e) {
                //异常自己捕捉     
                LOG.error("导出失败");
            } finally {
                //关闭流，不可少
                if (input != null)
                    input.close();
                if (output != null)
                   output.close();
                file.delete();//删除文件，以免占用服务器空间
            }
        } catch (Exception e) {
            LOG.error("导出失败",e);
        }
    }
}