Open hy-net opened 3 months ago
Hi @hy-net,
Unfortunately, that sample is out of date. in v3+ the .Close() method has been removed. That sample repo is archived, so it will be difficult to update But you can change .Close() to .Dispose() and that will resolve the issue, although the more modern and less error prone method is to use a using statement:
void CreatePackage(string sFile, string imageFileName)
{
try
{
if (File.Exists(sFile))
{
File.Delete(sFile);
}
// Create a spreadsheet document by supplying the filepath.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
}
var worksheetDrawing = drawingsPart.WorksheetDrawing;
var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
using (var stream = new FileStream(imageFileName, FileMode.Open))
{
imagePart.FeedData(stream);
}
Bitmap bm = new Bitmap(imageFileName);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 0;
var rowOffset = 0;
int colNumber = 5;
int rowNumber = 10;
var nvps = worksheetDrawing.Descendants<S.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > 0 ?
(UInt32Value)worksheetDrawing.Descendants<S.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 :
1U;
var oneCellAnchor = new S.OneCellAnchor(
new S.FromMarker
{
ColumnId = new S.ColumnId((colNumber - 1).ToString()),
RowId = new S.RowId((rowNumber - 1).ToString()),
ColumnOffset = new S.ColumnOffset(colOffset.ToString()),
RowOffset = new S.RowOffset(rowOffset.ToString())
},
new S.Extent { Cx = extentsCx, Cy = extentsCy },
new S.Picture(
new S.NonVisualPictureProperties(
new S.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFileName },
new S.NonVisualPictureDrawingProperties(new D.PictureLocks { NoChangeAspect = true })
),
new S.BlipFill(
new D.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = D.BlipCompressionValues.Print },
new D.Stretch(new D.FillRectangle())
),
new S.ShapeProperties(
new D.Transform2D(
new D.Offset { X = 0, Y = 0 },
new D.Extents { Cx = extentsCx, Cy = extentsCy }
),
new D.PresetGeometry { Preset = D.ShapeTypeValues.Rectangle }
)
),
new S.ClientData()
);
worksheetDrawing.Append(oneCellAnchor);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Describe the bug I have tried all samples / methods that I can find in the web, but none is working. All results are either the image missing or Excel throw error when opening.
To Reproduce
Use this sample below, easy to follow.
https://github.com/microsoftarchive/msdn-code-gallery-microsoft/tree/master/OneCodeTeam/How%20to%20insert%20image%20into%20Excel%20using%20Open%20XML%20SDK/%5BC%23%5D-How%20to%20insert%20image%20into%20Excel%20using%20Open%20XML%20SDK/C%23