HExcel,一个简单通用的导入导出Excel工具类

  前言

  日常开发中,Excel的导出、导入可以说是最常见的功能模块之一,一个通用的、健壮的的工具类可以节省大量开发时间,让我们把更多精力放在业务处理上中

  之前我们也写了一个Excel的简单导出,甚至可以不依赖poi,还扩展了纯前端导出Excel!详情请戳:《POI导出Excel 》《踹掉后端,前端导出Excel》,遗憾的是这些导出并不支持复杂表头

  HExcel,一个简单通用的导入导出Excel工具类
    1、支持导出复杂表头(支持表头单元格水平合并、垂直合并,支持表头单元格个性化样式)
    2、支持导入读取sheet数据(只需要提供title与key的关系,不需要管列的顺序)

  代码思路都在代码注释里,感兴趣的自己看注释

 

  PS:依赖 poi 以及 hutool

<!-- POI -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>
<!-- hutool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.4</version>
</dependency>

 

  先睹为快

   表头目前支持以下属性,可自行扩展:

title  标题
key  key
width  宽度
align 对齐方式
background-color  背景颜色(POI的IndexedColors)
color  字体颜色(POI的IndexedColors)
children  子级表头

 

  导出

  代码

//获取HExcel实例
HExcel hExcel1 = HExcel.newInstance();

//数据,一般是查数据库,经过数据处理生成
List<Map<String, Object>> dataList = new ArrayList<>();
HashMap<String, Object> date1 = new HashMap<>();
date1.put("user_name","张三");
date1.put("sex","男");
date1.put("age",20);
date1.put("yu_wen",90);
date1.put("ying_yu",0);
date1.put("shu_xue",85);
date1.put("wu_li",80);
date1.put("total",255);
dataList.add(date1);

HashMap<String, Object> date2 = new HashMap<>();
date2.put("user_name","李四");
date2.put("sex","女");
date2.put("age",18);
date2.put("yu_wen",81);
date2.put("ying_yu",0);
date2.put("shu_xue",90);
date2.put("wu_li",70);
date2.put("total",241);
dataList.add(date2);


//如果是固定表头数据,可以在项目资源文件夹下面新建个json文件夹,用来保存表头json数据,方便读、写
//JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json"));

//如果是动态表头数据,直接把json字符串写在代码里,方便动态生成表头数据

//表头
String sheetName = "学生成绩单";
JSONArray headers = JSONUtil.parseArray("" +
        "[\n" +
        "    {\n" +
        "        \"title\":\""+sheetName+"\",\n" +
        "        \"children\":[\n" +
        "            {\n" +
        "                \"title\":\"日期:"+DateUtil.today()+"\",\n" +
        "                \"align\":\"right\",\n" +
        "                \"children\":[\n" +
        "                    {\n" +
        "                        \"title\":\"姓名\",\n" +
        "                        \"key\":\"user_name\",\n" +
        "                    },\n" +
        "                    {\n" +
        "                        \"title\":\"语文\",\n" +
        "                        \"key\":\"yu_wen\",\n" +
        "                    },\n" +
        "                    {\n" +
        "                        \"title\":\"数学\",\n" +
        "                        \"key\":\"shu_xue\",\n" +
        "                    },\n" +
        "                    {\n" +
        "                        \"title\":\"总分\",\n" +
        "                        \"key\":\"total\",\n" +
        "                        \"background-color\":17,\n" +
        "                        \"color\":10,\n" +
        "                        \"width\":30,\n" +
        "                    },\n" +
        "                ]\n" +
        "            },\n" +
        "        ]\n" +
        "    },\n" +
        "]" +
        "");
//生成sheet
hExcel1.buildSheet(sheetName, headers, dataList);


//保存成File文件
hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls");

//关闭对象
hExcel1.close();

  效果

 

  导入

  需要导入的Excel文件

   代码

//需要设置title与key的关系
JSONObject headerTitleKey = new JSONObject("" +
        "{\n" +
        "    \"姓名\":\"user_name\",\n" +
        "    \"语文\":\"yu_wen\",\n" +
        "    \"数学\":\"shu_xue\",\n" +
        "    \"总分\":\"total\",\n" +
        "}" +
        "");

//根据Excel文件,获取HExcel实例
HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls"));

//根据title-key关系,读取指定位置的sheet数据
List<Map<String, Object>> sheetList = hExcel2.readSheet(2, 3, headerTitleKey);

//打印sheetList数据
for (Map<String, Object> map : sheetList) {
    System.out.println(map.toString());
}

//关闭对象
hExcel2.close();

  效果

  {user_name=张三, yu_wen=90, shu_xue=85, total=255}
  {user_name=李四, yu_wen=81, shu_xue=90, total=241}

 

  完整代码

package cn.huanzi.qch.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * HExcel,一个简单通用的导入导出Excel工具类
 * 1、支持复杂表头导出(支持表头单元格水平合并、垂直合并,支持表头单元格个性化样式)
 * 2、支持导入读取sheet数据(需要提供表头数据)
 *
 * PS:依赖 poi 以及 hutool
 *
 * 详情请戳:https://www.cnblogs.com/huanzi-qch/p/17797355.html
 */
public class HExcel {

    /**
     * 获取一个HExcel实例,并初始化空Workbook对象
     */
    public static HExcel newInstance(){
        HExcel hExcelUtil = new HExcel();
        hExcelUtil.hSSFWorkbook = new HSSFWorkbook();
        return hExcelUtil;
    }

    /**
     * 获取一个HExcel实例,并根据excelFile初始化Workbook对象
     */
    public static HExcel newInstance(File excelFile){
        HExcel hExcelUtil = new HExcel();
        try {
            hExcelUtil.hSSFWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
        } catch (IOException e) {
            throw new RuntimeException("【HExcel】 根据excelFile初始化Workbook对象异常",e);
        }
        return hExcelUtil;
    }

    /**
     * 导入并读取Excel
     *
     * @param sheetIndex 需要读取的sheet下标,从0开始
     * @param headers 表头
     * @return 返回数据集合
     */
    public <T> ArrayList<T> readSheet(int sheetIndex,JSONArray headers,Class<T> clazz){
        //title与key的关系json对象headers = {JSONArray@2046}  size = 1
        JSONObject headerTitleKey = this.getLastHeader(headers);
        int firstDataRow = headerTitleKey.getInt("header-depth");

        //最终返回的数据集合
        ArrayList<T> list = new ArrayList<>();

        //获取sheet
        HSSFSheet sheet = this.hSSFWorkbook.getSheetAt(sheetIndex);

        //获取title与col的对应关系
        HashMap<Integer, String> headerMap = new HashMap<>();
        int lastCellNum = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            for (int j = firstDataRow-1; j >=0 ; j--) {
                HSSFCell cell = sheet.getRow(j).getCell(i);
                if(cell != null && !"".equals(cell.getStringCellValue())){
                    String title = cell.getStringCellValue();
                    headerMap.put(i,title);
                    break;
                }
            }
        }

        //获取数据
        for (int i = firstDataRow; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject object = new JSONObject();
            for (int j = 0; j < lastCellNum; j++) {
                String title = headerMap.get(j);
                String key = headerTitleKey.getStr(title);

                if(key != null && !"".equals(key)){
                    HSSFCell cell = row.getCell(j);
                    Object value;
                    if(cell.getCellType().toString().equals("NUMERIC")){//数字类型
                        value = cell.getNumericCellValue();
                    }else{//字符串类型
                        value = cell.getStringCellValue();
                    }
                    object.set(key,value);
                }
            }
            list.add(object.toBean(clazz));
        }



        return list;
    }

    /**
     * 构造一个sheet,以及生成复杂表头、表数据
     *
     * @param sheetName sheet名称
     * @param headers 复杂表头json数组对象
     * @param dataLists 表数据集合
     * @return HExcel
     */
    public <T> HExcel buildSheet(String sheetName, JSONArray headers, List<T> dataLists) {
        //建立新的sheet对象
        HSSFSheet sheet = this.hSSFWorkbook.getSheet(sheetName);
        if(sheet == null){
            sheet = this.hSSFWorkbook.createSheet(sheetName);//设置表单名
        }

        //生成复杂表头
        int row = 0;//当前行
        int col = 0;//当前列
        HashMap<String, Object> hashMap = createHeader(sheet,row,col,headers);
        ArrayList<JSONObject> headerList = (ArrayList<JSONObject>) hashMap.get("headerList");
        row = (int) hashMap.get("maxRow");

        //取出水平合并区域数据
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        //垂直合并,单元格为空,且不属于水平合并区域
        //这里row-1是因为,生成所有表头结束后,maxRow比最大行+1,
        for (int i = 0; i < headerList.size(); i++) {
            for (int j = 0; j <= row-1; j++) {
                boolean flag = true;

                //单元格不为空
                HSSFCell cell = sheet.getRow(j).getCell(i);
                if(cell != null){
                    continue;
                }
                //检查合并区域
                for (CellRangeAddress cellAddresses : cellRangeAddressList) {
                    int OldFirstRow = cellAddresses.getFirstRow();
                    int OldLastRow = cellAddresses.getLastRow();
                    int OldFirstCol = cellAddresses.getFirstColumn();
                    int OldLastCol = cellAddresses.getLastColumn();

                    //与合并区域重叠
                    if ((OldFirstRow >= j && OldLastRow <= j) && (OldFirstCol >= i && OldLastCol <= i)) {
                        flag = false;
                        break;
                    }
                }

                //满足条件,将上一个单元格与最后一个单元格合并
                if(flag){
                    mergedCell(sheet,j-1,row-1,i,i);
                    break;
                }
            }
        }

        //开始填充数据
        HSSFCellStyle dataStyle = createDataStyle(sheet);
        for (Object object : dataLists) {
            Map<String, Object> map = BeanUtil.beanToMap(object);

            //创建内容行
            HSSFRow dataHSSFRow = sheet.createRow(row);
            //隐藏行
            //dataHSSFRow.setZeroHeight(true);
            for (int i = 0; i < headerList.size(); i++) {
                JSONObject header = headerList.get(i);
                String key = header.getStr("key");
                Object val = map.get(key);

                //当前列是否设置为下拉框,用于限定用户输入内容
                List<String> dropDownDataList = header.getBeanList("drop-down", String.class);
                if(dropDownDataList != null && dropDownDataList.size() > 0){
                    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
                    DataValidationConstraint dataValidationConstraint = dataValidationHelper.createExplicitListConstraint(dropDownDataList.toArray(new String[] {}));
                    DataValidation dataValidation = dataValidationHelper.createValidation(dataValidationConstraint, new CellRangeAddressList(row, row, i, i));
                    sheet.addValidationData(dataValidation);

                    //数据与下拉框内容不匹配时,置空
                    if(!dropDownDataList.contains(String.valueOf(val))){
                        val = null;
                    }
                }

                //如果是隐藏列,取消自动换行
                Object hide = header.get("hide");
                if(hide != null && (boolean)hide){
                    dataStyle.setWrapText(false);
                }

                createCell(dataHSSFRow, i, dataStyle, val == null ? "" : val);
            }
            row++;
        }

        return this;
    }

    /**
     * 保存成File文件
     *
     * @param path 完整文件路径+文件名
     */
    public void toFile(String path) {
        //try-catch语法糖
        try (FileOutputStream out = new FileOutputStream(path);){
            this.hSSFWorkbook.write(out);
        }catch (IOException e){
            throw new RuntimeException("【HExcel】 Workbook对象文件流写入File异常",e);
        }
    }

    /**
     * 保存到HttpServletResponse
     *
     * @param fileName 文件名
     * @param response HttpServletResponse对象
     */
    public void toHttpServletResponse(String fileName, HttpServletResponse response) {
        //try-catch语法糖
        try (ServletOutputStream outputStream = response.getOutputStream();){
            response.setHeader("Accept-Ranges", "bytes");
            response.setHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
            response.setContentType("application/octet-stream");
            this.hSSFWorkbook.write(outputStream);
        }catch (Exception e){
            throw new RuntimeException("【HExcel】 Workbook对象文件流写入Response异常",e);
        }
    }

    /**
     * 关闭Workbook
     */
    public void close(){
        try{
            //关闭Workbook
            this.hSSFWorkbook.close();
        } catch (Exception e) {
            throw new RuntimeException("【HExcel】 关闭Workbook异常",e);
        }
    }


    /*          已下设置私有,对外隐藏实现细节           */

    /**
     * Workbook对象
     */
    private HSSFWorkbook hSSFWorkbook;

    /**
     * 构造表头
     *
     * @param sheet sheet
     * @param row 当前操作行
     * @param col 当前操作列
     * @param headers 表头数据
     * @return 返回一个map对象,供上级表头获取最新当前操作行、列、key集合
     */
    private HashMap<String,Object> createHeader(HSSFSheet sheet, int row, int col, JSONArray headers){
        //最终返回对象
        HashMap<String, Object> hashMap = new HashMap<>();

        //key集合
        ArrayList<JSONObject> headerList = new ArrayList<>();

        HSSFWorkbook wb = sheet.getWorkbook();
        HSSFRow headerHSSFRow = sheet.getRow(row);
        if(headerHSSFRow == null){
            headerHSSFRow = sheet.createRow(row);
        }
        for (Object object : headers) {
            JSONObject header = (JSONObject) object;
            String title = (String) header.get("title");
            String key = (String) header.get("key");
            Object width = header.get("width");
            Object hide = header.get("hide");
            Object align = header.get("align");
            Object backgroundColor = header.get("background-color");
            Object color = header.get("color");
            Object children = header.get("children");

            //单元格样式
            HSSFCellStyle headerStyle = createHeaderStyle(sheet);

            //自定义单元格背景色
            if(backgroundColor != null){
                headerStyle.setFillForegroundColor(Short.parseShort(backgroundColor+""));
            }

            //自定义单元格字体颜色
            if(color != null){
                headerStyle.getFont(wb).setColor(Short.parseShort(color+""));
            }

            //默认单元格宽度,20
            sheet.setColumnWidth(col, 20 * 256);
            if(width != null){
                //自定义单元格宽度
                sheet.setColumnWidth(col, (int) width * 256);
            }

            //隐藏列,设置宽度0
            if(hide != null && (boolean)hide){
                //sheet.setColumnWidth(col, 0);
                sheet.setColumnHidden(col,true);
            }

            //默认水平对齐方式(水平居中)
            if(align != null){
                //自定义水平对齐方式
                HorizontalAlignment alignment;
                switch (String.valueOf(align).toUpperCase()){
                    case "LEFT":
                        alignment = HorizontalAlignment.LEFT;
                        break;
                    case "RIGHT":
                        alignment = HorizontalAlignment.RIGHT;
                        break;
                    default:
                        alignment = HorizontalAlignment.CENTER;
                        break;
                }
                headerStyle.setAlignment(alignment);
            }

            //System.out.println(title + " " + key + " " + row + " " + col);

            //生成单元格同时设置内容
            createCell(headerHSSFRow, col, headerStyle, title);

            //无子级表头
            if(children == null){
                //保留顺序,方便后面设置数据
                headerList.add(header);

                //当前列+1
                col++;
            }
            //有子级表头
            else{
                //递归生成子级表头前,保存父级表头col,用于水平合并
                int firstCol = col;

                //递归调用
                HashMap<String, Object> hashMap1 = createHeader(sheet, row + 1, col, (JSONArray) children);

                //获取最新col、key集合
                col = (int) hashMap1.get("col");
                hashMap.put("maxRow",hashMap1.get("maxRow"));
                headerList.addAll((ArrayList<JSONObject>) hashMap1.get("headerList"));

                //水平合并,这里col-1是因为,生成子级表头结束后,col比最后一个下级表头+1,
                if(!(firstCol == col-1)){
                    mergedCell(sheet,row,row,firstCol,col-1);
                }
            }
        }

        //将数据设置到对象中,返回上一层
        hashMap.put("maxRow",(hashMap.get("maxRow") != null ? Integer.parseInt(hashMap.get("maxRow")+"") : 0) + 1);//最大行
        hashMap.put("row",row);//当前操作行
        hashMap.put("col",col);//当前操作列
        hashMap.put("headerList",headerList);//key集合

        return hashMap;
    }

    /**
     * 创建一个单元格
     *
     * @param hSSFRow 当前行对象
     * @param col 当前列
     * @param cellStyle 单元格样式对象
     * @param value 单元格内容
     */
    private void createCell(HSSFRow hSSFRow, int col, HSSFCellStyle cellStyle, Object value) {
        HSSFCell cell = hSSFRow.createCell(col);  // 创建单元格
        cell.setCellStyle(cellStyle); // 设置单元格样式

        // 设置值
        String valueStr = String.valueOf(value);
        if  (NumberUtil.isNumber(valueStr)){//数字
            cell.setCellValue(NumberUtil.parseDouble(NumberUtil.toStr(NumberUtil.parseNumber(valueStr))));
        } else if  (value instanceof Date){//日期
            cell.setCellValue(DateUtil.format((Date) value, DatePattern.NORM_DATETIME_FORMAT));
        }else{//字符串
            cell.setCellValue(valueStr);
        }

    }

    /**
     * 构造表头、数据样式
     *
     * @param sheet sheet
     * @return 返回一个单元格样式对象
     */
    private HSSFCellStyle createHeaderStyle(HSSFSheet sheet){
        HSSFWorkbook wb = sheet.getWorkbook();

        //表头的样式
        HSSFCellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //列名的字体
        HSSFFont dataFont = wb.createFont();
        dataFont.setFontHeightInPoints((short) 12);
        dataFont.setFontName("新宋体");
        headerStyle.setFont(dataFont);// 把字体 应用到当前样式
        headerStyle.setWrapText(true);//自动换行
        //填充样式,前景色、天空蓝
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);

        return headerStyle;
    }
    private HSSFCellStyle createDataStyle(HSSFSheet sheet){
        HSSFWorkbook wb = sheet.getWorkbook();

        //内容的样式
        HSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //内容的字体
        HSSFFont font3 = wb.createFont();
        font3.setFontHeightInPoints((short) 12);
        font3.setFontName("新宋体");
        dataStyle.setFont(font3);// 把字体 应用到当前样式
        dataStyle.setWrapText(true);//自动换行
        //默认无填充
        dataStyle.setFillPattern(FillPatternType.NO_FILL);
        // 设置边框
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setBorderTop(BorderStyle.THIN);

        return dataStyle;
    }

    /**
     * 合并单元格
     *
     * @param sheet sheet
     * @param firstRow 起始行
     * @param lastRow 结束行
     * @param firstCol 起始列
     * @param lastCol 结束列
     */
    private void mergedCell(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
        //一个单元格无需合并,例如:[0,0,0,0]
        if(firstRow == lastRow && firstCol == lastCol){
            return;
        }

        //先取出合并前的单元格样式
        HSSFCellStyle cellStyle = sheet.getRow(firstRow).getCell(firstCol).getCellStyle();

        //合并
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

        //解决合并后的边框等样式问题
        int first;
        int end;
        //垂直合并
        if(firstCol == lastCol){
            first = firstRow;
            end = lastRow+1;

            for (int i = first; i < end; i++) {
                HSSFRow row = sheet.getRow(i);
                if(row == null){
                    row = sheet.createRow(i);
                }
                HSSFCell cell = row.getCell(firstCol);
                if(cell == null){
                    cell = row.createCell(firstCol);
                }
                cell.setCellStyle(cellStyle);
            }
        }
        //水平合并
        else{
            first = firstCol;
            end = lastCol+1;

            for (int i = first; i < end; i++) {
                HSSFRow row = sheet.getRow(firstRow);
                if(row == null){
                    row = sheet.createRow(firstRow);
                }
                HSSFCell cell = row.getCell(i);
                if(cell == null){
                    cell = row.createCell(i);
                }
                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 递归获取最末端表头、以及深度
     * @param headers 表头
     * @return 末端表头,以及额外设置多一个表头深度属性
     */
    private JSONObject getLastHeader(JSONArray headers){
        return this.getLastHeader(headers,1);
    }
    private JSONObject getLastHeader(JSONArray headers,int currentRow){
        JSONObject headerTitleKey = new JSONObject();
        for (Object o : headers) {
            JSONObject header = (JSONObject) o;
            String title = (String) header.get("title");
            String key = (String) header.get("key");
            Object children = header.get("children");

            if(children != null){
                int lastRow = currentRow;
                headerTitleKey.putAll(this.getLastHeader((JSONArray)children,++currentRow));
                currentRow = lastRow;
            }

            if (key == null) {
                continue;
            }

            headerTitleKey.set(title,key);
        }

        //额外设置表头深度属性
        headerTitleKey.set("header-depth", Math.max(currentRow,headerTitleKey.getInt("header-depth") != null ? headerTitleKey.getInt("header-depth") : currentRow));
        return headerTitleKey;
    }

}
HExcel.java

 

   完整main测试

 

    /**
     * main测试学生类
     */
    static class Student {
        private String userId; //id
        private String userName; //姓名
        private String sex; //性别
        private Integer age; //年龄
        private Date date; //入学时间
        private Double yuWen; //语文成绩
        private Double yingYu; //英语成绩
        private Double shuXue; //数学成绩
        private Double wuLi; //物理成绩
        private Double total; //总分
        private String level; //等级

        public Student() {
        }

        public String getUserId() {
            return userId;
        }

        public void setUserId(String userId) {
            this.userId = userId;
        }

        public String getUserName() {
            return userName;
        }

        public void setUserName(String userName) {
            this.userName = userName;
        }

        public String getSex() {
            return sex;
        }

        public void setSex(String sex) {
            this.sex = sex;
        }

        public Integer getAge() {
            return age;
        }

        public void setAge(Integer age) {
            this.age = age;
        }

        public Date getDate() {
            return date;
        }

        public void setDate(Date date) {
            this.date = date;
        }

        public Double getYuWen() {
            return yuWen;
        }

        public void setYuWen(Double yuWen) {
            this.yuWen = yuWen;
        }

        public Double getYingYu() {
            return yingYu;
        }

        public void setYingYu(Double yingYu) {
            this.yingYu = yingYu;
        }

        public Double getShuXue() {
            return shuXue;
        }

        public void setShuXue(Double shuXue) {
            this.shuXue = shuXue;
        }

        public Double getWuLi() {
            return wuLi;
        }

        public void setWuLi(Double wuLi) {
            this.wuLi = wuLi;
        }

        public Double getTotal() {
            return total;
        }

        public void setTotal(Double total) {
            this.total = total;
        }

        public String getLevel() {
            return level;
        }

        public void setLevel(String level) {
            this.level = level;
        }

        @Override
        public String toString() {
            return "Student{" +
                    "userId='" + userId + '\'' +
                    ", userName='" + userName + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    ", date=" + date +
                    ", yuWen=" + yuWen +
                    ", yingYu=" + yingYu +
                    ", shuXue=" + shuXue +
                    ", wuLi=" + wuLi +
                    ", total=" + total +
                    ", level='" + level + '\'' +
                    '}';
        }
    }


  public static void main(String[] args) {
        //获取HExcel实例
        HExcel hExcel1 = HExcel.newInstance();

        //数据,一般是查数据库,经过数据处理生成
        List<Student> dataList = new ArrayList<>();

        Student data1 = new Student();
        data1.setUserId("asdfasdffadfasdfasdfsdfasdfasdfadfasdfasdf");
        data1.setUserName("张三");
        data1.setSex("男");
        data1.setAge(20);
        data1.setDate(new Date());
        data1.setYuWen(90.500);
        data1.setYingYu(0.0);
        data1.setShuXue(85.123);
        data1.setWuLi(80.000);
        data1.setTotal(255.0);
        data1.setLevel("A");
        dataList.add(data1);

        Student data2 = new Student();
        data2.setUserId("222");
        data2.setUserName("李四");
        data2.setSex("女");
        data2.setAge(18);
        data2.setDate(new Date());
        data2.setYuWen(81.0);
        data2.setYingYu(0.0);
        data2.setShuXue(90.0);
        data2.setWuLi(70.0);
        data2.setTotal(241.0);
        dataList.add(data2);


        //如果是固定表头数据,可以在项目资源文件夹下面新建个json文件夹,用来保存表头json数据,方便读、写
        //JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json"));

        //如果是动态表头数据,直接把json字符串写在代码里,方便动态生成表头数据

        //表头
        String sheetName = "学生成绩单";
        JSONArray headers = JSONUtil.parseArray("" +
                "[\n" +
                "    {\n" +
                "        \"title\":\""+sheetName+"\",\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"title\":\"日期:"+DateUtil.today()+"\",\n" +
                "                \"align\":\"right\",\n" +
                "                \"children\":[\n" +
                "                    {\n" +
                "                        \"title\":\"隐藏列,用户id\",\n" +
                "                        \"key\":\"userId\",\n" +
                "                        \"hide\":true,\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"姓名\",\n" +
                "                        \"key\":\"userName\",\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"入学时间\",\n" +
                "                        \"key\":\"date\",\n" +
                "                        \"width\":30,\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"语文\",\n" +
                "                        \"key\":\"yuWen\",\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"数学\",\n" +
                "                        \"key\":\"shuXue\",\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"总分\",\n" +
                "                        \"key\":\"total\",\n" +
                "                        \"background-color\":17,\n" +
                "                        \"color\":10,\n" +
                "                        \"width\":30,\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"考试成绩等级\",\n" +
                "                        \"key\":\"level\",\n" +
                "                        \"width\":10,\n" +
                "                        \"background-color\":25,\n" +
                "                        \"color\":5,\n" +
                "                        \"drop-down\":[\n" +
                "                            \"A\",\n" +
                "                            \"B\",\n" +
                "                            \"C\",\n" +
                "                            \"D\",\n" +
                "                        ],\n" +
                "                    },\n" +
                "                ]\n" +
                "            },\n" +
                "        ]\n" +
                "    },\n" +
                "]" +
                "");
        //生成sheet
        hExcel1.buildSheet(sheetName, headers, dataList);

        //表头
        JSONArray headers2 = JSONUtil.parseArray("" +
                "[\n" +
                "    {\n" +
                "        \"title\":\"姓名\",\n" +
                "        \"key\":\"userName\",\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"学科成绩\",\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"title\":\"语文\",\n" +
                "                \"key\":\"yuWen\",\n" +
                "            },\n" +
                "            {\n" +
                "                \"title\":\"数学\",\n" +
                "                \"key\":\"shuXue\",\n" +
                "            },\n" +
                "        ]\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"总分\",\n" +
                "        \"key\":\"total\",\n" +
                "        \"align\":\"right\",\n" +
                "        \"background-color\":17,\n" +
                "        \"color\":10,\n" +
                "        \"width\":30\n," +
                "    },\n" +
                "]" +
                "");
        //生成sheet
        hExcel1.buildSheet("学生成绩单2", headers2, dataList);

        //表头
        JSONArray headers3 = JSONUtil.parseArray("" +
                "[\n" +
                "    {\n" +
                "        \"title\":\"姓名\",\n" +
                "        \"key\":\"userName\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"性别\",\n" +
                "        \"key\":\"sex\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"年龄\",\n" +
                "        \"key\":\"age\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"学科成绩\",\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"title\":\"语言类\",\n" +
                "                \"children\":[\n" +
                "                    {\n" +
                "                        \"title\":\"语文\",\n" +
                "                        \"key\":\"yuWen\",\n" +
                "                        \"background-color\":7,\n" +
                "                        \"color\":5,\n" +
                "                    },\n" +
                "                  ]\n" +
                "            },\n" +
                "            {\n" +
                "                \"title\":\"科学类\",\n" +
                "                \"background-color\":10,\n" +
                "                \"children\":[\n" +
                "                    {\n" +
                "                        \"title\":\"数学\",\n" +
                "                        \"key\":\"shuXue\"\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"物理\",\n" +
                "                        \"key\":\"wuLi\"\n" +
                "                    }\n" +
                "                 ]\n" +
                "            },\n" +
                "        ]\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"总分\",\n" +
                "        \"key\":\"total\",\n" +
                "        \"align\":\"right\",\n" +
                "        \"background-color\":17,\n" +
                "        \"color\":10,\n" +
                "        \"width\":30\n," +
                "    },\n" +
                "]"+
                "");
        //生成sheet
        hExcel1.buildSheet("学生成绩单3", headers3, dataList);

        //表头
        JSONArray headers4 = JSONUtil.parseArray("" +
                "[\n" +
                "    {\n" +
                "        \"title\":\"姓名\",\n" +
                "        \"key\":\"userName\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"性别\",\n" +
                "        \"key\":\"sex\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"年龄\",\n" +
                "        \"key\":\"age\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"学科成绩\",\n" +
                "        \"children\":[\n" +
                "            {\n" +
                "                \"title\":\"语文\",\n" +
                "                \"key\":\"yuWen\",\n" +
                "            },\n" +
                "            {\n" +
                "                \"title\":\"科学类\",\n" +
                "                \"background-color\":10,\n" +
                "                \"children\":[\n" +
                "                    {\n" +
                "                        \"title\":\"数学\",\n" +
                "                        \"key\":\"shuXue\"\n" +
                "                    },\n" +
                "                    {\n" +
                "                        \"title\":\"物理\",\n" +
                "                        \"key\":\"wuLi\"\n" +
                "                    }\n" +
                "                 ]\n" +
                "            },\n" +
                "            {\n" +
                "                \"title\":\"英语\",\n" +
                "                \"key\":\"yingYu\",\n" +
                "            },\n" +
                "        ]\n" +
                "    },\n" +
                "    {\n" +
                "        \"title\":\"总分\",\n" +
                "        \"key\":\"total\",\n" +
                "        \"align\":\"right\",\n" +
                "        \"background-color\":17,\n" +
                "        \"color\":10,\n" +
                "        \"width\":30\n" +
                "      \n" +
                "    }\n" +
                "]"+
                "");
        //生成sheet
        hExcel1.buildSheet("学生成绩单4", headers4, dataList);

        //保存成File文件
        hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls");
        System.out.println("导出完成!\n");

        //关闭对象
        hExcel1.close();

        //导入

        //根据Excel文件,获取HExcel实例
        HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls"));

        //根据表头,读取指定位置的sheet数据,打印sheetList数据
        System.out.println("导入读取完成!");
        for (Student map : hExcel2.readSheet(0, headers, Student.class)) {
            System.out.println(map.toString());
        }
        System.out.println("导入读取完成!");
        for (Student map : hExcel2.readSheet(1, headers2, Student.class)) {
            System.out.println(map.toString());
        }
        System.out.println("导入读取完成!");
        for (Student map : hExcel2.readSheet(2, headers3, Student.class)) {
            System.out.println(map.toString());
        }
        System.out.println("导入读取完成!");
        for (Student map : hExcel2.readSheet(3, headers4, Student.class)) {
            System.out.println(map.toString());
        }

        //关闭对象
        hExcel2.close();

    }

  控制台打印

导出完成!

导入读取完成!
Student{userId='asdfasdffadfasdfasdfsdfasdfasdfadfasdfasdf', userName='张三', sex='null', age=null, date=Sat Jun 15 13:12:18 CST 2024, yuWen=90.5, yingYu=null, shuXue=85.123, wuLi=null, total=255.0, level='A'}
Student{userId='222.0', userName='李四', sex='null', age=null, date=Sat Jun 15 13:12:18 CST 2024, yuWen=81.0, yingYu=null, shuXue=90.0, wuLi=null, total=241.0, level=''}
导入读取完成!
Student{userId='null', userName='张三', sex='null', age=null, date=null, yuWen=90.5, yingYu=null, shuXue=85.123, wuLi=null, total=255.0, level='null'}
Student{userId='null', userName='李四', sex='null', age=null, date=null, yuWen=81.0, yingYu=null, shuXue=90.0, wuLi=null, total=241.0, level='null'}
导入读取完成!
Student{userId='null', userName='张三', sex='男', age=20, date=null, yuWen=90.5, yingYu=null, shuXue=85.123, wuLi=80.0, total=255.0, level='null'}
Student{userId='null', userName='李四', sex='女', age=18, date=null, yuWen=81.0, yingYu=null, shuXue=90.0, wuLi=70.0, total=241.0, level='null'}
导入读取完成!
Student{userId='null', userName='张三', sex='男', age=20, date=null, yuWen=90.5, yingYu=0.0, shuXue=85.123, wuLi=80.0, total=255.0, level='null'}
Student{userId='null', userName='李四', sex='女', age=18, date=null, yuWen=81.0, yingYu=0.0, shuXue=90.0, wuLi=70.0, total=241.0, level='null'}

  导出的Excel文件

 

 

  后记

  一个简单通用的导入导出Excel工具类暂时先记录到这,后续再进行补充

 

 

  补充

 

  2024-06-15更新

   导出新增隐藏列、支持单元格支持配置下拉框;优化导入功能;

 

posted @ 2023-10-30 11:30  huanzi-qch  阅读(1011)  评论(0编辑  收藏  举报