[Java知识库]java导入导出

方式一：采用easyexcel导入

1.需要导入的依赖

     <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>

实体类代码：

@Data
@Entity  // 该注解声明一个实体类，与数据库中的表对应
@Table(name = "tb_user")  //表示当映射多个表时，指定表的表中的字段。默认值为主表的表名。
@ApiModel(description = "用户表")
public class User{
    //主键标识：表明该声明的属性字段和数据库的主键字段一一对应
    @Id
    //主键生成策略，不写意味着没有主键生成策略，也意味着是手动主键
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @ApiModelProperty(hidden = true)   //swagger注解
    private Integer id;
    @ApiModelProperty(value = "用户名")
    @ExcelProperty(index = 1)
    //unique
    //表示该字段是否为唯一标识，默认为false。如果表中有一个字段需要唯一标识，则既可以使用该标记，也可以使用@Table标记中的@UniqueConstraint。
    //nullable 表示该字段是否可以为null值，默认为true。
    // length  表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符。
    @Column(name = "user_name", unique = true, nullable = false, length = 50)
    private String userName;
    @ApiModelProperty(value = "用户工号")
    @ExcelProperty(index = 2)
    @Column(name = "login_name", length = 20)
    private String loginName;

    @ApiModelProperty(value = "密码")
    @Column(name = "password", length = 50)
    private String password;

    @ApiModelProperty(value = "头像")
    @Column(name = "avatar", length = 100)
    private String avatar;

    @ApiModelProperty(value = "性别")
    @ExcelProperty(index = 3)
    @Column(name = "gender", nullable = false, length = 1)
    private String gender;

    @ApiModelProperty(value = "邮箱")
    @ExcelProperty(index = 4)
    @Column(name = "email", length = 50)
    private String email;

    @ApiModelProperty(value = "描述")
    @ExcelProperty(index = 7,value = "描述")
    @Column(name = "description")
    private String description;

    @ApiModelProperty(value = "所属公司")
    @ExcelProperty(index = 5,value = "所属公司")
    @Column(name = "company", length = 50)
    private String company;

    @ApiModelProperty(value = "职位")
    @ExcelProperty(index = 6,value ="职位")
    @Column(name = "position", length = 20)
    private String position;

    @ApiModelProperty(hidden = true)
    private Date createTime;

    @ApiModelProperty(hidden = true)
    private Date updateTime;
}

实现监听回调

public class PrtcpntInfoListener extends AnalysisEventListener<User> {
    private static final Logger log = LoggerFactory.getLogger(PrtcpntInfoListener.class);
    private static int TOTAL_COUNT;

    /**
     * 阈值：超20000条，批量写入库一次，清理list，回收内存
     */
    private static final int BATCH_COUNT = 110000;
    List<User> list = new ArrayList<>();
    private BatchLead batchLead;

    public PrtcpntInfoListener(BatchLead batchLead) {
        this.batchLead = batchLead;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(User data, AnalysisContext context) {
       log.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 每达到阈值，就去做批量入库，防止内存溢出
        if (list.size() >= BATCH_COUNT) {
            ThreadOperation.readExcel(list);
            TOTAL_COUNT += list.size();
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //执行完让线程直接进入等待
         batchLead.save(list);
        TOTAL_COUNT += list.size();
        log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
    }


    public static void main(String[] args) {
        Long startTime = System.currentTimeMillis();
        ExcelReader excelReader = null;
        try {
            String filePath = "C:/Users/86135/Desktop/用户信息.xlsx";
            excelReader = EasyExcel.read(filePath, User.class, new PrtcpntInfoListener(new BatchLead())).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
            excelReader.read(readSheet);
        } finally {
            // 关闭资源
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");
    }
}

保存数据 JDBC直连方式

public class BatchLead {
    private static final Logger log = LoggerFactory.getLogger(BatchLead.class);
    int i = 0;

    public void save(List<User> list) {
        System.out.println("保存="+list.size());
        Long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position)values(?,?,?,?,?,?,?,?,?)";
        conn = getConnection();
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        for (User dataInfo : list) {
            try {
                pstmt.setString(1, dataInfo.getUserName());
                pstmt.setString(2, dataInfo.getLoginName());
                pstmt.setString(3, dataInfo.getPassword());
                pstmt.setString(4, dataInfo.getAvatar());
                pstmt.setString(5, dataInfo.getGender());
                pstmt.setString(6, dataInfo.getEmail());
                pstmt.setString(7, dataInfo.getDescription());
                pstmt.setString(8, dataInfo.getCompany());
                pstmt.setString(9, dataInfo.getPosition());
                pstmt.addBatch();
                i++;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            pstmt.executeBatch();
            conn.commit();
            conn.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        log.info("批量入库，本次入库数据：{}条，耗时：{}(s)", list.size(), (System.currentTimeMillis() - startTime) / 1000);
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/localtest?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC",
                    "root", "root");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

或者使用mybatis，在覆盖doAfterAllAnalysed方法中调用

   @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
        userMapper.batchAddUser(list);
        TOTAL_COUNT += list.size();
        log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
    }

controller层调用：

  /**
     * 批量导入
     *
     * @param
     * @return
     */
    @PostMapping("/users/add")
    @ApiOperation(value = "批量导入")
    public ResultDTO saveBatchUser(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (!StrUtil.endWithAny(fileName, EXCEL_XLS, EXCEL_XLSX)) {
            return ResultDTO.error("只支持.xlsx与.xls格式的文件导入");
        }
        return  myUserService.saveBatchUser(file,fileName);
    }

service层代码：

  public ResultDTO saveBatchUser(MultipartFile multipartFile, String fileName) {
        Long startTime = System.currentTimeMillis();
        ExcelReader excelReader = null;
        File file = null;
        try {
            String[] filename = fileName.split("\\.");
            file=File.createTempFile(filename[0], ".xlsx");
            multipartFile.transferTo(file);
            excelReader = EasyExcel.read(file, User.class, new PrtcpntInfoListener(new BatchLead())).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(1).build();
            excelReader.read(readSheet);
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (excelReader != null) {
                excelReader.finish();
            }
           // file.deleteOnExit();
        }
        log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");

        return ResultDTO.isSuccess();
    }

上面是单线程版
多线程保存数据库方式
先定义线程资源类

public class ImportThread implements Runnable {
    BatchLead batchLead;
    private List<User> list;
    private CountDownLatch end;
    private MyUserMapper myUserMapper;

    public ImportThread(List<User> list, CountDownLatch begin, CountDownLatch end, BatchLead batchLead) {
        this.list = list;
        this.end = end;
        this.batchLead = batchLead;
    }

  @Override
    public void run() {
        //这里三种方式选取一种
        //1.mybatis方式
        UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
        userMapper.batchAddUser(list);
        //2.jpa方式
      //  MyUserMapper myUserMapper = ApplicationContextProvider.getBean(MyUserMapper.class);
      //  myUserMapper.saveAll(list);
        //3.JDBC直连
      //  batchLead.save(list);
      //  end.countDown();
    }
}

上面连接数据库如果使用mybatis方式，sql语句如下

     <insert id="batchAddUser">
        insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position) values
        <foreach collection="list" separator="," item="item">
            (#{item.userName},#{item.loginName},#{item.password},#{item.avatar},#{item.gender},#{item.email},#{item.description},#{item.company},#{item.position})
        </foreach>
    </insert>

使用线程池

public class ThreadOperation {
    public static void readExcel(List<User> list) {
        //一个线程处理数据数
        int count = 11000;
        // 开启的线程数
        int runSize = 10;
        // 存放每个线程的执行数据
        List<User> newlist = null;
//        int corePoolSize,核心线程数
//        int maximumPoolSize,最大线程数
//        long keepAliveTime,线程空闲时最大存活时间
//        TimeUnit unit,最大存活时间单位
//        BlockingQueue<Runnable> workQueue,任务队列
//        ThreadFactory threadFactory,线程工厂用默认的即可
//        RejectedExecutionHandler handler)线程池和队列都满时的拒绝策略

        //创建一个线程池,数量和开启线程的数据量一样
        ThreadPoolExecutor executor = new ThreadPoolExecutor(
                runSize, runSize,  1, TimeUnit.SECONDS, new ArrayBlockingQueue<>(5),
                new ThreadPoolExecutor.DiscardOldestPolicy());
                //并发容器，计数器，等待协同
        CountDownLatch end = new CountDownLatch(runSize);
        //循环创建线程
        for (int i = 0; i < runSize; i++) {
            //计算每个线程执行的数据
            if ((i+1)==runSize){
                newlist=list.subList((i*count),list.size());
            }else {
                newlist=list.subList((i*count),(i+1)*count);
            }
            //线程类 操作资源
            ImportThread mythead = new ImportThread(newlist, end,new BatchLead());
            //执行线程
            executor.execute(mythead);
        }
        try {
            end.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        //执行完关闭线程池
        executor.shutdown();
    }
}

方式二：采用POI导入

依赖准备

             <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>

            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
                <version>3.4</version>
            </dependency>

自定义注解

/**
 * 设置允许导出
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExport {
     String fileName();
}

/**
 * 设置该字段允许导出
 * 并且可以设置宽度
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExportField {
     int colWidth() default  100;
     String colName();
}

/**
 * 导入时索引
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ImportIndex {
     int index() ;
}

//pojo
@EnableExport(fileName = "XXX功能")
public class DataCheckExport {
    @EnableExportField(colName = "编码",colWidth = 90)
    @ImportIndex(index = 0)
    private String code;
    ....
    }

导入 、导出、 自定义表格颜色工具类

public class ExcelUtils {
    /**  所有的下拉列表数据存在这个map中，key是对应的Excel列的序号，从0开始，value为下拉列表键对值 **/
    public static final  Map<Integer,Map<String,String>> ALL_SELECT_LIST_MAP = new HashMap<Integer,Map<String,String>> ();
        /**
         * 将Excel转换为对象集合
         * @param excel Excel 文件
         * @param clazz pojo类型
         * @return
         */
        public static List<?> parseExcelToList(File excel,Class clazz){
            List<Object> res = new ArrayList<Object>();
            // 创建输入流，读取Excel
            InputStream is = null;
            Sheet sheet = null;
            try {
                is = new FileInputStream(excel.getAbsolutePath());
                if (is != null) {
                    Workbook workbook = WorkbookFactory.create(is);
                    //默认只获取第一个工作表
                    sheet = workbook.getSheetAt(0);
                    if (sheet != null) {
                        int i = 2;
                        String values[] ;
                        Row row = sheet.getRow(i);
                        while (row != null) {
                            //获取单元格数目
                            int cellNum = row.getPhysicalNumberOfCells();
                            values = new String[cellNum];
                            for (int j = 0; j <= cellNum; j++) {
                                Cell cell =   row.getCell(j);
                                if (cell != null) {
                                    //设置单元格内容类型
                                    cell.setCellType(Cell.CELL_TYPE_STRING );
                                    //获取单元格值
                                    String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
                                    values[j]=value;
                                }
                            }
                            Field[] fields = clazz.getDeclaredFields();
                            Object obj = clazz.newInstance();
                            for(Field f : fields){
                                if(f.isAnnotationPresent(ImportIndex.class)){
                                    ImportIndex annotation = f.getAnnotation(ImportIndex.class);
                                    int index = annotation.index();
                                    String useSetMethodName = annotation.useSetMethodName();
                                    if(!"".equals(useSetMethodName)){
                                        Object val = TypeUtils.cast(values[index],f.getType(),null);
                                        f.setAccessible(true);
                                        Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType(),Object.class});
                                        method.setAccessible(true);
                                        method.invoke(obj, new Object[]{f.get(obj),val});
                                    }else{
                                        f.setAccessible(true);
                                        Object val =TypeUtils.cast(values[index],f.getType(),null);
                                        f.set(obj,val);
                                    }
                                }
                            }
                            res.add(obj);
                            i++;
                            row=sheet.getRow(i);
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return res;
        }

        /**
         * 将Excel转换为对象集合
         * @param excel Excel 文件输入流
         * @param clazz pojo类型
         * @return
         */
        public static List<?> parseExcelToList(InputStream excel,Class clazz) throws IOException,
                InvalidFormatException,
                InstantiationException,
                IllegalAccessException,
                NoSuchMethodException,
                InvocationTargetException {
            List<Object> res = new ArrayList<Object>();
            // 创建输入流，读取Excel
            InputStream is = null;
            Sheet sheet = null;

            is = excel;
            if (is != null) {
                Workbook workbook = WorkbookFactory.create(is);
                //默认只获取第一个工作表
                sheet = workbook.getSheetAt(0);
                if (sheet != null) {
                    int i = 2;
                    String values[] ;
                    Row row = sheet.getRow(i);
                    while (row != null) {
                        //获取单元格数目
                        int cellNum = row.getPhysicalNumberOfCells();
                        values = new String[cellNum];
                        for (int j = 0; j <= cellNum; j++) {
                            Cell cell =   row.getCell(j);
                            if (cell != null) {
                                //设置单元格内容类型
                                cell.setCellType(Cell.CELL_TYPE_STRING );
                                //获取单元格值
                                String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
                                values[j]=value;
                            }
                        }
                        Field[] fields = clazz.getDeclaredFields();
                        Object obj = clazz.newInstance();
                        for(Field f : fields){
                            if(f.isAnnotationPresent(ImportIndex.class)){

                                ImportIndex annotation = f.getAnnotation(ImportIndex.class);
                                int index = annotation.index();
                                Object value = values[index];
                                if(f.isAnnotationPresent(EnableSelectList.class)){

                                    value = getKeyByValue(ALL_SELECT_LIST_MAP.get(index),String.valueOf(value ) );

                                }
                                String useSetMethodName = annotation.useSetMethodName();
                                if(!"".equals(useSetMethodName)){
                                    Object val =TypeUtils.cast(value,f.getType(),null);
                                    f.setAccessible(true);
                                    Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType(),Object.class});
                                    method.setAccessible(true);
                                    method.invoke(obj, new Object[]{f.get(obj),val});
                                }else{
                                    f.setAccessible(true);
                                    Object val =TypeUtils.cast(value,f.getType(),null);
                                    f.set(obj,val);
                                }

                            }
                        }
                        res.add(obj);
                        i++;
                        row=sheet.getRow(i);
                    }
                }
            }

            return res;
        }

        /**
         * 导出 Excel
         * @param outputStream 输出流，用于写文件
         * @param dataList 需要导出的数据
         * @param clazz 导出数据的pojo类型
         * @param selectListMap 下拉列表的列
         * @param exportTitle 当该参数不为空则替换默认的标题
         */
        public static void exportExcel(OutputStream outputStream, List  dataList, Class clazz, Map<Integer,Map<String,String>> selectListMap,String exportTitle){
            //创建一个Excel工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //建立表
            HSSFSheet hssfsheet =  workbook.createSheet();

            hssfsheet.setDefaultRowHeight( ( short )(20*20) );
            //检查当前pojo是否允许导出
            if(clazz.isAnnotationPresent(EnableExport.class)) {
                EnableExport export = (EnableExport) clazz.getAnnotation(EnableExport.class);
                //获取所有标题名称
                List<String> colNames =new ArrayList<String>();
                //获取所有标题的背景颜色
                List<ColorEnum> colors =new ArrayList<ColorEnum>();
                //所有允许导出的字段
                List<Field> fieldList = new ArrayList<Field>();
                for(Field field : clazz.getDeclaredFields()){
                    if(field.isAnnotationPresent(EnableExportField.class)){
                        EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                        colNames.add(enableExportField.colName());
                        colors.add(enableExportField.cellColor());
                        fieldList.add(field);
                    }
                }
                //设置每列的宽度
                for(int i=0;i<fieldList.size();i++){
                    Field field = fieldList.get(i);
                    hssfsheet.setColumnWidth(i,field.getAnnotation(EnableExportField.class).colWidth()*20);
                }

                HSSFRow hssfRow = null;
                HSSFCell hssfcell = null;

                //绘制表头以及菜单
                String fileName =export.fileName();
                if(exportTitle!=null){
                    fileName = exportTitle;
                }
                //绘制标题
                createTitle(workbook,hssfRow,hssfcell,hssfsheet, colNames.size()-1 ,fileName,export.cellColor());
                //创建标题行（表头）
                createHeadRow(workbook,hssfRow,hssfcell,hssfsheet,colNames,colors);
                try {
                    //表格样式
                    HSSFCellStyle cellStyle= getBasicCellStyle(workbook);
                    //插入内容
                    int i=0;
                    for (Object obj : dataList) {
                        hssfRow = hssfsheet.createRow(i + 2);
                        //设置每列的宽度
                        //此处设置j=-1 ：添加一列，序号列
                        for(int j=0;j<fieldList.size();j++){
                            Field field = fieldList.get(j);
                            field.setAccessible(true);
                            Object value = field.get(obj);
                            EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                            String getMethodName = enableExportField.useGetMethod();
                            if(!"".equals( getMethodName )){
                                Method  method = clazz.getMethod(getMethodName, new Class[]{field.getType()});
                                method.setAccessible(true);
                                value= method.invoke(obj, new Object[]{value});
                            }
                            if(field.isAnnotationPresent(EnableSelectList.class)){
                                if(selectListMap!=null&& selectListMap.get(j)!=null)
                                    value =selectListMap.get(j).get(value);
                            }
                            setCellValue(value, hssfcell, hssfRow, cellStyle, j);
                        }
                        i++;
                    }
                    //创建下拉列表
                    createDataValidation(hssfsheet,selectListMap);
                    workbook.write(outputStream);
                    outputStream.flush();
                    outputStream.close();

                } catch (IllegalAccessException e ) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }catch (InvocationTargetException e) {
                    e.printStackTrace();
                }

            }
        }

        /**
         * 获取一个基本的带边框的单元格
         * @param workbook
         * @return
         */
        private static HSSFCellStyle getBasicCellStyle(HSSFWorkbook workbook){
            HSSFCellStyle hssfcellstyle = workbook.createCellStyle();
            hssfcellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            hssfcellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            hssfcellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            hssfcellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            hssfcellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            hssfcellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            hssfcellstyle.setWrapText(true);
            return hssfcellstyle;
        }

        /**
         * 获取带有背景色的标题单元格
         * @param workbook
         * @return
         */
        private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook,ColorEnum color){
            HSSFCellStyle hssfcellstyle =  getBasicCellStyle(workbook);
            hssfcellstyle.setFillForegroundColor(color.getIndex()); // 设置背景色
            hssfcellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            return hssfcellstyle;
        }

        /**
         * 创建一个跨列的标题行
         * @param workbook
         * @param hssfRow
         * @param hssfcell
         * @param hssfsheet
         * @param allColNum
         * @param title
         */
        private static void createTitle(HSSFWorkbook workbook, HSSFRow hssfRow , HSSFCell hssfcell, HSSFSheet hssfsheet,int allColNum,String title,ColorEnum color){
            //在sheet里增加合并单元格
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, allColNum);
            hssfsheet.addMergedRegion(cra);
            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(1, cra, hssfsheet, workbook); // 下边框
            RegionUtil.setBorderLeft(1, cra, hssfsheet, workbook); // 左边框
            RegionUtil.setBorderRight(1, cra, hssfsheet, workbook); // 有边框
            RegionUtil.setBorderTop(1, cra, hssfsheet, workbook); // 上边框

            //设置表头
            hssfRow = hssfsheet.getRow(0);
            hssfcell = hssfRow.getCell(0);
            hssfcell.setCellStyle( getTitleCellStyle(workbook,color));
            hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
            hssfcell.setCellValue(title);
        }

        /**
         * 设置表头标题栏以及表格高度
         * @param workbook
         * @param hssfRow
         * @param hssfcell
         * @param hssfsheet
         * @param colNames
         */
        private static void createHeadRow(HSSFWorkbook workbook,HSSFRow hssfRow , HSSFCell hssfcell,HSSFSheet hssfsheet,List<String> colNames,List<ColorEnum> colors){
            //插入标题行
            hssfRow = hssfsheet.createRow(1);
            for (int i = 0; i < colNames.size(); i++) {
                hssfcell = hssfRow.createCell(i);
                hssfcell.setCellStyle(getTitleCellStyle(workbook,colors.get(i)));
                hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
                hssfcell.setCellValue(colNames.get(i));
            }
        }
        /**
         * excel添加下拉数据校验
         * @param sheet 哪个 sheet 页添加校验
         * @return
         */
        public static void createDataValidation(Sheet sheet, Map<Integer,Map<String,String>> selectListMap) {
            if(selectListMap!=null) {
                for(Map.Entry<Integer,Map<String,String>> entry:selectListMap.entrySet()  ){
                    Integer key = entry.getKey();
                    Map<String,String> value = entry.getValue();
                    // 第几列校验（0开始）key 数据源数组value
                    if(value.size()>0) {
                        int i=0;
                        String[] valueArr = new String[value.size()];
                        for(Map.Entry<String,String> ent :value.entrySet()){
                            valueArr[i] = ent.getValue();
                            i++;
                        }
                        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, key, key);
                        DataValidationHelper helper = sheet.getDataValidationHelper();
                        DataValidationConstraint constraint = helper.createExplicitListConstraint(valueArr);
                        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                        //处理Excel兼容性问题
                        if (dataValidation instanceof XSSFDataValidation) {
                            dataValidation.setSuppressDropDownArrow(true);
                            dataValidation.setShowErrorBox(true);
                        } else {
                            dataValidation.setSuppressDropDownArrow(false);
                        }
                        dataValidation.setEmptyCellAllowed(true);
                        dataValidation.setShowPromptBox(true);
                        dataValidation.createPromptBox("提示", "只能选择下拉框里面的数据");
                        sheet.addValidationData(dataValidation);
                    }
                }
            }
        }
        /**
         *通过value获取key值
         * @param selectMap
         * @param value
         * @return
         */
        private static String getKeyByValue(Map<String,String> selectMap,String value){
            if(selectMap!=null){
                for(Map.Entry<String,String> ent :selectMap.entrySet()){
                    if(value!=null&&value.equals(ent.getValue()))
                        return ent.getKey();
                }
            }else{
                return value;
            }
            return null;
        }


        /**
         *判断字符串是否为数字
         * @param str
         * @return
         */
        private static boolean isNumeric(String str) {
            Pattern pattren =
                    Pattern.compile("[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
            if (str != null && !"".equals(str.trim())) {
                Matcher matcher = pattren.matcher(str);
                if (matcher.matches()) {
                    if (!str.contains(".") && str.startsWith("0")) {
                        return false;
                    }
                    return true;
                }
            }
            return false;
        }

        /**
         *设置单元格的值
         * @param value
         * @param hssfcell
         * @param hssfRow
         * @param cellStyle
         * @param cellIndex
         */
        private static void setCellValue(Object value,HSSFCell hssfcell,HSSFRow hssfRow,CellStyle cellStyle,int cellIndex) {
            String valueStr = String.valueOf(value);
            hssfcell =hssfRow.createCell(cellIndex );
            //暂时认为数字类型不会有下拉列表
            if (isNumeric(valueStr)) {
                hssfcell.setCellStyle(cellStyle);
                hssfcell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                hssfcell.setCellValue(Double.valueOf(valueStr));
            } else {
                hssfcell.setCellStyle(cellStyle);
                hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
                hssfcell.setCellValue(valueStr);
            }
        }
}

还可以自定义表格颜色，根据业务需要使用，定义一个枚举类存储需要的颜色。

public enum ColorEnum {
    RED("红色", HSSFColor.RED.index),
    GREEN("绿色", HSSFColor.GREEN.index),
    BLANK("白色", HSSFColor.WHITE.index),
    YELLOW("黄色", HSSFColor.YELLOW.index),
    BLUE("蓝色", HSSFColor.CORNFLOWER_BLUE.index);
    private String name;
    private short index;
    private ColorEnum( String name, short index) {
        this.name = name;
        this.index = index;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public short getIndex() {
        return index;
    }

    public void setIndex(short index) {
        this.index = index;
    }

}