Poi Excel工具类

• 常量

public class CommonConstant {
    public static String SCIENTIFIC_NOTATION = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
    public static String NUMBER = "^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$";
}

• 工具类

package com.example.rbacdemo.util;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

import static com.example.rbacdemo.constant.CommonConstant.NUMBER;
import static com.example.rbacdemo.constant.CommonConstant.SCIENTIFIC_NOTATION;

/**
 * Excel import、export
 */
public class ExcelUtil {


    public static List<Map<String, String>> readExcelForStream(InputStream inputStream, Integer sheetIndex) throws Exception {
        List<Map<String, String>> dataList = new ArrayList<>();
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(sheetIndex);
        int maxRownum = sheet.getPhysicalNumberOfRows();
        Row firstRow = sheet.getRow(0);
        int maxColnum = firstRow.getPhysicalNumberOfCells();
        String[] columns = new String[maxColnum];
        for (int i = 0; i < maxRownum; i++) {
            Map<String, String> map = new HashMap<>(100);
            map = new LinkedHashMap<>();
            firstRow = sheet.getRow(i);
            if (firstRow != null) {
                String cellData;
                for (int j = 0; j < maxColnum; j++) {
                    cellData = getCellValue(firstRow.getCell(j));
                    if (StringUtils.isNotBlank(cellData)) {
                        //考虑科学计数法问题
                        Pattern pattern = Pattern.compile(SCIENTIFIC_NOTATION);
                        boolean isNumber = pattern.matcher(cellData).matches();
                        if (isNumber) {
                            BigDecimal cellValue_ = new BigDecimal(cellData);
                            cellData = cellValue_.toPlainString();
                            if (cellData.length() > 11) {
                                cellData = cellValue_.toString();
                            }
                        }
                        Pattern patternNum = Pattern.compile(NUMBER);
                        boolean isNum = patternNum.matcher(cellData).matches();
                        //非数字去除空格
                        if (!isNum) {
                            cellData = cellData.trim();
                        }
                    } else {
                        if (cellData != null) {
                            cellData = null;
                        }
                    }
                    if (i == 0) {
                        columns[j] = cellData;
                        map.put(columns[j], cellData);
                    } else {
                        map.put(columns[j], cellData);
                    }
                }
            } else {
                continue;
            }
            dataList.add(map);
        }
        return dataList;
    }

    public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) {
        List<Map<String, String>> dataList = new ArrayList<>();
        Workbook wb = ExcelUtil.createWorkBook(filePath);
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(sheetIndex);
            int maxRownum = sheet.getPhysicalNumberOfRows();
            Row firstRow = sheet.getRow(0);
            int maxColnum = firstRow.getPhysicalNumberOfCells();
            for (int i = 0; i < maxRownum; i++) {
                Map<String, String> map = new HashMap<>(100);
                if (i > 0) {
                    map = new LinkedHashMap<>();
                    firstRow = sheet.getRow(i);
                }
                if (firstRow != null) {
                    String cellData = null;
                    for (int j = 0; j < maxColnum; j++) {
                        cellData = getCellValue(firstRow.getCell(j));
                        if (i == 0) {
                            //columns[j] = cellData;
                        } else {
                            map.put(String.valueOf(j), cellData);
                        }
                    }
                } else {
                    break;
                }
                if (i > 0) {
                    dataList.add(map);
                }
            }
        }
        return dataList;
    }


    private static Workbook createWorkBook(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            //以下是判断数据类型
            switch (cell.getCellType()) {
                case _NONE:
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formater.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA:
                    break;
                case BLANK: //空值
                    cellValue = "";
                case ERROR:
                    cellValue = "非法字符";
                    break;
            }
        }
        return cellValue;
    }


    private static Object washWord(Object obj) {
        String words = "#N/A;NA";
        if (obj != null) {
            String str = obj.toString();
            if (words.contains(str)) {
                return "0";
            } else {
                if (str.startsWith("$") || str.endsWith("$")) {
                    str = str.replace("$", "").trim();
                }
            }
            return str;
        }
        return "";
    }


    /**
     * 用户信息导出类
     *
     * @param response   响应
     * @param fileName   文件名
     * @param columnList 每列的标题名
     * @param dataList   导出的数据
     */
    public static void export(HttpServletResponse response, String fileName, List<String> columnList, List<List<Object>> dataList) {
        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(response, fileName);
        try {
            //获取输出流
            os = response.getOutputStream();
            //内存中保留1000条数据，以免内存溢出，其余写入硬盘
            try (SXSSFWorkbook wb = new SXSSFWorkbook(1000)) {
                //获取该工作区的第一个sheet
                Sheet sheet1 = wb.createSheet("sheet1");
                int excelRow = 0;
                //创建标题行
                Row titleRow = sheet1.createRow(excelRow++);
                for (int i = 0; i < columnList.size(); i++) {
                    //创建该行下的每一列，并写入标题数据
                    Cell cell = titleRow.createCell(i);
                    cell.setCellValue(columnList.get(i));
                }
                //设置内容行
                if (dataList != null && dataList.size() > 0) {
                    //外层for循环创建行
                    for (int i = 0; i < dataList.size(); i++) {
                        Row dataRow = sheet1.createRow(excelRow++);
                        //内层for循环创建每行对应的列，并赋值
                        for (int j = 0; j < dataList.get(0).size(); j++) {
                            Cell cell = dataRow.createCell(j);
                            Object v = dataList.get(i).get(j);
                            if (v instanceof String) {
                                cell.setCellValue(v.toString());
                            } else if (v.toString().matches(NUMBER)) {
                                cell.setCellValue(Double.parseDouble(v.toString()));
                            }
                        }
                    }
                }
                //将整理好的excel数据写入流中
                wb.write(os);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭输出流
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
        设置浏览器下载响应头
     */
    private static 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=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}