Java之使用poi导出excel文件，并为特定单元格加锁

使用 SXSSFWorkbook 进行Excel导出下载

注意：测试结果没有达到预期，那换个方式试试

参考：1.https://blog.csdn.net/aiza4108/article/details/101129894
2.https://blog.csdn.net/cc_yy_zh/article/details/78772217

我想要的需求
1.导出的excel表头不能被修改
2.表头所在列的行（初始化行数，文章是50w行）是可以被编辑的（除过表头）
3.其余是不可编辑的
4.如下图






为特定列行加锁
1.先锁住整张sheet
2.在对不加锁的列和行进行解锁

加锁核心代码
为当前sheet加锁，加完锁后，整个sheet就会被锁定

// 为当前sheet加锁
sheet.protectSheet(EXCEL_LOCK);

解锁锁核心代码

// 1.为单元格解锁，并设置单元格格式为文本
CellStyle unlockStyle = sxssfWorkbook.createCellStyle();
unlockStyle.setLocked(false);
unlockStyle.setDataFormat(dataFormat.getFormat("@"));

// 2.将设置好的unlockStyle 赋予到特定单元格上
SXSSFCell cell = sheetRow.createCell(column);
cell.setCellStyle(unlockStyle);

示例代码1

package com.qzlink.util.excel;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Record;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.base.Joiner;

public class ExcelUtil {
    private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    
    private static final String EXCEL_LOCK = "732a138b-288d-49fd-8039-3f3673c64a66";
    
    public static void exportExcel(Controller controller, String[] headers, String[] columns, String fileName, List<Record> dataList) {

        HttpServletResponse response = controller.getResponse();
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        OutputStream out = null;
        try{
            out = response.getOutputStream();
            // 创建工作簿
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();

            // 数据格式化
            DataFormat dataFormat = sxssfWorkbook.createDataFormat();

            // 设置单元格格式为“文本”格式
            CellStyle cellStyle = sxssfWorkbook.createCellStyle();
            cellStyle.setDataFormat(dataFormat.getFormat("@"));

            // 设置单元格为锁定模式，并设置单元格格式为文本  // 以下加锁代码可不用，这段代码多此一举
            CellStyle lockStyle = sxssfWorkbook.createCellStyle();
            lockStyle.setLocked(true);
            lockStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            lockStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            lockStyle.setDataFormat(dataFormat.getFormat("@"));

            // 为单元格解锁，并设置单元格格式为文本 // 解锁代码
            CellStyle unlockStyle = sxssfWorkbook.createCellStyle();
            unlockStyle.setLocked(false);
            unlockStyle.setDataFormat(dataFormat.getFormat("@"));

            // 创建工作表
            SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet1");
            // 判断header是否为空
            if (null == headers || headers.length <= 0) {
                // 写入文件
                sxssfWorkbook.write(out);
                return;
            }
            // 判断columns是否为空
            if (null == columns || columns.length <= 0 || null == dataList || dataList.size() <= 0) {
                for(int row = 0; row < UPLOAD_FILE_UPPER_LIMIT_SIZE; row++) {
                    SXSSFRow sheetRow = sheet.createRow(row);
                    if (row == 0) { // header
                        for (int column=0; column<headers.length; column++) {
                            SXSSFCell cell = sheetRow.createCell(column);
                            cell.setCellValue(headers[column]);
                            // cell.setCellStyle(lockStyle);  // 这段代码多此一举
                        }
                    } else { // 初始化50w数据
                        for (int column=0; column<headers.length; column++) {
                            SXSSFCell cell = sheetRow.createCell(column);
                            cell.setCellStyle(unlockStyle);
                        }
                    }
                }
                // 为整个sheet加锁
                sheet.protectSheet(EXCEL_LOCK);
                // 写入文件
                sxssfWorkbook.write(out);
                return;
            }
            // 设置数据
            for (int row=0; row<=dataList.size(); row++) {
                SXSSFRow sheetRow = sheet.createRow(row);
                if (row == 0) { // 设置head
                    for (int column=0; column<headers.length; column++) {
                        sheetRow.createCell(column).setCellValue(headers[column]);
                        sheet.setDefaultColumnStyle(column,cellStyle);
                    }
                } else { // 设置数据
                    for (int column=0; column<headers.length; column++) {
                        sheetRow.createCell(column).setCellValue(
                                dataList.get(row-1).getStr(columns[column]) == null ? "" : dataList.get(row-1).getStr(columns[column]));
                        sheet.setDefaultColumnStyle(column,cellStyle);
                    }
                }
            }
            // 写入文件
            sxssfWorkbook.write(out);
        } catch (Exception e) {
            log.error("ExcelUtil exportExcel exception catch : " + e);
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
                controller.renderNull();
            } catch (IOException e) {
                log.error("ExcelUtil exportExcel exception finally catch : " + e);
            }
        }

    }
}