java 导入导出excel

最新推荐文章于 2024-09-16 21:37:07 发布

u014723730

最新推荐文章于 2024-09-16 21:37:07 发布

阅读量254

点赞数 2

文章标签： java excel 开发语言

本文链接：https://blog.csdn.net/u014723730/article/details/141168341

版权

1.引入相关jar包

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.2.0</version>
        </dependency>

2.引入工具类

package com.chinaunicom.standard.product.ucenter.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelSelectListUtil {

    /**
     * 下拉框长度，超过该长度，需要新建sheet来存储
     */
    private static final Integer DROP_DOWN_LENGTH = 30;

    /**
     * 存储下拉框列表值的sheet名称
     */
    private static final String SHEET_NAME = "hidden_";

    /**
     * firstRow 開始行號 根据此项目，默认为2(下标0开始)
     * lastRow  根据此项目，默认为最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * strings 下拉内容, 当下拉框内容过多时，需要生成一个sheet来存储下拉列表值
     * */
    public static void selectList(Workbook workbook, int firstCol, int lastCol, String[] strings ){
        Sheet sheet = workbook.getSheetAt(0);
        if(strings.length >= DROP_DOWN_LENGTH){
            //创建下拉列表值存储工作表
            String hiddenName = SHEET_NAME + firstCol;
            Sheet hiddenSheet = workbook.createSheet(hiddenName);
            // 循环往该sheet中设置添加下拉列表的值
            for (int i = 0; i < strings.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell((int)0);
                cell.setCellValue(strings[i]);
            }
            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = hiddenSheet.getDataValidationHelper();
            DataValidationConstraint formulaListConstraint = dataValidationHelper.createFormulaListConstraint(hiddenName + "!$A$1:$A$" + strings.length);
            DataValidation dataValidation = dataValidationHelper.createValidation(formulaListConstraint, regions);
            // 将sheet设置为隐藏
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenName), true);
            sheet.addValidationData(dataValidation);
        }else{
            //  生成下拉列表
            //  只对(x，x)单元格有效
            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
            DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(strings);
            DataValidation dataValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
            //  对sheet页生效
            sheet.addValidationData(dataValidation);
        }

    }

    /**
     * firstRow 開始行號 根据此项目，默认为2(下标0开始)
     * lastRow  根据此项目，默认为最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * strings 下拉内容, 当下拉框内容过多时，需要生成一个sheet来存储下拉列表值
     * sheetIndex   sheet 序号
     * */
    public static void selectListSheet(Workbook workbook, int firstCol, int lastCol, String[] strings, int sheetIndex){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if(strings.length >= DROP_DOWN_LENGTH) {
            //创建下拉列表值存储工作表
            String hiddenName = SHEET_NAME + firstCol;
            Sheet hiddenSheet = workbook.createSheet(hiddenName);
            // 循环往该sheet中设置添加下拉列表的值
            for (int i = 0; i < strings.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell((int)0);
                cell.setCellValue(strings[i]);
            }
            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = hiddenSheet.getDataValidationHelper();
            DataValidationConstraint formulaListConstraint = dataValidationHelper.createFormulaListConstraint(hiddenName + "!$A$1:$A$" + strings.length);
            DataValidation dataValidation = dataValidationHelper.createValidation(formulaListConstraint, regions);
            // 将sheet设置为隐藏
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenName), true);
            sheet.addValidationData(dataValidation);
        }else{
            //  生成下拉列表
            //  只对(x，x)单元格有效
            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
            DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(strings);
            DataValidation dataValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
            //  对sheet页生效
            sheet.addValidationData(dataValidation);
        }

    }

    /**
     * 创建省市区sheet
     * @param workbook
     * @param firstCol
     * @param lastCol
     * @param strings
     * @param sheetIndex
     */
    public static void createAreaSheet(Workbook workbook, int firstCol, int lastCol, String[] strings, int sheetIndex, int firstRow) {

        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if(strings.length >= DROP_DOWN_LENGTH) {
            //创建下拉列表值存储工作表
            String hiddenName = SHEET_NAME + firstCol;
            Sheet hiddenSheet = workbook.createSheet(hiddenName);
            // 循环往该sheet中设置添加下拉列表的值
            for (int i = 0; i < strings.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell((int)0);
                cell.setCellValue(strings[i]);
            }
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = hiddenSheet.getDataValidationHelper();
            DataValidationConstraint formulaListConstraint = dataValidationHelper.createFormulaListConstraint(hiddenName + "!$A$1:$A$" + strings.length);
            DataValidation dataValidation = dataValidationHelper.createValidation(formulaListConstraint, regions);
            // 将sheet设置为隐藏
            workbook.setSheetHidden(workbook.getSheetIndex(hiddenName), true);
            sheet.addValidationData(dataValidation);
        } else {
            //  生成下拉列表
            //  只对(x，x)单元格有效
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, lastCol);
            DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
            DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(strings);
            DataValidation dataValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
            //  对sheet页生效
            sheet.addValidationData(dataValidation);

        }
    }

}

Excel导入导出工具类

package com.chinaunicom.standard.product.ucenter.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * Excel导入导出工具类
 *
 * @author shs-ggw
 * @date 2024-08-13
 */
public class ExcelToolUtils {

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 基于pojo类注解excel导出，带下拉框
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     * @param selectMap 下拉框
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response, Map<Integer, String[]> selectMap) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        for (Map.Entry<Integer, String[]> entry : selectMap.entrySet()) {
            int column = entry.getKey();
            String[] strings = entry.getValue();
            ExcelSelectListUtil.selectList(workbook, column, column, strings);
        }
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list           数据列表
     * @param title          表格内数据标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       导出时的excel名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 自定义excel导出
     *
     * @param title     标题
     * @param sheetName sheet名称
     * @param excelList 表头
     * @param list      列表数据
     * @param fileName  文件名称
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String title, String sheetName, List<ExcelExportEntity> excelList, List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, excelList, list);
        downLoadExcel(fileName, response, workbook);
    }


    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 0, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try(InputStream is = file.getInputStream()) {
            return importExcel(is, titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
//        params.setSaveUrl("/excel/");
        params.setNeedSave(false);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

}

3.具体实现

 public Object exportExcel(Long userId, HttpServletResponse response) {
        List<NewStaffDTO> list = new ArrayList<>();
        try {
            ExcelToolUtils.exportExcel(list, "新办卡员工信息", "新办卡员工信息", NewStaffDTO.class, String.valueOf(System.currentTimeMillis()), response);
        } catch (Exception e) {
            log.info("新办卡员工信息导入模板下载失败", e);
        }

        return GatewayResp.ok();
    }