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

版本: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"%> <! DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> < html > < head > < meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> < title >Insert title here</ title > </ head > < body >          < form method="post">              < a href="/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 {      private static String url;      private static int port;      private static String db;      private static 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 返回类型       */      public static 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();          }          return conn;      }      /**       * 关闭连接       * @Description:       * @param @param m        * @return void 返回类型       */      public static 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 返回类型       */      public static 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 ( int i = 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 extends HttpServlet {      private static final long serialVersionUID = 1L;               /**       * @see HttpServlet#HttpServlet()       */      public DownDemo() {          super ();          // TODO Auto-generated constructor stub      }        /**       * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)       */      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {          // TODO Auto-generated method stub          doPost(request, response);      }        /**       * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)       */      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {          // TODO Auto-generated method stub          getMongodbExcle(request, response);      }            /**       * 从mongdb中读取数据下载到客户端       * @Description:       * @param  request       * @param  response        * @return void 返回类型       */      private void 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);                         } }