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);

          }

       }

   }

Add Columns / Fields Programmatically to a SharePoint List

 class Program
    {
        static void Main(string[] args)
        {
            using (SPSite oSPsite = new SPSite("http://"))
            {
                oSPsite.AllowUnsafeUpdates = true;

                using (SPWeb oSPWeb = oSPsite.OpenWeb())
                {
                    oSPWeb.AllowUnsafeUpdates = true;

                    /* get the SPList object by list name*/
                    SPList lst = oSPWeb.Lists["EmpList"];

                    /* create a Numeric field for EmpID */
                    SPFieldNumber fldEmpID = (SPFieldNumber)lst.Fields.CreateNewField(SPFieldType.Number.ToString(), "EmpID");
                    fldEmpID.Required = true;
                    fldEmpID.DisplayFormat = SPNumberFormatTypes.NoDecimal;

                    /* create a Text field for Name */
                    SPFieldText fldName = (SPFieldText)lst.Fields.CreateNewField(SPFieldType.Text.ToString(), "Name");
                    fldName.Required = true;
                    fldName.MaxLength = 50;

                    /* create a Date field for Dob*/
                    SPFieldDateTime fldDob = (SPFieldDateTime)lst.Fields.CreateNewField(SPFieldType.DateTime.ToString(), "Dob");
                    fldDob.DisplayFormat = SPDateTimeFieldFormatType.DateOnly;

                    /* create a Currency field for Salary */
                    SPFieldCurrency fldSal = (SPFieldCurrency)lst.Fields.CreateNewField(SPFieldType.Currency.ToString(), "Salary");
                    fldSal.Currency = SPCurrencyFieldFormats.UnitedStates;
                    fldSal.DisplayFormat = SPNumberFormatTypes.TwoDecimals;

                    /* add the new fields to the list */
                    lst.Fields.Add(fldEmpID);
                    lst.Fields.Add(fldName);
                    lst.Fields.Add(fldDob);
                    lst.Fields.Add(fldSal);

                    /* finally update list */
                    lst.Update();

                    oSPWeb.AllowUnsafeUpdates = false;
                }

                oSPsite.AllowUnsafeUpdates = false;
            }
        }
    }

Creating Fields or Columns in SharePoint List Programmatically using C# (Single Line Test, Multiline Text, Choice, Number, Currency, Date Time, Lookup, Person or Group)

Question:Creating Fields or Columns in SharePoint List Programmatically using C#(Single Line Test, Multiline Text, Choice, Number, Currency, Date Time, Lookup, Person or Group)

It is very easy to add a column in a SharePoint List from website front-end, we just need to click "List Settings -> Create Column" and enter the title and type of the field, and click "OK" button. That's it, new field is created.

In simple terms "When you create a custom list, a new empty list is created with just two columns - Title and Attachments. The list contains a single default view. Once you create the list, you can add more columns, views, and so on"

We can use SharePoint API to create these fields and associate the corresponding attributes programmatically within an existing List. Provided below is a code snippet in C#.Net.

using (SPWeb web = new SPSite("http://kapplesofts/").OpenWeb())

            {

                web.AllowUnsafeUpdates = true;

                SPList list = web.Lists["KapplesSofts List"];

                //Creating List Fields or Columns

                //single Line Text Field

                string SLTInternalName = list.Fields.Add("Single Line Text", SPFieldType.Text, false);

                //'false' means Required field not mandatory

                //'true' means Required field mandatory 

                //Multiline Text Field

                string MLTInternalName = list.Fields.Add("Multiline Text", SPFieldType.Note, false);

                //Choice Field

                string fieldStatusName = list.Fields.Add("Status", SPFieldType.Choice, false);

                SPFieldChoice fieldStatus = (SPFieldChoice)list.Fields.GetFieldByInternalName(fieldStatusName);

                string[] activityTypes = { "Active", "Closed" };

                fieldStatus.Choices.AddRange(activityTypes);

                fieldStatus.DefaultValue = "Active";

                fieldStatus.Update();

                //or Choice Field Dropdown

                SPField fieldCategory = new SPField(list.Fields, "Choice", "Category");

                list.Fields.Add(fieldCategory);

                list.Update();

                SPFieldChoice CategoryField = (SPFieldChoice)list.Fields["Category"];

                CategoryField.EditFormat = SPChoiceFormatType.Dropdown;

                CategoryField.Choices.Add("Non Compliance");

                CategoryField.Choices.Add("Observation");

                CategoryField.Choices.Add("Suggestion");

                CategoryField.Choices.Add("Improvement");

                CategoryField.DefaultValue = "Observation";

                CategoryField.Update();

                //or Choice Field RadioButtons

                SPField field = new SPField(list.Fields, "Choice", "Siverity");

                list.Fields.Add(field);

                list.Update();

                SPFieldChoice rdField = (SPFieldChoice)list.Fields["Siverity"];

                rdField.EditFormat = SPChoiceFormatType.RadioButtons;

                rdField.Choices.Add("Major Finding");

                rdField.Choices.Add("Minor Finding");

                //DefaultValue empty

                rdField.Update();

                //Number Field

                string NumberInternalName = list.Fields.Add("Number", SPFieldType.Number, false);

                SPFieldNumber fieldTimeSpent = new SPFieldNumber(list.Fields, NumberInternalName);

                fieldTimeSpent.DisplayFormat = SPNumberFormatTypes.NoDecimal;

                fieldTimeSpent.Update();

                // Currency field

                string CurrencyInternalName = list.Fields.Add("Salary", SPFieldType.Currency, false);

                SPFieldCurrency currencyTimeSpent = new SPFieldCurrency(list.Fields, CurrencyInternalName);

                currencyTimeSpent.Currency = SPCurrencyFieldFormats.India;

                currencyTimeSpent.DisplayFormat = SPNumberFormatTypes.TwoDecimals;

                currencyTimeSpent.Update();

        

                //Date Time Field

                string fieldInternalName = list.Fields.Add("DOB", SPFieldType.DateTime, false);

                SPFieldDateTime fieldDateTime = new SPFieldDateTime(list.Fields, fieldInternalName);

                fieldDateTime.DisplayFormat = SPDateTimeFieldFormatType.DateOnly;

                fieldDateTime.DefaultValue = "[today]";

                fieldDateTime.Update();

                //Lookup Field

                SPField titleField = list.Fields.GetField("Title");

                titleField.Title = "Employee Name";

                titleField.Update();                              

                SPList lookupList = web.Lists["Sample List"];

                list.Fields.AddLookup("Employee FirstName", lookupList.ID, false);

                SPFieldLookup lkp = (SPFieldLookup)list.Fields["Employee FirstName"];

                lkp.LookupField = list.Fields["Employee Name"].InternalName;

                lkp.Update();

                //Boolean Field

                string BooleanInternalName = list.Fields.Add("Boolean", SPFieldType.Boolean, false);

                //Person or Group Field

                string fieldUserName = list.Fields.Add("Employee Name", SPFieldType.User, false);

                SPFieldUser userField = new SPFieldUser(list.Fields, fieldUserName);

                userField.AllowMultipleValues = false;

                userField.Required = false;

                userField.SelectionMode = SPFieldUserSelectionMode.PeopleOnly;

                userField.LookupField = "Title";

                userField.Update();

                list.Update();

                web.AllowUnsafeUpdates = false;

            }

Out Put list new form:

List setting: