Programmatically Export data from SharePoint List to Excel SpreadSheet

Hi All,
 Today I will give you the code for exporting data from SharePoint List to Excel.

Ok Without delay, directly going to the code...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data;
using System.IO;
using System.Web.UI;

namespace Excel
{
    class Program
    {
        private static DataTable dataTable;
        private static SPList list;

        static void Main(string[] args)
        {
            try
            {
                SPSite Osite = new SPSite("http://wf13staging.myhcl.com/sites/BPRCreatives/Creative%20CR%20Tracker");
                SPWeb oWeb = Osite.OpenWeb();
                string _siteUrl = oWeb.Url.ToString();
                if (!string.IsNullOrEmpty(_siteUrl))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        using (SPSite site = new SPSite(oWeb.Url))
                        {
                            if (site != null)
                            {
                                SPWeb web = site.OpenWeb();
                                if (web != null)
                                {
                                    #region Export List
                                    string _listName = "Excel List"; // Name of your List
                                    if (!string.IsNullOrEmpty(_listName))
                                    {
                                        list = web.Lists["Excel"];
                                        if (list != null)
                                        {
                                            dataTable = new DataTable();
                                            InitializeExcel(list, dataTable);
                                            string _schemaXML = list.DefaultView.ViewFields.SchemaXml;

                                            if (list.Items != null && list.ItemCount > 0)
                                            {
                                                foreach (SPListItem _item in list.Items)
                                                {
                                                    DataRow dr = dataTable.NewRow();
                                                    foreach (DataColumn _column in dataTable.Columns)
                                                    {
                                                        if (dataTable.Columns[_column.ColumnName] != null && _item[_column.ColumnName] != null)
                                                        {
                                                            dr[_column.ColumnName] = _item[_column.ColumnName].ToString();
                                                        }
                                                    }
                                                    dataTable.Rows.Add(dr);
                                                }
                                            }
                                        }
                                    }
                                    System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                                    grid.HeaderStyle.Font.Bold = true;
                                    grid.DataSource = dataTable;
                                    grid.DataBind();
          // U can modify the name of the excel file according to ur wish :)

                                    using (StreamWriter streamWriter = new StreamWriter(@"F:\Chandra\Excel" + list.Title + ".xls", false, Encoding.UTF8))
                                    {
                                        using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(streamWriter))
                                        {
                                            grid.RenderControl(htmlTextWriter);
                                        }
                                    }

                                    #endregion
                                }
                            }
                        }
                    });
                }
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }

        public static void InitializeExcel(SPList list, DataTable _datatable)
        {
            if (list != null)
            {
                string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
                if (list.Items != null && list.ItemCount > 0)
                {
                    foreach (SPListItem _item in list.Items)
                    {
                        foreach (SPField _itemField in _item.Fields)
                        {
                            if (_schemaXML.Contains(_itemField.InternalName))
                            {
                                if (_item[_itemField.InternalName] != null)
                                {
                                    if (!_datatable.Columns.Contains(_itemField.InternalName))
                                    {
                                        _datatable.Columns.Add(new DataColumn(_itemField.StaticName, Type.GetType("System.String")));
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}



That's it... Directly paste the code and Ur work is done.... ;)

SPListItem.Delete() Vs SPListItem.Recycle()

Hi All,

Good Day. Hope u all are doing good. Today we will see about 2 ways of deleting an item programmatically, which ofcourse are significant on their own ways.

While doing coding , 

SPListItem.Delete() is the most used method to delete an item. There is one more method - SPListItem.Recycle().

SPListItem.Recycle() :

When we use this method, the item gets moved to the Recycle Bin. So that , it can be restored if needed.

SPListItem.Delete() :

When we use this method, the item gets permanently deleted.

Under the hood :

Internally there isn’t much difference between the SPListItem.Delete and SPListItem.Recycle methods. Both call an internal SPListItem.Delete method with a different parameter which determines whether an item should be moved to the Recycle Bin or permanently deleted.

public override void Delete()   
{   
      if (this.HasExternalDataSource)   
      {   
            SPUtility.ValidateFormDigest();   
            string bdcid = (string) ((string) this.GetValue("BdcIdentity"));   
            this.ParentList.DataSource.DeleteItem(bdcid);   
      }   
      else   
      {   
            this.DeleteCore(DeleteOp.Delete);   
      }   
}   
public System.Guid Recycle()   
{   
      if (this.HasExternalDataSource)   
      {   
            SPExternalList.ThrowNotSupportedExceptionForMethod("Recycle", base.GetType());   
      }   
      return this.DeleteCore(DeleteOp.Recycle);   

}   

I hope you will be getting a good use of Recycle method in your forthcoming challenges.

Thanks for reading. 

Users vs AllUsers vs SiteUsers in SharePoint

Hi All,

Today we will see about the Usercollections in SharePoint.Lets we see the Scenario

Scenario:

You want to get all the users who have access to the site. An SPWeb object exposes three different collections of users, as shown in this code below.

Code:

SPWeb web = SPContext.Current.Web;

SPUserCollection c1 = web.Users;

SPUserCollection c2 = web.AllUsers;

SPUserCollection c3 = web.SiteUsers;



Ok.... So what is the difference among these 3 collections.

The "Users" collection has the smallest membership of these three collections. This collection includes all the external principals that have been explicitly assigned permissions within the current site.


The "AllUsers" collection includes all members of the Users collection, plus external users that have accessed objects within the site using implicit permissions through group or role membership. For example, imagine a user named Sachin with the login of Company\Sachin that has never been given explicit permissions to access a site and view a particular list. However, he might still be able to view the list because of his membership within an Active Directory group that has been configured with list view permissions. When Sandeep first accesses the site or one of its objects (say, a list using implicit permissions), he is added as a member of the AllUsers collection, but he is not added as a member of the Users collection.

The "SiteUsers" collection is an aggregation that combines membership for each AllUsers collection within the current site collection. The membership of this collection includes all external principals that have been assigned permissions to any object within the site collection as well as all external users that have been granted access to any of the site collection's objects using implicit permissions.

I hope now u will get an idea about these collections.

Obviously there, u will get this doubt.


-----What will the SharePoint UserInfoList contain - AllUsers or SiteUsers or Users?

And the answer is, Actually it contains cached information about the users logged in at least once to the site collection.

See u on my next post :) byeee....

DateTime Fields and CAML Queries , OffsetDays...

Hi Friends,
 Today I will share you some tips regarding CAML queries in SharePoint. Most of you would have played with CAML Queries.
OK , Now I will tel you how to deal with the Dates in CAML Query.

Scenario 1 : If you are checking for the items having "Due Date" field value as "Today", then this would be the query for u.

<Where>
<Eq>
<FieldRef Name="DueDate" />
<Value Type="DateTime">
<Today/>
</Value>
</Eq>
</Where>

Scenario 2 : what if u want something dynamic???
 EX: if u want to get the items, having "Due Date" 5 days before Today. For this you can't give a static day, because "Today" changes daily.
     Option is "OffsetDays".
The Query goes like this :

<Where>
<Eq>
<FieldRef Name="DueDate" />
<Value Type="DateTime">
<Today OffsetDays="-5" />  ----------> for future dates the value should be positive.
</Value>
</Eq>
</Where>

In many documents they have mentioned Offset instead of "OffsetDays"... better be careful while checking...

Have a good Day :)

Sharepoint Designer Workflow - Empty Date field value

Hi All,
 Today we will see a peculiar behaviour of Sharepoint Date Field in Designer Workflow.

ok,, lets consider this scenario. There is a list with a DateTime column(Say, "Publishing Date") and that is a not a mandatory field.
My requirement is to get the value of the "Publishing date" in mail..
I retrieved the value and showed that in mail..

Now the problem comes,,,

1) If the "Publishing Date" is filled , then it is well and good. It is also shown in the mail.
2) If "Publishing Date" is not filled and left empty in SharePoint then the mail is showing "1/1/0001" in that area.

   Note : No exception comes, No blank field comes in that area of the mail.

So obviously my requirement was to show a blank space when the "Publishing Date" is empty...

Workaround for this is shown in the below image.. this is done by adding a WorkFlow Variable- "stored variable" and setting its value to an blank (i.e) "one empty space" in its value assigning area.

Its doneee... Thank you.. See u all on next post...

What is SharePoint\System account?

Hi All,
  Today we will see about the "System Account" which we used to come accross while working on SharePoint...

Some interesting things about it...


 What is SharePoint\System account?


1) SHAREPOINT\System account is the same account used by application pool of your SharePoint web application in IIS at the time of creation/extension of respective web application.

2) This is the account which is used when you run your code in under elevated privileges by SPSecurity.RunWithElevatedPrivileges. (Most of the people think that it runs under the Farm Administrator credentials, which is no true..)

3) When you create/extend a web application in SharePoint and specify an Application Pool then the identity (Network Account) used by that Application pool becomes the SHAREPOINT\System account.

4) It is highly recommended that end user should not be allowed to use this account to avoid unexpected errors.

5) If you change the identity of App Pool account after creating/extending the SharePoint web application, the new account will not become the SHAREPOINT\System.


 I hope you will be having these points in mind, while coming accross apt situations...

See u on my next post... Biiiii....

PreSaveAction for Sharepoint Form Fields Validations

HI All,

 Today I will share you one very useful in-built JavaScript functionality, which is used for validating the form fields. Obviously u can go for “Calculated columns”, “Column Validations”, “List Validations” for some validations and required result.

But,,,,,,, Sometimes, the requirement could be quite complicated..

Eg :

#“Start Date” less than “End Date”

##Out of two fields, if one is filled then save the item else cancel.

Likewise this can be used for many validations which we cannot do OOTB.

Ok, now I will give the basic code,

if the requirement is “End date” should not be earlier than “Start Date”..

========================================================================

<script language=”javascript” type=”text/javascript”>

function PreSaveAction()

{

var date1 = getTagFromIdentifierAndTitle(“INPUT”,”DateTimeFieldDate”,”Start Date”);

var date2 = getTagFromIdentifierAndTitle(“INPUT”,”DateTimeFieldDate”,”End Date”);

var arrDate1 = date1.value.split(“/”);

var useDate1 = new Date(arrDate1[2], arrDate1[1]-1, arrDate1[0]);

var arrDate2 = date2.value.split(“/”);

var useDate2 = new Date(arrDate2[2], arrDate2[1]-1, arrDate2[0]);

if(useDate1 > useDate2)

{

alert(“The End Date cannot happen earlier than the Start Date”);

return false; // Cancel the item save process

}

return true; // OK to proceed with the save item

}

function getTagFromIdentifierAndTitle(tagName, identifier, title) {

var len = identifier.length;

var tags = document.getElementsByTagName(tagName);

for (var i=0; i < tags.length; i++) {

var tempString = tags[i].id;

if (tags[i].title == title && (identifier == “” || tempString.indexOf(identifier) == tempString.length – len)) {

return tags[i];

}

}

return null;

}

</script>

========================================================================

U can just do some simple tweaks in this piece of code and can do magicsss :)

See you next time… byyyeee..:)