使用POI将数据库导出为excel表格

使用POI将数据库导出为excel表格

准备

使用的是POI,是Apache项目,目前已经很少有人用jxl了,学就学习常用的东西,没有必要去学冷门.
此种方式使用的map封装的数据,操作比较的简单使用的是mybatis+map的方式.
另有一种方式略微复杂,使用jdbc+list,效率高些: https://blog.csdn.net/sqlgao22/article/details/100736325

数据库

        <!--操作xls类型文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--操作xlsx类型文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

思路

将数据库中的内容全部取出存到实体类的list中,
将表格的一行看作是一个实体类的映射,表头是实体类的属性,因此,重要的是将实体类转换为map集合,才能取出map中的key.
幸运的是,我们不用自己去反射去组合,spring框架中有一个很好的工具类:
org.springframework.cglib.beans.BeanMap,可以方便的使用.
我将方法做了简单的封装,可以使用.

public class MapAndBeanUtil {
    /**
     * 将实体类转换为一个Map
     */
    public static <T> Map<String,Object> beanToMap (T bean) {
        Map<String, Object> map = new HashMap<>();
        //创建一个BeanMap
        BeanMap beanMap = BeanMap.create(bean);
        //将BeanMap转换为Map
        Iterator iterator = beanMap.keySet().iterator();
        while (iterator.hasNext()) {
            String key = (String) iterator.next();
            map.put(key, beanMap.get(key));
        }
        return map;
    }

    /**
     * 实体类集合转换为List<Map>
     */
    public static <T> List<Map<String, Object>> beansToMap(List<T> beanList) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        Iterator<T> iterator = beanList.iterator();
        while (iterator.hasNext()) {
            T bean = iterator.next();
            Map<String, Object> map = beanToMap(bean);
            mapList.add(map);
        }
        return mapList;
    }

    /**
     * 将map转化为实体类
     */
    public static <T> T mapToBean(Map<String, Object> map,Class<T> clazz) throws IllegalAccessException, InstantiationException {
        T bean = clazz.newInstance();
        BeanMap beanMap = BeanMap.create(bean);
        //将map转换为bean
        beanMap.putAll(map);
        return bean;
    }

    /**
     * 将List<Map>转化为实体类
     */
    public static <T> List<T> mapsToBeans(List<Map<String, Object>> mapList,Class<T> clazz) throws IllegalAccessException, InstantiationException {
        List<T> list = new ArrayList<>();
        Iterator<Map<String, Object>> iterator = mapList.iterator();
        while (iterator.hasNext()) {
            Map<String, Object> map = iterator.next();
            T bean = mapToBean(map, clazz);
            list.add(bean);
        }
        return list;
    }
}

实现

有两种方式实现,:
方式一:创建一个新的文件,只需要指定文件的路径即可,
方式二:直接准备一个excel将数据写入.

先看方式一

public class CreatExcel {
    public static void writeExcel(List<Map<String,Object>> dataList,String filePath) throws Exception {
        //创建文件
        File path = new File(filePath);
        if (!path.exists()) {
            path.mkdirs();
        }
        String fileName = System.currentTimeMillis() + ".xlsx";
        File file = new File(path, fileName);

        //创建一个excel对象
        Workbook workbook = new XSSFWorkbook();

        //创建第一个工作簿
        Sheet sheet = workbook.createSheet("dbName");

        //将数据库字段（map的key）写入第一行
        Map<String, Object> firstRowMap = dataList.get(0);
        Set<String> keySet = firstRowMap.keySet();
        Object[] keyArr = keySet.toArray();
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < keyArr.length; i++) {
            //创建单元格并写入数据
            Cell cell = row0.createCell(i);
            cell.setCellValue(keyArr[i].toString());
        }

        //将数据写入其他行
        for (int i = 0; i < dataList.size(); i++) {
            //控制有多少行
            Map<String, Object> rowMap = dataList.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < rowMap.size(); j++) {
                Cell cell = row.createCell(j);
                Object o = rowMap.get((String) keyArr[j]);
                //除掉空值,也可以写入对应类型,此处直接写入string
                cell.setCellValue(null==o?"":o.toString());
            }
        }

        //输出文件
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
    }
}

测试

读取数据库的代码就不放了.

    @Test
    public void Test01() throws Exception {
        List<User> allUser = userController.getAllUser();
        List<Map<String, Object>> mapList = MapAndBeanUtil.beansToMap(allUser);
        CreatExcel.writeExcel(mapList,"d://222222/333/444");
    }

导出的结果就是这样,文件也生成了.顺序需要自己调整.
原因相信不用我说,都知道.

方式二

方式二:直接准备一个excel将数据写入.方法大同小异

public class WriteExcel {
    public static void writeExcel(List<Map<String,Object>> dataList,File file) throws Exception {
        //判断文件类型获取文件
        Workbook workbook = getWorkbook(file);

        //获取第一个工作簿
        Sheet sheet = workbook.getSheetAt(0);

        //清除表内的原有的数据
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (null!=row) {
                sheet.removeRow(row);
            }
        }
        //将数据库字段（map的key）写入第一行
        Map<String, Object> firstRowMap = dataList.get(0);
        Set<String> keySet = firstRowMap.keySet();
        Object[] keyArr = keySet.toArray();
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < keyArr.length; i++) {
            row0.createCell(i).setCellValue(keyArr[i].toString());
        }
        //将数据写入其他行
        for (int i = 0; i < dataList.size(); i++) {
            //控制有多少行
            Map<String, Object> rowMap = dataList.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < rowMap.size(); j++) {
                Cell cell = row.createCell(j);
                System.out.println(keyArr[j]);
                cell.setCellValue(null==(rowMap.get((String)keyArr[j]))?"":rowMap.get(keyArr[j]).toString());
            }
        }
        //输出文件
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
    }

    private static Workbook getWorkbook(File file) throws Exception {
        Workbook workbook = null;
        FileInputStream fis = new FileInputStream(file);
        if (file.getName().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        } else if (file.getName().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else {
            throw new Exception("格式错误");
        }
        return workbook;
    }
}

结果同样写入.