java中导出EXCEL表格

 

话不多说,直接上代码!就是这么豪爽!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

1.首先说说需要的依赖:

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

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

2.就是工具类createWorkBook  传入的参数是List<实体类>


注意:   这个方法在写的时候考虑到了实体类的序列化,所以大家用的时候一定要注意!
局限:    serialVersionUID在属性数组中索引的位置为0,按照这个逻辑写下去的,大家用的时候根据实际情况去决定!

 public static Workbook createWorkBook(List<KdWebserviceLogEntity> list) {
        // 创建excel工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook();
        // 创建sheet（页），
        Sheet sheet = wb.createSheet();
        //  获得实体类定义的属性
        Field[] fields = null;
        for (int i = 0; i < list.size(); i++) {
            Class cls = list.get(i).getClass();
            fields = cls.getDeclaredFields();
        }

        //实体类中是否被序列化？ iserialVersionUID = false  表示没有被序列化
        boolean isSerialVersionUID = false;
        // isSize用来记录serialVersionUID在属性数组中的位置
        for (int i = 0; i < fields.length; i++) {
            //实体类的属性名
            String columnName = fields[i].getName();
            if ("serialVersionUID".equals(columnName)) {
                isSerialVersionUID = true;
            }
            break;
        }
        // 创建第一行 ，设置列名即实体类的字段名     serialVersionUID在属性数组中索引的位置为0
        Row row = sheet.createRow(0);
        if (isSerialVersionUID) {
            for (int i = 0; i < fields.length; i++) {
                //实体类的属性名
                String columnName = fields[i].getName();
                if (columnName.equals("serialVersionUID")) {
                    continue;
                }
                sheet.setColumnWidth(i - 1, (int) (35.7 * 80));
                Cell cell = row.createCell(i - 1);
                cell.setCellValue(columnName);
            }
        } else {
            for (int i = 0; i < fields.length; i++) {
                String columnName = fields[i].getName();
                sheet.setColumnWidth(i, (int) (35.7 * 80));
                Cell cell = row.createCell(i);
                cell.setCellValue(columnName);
            }
        }
        //设置每行每列的值  list.size()为总共的行数
        for (int i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，从第二行开始创建  在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建(fields.length)个方格
            if (fields[0].getName().equals("serialVersionUID")) {
                for (int j = 0; j < fields.length; j++) {
                    String columnName = fields[j].getName();
                    if (columnName.equals("serialVersionUID")) {
                        continue;
                    }
                    Cell cell = row1.createCell(j - 1);
                    Field f = fields[j];
                    f.setAccessible(true);
                    try {
                        if (f.getType() == java.util.Date.class && f.get(list.get(i)) != null) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String time = sdf.format(f.get(list.get(i)));
                            cell.setCellValue(time);
                        } else if (f.getType() != java.util.Date.class && f.get(list.get(i)) != null) {
                            cell.setCellValue(f.get(list.get(i)).toString());
                        } else {
                            cell.setCellValue("");
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }

                }
            } else {
                for (int j = 0; j < fields.length; j++) {
                    //String columnName = fields[j].getName();
                    Cell cell = row1.createCell(j);
                    Field f = fields[j];
                    f.setAccessible(true);
                    try {
                        if (f.getType() == java.util.Date.class && f.get(list.get(i)) != null) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String time = sdf.format(f.get(list.get(i)));
                            cell.setCellValue(time);
                        } else if (f.getType() != java.util.Date.class && f.get(list.get(i)) != null) {
                            cell.setCellValue(f.get(list.get(i)).toString());
                        } else {
                            cell.setCellValue("");
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return wb;
    }

 public String exportExcel(HttpServletResponse response) {

//        //1.查询获得List<实体类>       
        List<KdWebserviceLogEntity> userlist = kdWebserviceLogService.selectList(new EntityWrapper<KdWebserviceLogEntity>());

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtisTTTTTTTTTTTTTT.createWorkBook(userlist).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(), "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
                bos.flush();
            }
        } catch (final IOException e) {
            try {
                throw e;
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    }

 

 

4.结果是: