Apache POI 锁定单元格但允许调整列大小 [英] Apache POI lock the cell but allow column resize

问题描述

我通过 Apache POI XSSF 创建了一个 Excel 文件，并用密码锁定了工作表，因此用户无法更改前两行和前五列的值(我锁定了工作表并允许编辑其他单元格).一切正常，唯一的问题是用户无法调整列大小，因此他既不能更改列大小，也不能调整列大小以读取所有单元格值.即使工作表受到保护，是否可以允许调整列大小?这是我的配置

I create an Excel file through Apache POI XSSF and I lock the sheet with a password so user can't change the value of the first two row and first five columns (I lock the sheet and allowed editing of other cells). All work fine, the only problem is that the user can't resize the column so he can neither change nor resize the columns to read all the cells value. Is it possible to allow column resize even if the sheet is protected? Thi is my configuration

workbook = new XSSFWorkbook();
sheet = workbook.createSheet("Sheet1");
sheet.protectSheet("passwordExcel"); 
unlockedNumericStyle = workbook.createCellStyle(); 
unlockedNumericStyle.setLocked(false);
// Format cell for date
dateStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
sheet.autoSizeColumn(1);

我阅读了有关 lockFormatCell() 的内容，但我不明白它是否可以帮助我.谢谢

I read about lockFormatCell() but I don't understand if it can help me. Thanks

推荐答案

要在工作表受保护时调整列大小，您需要设置 XSSFSheet.lockFormatColumns 到 false.

To be able resizing the column size while sheet is protected, you will need setting XSSFSheet.lockFormatColumns to false.

完整示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class CreateExcelXSSFProtectedSheet {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();

  CreationHelper createHelper = workbook.getCreationHelper();

  CellStyle unlockedNumericStyle = workbook.createCellStyle();
  unlockedNumericStyle.setDataFormat(createHelper.createDataFormat().getFormat("$#,##0.00_);[Red]($#,##0.00)"));
  unlockedNumericStyle.setLocked(false);

  CellStyle dateStyle = workbook.createCellStyle();
  dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));

  Sheet sheet = workbook.createSheet();

  Row row = sheet.createRow(0);
  Cell cell = row.createCell(1);
  cell.setCellValue("some data");

  row = sheet.createRow(1);
  cell = row.createCell(1);
  cell.setCellValue(-123456789.0123456);
  cell.setCellStyle(unlockedNumericStyle);

  row = sheet.createRow(2);
  cell = row.createCell(1);
  cell.setCellValue(new java.util.Date());
  cell.setCellStyle(dateStyle);

  ((XSSFSheet)sheet).lockFormatColumns(false);

  sheet.protectSheet("passwordExcel"); 

  sheet.autoSizeColumn(1);

  workbook.write(new FileOutputStream("CreateExcelXSSFProtectedSheet.xlsx"));
  workbook.close();

 }

}

这篇关于Apache POI 锁定单元格但允许调整列大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！