Results 1 to 11 of 11
Like Tree1Likes
  • 1 Post By gozzy

Thread: How do I retrieve data from MongoDB in a Excel sheet using Java?

  1. #1
    Deepak is offline Member
    Join Date
    Feb 2017
    Posts
    6
    Rep Power
    0

    Question How do I retrieve data from MongoDB in a Excel sheet using Java?

    How to retrieve data from mongodb database using java poi jar...plz help...???

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    Mongo has an export utility for exporting to CSV, if that's any help.
    If, however, this is some sort of fancy, structured, report in Excel, then you'll need to read the data you want from Mongo using the Mongo client, and then hand that to the part of your application that formats it into an Excel document, using POI.

    So that's two different parts.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    Deepak is offline Member
    Join Date
    Feb 2017
    Posts
    6
    Rep Power
    0

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    i can read data from mongodb database but dont know how to print it on excel sheet this is the code.

    import com.mongodb.MongoClient;
    import com.mongodb.MongoException;
    import com.mongodb.WriteConcern;

    import com.mongodb.DB;
    import com.mongodb.DBCollection;
    import com.mongodb.BasicDBObject;
    import com.mongodb.DBObject;
    import com.mongodb.DBCursor;

    import com.mongodb.ServerAddress;
    import java.util.Arrays;

    public class Getdata {

    public static void main(String args[]) {

    try {

    // To connect to mongodb server
    MongoClient mongoClient = new MongoClient("localhost", 27017);

    // Now connect to your databases
    DB db = mongoClient.getDB("mytest");
    System.out.println("Connect to database successfully");

    DBCollection coll = db.getCollection("myCollection");
    System.out.println("Collection mycol selected successfully");

    DBCursor cursor = coll.find();
    //int i = 1;

    while (cursor.hasNext()) {
    //System.out.println("Inserted Document: \n" + i);
    System.out.println(cursor.next());
    //i++;
    }

    } catch (Exception e) {
    System.err.println(e.getClass().getName() + ": " + e.getMessage());
    }
    }
    }




    2. this code for generate excel sheet





    import java.io.FileOutputStream;
    import java.io.IOException;

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    /**
    * A very simple program that writes some data to an Excel file
    * using the Apache POI library.
    * @author CodeJava - Coding Your Passion
    *
    */
    public class SimpleExcelWriterExample {

    public static void main(String[] args) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Java Books");

    Object[][] bookData = {
    {"Head First Java", "Kathy Serria", 79},
    {"Effective Java", "Joshua Bloch", 36},
    {"Clean Code", "Robert martin", 42},
    {"Thinking in Java", "Bruce Eckel", 35},
    };

    int rowCount = 0;

    for (Object[] aBook : bookData) {
    Row row = sheet.createRow(++rowCount);

    int columnCount = 0;

    for (Object field : aBook) {
    Cell cell = row.createCell(++columnCount);
    if (field instanceof String) {
    cell.setCellValue((String) field);
    } else if (field instanceof Integer) {
    cell.setCellValue((Integer) field);
    }
    }

    }


    try (FileOutputStream outputStream = new FileOutputStream("JavaBooks.xlsx")) {
    workbook.write(outputStream);
    }
    }

    }

    but how to retrieve data from database in excel sheet help ...

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    When posting code please wrap it in code tags so it retains its formatting.

    Right, so the next step is to come up with a model that represents the data you want to output to the sheet. (1)

    Then your Mongo code should create a List<YourModel> which it returns when asked for it. (2)

    Finally the POI code should take a List<YourModel> and use that to create the sheet. (3)

    So that's three steps there.
    Take them one at a time.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    gozzy is offline Member
    Join Date
    Jan 2017
    Posts
    46
    Rep Power
    0

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    Why don't you just export the data to a file x.csv? It will open in Excel by default
    Deepak likes this.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    That's why I mentioned mongoexport in my first post.
    If there's no data crunching or formatting required then that would be the easiest option.

    I can only assume that either this is just for fun, or there is some layout work involved.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    Deepak is offline Member
    Join Date
    Feb 2017
    Posts
    6
    Rep Power
    0

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    gozzy could you give me any example how to export data into .csv file through java.

  8. #8
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    Quote Originally Posted by Deepak View Post
    gozzy could you give me any example how to export data into .csv file through java.
    Put some effort in it, it's not that hard. This is a two step exercise:
    1) Write to a file.
    2) Use a format that matches CSV values.

    How to do both steps is easy to find on teh interwebs.

    Java Code:
    BufferedWriter w = new BufferedWriter( new FileWriter( yourFile));
    ...loop over your data ....
      w.write( your data separated by tab or semi-colon);
    ... end loop
    w.flush()
    w.close();
    Last edited by SurfMan; 02-27-2017 at 10:35 AM.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  9. #9
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    Having said that, CSV format has some specific rules you need to follow regarding quotes etc. If you are using this export to send around the world for business use, make sure you follow the format as specified in RFC4180.

    Or use the excellent OpenCSV library. That does all the heavy lifting for you.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    But Mongo has an exporter that exports direct to CSV.
    So why code that by hand?
    https://docs.mongodb.com/manual/refe...m/mongoexport/
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  11. #11
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: How do I retrieve data from MongoDB in a Excel sheet using Java?

    And another job well done here :)
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

Similar Threads

  1. Replies: 1
    Last Post: 08-24-2012, 09:49 AM
  2. problem in reading data from excel sheet
    By jainmayank33 in forum Advanced Java
    Replies: 2
    Last Post: 11-03-2011, 03:05 AM
  3. How can i store a data from Excel sheet to an array.
    By deshmukh.niraj04 in forum New To Java
    Replies: 0
    Last Post: 05-03-2011, 06:33 AM
  4. Replies: 3
    Last Post: 01-24-2011, 06:37 AM
  5. Save data from Excel sheet to servlet
    By chandu.v in forum Java Servlet
    Replies: 2
    Last Post: 04-10-2008, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •