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 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; }