poi对excel进行写入

一、poi对excel进行写入背景：
在我们日常的开发中经常会有产品经理或者项目上有要求就是实现excel导出这个功能，今天花点时间做了这个功能。整个能力实现是基于poi框架进行的，在以后工作中可以直接使用和持续完善。
二、excel组件导出实现代码：
1.公用实体-excel组件配置

public class Config {

    private int titleRow; //标题行

    private int headRow; //头部行

    private int startRow; //开始行

    private String title; //标题

    public int getTitleRow() {
        return titleRow;
    }

    public void setTitleRow(int titleRow) {
        this.titleRow = titleRow;
    }

    public int getHeadRow() {
        return headRow;
    }

    public void setHeadRow(int headRow) {
        this.headRow = headRow;
    }

    public int getStartRow() {
        return startRow;
    }

    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

}

2.excel组件反射工具类

public class ExcelReflect {

    /**
      * 从object中获取execel注解字段的值
      * @param obj  object实体对象
      * @param excelFieldMap    excel字段集合
      * @return 值集合
     */
    public static Map<String, String> getFieldsValue(Object obj, Map<String, Object> excelFieldMap) {
        Map<String, String> valueMap = new HashedMap<String, String>();
        for (String fieldName : excelFieldMap.keySet()) {
            AppCloudExcel appCloudExcel = (AppCloudExcel) excelFieldMap.get(fieldName);
            valueMap.put(appCloudExcel.serial() + "", fieldExtValue(fieldName, obj).toString());
        }
        return valueMap;
    }

    /**
      * 获取class类中注解excel的字段集合
      * @param clazz    class类
      * @return excel字段集合
     */
    public static <T> Map<String, Object> getClassExcelFieldsList(Class<T> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Object> excelFieldMap = new HashedMap<String, Object>();
        for (int j = 0; j < fields.length; j++) {
            AppCloudExcel appCloudExcel = fields[j].getAnnotation(AppCloudExcel.class);
            if (null != appCloudExcel) {
                excelFieldMap.put(fields[j].getName(), fields[j].getAnnotation(AppCloudExcel.class));
            }
        }
        return excelFieldMap;

    }

    /**
      * list的map集合转换为list的object集合
      * @param listMap list的map集合
      * @param clazz    class类
      * @return list的object集合
     */
    @SuppressWarnings({ "unchecked" })
    public static <T> List<T> listMapToListObj(List<Map<String, String>> listMap, Class<T> clazz) {
        List<Object> objList = new ArrayList<>();
        try {
            Field[] fields = clazz.getDeclaredFields();
            Map<String, Field> excelFieldMap = new HashedMap<String, Field>();
            for (int j = 0; j < fields.length; j++) {
                int serial = fields[j].getAnnotation(AppCloudExcel.class).serial();
                excelFieldMap.put(serial + "", fields[j]);
            }

            for (Map<String, String> map : listMap) {
                Object obj = clazz.newInstance();
                for (String key : excelFieldMap.keySet()) {
                    String fieldValue = map.get(key);
                    Field field = excelFieldMap.get(key);
                    obj = fieldAssValue(obj, field, fieldValue);
                }
                objList.add(obj);
            }
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return (List<T>) objList;
    }

    /**
     * 根据字段名获取字段值
     * @param fieldName    字段名
     * @param obj  实体
     * @return 字段值
    */
    public static Object fieldExtValue(String fieldName, Object obj) {
        try {
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (fieldName.equals(field.getName())) {
                    Method method = obj.getClass().getMethod("get" + StringUtils.capitalize(fieldName));
                    return method.invoke(obj);
                }

            }
        } catch (SecurityException | IllegalArgumentException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 字段赋值
     * @param obj  实体对象
     * @param field    属性对象
     * @param value    属性值
     * @return
    */
    private static Object fieldAssValue(Object obj, Field field, Object value) {
        if (null == value || StringUtils.isBlank(value.toString())) {
            return obj;
        }
        try {
            Method method = null;
            //此处使用else if减少频繁判断，当条件满足则执行代码，其他else if将不再执行。
            if (field.getType() == String.class) { //string类型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), String.class);
                method.invoke(obj, String.valueOf(value));
            } else if (field.getType() == Long.class || field.getType() == long.class) { //long型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Long.class);
                method.invoke(obj, new Long(Long.parseLong(value.toString())));
            } else if (field.getType() == Integer.class || field.getType() == int.class) { //int型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Integer.class);
                method.invoke(obj, new Integer(Integer.parseInt(value.toString())));
            } else if (field.getType() == Double.class || field.getType() == double.class) { //double型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Double.class);
                method.invoke(obj, new Double(Double.parseDouble(value.toString())));
            } else if (field.getType() == Date.class) { //date型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Date.class);
                SimpleDateFormat smdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date date = smdf.parse(value.toString());
                method.invoke(obj, date);
            } else if (field.getType() == Boolean.class || field.getType() == boolean.class) { //boolean型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Boolean.class);
                method.invoke(obj, new Boolean(Boolean.parseBoolean(value.toString())));
            } else if (field.getType() == Short.class || field.getType() == short.class) { //short型
                method = obj.getClass().getMethod("set" + StringUtils.capitalize(field.getName()), Short.class);
                method.invoke(obj, new Short(Short.parseShort(value.toString())));
            }
        } catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException
                | ParseException e) {
            e.printStackTrace();
        }
        return obj;
    }
}

3.excel组件常量

public class ExcelConstant {

    public static final String XLSX = ".xlsx";

    public static final String XLS = ".xls";
}

4.excel注解

@Target({ ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
public @interface AppCloudExcel {

    //序号，用于和excel返回结果进行匹配
    public int serial();

    //excel表头显示名称，用于导出时使用
    public String headName();
}

5…xlsx类型的excel生成

public class XlsxConverter {

    private static SXSSFWorkbook wb;

    /**
      * xlsx类型excel转换
      * @param dataList 数据集合
      * @param filePath 文件路径
      * @param config   excel组件配置
      * @return excel路径
      * @throws FileNotFoundException   文件找不到异常
     */
    public <T> String converter(List<T> dataList, String filePath, Config config) throws FileNotFoundException {
        Map<String, Object> fieldMap = ExcelReflect.getClassExcelFieldsList(dataList.get(0).getClass());
        //1.获取workbook
        wb = new SXSSFWorkbook();
        //整个文件统一的样式对象
        CellStyle cellStyle = wb.createCellStyle();
        //2.创建sheet页
        SXSSFSheet sheet = wb.createSheet("系统数据");
        //3.创建标题行和列
        SXSSFRow titleRow = sheet.createRow(config.getTitleRow());
        //3.1设置行高
        titleRow.setHeight((short) (20 * 20));
        //3.2标题行合并列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fieldMap.size() - 1));
        for (int j = 0; j < fieldMap.size(); j++) {
            SXSSFCell cell = titleRow.createCell(j);
            setCellStyle(cellStyle, cell);
        }
        //3.3获得标题列
        SXSSFCell titleCell = titleRow.getCell(0);
        //3.4设置标题
        titleCell.setCellValue(config.getTitle());
        setCellCenter(cellStyle, titleCell);

        //4.创建表头行和列
        SXSSFRow headRow = sheet.createRow(config.getHeadRow());
        //4.1设置行高
        headRow.setHeight((short) (20 * 20));
        //4.2创建列
        for (int j = 0; j < fieldMap.size(); j++) {
            SXSSFCell cell = headRow.createCell(j);
            //4.2.1设置单元格样式
            setCellStyle(cellStyle, cell);
            setCellStyle(cellStyle, cell);
            //4.2.2赋值
            for (String key : fieldMap.keySet()) {
                AppCloudExcel appCloudExcel = (AppCloudExcel) fieldMap.get(key);
                //4.2.3判断序号是否和当前列相同
                if (appCloudExcel.serial() == j) {
                    cell.setCellValue(appCloudExcel.headName());
                }
            }
        }
        //5.创建数据行和列,写入数据
        for (int i = 2; i < dataList.size() + 2; i++) {
            //5.1创建数据行
            SXSSFRow row = sheet.createRow(i);
            Object obj = dataList.get(i - 2);
            //5.2获取对象值
            Map<String, String> valuesMap = ExcelReflect.getFieldsValue(obj, fieldMap);
            row.setHeight((short) (20 * 20));
            for (int j = 0; j < fieldMap.size(); j++) {
                //5.3创建单元格
                SXSSFCell cell = row.createCell(j);
                //5.4写入值
                cell.setCellValue(valuesMap.get(j + ""));
                setCellStyle(cellStyle, cell);
            }
        }
        try {
            //7.输出Excel文件
            FileOutputStream output = new FileOutputStream(filePath);
            wb.write(output);
            output.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return filePath;
    }

    /**
      * 设置单元格居中
      * @param cellStyle
      * @param cell
     */
    private void setCellCenter(CellStyle cellStyle, SXSSFCell cell) {
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
        cell.setCellStyle(cellStyle);
    }

    /**
      * 设置单元格样式
      * @param cellStyle
      * @param cell
     */
    private void setCellStyle(CellStyle cellStyle, SXSSFCell cell) {
        // 设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框    
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框    
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框    
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框   
        cell.setCellStyle(cellStyle);
    }

}

6.实体数据转换excel

public class ExcelConverter {

    private Config config;

    public ExcelConverter(Config config) {
        super();
        this.config = config;
    }

    /**
      * excel转换器
      * @param dataList 数据集合
      * @param filePath 转换的excel存放路径
      * @return 转换出来的excel文件存放路径
     */
    public String converter(List<T> dataList, String filePath) {
        String extName = filePath.substring(filePath.lastIndexOf("."));
        try {
            if (ExcelConstant.XLSX.equals(extName)) {
                XlsxConverter xlsxConverter = new XlsxConverter();
                return xlsxConverter.converter(dataList, filePath, config);
            } else if (ExcelConstant.XLS.equals(extName)) {

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return extName;
    }

}

7.测试-实体

public class SpecialTrain {

    @AppCloudExcel(headName = "日期", serial = 0)
    private Date trainDate;

    @AppCloudExcel(headName = "时间", serial = 1)
    private String trainTime;

    @AppCloudExcel(headName = "内容", serial = 2)
    private String content;

    @AppCloudExcel(headName = "讲师", serial = 3)
    private String lecturer;

    @AppCloudExcel(headName = "参训人员", serial = 4)
    private String trainees;

    @AppCloudExcel(headName = "跟踪人", serial = 5)
    private String trackPeople;

    public Date getTrainDate() {
        return trainDate;
    }

    public void setTrainDate(Date trainDate) {
        this.trainDate = trainDate;
    }

    public String getTrainTime() {
        return trainTime;
    }

    public void setTrainTime(String trainTime) {
        this.trainTime = trainTime;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getLecturer() {
        return lecturer;
    }

    public void setLecturer(String lecturer) {
        this.lecturer = lecturer;
    }

    public String getTrainees() {
        return trainees;
    }

    public void setTrainees(String trainees) {
        this.trainees = trainees;
    }

    public String getTrackPeople() {
        return trackPeople;
    }

    public void setTrackPeople(String trackPeople) {
        this.trackPeople = trackPeople;
    }

}

8.测试类

public class ExcelConverterTest {

    public static void main(String[] args) {
        Config config = new Config();
        config.setTitleRow(0);
        config.setHeadRow(1);
        config.setStartRow(2);
        config.setTitle("测试标题");

        ExcelConverter excelConverter = new ExcelConverter(config);

        String excelFilePath = "D:\\测试.xlsx";
        excelConverter.converter(getData(), excelFilePath);
    }

    @SuppressWarnings("unchecked")
    public static <T> List<T> getData() {
        List<SpecialTrain> dataList = new ArrayList<SpecialTrain>();

        SpecialTrain one = new SpecialTrain();
        one.setTrainDate(new Date());
        one.setTrainTime("12:00:00");
        one.setContent("基础数据培训");
        one.setLecturer("张三");
        one.setTrainees("里斯");
        one.setTrackPeople("测试人员");

        dataList.add(one);
        return (List<T>) dataList;
    }
}