C#winform使用NOPI读取Excel读取图片 - ToB企服应用市场:ToB评测及商务社交产业平台

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;
}

复制代码

#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

复制代码

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;
}
}

复制代码