OPENXML: Insert Multiple images into MS Excel file

On the web, found 2 good blog about insert image into MS Excel file.
1. How to insert an image in Excel Open XML
2. How to insert multiple images in Excel Open XML

Method 1 is very good, but it doesn't support multiple images.
Method 2 is also good, but it cannot specify the start cell to insert.

After some research work, I modified the source code based on method 1, to let it can support multiple images.

The code:

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace WindowsFormsApplication2
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public static string sourceFile = @"D:\Projects\myExcel\Travel.xlsx";
        public static string targetFile = @"C:\Temp\Test.xlsx";
        public static string ImageFile = @"C:\Temp\DSC_7328.JPG";

        private void button1_Click(object sender, EventArgs e)
        {
            File.Copy(sourceFile, targetFile, true);

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(targetFile, true))
            {
                WorkbookPart workbookpart = document.WorkbookPart;
                //WorksheetPart sheet1 = workbookpart.WorksheetParts.First();
                WorksheetPart sheet1 = GetSheetByName(workbookpart, "sheet1");
               
                //insert Image by specifying two range
                InsertImage(sheet1, 1, 1, 1, 1, new FileStream(ImageFile, FileMode.Open));
                InsertImage(sheet1, 2, 1, 2, 1, new FileStream(ImageFile, FileMode.Open));
                document.WorkbookPart.Workbook.Save();
                // Close the document handle.
                document.Close();
            }
            System.Diagnostics.Process.Start(targetFile);
        }

        /// <summary>
        /// Inserts the image at the specified location
        /// </summary>
        /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
        /// <param name="startRowIndex">The starting Row Index</param>
        /// <param name="startColumnIndex">The starting column index</param>
        /// <param name="endRowIndex">The ending row index</param>
        /// <param name="endColumnIndex">The ending column index</param>
        /// <param name="imageStream">Stream which contains the image data</param>
        private void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
        {
            ImagePartType ipt=ImagePartType.Jpeg;
            DrawingsPart drawingsPart1;
            ImagePart imagePart1;
            WorksheetDrawing worksheetDrawing1;
            if (sheet1.DrawingsPart == null)
            {
                drawingsPart1 = sheet1.AddNewPart<DrawingsPart>();
                imagePart1 = drawingsPart1.AddImagePart(ipt, sheet1.GetIdOfPart(drawingsPart1));
                worksheetDrawing1 = new WorksheetDrawing();
            }
            else
            {
                drawingsPart1 = sheet1.DrawingsPart;
                imagePart1 = drawingsPart1.AddImagePart(ipt);
                drawingsPart1.CreateRelationshipToPart(imagePart1);
                worksheetDrawing1 = drawingsPart1.WorksheetDrawing;
            }
           
            int imageNumber = drawingsPart1.ImageParts.Count<ImagePart>();
            if (imageNumber == 1)
            {
                Drawing drawing = new Drawing();
                drawing.Id = drawingsPart1.GetIdOfPart(imagePart1);
                sheet1.Worksheet.Append(drawing);
            }
            imagePart1.FeedData(imageStream);
           
            Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };

            Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
            Xdr.ColumnId columnId1 = new Xdr.ColumnId();
            columnId1.Text = startColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
            columnOffset1.Text = "38100";
            Xdr.RowId rowId1 = new Xdr.RowId();
            rowId1.Text = startRowIndex.ToString();
            Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
            rowOffset1.Text = "0";

            fromMarker1.Append(columnId1);
            fromMarker1.Append(columnOffset1);
            fromMarker1.Append(rowId1);
            fromMarker1.Append(rowOffset1);

            Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
            Xdr.ColumnId columnId2 = new Xdr.ColumnId();
            columnId2.Text = endColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
            columnOffset2.Text = "542925";
            Xdr.RowId rowId2 = new Xdr.RowId();
            rowId2.Text = endRowIndex.ToString();
            Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
            rowOffset2.Text = "161925";

            toMarker1.Append(columnId2);
            toMarker1.Append(columnOffset2);
            toMarker1.Append(rowId2);
            toMarker1.Append(rowOffset2);

            Xdr.Picture picture1 = new Xdr.Picture();

            Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
            Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = new UInt32Value((uint)(1024 + imageNumber)), Name = "Picture " + imageNumber.ToString() };

            Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
            A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };

            nonVisualPictureDrawingProperties1.Append(pictureLocks1);

            nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
            nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);

            Xdr.BlipFill blipFill1 = new Xdr.BlipFill();

            A.Blip blip1 = new A.Blip() { Embed = drawingsPart1.GetIdOfPart(imagePart1), CompressionState = A.BlipCompressionValues.Print };
            blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();

            A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };

            A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
            useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");

            blipExtension1.Append(useLocalDpi1);

            blipExtensionList1.Append(blipExtension1);

            blip1.Append(blipExtensionList1);

            A.Stretch stretch1 = new A.Stretch();
            A.FillRectangle fillRectangle1 = new A.FillRectangle();

            stretch1.Append(fillRectangle1);

            blipFill1.Append(blip1);
            blipFill1.Append(stretch1);

            Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();

            A.Transform2D transform2D1 = new A.Transform2D();
            A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
            A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };

            transform2D1.Append(offset1);
            transform2D1.Append(extents1);

            A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
            A.AdjustValueList adjustValueList1 = new A.AdjustValueList();

            presetGeometry1.Append(adjustValueList1);

            shapeProperties1.Append(transform2D1);
            shapeProperties1.Append(presetGeometry1);

            picture1.Append(nonVisualPictureProperties1);
            picture1.Append(blipFill1);
            picture1.Append(shapeProperties1);
            Xdr.ClientData clientData1 = new Xdr.ClientData();

            twoCellAnchor1.Append(fromMarker1);
            twoCellAnchor1.Append(toMarker1);
            twoCellAnchor1.Append(picture1);
            twoCellAnchor1.Append(clientData1);

            worksheetDrawing1.Append(twoCellAnchor1);

            if (imageNumber == 1)
                drawingsPart1.WorksheetDrawing = worksheetDrawing1;

           
        }
        #region Helper methods
        /// <summary>
        /// Returns the WorksheetPart for the specified sheet name
        /// </summary>
        /// <param name="workbookpart">The WorkbookPart</param>
        /// <param name="sheetName">The name of the worksheet</param>
        /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
        private static WorksheetPart GetSheetByName(WorkbookPart workbookpart, string sheetName)
        {
            foreach (WorksheetPart sheetPart in workbookpart.WorksheetParts)
            {
                string uri = sheetPart.Uri.ToString();
                if (uri.EndsWith(sheetName + ".xml"))
                    return sheetPart;
            }
            return null;
        }
        #endregion Helper methods
    }
}

*********************************************************************************

In this code, I use a template MS excel file, not generate MS Excel from scratch.

Sample file after generated: Click here to download

OpenXML how to copy cells with Validation

It seems that blank cells with data validation (no value, only data validation) are not retaining the data validation after being copied to other place (e.g. another worksheet). As soon as I try to copy a cell with some value + data validation, everything gets copied correctly.

Scenario: 

If don't choose any value in the drop down list, when you copy the cell or the row to other place using code, the validation will not retain.
What I have done is put a space on top the values list (named range, refer to previous post for how to add named range, and how to put drop down list validation), and in my template, I choose the space value in it. and then when I copy the cell to other place, the validation is retained.

My Partial Code:

                var wb = new XLWorkbook(exportDest);
                var wsData = wb.Worksheet("Data");
                wb.NamedRange("CorpGrade").SetRefersTo("Data!$B$3:$B$15");
                wb.NamedRange("Category").SetRefersTo("Data!$D$3:$D$10");
                var ws = wb.Worksheet(2);
                int i = startRow;
                while (i < 100)
                {
                    ws.Row(i).InsertRowsAbove(1);
                    var row = ws.Row(i);
                    ws.Row(i + 1).CopyTo(row);
                    //var range = ws.Range(i + 1, 1, i + 1, 50).CopyTo(row.Cell(1));
                    i++;
                }

                wb.Save();

MS Excel Validation Drop Down List Values From Named Range

1. How to Add Named Range, CTRL + F3, click here to see the video

2. Add Validation for cells, click here to see the guide video.

Open XML - How to add a watermark to a docx document

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Vml;
using DocumentFormat.OpenXml.Vml.Office;
using DocumentFormat.OpenXml.Vml.Wordprocessing;
using DocumentFormat.OpenXml.Wordprocessing;
using HorizontalAnchorValues = DocumentFormat.OpenXml.Vml.Wordprocessing.HorizontalAnchorValues;
using Lock = DocumentFormat.OpenXml.Vml.Office.Lock;
using VerticalAnchorValues = DocumentFormat.OpenXml.Vml.Wordprocessing.VerticalAnchorValues;
using System.IO;

namespace BTGWordProcess
{
    public class WordWaterMark
    {
        static Header MakeHeader()
        {
            var header = new Header();
            var paragraph = new Paragraph();
            var run = new Run();
            var text = new Text();
            text.Text = "";
            run.Append(text);
            paragraph.Append(run);
            header.Append(paragraph);
            return header;
        }

        public static void Process(string fileName)
        {
            byte[] sourceBytes = File.ReadAllBytes(fileName);

            MemoryStream inMemoryStream = new MemoryStream();
            inMemoryStream.Write(sourceBytes, 0, (int)sourceBytes.Length);

            var doc = WordprocessingDocument.Open(inMemoryStream, true);
            AddWatermark(doc);
            doc.MainDocumentPart.Document.Save();

            doc.Close();
            doc.Dispose();
            doc = null;

            using (FileStream fileStream = new FileStream(fileName, System.IO.FileMode.Create))
            {
                inMemoryStream.WriteTo(fileStream);
            }

            inMemoryStream.Close();
            inMemoryStream.Dispose();
            inMemoryStream = null;
        }

        public static void AddWatermark(WordprocessingDocument doc)
        {
            if (doc.MainDocumentPart.HeaderParts.Count() == 0)
            {
                doc.MainDocumentPart.DeleteParts(doc.MainDocumentPart.HeaderParts);
                var newHeaderPart = doc.MainDocumentPart.AddNewPart<HeaderPart>();
                var rId = doc.MainDocumentPart.GetIdOfPart(newHeaderPart);
                var headerRef = new HeaderReference();
                headerRef.Id = rId;
                var sectionProps = doc.MainDocumentPart.Document.Body.Elements<SectionProperties>().LastOrDefault();
                if (sectionProps == null)
                {
                    sectionProps = new SectionProperties();
                    doc.MainDocumentPart.Document.Body.Append(sectionProps);
                }
                sectionProps.RemoveAllChildren<HeaderReference>();
                sectionProps.Append(headerRef);

                newHeaderPart.Header = MakeHeader();
                newHeaderPart.Header.Save();
            }

            foreach (HeaderPart headerPart in doc.MainDocumentPart.HeaderParts)
            {
                var sdtBlock1 = new SdtBlock();
                var sdtProperties1 = new SdtProperties();
                var sdtId1 = new SdtId() { Val = 87908844 };
               
                //var sdtContentDocPartObject1 = new DocPartObjectSdt();
                var sdtContentDocPartObject1 = new SdtContentDocPartObject();
                var docPartGallery1 = new DocPartGallery() { Val = "Watermarks" };
                var docPartUnique1 = new DocPartUnique();
                sdtContentDocPartObject1.Append(docPartGallery1);
                sdtContentDocPartObject1.Append(docPartUnique1);
                sdtProperties1.Append(sdtId1);
                sdtProperties1.Append(sdtContentDocPartObject1);

                var sdtContentBlock1 = new SdtContentBlock();
                var paragraph2 = new Paragraph()
                                     {
                                         RsidParagraphAddition = "00656E18",
                                         RsidRunAdditionDefault = "00656E18"
                                     };
                var paragraphProperties2 = new ParagraphProperties();
                var paragraphStyleId2 = new ParagraphStyleId() { Val = "Header" };
                paragraphProperties2.Append(paragraphStyleId2);
                var run1 = new Run();
                var runProperties1 = new RunProperties();
                var noProof1 = new NoProof();
                var languages1 = new Languages() { EastAsia = "zh-TW" };
                runProperties1.Append(noProof1);
                runProperties1.Append(languages1);
                var picture1 = new Picture();
                var shapetype1 = new Shapetype()
                                     {
                                         Id = "_x0000_t136",
                                         CoordinateSize = "21600,21600",
                                         OptionalNumber = 136,
                                         Adjustment = "10800",
                                         EdgePath = "m@7,l@8,m@5,21600l@6,21600e"
                                     };
                var formulas1 = new Formulas();
                var formula1 = new Formula() { Equation = "sum #0 0 10800" };
                var formula2 = new Formula() { Equation = "prod #0 2 1" };
                var formula3 = new Formula() { Equation = "sum 21600 0 @1" };
                var formula4 = new Formula() { Equation = "sum 0 0 @2" };
                var formula5 = new Formula() { Equation = "sum 21600 0 @3" };
                var formula6 = new Formula() { Equation = "if @0 @3 0" };
                var formula7 = new Formula() { Equation = "if @0 21600 @1" };
                var formula8 = new Formula() { Equation = "if @0 0 @2" };
                var formula9 = new Formula() { Equation = "if @0 @4 21600" };
                var formula10 = new Formula() { Equation = "mid @5 @6" };
                var formula11 = new Formula() { Equation = "mid @8 @5" };
                var formula12 = new Formula() { Equation = "mid @7 @8" };
                var formula13 = new Formula() { Equation = "mid @6 @7" };
                var formula14 = new Formula() { Equation = "sum @6 0 @5" };

                formulas1.Append(formula1);
                formulas1.Append(formula2);
                formulas1.Append(formula3);
                formulas1.Append(formula4);
                formulas1.Append(formula5);
                formulas1.Append(formula6);
                formulas1.Append(formula7);
                formulas1.Append(formula8);
                formulas1.Append(formula9);
                formulas1.Append(formula10);
                formulas1.Append(formula11);
                formulas1.Append(formula12);
                formulas1.Append(formula13);
                formulas1.Append(formula14);
                var path1 = new DocumentFormat.OpenXml.Vml.Path()
                {
                    AllowTextPath = true,
                    ConnectionPointType = ConnectValues.Custom,
                    ConnectionPoints = "@9,0;@10,10800;@11,21600;@12,10800",
                    ConnectAngles = "270,180,90,0"
                };
                var textPath1 = new TextPath()
                {
                    On = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true),
                    FitShape = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true)
                };
                var shapeHandles1 = new ShapeHandles();

                var shapeHandle1 = new ShapeHandle()
                                       {
                                           Position = "#0,bottomRight",
                                           XRange = "6629,14971"
                                       };

                shapeHandles1.Append(shapeHandle1);

                var lock1 = new Lock
                {
                    Extension = ExtensionHandlingBehaviorValues.Edit,
                    TextLock = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true),
                    ShapeType = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true)
                };
               
                shapetype1.Append(formulas1);
                shapetype1.Append(path1);
                shapetype1.Append(textPath1);
                shapetype1.Append(shapeHandles1);
                shapetype1.Append(lock1);
                var shape1 = new Shape()
                                 {
                                     Id = "PowerPlusWaterMarkObject357476642",
                                     Style = "position:absolute;left:0;text-align:left;margin-left:0;margin-top:0;width:527.85pt;height:131.95pt;rotation:315;z-index:-251656192;mso-position-horizontal:center;mso-position-horizontal-relative:margin;mso-position-vertical:center;mso-position-vertical-relative:margin",
                                     OptionalString = "_x0000_s2049",
                                     AllowInCell = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true),
                                     FillColor = "silver",
                                     Stroked = DocumentFormat.OpenXml.TrueFalseValue.FromBoolean(true),
                                     Type = "#_x0000_t136"
                                 };


                var fill1 = new Fill() { Opacity = ".5" };
                TextPath textPath2 = new TextPath()
                                         {
                                             Style = "font-family:\"Calibri\";font-size:1pt",
                                             String = "DRAFT"
                                         };

                var textWrap1 = new TextWrap()
                                    {
                                        AnchorX = HorizontalAnchorValues.Margin,
                                        AnchorY = VerticalAnchorValues.Margin
                                    };

                shape1.Append(fill1);
                shape1.Append(textPath2);
                shape1.Append(textWrap1);
                picture1.Append(shapetype1);
                picture1.Append(shape1);
                run1.Append(runProperties1);
                run1.Append(picture1);
                paragraph2.Append(paragraphProperties2);
                paragraph2.Append(run1);
                sdtContentBlock1.Append(paragraph2);
                sdtBlock1.Append(sdtProperties1);
                sdtBlock1.Append(sdtContentBlock1);
                headerPart.Header.Append(sdtBlock1);
                headerPart.Header.Save();
            }
        }
    }
}

Word 2007: Create Custom Properties, Insert Custom Properties

Create Custom Properties:

Insert Custom Properties to Document:

Arrange MS Excel to Fit one Page