POI导出Excel：合并单元格、设置Excel某列不可修改，设置行高列宽

 创建导出Excel工具类

package com.jlpt.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.util.Arrays;


public class ExcelUtil {

    /**
     * @param sheetName
     * @param ProhibitToEdit 是否禁止编辑，true则生成的表无法编辑，内容高亮显示，若允许某列可以编辑，可配置enableChange
     * @param enableChange   禁止修编辑，允许编辑的列；配置后显示可编辑并取消高亮
     * @param title          标题
     * @param calWidth       行宽,若传值为null或长度小于标题长度，则自动填充行宽15字符长度
     * @param values         内容
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, boolean ProhibitToEdit, int[] enableChange, String[] title,int[] calWidth, String[][] values) {

        // 创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = null;

        // 创建单元格，并设置值表头 设置表头居中

        //单元格样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(CellStyle.BORDER_THIN);// 右边框
        style.setWrapText(true);//自动换行

        HSSFCellStyle lockStyle = wb.createCellStyle();//列未锁定样式
        lockStyle.setLocked(false);   //设置列的锁定状态为未锁定
        lockStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        lockStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
        lockStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
        lockStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
        lockStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
        lockStyle.setWrapText(true);//自动换行
        //字体样式
        Font fontStyle = wb.createFont(); // 字体样式
        fontStyle.setBoldweight((short) 4); // 加粗
        fontStyle.setFontName("黑体"); // 字体
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 14);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderBottom(1, cra, sheet, wb); // 下边框
        RegionUtil.setBorderLeft(1, cra, sheet, wb); // 左边框
        RegionUtil.setBorderRight(1, cra, sheet, wb); // 有边框
        RegionUtil.setBorderTop(1, cra, sheet, wb); // 上边框

        //声明列对象
        HSSFCell cell = null;
        HSSFCell headercell = null;
        //标头样式
        HSSFRow row1 = sheet.createRow(0);
        HSSFCellStyle headerStyle = style;
        //Height的单位是1/20个点
        row1.setHeight((short) 600);
        headercell = row1.createCell(0);
        headercell.setCellValue("用户信息列表");
        headercell.setCellStyle(headerStyle);

        int [] reallyWidth=new int[title.length];
        Arrays.fill(reallyWidth,15);//设置默认列宽15字符长度（英）
        //设置列宽
        if(calWidth!=null){
            if(calWidth.length<title.length){
                reallyWidth=Arrays.copyOf(calWidth,calWidth.length);
            }else{
                reallyWidth=calWidth;
            }
        }

        //标题
        row = sheet.createRow(1);
        //创建标题
        for (int i = 0; i < title.length; i++) {
            //设置列宽度(参数的单位是1/256个字符宽度)
            sheet.setColumnWidth(i, reallyWidth[i] * 256);
            HSSFCellStyle titleStyle = style;
            titleStyle.setFont(fontStyle);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(titleStyle);
        }

        //创建内容
        if (ProhibitToEdit) {//禁止修改
            sheet.protectSheet("123456");
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
            style.setFillForegroundColor(HSSFColor.LIME.index);    //填蓝色
            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 221, (byte) 235, (byte) 247);
            if (enableChange != null) {//禁止修改后，允许编辑的列

                for (int i = 0; i < values.length; i++) {
                    row = sheet.createRow(i + 2);
                    for (int j = 0; j < values[i].length; j++) {
                        //将内容按顺序赋给对应的列对象
                        row.createCell(j).setCellValue(values[i][j]);
                        row.getCell(j).setCellStyle(style);
                    }
                    for (int columns : enableChange) {
                        row.getCell(columns).setCellStyle(lockStyle);
                    }
                }
            } else {
                for (int i = 0; i < values.length; i++) {
                    row = sheet.createRow(i + 2);
                    for (int j = 0; j < values[i].length; j++) {
                        //将内容按顺序赋给对应的列对象
                        row.createCell(j).setCellValue(values[i][j]);
                        row.getCell(j).setCellStyle(style);
                    }
                }
            }
        } else {
            for (int i = 0; i < values.length; i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < values[i].length; j++) {
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
        }
        return wb;
    }
}

controller控制器代码

    //批量导出
    @RequestMapping("/userExport")

    public String userExport(String orgId, HttpServletResponse response, HttpServletRequest request) {
        //获取数据
        List<UserInfo> userList= userService.getUserListByOrgId(orgId);
        int userNumber = userList.size();

        //excel标题
        String[] title = {"用户名", "真实姓名", "密码", "性别", "手机号","电话","邮箱","单位编码","用户编码","工作证号","职务","科室/处室","绑定IP","所在部门"};

        //excel文件名
        String fileName = "用户信息表" + System.currentTimeMillis() + ".xls";

        //sheet名
        String sheetName = "用户信息表";

        String [][] content=new String[userNumber][];
        for (int i = 0; i < userList.size(); i++) {
            content[i] = new String[title.length];
            UserInfo userInfo = userList.get(i);

            content[i][0] = userInfo.getUserName();
            content[i][1] = userInfo.getRealName();
            content[i][2] = userInfo.getPassword();
            content[i][3] = userInfo.getSex()=="0"?"男":"女";
            content[i][4] = userInfo.getCellPhone();
            content[i][5] = userInfo.getPhone();
            content[i][6] = userInfo.getEmail();
            content[i][7] = userInfo.getOrgCode();
            content[i][8] = userInfo.getUserCode();
            content[i][9] = userInfo.getCardId();
            content[i][10] = userInfo.getDuty();
            content[i][11] = userInfo.getDepartment();
            content[i][12] = userInfo.getIp();
        }
        //设置列宽
        int[] calWidth=new int[]{8,16,12,12,6,14,14,30,12,12,20,12,12,30,10};
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName,false,null, title,calWidth, content );

        //响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream ops = response.getOutputStream();
            wb.write(ops);
            ops.flush();
            ops.close();
        } catch (Exception e) {
            e.printStackTrace();

        }

        return null;
    }
//发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }