NPOI如何获取Excel图片坐标及锚点？
摘要：实测下来，可以正确获取Excel 2003版本的形状和图片这两种的坐标锚点，以及Excel 2007版本的图片的坐标锚点。
暂未解决如何将Excel 2007以上版本的形状(XSSFSimpleShape)转换成图片(X
有个妹纸找我请教如何获取图片坐标，因此我到家后花了点时间，写了这份代码。
实测下来，可以正确获取 Excel 2003 版本的形状和图片这两种的坐标/锚点，以及 Excel 2007 版本的图片的坐标/锚点。
暂未解决如何将 Excel 2007 以上版本的形状(XSSFSimpleShape)转换成图片(XSSFPicture)的问题？
如有大佬懂的，还请多多指教。
class Program
{
static void Main(params string[] args)
{
string excel2003FilePath = @"D:\Users\Allen\Desktop\image.xls";
GetPictureAnchorTest(excel2003FilePath);
string excel2007FilePath = @"D:\Users\Allen\Desktop\image.xlsx";
GetPictureAnchorTest(excel2007FilePath);
Console.ReadKey();
}
static void GetPictureAnchorTest(string excelFilePath)
{
IWorkbook workbook = WorkbookFactory.Create(excelFilePath);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerable<IPicture> pictures = sheet.GetPictures();
foreach (IPicture picture in pictures)
{
PictureAnchor anchor = picture.GetPictureAnchor();
Console.WriteLine($"PictureType:{picture.GetType().FullName}, LeftmostCellIndex: {anchor.LeftmostCellIndex}, RightmostCellIndex: {anchor.RightmostCellIndex}, TopmostRowIndex: {anchor.TopmostRowIndex}, BottommostRowIndex: {anchor.BottommostRowIndex}");
}
}
}
public readonly struct PictureAnchor
{
public PictureAnchor(int leftmostCellIndex, int rightmostCellIndex, int topmostRowIndex, int bottommostRowIndex)
{
LeftmostCellIndex = leftmostCellIndex;
RightmostCellIndex = rightmostCellIndex;
TopmostRowIndex = topmostRowIndex;
BottommostRowIndex = bottommostRowIndex;
}
public int LeftmostCellIndex { get; }
public int RightmostCellIndex { get; }
public int TopmostRowIndex { get; }
public int BottommostRowIndex { get; }
public override string ToString()
{
return $"LeftmostCellIndex: {LeftmostCellIndex}, RightmostCellIndex: {RightmostCellIndex}, TopmostRowIndex: {TopmostRowIndex}, BottommostRowIndex: {BottommostRowIndex}";
}
}
public static class NPOIExtensions
{
public static IEnumerable<IPicture> GetPictures(this ISheet sheet)
{
var dp = sheet.DrawingPatriarch;
// Excel 2003
if (dp is HSSFPatriarch patriarch)
{
return patriarch.GetShapes().Select(x =>
{
if (x is HSSFPicture picture)
{
return picture;
}
else
{
return new HSSFPicture(x, x.Anchor);
}
}).Cast<IPicture>();
}
// Excel 2007 above
if (dp is XSSFDrawing dr)
{
//TODO: How convert XSSFSimpleShape to XSSFPicture ???
return dr.GetShapes().Where(x => x is XSSFPicture).Cast<IPicture>();
}
throw new NotSupportedException($"Unsupported DrawingPatriarch object type：{dp.GetType().FullName}");
}
public static PictureAnchor GetPictureAnchor(this IPicture picture)
{
var anchor = picture.ClientAnchor;
return new PictureAnchor(anchor.Col1, anchor.Col2, anchor.Row1, anchor.Row2);
}
}
PS: WPS的插入图片函数 DISPIMG，是 WPS 特有的，不属于 Office 规范，因此获取的坐标不准。