Read From Excel & Import to Sharepoint List – Using Web Services

Another code snippet using SharePoint-Web Services. Requirement is: End-User wants to read data from Microsoft Excel (it will be placed atc:\contracts.xls) and append to SharePoint List (List Name: Contracts) on-demand from his desktop.

So the idea is: Lets build a console application, Add a web service reference to SharePoint List web service http://<Site>/_vti_bin/Lists.asmx and give it to the end user. Let him run the console application from his desktop on-demand.

using System;
using System.Collections;
using System.Data;
using System.Xml;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;

namespace ReadFromExcelImportToSharePoint
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime dt;
            string dt1;
   //initialize Web Service Reference
   Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();
   list.Credentials = System.Net.CredentialCache.DefaultCredentials;

            XmlNode contacts = list.GetList("Contracts");  

   XmlDocument doc = new XmlDocument();

   string listName = contacts.Attributes["ID"].Value;

            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\contracts.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM [Sheet1$]";
                    connection.Open();
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        int i = 0;
                        while (dr.Read())
                        {
                            lblRecordNo.Text = i.ToString();

                            XmlElement elBatch = doc.CreateElement("Batch");
                            elBatch.SetAttribute("OnError", "Continue");
                            elBatch.SetAttribute("ListVersion", "1");

                            XmlElement el1 = doc.CreateElement("Method");
                            el1.SetAttribute("ID", "1");
                            el1.SetAttribute("Cmd", "New");

                            XmlElement field1 = doc.CreateElement("Field");
                            field1.SetAttribute("Name", "ID");
                            field1.InnerText = "New";

                            //From Here List Fields starts.
                            XmlElement field2 = doc.CreateElement("Field");
                            field2.SetAttribute("Name", "Title");
                            field2.InnerText = dr["i-Many number"].ToString();

                            XmlElement field3 = doc.CreateElement("Field");
                            field3.SetAttribute("Name", "Executing_x0020_Site");
                            field3.InnerText = dr["Executing Site"].ToString();

                            XmlElement field4 = doc.CreateElement("Field");
                            field4.SetAttribute("Name", "SAP_x002f_Legacy_x0020_Pricing_x");
                            field4.InnerText = dr["SAP/Legacy Pricing System"].ToString();

                            XmlElement field5 = doc.CreateElement("Field");
                            field5.SetAttribute("Name", "Customer_x0020_Name");
                            field5.InnerText = dr["Customer Name"].ToString();

                            XmlElement field6 = doc.CreateElement("Field");
                            field6.SetAttribute("Name", "Type_x0020_of_x0020_Contract");
                            field6.InnerText = dr["Type of Contract"].ToString();

                            XmlElement field7 = doc.CreateElement("Field");
                            field7.SetAttribute("Name", "Contract_x0020_Description");
                            field7.InnerText = dr["Contract Description"].ToString();

                            XmlElement field8 = doc.CreateElement("Field");
                            field8.SetAttribute("Name", "Effective_x0020_Date");
                            dt = Convert.ToDateTime(dr["Effective Date"].ToString()) ;
                            dt1=dt.ToString("u");
                            field8.InnerText = dt1;

                            XmlElement field9 = doc.CreateElement("Field");
                            field9.SetAttribute("Name", "SBU");
                            field9.InnerText = dr["SBU"].ToString();

                            XmlElement field10 = doc.CreateElement("Field");
                            field10.SetAttribute("Name", "Customer_x0020_Type");
                            field10.InnerText = dr["Customer Type"].ToString();

                            elBatch.AppendChild(el1);

                            el1.AppendChild(field1);
                            el1.AppendChild(field2);
                            el1.AppendChild(field3);
                            el1.AppendChild(field4);
                            el1.AppendChild(field5);
                            el1.AppendChild(field6);
                            el1.AppendChild(field7);
                            el1.AppendChild(field8);
                            el1.AppendChild(field9);
                            el1.AppendChild(field10);
                            XmlNode rNode = list.UpdateListItems(listName, elBatch);

                            // 0x00000000 returned means that the list item was inserted correctly...
                           //   Console.WriteLine(rNode.InnerText);

                            i = i + 1;
                        }
                    }
                }
            }
       Console.WriteLine("No.of Records Imported:" +i);
          }
       }
   }