If somebody like the post and its helpful in your work then, add comments.

Thursday, December 17, 2009

SharePoint Timer Job : Key things while developing 2

Hi All,

When we develop the Timer job for SharePoint 2007, in the mean while in between when you developed and testing the solution on server, and you installed WSS 3.0 SP2 and MOSS SP2. This installation removes all the permission to Central Admin pool user.

So, we again get the same error or issue. So, again give the same permission, which we have given by using this link. :)

Sunday, December 13, 2009

SharePoint Timer Job : Key things while developing

Hi All,

 

While developing custom job for SharePoint 2007, I had faced many problem. I develop this job as feature in SiteCollection. On the deployment time of the feature for SharePoint job it gives many type of permission issues. Like when we activate the feature, it adds the SharePoint job in the Central Administration->Operation ->SharePoint Job definition.  So, the user (like Domain\User) which have administrator permission also generates the error on deployment time of SharePoint job. It gives following error “'System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'proc_putObject', database 'Intranet_Config', schema 'dbo'” at taskLoggerJob.Update(); code. So after many research and googling I find these following two resolution for the error.

  1. During feature activation I had: 'System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'proc_putObject', database 'Intranet_Config', schema 'dbo''. After doing a profiling on the database, I saw that the SPJobDefinition.Update() code is executed with the web application's application pool account. This account has to have access to the configuration database. I did this by setting the necessary rights in SQL Management Studio by giving to the web application's application pool account EXECUTE permissions on the proc_putClass, proc_putObject, proc_getNewObjects and proc_dropObject stored procedures.

  2. After doing the thing above, the SPJobDefinition.Delete code throws a weird exception: Access to the path 'C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\360c4621-fccb-4c2a-9182-b3c75ae80cf3\cache.ini' is denied. Solution: give full control to the WSS_WPG user on the folder where the cache.ini file is searched.

Find at following link.

Second, issue which I am faced the problem when debugging the solution. When I am attached the solution with OWSTIMER.EXE one the time of debugging the solution, when the error generates in the code and after resolution of the error and rerunning the solution in debug mode. The OWSTIMER.EXE is attached with previous dll of the solution. Means it does not refer the updated dll of the solution. So, to overcome this issue we have to rerun the “Windows SharePoint Timer” service from the services. When you deploy the solution, then we have to restart the service.

Tuesday, November 3, 2009

Get values from InfoPath form XML in Form Library

To get values from the InfoPath xml , their are two values

1. By promoting the values of InfoPath form to form library and get that values by using SPObject and SPListItemObject.

2. Is that is below :

SPWeb _web = SPContext.Current.Web;

string temXml = _web.GetFileAsString("Idea Forms/Formulate.xml");
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml(temXml);
            XmlNamespaceManager ns = new XmlNamespaceManager(xDoc.NameTable);
            ns.AddNamespace("my", "http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-02-08T09:24:40");

 

string strSampleValue = xDoc.SelectSingleNode("//my:myfield", ns).InnerText;

Monday, October 26, 2009

New in InfoPath 2010

In InfoPath 2010, there are many changes. Like is UI or it controls etc. Now it is also have some changes in designing, data connection. Some good and new changes/feature which I find is listed below.

Products Changes:

  1. Rich ribbon interface with InfoPath 2010.

        InfoPath2010005

   2. There are two products in Microsoft 2010 officer.

Microsoft InfoPath Designer 2010: For designing the form. 

   InfoPath2010001

      Microsoft InfoPath Editor 2010: For form filling.

   InfoPath2010002

Microsoft InfoPath Designer 2010 Changes :

Data Connection:

1. There is increase in receive data connection.

    InfoPath2010003

2. Shorting in data connection

   InfoPath2010004

Addition in Controls:

1. In Web form we have following controls :

   InfoPath2010006

2. In Client form we have following controls:

   InfoPath2010007

3. Control properties is show in ribbon

  InfoPath2010008

4. Compatibility changes

  InfoPath2010009

5. Automatic versioning

   InfoPath2010010

6. Property Promotion

 InfoPath2010011

7. Rule enhancements

   InfoPath2010012

8. Addition of Context in formula

   InfoPath2010013

9. Single Publish button

   InfoPath2010014

Tuesday, September 29, 2009

Timed Jobs in SharePoint

Microsoft Windows SharePoint Services (WSS) and its Portal version, Microsoft Office SharePoint Server (MOSS), carries out various automatic, scheduled jobs to realize repetitive tasks in a standard way.

The SharePoint Timed Jobs infrastructure takes care of the scheduled tasks needed to ensure proper functioning of the system. Timed Jobs run in the background and are based in the Microsoft SharePoint Timer service, which is always installed in the default setup of WSS and MOSS. Installed Timed Jobs can be configured from SharePoint's Central Administration using the Command-Line administrator's tool or programmed using the Object Model.

Limitation

Timed Jobs can be set up to run immediately or in a time frame based on "one," meaning a SharePoint Job can run each minute, day, or week but not, for example, every two hours or days. Another limitation is that it is impossible to schedule Jobs to run on relative time frames; for example, the second Monday of every month. A Timed Job requires a starting time, and the configured time frame determines the interval it will run at. The starting time can be an absolute time (at 12:00 am, for example) or a time range (between 8:00 and 9:00 am); in the first instance, the Job would require a few seconds to execute because the service needs to be initiated; in the second case, the Job will run at a random time within the range, giving the different servers in the farm the opportunity to progress at varied periods, balancing the load on the system.

Friday, August 21, 2009

Updating the SharePoint User Profile in SSP Programmatically

The User Profile Database in SharePoint is a great centralized location for storing all the information about the users of your SharePoint Portal.

To import the AD profiles in SharePoint then we have use UserProfileConfigManager class.

Add following references in solution:

using Microsoft.SharePoint;
using
Microsoft.Office.Server.UserProfiles;
using
Microsoft.Office.Server;
using
System.Web;

and the following code


SPSite HomeSite = new SPSite("http://mossvs2008");

           UserProfileConfigManager SPUserProfileConfigManager = new UserProfileConfigManager(ServerContext.GetContext(HomeSite));

           if (SPUserProfileConfigManager.IsImportInProgress() == false)

               SPUserProfileConfigManager.StartImport(true);


Sunday, August 9, 2009

WordProcessingML : How to update Table of Content in MS Word Document

To update the Table of content in the Microsoft word document first we have 
to create a table of content in first of document. Then write below code to
update the table of content updation.
class Program 
{
public static XNamespace ns = "http://schemas.openxmlformats.org/wordprocessingml/2006/main";

static void Main(string[] args)
{
using (WordprocessingDocument WorDocument = WordprocessingDocument.Create(@"C:\TestDocuments\TOCDocument.docx", WordprocessingDocumentType.Document))
{
// Add a new main document part.
MainDocumentPart mainPart = WorDocument.AddMainDocumentPart();
//Create Document tree for simple document.
mainPart.Document = new Document();
//Create Body (this element contains other elements that we want to include
Body body = new Body();

//Save changes to the main document part.
mainPart.Document.Append(body);

XDocument xmlXdocument = XDocument.Parse(mainPart.Document.InnerXml);
IEnumerable<XElement> xmlelement = xmlXdocument.Descendants(ns + "sdt");
XElement TOCRefNode = xmlelement.First();
GenerateTOC(xmlXdocument, TOCRefNode);

mainPart.Document.InnerXml = xmlXdocument.ToString();

mainPart.Document.Save();
WorDocument.Close();

}
}

#region TOC Creation

/// <summary>
///
returns title paragraphs of genereated doc fro creating toc hyperlink
/// </summary>
private static IEnumerable<XElement> TitleParagraphsElements(XDocument mainDocument)
{
IEnumerable<XElement> results = mainDocument.Descendants().Where
(
tag =>
tag.Name == ns + "p" &&
tag.Descendants(ns + "t").Count() > 0 &&
tag.Descendants().Where
(
tag2 =>
tag2.Name == ns + "pStyle" &&
(
tag2.Attribute(ns + "val").Value == "Head1" ||
tag2.Attribute(ns + "val").Value == "Head2" ||
tag2.Attribute(ns + "val").Value == "Head3" ||
tag2.Attribute(ns + "val").Value == "Head4" ||
tag2.Attribute(ns + "val").Value == "Head5" ||
tag2.Attribute(ns + "val").Value == "Head6"
)
).Count() > 0
);

return results;
}

private static void GenerateTOC(XDocument xmlMainDocument, XElement TOCRefNode)
{
int bookMarkIdCounter = 0;
int maxHeading = 1;
int tempheading = 1;

// sdtContent, will contain all the paragraphs used in the TOC
XElement sdtContent = new XElement(ns + "sdtContent");
String strContentHdr = "";
XElement xContentHdr = TOCRefNode.Elements(ns + "sdtContent").First().Descendants().Where(
tag =>
tag.Name == ns + "p" &&
tag.Descendants().Where
(
tag2 =>
tag2.Name == ns + "pStyle" &&
tag2.Attribute(ns + "val").Value == "TOCHeading"
).Count() > 0
).FirstOrDefault();

if (xContentHdr != null)
strContentHdr = xContentHdr.Descendants(ns + "t").FirstOrDefault().Value;

// some information regarding the attributes of the TOC
xContentHdr.Add(

new XElement(ns + "r",
new XElement(ns + "fldChar",
new XAttribute(ns + "fldCharType", "begin"))),
new XElement(ns + "r",
new XElement(ns + "instrText",
new XAttribute(XNamespace.Xml + "space", "preserve"),
"TOCLIMIT")),
new XElement(ns + "r",
new XElement(ns + "fldChar",
new XAttribute(ns + "fldCharType", "separate"))));
sdtContent.Add(new XElement(xContentHdr));


// for each title found it in the document, we have to wrap the run inside of it,
// with a bookmark, this bookmark will have an id which will work as an anchor,
// for link references in the TOC
foreach (XElement titleParagraph in TitleParagraphsElements(xmlMainDocument))
{
string bookmarkName = "_TOC" + bookMarkIdCounter;
XElement bookmarkStart =
new XElement(ns + "bookmarkStart",
new XAttribute(ns + "id", bookMarkIdCounter),
new XAttribute(ns + "name", bookmarkName));
XElement bookmarkEnd =
new XElement(ns + "bookmarkEnd",
new XAttribute(ns + "id", bookMarkIdCounter));

// wrap the run with bookmarkStart and bookmarkEnd
titleParagraph.AddFirst(bookmarkStart);
titleParagraph.Add(bookmarkEnd);

// get the name of the style of the parapgraph of the title, and for each one,
// choose a style to add in the paragraph inside the TOC
string referenceTitleStyle = "";
switch (titleParagraph.Descendants(ns + "pStyle").First().Attribute(ns + "val").Value)
{
case "Head1":
{
referenceTitleStyle = "TOC1";
tempheading = 1;
break;
}
case "Head2":
{
referenceTitleStyle = "TOC2";
tempheading = 2;
break;
}
case "Head3":
{
referenceTitleStyle = "TOC3";
tempheading = 3;
break;
}
case "Head4":
{
referenceTitleStyle = "TOC4";
tempheading = 4;
break;
}
case "Head5":
{
referenceTitleStyle = "TOC5";
tempheading = 5;
break;
}
case "Head6":
{
referenceTitleStyle = "TOC6";
tempheading = 6;
break;
}
}


string entryContent = "";
IEnumerable<XElement> owTList = titleParagraph.Descendants(ns + "t");
foreach (XElement entryElement in owTList)
{
entryContent += (entryElement == null ? string.Empty : entryElement.Value);
}

XElement TOCElement = null;
XElement tempTOCElement = TOCRefNode.Elements(ns + "sdtContent").First().Descendants().Where(
tag =>
tag.Name == ns + "p" &&
tag.Descendants().Where
(
tag2 =>
tag2.Name == ns + "pStyle" &&
tag2.Attribute(ns + "val").Value == referenceTitleStyle
).Count() > 0
).FirstOrDefault();

if (tempTOCElement != null)
{
if (maxHeading < tempheading)
maxHeading = tempheading;

TOCElement = new XElement(tempTOCElement);
//delete instrText which contains TOC 1-n
XElement instrTextTOC = TOCElement.Descendants().Where(
tag =>
tag.Name == ns + "r" &&
tag.Descendants().Where(
tag2 =>
tag2.Name == ns + "instrText" &&
tag2.Value.Contains(@"TOC \o ")
).Count() > 0

).FirstOrDefault();
if (instrTextTOC != null)
{
instrTextTOC.ElementsAfterSelf(ns + "r").Remove();
instrTextTOC.ElementsBeforeSelf(ns + "r").Remove();
instrTextTOC.Remove();
}

//get hyperlink node
XElement hyperlink = TOCElement.Descendants().Where(
tag =>
tag.Name == ns + "hyperlink"

).FirstOrDefault();
//update anchor attribute value
hyperlink.Attribute(ns + "anchor").Value = bookmarkName;

//get entry content node
XElement contentNode = hyperlink.Descendants().Where(
tag =>
tag.Name == ns + "r" &&
tag.Descendants().Where(
tag2 =>
tag2.Name == ns + "rStyle" &&
tag2.Attribute(ns + "val").Value == "Hyperlink"
).Count() > 0 &&
tag.Elements(ns + "t").Count() > 0

).FirstOrDefault().Elements(ns + "t").FirstOrDefault();

contentNode.Value = entryContent;

//update PAGEREF value
XElement instrText = TOCElement.Descendants().Where(
tag =>
tag.Name == ns + "instrText" &&
tag.Value.Contains("PAGEREF ")
).FirstOrDefault();
if (instrText != null)
{
instrText.Value = " PAGEREF " + bookmarkName + @" \h ";
}

sdtContent.Add(TOCElement);
bookMarkIdCounter++;
}


}

sdtContent.Descendants().Where(
tag =>
tag.Name == ns + "instrText"
&&
tag.Value.Contains("TOCLIMIT")
).FirstOrDefault().Value = String.Format(@"TOC \o ""1-{0}"" \h \z \u ", maxHeading);

sdtContent.Add(
new XElement(ns + "p",
new XElement(ns + "r",
new XElement(ns + "fldChar",
new XAttribute(ns + "fldCharType", "end")))));

// Finish the xml construction of the TOC
XElement TOC =
new XElement(ns + "sdt",
new XElement(ns + "sdtPr",
new XElement(ns + "docPartObj",
new XElement(ns + "docPartGallery",
new XAttribute(ns + "val", "Table of Contents")),
new XElement(ns + "docPartUnique"))),
sdtContent);

// add it to the original document
IEnumerable<XElement> tocNodes = xmlMainDocument.Descendants().Where
(
tag =>
tag.Name == ns + "sdt" &&
tag.Descendants(ns + "sdtContent").Count() > 0 &&
tag.Descendants().Where
(
tag2 =>
tag2.Name == ns + "p" &&
tag2.Descendants().Where
(
tag3 =>
tag3.Name == ns + "pStyle" &&
(
tag3.Attribute(ns + "val").Value == "TOCHeading"
)
).Count() > 0

).Count() > 0
);

TOCRefNode.ReplaceWith(TOC);

}

#endregion
Solution is provided by open xmldeveloper.org at following
link

Download Solution

Thursday, August 6, 2009

WordprocessingML : Insert page number in the word document in center bottom of MS Word Document

Their are 3 things required for the inserting page number;

1. Footer.xml
2. Footnotes.xml
3. EndNotes.xml

And Section Properties to append in the document.

Sample code for that

                Footer footer = new Footer(new Paragraph(new ParagraphProperties(new ParagraphStyleId() { Val = "Footer" })));
                var footerPart1 = mainPart.AddNewPart<FooterPart>("rid110");
                Footer1().Save(footerPart1);
                body.Append(footer);

                Footnotes footnotes = new Footnotes(new Paragraph(new ParagraphProperties(new ParagraphStyleId() { Val = "Footnotes" })));
                var footnodeinpage = mainPart.AddNewPart<FootnotesPart>("rid111");
                PageFootNote().Save(footnodeinpage);
                body.Append(footnotes);

                Endnotes endnotes = new Endnotes(new Paragraph(new ParagraphProperties(new ParagraphStyleId() { Val = "Endnotes" })));
                var endnodeinpage = mainPart.AddNewPart<EndnotesPart>("rid112");
                PageEndNote().Save(endnodeinpage);
                body.Append(endnotes);

                body.Append(PageSectionProperties());


Methods

#region Page Number
private static Endnotes PageEndNote()
{
var element =
new Endnotes(
new Endnote(
new Paragraph(
new ParagraphProperties(
new SpacingBetweenLines() { After = (UInt64Value)0UL, Line = 240, LineRule = LineSpacingRuleValues.Auto }),
new Run(
new SeparatorMark())
) { RsidParagraphAddition = "00670250", RsidParagraphProperties = "00EA1D8B", RsidRunAdditionDefault = "00670250" }
) { Type = FootnoteEndnoteValues.Separator, Id = 0 },
new Endnote(
new Paragraph(
new ParagraphProperties(
new SpacingBetweenLines() { After = (UInt64Value)0UL, Line = 240, LineRule = LineSpacingRuleValues.Auto }),
new Run(
new ContinuationSeparatorMark())
) { RsidParagraphAddition = "00670250", RsidParagraphProperties = "00EA1D8B", RsidRunAdditionDefault = "00670250" }
) { Type = FootnoteEndnoteValues.ContinuationSeparator, Id = 1 });
return element;
}

private static Footer Footer1()
{
var element =
new Footer(
new SdtBlock(
new SdtProperties(
new SdtId() { Val = 538536024 },
new DocPartObjectSdt(
new DocPartGallery() { Val = "Page Numbers (Bottom of Page)" },
new DocPartUnique())),
new SdtContentBlock(
new Paragraph(
new ParagraphProperties(
new ParagraphStyleId() { Val = "Footer" },
new Justification() { Val = JustificationValues.Center }),
new SimpleField(
new Run(
new RunProperties(
new NoProof()),
new Text("6") ->Total Page Number in document
) { RsidRunAddition = "00E906EE" }
) { Instruction = " PAGE \\* MERGEFORMAT " }
) { RsidParagraphAddition = "00EA1D8B", RsidRunAdditionDefault = "002E7045" })),
new Paragraph(
new ParagraphProperties(
new ParagraphStyleId() { Val = "Footer" })
) { RsidParagraphAddition = "00EA1D8B", RsidRunAdditionDefault = "00EA1D8B" });
return element;
}

private static Footnotes PageFootNote()
{
var element =
new Footnotes(
new Footnote(
new Paragraph(
new ParagraphProperties(
new SpacingBetweenLines() { After = (UInt64Value)0UL, Line = 240, LineRule = LineSpacingRuleValues.Auto }),
new Run(
new SeparatorMark())
) { RsidParagraphAddition = "00670250", RsidParagraphProperties = "00EA1D8B", RsidRunAdditionDefault = "00670250" }
) { Type = FootnoteEndnoteValues.Separator, Id = 0 },
new Footnote(
new Paragraph(
new ParagraphProperties(
new SpacingBetweenLines() { After = (UInt64Value)0UL, Line = 240, LineRule = LineSpacingRuleValues.Auto }),
new Run(
new ContinuationSeparatorMark())
) { RsidParagraphAddition = "00670250", RsidParagraphProperties = "00EA1D8B", RsidRunAdditionDefault = "00670250" }
) { Type = FootnoteEndnoteValues.ContinuationSeparator, Id = 1 });
return element;
}

//Adding the section properties in the document
public static SectionProperties PageSectionProperties()
{
var element =
new SectionProperties(
new FooterReference() { Type = HeaderFooterValues.Default, Id = "rid110" },
new PageSize() { Width = (UInt64Value)12240UL, Height = (UInt64Value)15840UL },
new PageMargin() { Top = 1440, Right = (UInt64Value)1440UL, Bottom = 1440, Left = (UInt64Value)1440UL, Header = (UInt64Value)720UL, Footer = (UInt64Value)720UL, Gutter = (UInt64Value)0UL },
new Columns() { Space = (UInt64Value)720UL },
new DocGrid() { LinePitch = 360 }
) { RsidRPr = "007024C7", RsidR = "000B6998", RsidSect = "006040EE" };
return element;
}

#endregion

Also find on openxmldeveloper.org Link

Monday, August 3, 2009

SpreadsheetML: Updating a range in the Excel Sheet

The below code update the cell rane in the excel sheet.

#region Approch 1

#region Calculate Sum of Cell Range

private static void CalculateSumOfCellRange(string docName, string worksheetName, string resultCell)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == worksheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}

WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;

#region Insert Cell Values
Cell result1 = InsertCellInWorksheet(GetColumnName("B4"), GetRowIndex(resultCell), worksheetPart);
Cell result2 = InsertCellInWorksheet(GetColumnName("C4"), GetRowIndex(resultCell), worksheetPart);
Cell result3 = InsertCellInWorksheet(GetColumnName("D4"), GetRowIndex(resultCell), worksheetPart);
Cell result4 = InsertCellInWorksheet(GetColumnName("E4"), GetRowIndex(resultCell), worksheetPart);
Cell result5 = InsertCellInWorksheet(GetColumnName("F4"), GetRowIndex(resultCell), worksheetPart);
Cell result6 = InsertCellInWorksheet(GetColumnName("G4"), GetRowIndex(resultCell), worksheetPart);
Cell resultSum = InsertCellInWorksheet(GetColumnName("J4"), GetRowIndex(resultCell), worksheetPart);

result1.DataType = new EnumValue(CellValues.Number);
result1.CellValue = new CellValue("1000");

result1.DataType = new EnumValue(CellValues.Number);
result1.CellValue = new CellValue("20000");

result2.DataType = new EnumValue(CellValues.Number);
result2.CellValue = new CellValue("100000");

result3.DataType = new EnumValue(CellValues.Number);
result3.CellValue = new CellValue("145000");

result4.DataType = new EnumValue(CellValues.Number);
result4.CellValue = new CellValue("301200000");

result5.DataType = new EnumValue(CellValues.Number);
result5.CellValue = new CellValue("440000");

result6.DataType = new EnumValue(CellValues.Number);
result6.CellValue = new CellValue("400000");

resultSum.CellFormula = new CellFormula("=sum(B4:G4)");
#endregion


worksheetPart.Worksheet.Save();
document.Close();
}
}

#endregion

#region Get Row Index in Sheet
// Given a cell name, parses the specified cell to get the row index.
private static uint GetRowIndex(string cellName)
{
// Create a regular expression to match the row index portion the cell name.
Regex regex = new Regex(@"\d+");
Match match = regex.Match(cellName);

return uint.Parse(match.Value);
}

#endregion

#region Get Column Name in Sheet
// Given a cell name, parses the specified cell to get the column name.
private static string GetColumnName(string cellName)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellName);

return match.Value;
}

#endregion

#region Insert a Cell into a Worksheet
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild();
string cellReference = columnName + rowIndex;

// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}

// If there is not a cell with the specified column name, insert one.
if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}

Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);

worksheet.Save();
return newCell;
}
}
#endregion

#endregion

I leverage the msdn link for to update cell range in the excel sheet.

Monday, July 27, 2009

SpredSheetML : Updating Column in Excel sheet

Hi,

Thw basic thing in excel is to update the cell in the sheet. We have know idea how to do that. From packaging class method it is diffcult/complez to update specific row column. The SPreadsheetML makes more easy to do that. We have to just add reference of the DocumentFormat.OpenXml and write below code in the solution.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ExcelUpdation
{
class Program
{
static void Main(string[] args)
{
// InsertText(@"c:\SummaryGraphs.xlsx", "123456789");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "C");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "D");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "E");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "F");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "G");
UpdateCell(@"c:\SummaryGraphs.xlsx", "420", 22, "H");

UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "C");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "D");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "E");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "F");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "G");
UpdateCell(@"c:\SummaryGraphs.xlsx", "421", 23, "H");


}

#region Approch 1

public static void UpdateCell(string docName, string text,uint rowIndex, string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Summary"); //Sheet Title in work book
if (worksheetPart != null)
{ Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild(). Elements().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column and
private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
Row row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements().Where(c => string.Compare (c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild().
Elements().Where(r => r.RowIndex == rowIndex).First();
}
#endregion

}
}

The reference link
Click Here!

Friday, July 17, 2009

Open XML : Retrieving Word Content Based on Styles

To find or retrive content style based conetent from created word document:

Follow this code:

public static string paraStyle = "Heading1";
public static string paraStyle2 = "Heading2";

using (WordprocessingDocument WorDocument = WordprocessingDocument.Create(@"c:\Test.docx", WordprocessingDocumentType.Document))
{
// Add a new main document part.
MainDocumentPart mainPart = WorDocument.AddMainDocumentPart();
//Create Document tree for simple document.
mainPart.Document = new Document();
//Create Body (this element contains other elements that we want to include
Body body = new Body();
mainPart.Document.Append(body);
// Save changes to the main document part.
mainPart.Document.Save();


int countHeading1= mainPart.ParagraphsByStyleName(paraStyle).Count();
int countHeading2 = mainPart.ParagraphsByStyleName(paraStyle2).Count();
}
}


//Content Serch code.

#region Content

public static string GetStyleIdFromStyleName(MainDocumentPart mainPart, string styleName)
{
StyleDefinitionsPart stylePart = mainPart.StyleDefinitionsPart;
string styleId = stylePart.Styles.Descendants <StyleId >().Where
(s = > s.Val.Value.Equals(styleName))
.Select(n = > ((Style)n.Parent).StyleId).FirstOrDefault();
return styleId ?? styleName;
}
public static IEnumerable <Paragraph > ParagraphsByStyleName(this MainDocumentPart mainPart, string styleName)
{
string styleId = GetStyleIdFromStyleName(mainPart, styleName);
IEnumerable <Paragraph > paraList =
mainPart.Document.Descendants <Paragraph >()
.Where(p = > IsParagraphInStyle(p, styleId));
return paraList;
}

private static bool IsParagraphInStyle(Paragraph p, string styleId)
{
ParagraphProperties pPr = p.GetFirstChild <ParagraphProperties >();
if (pPr != null)
{
ParagraphStyleId paraStyle = pPr.ParagraphStyleId;
if (paraStyle != null)
{
return paraStyle.Val.Value.Equals(styleId);
}
}
return false;
}
public static IEnumerable <Run > RunsByStyleName(this MainDocumentPart mainPart, string styleName)
{
string styleId = GetStyleIdFromStyleName(mainPart, styleName);
IEnumerable <Run > runList = mainPart.Document.Descendants <Run >()
.Where(r = > IsRunInStyle(r, styleId));
return runList;
}
private static bool IsRunInStyle(Run r, string styleId)
{
RunProperties rPr = r.GetFirstChild <RunProperties >();
if (rPr != null)
{
RunStyleId runStyle = rPr.RunStyleId;
if (runStyle != null)
{
return runStyle.Val.Value.Equals(styleId);
}
}
return false;
}
//For Table style
public static IEnumerable <Table > TablesByStyleName(this MainDocumentPart mainPart, string styleName)
{
string styleId = GetStyleIdFromStyleName(mainPart, styleName);
IEnumerable <Table > tableList = mainPart.Document.Descendants <Table >()
.Where(t = > IsTableInStyle(t, styleId));
return tableList;
}
private static bool IsTableInStyle(Table tbl, string styleId)
{
TableProperties tblPr = tbl.GetFirstChild <TableProperties >();
if (tblPr != null)
{
TableStyleId tblStyle = tblPr.TableStyleId;
if (tblStyle != null)
{
return tblStyle.Val.Value.Equals(styleId);
}
}
return false;
}

#endregion

Thursday, July 16, 2009

Open XML: How to use Reflector Tool in Open XML SDK 2.0

This brief description show how to use the documentreflector tool for open xml sdk 2.0.

1. Create a sample document to insert text programmatically like below in screen:


2. Open the tool from SDK folder tools.


3. Click on DocumentReflector Tool.


4. It opens as below in the screen.


5. Click on file->open


6. Select created document from the dialog box.



7. The tools show all the related xml from the document.


8. Click on Document-body-paragraph-text


9. Click on Paragraph.

10 Click on File->Export Code


11. Save the CS file


12. Export Success message


13. The genrated CS file. use in your code.

Open XML : Insert Bullets and Numbering in Word Document

To insert bullets and numbering in the word document use below code :

Numbering :

using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml;

namespace GeneratedCode
{
public class GeneratedClass
{
public static Paragraph GenerateParagraph()
{
var element =
new Paragraph(
new ParagraphProperties(
new ParagraphStyleId(){ Val = "ListParagraph" },
new NumberingProperties(
new NumberingLevelReference(){ Val = 0 },
Type of bullet style-> new NumberingId(){ Val = 1 })),
new Run(
new Text("List 1"))//Text you want to insert with number
){ RsidParagraphAddition = "005F3962", RsidParagraphProperties = "00330DA9", RsidRunAdditionDefault = "00330DA9" };
return element;
}

}
}

Bullets:

using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml;

namespace GeneratedCode
{
public class GeneratedClass
{
public static Paragraph GenerateParagraph()
{
var element =
new Paragraph(
new ParagraphProperties(
new ParagraphStyleId(){ Val = "ListParagraph" },
new NumberingProperties(
new NumberingLevelReference(){ Val = 0 },
Type of bullet style-> new NumberingId(){ Val = 2 })),
new Run(
new Text("P 2"))//Text tou want to insert with bullet
){ RsidParagraphAddition = "00031711", RsidParagraphProperties = "00031711", RsidRunAdditionDefault = "00031711" };
return element;
}

}
}

Also remember the styles.xml file to attached with document. How to it given in previous post.

Open XML : SDK and tools

The SDk and tool is at following link :

Click Here

Open XML : Image Part in the Word document

To insert image in the word document. Use reflector tool which comes with Open Xml 2.0 SDk. it will make great help in generatin the code. For inserting a image use below steps:

1. Create a blanck word dcoument.
2. Copy paste the image(which you want to insert).
3. The go to DocumentReflectorTool.
4. Open this document in that tool. copy the image code.
5. Add following code to solution:
ImagePart imgPart = mainPart.AddImagePart(ImagePartType.Jpeg);
using (FileStream strem = new FileStream(@"C:\Bharat.JPG", FileMode.Open))
{
imgPart.FeedData(strem);
}
imagePartID = mainPart.GetIdOfPart(imgPart);
mainPart.AddExtendedPart ("http://schemas.openxmlformats.org/officeDocument/2006/relationships/image", "media/image1.jpeg", imagePartID);
Paragraph ImageParagraph = new Paragraph();

body.Append(new Paragraph(new Run(GenerateDrawing())));

Copied code from reflector tool :

public static Drawing GenerateDrawing()
{
var element =
new Drawing(
new wp.Inline(
new wp.Extent(){ Cx = 536575L, Cy = 407670L },
new wp.EffectExtent(){ LeftEdge = 19050L, TopEdge = 0L, RightEdge = 0L, BottomEdge = 0L },
new wp.DocProperties(){ Id = (UInt32Value)1U, Name = "Picture 1", Description = "C:\\Bharat.JPG" },
new wp.NonVisualGraphicFrameDrawingProperties(
new a.GraphicFrameLocks(){ NoChangeAspect = true }),
new a.Graphic(
new a.GraphicData(
new pic.Picture(
new pic.NonVisualPictureProperties(
new pic.NonVisualDrawingProperties(){ Id = (UInt32Value)0U, Name = "Picture 1", Description = "C:\\Bharat.JPG" },
new pic.NonVisualPictureDrawingProperties(
new a.PictureLocks(){ NoChangeAspect = true, NoChangeArrowheads = true })),
new pic.BlipFill(
new a.Blip() { Embed = imagePartID, CompressionState = a.BlipCompressionValues.Print },
new a.SourceRectangle(),
new a.Stretch(
new a.FillRectangle())),
new pic.ShapeProperties(
new a.Transform2D(
new a.Offset(){ X = 0L, Y = 0L },
new a.Extents(){ Cx = 536575L, Cy = 407670L }),
new a.PresetGeometry(
new a.AdjustValueList()
){ Preset = a.ShapeTypeValues.Rectangle },
new a.NoFill(),
new a.Outline(
new a.NoFill(),
new a.Miter(){ Limit = 800000 },
new a.HeadEnd(),
new a.TailEnd()
){ Width = 9525 }
){ BlackWhiteMode = a.BlackWhiteModeValues.Auto })
){ Uri = "http://schemas.openxmlformats.org/drawingml/2006/picture" })
){ DistanceFromTop = (UInt32Value)0U, DistanceFromBottom = (UInt32Value)0U, DistanceFromLeft = (UInt32Value)0U, DistanceFromRight = (UInt32Value)0U });
return element;
}

Open XML : Styles in the Word document

Styles are the important part of the word document. When we create paragraph or link or other component in the word document we have to set some styles on that components like Hyperlink,Heading1,23.. or Title etc.

For this we have to do some exercise like as follows :

1. Create a word documents
2. Insert all the styles like in image.


3. Save the document.
4. Rename the document(DocName.docx to DocName.zip)
5. Extract the document.
6. Go to DocName\Word folder


7. Copy the styles.xml and paste in the solution.
8. Write the below code to use in our solution

StyleDefinitionsPart styleDefinitionsPart = mainPart.AddNewPart();

//File Styles Uploading : Location of style c:\\styles.xml
FileStream stylesTemplate = new FileStream("c:\\styles.xml", FileMode.Open, FileAccess.Read);
styleDefinitionsPart.FeedData(stylesTemplate);
styleDefinitionsPart.Styles.Save();
9. When we want to append Heading 1 with our para then we can use like :

Paragraph AuthorPara = new Paragraph();
Run run = new Run();
Text TextLine = new Text("Created by: " + System.Environment.UserName);
run4.Append(TextLine);
AuthorPara.Append(new ParagraphProperties (new ParagraphStyleId (){Val="Heading1"}), run);

This is the way we can append styles to our created document.

Wednesday, July 15, 2009

Open XML : Adding Hyperlink to word document by WordProcessingML

To add hyperlink in the Microsoft word document. We have to write following code:

using (WordprocessingDocument WorDocument = WordprocessingDocument.Create(@"c:\Test.docx", WordprocessingDocumentType.Document))
{
// Add a new main document part.
MainDocumentPart mainPart = WorDocument.AddMainDocumentPart();
//Create Document tree for simple document.
mainPart.Document = new Document();
//Create Body (this element contains other elements that we want to include
Body body = new Body();

//Hyperlink to add
Paragraph HprPara = new Paragraph(
new Hyperlink(
new Run(
new RunProperties(
new RunStyleId(){ Val = "Hyperlink" }),
new Text("TestLink")
){ RsidRunProperties = "00356238" }
){ History = BooleanValues.One, Id = "rId4" }
){ RsidParagraphAddition = "00651E0C", RsidRunAdditionDefault = "00356238" };

//External realationship to make for hyperlink
ExternalRelationship exprRealationship = mainPart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink", new Uri("http://www.google.com"), "rId4");

body.Append(HprPara);

mainPart.Document.Append(body);
// Save changes to the main document part.
mainPart.Document.Save();
}

Tuesday, July 14, 2009

Open XML : Working with WordProcessingML

To create word document from the open xml (packaging class) is quiet tedious job. MS provided a DocumentFormat.OpenXml assembly for Open XML work. This assembly provides object for Microsoft Word, Microsoft Excel, and Microsoft PowerPoint. It will make easy to developer to create word, excel or PowerPoint reports programmatically.

Add DocumentFormat.OpenXml assembly and start working on creation of the reports. This assembly is provided by the open xml SDK 2.0. It can be downloaded from the Microsoft download center.

The following references are used for creation of the word document from this assembly.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

We can first start with simple adding text in the word document from the code. To add text in the word document add the assembly reference and then add above namespaces:

First create a paragraph object and set its properties like below

using (WordprocessingDocument WorDocument = WordprocessingDocument.Create(@"c:\Test.docx", WordprocessingDocumentType.Document))
{
// Add a new main document part.
MainDocumentPart mainPart = WorDocument.AddMainDocumentPart();
//Create Document tree for simple document.
mainPart.Document = new Document();
//Create Body (this element contains other elements that we want to include
Body body = new Body();

FontSize fontsize = new FontSize();
fontsize.Val = 38;
Color TextColor = new Color();
TextColor.Val = "4F81BD";
Bold boldFont = new Bold();
boldFont.Val = DocumentFormat.OpenXml.Wordprocessing.BooleanValues.On;


Paragraph AuthorPara = new Paragraph();
Run run = new Run();
Text TextLine = new Text("Created by: " + System.Environment.UserName);
run.Append(TextLine);
AuthorPara.Append(run);
RunProperties runProps = new RunProperties();
RunFonts runFonts = new RunFonts();
runFonts.Ascii = "Segoe UI";
runProps.Append(fontsize);
runProps.Append(runFonts);
runProps.Append(boldFont);
runProps.Append(TextColor);
run4.PrependChild(runProps);
body.Append(AuthorPara);

mainPart.Document.Append(body);
// Save changes to the main document part.
mainPart.Document.Save();
}

For creating table in the word document create table object and set properties as below

Table object and properties
Table table = new Table();
TableProperties tblPr = new TableProperties();
TableBorders tblBorders = new TableBorders();
TableWidth tblWidth = new TableWidth();


tblWidth.Width = 550;
tblBorders.TopBorder = new TopBorder();
tblBorders.TopBorder.Val = new EnumValue(BorderValues.BasicBlackDots);
tblBorders.BottomBorder = new BottomBorder();
tblBorders.BottomBorder.Val = new EnumValue(BorderValues.BasicBlackDots);
tblBorders.LeftBorder = new LeftBorder();
tblBorders.LeftBorder.Val = new EnumValue(BorderValues.BasicBlackDots);
tblBorders.RightBorder = new RightBorder();
tblBorders.RightBorder.Val = new EnumValue(BorderValues.BasicBlackDots);
tblBorders.InsideHorizontalBorder = new InsideHorizontalBorder();
tblBorders.InsideHorizontalBorder.Val = BorderValues.Single;
tblBorders.InsideVerticalBorder = new InsideVerticalBorder();
tblBorders.InsideVerticalBorder.Val = BorderValues.Single;
tblPr.Append(tblBorders);
table.Append(tblPr);
tblPr = new TableProperties();
tblPr.Append(tblWidth);

Create new table row and table cell

//Table Start
TableRow tr = new TableRow();
TableCell tc = new TableCell(AuthorPara);//Paragraph above
TableCellWidth tblCellWidth = new TableCellWidth();
tblCellWidth.Width = 600;
TableCellProperties tcp = new TableCellProperties();
GridSpan griedSpan = new GridSpan();
griedSpan.Val = 11;

tcp.Append(griedSpan);
tcp.Append(tblCellWidth);
tc.Append(tcp);

tr.Append(tc);
table.Append(tr);

In this way table can be created in the open xml using wordprocessingML

Thursday, July 9, 2009

Question Tips 4 !

Hi all,

The another list of question on SharePoint

1. What is SPServerUpdate?
A: Causes the server to save its state and propagate the changes to all computers in the server farm.
2. Fetching data from multiple list. How?
3. SPBatch some thing like that ?
4. Deployment of the Work flow and web parts.
5. What is web part?
6. What is workflow?
7. Imp : Flow of SharePoint?
8. What is SPSiteData Query?
A: Represents a query that can be performed across multiple lists in multiple Web sites in the same Web site collection.
9. What is defference between in Method and Activity?

Tuesday, July 7, 2009

Number of days calculation in InfoPath

For number of days calculation, total days and total number of working days. Do the following steps :

1. Design the form like below


2. Add the below code in the button events

public void WorkingDays_Clicked(object sender, ClickedEventArgs e)
{
int CalculateDays = 1;
XPathNavigator Main = MainDataSource.CreateNavigator();
DateTime startDate = DateTime.Parse(Main.SelectSingleNode("/my:myFields/my:StartDate",NamespaceManager).Value);

DateTime endDate = DateTime.Parse(Main.SelectSingleNode("/my:myFields/my:EndDate", NamespaceManager).Value);

TimeSpan span =endDate.Subtract(startDate);

for(int i= 1;i<=span.Days;i++)
{
startDate = startDate.AddDays(1);
if(startDate.DayOfWeek !=DayOfWeek.Sunday && startDate.DayOfWeek !=DayOfWeek.Saturday)
{
CalculateDays++;
}
}

Main.SelectSingleNode("/my:myFields/my:WorkingDays",NamespaceManager).SetValue(CalculateDays.ToString());
}

public void TotalDays_Clicked(object sender, ClickedEventArgs e)
{
int CalculateDays = 1;
XPathNavigator Main = MainDataSource.CreateNavigator();

DateTime startDate = DateTime.Parse(Main.SelectSingleNode("/my:myFields/my:StartDate", NamespaceManager).Value);

DateTime endDate = DateTime.Parse(Main.SelectSingleNode("/my:myFields/my:EndDate", NamespaceManager).Value);

TimeSpan span = endDate.Subtract(startDate);
for (int i = 1; i <= span.Days; i++)
{
startDate = startDate.AddDays(1);

CalculateDays++;
}

Main.SelectSingleNode("/my:myFields/my:TotalDays", NamespaceManager).SetValue(CalculateDays.ToString());
}

Friday, July 3, 2009

Using SPQuery in SharePoint object model

SPSite rootSite = new SPSite("http://mossvs2008");
SPWeb SubSite = rootSite.AllWebs["LeaveSite"];
string strTitleValue = string.Empty;
Console.WriteLine("Please enter Employee ID");

strTitleValue = Console.ReadLine();
SPList SPList = SubSite.Lists["Employee List"];
SPQuery query = new SPQuery();
query.Query = <Where><Eq<>FieldRef Name='Title' /><Value Type='Text'>"+strTitleValue+">/Value>>/Eq></Where>";>/

SPListItemCollection itemcol = SPList.GetItems(query);
string strTitle = string.Empty;
string strDesignation = string.Empty;
string strEmployeeName = string.Empty;

foreach (SPListItem item in itemcol)
{
strTitle = item["Title"].ToString();
strDesignation = item["Designation"].ToString();
strEmployeeName = item["Employee Name"].ToString();
}
Console.WriteLine("Employee ID.........");
Console.WriteLine(strTitle);
Console.WriteLine("Employee Designation.");
Console.WriteLine(strDesignation);
Console.WriteLine("Employee Name........");
Console.WriteLine(strEmployeeName);

Thursday, July 2, 2009

Change the Top Tool Bar color in FormServer.aspx page in SharePoint 2007

To change the color of the toolbar in the InfoPath formserver.aspx page in SharePoint2007 follow these steps:

1. Go to following location "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS".
2. Open the page "FormServer.aspx" in notepad.
3. Write the tag of scripting below page directive and copy paste this below script


//function changeColor()
//{
//document.getElementById("__toolbar_top").style.backgroundColor = "#E96A37";
//}


4. Call this function in the body OnLoad event tage of the page.

body OnLoad="javascript:changeColor()" style="margin: 0px;overflow:auto;"


5. The resultant change in the page



We have to adopt this approach because there is no css class is attached with this div. Inline styles are written instead of CSS Class.

To Show Submit Confirmation Message Box in Web Based InfoPath 2007

Pop up a message box using managed code(c#) in VSTA for a Browser-enabled infopath form,there is no proper solution. But for form submit we can show confirmation message box in InfoPath when form is submitted.

Follow below steps:
1. Design a form Blank or by template.

2. Drag a Button on form "Submit".
3. Goto Prperties of the button and change the action Rules and Code to Submit


4. Click on Submit Options button


5. Click on Allow users to submit form



6. Click on Perform Custom Action using Code



7. Click on Show the Submit menu item....



8. Click on Advance>> button



9. Click on Success and failure messgae



10. Click on Cutom messages



11. Write your custom messages and select After Submit drop down like Close the form




12. Click on Edit Code, it will take to submit button event.



The Message box looks lik as in image