List test questions in EXCEL and export to Forms

Question

Dear colleagues,

I have 3 questions.

(1) Is it possible to export a question pool (i.e., questions and answer choices) listed in EXCEL to generate a Forms quiz?

(2) On Forms, Is it possible to have Forms randomly select a few questions, and not every question, from this EXCEL list to use as a quiz?

(3) Is it possible to randomize the questions Forms picks up, so that every student would have the same number of questions, but answer different quesiton from the quesion pool?

For instance, I have a quesion pool of about 250 questions and I want to make a Forms quiz. However, I would like the quiz to be 50 questions and would like every student to work on different and random questions.

Hope this makes sense.

Thanks in advance,

Steve

Answer 1

Hi @福田　スティーブ利久 , Welcome to Microsoft Q&A,

I wrote a sample: button2 can read the target excel, store the data in the public questionList, and add the list data into QuestionListRTB. button1 can create three different sets of data from 0 to the maximum value, and extract questions from questionList according to the data. Here the problem is put into RTB1, 2, 3 for demonstration. They can be exported to word, excel, etc. as needed.

I added Microsoft.Office.Interop.Excel, I use office 2016.

using System;
using System.Collections.Generic;
using System.Threading;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace _8_08_1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //Create a class called Question that has the following properties:
        class Question
        {
            public string QuestionText { get; set; }
            public List<string> Options { get; set; }
            public char CorrectOption { get; set; }
        }
        List<Question> questions = new List<Question>();
        string filePath = @"C:\Users\Administrator\Desktop\questions.xlsx";
        int ListSize = 0;
        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Open(filePath);
            Excel.Worksheet worksheet = workbook.Sheets[1]; // get the first worksheet
            int rowCount = worksheet.UsedRange.Rows.Count;
            int colCount = worksheet.UsedRange.Columns.Count;

            //If there is no title, i starts from 1; if there is a title, i starts from 2
            ListSize = rowCount;
            for (int i = 1; i <= rowCount; i++)
            {
                Question question = new Question();
                question.QuestionText = worksheet.Cells[i, 1].Value;
                question.Options = new List<string>();
                question.Options.Add(worksheet.Cells[i, 2].Value);
                question.Options.Add(worksheet.Cells[i, 3].Value);
                question.Options.Add(worksheet.Cells[i, 4].Value);
                question.Options.Add(worksheet.Cells[i, 5].Value);
                // question.CorrectOption = worksheet.Cells[i, 6].Value;
                questions.Add(question);
            }
            // Close workbooks and Excel applications
            workbook.Close();
            excelApp.Quit();

            // release resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

            QuestionListRTB.Text = null;
            for (int i = 0; i < questions.Count; i++)
            {
                QuestionListRTB.Text += $"{i + 1}:" + questions[i].QuestionText + " ";
                for (int j = 0; j < questions[i].Options.Count; j++)
                {
                    QuestionListRTB.Text += questions[i].Options[j] + " ";
                }
                QuestionListRTB.Text += "\n";
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<int> group1 = GenerateRandomNumbers(2, ListSize);
            richTextBox1.Text = null;
            for (int i = 0; i < group1.Count; i++)
            {
                richTextBox1.Text += $"{i + 1}:" + questions[group1[i]].QuestionText + " ";
                for (int j = 0; j < questions[group1[i]].Options.Count; j++)
                {
                    richTextBox1.Text += questions[group1[i]].Options[j] + " ";
                }
                richTextBox1.Text += "\n";
            }

            //For default, the random number is the same, so we need to sleep for a while.
            Thread.Sleep(20);
            List<int> group2 = GenerateRandomNumbers(2, ListSize);
            richTextBox2.Text = null;
            for (int i = 0; i < group2.Count; i++)
            {
                richTextBox2.Text += $"{i + 1}:" + questions[group2[i]].QuestionText + " ";
                for (int j = 0; j < questions[group2[i]].Options.Count; j++)
                {
                    richTextBox2.Text += questions[group2[i]].Options[j] + " ";
                }
                richTextBox2.Text += "\n";
            }

            List<int> group3 = GenerateRandomNumbers(2, ListSize);
            richTextBox3.Text = null;
            for (int i = 0; i < group3.Count; i++)
            {
                richTextBox3.Text += $"{i + 1}:" + questions[group3[i]].QuestionText + " ";
                for (int j = 0; j < questions[group3[i]].Options.Count; j++)
                {
                    richTextBox3.Text += questions[group3[i]].Options[j] + " ";
                }
                richTextBox3.Text += "\n";
            }
        }
        static List<int> GenerateRandomNumbers(int count, int MaxValue)
        {
            List<int> numbers = new List<int>();
            Random random = new Random();

            while (numbers.Count < count)
            {
                int randomNumber = random.Next(0, MaxValue); // Generate a random number between 0 and MaxValue
                if (!numbers.Contains(randomNumber))
                {
                    numbers.Add(randomNumber);
                }
            }
            return numbers;
        }
    }
}

Excel:

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.