Knowledgebase (2311)
Children categories
class Program
{
static void Main(string[] args)
{
AddCommentToPresentation(@"..\..\Documents\Myppt1.pptx",
"Katie Jordan", "KJ",
"This is my programmatically added comment.");
}
// Adds a comment to the first slide of the presentation document.
// The presentation document must contain at least one slide.
public static void AddCommentToPresentation(string file, string initials, string name, string text)
{
using (PresentationDocument doc = PresentationDocument.Open(file, true))
{
// Declare a CommentAuthorsPart object.
CommentAuthorsPart authorsPart;
// Verify that there is an existing comment authors part.
if (doc.PresentationPart.CommentAuthorsPart == null)
{
// If not, add a new one.
authorsPart = doc.PresentationPart.AddNewPart();
}
else
{
authorsPart = doc.PresentationPart.CommentAuthorsPart;
}
// Verify that there is a comment author list in the comment authors part.
if (authorsPart.CommentAuthorList == null)
{
// If not, add a new one.
authorsPart.CommentAuthorList = new CommentAuthorList();
}
// Declare a new author ID.
uint authorId = 0;
CommentAuthor author = null;
// If there are existing child elements in the comment authors list...
if (authorsPart.CommentAuthorList.HasChildren)
{
// Verify that the author passed in is on the list.
var authors = authorsPart.CommentAuthorList.Elements().Where(a => a.Name == name && a.Initials == initials);
// If so...
if (authors.Any())
{
// Assign the new comment author the existing author ID.
author = authors.First();
authorId = author.Id;
}
// If not...
if (author == null)
{
// Assign the author passed in a new ID
authorId = authorsPart.CommentAuthorList.Elements().Select(a => a.Id.Value).Max();
}
}
// If there are no existing child elements in the comment authors list.
if (author == null)
{
authorId++;
// Add a new child element(comment author) to the comment author list.
author = authorsPart.CommentAuthorList.AppendChild
(new CommentAuthor()
{
Id = authorId,
Name = name,
Initials = initials,
ColorIndex = 0
});
}
// Get the first slide, using the GetFirstSlide method.
SlidePart slidePart1 = GetFirstSlide(doc);
// Declare a comments part.
SlideCommentsPart commentsPart;
// Verify that there is a comments part in the first slide part.
if (slidePart1.GetPartsOfType().Count() == 0)
{
// If not, add a new comments part.
commentsPart = slidePart1.AddNewPart();
}
else
{
// Else, use the first comments part in the slide part.
commentsPart = slidePart1.GetPartsOfType().First();
}
// If the comment list does not exist.
if (commentsPart.CommentList == null)
{
// Add a new comments list.
commentsPart.CommentList = new CommentList();
}
// Get the new comment ID.
uint commentIdx = author.LastIndex == null ? 1 : author.LastIndex + 1;
author.LastIndex = commentIdx;
// Add a new comment.
Comment comment = commentsPart.CommentList.AppendChild(
new Comment()
{
AuthorId = authorId,
Index = commentIdx,
DateTime = DateTime.Now
});
// Add the position child node to the comment element.
comment.Append(
new Position() { X = 100, Y = 200 },
new Text() { Text = text });
// Save the comment authors part.
authorsPart.CommentAuthorList.Save();
// Save the comments part.
commentsPart.CommentList.Save();
}
}
// Get the slide part of the first slide in the presentation document.
public static SlidePart GetFirstSlide(PresentationDocument presentationDocument)
{
// Get relationship ID of the first slide
PresentationPart part = presentationDocument.PresentationPart;
SlideId slideId = part.Presentation.SlideIdList.GetFirstChild();
string relId = slideId.RelationshipId;
// Get the slide part by the relationship ID.
SlidePart slidePart = (SlidePart)part.GetPartById(relId);
return slidePart;
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
const string fileName = @"..\..\Documents\Sheets13.xlsx";
// Retrieve the value in cell A1.
string value = GetCellValue(fileName, "Sheet1", "A1");
Console.WriteLine(value);
// Retrieve the date value in cell A2.
value = GetCellValue(fileName, "Sheet1", "A2");
Console.WriteLine(DateTime.FromOADate(double.Parse(value)).ToShortDateString());
Console.ReadLine();
}
// Retrieve the value of a cell, given a file name, sheet name,
// and address name.
public static string GetCellValue(string fileName,
string sheetName,
string addressName)
{
string value = null;
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = wbPart.Workbook.Descendants().
Where(s => s.Name == sheetName).FirstOrDefault();
// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable =
wbPart.GetPartsOfType()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value =
stringTable.SharedStringTable
.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
}
return value;
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
const string DEMOFILE = @"..\..\Documents\Sheets12.xlsx";
var results = GetAllWorksheets(DEMOFILE);
foreach (Sheet item in results)
{
Console.WriteLine(item.Name);
}
Console.ReadLine();
}
// Retrieve a List of all the sheets in a workbook.
// The Sheets class contains a collection of
// OpenXmlElement objects, each representing one of
// the sheets.
public static Sheets GetAllWorksheets(string fileName)
{
Sheets theSheets = null;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
theSheets = wbPart.Workbook.Sheets;
}
return theSheets;
}
}
Published in
OpenXML
