Wednesday, 28 May 2014

Creating a SharePoint list Programmatically using xml field definitions.

private static Guid createList(string listName, SPWeb web)
{
      Guid ListId = web.Lists.Add(listName, "Description here", SPListTemplateType.GenericList);
      SPList list= web.Lists.GetList(ListId, false);
      if (list!= null)
      {
          list.Fields.AddFieldAsXml("<field displayname='Field1' enforceuniquevalues='FALSE' indexed='FALSE' required='TRUE' type='Text'>", true, SPAddFieldOptions.AddFieldToDefaultView);
          list.Fields.AddFieldAsXml("<field displayname='Field2' enforceuniquevalues='FALSE' indexed='FALSE' required='FALSE' type='Text'>", true, SPAddFieldOptions.AddFieldToDefaultView);
          list.Fields.AddFieldAsXml("<field displayname='Field3' enforceuniquevalues='FALSE' indexed='FALSE' required='FALSE' type='Text'>", true, SPAddFieldOptions.AddFieldToDefaultView);
          list.Fields.AddFieldAsXml("<field displayname='Field4' enforceuniquevalues='FALSE' indexed='FALSE' required='FALSE' type='Text'>", true, SPAddFieldOptions.AddFieldToDefaultView);

          list.Update();
      }
      return ListId;
}

Tuesday, 27 May 2014

How to read data from excel into a DataSet with c#

Note you will need to install AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe
using System.Data.OleDb;
private static DataSet getDataTableFromXLS(string xlsLocation, string sheet)
{
     DataSet ds = new DataSet();
     try
     {
          OleDbConnection oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
          Data Source=" + xlsLocation + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");

          oledbConn.Open();
          OleDbCommand cmd = new OleDbCommand("SELECT [Username],[First],[Last name],[Email] FROM [" + sheet+ "$]", oledbConn);
          OleDbDataAdapter oleda = new OleDbDataAdapter();
          ds = new DataSet();

          oleda = new OleDbDataAdapter(cmd);
          oleda.Fill(ds);
      }
      catch (Exception ex)
      {
          logException(ex);
      }
      return ds;
}