根据查询结果，把数据导出到本地excel

本文以mysql数据为例：

public class exportExcel{

public static void main(String[] args) throws Exception {

    CreateExcelDemo(connectDb());//根据数据库查询结果，导出到excel中

}

public static Connection getConnection() {

    try {

        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://127.0.0.1:3306/guizi?autoreconnect=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&autoReconnect=true&allowMultiQueries=true";

        String username = "root";

        String password = "root";

        Connection conn = DriverManager.getConnection(url, username,password);

        return conn;

    } catch (Exception e) {

        throw new IllegalArgumentException(e);

    }

}

public static List> connectDb(){

        List> list=new ArrayList>();

        Connection conn = getConnection();

        Statement stmt =null;

        ResultSet rs =null;

        try {

            String sql="select city,phone from socialize_user_main where DEL_FLAG=0 limit 120000";

            stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);

            while(rs.next()){

                Map map=new HashMap();

                String city=rs.getString("CITY");

                String phone=rs.getString("PHONE");

                map.put("CITY", city);

                map.put("PHONE", phone);

                list.add(map);

        }

        rs.close();

        stmt.close();

        conn.close();

        }catch(Exception ex){

            ex.printStackTrace();

        }

        return list;

    }

public static void CreateExcelDemo(List> list) throws Exception {

        String fileName="";

        // 第一步，创建一个webbook，对应一个Excel文件

        XSSFWorkbook wb = new XSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        XSSFSheet sheet = wb.createSheet("用户信息");

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short         XSSFRow row = sheet.createRow((int) 0);

        // 第四步，创建单元格，并设置值表头 设置表头居中

        XSSFCellStyle style = wb.createCellStyle();         //style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        // 创建一个居中格式

        XSSFCell cell = row.createCell((short) 0);

        cell.setCellValue("序号");

        cell = row.createCell((short) 1);

        cell.setCellValue("城市");

        cell = row.createCell((short) 2);

        cell.setCellValue("手机号");

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到

        for(int i=0;i<list.size();i++){

            HashMap map =(HashMap)list.get(i);

            // 第四步，创建单元格，并设置值

            row = sheet.createRow((int) i+1);

            XSSFCell celli = row.createCell((short) 0);

            row.createCell((short) 0).setCellValue(i);

            if(map.get("CITY")!=null)

            row.createCell((short) 1).setCellValue((String) map.get("CITY"));             if(map.get("PHONE")!=null)

            row.createCell((short) 2).setCellValue((String) map.get("PHONE"));

    }

    // 第六步，将文件存到指定位置

    try {

        fileName = "H:/export.xlsx";

        FileOutputStream fout = new FileOutputStream(fileName);

        wb.write(fout); fout.close();

    } catch (Exception e) {

        e.printStackTrace();

    }

}