需求:要求excel表格不能编辑并且数据不能进行复制,防止发生争议
实现:
//创建标签页
SXSSFSheet sheet=excel.createSheet(“sheet名称”);
//设置保护密码
sheet.protectSheet(“密码”);
//设置表格属性
sheet.lockSelectLockedCells(true);
sheet.lockSelectUnlockedCells(true);
sheet中的sheet.lockSelectLockedCells(true); 和 sheet.lockSelectUnlockedCells(true); 这两个属性相当于excel表格中的审阅-》保护工作表中的头两个选项的未选中状态:
使用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/qq_33841411/article/details/126919634