.NET (1273)
Children categories
class Program
{
static void Main(string[] args)
{
string fileName = @"..\..\Documents\myppt6.pptx";
string author = "MZ";
DeleteCommentsByAuthorInPresentation(fileName, author);
}
// Remove all the comments in the slides by a certain author.
public static void DeleteCommentsByAuthorInPresentation(string fileName, string author)
{
if (String.IsNullOrEmpty(fileName) || String.IsNullOrEmpty(author))
throw new ArgumentNullException("File name or author name is NULL!");
using (PresentationDocument doc = PresentationDocument.Open(fileName, true))
{
// Get the specifed comment author.
IEnumerable commentAuthors =
doc.PresentationPart.CommentAuthorsPart.CommentAuthorList.Elements()
.Where(e => e.Name.Value.Equals(author));
// Iterate through all the matching authors.
foreach (CommentAuthor commentAuthor in commentAuthors)
{
UInt32Value authorId = commentAuthor.Id;
// Iterate through all the slides and get the slide parts.
foreach (SlidePart slide in doc.PresentationPart.SlideParts)
{
SlideCommentsPart slideCommentsPart = slide.SlideCommentsPart;
// Get the list of comments.
if (slideCommentsPart != null && slide.SlideCommentsPart.CommentList != null)
{
IEnumerable commentList =
slideCommentsPart.CommentList.Elements().Where(e => e.AuthorId == authorId.Value);
List comments = new List();
comments = commentList.ToList();
foreach (Comment comm in comments)
{
// Delete all the comments by the specified author.
slideCommentsPart.CommentList.RemoveChild(comm);
}
// If the commentPart has no existing comment.
if (slideCommentsPart.CommentList.ChildElements.Count == 0)
// Delete this part.
slide.DeletePart(slideCommentsPart);
}
}
// Delete the comment author from the comment authors part.
doc.PresentationPart.CommentAuthorsPart.CommentAuthorList.RemoveChild(commentAuthor);
}
}
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
DeleteSlide(@"..\..\Documents\Myppt5.pptx", 2);
Console.WriteLine("Number of slides = {0}",CountSlides(@"..\..\Documents\Myppt5.pptx"));
}
// Get the presentation object and pass it to the next CountSlides method.
public static int CountSlides(string presentationFile)
{
// Open the presentation as read-only.
using (PresentationDocument presentationDocument = PresentationDocument.Open(presentationFile, false))
{
// Pass the presentation to the next CountSlide method
// and return the slide count.
return CountSlides(presentationDocument);
}
}
// Count the slides in the presentation.
public static int CountSlides(PresentationDocument presentationDocument)
{
// Check for a null document object.
if (presentationDocument == null)
{
throw new ArgumentNullException("presentationDocument");
}
int slidesCount = 0;
// Get the presentation part of document.
PresentationPart presentationPart = presentationDocument.PresentationPart;
// Get the slide count from the SlideParts.
if (presentationPart != null)
{
slidesCount = presentationPart.SlideParts.Count();
}
// Return the slide count to the previous method.
return slidesCount;
}
//
// Get the presentation object and pass it to the next DeleteSlide method.
public static void DeleteSlide(string presentationFile, int slideIndex)
{
// Open the source document as read/write.
using (PresentationDocument presentationDocument = PresentationDocument.Open(presentationFile, true))
{
// Pass the source document and the index of the slide to be deleted to the next DeleteSlide method.
DeleteSlide(presentationDocument, slideIndex);
}
}
// Delete the specified slide from the presentation.
public static void DeleteSlide(PresentationDocument presentationDocument, int slideIndex)
{
if (presentationDocument == null)
{
throw new ArgumentNullException("presentationDocument");
}
// Use the CountSlides sample to get the number of slides in the presentation.
int slidesCount = CountSlides(presentationDocument);
if (slideIndex < 0 || slideIndex >= slidesCount)
{
throw new ArgumentOutOfRangeException("slideIndex");
}
// Get the presentation part from the presentation document.
PresentationPart presentationPart = presentationDocument.PresentationPart;
// Get the presentation from the presentation part.
Presentation presentation = presentationPart.Presentation;
// Get the list of slide IDs in the presentation.
SlideIdList slideIdList = presentation.SlideIdList;
// Get the slide ID of the specified slide
SlideId slideId = slideIdList.ChildElements[slideIndex] as SlideId;
// Get the relationship ID of the slide.
string slideRelId = slideId.RelationshipId;
// Remove the slide from the slide list.
slideIdList.RemoveChild(slideId);
//
// Remove references to the slide from all custom shows.
if (presentation.CustomShowList != null)
{
// Iterate through the list of custom shows.
foreach (var customShow in presentation.CustomShowList.Elements())
{
if (customShow.SlideList != null)
{
// Declare a link list of slide list entries.
LinkedList slideListEntries = new LinkedList();
foreach (SlideListEntry slideListEntry in customShow.SlideList.Elements())
{
// Find the slide reference to remove from the custom show.
if (slideListEntry.Id != null && slideListEntry.Id == slideRelId)
{
slideListEntries.AddLast(slideListEntry);
}
}
// Remove all references to the slide from the custom show.
foreach (SlideListEntry slideListEntry in slideListEntries)
{
customShow.SlideList.RemoveChild(slideListEntry);
}
}
}
}
// Save the modified presentation.
presentation.Save();
// Get the slide part for the specified slide.
SlidePart slidePart = presentationPart.GetPartById(slideRelId) as SlidePart;
// Remove the slide part.
presentationPart.DeletePart(slidePart);
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
string filepath = @"..\..\Documents\PresentationFromFilename.pptx";
CreatePresentation(filepath);
}
public static void CreatePresentation(string filepath)
{
// Create a presentation at a specified file path. The presentation document type is pptx, by default.
PresentationDocument presentationDoc = PresentationDocument.Create(filepath, PresentationDocumentType.Presentation);
PresentationPart presentationPart = presentationDoc.AddPresentationPart();
presentationPart.Presentation = new Presentation();
CreatePresentationParts(presentationPart);
//Close the presentation handle
presentationDoc.Close();
}
private static void CreatePresentationParts(PresentationPart presentationPart)
{
SlideMasterIdList slideMasterIdList1 = new SlideMasterIdList(new SlideMasterId() { Id = (UInt32Value)2147483648U, RelationshipId = "rId1" });
SlideIdList slideIdList1 = new SlideIdList(new SlideId() { Id = (UInt32Value)256U, RelationshipId = "rId2" });
SlideSize slideSize1 = new SlideSize() { Cx = 9144000, Cy = 6858000, Type = SlideSizeValues.Screen4x3 };
NotesSize notesSize1 = new NotesSize() { Cx = 6858000, Cy = 9144000 };
DefaultTextStyle defaultTextStyle1 = new DefaultTextStyle();
presentationPart.Presentation.Append(slideMasterIdList1, slideIdList1, slideSize1, notesSize1, defaultTextStyle1);
SlidePart slidePart1;
SlideLayoutPart slideLayoutPart1;
SlideMasterPart slideMasterPart1;
ThemePart themePart1;
slidePart1 = CreateSlidePart(presentationPart);
slideLayoutPart1 = CreateSlideLayoutPart(slidePart1);
slideMasterPart1 = CreateSlideMasterPart(slideLayoutPart1);
themePart1 = CreateTheme(slideMasterPart1);
slideMasterPart1.AddPart(slideLayoutPart1, "rId1");
presentationPart.AddPart(slideMasterPart1, "rId1");
presentationPart.AddPart(themePart1, "rId5");
}
private static SlidePart CreateSlidePart(PresentationPart presentationPart)
{
SlidePart slidePart1 = presentationPart.AddNewPart("rId2");
slidePart1.Slide = new Slide(
new CommonSlideData(
new ShapeTree(
new P.NonVisualGroupShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)1U, Name = "" },
new P.NonVisualGroupShapeDrawingProperties(),
new ApplicationNonVisualDrawingProperties()),
new GroupShapeProperties(new TransformGroup()),
new P.Shape(
new P.NonVisualShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Title 1" },
new P.NonVisualShapeDrawingProperties(new ShapeLocks() { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape())),
new P.ShapeProperties(),
new P.TextBody(
new BodyProperties(),
new ListStyle(),
new Paragraph(new EndParagraphRunProperties() { Language = "en-US" }))))),
new ColorMapOverride(new MasterColorMapping()));
return slidePart1;
}
private static SlideLayoutPart CreateSlideLayoutPart(SlidePart slidePart1)
{
SlideLayoutPart slideLayoutPart1 = slidePart1.AddNewPart("rId1");
SlideLayout slideLayout = new SlideLayout(
new CommonSlideData(new ShapeTree(
new P.NonVisualGroupShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)1U, Name = "" },
new P.NonVisualGroupShapeDrawingProperties(),
new ApplicationNonVisualDrawingProperties()),
new GroupShapeProperties(new TransformGroup()),
new P.Shape(
new P.NonVisualShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "" },
new P.NonVisualShapeDrawingProperties(new ShapeLocks() { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape())),
new P.ShapeProperties(),
new P.TextBody(
new BodyProperties(),
new ListStyle(),
new Paragraph(new EndParagraphRunProperties()))))),
new ColorMapOverride(new MasterColorMapping()));
slideLayoutPart1.SlideLayout = slideLayout;
return slideLayoutPart1;
}
private static SlideMasterPart CreateSlideMasterPart(SlideLayoutPart slideLayoutPart1)
{
SlideMasterPart slideMasterPart1 = slideLayoutPart1.AddNewPart("rId1");
SlideMaster slideMaster = new SlideMaster(
new CommonSlideData(new ShapeTree(
new P.NonVisualGroupShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)1U, Name = "" },
new P.NonVisualGroupShapeDrawingProperties(),
new ApplicationNonVisualDrawingProperties()),
new GroupShapeProperties(new TransformGroup()),
new P.Shape(
new P.NonVisualShapeProperties(
new P.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Title Placeholder 1" },
new P.NonVisualShapeDrawingProperties(new ShapeLocks() { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape() { Type = PlaceholderValues.Title })),
new P.ShapeProperties(),
new P.TextBody(
new BodyProperties(),
new ListStyle(),
new Paragraph())))),
new P.ColorMap() { Background1 = D.ColorSchemeIndexValues.Light1, Text1 = D.ColorSchemeIndexValues.Dark1, Background2 = D.ColorSchemeIndexValues.Light2, Text2 = D.ColorSchemeIndexValues.Dark2, Accent1 = D.ColorSchemeIndexValues.Accent1, Accent2 = D.ColorSchemeIndexValues.Accent2, Accent3 = D.ColorSchemeIndexValues.Accent3, Accent4 = D.ColorSchemeIndexValues.Accent4, Accent5 = D.ColorSchemeIndexValues.Accent5, Accent6 = D.ColorSchemeIndexValues.Accent6, Hyperlink = D.ColorSchemeIndexValues.Hyperlink, FollowedHyperlink = D.ColorSchemeIndexValues.FollowedHyperlink },
new SlideLayoutIdList(new SlideLayoutId() { Id = (UInt32Value)2147483649U, RelationshipId = "rId1" }),
new TextStyles(new TitleStyle(), new BodyStyle(), new OtherStyle()));
slideMasterPart1.SlideMaster = slideMaster;
return slideMasterPart1;
}
private static ThemePart CreateTheme(SlideMasterPart slideMasterPart1)
{
ThemePart themePart1 = slideMasterPart1.AddNewPart("rId5");
D.Theme theme1 = new D.Theme() { Name = "Office Theme" };
D.ThemeElements themeElements1 = new D.ThemeElements(
new D.ColorScheme(
new D.Dark1Color(new D.SystemColor() { Val = D.SystemColorValues.WindowText, LastColor = "000000" }),
new D.Light1Color(new D.SystemColor() { Val = D.SystemColorValues.Window, LastColor = "FFFFFF" }),
new D.Dark2Color(new D.RgbColorModelHex() { Val = "1F497D" }),
new D.Light2Color(new D.RgbColorModelHex() { Val = "EEECE1" }),
new D.Accent1Color(new D.RgbColorModelHex() { Val = "4F81BD" }),
new D.Accent2Color(new D.RgbColorModelHex() { Val = "C0504D" }),
new D.Accent3Color(new D.RgbColorModelHex() { Val = "9BBB59" }),
new D.Accent4Color(new D.RgbColorModelHex() { Val = "8064A2" }),
new D.Accent5Color(new D.RgbColorModelHex() { Val = "4BACC6" }),
new D.Accent6Color(new D.RgbColorModelHex() { Val = "F79646" }),
new D.Hyperlink(new D.RgbColorModelHex() { Val = "0000FF" }),
new D.FollowedHyperlinkColor(new D.RgbColorModelHex() { Val = "800080" }))
{ Name = "Office" },
new D.FontScheme(
new D.MajorFont(
new D.LatinFont() { Typeface = "Calibri" },
new D.EastAsianFont() { Typeface = "" },
new D.ComplexScriptFont() { Typeface = "" }),
new D.MinorFont(
new D.LatinFont() { Typeface = "Calibri" },
new D.EastAsianFont() { Typeface = "" },
new D.ComplexScriptFont() { Typeface = "" }))
{ Name = "Office" },
new D.FormatScheme(
new D.FillStyleList(
new D.SolidFill(new D.SchemeColor() { Val = D.SchemeColorValues.PhColor }),
new D.GradientFill(
new D.GradientStopList(
new D.GradientStop(new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 },
new D.GradientStop(new D.SchemeColor(new D.Tint() { Val = 37000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 35000 },
new D.GradientStop(new D.SchemeColor(new D.Tint() { Val = 15000 },
new D.SaturationModulation() { Val = 350000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 100000 }
),
new D.LinearGradientFill() { Angle = 16200000, Scaled = true }),
new D.NoFill(),
new D.PatternFill(),
new D.GroupFill()),
new D.LineStyleList(
new D.Outline(
new D.SolidFill(
new D.SchemeColor(
new D.Shade() { Val = 95000 },
new D.SaturationModulation() { Val = 105000 })
{ Val = D.SchemeColorValues.PhColor }),
new D.PresetDash() { Val = D.PresetLineDashValues.Solid })
{
Width = 9525,
CapType = D.LineCapValues.Flat,
CompoundLineType = D.CompoundLineValues.Single,
Alignment = D.PenAlignmentValues.Center
},
new D.Outline(
new D.SolidFill(
new D.SchemeColor(
new D.Shade() { Val = 95000 },
new D.SaturationModulation() { Val = 105000 })
{ Val = D.SchemeColorValues.PhColor }),
new D.PresetDash() { Val = D.PresetLineDashValues.Solid })
{
Width = 9525,
CapType = D.LineCapValues.Flat,
CompoundLineType = D.CompoundLineValues.Single,
Alignment = D.PenAlignmentValues.Center
},
new D.Outline(
new D.SolidFill(
new D.SchemeColor(
new D.Shade() { Val = 95000 },
new D.SaturationModulation() { Val = 105000 })
{ Val = D.SchemeColorValues.PhColor }),
new D.PresetDash() { Val = D.PresetLineDashValues.Solid })
{
Width = 9525,
CapType = D.LineCapValues.Flat,
CompoundLineType = D.CompoundLineValues.Single,
Alignment = D.PenAlignmentValues.Center
}),
new D.EffectStyleList(
new D.EffectStyle(
new D.EffectList(
new D.OuterShadow(
new D.RgbColorModelHex(
new D.Alpha() { Val = 38000 })
{ Val = "000000" })
{ BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false })),
new D.EffectStyle(
new D.EffectList(
new D.OuterShadow(
new D.RgbColorModelHex(
new D.Alpha() { Val = 38000 })
{ Val = "000000" })
{ BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false })),
new D.EffectStyle(
new D.EffectList(
new D.OuterShadow(
new D.RgbColorModelHex(
new D.Alpha() { Val = 38000 })
{ Val = "000000" })
{ BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false }))),
new D.BackgroundFillStyleList(
new D.SolidFill(new D.SchemeColor() { Val = D.SchemeColorValues.PhColor }),
new D.GradientFill(
new D.GradientStopList(
new D.GradientStop(
new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 },
new D.GradientStop(
new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 },
new D.GradientStop(
new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 }),
new D.LinearGradientFill() { Angle = 16200000, Scaled = true }),
new D.GradientFill(
new D.GradientStopList(
new D.GradientStop(
new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 },
new D.GradientStop(
new D.SchemeColor(new D.Tint() { Val = 50000 },
new D.SaturationModulation() { Val = 300000 })
{ Val = D.SchemeColorValues.PhColor })
{ Position = 0 }),
new D.LinearGradientFill() { Angle = 16200000, Scaled = true })))
{ Name = "Office" });
theme1.Append(themeElements1);
theme1.Append(new D.ObjectDefaults());
theme1.Append(new D.ExtraColorSchemeList());
themePart1.Theme = theme1;
return themePart1;
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
string docName = @"..\..\Documents\Myppt3.pptx";
SetPPTShapeColor(docName);
}
// Change the fill color of a shape.
// The test file must have a filled shape as the first shape on the first slide.
public static void SetPPTShapeColor(string docName)
{
using (PresentationDocument ppt = PresentationDocument.Open(docName, true))
{
// Get the relationship ID of the first slide.
PresentationPart part = ppt.PresentationPart;
OpenXmlElementList slideIds = part.Presentation.SlideIdList.ChildElements;
string relId = (slideIds[0] as SlideId).RelationshipId;
// Get the slide part from the relationship ID.
SlidePart slide = (SlidePart)part.GetPartById(relId);
if (slide != null)
{
// Get the shape tree that contains the shape to change.
ShapeTree tree = slide.Slide.CommonSlideData.ShapeTree;
// Get the first shape in the shape tree.
Shape shape = tree.GetFirstChild();
if (shape != null)
{
// Get the style of the shape.
ShapeStyle style = shape.ShapeStyle;
// Get the fill reference.
Drawing.FillReference fillRef = style.FillReference;
// Set the fill color to SchemeColor Accent 6;
fillRef.SchemeColor = new Drawing.SchemeColor();
fillRef.SchemeColor.Val = Drawing.SchemeColorValues.Accent6;
// Save the modified slide.
slide.Slide.Save();
}
}
}
}
}
Published in
OpenXML
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
Retrieve the values of cells in a spreadsheet document
2016-01-15 06:52:17 Written by support iceblue
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
Retrieve a list of the worksheets in a spreadsheet document
2016-01-15 06:51:24 Written by support iceblue
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
Retrieve a list of the hidden worksheets in a spreadsheet document
2016-01-15 06:50:07 Written by support iceblue
class Program
{
static void Main(string[] args)
{
// Revise this path to the location of a file that contains hidden worksheets.
const string DEMOPATH = @"..\..\Documents\Sheets11.xlsx";
List sheets = GetHiddenSheets(DEMOPATH);
foreach (var sheet in sheets)
{
Console.WriteLine(sheet.Name);
}
Console.ReadLine();
}
public static List GetHiddenSheets(string fileName)
{
List returnVal = new List();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
var sheets = wbPart.Workbook.Descendants();
// Look for sheets where there is a State attribute defined,
// where the State has a value,
// and where the value is either Hidden or VeryHidden.
var hiddenSheets = sheets.Where((item) => item.State != null &&
item.State.HasValue &&
(item.State.Value == SheetStateValues.Hidden ||
item.State.Value == SheetStateValues.VeryHidden));
returnVal = hiddenSheets.ToList();
}
return returnVal;
}
}
Published in
OpenXML
Retrieve a dictionary of all named ranges in a spreadsheet document
2016-01-15 06:49:26 Written by support iceblue
class Program
{
static void Main(string[] args)
{
var result = GetDefinedNames(@"..\..\Documents\Sheet10.xlsx");
foreach (var dn in result)
Console.WriteLine("{0} {1}", dn.Key, dn.Value);
Console.ReadLine();
}
public static Dictionary GetDefinedNames(String fileName)
{
// Given a workbook name, return a dictionary of defined names.
// The pairs include the range name and a string representing the range.
var returnValue = new Dictionary();
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
var wbPart = document.WorkbookPart;
// Retrieve a reference to the defined names collection.
DefinedNames definedNames = wbPart.Workbook.DefinedNames;
// If there are defined names, add them to the dictionary.
if (definedNames != null)
{
foreach (DefinedName dn in definedNames)
returnValue.Add(dn.Name.Value, dn.Text);
}
}
return returnValue;
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
string strDoc = @"..\..\Documents\Sheet9.xlsx";
Stream stream = File.Open(strDoc, FileMode.Open);
OpenAndAddToSpreadsheetStream(stream);
stream.Close();
}
public static void OpenAndAddToSpreadsheetStream(Stream stream)
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheetDocument =SpreadsheetDocument.Open(stream, true);
// Add a new worksheet.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
spreadsheetDocument.WorkbookPart.Workbook.Save();
// Close the document handle.
spreadsheetDocument.Close();
// Caller must close the stream.
}
}
Published in
OpenXML
