【poi导出excel之XSSFWorkbook】

POI提供了HSSF、XSSF以及SXSSF三种方式操作Excel。

  • 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);
}
  • 20
    点赞
  • 113
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
Spring Boot是一个快速开发框架,可以轻松地创建基于Java的Web应用程序。POI是一个Java库,用于读写Microsoft Office格式的文件,包括Excel、Word和PowerPoint等。结合Spring Boot和POI,可以轻松地实现Excel文件的导出功能。 具体实现步骤如下: 1. 添加POI依赖 在pom.xml文件中添加POI依赖: ``` <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> ``` 2. 创建Excel文件 使用POI创建Excel文件,可以使用HSSFWorkbookXSSFWorkbook类。HSSFWorkbook适用于.xls格式的文件,XSSFWorkbook适用于.xlsx格式的文件。 ``` // 创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表 HSSFSheet sheet = workbook.createSheet("Sheet1"); // 创建行 HSSFRow row = sheet.createRow(); // 创建单元格 HSSFCell cell = row.createCell(); // 设置单元格的值 cell.setCellValue("Hello World"); ``` 3. 导出Excel文件 使用Spring Boot的ResponseEntity将Excel文件导出到浏览器。 ``` // 设置响应头 response.setHeader("Content-Disposition", "attachment;filename=test.xls"); response.setContentType("application/vnd.ms-excel"); // 将Excel文件写入响应流 workbook.write(response.getOutputStream()); ``` 完整的代码示例: ``` @GetMapping("/export") public ResponseEntity<byte[]> export() throws IOException { // 创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表 HSSFSheet sheet = workbook.createSheet("Sheet1"); // 创建行 HSSFRow row = sheet.createRow(); // 创建单元格 HSSFCell cell = row.createCell(); // 设置单元格的值 cell.setCellValue("Hello World"); // 设置响应头 HttpHeaders headers = new HttpHeaders(); headers.setContentDispositionFormData("attachment", "test.xls"); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); // 将Excel文件写入响应流 ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.OK); } ```

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值