protected void ExcelDl_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Chart1.RenderControl(hw);
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";
WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
// add contents
List<string> values = new List<string>();
//values.Add(lblSectionFunctionSelected.Text);
uint i = 1;
foreach (var value in values)
{
UInt32Value rowIndex = UInt32Value.FromUInt32(i);
var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sd.Append(row);
var cell = new Cell
{
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(CellValues.String)
};
row.InsertAt(cell, 0);
i++;
}
// add image
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
imgp.FeedData(fs);
}
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "Chart";
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(fileName);
//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;
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 = 0;
pos.Y = 10;
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(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "history chart";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = wb;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
File.WriteAllBytes(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx", stream.ToArray());
}
Response.WriteFile(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx");
Response.Flush();
Response.Close();
Response.End();
}
/// <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;
}
private void WriteImageParts(DrawingsPart dp)
{
ImagePart imgp;
Xdr.WorksheetDrawing wsd = dp.WorksheetDrawing;
SLRowProperties rp;
SLColumnProperties cp;
#region Charts
if (slws.Charts.Count > 0)
{
int FromAnchorRowIndex = 0;
long FromAnchorRowOffset = 0;
int FromAnchorColumnIndex = 0;
long FromAnchorColumnOffset = 0;
int ToAnchorRowIndex = 4;
long ToAnchorRowOffset = 0;
int ToAnchorColumnIndex = 4;
long ToAnchorColumnOffset = 0;
double fTemp = 0;
ChartPart chartp;
Xdr.GraphicFrame gf;
foreach (Charts.SLChart Chart in slws.Charts)
{
chartp = dp.AddNewPart<ChartPart>();
chartp.ChartSpace = Chart.ToChartSpace(ref chartp);
gf = new Xdr.GraphicFrame();
gf.Macro = string.Empty;
gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties();
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId;
++slws.NextWorksheetDrawingId;
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = Chart.ChartName;
// alt text for charts
//...gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Description = "";
gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();
gf.Transform = new Xdr.Transform();
gf.Transform.Offset = new A.Offset() { X = 0, Y = 0 };
gf.Transform.Extents = new A.Extents() { Cx = 0, Cy = 0 };
gf.Graphic = new A.Graphic();
gf.Graphic.GraphicData = new A.GraphicData();
gf.Graphic.GraphicData.Uri = SLConstants.NamespaceC;
gf.Graphic.GraphicData.Append(new C.ChartReference() { Id = dp.GetIdOfPart(chartp) });
FromAnchorRowIndex = (int)Math.Floor(Chart.TopPosition);
fTemp = Chart.TopPosition - FromAnchorRowIndex;
FromAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU);
if (slws.RowProperties.ContainsKey(FromAnchorRowIndex + 1))
{
rp = slws.RowProperties[FromAnchorRowIndex + 1];
if (rp.HasHeight) FromAnchorRowOffset = (long)(fTemp * rp.HeightInEMU);
}
FromAnchorColumnIndex = (int)Math.Floor(Chart.LeftPosition);
fTemp = Chart.LeftPosition - FromAnchorColumnIndex;
FromAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
if (slws.ColumnProperties.ContainsKey(FromAnchorColumnIndex + 1))
{
cp = slws.ColumnProperties[FromAnchorColumnIndex + 1];
if (cp.HasWidth)
{
FromAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU);
}
}
ToAnchorRowIndex = (int)Math.Floor(Chart.BottomPosition);
fTemp = Chart.BottomPosition - ToAnchorRowIndex;
ToAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU);
if (slws.RowProperties.ContainsKey(ToAnchorRowIndex + 1))
{
rp = slws.RowProperties[ToAnchorRowIndex + 1];
if (rp.HasHeight) ToAnchorRowOffset = (long)(fTemp * rp.HeightInEMU);
}
ToAnchorColumnIndex = (int)Math.Floor(Chart.RightPosition);
fTemp = Chart.RightPosition - ToAnchorColumnIndex;
ToAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
if (slws.ColumnProperties.ContainsKey(ToAnchorColumnIndex + 1))
{
cp = slws.ColumnProperties[ToAnchorColumnIndex + 1];
if (cp.HasWidth)
{
ToAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU);
}
}
Xdr.TwoCellAnchor tcanchor = new Xdr.TwoCellAnchor();
tcanchor.FromMarker = new Xdr.FromMarker();
tcanchor.FromMarker.RowId = new Xdr.RowId(FromAnchorRowIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.RowOffset = new Xdr.RowOffset(FromAnchorRowOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.ColumnId = new Xdr.ColumnId(FromAnchorColumnIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(FromAnchorColumnOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker = new Xdr.ToMarker();
tcanchor.ToMarker.RowId = new Xdr.RowId(ToAnchorRowIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.RowOffset = new Xdr.RowOffset(ToAnchorRowOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.ColumnId = new Xdr.ColumnId(ToAnchorColumnIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(ToAnchorColumnOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.Append(gf);
tcanchor.Append(new Xdr.ClientData());
wsd.Append(tcanchor);
wsd.Save(dp);
}
}
#endregion
#region Pictures
if (slws.Pictures.Count > 0)
{
foreach (Drawing.SLPicture Picture in slws.Pictures)
{
imgp = dp.AddImagePart(Picture.PictureImagePartType);
if (Picture.DataIsInFile)
{
using (FileStream fs = new FileStream(Picture.PictureFileName, FileMode.Open))
{
imgp.FeedData(fs);
}
}
else
{
using (MemoryStream ms = new MemoryStream(Picture.PictureByteData))
{
imgp.FeedData(ms);
}
}
Xdr.Picture pic = new Xdr.Picture();
pic.NonVisualPictureProperties = new Xdr.NonVisualPictureProperties();
pic.NonVisualPictureProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties();
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId;
++slws.NextWorksheetDrawingId;
// recommendation is to set as the actual filename, but we'll follow Excel here...
// Note: the name value can be used multiple times without Excel choking.
// So for example, you can have two pictures with "Picture 1".
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Name = string.Format("Picture {0}", dp.ImageParts.Count());
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Description = Picture.AlternativeText;
// hlinkClick and hlinkHover as children
if (Picture.HasUri)
{
HyperlinkRelationship hlinkrel = dp.AddHyperlinkRelationship(new System.Uri(Picture.HyperlinkUri, Picture.HyperlinkUriKind), Picture.IsHyperlinkExternal);
pic.NonVisualPictureProperties.NonVisualDrawingProperties.HyperlinkOnClick = new A.HyperlinkOnClick() { Id = hlinkrel.Id };
}
pic.NonVisualPictureProperties.NonVisualPictureDrawingProperties = new Xdr.NonVisualPictureDrawingProperties();
pic.BlipFill = new Xdr.BlipFill();
pic.BlipFill.Blip = new A.Blip();
pic.BlipFill.Blip.Embed = dp.GetIdOfPart(imgp);
if (Picture.CompressionState != A.BlipCompressionValues.None)
{
pic.BlipFill.Blip.CompressionState = Picture.CompressionState;
}
if (Picture.Brightness != 0 || Picture.Contrast != 0)
{
A.LuminanceEffect lumeffect = new A.LuminanceEffect();
if (Picture.Brightness != 0) lumeffect.Brightness = Convert.ToInt32(Picture.Brightness * 1000);
if (Picture.Contrast != 0) lumeffect.Contrast = Convert.ToInt32(Picture.Contrast * 1000);
pic.BlipFill.Blip.Append(lumeffect);
}
pic.BlipFill.SourceRectangle = new A.SourceRectangle();
pic.BlipFill.Append(new A.Stretch() { FillRectangle = new A.FillRectangle() });
Picture.ShapeProperties.BlackWhiteMode = A.BlackWhiteModeValues.Auto;
Picture.ShapeProperties.HasTransform2D = true;
Picture.ShapeProperties.Transform2D.HasOffset = true;
Picture.ShapeProperties.Transform2D.HasExtents = true;
// not supporting yet because you need to change the positional offsets too.
//if (Picture.RotationAngle != 0)
//{
// pic.ShapeProperties.Transform2D.Rotation = Convert.ToInt32(Picture.RotationAngle * (decimal)SLConstants.DegreeToAngleRepresentation);
//}
// used when it's relative positioning
// these are the actual values used, so it's 1 less than the given anchor indices.
int iColumnId = 0, iRowId = 0;
long lColumnOffset = 0, lRowOffset = 0;
if (Picture.UseRelativePositioning)
{
iColumnId = Picture.AnchorColumnIndex - 1;
iRowId = Picture.AnchorRowIndex - 1;
long lOffset = 0;
long lOffsetBuffer = 0;
int i;
for (i = 1; i <= iColumnId; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.HasWidth)
{
lOffsetBuffer += cp.WidthInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
else
{
// we use the current worksheet's column width
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
lOffsetBuffer += Picture.OffsetX;
lOffset = lOffsetBuffer;
if (lOffset <= 0)
{
// in case the given offset is so negative, it pushes the sum to negative
// We use "<= 0" here, so the else part assumes a positive offset
iColumnId = 0;
lColumnOffset = 0;
}
else
{
lOffsetBuffer = 0;
i = 1;
while (lOffset > lOffsetBuffer)
{
iColumnId = i - 1;
lColumnOffset = lOffset - lOffsetBuffer;
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.HasWidth)
{
lOffsetBuffer += cp.WidthInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
else
{
// we use the current worksheet's column width
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
++i;
}
}
Picture.ShapeProperties.Transform2D.Offset.X = lColumnOffset;
lOffsetBuffer = 0;
for (i = 1; i <= iRowId; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.HasHeight)
{
lOffsetBuffer += rp.HeightInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
else
{
// we use the current worksheet's row height
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
lOffsetBuffer += Picture.OffsetY;
lOffset = lOffsetBuffer;
if (lOffset <= 0)
{
// in case the given offset is so negative, it pushes the sum to negative
// We use "<= 0" here, so the else part assumes a positive offset
iRowId = 0;
lRowOffset = 0;
}
else
{
lOffsetBuffer = 0;
i = 1;
while (lOffset > lOffsetBuffer)
{
iRowId = i - 1;
lRowOffset = lOffset - lOffsetBuffer;
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.HasHeight)
{
lOffsetBuffer += rp.HeightInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
else
{
// we use the current worksheet's row height
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
++i;
}
}
Picture.ShapeProperties.Transform2D.Offset.Y = lRowOffset;
}
else
{
Picture.ShapeProperties.Transform2D.Offset.X = 0;
Picture.ShapeProperties.Transform2D.Offset.Y = 0;
}
Picture.ShapeProperties.Transform2D.Extents.Cx = Picture.WidthInEMU;
Picture.ShapeProperties.Transform2D.Extents.Cy = Picture.HeightInEMU;
Picture.ShapeProperties.HasPresetGeometry = true;
Picture.ShapeProperties.PresetGeometry = Picture.PictureShape;
pic.ShapeProperties = Picture.ShapeProperties.ToXdrShapeProperties();
Xdr.ClientData clientdata = new Xdr.ClientData();
// the properties are true by default
if (!Picture.LockWithSheet) clientdata.LockWithSheet = false;
if (!Picture.PrintWithSheet) clientdata.PrintWithSheet = false;
if (Picture.UseRelativePositioning)
{
Xdr.OneCellAnchor ocanchor = new Xdr.OneCellAnchor();
ocanchor.FromMarker = new Xdr.FromMarker();
// Subtract 1 because picture goes to bottom right corner
// Subtracting 1 makes it more intuitive that (1,1) means top-left corner of (1,1)
ocanchor.FromMarker.ColumnId = new Xdr.ColumnId() { Text = iColumnId.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset() { Text = lColumnOffset.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.RowId = new Xdr.RowId() { Text = iRowId.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.RowOffset = new Xdr.RowOffset() { Text = lRowOffset.ToString(CultureInfo.InvariantCulture) };
ocanchor.Extent = new Xdr.Extent();
ocanchor.Extent.Cx = Picture.WidthInEMU;
ocanchor.Extent.Cy = Picture.HeightInEMU;
ocanchor.Append(pic);
ocanchor.Append(clientdata);
wsd.Append(ocanchor);
}
else
{
Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor();
absanchor.Position = new Xdr.Position();
absanchor.Position.X = Picture.OffsetX;
absanchor.Position.Y = Picture.OffsetY;
absanchor.Extent = new Xdr.Extent();
absanchor.Extent.Cx = Picture.WidthInEMU;
absanchor.Extent.Cy = Picture.HeightInEMU;
absanchor.Append(pic);
absanchor.Append(clientdata);
wsd.Append(absanchor);
}
wsd.Save(dp);
}
}
#endregion
}
/// <summary>
/// Insert a chart into a chartsheet.
/// </summary>
/// <param name="Chart">An SLChart object with the chart's properties already set.</param>
/// <param name="ChartsheetName">The name should not be blank, nor exceed 31 characters. And it cannot contain these characters: \/?*[] It cannot be the same as an existing name (case-insensitive). But there's nothing stopping you from using 3 spaces as a name.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertChart(Charts.SLChart Chart, string ChartsheetName)
{
if (!SLTool.CheckSheetChartName(ChartsheetName))
{
return false;
}
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(ChartsheetName, StringComparison.OrdinalIgnoreCase))
{
return false;
}
}
ChartsheetPart csp = wbp.AddNewPart<ChartsheetPart>();
#region the drawing part
DrawingsPart dp = csp.AddNewPart<DrawingsPart>();
dp.WorksheetDrawing = new Xdr.WorksheetDrawing();
ChartPart chartp = dp.AddNewPart<ChartPart>();
chartp.ChartSpace = Chart.ToChartSpace(ref chartp);
Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor();
absanchor.Append(new Xdr.Position() { X = 0, Y = 0 });
// The paper size is involved. The default is Letter, which is 11 inch by 8.5 inch
// with landscape orientation.
// The page margin is also involved. In particular, the left and right margins for
// the width, and top and bottom margins for the height.
// We're using the "default" margin settings, which is
// left=0.7, right=0.7, top=0.75, bottom=0.75
// Excel also has a buffer of 0.12 inches around the sides...
// And so,
// 8668512 = (11 - 0.7 - 0.7 - 0.12) * 914400
// 6291072 = (8.5 - 0.75 - 0.75 - 0.12) * 914400
absanchor.Append(new Xdr.Extent() { Cx = 8668512, Cy = 6291072 });
Xdr.GraphicFrame gf = new Xdr.GraphicFrame() { Macro = "" };
gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties()
{
Id = 2,
Name = Chart.ChartName
};
gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties()
{
GraphicFrameLocks = new A.GraphicFrameLocks() { NoGrouping = true }
};
gf.Transform = new Xdr.Transform()
{
Offset = new A.Offset() { X = 0, Y = 0 },
Extents = new A.Extents() { Cx = 0, Cy = 0 }
};
gf.Graphic = new A.Graphic();
gf.Graphic.GraphicData = new A.GraphicData();
gf.Graphic.GraphicData.Uri = SLConstants.NamespaceC;
gf.Graphic.GraphicData.Append(new C.ChartReference() { Id = dp.GetIdOfPart(chartp) });
absanchor.Append(gf);
absanchor.Append(new Xdr.ClientData());
dp.WorksheetDrawing.Append(absanchor);
#endregion
#region the chartsheet part
csp.Chartsheet = new Chartsheet();
csp.Chartsheet.ChartSheetProperties = new ChartSheetProperties();
csp.Chartsheet.ChartSheetViews = new ChartSheetViews();
csp.Chartsheet.ChartSheetViews.Append(new ChartSheetView() { WorkbookViewId = 0 });
csp.Chartsheet.PageMargins = new PageMargins()
{
Left = SLConstants.NormalLeftMargin,
Right = SLConstants.NormalRightMargin,
Top = SLConstants.NormalTopMargin,
Bottom = SLConstants.NormalBottomMargin,
Header = SLConstants.NormalHeaderMargin,
Footer = SLConstants.NormalFooterMargin
};
csp.Chartsheet.Drawing = new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = csp.GetIdOfPart(dp)
};
#endregion
++giWorksheetIdCounter;
slwb.Sheets.Add(new SLSheet(ChartsheetName, (uint)giWorksheetIdCounter, wbp.GetIdOfPart(csp), SLSheetType.Chartsheet));
return true;
}
private static Drawing GetLogo(ExcelType excelType, WorksheetPart wsp, out uint startIndex)
{
var dp = wsp.AddNewPart<DrawingsPart>();
Drawing drawing;
var sImagePath = GetLogoPath(excelType);
if (string.IsNullOrEmpty(sImagePath))
{
startIndex = 1;
return null;
}
startIndex = 8;
//sorry, I must do it in this way, MEmory stream oesn't work in FeedData. There are several discussions in the internet about it.
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
var bm = new Bitmap(Resource.CEEX_small);
//var bm = new Bitmap(@"ExcelProvider\_img\CEEX_small.png");
using (var ms = new MemoryStream())
{
bm.Save(ms,ImageFormat.Png);
ms.Seek(0, 0);
imgp.FeedData(ms);
}
var nvdp = new NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "logo";
nvdp.Description = "polymathlogo";
var picLocks = new PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
var nvpdp = new NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
var nvpp = new NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
var stretch = new Stretch();
stretch.FillRectangle = new FillRectangle();
var blipFill = new BlipFill();
var blip = new Blip();
blip.Embed = dp.GetIdOfPart(imgp);
blip.CompressionState = BlipCompressionValues.Print;
blipFill.Blip = blip;
blipFill.SourceRectangle = new SourceRectangle();
blipFill.Append(stretch);
var t2d = new Transform2D();
var offset = new Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
//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 Extents();
extents.Cx = bm.Width * (long)(914400 / bm.HorizontalResolution);
extents.Cy = bm.Height * (long)(914400 / bm.VerticalResolution);
bm.Dispose();
t2d.Extents = extents;
var sp = new ShapeProperties();
sp.BlackWhiteMode = BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
var prstGeom = new PresetGeometry();
prstGeom.Preset = ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new AdjustValueList();
sp.Append(prstGeom);
sp.Append(new NoFill());
var picture = new Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
var pos = new Position();
pos.X = 0;
pos.Y = 0;
var ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
var anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
var wsd = new WorksheetDrawing();
wsd.Append(anchor);
drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
return drawing;
}
private void WriteImageParts(DrawingsPart dp)
{
ImagePart imgp;
Xdr.WorksheetDrawing wsd = dp.WorksheetDrawing;
SLRowProperties rp;
SLColumnProperties cp;
#region Charts
if (slws.Charts.Count > 0)
{
int FromAnchorRowIndex = 0;
long FromAnchorRowOffset = 0;
int FromAnchorColumnIndex = 0;
long FromAnchorColumnOffset = 0;
int ToAnchorRowIndex = 4;
long ToAnchorRowOffset = 0;
int ToAnchorColumnIndex = 4;
long ToAnchorColumnOffset = 0;
double fTemp = 0;
ChartPart chartp;
Xdr.GraphicFrame gf;
foreach (Charts.SLChart Chart in slws.Charts)
{
chartp = dp.AddNewPart<ChartPart>();
chartp.ChartSpace = Chart.ToChartSpace(ref chartp);
gf = new Xdr.GraphicFrame();
gf.Macro = string.Empty;
gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties();
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId;
++slws.NextWorksheetDrawingId;
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = Chart.ChartName;
// alt text for charts
//...gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Description = "";
gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();
gf.Transform = new Xdr.Transform();
gf.Transform.Offset = new A.Offset() { X = 0, Y = 0 };
gf.Transform.Extents = new A.Extents() { Cx = 0, Cy = 0 };
gf.Graphic = new A.Graphic();
gf.Graphic.GraphicData = new A.GraphicData();
gf.Graphic.GraphicData.Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart";
gf.Graphic.GraphicData.Append(new C.ChartReference() { Id = dp.GetIdOfPart(chartp) });
FromAnchorRowIndex = (int)Math.Floor(Chart.TopPosition);
fTemp = Chart.TopPosition - FromAnchorRowIndex;
FromAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU);
if (slws.RowProperties.ContainsKey(FromAnchorRowIndex + 1))
{
rp = slws.RowProperties[FromAnchorRowIndex + 1];
if (rp.HasHeight) FromAnchorRowOffset = (long)(fTemp * rp.HeightInEMU);
}
FromAnchorColumnIndex = (int)Math.Floor(Chart.LeftPosition);
fTemp = Chart.LeftPosition - FromAnchorColumnIndex;
FromAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
if (slws.ColumnProperties.ContainsKey(FromAnchorColumnIndex + 1))
{
cp = slws.ColumnProperties[FromAnchorColumnIndex + 1];
if (cp.HasWidth) FromAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU);
}
ToAnchorRowIndex = (int)Math.Floor(Chart.BottomPosition);
fTemp = Chart.BottomPosition - ToAnchorRowIndex;
ToAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU);
if (slws.RowProperties.ContainsKey(ToAnchorRowIndex + 1))
{
rp = slws.RowProperties[ToAnchorRowIndex + 1];
if (rp.HasHeight) ToAnchorRowOffset = (long)(fTemp * rp.HeightInEMU);
}
ToAnchorColumnIndex = (int)Math.Floor(Chart.RightPosition);
fTemp = Chart.RightPosition - ToAnchorColumnIndex;
ToAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
if (slws.ColumnProperties.ContainsKey(ToAnchorColumnIndex + 1))
{
cp = slws.ColumnProperties[ToAnchorColumnIndex + 1];
if (cp.HasWidth) ToAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU);
}
Xdr.TwoCellAnchor tcanchor = new Xdr.TwoCellAnchor();
tcanchor.FromMarker = new Xdr.FromMarker();
tcanchor.FromMarker.RowId = new Xdr.RowId(FromAnchorRowIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.RowOffset = new Xdr.RowOffset(FromAnchorRowOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.ColumnId = new Xdr.ColumnId(FromAnchorColumnIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(FromAnchorColumnOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker = new Xdr.ToMarker();
tcanchor.ToMarker.RowId = new Xdr.RowId(ToAnchorRowIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.RowOffset = new Xdr.RowOffset(ToAnchorRowOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.ColumnId = new Xdr.ColumnId(ToAnchorColumnIndex.ToString(CultureInfo.InvariantCulture));
tcanchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(ToAnchorColumnOffset.ToString(CultureInfo.InvariantCulture));
tcanchor.Append(gf);
tcanchor.Append(new Xdr.ClientData());
wsd.Append(tcanchor);
wsd.Save(dp);
}
}
#endregion
#region Pictures
if (slws.Pictures.Count > 0)
{
foreach (Drawing.SLPicture Picture in slws.Pictures)
{
imgp = dp.AddImagePart(Picture.PictureImagePartType);
if (Picture.DataIsInFile)
{
using (FileStream fs = new FileStream(Picture.PictureFileName, FileMode.Open))
{
imgp.FeedData(fs);
}
}
else
{
using (MemoryStream ms = new MemoryStream(Picture.PictureByteData))
{
imgp.FeedData(ms);
}
}
Xdr.Picture pic = new Xdr.Picture();
pic.NonVisualPictureProperties = new Xdr.NonVisualPictureProperties();
pic.NonVisualPictureProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties();
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId;
++slws.NextWorksheetDrawingId;
// recommendation is to set as the actual filename, but we'll follow Excel here...
// Note: the name value can be used multiple times without Excel choking.
// So for example, you can have two pictures with "Picture 1".
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Name = string.Format("Picture {0}", dp.ImageParts.Count());
pic.NonVisualPictureProperties.NonVisualDrawingProperties.Description = Picture.AlternativeText;
// hlinkClick and hlinkHover as children
if (Picture.HasUri)
{
HyperlinkRelationship hlinkrel = dp.AddHyperlinkRelationship(new System.Uri(Picture.HyperlinkUri, Picture.HyperlinkUriKind), Picture.IsHyperlinkExternal);
pic.NonVisualPictureProperties.NonVisualDrawingProperties.HyperlinkOnClick = new A.HyperlinkOnClick() { Id = hlinkrel.Id };
}
pic.NonVisualPictureProperties.NonVisualPictureDrawingProperties = new Xdr.NonVisualPictureDrawingProperties();
pic.BlipFill = new Xdr.BlipFill();
pic.BlipFill.Blip = new A.Blip();
pic.BlipFill.Blip.Embed = dp.GetIdOfPart(imgp);
if (Picture.CompressionState != A.BlipCompressionValues.None)
{
pic.BlipFill.Blip.CompressionState = Picture.CompressionState;
}
if (Picture.Brightness != 0 || Picture.Contrast != 0)
{
A.LuminanceEffect lumeffect = new A.LuminanceEffect();
if (Picture.Brightness != 0) lumeffect.Brightness = Convert.ToInt32(Picture.Brightness * 1000);
if (Picture.Contrast != 0) lumeffect.Contrast = Convert.ToInt32(Picture.Contrast * 1000);
pic.BlipFill.Blip.Append(lumeffect);
}
pic.BlipFill.SourceRectangle = new A.SourceRectangle();
pic.BlipFill.Append(new A.Stretch() { FillRectangle = new A.FillRectangle() });
pic.ShapeProperties = new Xdr.ShapeProperties();
pic.ShapeProperties.BlackWhiteMode = A.BlackWhiteModeValues.Auto;
pic.ShapeProperties.Transform2D = new A.Transform2D();
// not supporting yet because you need to change the positional offsets too.
//if (Picture.RotationAngle != 0)
//{
// pic.ShapeProperties.Transform2D.Rotation = Convert.ToInt32(Picture.RotationAngle * (decimal)SLConstants.DegreeToAngleRepresentation);
//}
pic.ShapeProperties.Transform2D.Offset = new A.Offset();
// used when it's relative positioning
// these are the actual values used, so it's 1 less than the given anchor indices.
int iColumnId = 0, iRowId = 0;
long lColumnOffset = 0, lRowOffset = 0;
if (Picture.UseRelativePositioning)
{
iColumnId = Picture.AnchorColumnIndex - 1;
iRowId = Picture.AnchorRowIndex - 1;
long lOffset = 0;
long lOffsetBuffer = 0;
int i;
if (iColumnId > 0)
{
for (i = 1; i <= iColumnId; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.HasWidth)
{
lOffsetBuffer += cp.WidthInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
else
{
// we use the current worksheet's column width
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
}
lOffsetBuffer += Picture.OffsetX;
lOffset = lOffsetBuffer;
if (lOffset <= 0)
{
// in case the given offset is so negative, it pushes the sum to negative
// We use "<= 0" here, so the else part assumes a positive offset
iColumnId = 0;
lColumnOffset = 0;
}
else
{
lOffsetBuffer = 0;
i = 1;
while (lOffset > lOffsetBuffer)
{
iColumnId = i - 1;
lColumnOffset = lOffset - lOffsetBuffer;
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.HasWidth)
{
lOffsetBuffer += cp.WidthInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
else
{
// we use the current worksheet's column width
lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
++i;
}
}
pic.ShapeProperties.Transform2D.Offset.X = lColumnOffset;
lOffsetBuffer = 0;
if (iRowId > 0)
{
for (i = 1; i <= iRowId; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.HasHeight)
{
lOffsetBuffer += rp.HeightInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
else
{
// we use the current worksheet's row height
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
}
lOffsetBuffer += Picture.OffsetY;
lOffset = lOffsetBuffer;
if (lOffset <= 0)
{
// in case the given offset is so negative, it pushes the sum to negative
// We use "<= 0" here, so the else part assumes a positive offset
iRowId = 0;
lRowOffset = 0;
}
else
{
lOffsetBuffer = 0;
i = 1;
while (lOffset > lOffsetBuffer)
{
iRowId = i - 1;
lRowOffset = lOffset - lOffsetBuffer;
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.HasHeight)
{
lOffsetBuffer += rp.HeightInEMU;
}
else
{
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
else
{
// we use the current worksheet's row height
lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
++i;
}
}
pic.ShapeProperties.Transform2D.Offset.Y = lRowOffset;
}
else
{
pic.ShapeProperties.Transform2D.Offset.X = 0;
pic.ShapeProperties.Transform2D.Offset.Y = 0;
}
pic.ShapeProperties.Transform2D.Extents = new A.Extents();
pic.ShapeProperties.Transform2D.Extents.Cx = Picture.WidthInEMU;
pic.ShapeProperties.Transform2D.Extents.Cy = Picture.HeightInEMU;
pic.ShapeProperties.Append(new A.PresetGeometry() { Preset = Picture.PictureShape, AdjustValueList = new A.AdjustValueList() });
switch (Picture.FillType)
{
case Drawing.SLPicture.SLPictureFillType.None:
pic.ShapeProperties.Append(new A.NoFill());
break;
case Drawing.SLPicture.SLPictureFillType.Solid:
A.SolidFill solidfill = new A.SolidFill();
solidfill.InnerXml = Picture.FillClassInnerXml;
pic.ShapeProperties.Append(solidfill);
break;
default:
pic.ShapeProperties.Append(new A.NoFill());
break;
}
if (Picture.HasOutline)
{
A.Outline outline = new A.Outline();
if (Picture.PictureOutline.Width != null)
{
outline.Width = Picture.PictureOutline.Width.Value;
}
if (Picture.PictureOutline.CompoundLineType != null)
{
outline.CompoundLineType = Picture.PictureOutline.CompoundLineType.Value;
}
if (Picture.PictureOutline.CapType != null)
{
outline.CapType = Picture.PictureOutline.CapType.Value;
}
outline.Append((A.SolidFill)Picture.PictureOutlineFill.CloneNode(true));
foreach (var child in Picture.PictureOutlineFill.ChildElements)
{
if (child is A.PresetDash)
{
outline.Append((A.PresetDash)child.CloneNode(true));
}
else if (child is A.Round)
{
outline.Append((A.Round)child.CloneNode(true));
}
else if (child is A.Bevel)
{
outline.Append((A.Bevel)child.CloneNode(true));
}
else if (child is A.Miter)
{
outline.Append((A.Miter)child.CloneNode(true));
}
}
pic.ShapeProperties.Append(outline);
}
if (Picture.HasEffectList)
{
A.EffectList effectlist = new A.EffectList();
if (Picture.HasGlow)
{
A.Glow glow = new A.Glow();
glow.Radius = Picture.GlowRadius;
glow.InnerXml = Picture.GlowColorInnerXml;
effectlist.Append(glow);
}
if (Picture.HasInnerShadow)
{
effectlist.Append((A.InnerShadow)Picture.PictureInnerShadow.CloneNode(true));
}
if (Picture.HasOuterShadow)
{
effectlist.Append((A.OuterShadow)Picture.PictureOuterShadow.CloneNode(true));
}
if (Picture.HasReflection)
{
A.Reflection refl = new A.Reflection();
if (Picture.ReflectionBlurRadius != 0) refl.BlurRadius = Picture.ReflectionBlurRadius;
if (Picture.ReflectionStartOpacity != 100000) refl.StartOpacity = Picture.ReflectionStartOpacity;
if (Picture.ReflectionStartPosition != 0) refl.StartPosition = Picture.ReflectionStartPosition;
if (Picture.ReflectionEndAlpha != 0) refl.EndAlpha = Picture.ReflectionEndAlpha;
if (Picture.ReflectionEndPosition != 100000) refl.EndPosition = Picture.ReflectionEndPosition;
if (Picture.ReflectionDistance != 0) refl.Distance = Picture.ReflectionDistance;
if (Picture.ReflectionDirection != 0) refl.Direction = Picture.ReflectionDirection;
if (Picture.ReflectionFadeDirection != 5400000) refl.FadeDirection = Picture.ReflectionFadeDirection;
if (Picture.ReflectionHorizontalRatio != 100000) refl.HorizontalRatio = Picture.ReflectionHorizontalRatio;
if (Picture.ReflectionVerticalRatio != 100000) refl.VerticalRatio = Picture.ReflectionVerticalRatio;
if (Picture.ReflectionHorizontalSkew != 0) refl.HorizontalSkew = Picture.ReflectionHorizontalSkew;
if (Picture.ReflectionVerticalSkew != 0) refl.VerticalSkew = Picture.ReflectionVerticalSkew;
if (Picture.ReflectionAlignment != A.RectangleAlignmentValues.Bottom) refl.Alignment = Picture.ReflectionAlignment;
if (!Picture.ReflectionRotateWithShape) refl.RotateWithShape = false;
effectlist.Append(refl);
}
if (Picture.HasSoftEdge)
{
A.SoftEdge softedge = new A.SoftEdge();
softedge.Radius = Picture.SoftEdgeRadius;
effectlist.Append(softedge);
}
pic.ShapeProperties.Append(effectlist);
}
if (Picture.HasScene3D)
{
A.Scene3DType scene3d = new A.Scene3DType();
scene3d.Camera = new A.Camera();
scene3d.Camera.Preset = Picture.CameraPreset;
scene3d.Camera.FieldOfView = Picture.CameraFieldOfView;
// default is 100%
if (Picture.CameraZoom != 100000)
{
scene3d.Camera.Zoom = Picture.CameraZoom;
}
if (Picture.CameraLatitude != 0 || Picture.CameraLongitude != 0 || Picture.CameraRevolution != 0)
{
scene3d.Camera.Rotation = new A.Rotation();
scene3d.Camera.Rotation.Latitude = Picture.CameraLatitude;
scene3d.Camera.Rotation.Longitude = Picture.CameraLongitude;
scene3d.Camera.Rotation.Revolution = Picture.CameraRevolution;
}
scene3d.LightRig = new A.LightRig();
scene3d.LightRig.Rig = Picture.LightRigType;
scene3d.LightRig.Direction = Picture.LightRigDirection;
if (Picture.LightRigLatitude != 0 || Picture.LightRigLongitude != 0 || Picture.LightRigRevolution != 0)
{
scene3d.LightRig.Rotation = new A.Rotation();
scene3d.LightRig.Rotation.Latitude = Picture.LightRigLatitude;
scene3d.LightRig.Rotation.Longitude = Picture.LightRigLongitude;
scene3d.LightRig.Rotation.Revolution = Picture.LightRigRevolution;
}
pic.ShapeProperties.Append(scene3d);
}
if (Picture.HasShape3D)
{
A.Shape3DType shape3d = new A.Shape3DType();
if (Picture.HasBevelTop)
{
shape3d.BevelTop = new A.BevelTop();
shape3d.BevelTop.Preset = Picture.BevelTopPreset;
shape3d.BevelTop.Width = Picture.BevelTopWidth;
shape3d.BevelTop.Height = Picture.BevelTopHeight;
}
if (Picture.HasBevelBottom)
{
shape3d.BevelBottom = new A.BevelBottom();
shape3d.BevelBottom.Preset = Picture.BevelBottomPreset;
shape3d.BevelBottom.Width = Picture.BevelBottomWidth;
shape3d.BevelBottom.Height = Picture.BevelBottomHeight;
}
if (Picture.HasExtrusion)
{
A.ExtrusionColor extcolor = new A.ExtrusionColor();
extcolor.InnerXml = Picture.ExtrusionColorInnerXml;
shape3d.ExtrusionColor = extcolor;
shape3d.ExtrusionHeight = Picture.ExtrusionHeight;
}
if (Picture.HasContour)
{
A.ContourColor contourcolor = new A.ContourColor();
contourcolor.InnerXml = Picture.ContourColorInnerXml;
shape3d.ContourColor = contourcolor;
shape3d.ContourWidth = Picture.ContourWidth;
}
if (Picture.HasMaterialType)
{
shape3d.PresetMaterial = Picture.MaterialType;
}
if (Picture.HasZDistance)
{
shape3d.Z = Picture.ZDistance;
}
pic.ShapeProperties.Append(shape3d);
}
Xdr.ClientData clientdata = new Xdr.ClientData();
// the properties are true by default
if (!Picture.LockWithSheet) clientdata.LockWithSheet = false;
if (!Picture.PrintWithSheet) clientdata.PrintWithSheet = false;
if (Picture.UseRelativePositioning)
{
Xdr.OneCellAnchor ocanchor = new Xdr.OneCellAnchor();
ocanchor.FromMarker = new Xdr.FromMarker();
// Subtract 1 because picture goes to bottom right corner
// Subtracting 1 makes it more intuitive that (1,1) means top-left corner of (1,1)
ocanchor.FromMarker.ColumnId = new Xdr.ColumnId() { Text = iColumnId.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset() { Text = lColumnOffset.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.RowId = new Xdr.RowId() { Text = iRowId.ToString(CultureInfo.InvariantCulture) };
ocanchor.FromMarker.RowOffset = new Xdr.RowOffset() { Text = lRowOffset.ToString(CultureInfo.InvariantCulture) };
ocanchor.Extent = new Xdr.Extent();
ocanchor.Extent.Cx = Picture.WidthInEMU;
ocanchor.Extent.Cy = Picture.HeightInEMU;
ocanchor.Append(pic);
ocanchor.Append(clientdata);
wsd.Append(ocanchor);
}
else
{
Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor();
absanchor.Position = new Xdr.Position();
absanchor.Position.X = Picture.OffsetX;
absanchor.Position.Y = Picture.OffsetY;
absanchor.Extent = new Xdr.Extent();
absanchor.Extent.Cx = Picture.WidthInEMU;
absanchor.Extent.Cy = Picture.HeightInEMU;
absanchor.Append(pic);
absanchor.Append(clientdata);
wsd.Append(absanchor);
}
wsd.Save(dp);
}
}
#endregion
}
private static void GeneratePicture(WorksheetPart worksheetPart, IXLPicture picture)
{
if (!File.Exists(picture.FilePath))
{
throw new ArgumentException("image file not found: " + picture.FilePath);
}
var drawingsPart = worksheetPart.DrawingsPart ?? worksheetPart.AddNewPart<DrawingsPart>();
var worksheet = worksheetPart.Worksheet;
if (!worksheet.ChildElements.OfType<Drawing>().Any())
{
var drawing = new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart),
};
worksheet.InsertBefore(drawing, worksheet.Last());
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
}
var sheetDrawing = drawingsPart.WorksheetDrawing;
var imagePart = drawingsPart.AddImagePart(XlPictureTypeConverter.Convert(picture.Type));
int imageWidth, imageHeight;
float imageResX, imageResY;
using (var stream = new FileStream(picture.FilePath, FileMode.Open))
{
using (var bitmap = new System.Drawing.Bitmap(stream))
{
imageWidth = bitmap.Width;
imageHeight = bitmap.Height;
imageResX = bitmap.HorizontalResolution;
imageResY = bitmap.VerticalResolution;
}
stream.Seek(0, SeekOrigin.Begin);
imagePart.FeedData(stream);
}
if (picture.WidthPx == default(int))
{
picture.WidthPx = imageWidth;
}
if (picture.HeightPx == default(int))
{
picture.HeightPx = imageHeight;
}
var nvps = sheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
var nvpId = (nvps.Count() > 0) ? (UInt32Value)nvps.Max(prop => prop.Id.Value) + 1 : 1U;
var pictureLocks = new A.PictureLocks()
{
NoChangeAspect = !picture.CanUserChangeAspect,
NoCrop = !picture.CanUserCrop,
NoMove = !picture.CanUserMove,
NoResize = !picture.CanUserResize,
NoRotation = !picture.CanUserRotate,
NoSelection = !picture.CanUserSelect,
};
OpenXmlCompositeElement cellAnchor;
{
var markers = picture.GetMarkers();
var markerCount = markers.Count();
if (markerCount == 0)
{
cellAnchor = new Xdr.AbsoluteAnchor(new Xdr.Position() { X = 0, Y = 0 });
}
else if (markerCount == 1)
{
cellAnchor = new Xdr.OneCellAnchor(
XLMarkerConverter.Convert<Xdr.FromMarker>(
markers.First(),
(x) => CalcEmuScale(x, imageResX),
(y) => CalcEmuScale(y, imageResY)));
}
else if (markerCount == 2)
{
var from = markers.First();
var to = markers.Last();
if (
from.RowIndex > to.RowIndex ||
from.ColumnIndex > to.ColumnIndex ||
(from.RowIndex == to.RowIndex && from.RowOffsetPx > to.RowOffsetPx) ||
(from.ColumnIndex == to.ColumnIndex && from.ColumnOffsetPx > to.ColumnOffsetPx)
)
{
throw new ArgumentException("positions of second marker must be larger than those of first marker");
}
cellAnchor = new Xdr.TwoCellAnchor(
XLMarkerConverter.Convert<Xdr.FromMarker>(
from,
(x) => CalcEmuScale(x, imageResX),
(y) => CalcEmuScale(y, imageResY)),
XLMarkerConverter.Convert<Xdr.ToMarker>(
to,
(x) => CalcEmuScale(x, imageResX),
(y) => CalcEmuScale(y, imageResY)));
}
else
{
throw new ArgumentException("invalid markers count: " + markers.Count());
}
}
var widthEmu = CalcEmuScale(picture.WidthPx, imageResX);
var heightEmu = CalcEmuScale(picture.HeightPx, imageResY);
cellAnchor.Append(
new Xdr.Extent() { Cx = widthEmu, Cy = heightEmu, },
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties()
{
Id = nvpId,
Name = (!string.IsNullOrEmpty(picture.Name)) ? picture.Name : nvpId.ToString() + "_" + Path.GetFileNameWithoutExtension(picture.FilePath),
Description = (!string.IsNullOrEmpty(picture.Description)) ? picture.Description : nvpId.ToString() + "_" + Path.GetFileNameWithoutExtension(picture.FilePath),
},
new Xdr.NonVisualPictureDrawingProperties(pictureLocks)
),
new Xdr.BlipFill(
new A.Blip() { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset() { X = 0, Y = 0, },
new A.Extents() { Cx = widthEmu, Cy = heightEmu }
),
new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle }
)
),
new Xdr.ClientData()
);
sheetDrawing.Append(cellAnchor);
#if false
var errors = new DocumentFormat.OpenXml.Validation.OpenXmlValidator().Validate(worksheetPart);
foreach (var error in errors)
{
Console.WriteLine(error.Description);
}
if (errors.Count() > 0) Console.Read();
#endif
}
