C#winform使用NOPI讀取Excel讀取圖片

需求：在Winform使用NOPI做導入時候，需要導入數據的同時導入圖片。

雖然代碼方面不適用(我好像也沒仔細看過代碼)，但是感謝大佬給了靈感http://www.wjhsh.net/IT-Ramon-p-13100039.html，將excel尾碼修改成Zip,解壓，在其中找到圖片的xml位置信息，以及對應的xml圖片

效果圖：

 1、核心讀取代碼

        System.Data.DataTable dt;
        private void btnImport_Click(object sender, EventArgs e)
        {
            dt = null;
            string copyAfterFileName = DateTime.Now.ToString("yyyyMMddHHssmm");//文件名 以及解壓後的文件夾名
            string copyAfterFileNameExt = ".zip";//壓縮尾碼
            string modelExlPath = Environment.CurrentDirectory + "\\Temp\\Cache";//緩存文件
            if (!Directory.Exists(modelExlPath)) { Directory.CreateDirectory(modelExlPath); }//創建緩存文件夾
            string copyAfterFullName = Path.Combine(modelExlPath, copyAfterFileName + copyAfterFileNameExt);//移動到該目錄並改為壓縮包
            string copyDirFullName = Path.Combine(modelExlPath, copyAfterFileName);//解壓後的文件夾位置

            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "導入Excel(*.xls,*.xlsx)|*.xls;*.xlsx";
            openfile.FilterIndex = 0;
            openfile.RestoreDirectory = true;
            openfile.Title = "導入文件路徑";
            //openfile.ShowDialog();
            if (openfile.ShowDialog() != DialogResult.OK)
            {
                return;
            }

           Common.ShowWaitForm();//正在載入..視窗
            FileInfo fi1 = new FileInfo(openfile.FileName);
            fi1.CopyTo(copyAfterFullName);//移動文件，並修改稱為yyyyMMddHHssmm.zip

            try
            {
                wsDr wsDrModel = null;
                if (!Directory.Exists(copyDirFullName)) { Directory.CreateDirectory(copyDirFullName); }
                //解壓到當前文件夾
                if (SharpZip.UnpackFiles(copyAfterFullName, copyDirFullName) == false)//yyyyMMddHHssmm.zip為文件夾yyyyMMddHHssmm
                {
                    Common.ShowErrorDialog("導入失敗！");
                    LogHelper.Instance.Error("產品導入失敗，" + copyDirFullName + "自解壓失敗！");
                    return;
                }
                else
                {
                    wsDrModel = GetImgLoaction(copyDirFullName);//讀取excel圖片信息，對應位置，對應的絕對路徑，對應的圖片實體
                }

                string msg = "";
                dt = ExcelUtil.ExcelToTable(openfile.FileName, wsDrModel, ref msg);//獲得Excel
                if (!string.IsNullOrEmpty(msg))
                {
                   Common.ShowInfoDialog(msg);
                }
                if (dt == null || dt.Rows.Count <= 0)
                {
                   Common.ShowSuccessTip("導入失敗！");
                    return;
                }
                LoadList();//刷新表格
            }
            catch (Exception ex)
            {
               Common.ShowErrorDialog("導入錯誤！" + ex.Message);
            }
            finally
            {
                Common.HideWaitForm();//隱藏正在載入..視窗
            }
        }

        private wsDr GetImgLoaction(string copydirfullname)
        {
            //copydirfullname = "E:\\administrator\\Desktop\\test\\yyyyMMddHHssmm";
            string pathMap = Path.Combine(copydirfullname, "xl\\drawings\\drawing1.xml");
            var doc = XDocument.Load(pathMap);
            //清理大部分命名空間，blip屬性中的情況只能手動指定了
            doc.Descendants().Attributes().Where(x => x.IsNamespaceDeclaration).Remove();
            foreach (var elem in doc.Descendants())
            {
                elem.Name = elem.Name.LocalName;
            }
            doc.Save(pathMap);
            wsDr model = XmlHelper.XmlToModelFile<wsDr>(pathMap);
            Relationships relationships = GetImg(copydirfullname);

            if (model.twoCellAnchorList.Count > 0)
            {
                //將圖片路徑，圖片實體保存在圖片位置表(wsDr)中
                foreach (var item in model.twoCellAnchorList)
                {
                    item.pic1.nvPicPr1.cNvPr1.img = relationships.Relationship.SingleOrDefault(a => a.Id == item.pic1.blipFill1.blip1.imgid).img;
                    item.pic1.nvPicPr1.cNvPr1.imgUrl = relationships.Relationship.SingleOrDefault(a => a.Id == item.pic1.blipFill1.blip1.imgid).imgUrl;
                }
            }
            return model;
        }
        private Relationships GetImg(string copydirfullname)
        {
            string pathMap = Path.Combine(copydirfullname, "xl\\drawings\\_rels\\drawing1.xml.rels");//獲取圖片所在目錄的xml.rels
            FileInfo fi = new FileInfo(pathMap);

            string newpathMap = Path.Combine(copydirfullname, "xl\\drawings\\_rels\\drawing1.xml");//轉換為xml，不然不可讀取
            if (fi.Exists)
            {
                fi.MoveTo(newpathMap);
            }

            //代碼可用
            var doc = XDocument.Load(newpathMap);
            doc.Descendants().Attributes().Where(x => x.IsNamespaceDeclaration).Remove();//移除大部分命名空間
            foreach (var elem in doc.Descendants())
                elem.Name = elem.Name.LocalName;
            doc.Save(newpathMap);

            Relationships model = XmlHelper.XmlToModelFile<Relationships>(newpathMap);//獲取圖片所在目錄的xml
            string pathMap1 = Path.Combine(copydirfullname, "xl\\drawings\\drawing1.xml");//../media/image1.png 是以xl/drawings文件夾的基礎上，而不是xl/drawings/_rels
            foreach (var item in model.Relationship)
            {
                string newpath = GetPath(Path.GetFullPath(pathMap1), item.Target);//獲取 yyyyMMddHHssmm\xl\drawings target ../media/image1.png 轉換為yyyyMMddHHssmm\xl\media\image1.png
                //保存路徑，該路徑在上傳圖片時使用
                item.imgUrl = newpath;
                //以流形式讀取圖片，不占用圖片
                using (var stream = new FileStream(newpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite | FileShare.Delete))
                {
                    item.img = Image.FromStream(stream);
                }
            }
            return model;
        }
        private string GetPath(string sourPath, string path)
        {
            string[] pathT = path.Split('/');
            string newpath = sourPath;
            for (int i = 0; i < pathT.Length; i++)
            {

                if (pathT[i] == "..")
                {
                    DirectoryInfo di = new DirectoryInfo(Path.GetDirectoryName(newpath));

                    newpath = di.Parent.FullName;
                }
                else
                {
                    newpath = Path.GetFullPath(Path.Combine(newpath, pathT[i]));
                }
            }
            return newpath;
        }

2、根據xml生成的實體，並處理後

  #region 主要實體
    //實體主要部分，根據xml生成
    [Serializable]
    public class wsDr
    {
        [XmlElement(ElementName = "twoCellAnchor")]//指定節點名稱
        public List<twoCellAnchor> twoCellAnchorList { get; set; }

    }
    //讀取圖片實體
    public class Relationships
    {
        [XmlElement("Relationship")]
        public List<Relationship> Relationship { get; set; }

    }
    #endregion
    #region 其他實體
    public class from
    {
        //這裡列名稱和節點名稱相同，不需要特意指定xml節點名稱
        public int col { get; set; }
        public int row { get; set; }

    }
    public class to
    {
        public int col { get; set; }
        public int row { get; set; }
    }
    public class cNvPr
    {
        [XmlAttribute( "id")]
        public string _id { get; set; }
        [XmlAttribute("name")]
        public string _name { get; set; }
        public Image img { get; set; }
        public string imgUrl { get; set; }
    }
    public class blip
    {
        /*手動指定xml命名空間，該命名空間使用..Where(x => x.IsNamespaceDeclaration).Remove()移除不了。
         * 註：xml中這塊讀取出來後不認為它是命名空間,因為是子級特意指定的
         */
        [XmlAttribute("embed",Namespace= "http://schemas.openxmlformats.org/officeDocument/2006/relationships")]
        public string imgid { get; set; }

    }
    public class blipFill
    {
        [XmlElement("blip")]
        public blip blip1 { get; set; }

    }
    public class nvPicPr
    {
        [XmlElement(ElementName = "cNvPr")]
        public cNvPr cNvPr1 { get; set; }
      
    }
    public class pic
    {
        [XmlElement(ElementName = "nvPicPr")]
        public nvPicPr nvPicPr1 { get; set; }
        [XmlElement("blipFill")]
        public blipFill blipFill1 { get; set; }
    }
    public class twoCellAnchor
    {
        [XmlElement(ElementName = "from")]
        public from from1 { get; set; }
        [XmlElement(ElementName = "to")]
        public to to1 { get; set; }
        [XmlElement(ElementName = "pic")]
        public pic pic1 { get; set; }

    }
  
    public class Relationship
    {
        [XmlAttribute("Id")]
        public string Id { get; set; }
        [XmlAttribute("Target")]
        public string Target { get; set; }
        public Image img { get; set; }
        public string imgUrl { get; set; }
    }
    #endregion

3、使用NOPI讀取Excel內容

private static ISheet ExcelToSheet(string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 適用XLSX格式，HSSFWorkbook 適用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(0);
                return sheet;
            }
        }
        /// <summary>
        /// Excel導入成Datable
        /// </summary>
        /// <param name="file">導入路徑(包含文件名與擴展名)</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file, wsDr wsDrModel, ref string msg)
        {
            try
            {
                ISheet sheet = ExcelToSheet(file);
                DataTable dt = new DataTable();
                //表頭  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                //List<int> columns = new List<int>();
                int columnsCount = 1;
                dt.Columns.Add(new DataColumn("Id"));
                dt.Columns.Add(new DataColumn("dnxh"));
                dt.Columns.Add(new DataColumn("dwxh"));
                dt.Columns.Add(new DataColumn("zwmc"));
                dt.Columns.Add(new DataColumn("ywmc"));
                dt.Columns.Add(new DataColumn("cplx"));
                dt.Columns.Add(new DataColumn("dw"));
                dt.Columns.Add(new DataColumn("sfwgcp"));
                dt.Columns.Add(new DataColumn("sflscp"));
                dt.Columns.Add(new DataColumn("mlj"));
                dt.Columns.Add(new DataColumn("Img", typeof(Image)));
                dt.Columns.Add(new DataColumn("cpcc"));
                dt.Columns.Add(new DataColumn("cpjz"));
                dt.Columns.Add(new DataColumn("ImgUrl"));

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    object dnxh = sheet.GetRow(i).GetCell(0).GetRealValue();//對內型號
                    if (dnxh == null || string.IsNullOrEmpty(dnxh.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【對內型號】!";
                        break;
                    }
                    dr["dnxh"] = dnxh;
                    object dwxh = sheet.GetRow(i).GetCell(1).GetRealValue();//對外型號
                    if (dwxh == null || string.IsNullOrEmpty(dwxh.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【對外型號】!";
                        break;
                    }
                    dr["dwxh"] = dwxh;
                    object zwmc = sheet.GetRow(i).GetCell(2).GetRealValue();//zwmc
                    if (zwmc == null || string.IsNullOrEmpty(zwmc.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【中文名稱】!";
                        break;
                    }
                    dr["zwmc"] = zwmc;
                    object ywmc = sheet.GetRow(i).GetCell(3).GetRealValue();//英文名稱
                    if (ywmc == null || string.IsNullOrEmpty(ywmc.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【英文名稱】!";
                        break;
                    }
                    dr["ywmc"] = ywmc;
                    object cplx = sheet.GetRow(i).GetCell(4).GetRealValue();//產品類型
                    if (cplx == null || string.IsNullOrEmpty(cplx.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【產品類型】!";
                        break;
                    }
                    dr["cplx"] = cplx;
                    object dw = sheet.GetRow(i).GetCell(5).GetRealValue();//單位
                    if (dw == null || string.IsNullOrEmpty(dw.ToString()))
                    {
                        msg += "請填寫第" + i + "行的【單位】!";
                        break;
                    }
                    dr["dw"] = dw;
                    object sfwgcp = sheet.GetRow(i).GetCell(6).GetRealValue();//是否外購
                    if (sfwgcp == null)
                    {
                        msg += "請填寫第" + i + "行的【是否外購】！";
                        break;
                    }
                    if (sfwgcp.ToString() != "自製" && sfwgcp.ToString() != "外購")
                    {
                        msg += "請填寫第" + i + "行的【是否外購】自製/外購,請勿填寫其他內容！";
                        break;
                    }
                    dr["sfwgcp"] = sfwgcp;
                    object sflscp = sheet.GetRow(i).GetCell(7).GetRealValue();//是否臨時產品
                    if (sflscp == null)
                    {
                        msg += "請填寫第" + i + "行的【是否臨時產品】！";
                        break;
                    }
                    if (sflscp.ToString() != "是" && sflscp.ToString() != "否")
                    {
                        msg += "請填寫第" + i + "行的【是否臨時產品】是/否,請勿填寫其他內容！";
                        break;
                    }
                    dr["sflscp"] = sflscp;
                    object mljobj = sheet.GetRow(i).GetCell(8).GetRealValue();//目錄價
                    decimal mlj = 0;
                    if (mljobj != null)
                    {
                        if (!decimal.TryParse(mljobj.ToString(), out mlj))
                        {
                            msg += "第" + i + "行的【目錄價】數值錯誤，請正確填寫！";
                            break;
                        }
                    }
                    dr["mlj"] = mlj;
                    object cpcc = sheet.GetRow(i).GetCell(10).GetRealValue();//產品尺寸
                    dr["cpcc"] = cpcc;
                    object cpjz = sheet.GetRow(i).GetCell(11).GetRealValue();//凈重
                    dr["cpjz"] = cpjz;
                    dr["id"] = Guid.NewGuid().ToString();
                    //-----------獲取圖片
                    //產品主圖 第9列
                    int col = 9;
                    if (wsDrModel != null && wsDrModel.twoCellAnchorList.Count > 0)
                    {
                        var list = wsDrModel.twoCellAnchorList.Where(a => i >= a.from1.row && col >= a.from1.col && i <= a.to1.row && col <= a.to1.col).ToList();
                        if (list.Count > 0)
                        {
                            dr["Img"] = (Image)list[0].pic1.nvPicPr1.cNvPr1.img;
                            dr["ImgUrl"] = list[0].pic1.nvPicPr1.cNvPr1.imgUrl;

                        }
                    }
                    //XmlHelper.SetValue
                    //XmlHelper.XmlToModel<>(pathMap);
                    //-----------
                    dt.Rows.Add(dr);
                }
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.Instance.Info(ex.ToString());
                throw ex;
            }
        }

4、保存信息

  private void btnSave_Click(object sender, EventArgs e)
        {
            List<Model.Product> listProduct = new List<Model.Product>();
         
            foreach (DataRow item in dt.Rows)
            {
                Model.Product model = new Model.Product();
               //組建信息保存到實體
                listProduct.Add(model);
            }
            if (listProduct.Count <= 0)
            {
                Common.ShowInfoTip("沒有需要保存的信息！請先導入");
                return;
            }
            Loading2.Show(this, new Action(() => UpLoadImage(listProduct)), new Action(() => this.Close()));//這個Loading非同步載入在其他文章有
        }
        bool isSuccess = false;
        private void UpLoadImage(List<Model.Product> listProduct)
        {
            List<CommandInfo> commandInfos = new List<CommandInfo>();
            foreach (var item in listProduct)
            {
                item.Cpzp = CommonUtil.UploadFile(item.Cpzp);//上傳後轉換成伺服器路徑 \\Upload\Image\202211030918546.png

                commandInfos.Add(BLLService.Instance.BaseProducts.AddSql(item));
            }
            try
            {
                if (BLLService.DoTran(commandInfos))//事務執行insert產品信息
                {
                   Common.ShowSuccessTip("導入成功！");
                    dt = null;
                    isSuccess = true;
                }
                else
                {
                    Common.ShowErrorDialog("導入失敗！");
                }
            }
            catch (Exception ex)
            {
                LogHelper.Instance.Error("導入失敗！" + ex);
                Common.ShowErrorDialog("導入失敗！" + ex);
            }
        }

過程：

將Excel尾碼換成zip解壓得到文件夾。

其中xl\drawings\drawing1.xml記錄了圖片位置信息和id。對應xl\drawings\_rels\drawing1.xml.rels中圖片位置和名稱

根據圖片位置，找到xl\media\image1.png

 作者：兮去博客
出處：https://www.cnblogs.com/bklsj/p/16784749.html
版權：本文版權歸作者和博客園共有
轉載：歡迎轉載，但未經作者同意，必須保留此段聲明；必須在文章中給出原文連接；否則必究法律責任