Fork me on GitHub

NPOI获取Excel文件里的形状/图片的坐标/锚点

有个妹纸找我请教如何获取图片坐标,因此我到家后花了点时间,写了这份代码。

实测下来,可以正确获取 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 规范,因此获取的坐标不准。

posted @ 2022-04-14 00:47  VAllen  阅读(462)  评论(0编辑  收藏  举报