Help us understand the problem. What is going on with this article?

C# で Open XML SDK を使ってエクセルに画像を貼り付ける

More than 1 year has passed since last update.

Open XML SDK を使って、エクセルに画像を貼り付けてみました。

環境

Windows 10
Visual Studio 2015
Open XML SDK 2.7.2
.Net Framework 4.5.2

参照の設定

DocumentFormat.OpenXml

  • ソリューション エクスプローラーで、[参照] を右クリックし、[NuGetパッケージの管理] をクリックします。
  • 「DocumentFormat.OpenXml」を検索し、「DocumentFormat.OpenXml」のNuGetパッケージをインストールします。

参照-1.PNG

  • NuGetパッケージマネージャーのコンソールでインストールする場合は、下記のようにします。
PM> Install-Package DocumentFormat.OpenXml -Version 2.7.2

(参考)
https://www.nuget.org/packages/DocumentFormat.OpenXml

WindowsBase

  • ソリューション エクスプローラーで、[参照] を右クリックし、[参照の追加] をクリックします。
  • 「WindowsBase」を追加します。

参照-2.PNG

エクセルに画像を貼り付ける

Program.cs
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Drawing;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace ExcelImagePaste
{
    class Program
    {
        public static Worksheet InsertWorksheet( SpreadsheetDocument document, string sheetName = "" )
        {
            if( document.WorkbookPart == null )
            {
                WorkbookPart wbpart = document.AddWorkbookPart();
                wbpart.Workbook = new Workbook();
                wbpart.Workbook.AppendChild<Sheets>( new Sheets() );
            }

            // Add a blank WorksheetPart.
            WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet( new SheetData() );

            Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = document.WorkbookPart.GetIdOfPart( newWorksheetPart );

            // Get a unique ID for the new worksheet.
            uint sheetId = 1;
            if( sheets.Elements<Sheet>().Count() > 0 )
            {
                sheetId = sheets.Elements<Sheet>().Select( s => s.SheetId.Value ).Max() + 1;
            }

            // Give the new worksheet a name.
            if( sheetName.Length == 0 )
            {
                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 );

            return newWorksheetPart.Worksheet;
        }

        public static bool InsertImage( Worksheet ws, long x, long y, string sImagePath, string sImageName = "" )
        {
            try
            {
                WorksheetPart wsp = ws.WorksheetPart;
                DrawingsPart dp;
                ImagePart imgp;
                WorksheetDrawing wsd;

                ImagePartType ipt;
                switch( sImagePath.Substring( sImagePath.LastIndexOf( '.' ) + 1 ).ToLower() )
                {
                case "png":
                    ipt = ImagePartType.Png;
                    break;
                case "jpg":
                case "jpeg":
                    ipt = ImagePartType.Jpeg;
                    break;
                case "gif":
                    ipt = ImagePartType.Gif;
                    break;
                default:
                    return false;
                }

                if( wsp.DrawingsPart == null )
                {
                    //----- no drawing part exists, add a new one
                    dp = wsp.AddNewPart<DrawingsPart>();
                    imgp = dp.AddImagePart( ipt, wsp.GetIdOfPart( dp ) );
                    wsd = new WorksheetDrawing();
                }
                else
                {
                    //----- use existing drawing part
                    dp = wsp.DrawingsPart;
                    imgp = dp.AddImagePart( ipt );
                    dp.CreateRelationshipToPart( imgp );
                    wsd = dp.WorksheetDrawing;
                }

                using( FileStream fs = new FileStream( sImagePath, FileMode.Open ) )
                {
                    imgp.FeedData( fs );
                }

                int imageNumber = dp.ImageParts.Count<ImagePart>();
                if( imageNumber == 1 )
                {
                    Drawing drawing = new Drawing();
                    drawing.Id = dp.GetIdOfPart( imgp );
                    ws.Append( drawing );
                }

                NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
                nvdp.Id = new UInt32Value( (uint)( 1024 + imageNumber ) );

                if( sImageName.Length == 0 )
                {
                    nvdp.Name = "Picture " + imageNumber.ToString();
                }
                else
                {
                    nvdp.Name = sImageName;
                }

                nvdp.Description = "";
                DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                picLocks.NoChangeAspect = true;
                picLocks.NoChangeArrowheads = true;
                NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
                nvpdp.PictureLocks = picLocks;
                NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
                nvpp.NonVisualDrawingProperties = nvdp;
                nvpp.NonVisualPictureDrawingProperties = nvpdp;

                DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

                BlipFill blipFill = new BlipFill();
                DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                blip.Embed = dp.GetIdOfPart( imgp );
                blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                blipFill.Blip = blip;
                blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                blipFill.Append( stretch );

                DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                offset.X = 0;
                offset.Y = 0;
                t2d.Offset = offset;

                DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

                using( Stream imageStream = imgp.GetStream() )
                {
                    Bitmap bm = new Bitmap( imageStream );
                    extents.Cx = (long)bm.Width * (long)( (float)914400 / bm.HorizontalResolution );
                    extents.Cy = (long)bm.Height * (long)( (float)914400 / bm.VerticalResolution );
                    bm.Dispose();
                }

                t2d.Extents = extents;
                ShapeProperties sp = new ShapeProperties();
                sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                sp.Transform2D = t2d;
                DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                sp.Append( prstGeom );
                sp.Append( new DocumentFormat.OpenXml.Drawing.NoFill() );

                DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
                picture.NonVisualPictureProperties = nvpp;
                picture.BlipFill = blipFill;
                picture.ShapeProperties = sp;

                Position pos = new Position();
                pos.X = x;
                pos.Y = y;
                Extent ext = new Extent();
                ext.Cx = extents.Cx;
                ext.Cy = extents.Cy;
                AbsoluteAnchor anchor = new AbsoluteAnchor();
                anchor.Position = pos;
                anchor.Extent = ext;
                anchor.Append( picture );
                anchor.Append( new ClientData() );
                wsd.Append( anchor );
                wsd.Save( dp );
            }
            catch( Exception ex )
            {
                Console.WriteLine( ex.ToString() );
                return false;
            }

            return true;
        }

        static void Main( string[] args )
        {
            string path, fname;

            //パス名(エクセルファイルの保存パス、画像ファイルのパス)
            path = @"D:\data";

            // 新しいドキュメントの作成
            fname = System.IO.Path.Combine( path, "test.xlsx" );
            SpreadsheetDocument document = SpreadsheetDocument.Create( fname, SpreadsheetDocumentType.Workbook, true );

            //シート1の追加
            Worksheet worksheet1 = InsertWorksheet( document );

            //シート1に画像の貼り付け
            fname = System.IO.Path.Combine( path, "image1.jpg" );
            if( !InsertImage( worksheet1, 0, 0, fname ) )
                return;

            //シート2の追加
            Worksheet worksheet2 = InsertWorksheet( document );

            //シート2に画像の貼り付け
            fname = System.IO.Path.Combine( path, "image2.jpg" );
            if( !InsertImage( worksheet2, 0, 0, fname ) )
                return;

            //ファイルの保存
            document.Close();
        }
    }
}

ソースコード

ソースコードは、次の場所からダウンロードでします。
https://github.com/itoru257/Excel-Image-Paste

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away