C# Npoi如何讀取單元格圖片并獲取所在單元格位置

更新時(shí)間：2025年04月29日 09:43:25 作者：umarururururu

這篇文章主要介紹了C# Npoi如何讀取單元格圖片并獲取所在單元格位置,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

C# Npoi讀取單元格圖片并獲取所在單元格位置

C#在excel中讀取圖片獲取圖片的單元格位置信息(僅限于xlsx)

主要的代碼邏輯

將excel使用zip直接打開
壓縮包內(nèi)圖片信息主要存放在兩個(gè)文件夾中(xl/media，xl/drawings)前者是圖片本身，后者是圖片的主要信息
在drawings文件夾下會(huì)存在若干以drawing開頭的.xml文件,一個(gè)sheet頁對(duì)應(yīng)一個(gè)drawing.xml后綴按照數(shù)字序號(hào)遞增(例如:第一頁為drawing1.xml,第二頁為drawing2.xml以此類推)
打開drawing.xml后,讀取文檔節(jié)點(diǎn),節(jié)點(diǎn)含義對(duì)照表在末尾
在pic標(biāo)簽中獲取到圖片的rid后通過該id在同級(jí)目錄下的_rels文件夾下找到對(duì)應(yīng)的drawing.xml.rels中讀取圖片的存放地址(例如drawing1.xml對(duì)應(yīng)drawing1.xml.rels)

twoCellAnchor	圖片信息的節(jié)點(diǎn) 節(jié)點(diǎn)下包含了圖片的起始單元格位置圖片的id即[r:embed]的內(nèi)容存在多少個(gè)[twoCellAnchor]即存在多少個(gè)圖片
twoCellAnchor/from	圖片左上角開始位置單元格信息
twoCellAnchor/to	圖片右下角結(jié)束位置單元格信息 (當(dāng)前需求需要盡量保證一張圖片存在一個(gè)單元格中,不允許跨單元格因?yàn)椴粫?huì)對(duì)其做其他的處理)
twoCellAnchor/pic	圖片的文件信息主要讀取 [blipFill->blip->r:embed]此信息為圖片的id信息可以讀取到文件在media文件夾中存放的位置
twoCellAnchor/xfrm	待確定該信息,主要猜想為圖片的大小?
twoCellAnchor/prstGeom	圖片的插入方式

以下是封裝完成的代碼

 public class ExcelImgHelper
 {
     #region 常量

     /// <summary>
     /// 文件id與文件路徑文件夾路徑
     /// </summary>
     private const string DrawingRels = "xl/drawings/_rels/drawing_id_.xml.rels";

     /// <summary>
     /// 圖片信息文件
     /// </summary>
     private const string Drawing = "xl/drawings/drawing_id_.xml";

     #region 圖片信息主要標(biāo)簽

     private const string twoCellAnchor = "twoCellAnchor";
     private const string embed = "embed";
     private const string link = "link";
     private const string prst = "prst";

     #endregion 圖片信息主要標(biāo)簽

     #endregion 常量

     #region 路徑信息

     /// <summary>
     /// excel文件地址
     /// </summary>
     private string ExcelPath { get; }

     /// <summary>
     /// 解壓的文件夾
     /// </summary>
     private string ExcelZipPath { get; }

     /// <summary>
     /// 壓縮包 *注意 與上方需要區(qū)分開當(dāng)前路勁是壓縮文件包.zip
     /// </summary>
     private string ExcelZipFilePath { get; }

     #endregion 路徑信息

     private List<ExcelImgInfo> ExcelImgInfos = new List<ExcelImgInfo>();

     public ExcelImgHelper(string filePath)
     {
         if (string.IsNullOrEmpty(filePath))
         {
             throw new ArgumentNullException(nameof(filePath));
         }

         //解壓后文件夾存放的位置 與源文件同目錄
         var dir = Path.GetDirectoryName(filePath);
         //獲取文件名
         var fileName = Path.GetFileNameWithoutExtension(filePath);
         //壓縮包路徑
         var zipFilePath = dir + "\\" + fileName + ".zip";

         //復(fù)制為壓縮包
         File.Copy(filePath, zipFilePath);

         //解壓文件
         if (UnZipFile(filePath, out string UnZipFilePath))
         {
             ExcelPath = filePath;
             ExcelZipPath = UnZipFilePath;
             ExcelZipFilePath = zipFilePath;

             ExcelImgInfos = Analysis();
         }
         else
         {
             throw new Exception("解壓失敗");
         }
     }

     /// <summary>
     /// 解析excel中的圖片
     /// </summary>
     /// <returns></returns>
     private List<ExcelImgInfo> Analysis()
     {
         List<ExcelImgPathAndId> imgs = new List<ExcelImgPathAndId>();
         List<ExcelImgInfo> excelImgInfos = new List<ExcelImgInfo>();

         //讀取所有圖片以及位置信息
         FindPicPathByID(ref imgs);

         //默認(rèn)命名空間
         XNamespace xdr_namespace = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing";
         XNamespace a_namespace = "http://schemas.openxmlformats.org/drawingml/2006/main";
         XNamespace r_namespace = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";

         //加載圖片信息文檔xml(替換的文件名與頁數(shù)對(duì)應(yīng) 此處可以優(yōu)化為傳入指定id)
         XDocument xdoc = XDocument.Load(ExcelZipPath + Drawing.Replace("_id_", "1"));

         //加載文檔中的默認(rèn)命名空間
         var root = xdoc.Root;
         foreach (var item in root.Attributes())
         {
             if (item.Name.LocalName == "xdr")
             {
                 xdr_namespace = item.Value;
             }
             else if (item.Name.LocalName == "a")
             {
                 a_namespace = item.Value;
             }
         }

         //讀取twoCellAnchor標(biāo)簽中的內(nèi)容 **核心部分**
         foreach (var node in xdoc.Descendants(xdr_namespace + twoCellAnchor))
         {
             //twoCellAnchor標(biāo)簽中子標(biāo)簽內(nèi)容順序永遠(yuǎn)為:from->to->pic
             //所以此處順序讀取即可
             var NodeFrom = (XElement)node.FirstNode;
             var NodeTo = (XElement)NodeFrom.NextNode;
             var NodePic = (XElement)NodeTo.NextNode;

             //找到blipFill節(jié)點(diǎn),并找到r節(jié)點(diǎn)的命名空間
             var blipFill = (XElement)(((XElement)NodePic.FirstNode.NextNode).FirstNode);
             r_namespace = blipFill.FirstAttribute.IsNamespaceDeclaration ? blipFill.FirstAttribute.Value : r_namespace;

             //找到spPr節(jié)點(diǎn)
             var spPr = (XElement)NodePic.FirstNode.NextNode.NextNode;

             //獲取圖片Id
             var ImgId = (blipFill.Attribute(r_namespace + embed) != null ? blipFill.Attribute(r_namespace + embed) : blipFill.Attribute(r_namespace + link)).Value.ToString();

             //獲取from
             var From = new Position()
             {
                 Col = int.Parse(((XElement)NodeFrom.FirstNode).Value),
                 ColOff = int.Parse(((XElement)NodeFrom.FirstNode.NextNode).Value),
                 Row = int.Parse(((XElement)NodeFrom.FirstNode.NextNode.NextNode).Value),
                 RowOff = int.Parse(((XElement)NodeFrom.FirstNode.NextNode.NextNode.NextNode).Value)
             };
             //獲取to
             var To = new Position()
             {
                 Col = int.Parse(((XElement)NodeTo.FirstNode).Value),
                 ColOff = int.Parse(((XElement)NodeTo.FirstNode.NextNode).Value),
                 Row = int.Parse(((XElement)NodeTo.FirstNode.NextNode.NextNode).Value),
                 RowOff = int.Parse(((XElement)NodeTo.FirstNode.NextNode.NextNode.NextNode).Value)
             };

             //獲取圖片插入方式
             var PrstGeom = ((XElement)spPr.FirstNode.NextNode).Attribute(prst).Value.ToString();

             //
             var xfrm = ((XElement)spPr.FirstNode);
             var xfrm_off = ((XElement)xfrm.FirstNode);
             var xfrm_ext = ((XElement)xfrm.FirstNode.NextNode);
             List<int> xfrm_offData = new List<int>
                 {
                     int.Parse(xfrm_off.Attribute("x").Value.ToString()),
                     int.Parse(xfrm_off.Attribute("y").Value.ToString())
                 };
             List<int> xfrm_extData = new List<int>
                 {
                     int.Parse(xfrm_ext.Attribute("cx").Value.ToString()),
                     int.Parse(xfrm_ext.Attribute("cy").Value.ToString())
                 };

             //獲取圖片實(shí)際位置
             var PathOfPicture = imgs.FirstOrDefault(e => e.Id == ImgId)?.Path;
             //此處圖片為相對(duì)位置需要處理成為絕對(duì)路徑
             PathOfPicture = PathOfPicture.Replace("../", ExcelZipPath + "xl\\").Replace("/", "\\");

             //至此 所有需要使用的節(jié)點(diǎn)全部取出 開始組裝數(shù)據(jù)
             ExcelImgInfo excelImgInfo = new ExcelImgInfo(
                 imgId: ImgId,
                 from: From,
                 to: To,
                 prstGeom: PrstGeom,
                 xfrm_off: xfrm_offData,
                 xfrm_ext: xfrm_extData,
                 pathOfPicture: PathOfPicture);

             excelImgInfos.Add(excelImgInfo);
         }
         //Dispose();
         return excelImgInfos;
     }

     /// <summary>
     /// 解壓文件
     /// </summary>
     /// <param name="zipFilePath">壓縮文件路徑</param>
     /// <param name="path">返回壓縮文件夾路徑</param>
     /// <param name="unZipDir">解壓文件存放路徑,為空時(shí)默認(rèn)與壓縮文件同一級(jí)目錄下，跟壓縮文件同名的文件夾</param>
     /// <returns></returns>
     private bool UnZipFile(string zipFilePath, out string path, string unZipDir = null)
     {
         if (zipFilePath == string.Empty)
         {
             path = null;
             return false;
         }

         if (!System.IO.File.Exists(zipFilePath))
         {
             path = null;
             return false;
         }
         //解壓文件夾為空時(shí)默認(rèn)與壓縮文件同一級(jí)目錄下，跟壓縮文件同名的文件夾
         if (string.IsNullOrWhiteSpace(unZipDir))
             unZipDir = zipFilePath.Replace(Path.GetFileName(zipFilePath), Path.GetFileNameWithoutExtension(zipFilePath));

         if (!unZipDir.EndsWith("\\"))
             unZipDir += "\\";

         if (!Directory.Exists(unZipDir))
             Directory.CreateDirectory(unZipDir);
         try
         {
             using (ZipInputStream s = new ZipInputStream(System.IO.File.OpenRead(zipFilePath)))
             {
                 ZipEntry theEntry;
                 while ((theEntry = s.GetNextEntry()) != null)
                 {
                     string directoryName = Path.GetDirectoryName(theEntry.Name);
                     string fileName = Path.GetFileName(theEntry.Name);
                     if (directoryName.Length > 0)
                     {
                         Directory.CreateDirectory(unZipDir + directoryName);
                     }
                     if (!directoryName.EndsWith("\\"))
                         directoryName += "\\";
                     if (fileName != String.Empty)
                     {
                         using (FileStream streamWriter = System.IO.File.Create(unZipDir + theEntry.Name))
                         {
                             int size = 2048;
                             byte[] data = new byte[2048];
                             while (true)
                             {
                                 size = s.Read(data, 0, data.Length);
                                 if (size > 0)
                                 {
                                     streamWriter.Write(data, 0, size);
                                 }
                                 else
                                 {
                                     break;
                                 }
                             }
                         }
                     }
                 }
             }
         }
         catch
         {
             path = null;
             return false;
         }
         path = unZipDir;

         return true;
     }

     /// <summary>
     /// 獲取全部文件id與路徑
     /// </summary>
     /// <param name="imgs"></param>
     /// <param name="_id"></param>
     private void FindPicPathByID(ref List<ExcelImgPathAndId> imgs, int _id = 1)
     {
         string _file = Path.Combine(ExcelZipPath + DrawingRels.Replace("_id_", _id.ToString()));

         if (!File.Exists(_file))
         {
             throw new DirectoryNotFoundException(_file);
         }

         XDocument xDoc = XDocument.Load(_file);
         var root = xDoc.Root;
         foreach (XElement node in root.Nodes())
         {
             var attrs = node.Attributes();
             string Id = "";
             string Target = "";
             foreach (var attr in attrs)
             {
                 if (attr.Name == "Id")
                     Id = attr.Value.ToString();
                 else if (attr.Name == "Target")
                     Target = attr.Value.ToString();
             }
             imgs.Add(new ExcelImgPathAndId()
             {
                 Id = Id,
                 Path = Target
             });
         }
     }

     /// <summary>
     /// 獲取excel圖片以及位置信息
     /// </summary>
     /// <returns></returns>
     public List<ExcelImgInfo> GetAllImgs()
     {
         return ExcelImgInfos;
     }

     /// <summary>
     /// 刪除解壓的文件
     /// </summary>
     public void Dispose()
     {
         File.Delete(ExcelZipFilePath);
         DirectoryInfo di = new DirectoryInfo(ExcelZipPath);
         di.Delete(true);
     }
 }

需要用到的輔助類

 /// <summary>
 /// 提取出來的圖片信息類
 /// </summary>
 public class ExcelImgInfo
 {
     public ExcelImgInfo()
     {
     }

     public ExcelImgInfo(string imgId, Position from, Position to, string prstGeom, List<int> xfrm_off, List<int> xfrm_ext, string pathOfPicture)
     {
         try
         {
             ImgId = imgId;
             From = from;
             To = to;
             PrstGeom = prstGeom;
             this.xfrm_off = xfrm_off;
             this.xfrm_ext = xfrm_ext;
             PathOfPicture = pathOfPicture;

             if (File.Exists(PathOfPicture))
             {
                 //將圖片讀取到內(nèi)存中并且不鎖定文件
                 FileStream fileStream = new FileStream(PathOfPicture, FileMode.Open, FileAccess.Read);

                 int byteLength = (int)fileStream.Length;
                 byte[] fileBytes = new byte[byteLength];
                 fileStream.Read(fileBytes, 0, byteLength);
                 fileStream.Close();

                 using (MemoryStream ms = new MemoryStream(fileBytes))
                 {
                     ms.Write(fileBytes, 0, fileBytes.Length);
                     ExcelImage = Image.FromStream(ms, true);
                 }
                 imgByteArray = fileBytes;
             }
             else
             {
                 throw new FileNotFoundException("圖片位置錯(cuò)誤");
             }
         }
         catch (Exception e)
         {
             throw new Exception($" 圖片對(duì)象初始化時(shí)錯(cuò)誤:[{imgId}]\n{e.Message}\n{e.StackTrace}");
         }
     }

     /// <summary>
     /// 圖片Id
     /// </summary>
     public string ImgId { get; protected set; }

     /// <summary>
     /// 開始的單元格
     /// </summary>
     public Position From { get; protected set; }

     /// <summary>
     /// 結(jié)束的單元格
     /// </summary>
     public Position To { get; protected set; }

     /// <summary>
     /// 圖片插入方式
     /// </summary>
     public string PrstGeom { get; protected set; }

     /// <summary>
     /// [0]:x
     /// [1]:y
     /// </summary>
     public List<int> xfrm_off { get; protected set; } = new List<int>();

     /// <summary>
     /// [0]:cx
     /// [1]:cy
     /// </summary>
     public List<int> xfrm_ext { get; protected set; } = new List<int>();

     /// <summary>
     /// 圖片地址
     /// </summary>
     public string PathOfPicture { get; protected set; }

     /// <summary>
     /// 圖片數(shù)據(jù)
     /// </summary>
     public Image ExcelImage { get; protected set; }

     /// <summary>
     /// 圖片數(shù)組
     /// </summary>
     public byte[] imgByteArray { get; protected set; }

     public void Dispose()
     {
         //ExcelImage.Dispose();
     }
 }

 /// <summary>
 /// 文件Id與路徑
 /// </summary>
 public class ExcelImgPathAndId
 {
     public string Id { get; set; }

     public string Path { get; set; }
 }

 /// <summary>
 /// 位置信息
 /// </summary>
 public class Position
 {
     public int Col { get; set; }
     public int ColOff { get; set; }
     public int Row { get; set; }
     public int RowOff { get; set; }
 }