Java Poi Excel 大量数据导入导出

1、Excel导入
1）读取文件
2）转换Workbook
3）拼装数据集

    String filePath = "excle/20171207.xls";
    String fullPath = Thread.currentThread().getContextClassLoader().getResource(filePath).getPath();
    Workbook wb = null;
    try {
        InputStream in = new BufferedInputStream(new FileInputStream(fullPath));
        wb = WorkbookFactory.create(in); 
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
    } catch (InvalidFormatException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
    } catch (IOException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
    }
    List<String[]> list = new ArrayList<String[]>();
    for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++){
        Sheet st = wb.getSheetAt(sheetIndex);
        // 第一行为标题，不取
        for (int rowIndex = 0; rowIndex <= st.getPhysicalNumberOfRows(); rowIndex++){
            Row row = st.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            String[] cells = cellArray(row);
            list.add(cells);
        }
    }

2、Excel导出
1）声明一个工作薄
2）设置样式
3）设置字体
4）设置表格标题行
5）生成表格具体数据行，超过上限生成下一个sheet
6）利用反射获取对应的属性值并赋值到表格
7）判断值的类型后进行格式转换（日期，浮点数特殊格式处理）

private static Pattern NUMBER_PATTERN = Pattern.compile("^//d+(//.//d+)?$");
public <T> byte[] exportExcel(String title, String[] headers, Collection<T> dataSet, String pattern) {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
        // 生成一个表格
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);

        Font font2 = workbook.createFont();
        Sheet sheet = null;
        Row row = null;
        int index = 0;
        int sheetnum = 0;
        Iterator<T> it = dataSet.iterator();
        // 生成表格标题行
        sheet = workbook.createSheet(title + sheetnum);
        sheet.setDefaultColumnWidth(15);
        row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        ByteArrayOutputStream baos = null;
        try {
            // 生成表格具体数据行
            while (it.hasNext()) {
                index++;
                // 如果数据大于5000行，生成下一个sheet
                if (index > 50000) {
                    index = 0;
                    ++sheetnum;
                    sheet = workbook.createSheet(title + sheetnum);
                    sheet.setDefaultColumnWidth(15);
                    row = sheet.createRow(0);
                    for (int i = 0; i < headers.length; i++) {
                        Cell cell = row.createCell(i);
                        cell.setCellStyle(style);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }
                }
                row = sheet.createRow(index);
                T t = (T) it.next();
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(style);
                    Field field = fields[i];
                    String fieldName = field.getName();

                    Object value = PropertyUtils.getProperty(t, fieldName);
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        textValue = new XSSFRichTextString(
                                String.valueOf(fValue)).toString();
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        textValue = new XSSFRichTextString(
                                String.valueOf(dValue)).toString();
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    }
                    if (value instanceof Boolean) {
                        boolean bValue = (Boolean) value;
                        textValue = "true";
                        if (!bValue) {
                            textValue = "false";
                        }
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        if ("".equals(pattern)) {
                            pattern = "yyy-MM-dd";
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else {
                        if (null == value || "".equals(value)) {
                            value = "";
                        } else {
                            textValue = value.toString();
                        }
                    }
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                    if (textValue != null) {
                        Pattern p = NUMBER_PATTERN;
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            XSSFRichTextString richString = new XSSFRichTextString(
                                    textValue);
                            font2.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font2);
                            cell.setCellValue(richString);
                        }
                    }
                
                }
            }
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("poi处理出错");
        } finally{
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return baos.toByteArray();
    }