Java将多个数据表导出多个Sheet到Excel文件

将MySQL数据库中的多个表导出到Excel文件，并在一个Excel文件中以多个sheet的形式存储下来。
1、建立Excel文件，并获取数据表数据：

    private static void process(String[] sqls) {
        Connection conn = getMySqlConnection();
        HSSFWorkbook workbook = new HSSFWorkbook();//声明工作薄
        Statement st = null;
        ResultSet rs = null;
        int count = sqls.length;
        for (String sql : sqls) {
            try {
                st = conn.createStatement();
                rs = st.executeQuery(sql);
                ResultSetMetaData data = rs.getMetaData();//根据查询的结果，分析结果集的元数据

                System.out.println("execute sql : " + sql);

                creatExcel(workbook, rs, data, count--);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Toolkit.close(conn, rs, st);
        System.out.println("--- process end ---");
    }

2、创建Excel文件中的不同表，并将数据写入表中：

    public static void creatExcel(HSSFWorkbook workbook, ResultSet rs, ResultSetMetaData data, int count) {
        HSSFSheet sheet = null;
        try {
            if (count == 3) {
                sheet = workbook.createSheet("mt_cv_brand");//声明表
            } else if (count == 2) {
                sheet = workbook.createSheet("mt_cv_series");//声明表
            } else if (count == 1) {
                sheet = workbook.createSheet("mt_cv_model");//声明表
            }

            FileOutputStream outputStream = export(sheet, rs, data);
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

将数据写入FileOutputStream中：

    public static FileOutputStream export(HSSFSheet sheet, ResultSet rs, ResultSetMetaData data) {
        FileOutputStream outputStream = null;
        try {
            int cols = data.getColumnCount();//获取结果行数量
            //获取所有列名,并创建列名
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < cols; i++) {
                String colName = data.getColumnName(i+1);
                HSSFCell cell = row.createCell(i);//创建一个新的列
                cell.setCellValue(colName); //写入列名
            }
            int index = 1;
            while (rs.next()) {
                row = sheet.createRow(index++);
                for (int i = 0; i < cols; i++) {
                    String val = rs.getString(i+1);
                    HSSFCell cel = row.createCell(i);
                    cel.setCellValue(val);
                }
            }
            outputStream = new FileOutputStream(FILE_PATH);
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            Toolkit.close(rs, null);
        }
        return outputStream;
    }

– 将数据写入FileOutputStream中（存储为不同的数据类型）：

    public static FileOutputStream export(HSSFSheet sheet, ResultSet rs, ResultSetMetaData data) {
        FileOutputStream outputStream = null;
        try {
            int cols = data.getColumnCount();//获取结果行数量
            //获取所有列名,并创建列名
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < cols; i++) {
//              String colName = data.getColumnName(i+1);//获取指定列的名称。
                String colName = data.getColumnLabel(i+1);//获取用于打印输出和显示的指定列的建议标题。
                HSSFCell cell = row.createCell(i);//创建一个新的列
                cell.setCellValue(colName); //写入列名
            }
            int index = 1;
            while (rs.next()) {
                row = sheet.createRow(index++);
                for (int i = 0; i < cols; i++) {
                    Object val = rs.getObject(i + 1);
                    HSSFCell cel = row.createCell(i);
                    if (val instanceof Integer) {
                        cel.setCellType(cel.CELL_TYPE_NUMERIC);
                        int intVal = ((Integer) val).intValue();
                        cel.setCellValue(intVal);
                    } else if (val instanceof Float) {
                        cel.setCellType(cel.CELL_TYPE_NUMERIC);
                        float floatVal = ((Float) val).floatValue();
                        cel.setCellValue(floatVal);
                    } else if (val instanceof Double) {
                        cel.setCellType(cel.CELL_TYPE_NUMERIC);
                        double doubleVal = ((Double) val).doubleValue();
                        cel.setCellValue(doubleVal);
                    } else {
                        cel.setCellValue((String)val);
                    }
                }
            }
            outputStream = new FileOutputStream(FILE_PATH);
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            ToolkitForSpider.close(rs, null);
        }
        return outputStream;
    }