using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace ExcelOpenXmlWithImageAndStyles
{
	class Program
	{
		static void Main(string[] args)
		{
			string sFile = "ExcelOpenXmlWithImageAndStyles.xlsx";
			if (File.Exists(sFile))
			{
				File.Delete(sFile);
			}
			BuildWorkbook(sFile);
		}

		private static void BuildWorkbook(string filename)
		{
			try
			{
				using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
				{
					WorkbookPart wbp = xl.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();

					WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
					wbsp.Stylesheet = CreateStylesheet();
					wbsp.Stylesheet.Save();

					string sImagePath = "polymathlogo.png";
					DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
					ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
					using (FileStream fs = new FileStream(sImagePath, 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;
					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.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);

					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();
					sheet.Name = "Sheet1";
					sheet.SheetId = 1;
					sheet.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)
			{
				Console.WriteLine(e.ToString());
				Console.ReadLine();
			}
		}

		private static Stylesheet CreateStylesheet()
		{
			Stylesheet ss = new Stylesheet();

			Fonts fts = new Fonts();
			DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
			FontName ftn = new FontName();
			ftn.Val = StringValue.FromString("Calibri");
			FontSize ftsz = new FontSize();
			ftsz.Val = DoubleValue.FromDouble(11);
			ft.FontName = ftn;
			ft.FontSize = ftsz;
			fts.Append(ft);

			ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
			ftn = new FontName();
			ftn.Val = StringValue.FromString("Palatino Linotype");
			ftsz = new FontSize();
			ftsz.Val = DoubleValue.FromDouble(18);
			ft.FontName = ftn;
			ft.FontSize = ftsz;
			fts.Append(ft);

			fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);

			Fills fills = new Fills();
			Fill fill;
			PatternFill patternFill;
			fill = new Fill();
			patternFill = new PatternFill();
			patternFill.PatternType = PatternValues.None;
			fill.PatternFill = patternFill;
			fills.Append(fill);

			fill = new Fill();
			patternFill = new PatternFill();
			patternFill.PatternType = PatternValues.Gray125;
			fill.PatternFill = patternFill;
			fills.Append(fill);

			fill = new Fill();
			patternFill = new PatternFill();
			patternFill.PatternType = PatternValues.Solid;
			patternFill.ForegroundColor = new ForegroundColor();
			patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
			patternFill.BackgroundColor = new BackgroundColor();
			patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
			fill.PatternFill = patternFill;
			fills.Append(fill);

			fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

			Borders borders = new Borders();
			Border border = new Border();
			border.LeftBorder = new LeftBorder();
			border.RightBorder = new RightBorder();
			border.TopBorder = new TopBorder();
			border.BottomBorder = new BottomBorder();
			border.DiagonalBorder = new DiagonalBorder();
			borders.Append(border);

			border = new Border();
			border.LeftBorder = new LeftBorder();
			border.LeftBorder.Style = BorderStyleValues.Thin;
			border.RightBorder = new RightBorder();
			border.RightBorder.Style = BorderStyleValues.Thin;
			border.TopBorder = new TopBorder();
			border.TopBorder.Style = BorderStyleValues.Thin;
			border.BottomBorder = new BottomBorder();
			border.BottomBorder.Style = BorderStyleValues.Thin;
			border.DiagonalBorder = new DiagonalBorder();
			borders.Append(border);
			borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

			CellStyleFormats csfs = new CellStyleFormats();
			CellFormat cf = new CellFormat();
			cf.NumberFormatId = 0;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			csfs.Append(cf);
			csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);

			uint iExcelIndex = 164;
			NumberFormats nfs = new NumberFormats();
			CellFormats cfs = new CellFormats();

			cf = new CellFormat();
			cf.NumberFormatId = 0;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cfs.Append(cf);

			NumberFormat nfDateTime = new NumberFormat();
			nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
			nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
			nfs.Append(nfDateTime);

			NumberFormat nf4decimal = new NumberFormat();
			nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
			nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
			nfs.Append(nf4decimal);

			// #,##0.00 is also Excel style index 4
			NumberFormat nf2decimal = new NumberFormat();
			nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
			nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
			nfs.Append(nf2decimal);

			// @ is also Excel style index 49
			NumberFormat nfForcedText = new NumberFormat();
			nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
			nfForcedText.FormatCode = StringValue.FromString("@");
			nfs.Append(nfForcedText);

			// index 1
			cf = new CellFormat();
			cf.NumberFormatId = nfDateTime.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 2
			cf = new CellFormat();
			cf.NumberFormatId = nf4decimal.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 3
			cf = new CellFormat();
			cf.NumberFormatId = nf2decimal.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 4
			cf = new CellFormat();
			cf.NumberFormatId = nfForcedText.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 5
			// Header text
			cf = new CellFormat();
			cf.NumberFormatId = nfForcedText.NumberFormatId;
			cf.FontId = 1;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 6
			// column text
			cf = new CellFormat();
			cf.NumberFormatId = nfForcedText.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 1;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 7
			// coloured 2 decimal text
			cf = new CellFormat();
			cf.NumberFormatId = nf2decimal.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 2;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			// index 8
			// coloured column text
			cf = new CellFormat();
			cf.NumberFormatId = nfForcedText.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 2;
			cf.BorderId = 1;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
			cfs.Append(cf);

			nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
			cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);

			ss.Append(nfs);
			ss.Append(fts);
			ss.Append(fills);
			ss.Append(borders);
			ss.Append(csfs);
			ss.Append(cfs);

			CellStyles css = new CellStyles();
			CellStyle cs = new CellStyle();
			cs.Name = StringValue.FromString("Normal");
			cs.FormatId = 0;
			cs.BuiltinId = 0;
			css.Append(cs);
			css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
			ss.Append(css);

			DifferentialFormats dfs = new DifferentialFormats();
			dfs.Count = 0;
			ss.Append(dfs);

			TableStyles tss = new TableStyles();
			tss.Count = 0;
			tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
			tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
			ss.Append(tss);

			return ss;
		}

		private static Row CreateHeader(UInt32 index)
		{
			Row r = new Row();
			r.RowIndex = index;

			Cell c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 5;
			c.CellReference = "A" + index.ToString();
			c.CellValue = new CellValue("Congratulations! You can now create Excel Open XML styles.");
			r.Append(c);

			return r;
		}

		private static Row CreateColumnHeader(UInt32 index)
		{
			Row r = new Row();
			r.RowIndex = index;

			Cell c;
			c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 6;
			c.CellReference = "A" + index.ToString();
			c.CellValue = new CellValue("Product ID");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 6;
			c.CellReference = "B" + index.ToString();
			c.CellValue = new CellValue("Date/Time");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 6;
			c.CellReference = "C" + index.ToString();
			c.CellValue = new CellValue("Duration");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 6;
			c.CellReference = "D" + index.ToString();
			c.CellValue = new CellValue("Cost");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.StyleIndex = 8;
			c.CellReference = "E" + index.ToString();
			c.CellValue = new CellValue("Revenue");
			r.Append(c);

			return r;
		}

		private static Row CreateContent(UInt32 index, ref Random rd)
		{
			Row r = new Row();
			r.RowIndex = index;

			Cell c;
			c = new Cell();
			c.CellReference = "A" + index.ToString();
			c.CellValue = new CellValue(rd.Next(1000000000).ToString("d9"));
			r.Append(c);

			DateTime dtEpoch = new DateTime(1900, 1, 1, 0, 0, 0, 0);
			DateTime dt = dtEpoch.AddDays(rd.NextDouble() * 100000.0);
			TimeSpan ts = dt - dtEpoch;
			double fExcelDateTime;
			// Excel has "bug" of treating 29 Feb 1900 as valid
			// 29 Feb 1900 is 59 days after 1 Jan 1900, so just skip to 1 Mar 1900
			if (ts.Days >= 59)
			{
				fExcelDateTime = ts.TotalDays + 2.0;
			}
			else
			{
				fExcelDateTime = ts.TotalDays + 1.0;
			}
			c = new Cell();
			c.StyleIndex = 1;
			c.CellReference = "B" + index.ToString();
			c.CellValue = new CellValue(fExcelDateTime.ToString());
			r.Append(c);

			c = new Cell();
			c.StyleIndex = 2;
			c.CellReference = "C" + index.ToString();
			c.CellValue = new CellValue(((double)rd.Next(10, 10000000) + rd.NextDouble()).ToString("f4"));
			r.Append(c);

			c = new Cell();
			c.StyleIndex = 3;
			c.CellReference = "D" + index.ToString();
			c.CellValue = new CellValue(((double)rd.Next(10, 10000) + rd.NextDouble()).ToString("f2"));
			r.Append(c);

			c = new Cell();
			c.StyleIndex = 7;
			c.CellReference = "E" + index.ToString();
			c.CellValue = new CellValue(((double)rd.Next(10, 1000) + rd.NextDouble()).ToString("f2"));
			r.Append(c);

			return r;
		}
	}
}