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 at c:\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);

          }

       }

   }