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 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
}
}
public Drawing BuildDrawing(WorksheetPart worksheetPart, List <ExcelImage> excelImages)
{
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
var worksheetDrawing = new WorksheetDrawing();
var oneCellAnchors = new List <OneCellAnchor>();
foreach (var excelImage in excelImages)
{
var imagePart = drawingsPart.AddImagePart(excelImage.Type);
using (var stream = new MemoryStream(excelImage.ImageBytes))
{
imagePart.FeedData(stream);
}
long extentsCx, extentsCy;
using (var stream = new MemoryStream(excelImage.ImageBytes))
{
var bm = new Bitmap(stream);
extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
}
const int colOffset = 0;
const int rowOffset = 0;
var nvps = worksheetDrawing.Descendants <NonVisualDrawingProperties>();
var nvpId = nvps.Any() ?
(UInt32Value)worksheetDrawing.Descendants <NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 :
1U;
var oneCellAnchor = new OneCellAnchor(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker
{
ColumnId = new ColumnId((excelImage.ColNumber - 1).ToString()),
RowId = new RowId((excelImage.RowNumber - 1).ToString()),
ColumnOffset = new ColumnOffset(colOffset.ToString()),
RowOffset = new RowOffset(rowOffset.ToString())
},
new Extent {
Cx = extentsCx, Cy = extentsCy
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(
new NonVisualPictureProperties(
new NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId
},
new NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks {
NoChangeAspect = true
})
),
new BlipFill(
new DocumentFormat.OpenXml.Drawing.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
},
new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle())
),
new ShapeProperties(
new DocumentFormat.OpenXml.Drawing.Transform2D(
new DocumentFormat.OpenXml.Drawing.Offset {
X = 0, Y = 0
},
new DocumentFormat.OpenXml.Drawing.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new DocumentFormat.OpenXml.Drawing.PresetGeometry {
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
}
)
),
new ClientData()
);
oneCellAnchors.Add(oneCellAnchor);
}
using (var drawingsPartWriter = OpenXmlWriter.Create(drawingsPart))
{
drawingsPartWriter.WriteStartElement(worksheetDrawing);
foreach (var oneCellAnchor in oneCellAnchors)
{
drawingsPartWriter.WriteElement(oneCellAnchor);
}
drawingsPartWriter.WriteEndElement();
drawingsPartWriter.Close();
}
return(new Drawing
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
public void GetChart(string fileName)
{
string txt;
//string fileName = @"D:\c#file\excelfile\test1.xlsx";
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
//Console.WriteLine("*********"+ doc.WorkbookPart);
WorkbookPart bkPart = doc.WorkbookPart;
Workbook workbook = bkPart.Workbook;
//Console.WriteLine(workbook.Ancestors());
//Sheet s = workbook.Descendants<Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
//Sheet s = workbook.Descendants<Sheet>().Where(sht => sht.Name == "Razem").FirstOrDefault();
//IEnumerable s1 = workbook.Descendants<Sheet>();
//Console.WriteLine(workbook.Descendants<Sheet>());
Sheet s = workbook.Descendants <Sheet>().FirstOrDefault();
//Console.WriteLine(s);
//Console.WriteLine(s.Id);
//Console.WriteLine("$$$$$$$");
WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
DrawingsPart dp = (DrawingsPart)wsPart.DrawingsPart;
/*
* Console.WriteLine("$$$$$$$");
* Console.WriteLine(dp.ChartParts);
* Console.WriteLine("$$$$$$$");
* foreach (var chartPart in dp.ChartParts) {
* Console.WriteLine(chartPart.ChartColorStyleParts);
* Console.WriteLine("$$$$$$$");
* }*/
WorksheetDrawing dWs = dp.WorksheetDrawing;
Console.WriteLine("The count of the charts is : " + dWs.ChildElements.Count);
//Console.WriteLine(dWs.ChildElements[1]);
//Console.WriteLine(dWs);
Console.WriteLine("**********************");
//Console.WriteLine(dWs.Descendants());
txt = dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name;
Console.WriteLine("The name(not title) of the charts is : " + txt);
Console.WriteLine(dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().Count());
//Console.WriteLine(dWs.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().ChildElements + "############");
txt = dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().ElementAtOrDefault(0).Name;
//Console.WriteLine(txt );
Console.WriteLine("**********************");
//Console.WriteLine(dp.ChartParts.Count());
ChartPart cp = dp.ChartParts.FirstOrDefault();
Console.WriteLine("the chart space language is :" + cp.ChartSpace.EditingLanguage.Val);
Console.WriteLine("**********************");
//Console.WriteLine("the XXXXX is :" + cp.ChartSpace.RoundedCorners);
A.Charts.ChartShapeProperties cs = cp.ChartSpace.Descendants <A.Charts.ChartShapeProperties>().FirstOrDefault();
//Console.WriteLine("the property of the Charts :" );
//Console.WriteLine(cs.LocalName);
A.Charts.AxisDataSourceType adst = cp.ChartSpace.Descendants <A.Charts.AxisDataSourceType>().FirstOrDefault();
Console.Write("the reference of the catagory is :");
//Console.WriteLine(adst.StringReference.Formula.InnerText); // if there is a reference
Console.WriteLine("the reference of the Number is :");
//Console.WriteLine(adst.NumberReference.Formula.InnerText); // if there is a reference
Console.WriteLine("**********************");
A.Charts.CategoryAxis ca = cp.ChartSpace.Descendants <A.Charts.CategoryAxis>().FirstOrDefault();
Console.WriteLine("the title of the Category axix is :");
Console.WriteLine(ca.Title.InnerText);
A.Charts.ValueAxis va = cp.ChartSpace.Descendants <A.Charts.ValueAxis>().FirstOrDefault();
Console.WriteLine("the title of the Value axix is :");
Console.WriteLine(va.Title.InnerText);
A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants <A.Charts.Chart>().FirstOrDefault();
//Console.WriteLine(c.LocalName);
Console.WriteLine("**********************");
Console.WriteLine("the title of the chart is :" + c.Title.InnerText);
//Console.WriteLine( c.LocalName);
//Console.WriteLine("chart title is :" + c.Title.InnerXml);
//Console.WriteLine("chart title is :" + c.Title.ChartText.RichText.InnerText);
Console.WriteLine("**********************");
Console.WriteLine("the type of the chart is :" + c.PlotArea.ChildElements[1].LocalName.ToString());
Console.WriteLine("**********************");
Console.WriteLine("the other of the chart is :" + c.PlotArea.LocalName);
//Console.WriteLine("the type of the chart is :" + c.PlotArea.ChildElements[1].ChildElements.Count);
//Console.WriteLine(txt + "############");
//Console.WriteLine(c.PlotArea.ChildElements[2].LocalName.ToString() + "############");
//Console.WriteLine(c.PlotArea.ChildElements[3].LocalName.ToString());
//Console.WriteLine(c.PlotArea.ChildElements[4].LocalName.ToString());
//Console.WriteLine(c.PlotArea.ChildElements[5].LocalName.ToString());
Console.ReadKey();
//this.txtMessages.Text = txt;
}
}
private void PlaceImageOnCell(Worksheet worksheet, Bitmap image, int Col, int Row, double colWid, double rowHeight, string type, string imgName = "", string imgDesc = "")//, float? W, float? H)
{
Dictionary <string, ImagePartType> mimeToImagePartType = new Dictionary <string, ImagePartType>()
{
{ "image/bmp", ImagePartType.Bmp },
{ "image/gif", ImagePartType.Gif },
{ "image/jpg", ImagePartType.Jpeg },
{ "image/jpeg", ImagePartType.Jpeg },
{ "image/png", ImagePartType.Png },
{ "image/tiff", ImagePartType.Tiff }
};
try
{
ImagePart imagePart;
ImagePartType ip;
if (!mimeToImagePartType.TryGetValue(type, out ip))
{
ip = ImagePartType.Jpeg;
}
WorksheetDrawing wsd;
DrawingsPart dp;
WorksheetPart worksheetPart = worksheet.WorksheetPart;
double imgMargin = 5;
//string sImagePath = "D:/Documents/Source/Repos/DarrylSite/DarrylSite/Images/DC.png";
if (worksheetPart.DrawingsPart == null)
{
dp = worksheetPart.AddNewPart <DrawingsPart>();
imagePart = dp.AddImagePart(ip, worksheetPart.GetIdOfPart(dp));
wsd = new WorksheetDrawing();
}
else
{
dp = worksheetPart.DrawingsPart;
imagePart = dp.AddImagePart(ip);
dp.CreateRelationshipToPart(imagePart);
wsd = dp.WorksheetDrawing;
}
NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
nvdp.Id = GetNextImageId();
nvdp.Name = imgName;
nvdp.Description = imgDesc;
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(imagePart);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Email;
/*
* string outerXml =
* "<a:ext uri=\"{28A0092B-C50C-407E-A947-70E740481C1C}\">" +
* "<a14:useLocalDpi xmlns:a14 = \"http://schemas.microsoft.com/office/drawing/2010/main\"/>" +
* "</a:ext> ";
*
* string outerXml = "uri=\"{28A0092B-C50C-407E-A947-70E740481C1C}\">";
*/
A14.UseLocalDpi localDpi = new A14.UseLocalDpi();
/*
* ExtensionList extLst = new ExtensionList();
* Extension extsn = new Extension(localDpi);
* extsn.Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}";
* extLst.Append(extsn);
* blip.Append(extLst);
*/
A.BlipExtensionList blipExtLst = new A.BlipExtensionList();
A.BlipExtension blipExt = new A.BlipExtension()
{
Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}"
};
localDpi.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
blipExt.Append(localDpi);
blipExtLst.Append(blipExt);
blip.Append(blipExtLst);
blip.Append();
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);
//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.Cy = (long)image.Height * (long)((float)914400 / image.VerticalResolution);
//extents.Cy = (int)(rowHeight * 72 / 96 * (100 - (2 * imgMargin)) / 100) * 12700;
extents.Cy = (int)((rowHeight * 72 / 96 * 12700) * (((100 - (2 * imgMargin)) / 100)));
//extents.Cx = (int)(((((colWid - 1) * 7) + 12) * 12700) * (.5 * image.Height / image.Width));
//extents.Cx = (int)(((((colWid - 1) * 7) + 12) * 12700) * (.5 * extents.Cy / image.Width));
extents.Cx = image.Width * extents.Cy / image.Height;
;
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;
Xdr.FromMarker fromMarker = new Xdr.FromMarker();
Xdr.ToMarker toMarker = new Xdr.ToMarker();
// From
ColumnId columnId1 = new ColumnId();
columnId1.Text = Col.ToString();
ColumnOffset columnOffset1 = new ColumnOffset();
//columnOffset1.Text = "228600";
double colloff = ((((colWid - 1) * 7) + 12) * 12700) * 72 / 96;
colloff -= extents.Cx;
colloff /= 2;
colloff = (int)colloff;
columnOffset1.Text = colloff.ToString();
RowId rowId1 = new RowId();
rowId1.Text = Row.ToString();
RowOffset rowOffset1 = new RowOffset();
double rowoff = (int)((rowHeight * 72 / 96 * 12700) * (imgMargin / 100));
rowOffset1.Text = (rowoff).ToString();
fromMarker.Append(columnId1);
fromMarker.Append(columnOffset1);
fromMarker.Append(rowId1);
fromMarker.Append(rowOffset1);
// To
ColumnId columnId2 = new ColumnId();
ColumnOffset columnOffset2 = new ColumnOffset();
RowId rowId2 = new RowId();
RowOffset rowOffset2 = new RowOffset();
columnId2.Text = (Col).ToString();
columnOffset2.Text = "0";// "152381";
// Margin is accounted for, so take it off the width
columnOffset2.Text = (extents.Cx + colloff).ToString();
rowId2.Text = (Row).ToString();
rowOffset2.Text = "4572000"; // 4572000 = 12700 (#EMUs/pixel) * 45 (Olivia's cell height in Excel Cell units (aka 60 pixels)) * 8 (???)
rowOffset2.Text = (extents.Cy + rowoff).ToString(); //
toMarker.Append(columnId2);
toMarker.Append(columnOffset2);
toMarker.Append(rowId2);
toMarker.Append(rowOffset2);
//Position pos = new Position(); pos.X = Row; pos.Y = Col;
Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy;
TwoCellAnchor anchor = new TwoCellAnchor();
//OneCellAnchor anchor = new OneCellAnchor();
anchor.Append(fromMarker);
anchor.Append(toMarker);
//anchor.Extent = ext;
//anchor.Append(toMarker);
anchor.Append(picture);
anchor.Append(new ClientData());
wsd.Append(anchor);
{
MemoryStream imgStream = new MemoryStream();
ImageCodecInfo[] codecs = ImageCodecInfo.GetImageEncoders();
ImageCodecInfo codec = codecs.First(c => c.MimeType == type);
Guid imgGuid = codec.FormatID;
ImageFormat imf = new ImageFormat(imgGuid);
float h = image.HorizontalResolution;
float v = image.VerticalResolution;
Bitmap compressedImage = new Bitmap(64, 64);//, gr);
Rectangle rect = new Rectangle(0, 0, 64, 64);
Graphics gr = Graphics.FromImage(compressedImage);
gr.DrawImage(image, rect);
compressedImage.SetResolution(48, 48);
compressedImage.Save(imgStream, imf);
gr.Dispose();
compressedImage.Dispose();
imgStream.Position = 0;
imagePart.FeedData(imgStream);
imgStream.Dispose();
}
if (drawing == null)
{
drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imagePart);
worksheet.Append(drawing);
}
wsd.Save(dp);
worksheetPart.Worksheet.Save();
/* Sheets sheets = new Sheets();Sheet sheet = new Sheet();sheet.Name = "Sheet1";sheet.SheetId = 1;sheet.Id = wbp.GetIdOfPart(wsp);sheets.Append(sheet);wb.Append(sheets); */
}
catch (Exception e)
{
ModelState.AddModelError("Caught fatal exception placing image", e);
Trace.TraceError($"OJException: Could not place image {e.Message}");
}
}
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 void AddImage(Func <FileStream> fileStreamFunc, WorksheetPart worksheetPart)
{
if (fileStreamFunc == null)
{
return;
}
DrawingsPart drawingsPart;
ImagePart imagePart;
Extents extents;
using (var fileStream = fileStreamFunc())
{
if (fileStream == null)
{
return;
}
drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
imagePart = drawingsPart.AddImagePart(ImagePartType.Png, worksheetPart.GetIdOfPart(drawingsPart));
imagePart.FeedData(fileStream);
Image image = Image.FromStream(fileStream);
//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
extents = new Extents
{
Cx = (long)image.Width * (long)((float)914400 / image.HorizontalResolution),
Cy = (long)image.Height * (long)((float)914400 / image.VerticalResolution)
};
image.Dispose();
}
NonVisualPictureProperties pictureNonVisualPictureProperties =
new NonVisualPictureProperties
{
NonVisualDrawingProperties = new NonVisualDrawingProperties
{
Id = 1025,
Name = "Picture 1",
Description = "eventtree"
},
NonVisualPictureDrawingProperties = new NonVisualPictureDrawingProperties
{
PictureLocks = new PictureLocks
{
NoChangeAspect = true,
NoChangeArrowheads = true
}
}
};
Stretch stretch =
new Stretch
{
FillRectangle = new FillRectangle()
};
BlipFill blipFill = new BlipFill
{
Blip = new Blip
{
Embed = drawingsPart.GetIdOfPart(imagePart),
CompressionState = BlipCompressionValues.Print
},
SourceRectangle = new SourceRectangle()
};
blipFill.Append(stretch);
ShapeProperties shapeProperties =
new ShapeProperties
{
BlackWhiteMode = BlackWhiteModeValues.Auto,
Transform2D = new Transform2D
{
Offset = new Offset
{
X = 0,
Y = 0
},
Extents = extents
}
};
PresetGeometry prstGeom =
new PresetGeometry
{
Preset = ShapeTypeValues.Rectangle,
AdjustValueList = new AdjustValueList()
};
shapeProperties.Append(prstGeom);
shapeProperties.Append(new SolidFill
{
RgbColorModelHex = new RgbColorModelHex
{
Val = Color.White.ToSimpleHexValue()
}
});
DocumentFormat.OpenXml.Drawing.Outline outline = new DocumentFormat.OpenXml.Drawing.Outline
{
Width = 25400,
};
var solidFill1 = new SolidFill
{
RgbColorModelHex = new RgbColorModelHex
{
Val = StyleSheetLibrary.BorderColor.ToSimpleHexValue()
}
};
outline.Append(solidFill1);
shapeProperties.Append(outline);
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture =
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
{
NonVisualPictureProperties = pictureNonVisualPictureProperties,
BlipFill = blipFill,
ShapeProperties = shapeProperties
};
var iColumnId = 11;
var iRowId = 1;
var lColumnOffset = 0;
var lRowOffset = 0;
OneCellAnchor ocanchor = new OneCellAnchor
{
FromMarker = new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker
{
ColumnId = new ColumnId {
Text = iColumnId.ToString(CultureInfo.InvariantCulture)
},
ColumnOffset = new ColumnOffset {
Text = lColumnOffset.ToString(CultureInfo.InvariantCulture)
},
RowId = new RowId {
Text = iRowId.ToString(CultureInfo.InvariantCulture)
},
RowOffset = new RowOffset {
Text = lRowOffset.ToString(CultureInfo.InvariantCulture)
}
},
Extent = new Extent {
Cx = extents.Cx, Cy = extents.Cy
}
};
ocanchor.Append(picture);
ocanchor.Append(new ClientData());
WorksheetDrawing worksheetDrawing = new WorksheetDrawing();
worksheetDrawing.Append(ocanchor);
Drawing drawing = new Drawing {
Id = drawingsPart.GetIdOfPart(imagePart)
};
worksheetDrawing.Save(drawingsPart);
worksheetPart.Worksheet.Append(drawing);
}
// -----------------------------------------------------------------------------------------
// 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);
}