【java】设置下载的Excel不可编辑复制

客官°小女子只卖身不卖艺 2023-09-23 17:08 109阅读 0赞

**需求：要求excel表格不能编辑并且数据不能进行复制，防止发生争议**

实现：

//创建标签页
    SXSSFSheet sheet=excel.createSheet(“sheet名称”);
    //设置保护密码
    sheet.protectSheet(“密码”);
    //设置表格属性
    sheet.lockSelectLockedCells(true);
    sheet.lockSelectUnlockedCells(true);

sheet中的sheet.lockSelectLockedCells(true); 和 sheet.lockSelectUnlockedCells(true); 这两个属性相当于excel表格中的审阅-》保护工作表中的头两个选项的未选中状态:  
![在这里插入图片描述][8c0a65fa47fc4b11a0ef9bc0b73d0a76.png]

**使用easyExcel生成文件，并使得excel表格不能编辑并且数据不能进行复制：**

//添加文件导出格式限制
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    // 模板地址
    String templateFileName = "template" + File.separator + "excelTemplate.xlsx";
    //导出模板
    ExcelWriter excelWriter = null;
    try {
        
           InputStream inputStream = new ClassPathResource(templateFileName).getInputStream();
           excelWriter = EasyExcel.write(response.getOutputStream())
                                   .withTemplate(inputStream)
                                   .registerWriteHandler(new CustomSheetWriteHandler())//设置表格不可编辑和复制的处理策略
                                   .build();
    } catch (IOException e) {
        
         response.reset();
         response.setContentType("application/json");
         response.setHeader("Content-Disposition", "");
                               return R.failed("下载异常，请联系管理员进行排查！");
    }
    //读取Excel
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    //是否新增行
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    //list填充
    excelWriter.fill(list, fillConfig, writeSheet);
    //map填充
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("sumMoney",sumMoney);
    map.put("ylSumMoney",ylSumMoney);
    map.put("sySumMoney",sySumMoney);
    map.put("gsSumMoney",gsSumMoney);
    excelWriter.fill(map, writeSheet);
    // 关闭流
    excelWriter.finish();

**处理策略：只有当使用者输入正确的保护密码时才可对下载sheet进行更改。**

public class CustomSheetWriteHandler implements SheetWriteHandler {
        
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
            // 设置保护密码
            writeSheetHolder.getSheet().protectSheet("一个复杂密码");
            // 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)
            ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);
            ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectUnlockedCells(true);
        }
    
    }

参考链接：[https://blog.csdn.net/lu200601051315/article/details/104789830][https_blog.csdn.net_lu200601051315_article_details_104789830]  
复杂版的参考链接：[https://blog.csdn.net/qq\_33841411/article/details/126919634][https_blog.csdn.net_qq_33841411_article_details_126919634]

[8c0a65fa47fc4b11a0ef9bc0b73d0a76.png]: https://img-blog.csdnimg.cn/8c0a65fa47fc4b11a0ef9bc0b73d0a76.png
[https_blog.csdn.net_lu200601051315_article_details_104789830]: https://blog.csdn.net/lu200601051315/article/details/104789830v
[https_blog.csdn.net_qq_33841411_article_details_126919634]: https://blog.csdn.net/qq_33841411/article/details/126919634