poi批量导入Excel

思路：count()查出数据库数据的总条数，然后根据count()总条数%excel中多少条分一页算出所导出数据库的数据，可以分多少页（上图）分批查出数据库的数据导出到excel中。


（本图为每四条数据分一页，五条数据，共分两页）

上代码：

    public ResponseEntity<byte[]> excelReport(){
        #定义参数为OutPutStream流
        try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()){
            #定义excel表头
            String keyValue = "表型名称:meterName,现金:cash,支付宝:alipay,微信:weChat" +
                    ",银联:unionPay,累计:total";
            List<ReportDetailDTO> list = null;
            HSSFWorkbook wb = new HSSFWorkbook();
            #查询数据库所有的数据条数
            int count = reportService.countDataBase();
            #根据excel每页存放多少条数据,算出总页数
            int sheet = count%num>0 ? count/num+1 : count/num;
            for(int i=0;i<sheet;i++){
                #定义limit的第一个参数（查询数据的起始位置）
                int a = i*num;
                #100w的查询数据库的数据，分批查询
                list = reportService.selectDataBase(a,num);
                ExcelUtils.exportExcel(wb,byteArrayOutputStream, keyValue, list, "cn.enn.chaoscloud.domain.report.dto.ReportDTO",i+1,sheet);
            }
            #将数据输出为二进制的形式转为byte[]
            byte[] bytes = byteArrayOutputStream.toByteArray();
            #输出
            return ResponseEntity.ok()
                    .contentLength(bytes.length)
                    .contentType(MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
                    .body(bytes);
        }catch (Exception e){
            e.printStackTrace();
        }
        return ResponseEntity.badRequest().build();
    }

ExcelUtils工具类

package cn.enn.chaoscloud.master.utils;

import com.lkx.util.ExcelParam;
import com.lkx.util.ExcelTypeEnum;
import com.lkx.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * getMap:(将传进来的表头和表头对应的属性存进Map集合，表头字段为key,属性为value)
     *
     * @author likaixuan
     * @param
     *            : String keyValue = "手机名称:phoneName,颜色:color,售价:price";
     * @return
     * @since JDK 1.7
     */
    public static Map<String, String> getMap(String keyValue) {
        Map<String, String> map = new HashMap<String, String>();
        if (keyValue != null) {
            String[] str = keyValue.split(",");
            for (String element : str) {
                String[] str2 = element.split(":");
                map.put(str2[0], str2[1]);
            }
        }
        return map;
    }

    /**
     * @author likaixuan
     * @param
     * @return List
     * @Date 2018年5月9日 21:42:24
     * @since JDK 1.7
     */
    public static List<String> getList(String keyValue) {
        List<String> list = new ArrayList<String>();
        if (keyValue != null) {
            String[] str = keyValue.split(",");

            for (String element : str) {
                String[] str2 = element.split(":");
                list.add(str2[0]);
            }
        }
        return list;
    }

    /**
     * setter:(反射的set方法给属性赋值)
     *
     * @author likaixuan
     * @param obj
     *            具体的类
     * @param att
     *            类的属性
     * @param value
     *            赋予属性的值
     * @param type
     *            属性是哪种类型 比如:String double boolean等类型
     * @throws Exception
     * @since JDK 1.7
     */
    public static void setter(Object obj, String att, Object value, Class<?> type, int row, int col, Object key)
            throws Exception {
        try {
            Method method = obj.getClass().getMethod("set" + StringUtil.toUpperCaseFirstOne(att), type);
            method.invoke(obj, value);
        } catch (Exception e) {
            throw new Exception("第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 赋值异常  " + e);
        }

    }

    /**
     * getAttrVal:(反射的get方法得到属性值)
     *
     * @author likaixuan
     * @param obj
     *            具体的类
     * @param att
     *            类的属性
     * @param
     *
     * @param type
     *            属性是哪种类型 比如:String double boolean等类型
     * @throws Exception
     * @since JDK 1.7
     */
    public static Object getAttrVal(Object obj, String att, Class<?> type) throws Exception {
        try {
            Method method = obj.getClass().getMethod("get" + StringUtil.toUpperCaseFirstOne(att));
            Object value = new Object();
            value = method.invoke(obj);
            return value;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * getValue:(得到Excel列的值)
     *
     * @author likaixuan
     * @param
     * @return
     * @throws Exception
     * @since JDK 1.7
     */
    public static void getValue(Cell cell, Object obj, String attr, Class attrType, int row, int col, Object key)
            throws Exception {
        Object val = null;
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            val = cell.getBooleanCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    if (attrType == String.class) {
                        val = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else {
                        val = dateConvertFormat(sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())));
                    }
                } catch (ParseException e) {
                    throw new Exception("第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 日期格式转换错误  ");
                }
            } else {
                if (attrType == String.class) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    val = cell.getStringCellValue();
                } else if (attrType == BigDecimal.class) {
                    val = new BigDecimal(cell.getNumericCellValue());
                } else if (attrType == long.class) {
                    val = (long) cell.getNumericCellValue();
                } else if (attrType == Double.class) {
                    val = cell.getNumericCellValue();
                } else if (attrType == Float.class) {
                    val = (float) cell.getNumericCellValue();
                } else if (attrType == int.class || attrType == Integer.class) {
                    val = (int) cell.getNumericCellValue();
                } else if (attrType == Short.class) {
                    val = (short) cell.getNumericCellValue();
                } else {
                    val = cell.getNumericCellValue();
                }
            }

        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            val = cell.getStringCellValue();
        }

        setter(obj, attr, val, attrType, row, col, key);
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @author likaixuan
     * @param
     * @return
     * @throws Exception
     * @since JDK 1.7
     */
    public static void exportExcel(OutputStream outputStream, String keyValue, List<?> list, String classPath,Integer sheetNum,HSSFWorkbook wb,Integer sheet1)
            throws Exception {

        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Class<?> demo = null;
        demo = Class.forName(classPath);
        Object obj = demo.newInstance();
        if(sheetNum == 0) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
        }
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("sheet"+sheetNum);
            // 声明样式
            HSSFCellStyle style = wb.createCellStyle();
            // 居中显示
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(0);
            // 创建单元格并设置单元格内容

            // 存储属性信息
            Map<String, String> attMap = new HashMap();
            int index = 0;

            for (String key : keyList) {
                rowHeader.createCell(index).setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key).toString());
                index++;
            }

            // 在sheet里创建表头下的数据
            for (int i = 0; i < list.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < map.size(); j++) {

                    Class<?> attrType = BeanUtils.findPropertyType(attMap.get(Integer.toString(j)),
                            new Class[]{obj.getClass()});

                    Object value = getAttrVal(list.get(i), attMap.get(Integer.toString(j)), attrType);
                    if (null == value) {
                        value = "";
                    }
                    row.createCell(j).setCellValue(value.toString());
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                }
        }


        // 输出Excel文件
        try {
            if((sheetNum+1) == sheet1) {
                wb.write(outputStream);
                outputStream.close();
            }
        } catch (FileNotFoundException e) {
            throw new FileNotFoundException("导出失败！" + e);
        } catch (IOException e) {
            throw new IOException("导出失败！" + e);
        }

    }

    /**
     * String类型日期转为Date类型
     *
     * @param dateStr
     * @return
     * @throws ParseException
     * @throws Exception
     */
    public static Date dateConvertFormat(String dateStr) throws ParseException {
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        date = format.parse(dateStr);
        return date;
    }
}