public void AbsoluteAnchor()
{
using (Workbook wb = editor.LoadWorkbook("../../TestFiles/ChartsSheets.xlsx"))
{
Assert.NotNull(wb);
Chartsheet chartsheet1 = wb.Sheets["Диаграмма1"] as Chartsheet;
Chart chart1 = chartsheet1.Chart;
Assert.NotNull(chart1);
AbsoluteAnchor anchor1 = chart1.AbsoluteAnchor;
Assert.NotNull(anchor1);
Assert.Equals(9303058 / 12700.0, anchor1.Width, 1e-13);
Assert.Equals(6075655 / 12700.0, anchor1.Height, 1e-13);
Assert.Equals(0, anchor1.PosX, 1e-13);
Assert.Equals(0, anchor1.PosY, 1e-13);
Chartsheet chartsheet2 = wb.Sheets["Диаграмма2"] as Chartsheet;
Chart chart2 = chartsheet2.Chart;
Assert.NotNull(chart2);
AbsoluteAnchor anchor2 = chart2.AbsoluteAnchor;
Assert.NotNull(anchor2);
Assert.Equals(8720461 / 12700.0, anchor2.Width, 1e-13);
Assert.Equals(3153423 / 12700.0, anchor2.Height, 1e-13);
Assert.Equals(545606 / 12700.0, anchor2.PosX, 1e-13);
Assert.Equals(360655 / 12700.0, anchor2.PosY, 1e-13);
}
}
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);
}
