c# winform使用NOPI读取Excel读取图片

  • c# winform使用NOPI读取Excel读取图片已关闭评论
  • 18 次浏览
  • A+
所属分类:.NET技术
摘要

需求:在Winform使用NOPI做导入时候,需要导入数据的同时导入图片。虽然代码方面不适用(我好像也没仔细看过代码),但是感谢大佬给了灵感http://www.wjhsh.net/IT-Ramon-p-13100039.html,将excel后缀修改成Zip,解压,在其中找到图片的xml位置信息,以及对应的xml图片

需求:在Winform使用NOPI做导入时候,需要导入数据的同时导入图片。

虽然代码方面不适用(我好像也没仔细看过代码),但是感谢大佬给了灵感http://www.wjhsh.net/IT-Ramon-p-13100039.html,将excel后缀修改成Zip,解压,在其中找到图片的xml位置信息,以及对应的xml图片

效果图:

c# winform使用NOPI读取Excel读取图片

 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);//获取 yyyyMMddHHssmmxldrawings target ../media/image1.png 转换为yyyyMMddHHssmmxlmediaimage1.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);//上传后转换成服务器路径 \UploadImage202211030918546.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);             }         }

过程:

 

 作者:兮去博客
出处:https://www.cnblogs.com/bklsj/p/16784749.html
版权:本文版权归作者和博客园共有
转载:欢迎转载,但未经作者同意,必须保留此段声明;必须在文章中给出原文连接;否则必究法律责任