欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

excel 导出 (带加密)

程序员文章站 2022-03-26 12:19:17
...

试了好几种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: 522378972@qq.com
 * @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: zhangyan1@juxinli.com
 * @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);
        }
    }
}

 

 

相关标签: java jxcell