Read From Excel & Import to SharePoint List – Using Web Services
Another code snippet using SharePoint-Web Services. The requirement is: End-User wants to read data from Microsoft Excel (it will be placed at c:\contracts.xls) and append it to SharePoint List (List Name: Contracts) on-demand from his desktop.
So the idea is: let’s 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);
}
}
}
List web service reference: http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx
I am using Visual studio 2015, I was not able to add Web reference. Did some research on it on some forums and tried several approaches but couldn’t possibly find a solution. Let me know if there is an alternate option to replace web reference and code something inline to connect to the sharepoint url.
How to update the lookup fields and people and group fields.
Lookup fields need to use the format of – number;#LookupValue
Where the number is the index of the lookup item. So, You got to get the index using GetListItems or some other method first!
Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();
can you please explain what this statement is for??
That is to Initiate SharePoint List Web Service! Just right click the Project >> Add Web Reference >> Specify SharePoint List Web service URL >> Give name as: Site1.Lists