【poi导出excel之XSSFWorkbook】

一个搬砖的农民工

已于 2023-09-22 12:02:58 修改

阅读量2.4w

点赞数 24

分类专栏： Java 文章标签： poi XSSF XSSFWorkbook excel

于 2022-08-03 15:19:17 首次发布

本文链接：https://blog.csdn.net/weixin_44183847/article/details/126029216

版权

Java 专栏收录该内容

65 篇文章 11 订阅

订阅专栏

HSSF：Excel97-2003版本，扩展名为.xls。一个sheet最大行数65536，最大列数256。
XSSF：Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384。
SXSSF：是在XSSF基础上，POI3.8版本开始提供的支持低内存占用的操作方式，扩展名为.xlsx。

因为xssf是将数据存在内存，所以为了防止内存溢出，就出现了sxssf，例：

SXSSFWorkbook w3= new SXSSFWorkbook(1000);

SXSSFWorkbook 设置内存中最多只有1000行数据，当超过这个数据时，就将内存之前的数据删除，并且会在硬盘中生成临时文件。从而保证了低内存消耗。
注：针对 SXSSF Beta 3.8下，会有临时文件产生

1、XSSFWorkbook 生成excel

	// 1、创建工作表
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
	XSSFSheet xssfSheet = xssfWorkbook.createSheet("sheet名称");
	XSSFRow xssfRow; // 行
	XSSFCell xssfCell; // 列
	// 2、在sheet中创建行，注意判断 第一行是否已经创建，否则会覆盖之前的数据
	xssfRow = xssfSheet.getRow(1);
	if (xssfRow == null) {
		xssfRow = xssfSheet.createRow(1);
	}
	// 3、创建单元格
	xssfCell = xssfRow.createCell(1);
	// 4、设置单元格内容
	xssfCell.setCellValue("测试"); 
	// 5、导出excel
	response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("表名.xlsx", "UTF-8"));
    ServletOutputStream out = response.getOutputStream();
    xssfWorkbook.write(out);
    out.close();
    xssfWorkbook.close();

2、合并单元格

把上面第2步到第4步替换成如下
（注：创建的单元格和值要取合并单元格左上角第一个）

	// 创建合并单元格（int firstRow, int lastRow, int firstCol, int lastCol）
	CellRangeAddress range = new CellRangeAddress(1, 1, 2, 5);
	// 创建行
	xssfRow = xssfSheet.getRow(range.getFirstRow());
	if (xssfRow == null) {
	    xssfRow = xssfSheet.createRow(range.getFirstRow());
	}
	// 创建列
	xssfCell = xssfRow.createCell(range.getFirstColumn());
	// 设置单元格内容
	xssfCell.setCellValue("测试"); 
	// 添加合并单元格到sheet
	xssfSheet.addMergedRegion(range);
	
	// 设置单元格样式：
	CellStyle style = xssfWorkbook.createCellStyle();
	// 1、水平\垂直居中
	style.setAlignment(HorizontalAlignment.CENTER);
	style.setVerticalAlignment(VerticalAlignment.CENTER);
	// 2、设置背景色
	style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 全部填充
	// 3、字段加粗
	XSSFFont font = xssfWorkbook.createFont();
	font.setBold(true);
	style.setFont(font);
	// 4、设置单元格样式为黑色实线（不是合并单元格，如果是合并单元格用下面第5个）
    style.setBorderBottom(BorderStyle.THICK); //下边框
    style.setBorderLeft(BorderStyle.THICK); //左边框
    style.setBorderTop(BorderStyle.THICK); //上边框
    style.setBorderRight(BorderStyle.THICK); //右边框
	xssfCell.setCellStyle(style);
	// 5、设置合并边框样式为黑色实线（放最后以免样式被覆盖）
	RegionUtil.setBorderBottom(BorderStyle.THICK, range , xssfSheet);
	RegionUtil.setBorderRight(BorderStyle.THICK, range , xssfSheet);
	RegionUtil.setBorderTop(BorderStyle.THICK, range , xssfSheet);
	RegionUtil.setBorderLeft(BorderStyle.THICK, range , xssfSheet);

单元格对应颜色：POI 设置Excel单元格背景色

3、单元格设置下拉列表

	XSSFWorkbook workbook = new XSSFWorkbook();
	XSSFSheet sheet = workbook.createSheet("导入模板");
	// 1、设置表头
	。。。。。
	// 2、为第3列设置下拉选项
	String[] arr = {"选项1","选项2"};
	sheet.addValidationData(addValidationData(sheet, arr, 3));
	// 如果下拉数据过长不展示，用下面这个方法
	setLongHSSFValidation(workbook,arr,sheet,1,arr.length,3,1);

	
	
	/**
	* 设置下拉列表
	*
	* @param sheet 页
	* @param datas 下拉数组
	* @param index 下拉列序号
	* @return
	*/
	private XSSFDataValidation addValidationData(XSSFSheet sheet, String[] datas, Integer index) {
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(datas);
		CellRangeAddressList addressList = null;
		XSSFDataValidation validation = null;
		addressList = new CellRangeAddressList(1, 100000, index, index);// 开始结束行，index表示开始和结束列
		validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
		// 这两行设置单元格只能是列表中的内容，否则报错
		validation.setSuppressDropDownArrow(true);
		validation.setShowErrorBox(true);
		return validation;
	}
	
	/**
        *  解决下拉框过长不显示问题
        * @param workbook
        * @param deptList 下拉数据数组
        * @param sheet
        * @param firstRow 开始行
        * @param endRow 结束行
        * @param cellNum 下拉框所在的列
        * @param sheetIndex 隐藏sheet名称
    */
    public static void setLongHSSFValidation(XSSFWorkbook workbook,String[] deptList ,XSSFSheet sheet ,int firstRow, int endRow, int cellNum,int sheetIndex) {
        String hiddenName = "hidden"+cellNum;
        //1.创建隐藏的sheet页。        起个名字吧！叫"hidden"！
        XSSFSheet hidden = workbook.createSheet(hiddenName);
        //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
        for (int i = 0, length = deptList.length; i < length; i++) {
            hidden.createRow(endRow + i).createCell(cellNum).setCellValue(deptList[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenName);
        //3 A1:A代表隐藏域创建第N列createCell(N)时。以A1列开始A行数据获取下拉数组
        category1Name.setRefersToFormula(hiddenName + "!A1:A" + (deptList.length + endRow));
        //
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenName);
        CellRangeAddressList addressList = new CellRangeAddressList(1, endRow, cellNum, cellNum);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof XSSFDataValidation) {
            // 数据校验
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        // 作用在目标sheet上
        sheet.addValidationData(dataValidation);
        // 设置hiddenSheet隐藏
        workbook.setSheetHidden(sheetIndex, true);
    }

4、Excel 导入模板工具类

应用场景：需要包含下拉的导入模板
（1）工具类


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.nicenet.cloud.framework.excel.core.listener.EasyExcelListener;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel 工具类
 *
 * @author 
 */
public class ExcelUtils {


    /**
     * 1、获取excel导入模板
     *
     * @param clazz    实体类
     * @param fileName 文件名
     * @param response 返回流
     * @throws IOException
     */
    public static void exportTemplate(Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
        exportTemplateWithDropdown(clazz, fileName, new HashMap<>(), response);
    }

    /**
     * 2、获取excel导入模板（包含下拉）
     *
     * @param clazz    实体类
     * @param fileName 文件名
     * @param map      下拉数据  Map<列编号, 下拉数组>
     * @param response 返回流
     * @throws IOException
     */
    public static void exportTemplateWithDropdown(Class<?> clazz, String fileName, Map<Integer, String[]> map, HttpServletResponse response) throws IOException {
        // 创建 Excel 文件并获取工作表
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(fileName);
        // 设置实体类字段ExcelProperty注解中文作为第一行标题
        Map<Integer, String> titleMap = new HashMap<>();
        Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelProperty.class))
                .forEach(field -> {
                    ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                    // 有index则按照大小排序，否则默认为字段先后顺序
                    if (excelProperty.index() >= 0){
                        titleMap.put(excelProperty.index(), excelProperty.value()[0]);
                    }else {
                        titleMap.put(titleMap.size(), excelProperty.value()[0]);
                    }
                });
        // 写入标题行
        sheet.createRow(0);
        for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
            sheet.getRow(0).createCell(entry.getKey()).setCellValue(entry.getValue());
        }
        // 设置下拉选项
        if (map != null){
            for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
            	if (entry.getKey() != null && ArrayUtil.isNotEmpty(entry.getValue())){
                    setLongHSSFValidation(sheet, entry.getValue(), 1000000, entry.getKey());
                }
            }
        }
        // 返回 Excel 文件
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    /**
     * 设置下拉列表
     *
     * @param sheet    数据页
     * @param deptList 下拉数据枚举
     * @param endRow   结束行
     * @param cellNum  下拉框所在的列
     */
    public static void setLongHSSFValidation(XSSFSheet sheet, String[] deptList, int endRow, int cellNum) {
        // 数据验证 Helper
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        // 数据验证约束
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(deptList);
        // 该约束将下拉列表选项应用于列，定义下拉列表的有效范围
        CellRangeAddressList addressList = new CellRangeAddressList(1, endRow, cellNum, cellNum);
        // 创建一个数据验证实例，并将其添加到工作表。
        DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
        if (validation instanceof XSSFDataValidation) {
            // 是否隐藏下拉箭头,设置为 true 时则不显示
            validation.setSuppressDropDownArrow(true);
            // 是否显示错误框,设置为 true 则显示
            validation.setShowErrorBox(true);
            // 设置错误样式, 当输入值不在验证约束范围内时，停止输入
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            // 创建错误框。用于定义错误框的标题和内容文本
            validation.createErrorBox("Invalid input", "Please select a value from the dropdown list.");
        } else {
            validation.setSuppressDropDownArrow(false);
        }
        // 添加到工作表
        sheet.addValidationData(validation);
    }

}

（2）实体类

@Data
@Accessors(chain = false)
public class GatewayBrandImportDTO implements Serializable {

    @ExcelProperty(index = 0, value = "品牌名称")
    private String gatewayBrandName;

    @ExcelProperty(index = 1, value = "是否启用")
    private String isEnable;

    @ExcelProperty(index = 2, value = "备注")
    private String remark;
}

（3）枚举类


import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

public enum IsEnableEnum {
    /**
     * 启用
     */
    enable(0, "启用"),
    /**
     * 禁用
     */
    disabled(1, "禁用");
    private final Integer status;
    private final String remark;

    IsEnableEnum(Integer status, String remark) {
        this.status = status;
        this.remark = remark;
    }

    public Integer getStatus() {
        return status;
    }


    public String getRemark() {
        return remark;
    }

    public static String[] getValues() {
        return Arrays.stream(IsEnableEnum .values()).map(IsEnableEnum::getRemark).toArray(String[]::new);
    }

    // 根据备注获取枚举
    private static Map<String, IsEnableEnum> map = new HashMap<>();

    static {
        for (IsEnableEnum value : IsEnableEnum.values()) {
            map.put(value.getRemark(), value);
        }
    }

    public static IsEnableEnum getByRemark(String remark) {
        return map.get(remark);
    }

    // 根据状态获取枚举
    private static Map<Integer, IsEnableEnum> map2 = new HashMap<>();
    static {
        for (IsEnableEnum value : IsEnableEnum .values()) {
            map2.put(value.getStatus(),value);
        }
    }
    public static IsEnableEnum getByStatus(Integer status){
        return map2.get(status);
    }

}

（4）测试

@GetMapping("/template")
public void getTemplate(HttpServletResponse response) {
    Map<Integer, String[]> map = new HashMap<>();
	map.put(1, IsEnableEnum.getValues());
	// map.put(...);
	ExcelUtils.exportTemplateWithDropdown(GatewayBrandImportDTO.class, "导入模板", map, response);
}