Flat Data into Structured XML

One of the best ways to get data from users, and to archive it, is to use a spreadsheet. This format allows for the easy creation and processing of data. In this tutorial I process an Excel spreadsheet, and pick off values from the spreadsheet, in order to create a well structured XML format, which can be easily parsed by a Web page.

For the demo, I open the spreadsheet, and then process each worksheet at a time, then then for each spreadsheet, I read one row at a time. For each worksheet I then save the contents in an XML file, using a format generated from the worksheet values:

Here is the associated code:

      public static void createTests(string xls, string folder)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            string curr = Directory.GetCurrentDirectory();

            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(curr + "\\" + xls, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing);

            foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
            {
                Microsoft.Office.Interop.Excel.Range excelRange = sheet.UsedRange;
                string book = "", fname = "", subject = "", nq = "", chapter = "", unit = "";
                ArrayList questions = new ArrayList();
                foreach (Microsoft.Office.Interop.Excel.Range row in excelRange.Rows)
                {
                    int rowNumber = row.Row;

                    string[] r = GetRange("A" + rowNumber + ":S" + rowNumber + "", sheet);
                    if (r[0].ToLower() == "book") book = r[1];
                    else if (r[0].ToLower() == "questions") nq = r[1];
                    else if (r[0].ToLower() == "subject") subject = r[1];
                    else if (r[0].ToLower() == "chapter")
                    {
                        chapter = r[1]; unit = r[3];
                        fname = "App_Data\\" + folder + String.Format("{0:00}", Convert.ToInt32(chapter)) + "_" + String.Format("{0:00}", Convert.ToInt32(unit)) + ".xml";
                    }
                    else if (r[0].ToLower() == "title") { }
                    else if (r[0].ToLower() == "") { }
                    else
                    {
                        questions.Add(r);
                    }
                }

                using (StreamWriter sw = File.CreateText(fname))
                {
                    sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\" ?><questions>");
                    sw.WriteLine(String.Format("<subject>{0}</subject>\r\n", subject));
                    sw.WriteLine(String.Format("<no_questions>{0}</no_questions>\r\n", nq));

                    foreach (string[] s in questions)
                    {
                        string xml = getXML(s);
                        sw.WriteLine(xml);
                    }
                    sw.WriteLine("</questions>");
                    sw.Close();
                }
            }
            wb.Close();
        }
        public static string getXML(string[] s)
        {
            //     Title    q1    q2    q3    q4    q5    q6    Ans    Image    Video    Explain    Hint    Level    FeedA    FeedB    FeedC    FeedD    FeedE    FeedF

            string rtn = "<quest>\r\n";
            if (s[0] != "") rtn += String.Format("<title>{0}</title>\r\n", s[0]);
            if (s[1] != "") rtn += String.Format(" <q1>{0}</q1>\r\n", s[1]);
            if (s[2] != "") rtn += String.Format(" <q2>{0}</q2>\r\n", s[2]);
            if (s[3] != "") rtn += String.Format(" <q3>{0}</q3>\r\n", s[3]);
            if (s[4] != "") rtn += String.Format(" <q4>{0}</q4>\r\n", s[4]);
            if (s[5] != "") rtn += String.Format(" <q5>{0}</q5>\r\n", s[5]);
            if (s[6] != "") rtn += String.Format(" <q6>{0}</q6>\r\n", s[6]);
            if (s[7] != "") rtn += String.Format(" <ans>{0}</ans>\r\n", s[7]);
            if (s[8] != "") rtn += String.Format(" <image>{0}</image>\r\n", s[8]);
            if (s[9] != "") rtn += String.Format(" <video>{0}</video>\r\n", s[9]);
            rtn += String.Format("</quest>\r\n");
            return (rtn);
        }
        public static string[] GetRange(string range, Worksheet excelWorksheet)
        {
            Microsoft.Office.Interop.Excel.Range workingRangeCells =
              excelWorksheet.get_Range(range, Type.Missing);
            //workingRangeCells.Select();

            System.Array array = (System.Array)workingRangeCells.Cells.Value2;
            string[] arrayS = ConvertToStringArray(array);

            return arrayS;
        }
        public static string[] ConvertToStringArray(System.Array values)
        {

            // create a new string array
            string[] theArray = new string[values.Length];

            // loop through the 2-D System.Array and populate the 1-D String Array
            for (int i = 1; i <= values.Length; i++)
            {
                if (values.GetValue(1, i) == null)
                    theArray[i - 1] = "";
                else
                    theArray[i - 1] = (string)values.GetValue(1, i).ToString();
            }

            return theArray;
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s