7

On my app I have an object of records, let's say I have todo a quiz with more than 5 guys, so I can then get the results of all of them and I have an Object with all of that information, is it possible to convert this Object to an Excel file? For example : 

|---------------------|------------------|
|        Quiz         |     Question 1   |...
|---------------------|------------------|
|      Quiz Name      |    What's 1+1    |...
|---------------------|------------------|

Something like this, to know each user what answered and its score, all of it I have it on my Object.

And then if I can send it via Mail the .xls or whatever format.

EDIT

For example I'll need Quiz name and if I can add more about this quiz is ok, but then I need to add all of users have asked this quiz (another object from api) so I could get the name and the score.

I'd like to have a .csv or an Excel whatever where it says explicit :

Let's say I have a list of :

  1. createdAt
  2. updatedAt
  3. user (just the user.email)
  4. quiz (just the quiz.name)
  5. totalPoints

So I want to populate it to the .csv or an Excel whatever

StuartDTO
  • 783
  • 7
  • 26
  • 72

4 Answers4

6

A CSV file is a simple comma separated text file. In your case, the format will be:

Quiz,Question 1
Quiz Name,What's 1+1

As long as you are able to write records in above format to a file with extension "csv", you will be able to open it in excel and email it too.

Please refer to following stackoverflow post.

How to create a .csv on android

farhanjk
  • 1,652
  • 16
  • 17
4

Here is an example of what you could do.

First I created a Question class:

class Question {
    String question;
    String answer;

    Question(String question, String answer) {
        this.question = question;
        this.answer = answer;
    }
}

And a Quiz class:

public class Quiz {

    String quizName;
    List<Question> questions;

    void addQuestion(Question question) {
        if (null == questions) {
            questions = new ArrayList<>();
        }
        questions.add(question);
    }
}

Then, here is the actual application, where I make use of Apache POI:

public class MailExcel {

    public static void main(String[] args) {

        //Creating the quiz

        Quiz mQuiz = new Quiz();
        mQuiz.quizName = "Excel-quiz";
        Question question1 = new Question("Where do you find the best answers?", "Stack-Overflow");
        Question question2 = new Question("Who to ask?", "mwb");
        mQuiz.addQuestion(question1);
        mQuiz.addQuestion(question2);


        //Creating the workbook

        Workbook workbook = new XSSFWorkbook();
        CreationHelper creationHelper = workbook.getCreationHelper();
        Sheet sheet = workbook.createSheet("Quiz");
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        row1.createCell(0).setCellValue("Quiz");
        row2.createCell(0).setCellValue(mQuiz.quizName);
        int col = 1;
        for (Question question: mQuiz.questions) {
            row1.createCell(col).setCellValue("Question " + col);
            row2.createCell(col).setCellValue(question.question);
            col++;
        }


        //Creating and saving the file

        FileOutputStream file = null;
        try {
            file = new FileOutputStream("quiz.xlsx");
            workbook.write(file);
            file.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

What is important, is that you include the jar-files for org.apache.poi. Or, as I did, add the dependencies to your Maven pom-file (or gradle file e.g., if you do Android development). Here is my pom-file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>mail-excel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

</project>

Hope this works for you (does for me)!

I uploaded my solution to GitHub: https://github.com/mwbouwkamp/create-excel

In case of Android development, add the following dependency:

implementation "org.apache.poi:poi:3.17"
implementation "org.apache.poi:poi-ooxml:3.17"
MWB
  • 1,830
  • 1
  • 17
  • 38
  • Add the right gradle dependencies: https://www.blueappsoftware.com/how-to-read-excel-file-in-android-tutorial/ – MWB Apr 14 '19 at 15:13
4

You can use Open CSV also.

<dependency> 
    <groupId>com.opencsv</groupId> 
    <artifactId>opencsv</artifactId> 
    <version>4.1</version> 
</dependency>

you can refer this.

Java Object to CSV file

Dhrumil Patel
  • 364
  • 1
  • 9
3

If you want to use your app in offline then you can use Apache POI, already answered by MWB but since you mentioned to email the file, I presume there is no restrictions on the internet access and so you can consider the following solution.

Google App Scripts make it quite simple to automate the process of fetching data from Google Sheets, composing a template email with the specified data from Google Sheets. Google has SHEETS v4 API which you can integrate in your Android app to work with a sheet. You can do all this automated process using JavaScript in Google App Script or use the API's if you prefer to integrate in your app.

Mirwise Khan
  • 1,317
  • 17
  • 25