Excel doc contents to webservice

Question

I have a wpf staff creation window in which I can create basic information like first name, last name etc this creates the staff in my REST web service. An example:

Client side:

    private void CreateStaffMember_Click(object sender, RoutedEventArgs e)
    {
        string uri = "http://localhost:8001/Service/Staff";
        StringBuilder sb = new StringBuilder();
        sb.Append("<Staff>");
        sb.AppendLine("<FirstName>" + this.textBox1.Text + "</FirstName>");
        sb.AppendLine("<LastName>" + this.textBox2.Text + "</LastName>");
        sb.AppendLine("<Password>" + this.passwordBox1.Password + "</Password>");
        sb.AppendLine("</Staff>");
        string NewStudent = sb.ToString();
        byte[] arr = Encoding.UTF8.GetBytes(NewStudent);
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
        req.Method = "POST";
        req.ContentType = "application/xml";
        req.ContentLength = arr.Length;
        Stream reqStrm = req.GetRequestStream();
        reqStrm.Write(arr, 0, arr.Length);
        reqStrm.Close();
        HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
        MessageBox.Show("Staff Creation: Status " + resp.StatusDescription);
        reqStrm.Close();
        resp.Close();
    }

Web Service side:

    #region POST

    [OperationContract]
    [WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.Bare, RequestFormat = WebMessageFormat.Xml, ResponseFormat = WebMessageFormat.Xml, UriTemplate = "/Staff")]
    void AddStaff(Staff staff);

    #endregion

    public void AddStaff(Staff staff)
    {
        staff.StaffID = (++eCount).ToString();
        staff.Salt = GenerateSalt();
        byte[] passwordHash = Hash(staff.Password, staff.Salt);
        staff.Password = Convert.ToBase64String(passwordHash);
        staffmembers.Add(staff);
    }

All fine on that side, but Im looking to "import" the staff details from an excel spreadsheet, not sure if import is the correct word but I want to take the first names and last names contained in such n such spreadsheet and add them to the web service from the client side wpf application.

How would I go about it? I have my open file dialog:

    private void Import_Click(object sender, RoutedEventArgs e)
    {
        Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        // Show open file dialog box
        Nullable<bool> result = dlg.ShowDialog();

        // Process open file dialog box results
        if (result == true)
        {
            // Open document
            string filename = dlg.FileName;
        }
    }

So I open my excel spread sheet then how would I go about taking the inner contents and sending it to the web service? Really stuck on the code or how to go about it :/

Just looking for an automated way of adding staff members rather than manually typing the names, but seeing as the staff excel doc could be named anything I wanted the open file dialog box. The structure inside will always be the same first name then last name.

Answer 1

First, here is my test Excel file that contains the Staff you want to import:

(Column 'A' if first name, column 'B' is last name and column 'C' is the password...)

Ok, so assuming that your code calling your web service works, here is my version of the Import_Click method (and a generic method to save new staff):

    private void Import_Click(object sender, RoutedEventArgs e)
    {
        Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        // Show open file dialog box
        Nullable<bool> result = dlg.ShowDialog();

        // Process open file dialog box results
        if (result == true)
        {
            // Open document
            string filename = dlg.FileName;

            Microsoft.Office.Interop.Excel.Application vExcelObj = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Workbook theWorkbook = vExcelObj.Workbooks.Open(filename, Type.Missing, true);

                Worksheet sheet = theWorkbook.Worksheets[1];  // This is assuming that the list of staff is in the first worksheet

                string vFirstName = "temp";
                string vLastName = "temp";
                string vPassword = "temp";
                int vIndex = 1;

                while (vFirstName != "")
                {
                    // Change the letters of the appropriate columns here!  
                    // In my example, 'A' is first name, 'B' is last name and 'C' is the password
                    vFirstName = sheet.get_Range("A" + vIndex.ToString()).Value.ToString();
                    vLastName = sheet.get_Range("B" + vIndex.ToString()).Value.ToString();
                    vPassword = sheet.get_Range("C" + vIndex.ToString()).Value.ToString();

                    this.SaveNewStaff(vFirstName, vLastName, vPassword);

                    vIndex++;

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error processing excel file : " + ex.Message);
            }
            finally {
                vExcelObj.Quit();
            }
        }
    }

    private void SaveNewStaff(string firstName, string lastName, string password) {
        string uri = "http://localhost:8001/Service/Staff";
        StringBuilder sb = new StringBuilder();
        sb.Append("<Staff>");
        sb.AppendLine("<FirstName>" + firstName + "</FirstName>");
        sb.AppendLine("<LastName>" + lastName + "</LastName>");
        sb.AppendLine("<Password>" + password + "</Password>");
        sb.AppendLine("</Staff>");
        string NewStudent = sb.ToString();
        byte[] arr = Encoding.UTF8.GetBytes(NewStudent);
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
        req.Method = "POST";
        req.ContentType = "application/xml";
        req.ContentLength = arr.Length;
        Stream reqStrm = req.GetRequestStream();
        reqStrm.Write(arr, 0, arr.Length);
        reqStrm.Close();
        HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
        //MessageBox.Show("Staff Creation: Status " + resp.StatusDescription);
        reqStrm.Close();
        resp.Close();
    }

Note: I have REMed out the MessageBox in the call to the web service to make sure you are not annoyed by it if the list is long, but you are free to "unREM" it if you need confirmation for every staff creation. In the same line of taught, there is not validation that the creation has occurred successfully. I would need more details to create a decent validation process. Also VERY important, this does not validate if the staff you are saving already exists in the list. If you re-run this import procedure multiple times, it may (and probably will) create duplicate entries.

Cheers