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

推荐答案

要在保护工作表的同时调整大小，您需要设置

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屋！