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.
List web service reference: http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx
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); } } }
List web service reference: http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx
Read From Excel & Import to Sharepoint List - Using Web Services
Reviewed by Unknown
on
June 04, 2012
Rating:
Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();
ReplyDeletecan 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
DeleteHow to update the lookup fields and people and group fields.
ReplyDeleteLookup fields need to use the format of - number;#LookupValue
DeleteWhere the number is the index of the lookup item. So, You got to get the index using GetListItems or some other method first!
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.
ReplyDelete