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);
}
public static Wp.Drawing GetAnchorPicture(String imagePartId, uint width = 1500, uint height = 1500, uint horizontalOffset = 0, uint verticalOffset = 0, String pictureName = "Picture")
{
Wp.Drawing _drawing = new Wp.Drawing();
DWp.Anchor _anchor = new DWp.Anchor()
{
DistanceFromTop = (OXML.UInt32Value) 0U,
DistanceFromBottom = (OXML.UInt32Value) 0U,
DistanceFromLeft = (OXML.UInt32Value) 0U,
DistanceFromRight = (OXML.UInt32Value) 0U,
SimplePos = false,
RelativeHeight = (OXML.UInt32Value) 0U,
BehindDoc = true,
Locked = false,
LayoutInCell = true,
AllowOverlap = true,
EditId = "44CEF5E4",
AnchorId = "44803ED1"
};
DWp.SimplePosition _spos = new DWp.SimplePosition()
{
X = 0L,
Y = 0L
};
DWp.HorizontalPosition _hp = new DWp.HorizontalPosition()
{
RelativeFrom = DWp.HorizontalRelativePositionValues.Column
};
DWp.PositionOffset _hPO = new DWp.PositionOffset();
_hPO.Text = horizontalOffset.ToString();
_hp.Append(_hPO);
DWp.VerticalPosition _vp = new DWp.VerticalPosition()
{
RelativeFrom = DWp.VerticalRelativePositionValues.Paragraph
};
DWp.PositionOffset _vPO = new DWp.PositionOffset();
_vPO.Text = verticalOffset.ToString();
_vp.Append(_vPO);
DWp.Extent _e = new DWp.Extent()
{
Cx = height,
Cy = width
};
DWp.EffectExtent _ee = new DWp.EffectExtent()
{
LeftEdge = 0L,
TopEdge = 0L,
RightEdge = 0L,
BottomEdge = 0L
};
DWp.WrapTight _wp = new DWp.WrapTight()
{
WrapText = DWp.WrapTextValues.BothSides
};
DWp.WrapPolygon _wpp = new DWp.WrapPolygon()
{
Edited = false
};
DWp.StartPoint _sp = new DWp.StartPoint()
{
X = 0L,
Y = 0L
};
DWp.LineTo _l1 = new DWp.LineTo()
{
X = 0L, Y = 0L
};
DWp.LineTo _l2 = new DWp.LineTo()
{
X = 0L, Y = 0L
};
DWp.LineTo _l3 = new DWp.LineTo()
{
X = 0L, Y = 0L
};
DWp.LineTo _l4 = new DWp.LineTo()
{
X = 0L, Y = 0L
};
_wpp.Append(_sp);
_wpp.Append(_l1);
_wpp.Append(_l2);
_wpp.Append(_l3);
_wpp.Append(_l4);
_wp.Append(_wpp);
DWp.DocProperties _dp = new DWp.DocProperties()
{
Id = 1U,
Name = pictureName
};
OXML.Drawing.Graphic _g = new OXML.Drawing.Graphic();
OXML.Drawing.GraphicData _gd = new OXML.Drawing.GraphicData()
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/picture"
};
OXML.Drawing.Pictures.Picture _pic = new OXML.Drawing.Pictures.Picture();
OXML.Drawing.Pictures.NonVisualPictureProperties _nvpp = new OXML.Drawing.Pictures.NonVisualPictureProperties();
OXML.Drawing.Pictures.NonVisualDrawingProperties _nvdp = new OXML.Drawing.Pictures.NonVisualDrawingProperties()
{
Id = 0,
Name = pictureName
};
OXML.Drawing.Pictures.NonVisualPictureDrawingProperties _nvpdp = new OXML.Drawing.Pictures.NonVisualPictureDrawingProperties();
_nvpp.Append(_nvdp);
_nvpp.Append(_nvpdp);
OXML.Drawing.Pictures.BlipFill _bf = new OXML.Drawing.Pictures.BlipFill();
OXML.Drawing.Blip _b = new OXML.Drawing.Blip()
{
Embed = imagePartId,
CompressionState = OXML.Drawing.BlipCompressionValues.Print
};
_bf.Append(_b);
OXML.Drawing.Stretch _str = new OXML.Drawing.Stretch();
OXML.Drawing.FillRectangle _fr = new OXML.Drawing.FillRectangle();
_str.Append(_fr);
_bf.Append(_str);
OXML.Drawing.Pictures.ShapeProperties _shp = new OXML.Drawing.Pictures.ShapeProperties();
OXML.Drawing.Transform2D _t2d = new OXML.Drawing.Transform2D();
OXML.Drawing.Offset _os = new OXML.Drawing.Offset()
{
X = 0L,
Y = 0L
};
OXML.Drawing.Extents _ex = new OXML.Drawing.Extents()
{
Cx = 989965L,
Cy = 791845L
};
_t2d.Append(_os);
_t2d.Append(_ex);
OXML.Drawing.PresetGeometry _preGeo = new OXML.Drawing.PresetGeometry()
{
Preset = OXML.Drawing.ShapeTypeValues.Rectangle
};
OXML.Drawing.AdjustValueList _adl = new OXML.Drawing.AdjustValueList();
_preGeo.Append(_adl);
_shp.Append(_t2d);
_shp.Append(_preGeo);
_pic.Append(_nvpp);
_pic.Append(_bf);
_pic.Append(_shp);
_gd.Append(_pic);
_g.Append(_gd);
_anchor.Append(_spos);
_anchor.Append(_hp);
_anchor.Append(_vp);
_anchor.Append(_e);
_anchor.Append(_ee);
_anchor.Append(_wp);
_anchor.Append(_dp);
_anchor.Append(_g);
_drawing.Append(_anchor);
return(_drawing);
}
/// <summary>
/// Add the logo of the system
/// </summary>
/// <param name="logoPath">Path of the logo</param>
/// <param name="worksheetPart">Worksheet Part</param>
/// <returns>Drawing</returns>
private static Drawing AddLogo(string logoPath, WorksheetPart worksheetPart)
{
string sImagePath = logoPath;
DrawingsPart dp = worksheetPart.AddNewPart<DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, worksheetPart.GetIdOfPart(dp));
using (FileStream fs = new FileStream(sImagePath, FileMode.Open, FileAccess.Read))
{
imgp.FeedData(fs);
}
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "logo";
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.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();
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;
DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.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;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();
pos.X = 18 * 914400 / 72;
pos.Y = 28 * 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());
WorksheetDrawing wsd = new WorksheetDrawing();
wsd.Append(anchor);
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
return drawing;
}
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
}
}
/// <summary>
/// Inserts the qr code.
/// </summary>
/// <param name="pay">The pay.</param>
/// <param name="dataSheetData">The data sheet data.</param>
/// <param name="baseRowIndex">Index of the base row.</param>
private void InsertQrCode(Pay pay, SheetData dataSheetData, int baseRowIndex)
{
// real image size in cm
const double realWidth = 5.98D;
const double realHeight = 5.17D;
// get the worksheet
var worksheet = ((Worksheet)dataSheetData.Parent);
// get the parts
var worksheetPart = worksheet.WorksheetPart;
var drawingsPart = worksheetPart.DrawingsPart ?? worksheetPart.AddNewPart <DocumentFormat.OpenXml.Packaging.DrawingsPart>();
var imagePart = drawingsPart.AddImagePart(DocumentFormat.OpenXml.Packaging.ImagePartType.Png);
// get the DrawingsPart/WorksheetDrawing
var worksheetDrawing = drawingsPart.WorksheetDrawing;
if (worksheetDrawing == null)
{
worksheetDrawing = drawingsPart.WorksheetDrawing = new DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing();
}
// get the WorkSheet/Drawing
var drawing = worksheet.Elements <Drawing>().FirstOrDefault();
if (drawing == null)
{
drawing = new Drawing
{
Id = worksheetPart.GetIdOfPart(drawingsPart),
};
worksheet.AppendChild(drawing);
}
// image size in EMUs
int imageWidth;
int imageHeight;
// get the qr image and put it into image part
var qrString = this.BySquareEncoder.Encode(pay);
using (var qrBitmap = this.ImageManipulator.CreateQrCodeWithLogo(qrString))
{
var qrImageData = this.ImageManipulator.GetImageData(qrBitmap);
using (var imageStream = new System.IO.MemoryStream(qrImageData))
{
imagePart.FeedData(imageStream);
}
// determine image size in EMUs
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
// var imageWidth = (long) bm.Width * (long) ((float) 914400 / bm.HorizontalResolution);
// var imageHeight = (long) bm.Height * (long) ((float) 914400 / bm.VerticalResolution);
imageWidth = (int)(realWidth * 360000);
imageHeight = (int)(realHeight * 360000);
}
// create the Drawing.Spreadsheet.NonVisualDrawingProperties
var nvpId = 1 + (worksheetDrawing
.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties>()
.Select(p => p.Id.Value)
.DefaultIfEmpty()
.Max()
);
var nonVisualPictureProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties
{
NonVisualDrawingProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties
{
Id = nvpId,
Name = $"Picture {nvpId}",
Description = pay.Payments[0].PaymentNote,
},
NonVisualPictureDrawingProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties
{
PictureLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks
{
NoChangeAspect = true,
NoChangeArrowheads = true,
}
},
};
// create the BlipFill
var blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill
{
Blip = new DocumentFormat.OpenXml.Drawing.Blip
{
Embed = drawingsPart.GetIdOfPart(imagePart),
CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print,
},
SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle()
};
var stretch = new DocumentFormat.OpenXml.Drawing.Stretch
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
blipFill.AppendChild(stretch);
// create the ShapeProperties
var shapeProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties
{
BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto,
Transform2D = new DocumentFormat.OpenXml.Drawing.Transform2D
{
Offset = new DocumentFormat.OpenXml.Drawing.Offset
{
X = 0,
Y = 0,
},
Extents = new DocumentFormat.OpenXml.Drawing.Extents
{
Cx = imageWidth,
Cy = imageHeight,
},
},
};
var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle,
AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList(),
};
shapeProperties.AppendChild(prstGeom);
shapeProperties.AppendChild(new DocumentFormat.OpenXml.Drawing.NoFill());
// create the Picture
var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
{
NonVisualPictureProperties = nonVisualPictureProperties,
BlipFill = blipFill,
ShapeProperties = shapeProperties,
};
// create the OneCellAnchor
var oneCellAnchor = new DocumentFormat.OpenXml.Drawing.Spreadsheet.OneCellAnchor
{
FromMarker = new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker
{
// anchor it at 10th column
ColumnId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnId(('K' - 'A').ToString()),
// anchor it at baseRowIndex row
RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((baseRowIndex + 1).ToString()),
ColumnOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnOffset("0"),
RowOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset("0"),
},
Extent = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Extent
{
Cx = imageWidth,
Cy = imageHeight,
},
};
oneCellAnchor.AppendChild(picture);
oneCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData());
// add the anchored image to WorksheetDrawing
worksheetDrawing.AppendChild(oneCellAnchor);
}
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());
}
}
private static Picture AddPicture(Slide slide, Shape referingShape, string imageFile)
{
Picture picture = new Picture();
string embedId = string.Empty;
DocumentFormat.OpenXml.UInt32Value picId = 10001U;
string name = string.Empty;
if (slide.Elements <Picture>().Count() > 0)
{
picId = ++slide.Elements <Picture>().ToList().Last().NonVisualPictureProperties.NonVisualDrawingProperties.Id;
}
name = "image" + picId.ToString();
embedId = "rId" + (slide.Elements <Picture>().Count() + 915).ToString(); // some value
NonVisualPictureProperties nonVisualPictureProperties = new NonVisualPictureProperties()
{
NonVisualDrawingProperties = new NonVisualDrawingProperties()
{
Name = name, Id = picId, Title = name
},
NonVisualPictureDrawingProperties = new NonVisualPictureDrawingProperties()
{
PictureLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks()
{
NoChangeAspect = true
}
},
ApplicationNonVisualDrawingProperties = new ApplicationNonVisualDrawingProperties()
{
UserDrawn = true
}
};
BlipFill blipFill = new BlipFill()
{
Blip = new DocumentFormat.OpenXml.Drawing.Blip()
{
Embed = embedId
}
};
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch()
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
blipFill.Append(stretch);
ShapeProperties shapeProperties = new ShapeProperties()
{
Transform2D = new DocumentFormat.OpenXml.Drawing.Transform2D()
{
//MMS:4
Offset = new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 1565275, Y = 612775
}, // { X = 457200L, Y = 1124000L }
Extents = new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 5486400, Cy = 4114800
} //{ Cx = 8229600L, Cy = 5029200L }
}
//<a:off x="1565275" y="612775" />
//<a:ext cx="5486400" cy="4114800" />
};
DocumentFormat.OpenXml.Drawing.PresetGeometry presetGeometry = new DocumentFormat.OpenXml.Drawing.PresetGeometry()
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
};
DocumentFormat.OpenXml.Drawing.AdjustValueList adjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
presetGeometry.Append(adjustValueList);
shapeProperties.Append(presetGeometry);
picture.Append(nonVisualPictureProperties);
picture.Append(blipFill);
picture.Append(shapeProperties);
slide.CommonSlideData.ShapeTree.Append(picture);
// Add Image part
AddImagePart(slide, embedId, imageFile);
slide.Save();
return(picture);
}
// -----------------------------------------------------------------------------------------
// 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);
}
