Java Excel 表格 导入、导出 工具类

工具类

/**
 * 表格 导入、导出 工具类
 *
 */
public class FileUtil {
    public static final String XLSX = "xlsx";
    public static final String XLS = "xls";

    /**
     * 得到表格中数据
     *
     * @return list
     */
    public static List<Map<String, Object>> getExcelData(CommonsMultipartFile file, String[] title) {
        List<Map<String, Object>> list = new ArrayList<>();
        String filename = file.getOriginalFilename();
        try {
            InputStream is = file.getInputStream();
            if (getFileSuffix(filename).equals(XLS)) {
                // xls 文件
                HSSFWorkbook wb = new HSSFWorkbook(is);
                HSSFSheet sheet = wb.getSheetAt(0);
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    HSSFRow row = sheet.getRow(i);
                    if (row == null) {
                        //如果行为空则跳出
                        continue;
                    }
                    Map<String, Object> map = new HashMap<>(title.length);
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        HSSFCell cell = row.getCell(j);
                        Object value = getHssfCell(cell);
                        if (value != null) {
                            map.put(title[j], value);
                        }
                    }
                    list.add(map);
                }
            } else if (getFileSuffix(filename).equals(XLSX)) {
                // xlsx 文件
                XSSFWorkbook exc = new XSSFWorkbook(is);
                XSSFSheet sheet = exc.getSheetAt(0);
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    XSSFRow row = sheet.getRow(i);
                    if (row == null) {
                        //如果行为空则跳出
                        continue;
                    }
                    Map<String, Object> map = new HashMap<>(title.length);
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        XSSFCell cell = row.getCell(j);
                        Object value = getXssfCell(cell);
                        if (value != null) {
                            map.put(title[j], value);
                        }
                    }
                    list.add(map);
                }
            }
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 返回单元格数据
     *
     * @param cell 表格单元格
     * @return 单元格值
     */
    private static Object getHssfCell(HSSFCell cell) {
        if (cell == null) {
            return null;
        }
        Object value = null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                // 字符串
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                // 布尔类型
                value = cell.getBooleanCellValue();
                break;
            default:
                break;
        }

        return value;
    }

    /**
     * @param cell xlsx
     * @return 单元格值
     */
    private static Object getXssfCell(XSSFCell cell) {
        if (cell == null) {
            return null;
        }
        Object val = null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    val = cell.getDateCellValue();
                } else {
                    val = cell.getNumericCellValue();
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                // 字符串
                val = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                // 布尔类型
                val = cell.getBooleanCellValue();
                break;
            default:
                break;
        }

        return val;
    }


    /**
     * @param resp     HttpServletResponse
     * @param strName  excel表格头部中文名称
     * @param data     写入的数据
     * @param mapKey   写入数据对应的实体名称
     * @param filename 导出的文件名称
     */
    public static void excelTemplate(HttpServletResponse resp, String[] strName, List<Map<String, String>> data, String[] mapKey, String filename) {
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建excel 头
        XSSFSheet sheet = wb.createSheet();
        CellStyle headStyle = headStyle(wb);
        CellStyle cellStyle = cellStyle(wb);
        // 设置第一行头标题
        setSheet(sheet, strName, headStyle);
        // 填充单元格数据
        setCallData(data, sheet, mapKey, cellStyle);
        try {
            resp.reset();
            resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
            resp.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param sheet   sheet
     * @param strName 头中文名称
     */
    private static void setSheet(XSSFSheet sheet, String[] strName, CellStyle headStyle) {
        // 创建行
        XSSFRow row = sheet.createRow(0);
        // 设置头标题
        for (int i = 0; i < strName.length; i++) {
            // 创建列
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            // 设置单元格类型为String
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strName[i]);
        }
    }

    /**
     * @param data   数据
     * @param sheet  sheet
     * @param mapKey map数据的key值
     */
    private static void setCallData(List<Map<String, String>> data, XSSFSheet sheet, String[] mapKey, CellStyle cellStyle) {
        // 添加数据
        for (int i = 0; i < data.size(); i++) {
            // 创建行
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < data.get(i).size(); j++) {
                // 创建单元格
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                // 设置类型为字符串
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(data.get(i).get(mapKey[j]));

            }
        }
    }

    /**
     * @param wb XSSFWorkbook
     * @return 表头样式
     */
    private static CellStyle headStyle(XSSFWorkbook wb) {
        // 创建样式
        CellStyle style = wb.createCellStyle();
        // 创建字体
        XSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setBold(true);
        font.setColor(Font.COLOR_RED);
        // 设置单元格样式 居中
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);
        return style;
    }

    /**
     * @param wb XSSFWorkbook
     * @return 单元格样式
     */
    private static CellStyle cellStyle(XSSFWorkbook wb) {
        // 创建样式
        CellStyle style = wb.createCellStyle();
        // 创建字体
        XSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        // 设置单元格样式 居中
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);
        return style;
    }

    /**
     * @param fileName 文件名称
     * @return 返回文件后缀
     */
    public static String getFileSuffix(String fileName) {
        return fileName.substring(fileName.lastIndexOf(".") + 1);
    }

}

测试

    @RequestMapping("/upload")
    public BaseResult uploadExcel(PageForm form) {
        CommonsMultipartFile file = form.getFile();
        String[] title = {"name", "age", "sex", "class"};
        List<Map<String, Object>> list = FileUtil.getExcelData(file, title);
        BaseResult rs = new BaseResult();
        rs.setData(list);
        return rs;

    }
    @RequestMapping("/downExcel")
    public void download(HttpServletResponse resp){
        String filename= "导出excel测试."+FileUtil.XLSX;
        String[] strName = {"姓名", "年龄"};
        String[] mapKey = {"name", "age"};
        List<Map<String, String>> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Map<String, String> map = new HashMap<>();
            map.put("name", "张三" + i);
            map.put("age", "10" + i);
            data.add(map);
        }
        FileUtil.excelTemplate(resp,strName,data,mapKey,filename);
    }