龙空技术网

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

中年农码工 624

前言:

现在我们对“html读取access”大约比较重视,姐妹们都想要剖析一些“html读取access”的相关知识。那么小编在网摘上搜集了一些对于“html读取access””的相关文章,希望各位老铁们能喜欢,咱们一起来学习一下吧!

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

虽然代码方面不适用(我好像也没仔细看过代码),但是感谢大佬给了灵感,将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= ";)]        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

标签: #html读取access #winform读取excel