private void LoadImage(Worksheet ws, WorksheetPart wsp, WorkbookPart wp) { string path = System.IO.Path.Combine(Server.MapPath("~/Scripts/Images"), "unnamed.png").ToString(); DrawingsPart dp = wsp.AddNewPart <DrawingsPart>(); ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp)); using (FileStream fs = new FileStream(path, FileMode.Open)) { imgp.FeedData(fs); } NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "Picture 1"; nvdp.Description = "polymathlogo"; 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; System.Drawing.Bitmap bm = new System.Drawing.Bitmap(path); //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML //http://stackoverflow.com/questions/1341930/pixel-to-centimeter //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); 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 = 0; pos.Y = 0; 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()); WorksheetDrawing wsd = new WorksheetDrawing(); wsd.Append(anchor); Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); ws.Append(drawing); }
private void BuildWorkbook(string filename) { try { using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { var wbp = xl.AddWorkbookPart(); var wsp = wbp.AddNewPart <WorksheetPart>(); var wb = new Workbook(); var fv = new FileVersion { ApplicationName = "Microsoft Office Excel" }; var ws = new Worksheet(); var sd = new SheetData(); var wbsp = wbp.AddNewPart <WorkbookStylesPart>(); wbsp.Stylesheet = CreateStylesheet(); wbsp.Stylesheet.Save(); var sImagePath = "polymathlogo.png"; var dp = wsp.AddNewPart <DrawingsPart>(); var imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp)); using (FileStream fs = new FileStream(sImagePath, FileMode.Open)) { imgp.FeedData(fs); } var nvdp = new NonVisualDrawingProperties { Id = 1025, Name = "Picture 1", Description = "polymathlogo" }; var picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks { NoChangeAspect = true, NoChangeArrowheads = true }; var nvpdp = new NonVisualPictureDrawingProperties { PictureLocks = picLocks }; var nvpp = new NonVisualPictureProperties { NonVisualDrawingProperties = nvdp, NonVisualPictureDrawingProperties = nvpdp }; var stretch = new DocumentFormat.OpenXml.Drawing.Stretch { FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle() }; var blip = new DocumentFormat.OpenXml.Drawing.Blip { Embed = dp.GetIdOfPart(imgp), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print }; var blipFill = new BlipFill { Blip = blip, SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle() }; blipFill.Append(stretch); var offset = new DocumentFormat.OpenXml.Drawing.Offset { X = 0, Y = 0 }; var t2d = new DocumentFormat.OpenXml.Drawing.Transform2D { Offset = offset }; var bm = Xwt.Drawing.Image.FromFile(sImagePath).ToBitmap(); //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML //http://stackoverflow.com/questions/1341930/pixel-to-centimeter //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c var extents = new DocumentFormat.OpenXml.Drawing.Extents { Cx = (long)bm.Width * (long)((float)914400 / bm.PixelWidth), Cy = (long)bm.Height * (long)((float)914400 / bm.PixelHeight) }; bm.Dispose(); t2d.Extents = extents; var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry { Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle, AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList() }; var sp = new ShapeProperties { BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto, Transform2D = t2d }; sp.Append(prstGeom); sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill()); var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture { NonVisualPictureProperties = nvpp, BlipFill = blipFill, ShapeProperties = sp }; var pos = new Position { X = 0, Y = 0 }; Extent ext = new Extent { Cx = extents.Cx, Cy = extents.Cy }; var anchor = new AbsoluteAnchor { Position = pos, Extent = ext }; anchor.Append(picture); anchor.Append(new ClientData()); var wsd = new WorksheetDrawing(); wsd.Append(anchor); var drawing = new Drawing { Id = dp.GetIdOfPart(imgp) }; wsd.Save(dp); UInt32 index; Random rand = new Random(); sd.Append(CreateHeader(10)); sd.Append(CreateColumnHeader(11)); for (index = 12; index < 30; ++index) { sd.Append(CreateContent(index, ref rand)); } ws.Append(sd); ws.Append(drawing); wsp.Worksheet = ws; wsp.Worksheet.Save(); Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = wbp.GetIdOfPart(wsp) }; sheets.Append(sheet); wb.Append(fv); wb.Append(sheets); xl.WorkbookPart.Workbook = wb; xl.WorkbookPart.Workbook.Save(); xl.Close(); } } catch (Exception e) { Debug.WriteLine(e.ToString()); } }
public void InsertImage(long x, long y, long?width, long?height, string sImagePath) { try { WorksheetPart wsp = CurrentWorksheetPart; 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; } 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); CurrentWorksheetPart.Worksheet.Append(drawing); } NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties(); nvdp.Id = new UInt32Value((uint)(1024 + imageNumber)); nvdp.Name = "Picture " + imageNumber.ToString(); 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; Bitmap bm = new Bitmap(sImagePath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); if (width == null) { extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); } else { extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution); } if (height == null) { extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); } else { extents.Cy = 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 * 914400 / 72; pos.Y = y * 914400 / 72; 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) { throw ex; // or do something more interesting if you want } }
// ----------------------------------------------------------------------------------------- // http://polymathprogrammer.com/2010/11/10/how-to-insert-multiple-images-in-excel-open-xml/ // November 10, 2010 by Vincent Tan // ----------------------------------------------------------------------------------------- // Funcion to insert an .jpg image into an EXCEL worksheet private void InsertImage(WorksheetPart worksheetpart, string imagepath) { long xpos = 4000000; long ypos = 100000; Worksheet worksheet = worksheetpart.Worksheet; DrawingsPart dp; ImagePart imgp; WorksheetDrawing wsd; ImagePartType ipt = ImagePartType.Jpeg; // Create new or use existing Drawing part if (worksheetpart.DrawingsPart == null) { dp = worksheetpart.AddNewPart <DrawingsPart>(); imgp = dp.AddImagePart(ipt, worksheetpart.GetIdOfPart(dp)); wsd = new WorksheetDrawing(); } else { dp = worksheetpart.DrawingsPart; imgp = dp.AddImagePart(ipt); dp.CreateRelationshipToPart(imgp); wsd = dp.WorksheetDrawing; } using (FileStream fs = new FileStream(imagepath, FileMode.Open)) { imgp.FeedData(fs); } int imageNumber = dp.ImageParts.Count <ImagePart>(); if (imageNumber == 1) { Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); worksheet.Append(drawing); } NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties(); nvdp.Id = new UInt32Value((uint)(1024 + imageNumber)); nvdp.Name = "Picture " + imageNumber.ToString(); 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; Bitmap bm = new Bitmap(imagepath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); 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 = xpos; pos.Y = ypos; 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); }