Export to excel from Sharepoint Reports

protected void btnSendtoExcel_Click1(object sender, EventArgs e)
{
try
{
if (grdResultView.Rows.Count != 0)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", "KCADetails.xls"));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
table.GridLines = GridLines.Both;
// add each of the data rows to the table
table.Rows.Add(grdResultView.HeaderRow);
if (grdResultView.Rows.Count != 0)
{
foreach (GridViewRow row in grdResultView.Rows)
{
//GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// render the table into the htmlwriter
table.RenderControl(htw);

// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
}
}
catch (Exception ex)
{ }
}

Deploy Timer jobs through STSADM

[For Feature Install]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o installfeature -name MyTimerFeature

Operation completed successfully.

[For Feature Activate]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o activatefeature -name MyTimerFeature -url "http://sgvm03-sgesx01.sg.dupont
.com:22222/" -force

Operation completed successfully.

[For IIS Reset]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>iisr
eset

Attempting stop...
Internet services successfully stopped
Attempting start...
Internet services successfully restarted

[For Deactivate Feature]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o deactivatefeature -name MyTimerFeature -url "http://sgvm03-sgesx01.sg.dupo
nt.com:22222/" -force

Operation completed successfully.
[For Uninstall Feature]

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o uninstallfeature -name MyTimerFeature

Operation completed successfully.

[for Copy .pdb file]

F:\TimerNameSpace\TimerNameSpace\obj\Debug>xcopy TimerNameSpace.pdb \sgvm03-sge
sx01.sg.dupont.com\c$\windows\assembly
Does \sgvm03-sgesx01.sg.dupont.com\c$\windows\assembly specify a file name
or directory name on the target
(F = file, D = directory)? f
F:TimerNameSpace.pdb
1 File(s) copied

F:\TimerNameSpace\TimerNameSpace\obj\Debug>xcopy TimerNameSpace.pdb c:\windows\
assembly\
F:TimerNameSpace.pdb
1 File(s) copied

F:\TimerNameSpace\TimerNameSpace\obj\Debug>dir /w
Volume in drive F is Data01
Volume Serial Number is 186C-CB1F

Directory of F:\TimerNameSpace\TimerNameSpace\obj\Debug

[.] [..]
ResolveAssemblyReference.cache [TempPE]
TimerNameSpace.dll TimerNameSpace.pdb
3 File(s) 113,597 bytes
3 Dir(s) 3,332,431,872 bytes free

Create Timer Job

Creating a Timer job in MOSS
First we need to have a class derived from SPJobDefinition

Below is the skeleton of that class.
namespace TimerNameSpace
{
#region Using
using System;
using System.Collections.Generic;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
#endregion Using

///
/// Timer service
///
public class MyTimerService : SPJobDefinition
{
#region Constructor

///
/// Initializes a new instance of the TimerService class
///
public MyTimerService()
: base()
{
}

///
/// Initializes a new instance of the MyTimerService class
///
/// Job Title for the timer service
/// Web appication for which this service will run
public MyTimerService(string jobName, SPWebApplication webApp)
: base(jobName, webApp, null, SPJobLockType.ContentDatabase)
{
this.Title = "My Timer";
}
#endregion Constructor

#region Execute

///
/// Overriden Execute method
///
/// guid of the target object
public override void Execute(Guid targetInstanceId)
{
try
{
/// Here the timer logic goes
}
catch (Exception ex)
{
//Log exception
}
}

#endregion Execute
}
}
One this class is ready. Next thing we need to look into is creating a Feature. This feature will internally schedule the timer as a Job and runs it. Below is the snapshot of this class.
namespace TimerNameSpace
{
#region Namespace Inclusions
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
#endregion Namespace Inclusions

///
/// Class is feature receiver for feature to install approval timer service
///
public class MyTimerFeature : SPFeatureReceiver
{
///
/// Notifications job name
///
public string MyTimerFeatureName = "MyTimerServiceFeature";

///
/// Occurs after a Feature is installed.
///
/// An object that represents the properties of the event.
public override void FeatureInstalled(SPFeatureReceiverProperties properties)
{
}

///
/// Occurs when a Feature is uninstalled.
///
/// An object that represents the properties of the event.
public override void FeatureUninstalling(SPFeatureReceiverProperties properties)
{
}

///
/// Occurs after a Feature is activated.
///
/// An object that represents the properties of the event.
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
// register the the current web
SPWeb web = (SPWeb)properties.Feature.Parent;

SPSite site = web.Site;

// make sure the job isn't already registered
foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
{
if (job.Name == this.MyTimerFeatureName)
{
job.Delete();
break;
}
}

MyTimerService myJob = new MyTimerService(this.MyTimerFeatureName, site.WebApplication);

SPMinuteSchedule schedule = new SPMinuteSchedule();
schedule.BeginSecond = 0;
schedule.EndSecond = 5;
schedule.Interval = 2;

myJob.Schedule = schedule;
myJob.Update();
}

///
/// Occurs when a Feature is deactivated.
///
/// An object that represents the properties of the event.
public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
// current web
SPWeb web = (SPWeb)properties.Feature.Parent;

SPSite site = web.Site;

// delete the job
foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
{
if (job.Name == this.MyTimerFeatureName)
{
job.Delete();
break;
}
}
}
}
}
Similar to SPMinuteSchedule, there is Hourl Schedule also. Use the one as per your requirement.
Once this much is done, we need to create Feature.xml file for the feature we have built and paste it under the Features Folder (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES). Create a folder named MyTimerFeature and under this folder create Feature.xml with the following content.



Creator="Amal Hashim"
Id="{76D2F200-5CA0-4882-B64F-9FC6208C1234}"
Title="MyTimerFeature"
Description="My Timer."
Scope="Web"
Hidden="FALSE"
Version="1.0.0.0"
ReceiverAssembly="MyTimer, Version=1.0.0.0, Culture=neutral, PublicKeyToken=12344ab009fa4028"
ReceiverClass="TimerNameSpace.MyTimerFeature">

You can activate the feature using the Stsadm command as shown below.
stsadm -o installfeature -name MyTimerFeature

For checking whether the timer has activated successfully or not, you can go to the central admin page
Central Administration –> Operations –> Timer Job Status

For debugging Timer Job we add .pdb file with the help of DOS.

Merge word document and upload in a List with attachment

private static void Merge(string filesToMerge, string outputFilename, bool insertPageBreaks, string defaultWordDocumentTemplate)
{
{
object defaultTemplate = @"Normal.dot";
object missing = System.Type.Missing;
object pageBreak = Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
object outputFile = outputFilename;

// Create a new Word application
Microsoft.Office.Interop.Word._Application wordApplication = new Microsoft.Office.Interop.Word.Application();

try
{
// Create a new file based on our template
Microsoft.Office.Interop.Word._Document wordDocument = wordApplication.Documents.Add(
ref defaultTemplate
, ref missing
, ref missing
, ref missing);

// Make a Word selection object.
Microsoft.Office.Interop.Word.Selection selection = wordApplication.Selection;

// Loop thru each of the Word documents
string StrMergeDoc = filesToMerge.ToString();
string[] Delimeter = StrMergeDoc.Split(',');
for (int i = 0; i <= Delimeter.Length - 1; i++)
{
string strMDoc = "F:/punit/event handler/EventHnadlerdeployment/EventHnadlerdeployment/" + Delimeter[i].ToString().Trim() + ".doc";
// foreach (string file in filesToMerge)
//{
// Insert the files to our template
selection.InsertFile(strMDoc, ref missing, ref missing, ref missing, ref missing);

//Do we want page breaks added after each documents?
if (insertPageBreaks)
{
selection.InsertBreak(ref pageBreak);
}
}

// Save the document to it's output file.
wordDocument.SaveAs(ref outputFile, ref missing, ref missing, ref missing, ref missing, ref missing
, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing
, ref missing, ref missing);

// Clean up!
wordDocument = null;
}
catch (Exception ex)
{
//I didn't include a default error handler so i'm just throwing the error
//throw ex;
}
finally
{
// Finally, Close our Word application
wordApplication.Quit(ref missing, ref missing, ref missing);
}
} //throw new Exception("The method or operation is not implemented.");
}


private static void uploadfile(SPListItem item)
{
SPAttachmentCollection spAttachmentCollection = item.Attachments;
FileStream fStream = File.OpenRead("C:\\Punit.doc"); //path of the file to upload
byte[] contents = new byte[fStream.Length];
fStream.Read(contents, 0, (int)fStream.Length);
fStream.Close();
fStream.Dispose();
spAttachmentCollection.Add("Competency.doc", contents);
item.Update();
}
public void MergeDocument(SPListItem item)
{
string defaultWordDocumentTemplate = @"Normal.dot";
string outputFilename = @"C:\Punit.doc";
string filesToMerge = string.Empty;
SPSite site = new SPSite("http://dhts1:22222");
SPWeb web = site.OpenWeb();
web.AllowUnsafeUpdates = true;
SPListItemCollection itemcoll = web.Lists["Creation of Job Description"].Items;
if (itemcoll != null && itemcoll.Count > 0)
{
{
string IndividualContributor = Convert.ToString(item["Individual_x0020_Contributor"]).Replace('#', ' ');
string newIP = IndividualContributor.Remove(0, 1);
string PPL = Convert.ToString(item["PPL"]).Replace('#', ' ');
string newPPL = PPL.Remove(0, 1);
string BFL = Convert.ToString(item["BFL"]).Replace('#', ' ');
string newBFL = BFL.Remove(0, 1);
string STL = Convert.ToString(item["STL"]).Replace('#', ' ');
string newSTL = STL.Remove(0, 1);
filesToMerge = newIP.Trim() + newPPL.Trim() + newBFL.Trim() + newSTL.Trim();
filesToMerge = filesToMerge.Replace(';', ',');
}
}
Merge(filesToMerge, outputFilename, true, defaultWordDocumentTemplate);

}

stsadm command for backup and restore

C:\Program Files>
C:\Program Files>cd "Common Files"

C:\Program Files\Common Files>cd "Microsoft Shared"

C:\Program Files\Common Files\Microsoft Shared>cd "web server extensions"

C:\Program Files\Common Files\Microsoft Shared\web server extensions>cd 12

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12>cd bin

[Backup]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o backup -url "http://sgvm03-sgesx01.sg.dupont.com:22222/" -filename C:\Late
stBackUp\Latestbackup

Operation completed successfully.
[Restore]
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o restore -url "http://dhts1:22222/" -filename C:\LatestBackUp\Latestbackup

Operation completed successfully.

[Export]

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o export -url "http://sgvm03-sgesx01.sg.dupont.com:55555/teamroom" -filenam
e "c:\export" -includeusersecurity

[Import]
c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
m -o import -filename "c:\export.cmp" -url "http://dhts1:55555/teamroomNew"

Creating Report in Sharepoint

Whenever microsoft talks about reports, it talks about the reports created in excel. But, when we talk about creating report of some data that is entered in lists in Microsoft Sharepoint Server, it is a difficult task.

What we have to do is get the entire lists in sharepoint, store them in datatables and then join them.

#region Datatable DocInfo
DataTable dtDocInfo = new DataTable();
SPQuery qDoc = new SPQuery();
DataRow drDocInfo;
dtDocInfo.Columns.Add("Description");
dtDocInfo.Columns.Add("KCAno");
dtDocInfo.Columns.Add("KCAid");

SPListItemCollection docInfoCol;
#endregion

#region Datatable Eval Info
//Data table Dt Eval info

DataTable dtEvalInfo = new DataTable();
DataRow drEvalInfo;

dtEvalInfo.Columns.Add("Evaluation");
dtEvalInfo.Columns.Add("KCAno");
dtEvalInfo.Columns.Add("KCAid");
SPQuery qEval = new SPQuery();
SPListItemCollection evalInfoCol;
#endregion

#region Datatable Audit Finding
DataTable dtAuditFinding = new DataTable();
DataRow drAuditFinding;

dtAuditFinding.Columns.Add("Audit No");
dtAuditFinding.Columns.Add("KCAno");
dtAuditFinding.Columns.Add("KCAid");

SPQuery qAudit = new SPQuery();
SPListItemCollection auditFindingCol;
#endregion

#region Create First Relation

DataSet ds = new DataSet();
ds.Tables.Add(dtDocInfo);
ds.Tables.Add(dtEvalInfo);
DataTable jt = new DataTable("Joinedtable");
jt.Columns.Add("Description", typeof(String));
jt.Columns.Add("Evaluation", typeof(String));
jt.Columns.Add("KCAno", typeof(String));
#endregion

#region Create second relation
//Third table
DataSet thirdDS = new DataSet();
thirdDS.Tables.Add(jt);
thirdDS.Tables.Add(dtAuditFinding);

DataTable finaljt = new DataTable("FinalJoinedtable");
finaljt.Columns.Add("Description", typeof(String));
finaljt.Columns.Add("Evaluation", typeof(String));
finaljt.Columns.Add("KCAno", typeof(String));
//thirdDS.Tables.Add(finaljt);

#endregion

for (int h = 0; h < kcaCount; h++)
{

#region getdata from Audit finding
//Get data from auditFindingList///////////////////////////////////////////////////////////////////////////////////////
qAudit = new SPQuery();
qAudit.Query = @"


" + kcas[h] + @"



";
qAudit.RowLimit = 1;

auditFindingCol = auditFindingList.GetItems(qAudit);
foreach (SPListItem audititem in auditFindingCol)
{
//dtAuditFinding.Clear();
drAuditFinding = dtAuditFinding.NewRow();
drAuditFinding["Audit No"] = audititem["Audit No"];
drAuditFinding["KCAno"] = audititem["KCAno"];
drAuditFinding["KCAid"] = audititem["KCAid"];
dtAuditFinding.Rows.Add(drAuditFinding);
}

#endregion

#region getdata from eval info list
// //Get data from evalInfoList///////////////////////////////////////////////////////////////////////////////////////

qEval = new SPQuery();
qEval.Query = @"


" + kcas[h] + @"



";
qEval.RowLimit = 1;

evalInfoCol = evalInfoList.GetItems(qEval);
//dtEvalInfo = evalInfoCol.GetDataTable();
foreach (SPListItem evalitem in evalInfoCol)
{
//dtEvalInfo.Clear();
drEvalInfo = dtEvalInfo.NewRow();
drEvalInfo["Evaluation"] = evalitem["Evaluation"];
drEvalInfo["KCAno"] = evalitem["KCAno"];
drEvalInfo["KCAid"] = evalitem["KCAid"];
dtEvalInfo.Rows.Add(drEvalInfo);
}

#endregion

#region get data from doc info list
//Get data from docInfoList////////////////////////////////////////////////////////////////////////////////////
qDoc = new SPQuery();
qDoc.Query = @"


" + kcas[h] + @"




";
qDoc.RowLimit = 1;

docInfoCol = docInfoList.GetItems(qDoc);
//dtDocInfo = docInfoCol.GetDataTable();
if (docInfoCol != null)
{
foreach (SPListItem docitem in docInfoCol)
{
//dtDocInfo.Clear();
drDocInfo = dtDocInfo.NewRow();
drDocInfo["Description"] = docitem["Description"];
drDocInfo["KCAno"] = docitem["KCAno"];
drDocInfo["KCAid"] = docitem["KCAid"];
dtDocInfo.Rows.Add(drDocInfo);
}
}
#endregion

#region join table 1 & 2

//ds.Tables.Add(jt);
foreach (DataRow dr1 in dtDocInfo.Rows)
{
foreach (DataRow dr2 in dtEvalInfo.Rows)
{
string Kcano = (string)dr2["KCAno"];
string newKcaNo = "";
if (Kcano.Contains("#"))
{
string[] split = Kcano.Split(new Char[] { '#' });
newKcaNo = split[1].ToString();
}
else { newKcaNo = Kcano; }

if ((dr1.RowState != DataRowState.Deleted) && ((string)dr1["KCAno"] == newKcaNo))
{
try
{
DataRow newt = jt.NewRow();
newt["Description"] = dr1["Description"];
newt["Evaluation"] = dr2["Evaluation"];
newt["KCAno"] = dr2["KCAno"];
jt.Rows.Add(newt);
}
catch (Exception ex)
{ }
}
}
}
#endregion

# region Join table jt and 3

foreach (DataRow dr1 in jt.Rows)
{
foreach (DataRow dr2 in dtAuditFinding.Rows)
{
string Kcano = (string)dr2["KCAno"];
string newKcaNo = "";
if (Kcano.Contains("#"))
{
string[] split = Kcano.Split(new Char[] { '#' });
newKcaNo = split[1].ToString();
}
else { newKcaNo = Kcano; }

if ((dr1.RowState != DataRowState.Deleted) && ((string)dr1["KCAno"] == newKcaNo))
{
DataRow newt = finaljt.NewRow();
newt["Description"] = dr1["Description"];
newt["Evaluation"] = dr1["Evaluation"];
newt["KCAno"] = dr2["KCAno"];
finaljt.Rows.Add(newt);
}
}
}

#endregion

//End loop for KCA's
}

thirdDS.Tables.Add(finaljt);

DataSet FullFinalDs = new DataSet();
FullFinalDs = thirdDS.Clone();
DataRow drfinal = null;
for (int z = 0; z < kcas.Length; z++)
{
DataRow[] foundRows = thirdDS.Tables[2].Select("KCAno = '" + kcas[z] + "'");
if (foundRows.Length > 0)
{
drfinal = FullFinalDs.Tables[2].NewRow();
for (int count = 0; count < thirdDS.Tables[2].Columns.Count; count++)
{
drfinal[count] = Convert.ToString(foundRows[0][count]);
}

FullFinalDs.Tables[2].Rows.Add(drfinal);
}
}
DataView dvnew = FullFinalDs.Tables[2].DefaultView;
if (dvnew.Count == 0)
{
grdResultView.Visible = false;
lblMessage.Visible = true;
lblMessage.Text = "No Records are available for this selection";
}
grdResultView.DataSource = dvnew;
grdResultView.DataBind();
grdResultView.Visible = true;
}

Why do we need WCF

There was a time when applications used to sit on separate computers, then came a need for the different computers to talk to each other. Well we all know that web and web services have long been in there and have been communicating with each other.

We all understand that XML has been adopted as a great medium of communication between web services and their clients. But, in the absence of a protocol, there is no one way to use XML. The sender and receiver of the information have to decide on a certain format for the information every time. It is easier if a global standard is present and the information is shared by default based on that standard.
Web services and client applications communicate with each other by using the Simple Object Access Protocol or SOAP.
SOAP defines the following:

- The format of the SOAP message
- How data should be encoded
- How to send messages
- How to handle replies to these messages

A web service can simply display the messages that a client application can send it and the responses the client application will receive by publishing a Web Service Description Language (WSDL) document.
Now, there are other technologies apart from WCF like .Net Remoting and MSMQ for transfer of information.
What WCF does is provides a unified programming model for many of these technologies, enabling you to build applications that are as independent as possible from the underlying mechanism being used to connect services and applications together. So that the programmatic structure is completely separate from the communication structure.

Now, how does it do that????

So suppose we have a server application and a client application – at the base, we have .Net framework, a set of classes in form of WCF on top and the two interacting with the help of SOAP. By default WCF operations are of the type known as request – reply; the client issues a request in the form of a message and the server responds by replying to the request.


The client need not be based on WCF; it can be J2EE based or anything that supports SOAP. This is a very important point as it supports interoperability with applications built on other technologies.
WCF can also be extended to process plain XML data that is not wrapped in the SOAP envelope. WCF can also be extended to support ATOM or JSON. Thus, we can say WCF is really helpful and is designed to support the present as well as the future scenarios.