Poi工具类快速生成Ecxel(升级版)

1.导入依赖

<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

2.处理代码(写的有点拉跨)

package com.sjyf.gym.utils;

import com.sjyf.gym.annotation.Excel;
import com.sjyf.gym.ex.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.*;

/**
 * @author 何杰
 * @date 2020年5月20日
 */
public class ExcelUtil {

    /**
     * @Title: createEcxel
     * @Description: 通过属性组创建Excel,序号自动生成
     * @param title
     *            标题
     * @param property
     *            对象的属性名
     * @param coll
     *            需要生成表格的对象
     * @param suffix
     *            后缀名 xls || xlsx
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws IOException
     */
    public static SXSSFWorkbook createEcxelByGroups(String[] title, String[] property,
                                                    List<?> coll, PoiType suffix) {
        if (suffix == null) {
            throw new RuntimeException("请选择文件后缀名");
        }
        SXSSFWorkbook wb = null;
        if (suffix == PoiType.XLS) {
            wb = new SXSSFWorkbook();
        } else {
            wb = new SXSSFWorkbook();
        }
        Sheet st = wb.createSheet();
        // 创建第一行
        Row rowone = st.createRow(0);
        // 设置标题
        for (int i = 0; i <= title.length; i++) {
            Cell cell = rowone.createCell(i);
            if (i == 0) {
                cell.setCellValue("序号");
                continue;
            }
            cell.setCellValue(title[i - 1]);
        }

        // 填充值
        try {
            for (int i = 0; i < coll.size(); i++) {
                Class<? extends Object> clazz = coll.get(i).getClass();

                Row row = st.createRow(i + 1);
                for (int j = 0; j <= property.length; j++) {
                    // 创建j++列
                    Cell cell = row.createCell(j);
                    if (j == 0) {
                        cell.setCellValue(i + 1);
                        continue;
                    }
                    // 判断要获取的属性名是否包含.
                    if(property[j - 1].indexOf(".") == -1){
                        // 不包含
                        Field field = null;
                        try {
                            field = coll.get(i).getClass()
                                    .getDeclaredField(property[j - 1]);
                        } catch (Exception e) {
                            field = clazz.getSuperclass().getDeclaredField(property[j - 1]);
                        }
                        Method method = clazz.getMethod("get" + toUpperCase(field.getName()));
                        Object invoke = method.invoke(coll.get(i));
                        // 设置对象的访问权限,保证对private的属性的访问
                        String classType = field.getType().toString();
                        if (classType.equals("class java.util.Date")){
                            String format = dateFormat(invoke);
                            cell.setCellValue(format == null ? "" : format);
                            continue;
                        }
                        if(classType.equals("class java.time.LocalDateTime")){
                            Date date = localDateTimeToDate((LocalDateTime) invoke);
                            String format = dateFormat(date);
                            cell.setCellValue(format == null ? "" : format);
                            continue;
                        }
                        Excel excel = field.getAnnotation(Excel.class);
                        if(excel != null){
                            String value = getValue(excel.value(), invoke + "");
                            cell.setCellValue(value);
                        }else{
                            cell.setCellValue(invoke == null ? "" : invoke + "");
                        }
                    }else{
                        // 包含
                        Field field = coll.get(i).getClass()
                                .getDeclaredField(property[j - 1].substring(0, property[j - 1].lastIndexOf(".")));
                        // 设置对象的访问权限,保证对private的属性的访问
                        field.setAccessible(true);
                        Object o = field.get(coll.get(i));
                        // 判断是否属于基本数据类型,或是否包含.
                        if(!typeDict(o) && property[j - 1].indexOf(".") != -1){
                            String value = getFieldValue(o, property[j - 1]);
                            cell.setCellValue(value == null ? "" : value +  "" );
                            continue;
                        }
                        cell.setCellValue(o == null ? "" : o + "");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("导出错误,请联系管理员");
        }
        return wb;
    }

    /**
     *
     * @Description: 获取字段值
     * @param @param obj 对象
     * @param @param field 获取的字段名
     * @param @return
     * @return String
     * @throws
     */
    public static String getFieldValue(Object obj, String field){
        // 截取字段名,只支持一级,obj -> obj,
        try {
            String fname = field.substring(field.lastIndexOf(".") + 1,field.length());
            // 创建j++列
            Field d = obj.getClass().getDeclaredField(fname);
            // 设置对象的访问权限,保证对private的属性的访问
            d.setAccessible(true);
            Object o = d.get(obj);
            return o + "";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 判断是否是常用基本数据类型
     */
    public static boolean typeDict(Object obj){
        Class<?>[] clazzs = {int.class,short.class,double.class,
                float.class,byte.class,long.class,char.class,boolean.class,
                String.class,Double.class,Float.class,Integer.class,Long.class,
                Short.class,Boolean.class,Character.class,Byte.class,BigDecimal.class};

        for (Class<?> c : clazzs) {
            if(obj.getClass().isAssignableFrom(c)){
                return true;
            }
        }
        return false;
    }

    public static String toUpperCase(String name){
        return name.substring(0,1).toUpperCase() + name.substring(1,name.length());
    }

    public static void main(String[] args) {
        toUpperCase(null);
    }

    public enum PoiType {
        XLS, XLSX;
    }

    public static Date localDateTimeToDate(LocalDateTime localDateTime){
        ZoneId zoneId = ZoneId.systemDefault();
        ZonedDateTime zdt = localDateTime.atZone(zoneId);
        return Date.from(zdt.toInstant());
    }


    /**
     * 导出
     */
    public static void exportExcel(SXSSFWorkbook wb, String fileName){
        OutputStream out = null;
        try {
            HttpServletResponse response = HttpContextUtils.getHttpServletResponse();
            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),
                    "ISO-8859-1") + ".xlsx");
            out = response.getOutputStream();
            wb.write(out);
        } catch (Exception e) {
            throw new BusinessException("导出Excel失败,请联系管理员!");
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    public static String dateFormat(Object obj){
        if(Objects.isNull(obj)){
            return "";
        }
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(obj);
    }

    /**
     * 获取字段的字典值
     */
    public static Map<String, String> getDict(String dictValues) {
        // String dictValues = "1=待学员确认,2=待教练确认,3=待销课,4=已销课待评价,5=已销课已评价,6=已退还,7=已取消";
        String[] keyAndValues = dictValues.split(",");
        Map<String, String> values = new HashMap<>(keyAndValues.length);
        for (String value : keyAndValues) {
            String[] key = value.split("=");
            values.put(key[0], key[1]);
        }
        return values;
    }

    public static String getValue(String dictValues, String key){
        Map<String, String> dict = getDict(dictValues);
        if (dict == null || dict.size() == 0){
            return "";
        }
        return dict.get(key);
    }

    public static XSSFSheet getSheet() {
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream("C:\\Users\\qian\\Desktop\\私教模板(1)(2).xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = sheets.getSheetAt(0);
            return sheet;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}

3.所使用的注解类

package com.sjyf.gym.annotation;


import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

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

    String value() default "";

}

4.注解类的使用方法

  首先说明为什么要使用注解,因为有些类是int值,当然你可以在数据库用case when去处理,使用格式(1=正常,2=过期,3=停用)

程序会按照相应的int值匹配,去设置其相应的注释值。

    @Excel("1=正常,2=过期,3=停用")
    private Integer status;

5.使用(就是这么简单,当然还能更简单,可以使用注解,然后反射得到注解的值和key,自行发挥吧!)

     List<ContractPersonalExportVo> personals = contractPersonalMapper.exportExcel();
        String[] titles = {"门店", "虚拟会员卡号", "会员姓名", "手机号码", "性别", "身份证号", "课程名称", "总课时",
                "剩余课时", "总金额", "销售员", "状态", "单价", "合同编号", "创建时间"};

        String[] propertys = {"storeName", "virtualCardNum", "userName", "phone", "sex", "idCard", "courseName",
                "totalNum", "surplusNum", "totalAmt", "nickName", "status", "avgPrice", "code", "createTime"};
        SXSSFWorkbook workbook = ExcelUtil.createEcxelByGroups(titles, propertys, personals, ExcelUtil.PoiType.XLSX);
        ExcelUtil.exportExcel(workbook, "业务合同");

 

上一篇:ExcelToTxt 工具类


下一篇:读表格