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.