poi处理excel

说明：

1. 通过poi的工具，导出或者导入excel

1.Excel的导出操作

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.UUID;
/**
 * 导出的excel，通过流操作写入到response的相相应中。
 */
   public static void exportTemplate(String eventName, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        String agent = request.getHeader("user-agent");
        try {
            if (agent != null && agent.toLowerCase().indexOf("firefox") > -1) {
                String event = eventName + ".xls";
                response.setHeader("content-Disposition", "attachment;filename=" + (new String(event.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1)));
            } else {
                response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode(eventName + System.currentTimeMillis() + ".xls", "utf-8"));
            }
        } catch (UnsupportedEncodingException e) {
            logger.error("log", e);
        }
        try {
            OutputStream out = response.getOutputStream();
            //将workbook写入
            wb.write(out);
            out.close();
        } catch (IOException e) {
            logger.error("log", e);
        }
    }
/**
     * 创建下拉列表选项(常规的简单下拉框)
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param col      起始列（从0开始）
     */
    public static void setHSSFValidation(HSSFSheet sheet, String[] values, int firstRow, int lastRow, int col) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, col, col);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }
    /**
     * 单元格添加下拉菜单(不限制菜单可选项个数)
     * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]
     *
     * @param @param workbook
     * @param @param tarSheet 目标单元格所在的sheet
     * @param @param menuItems 下拉菜单可选项数组
     * @param @param firstRow 第一个目标单元格所在的行号(从0开始)
     * @param @param lastRow 最后一个目标单元格所在的行(从0开始)
     * @param @param column 待添加下拉菜单的单元格所在的列(从0开始)
     */
    /* 通过添加影藏的sheet页。解决cell长度唱过255的限制！！ */
    public static void setLongHSSFValidation(HSSFWorkbook workbook, HSSFSheet tarSheet, String[] menuItems, int firstRow, int lastRow, int column) {
        //必须以字母开头，最长为31位
        String hiddenSheetName = "a" + UUID.randomUUID().toString().replace("-", "").substring(1, 31);
        //excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
        String formulaId = "form" + UUID.randomUUID().toString().replace("-", "");
        //用于存储 下拉菜单数据
        HSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        //存储下拉菜单项的sheet页不显示
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
        HSSFRow row = null;
        HSSFCell cell = null;
        //隐藏sheet中添加菜单数据
        for (int i = 0; i < menuItems.length; i++) {
            row = hiddenSheet.createRow(i);
            //隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
            cell = row.createCell(column);
            cell.setCellValue(menuItems[i]);
        }
        //创建"名称"标签，用于链接
        HSSFName namedCell = workbook.createName();
        namedCell.setNameName(formulaId);
        namedCell.setRefersToFormula(hiddenSheetName + "!A$1:A$" + menuItems.length);
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(tarSheet);
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaId);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);
        //添加菜单(将单元格与"名称"建立关联)
        HSSFDataValidation validation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        tarSheet.addValidationData(validation);
    }
        /**
     * 从调用addDropDownList后添加下拉菜单的Workbook中获取用户输入数据的shee列表
     * @param book
     * @return
     */
    public static List<HSSFSheet> getDataSheetInDropMenuBook(HSSFWorkbook book){
        return getUnHideSheets(book);
    }
    /**
     * 获取所有未隐藏的sheet
     * @param book
     * @return
     */
    public static List<HSSFSheet> getUnHideSheets(HSSFWorkbook book){
        List<HSSFSheet> ret = new ArrayList<HSSFSheet>();
        if(null == book){
            return ret;
        }
        int sheetCnt = book.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            if(!book.isSheetHidden(i)){
                ret.add(book.getSheetAt(i));
            }
        }
        return ret;
    }
    /**
     * 在实际的操作过程中，发现如果直接采用Hutool的ExcelReader工具，能够直接读取到下拉框内的值
     */
    //在类中添加pattern
    private Pattern excelPattern = Pattern.compile("\\.(xls)|(xlsx)");
    private Pattern excelBytePattern = Pattern.compile("(((?i)xls)|((?i)xlsx)|((?i)zip))$");
    public List<List<Object>> readeExcelInfo(MultipartFile excelFile){
        if (excelFile == null) {
            throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "上传内容不能为空");
        }
        Matcher matcher = excelPattern.matcher(excelFile.getOriginalFilename());
        if (!matcher.find()) {
            throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "文件后缀名不符,只能上传excel文件");
        }
        InputStream inputStream = null;
        try {
                String type = null;
                inputStream = excelFile.getInputStream();
                type = FileTypeUtil.getType(excelFile.getInputStream());
                Matcher matcherStream = excelBytePattern.matcher(type);
                if (!matcherStream.find()) {
                    throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "文件类型不符,只能上传excel文件");
                }
                ExcelReader reader = null;
                try {
                    reader = ExcelUtil.getReader(excelFile.getInputStream());   //Hutool提供的方法，直接从读取文件流
                } catch (IOException e) {
                    e.printStackTrace();
                }
                List<List<Object>> excelList = reader.read(1, reader.getRowCount());
                return excelList;
        }catch(IOException e) {
            throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "Excel文件流解析失败！");
        }
    }

2. Excel的导入

/**
 * 导入Excel之后，解析对象
 */
public Boolean importNodesExcel(MultipartFile excelFile) {
        if (excelFile == null) {
            throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "上传内容不能为空");
        }
        Matcher matcher = excelPattern.matcher(excelFile.getOriginalFilename());
        if (!matcher.find()) {
            throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "文件后缀名不符,只能上传excel文件");
        }
        InputStream inputStream = null;
        try {
            try {
                inputStream = excelFile.getInputStream();
            } catch (IOException e) {
                e.printStackTrace();
            }
            String type = null;
            try {
                type = FileTypeUtil.getType(excelFile.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
            Matcher matcherStream = excelBytePattern.matcher(type);
            if (!matcherStream.find()) {
                throw new ServiceException(UnicomResponseEnums.BAD_REQUEST.getCode(), "文件类型不符,只能上传excel文件");
            }
            ExcelReader reader = null;
            try {
                reader = ExcelUtil.getReader(excelFile.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
            List<List<Object>> excelList = reader.read(1, reader.getRowCount());
            // 用来记录导入的内容：
            List<Entity> nodeEntityList=new ArrayList<>();
            excelList.forEach(t -> {
                NodeEntity nodeEntity = new NodeEntity();
                nodeEntity.setId(Integer.valueOf(checkValue(t.get(0))));
                nodeEntity.setBId(checkValue(t.get(1)));
                nodeEntity.setName(checkValue(t.get(2)));
                nodeEntity.setCType(Integer.valueOf(checkValue(t.get(3))));
                nodeEntity.setAType(Integer.valueOf(checkValue(t.get(4))));
                nodeEntity.setEType(Integer.valueOf(checkValue(t.get(5))));
                nodeEntity.setDType(Integer.valueOf(checkValue(t.get(6))));
                String guid = IdUtil.simpleUUID();
                nodeEntity.setStandardNodeId(guid);
                nodeEntity.setCreateDate(System.currentTimeMillis());
                nodeEntity.setFlag(1);
                nodeEntityList.add(nodeEntity);
            });
            int num = nodeMapper.batchInsert(nodeEntityListlist);
            if (num < 1) {
                throw new ServiceException(UnicomResponseEnums.ADD_DATA.getCode(), "新增失败没有数据入库--->nodeEntity");
            }
            return true;
        }catch (ServiceException e){
            throw new ServiceException(UnicomResponseEnums.ADD_DATA.getCode(), e.getErrorMessage());
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    System.out.println("关闭inputStream流失败：{}", e.getMessage());
                }
            }
        }
    }
    /**
     * <检查单元格值,防止null>
     *
     * @author:
     * @date:
     * @return: java.lang.String
     */
    private String checkValue(Object value) {
        if (ObjectUtil.isNotEmpty(value)) {
            if("null".equals(value)) {
                return null;
            }
            if(value.toString().contains("-")) {
                return value.toString().split("-")[1];
            }else {
                return value.toString();
            }
        }
        return null;
    }