远程从Mongodb 数据库中导出数据为Excel 文件

来源：互联网发布：户外拍摄技巧淘宝编辑：程序博客网时间：2024/06/06 00:16

版本:Mongodb2.4.8

通过页面下载Excel文件

jsp

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPEhtml PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<metahttp-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
        <formmethod="post">
            <ahref="/Demo/DownDemo"><h2>下载</h2></a>
        </form>
</body>
</html>

　　Mongodb配置文件dbconfig.properties：

?
1
2
3
4
url=localhost
port=27017
db=movie
table=mv

　　连接Mongodb工具类:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package util;
import java.io.IOException;
import java.io.InputStream;
import java.net.UnknownHostException;
import java.util.Properties;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.Mongo;
import com.mongodb.MongoException;
public class DBConn {
    privatestatic String url;
    privatestatic int port;
    privatestatic String db;
    privatestatic String table;
    // 初始化加载
    static{
        //加载
        Properties p =new Properties();
        InputStream input = DBConn.class.getClassLoader().getResourceAsStream("dbconfig.properties");
        try{
            p.load(input);
            url = p.getProperty("url");
            port = Integer.valueOf(p.getProperty("port"));
            db = p.getProperty("db");
            table = p.getProperty("table");
        }catch (IOException e) {
            e.printStackTrace();
        }finally{
            if(input != null) {
                try{
                    input.close();
                }catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 获取连接mongodb
     * @Description:
     * @param @return 
     * @return DBCollection 返回类型
     */
    publicstatic DBCollection getConn(){
        DBCollection conn =null;
        try{
            Mongo m =new Mongo(url,port);
            DB d = m.getDB(db);
            conn = d.getCollection(table);
        }catch (UnknownHostException e) {
            e.printStackTrace();
        }catch (MongoException e) {
            e.printStackTrace();
        }
        returnconn;
    }
    /**
     * 关闭连接
     * @Description:
     * @param @param m 
     * @return void 返回类型
     */
    publicstatic void getClose(Mongo m){
        m.close();
    }
}

　　读取Mongodb数据写入到excel中:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
public class WriteExcelUtils {
    /**
     * @Description:mongdb中读取数据写入到Excel
     * @param  title
     * @param  filename
     * @param  rownum
     * @param  cursor 
     * @return void 返回类型
     */
    publicstatic void  Excel(DBCursor cursor,OutputStream out) {
        Workbook book =new HSSFWorkbook();
        // 获取标题
        DBObject ob = cursor.toArray().get(0);
        ArrayList<String> title =new ArrayList<>();
        for(String key:ob.keySet()){
            if(key.equals("_id")) {
                continue;
            }
            title.add(key);
        }
        // 创建sheet
        Sheet sheet = book.createSheet();
        try{
            // 写入标题栏
            Row row =null;
            // 标题栏的行数
            Cell cell =null;
            for(inti = 0;i< (cursor.count() +1);i++){
                // 标题栏
                if(i == 0) {
                    row = sheet.createRow(i);
                    for(int j = 0; j < title.size(); j++) {
                        cell = row.createCell(j);
                        // 设置标题栏
                        cell.setCellValue(title.get(j));
                    }
                    continue;
                }
                // 写入数据
                row = sheet.createRow(i);
                DBObject obj =null;
                for(int j = 0; j < title.size(); j++) {
                    cell = row.createCell(j);
                    obj = cursor.toArray().get(j);   
                    for(String key :obj.keySet()){
                        if(key.equals("_id")) {
                            continue;
                        }
                        if(key.equals(title.get(j))) {
                            cell.setCellValue((String)(obj.get(key)));
                        }
                    }
                }
            }
            // 写入到excel
            book.write(out);
        }catch (IOException e1) {
            e1.printStackTrace();
        }finally {
            try{
                out.flush();
                out.close();
            }catch (IOException e) {
                e.printStackTrace();
            }
        }
 
    }
}

　　Servlet：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package servlet;
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.DBObject;
 
import util.DBConn;
import util.WriteExcelUtils;
 
/**
 * Servlet implementation class DownDemo
 */
@WebServlet("/DownDemo")
public class DownDemo extendsHttpServlet {
    privatestatic final long serialVersionUID = 1L;
        
    /**
     * @see HttpServlet#HttpServlet()
     */
    publicDownDemo() {
        super();
        // TODO Auto-generated constructor stub
    }
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protectedvoid doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        // TODO Auto-generated method stub
        doPost(request, response);
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protectedvoid doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        // TODO Auto-generated method stub
        getMongodbExcle(request, response);
    }
     
    /**
     * 从mongdb中读取数据下载到客户端
     * @Description:
     * @param  request
     * @param  response 
     * @return void 返回类型
     */
    privatevoid getMongodbExcle(HttpServletRequest request, HttpServletResponse response){
         // 设置请求
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition","attachment;filename=data.xls");
        // 从mongodb中读取数据
        DBCollection conn = DBConn.getConn();
        DBCursor cursor = conn.find();
        OutputStream out =null;
        try{
            out =new BufferedOutputStream(response.getOutputStream());
        }catch (IOException e) {
            e.printStackTrace();
        }
       WriteExcelUtils.Excel(cursor,out);
        
         
    }
}

阅读全文

0 0

远程从Mongodb 数据库中 导出数据为Excel 文件

远程从Mongodb 数据库中导出数据为Excel 文件