private void ExportToTag(string filePath, MSDS_Specification model)
{
Tag_ExportModel tag = new Tag_ExportModel();
tag.SheetName_Big = "大标签";
tag.SheetName_Small = "小标签";
tag.templateFilePath = filePath;
tag.ProductName = model.CN_Name ?? model.Product_Name;
#region 危险组分
tag.HS = "组分：";
if (model.HazardousSubstance != null && model.HazardousSubstance.Count > 0)
{
int i = 1;
foreach (MSDS_HazardousSubstances hs in model.HazardousSubstance)
{
if (i > 1)
{
tag.HS += "/n";
}
tag.HS += hs.HS_Name + hs.HS_MinPercent + "-" + hs.HS_MaxPercent + "%";
i++;
}
}
else
{
tag.HS = "";
}
#endregion
#region 危险图标
List <string> imgPathList = new List <string>();
string strRootPath = Server.MapPath("~/Content/Images/");
if (model.IsExplosive)
{
imgPathList.Add(strRootPath + "Explosive.png");
}
if (model.IsFlammable)
{
imgPathList.Add(strRootPath + "Flammable.png");
}
if (model.IsCorrosive)
{
imgPathList.Add(strRootPath + "Corrosive.png");
}
if (model.IsHealthHazard)
{
imgPathList.Add(strRootPath + "HealthHazard.png");
}
if (model.IsToxic)
{
imgPathList.Add(strRootPath + "Toxic.png");
}
if (model.IsOxidizing)
{
imgPathList.Add(strRootPath + "Oxidizing.png");
}
if (model.IsGasUnderPressure)
{
imgPathList.Add(strRootPath + "GasUnderPressure.png");
}
if (model.IsIrritant)
{
imgPathList.Add(strRootPath + "Irritant.png");
}
if (model.IsDangerousToEnvironment)
{
imgPathList.Add(strRootPath + "DangerousToEnvironment.png");
}
tag.WarningPicPaths = imgPathList;
tag.BlankPicPath = strRootPath + "blank.png";
#endregion
#region 危害警示
tag.WarningContent = model.GHS_Warning;
#endregion
char[] sp = new char[1] {
','
};
#region 危害简述
tag.HazardousDescription = "";
if (!string.IsNullOrEmpty(model.GHS_HazardouDes_Values))
{
string[] names = model.GHS_HazardouDes_Values.Split(sp, StringSplitOptions.RemoveEmptyEntries);
IList <MSDS_H_Statement> h_statments = _h_StatementService.GetListByNames(names);
foreach (MSDS_H_Statement item in h_statments)
{
tag.HazardousDescription += "" + item.Description + "，";
}
tag.HazardousDescription = tag.HazardousDescription.Substring(0, tag.HazardousDescription.Length - 1);
}
else
{
tag.HazardousDescription = model.GHS_HazardouDes_Append;
}
#endregion
#region 预防措施
tag.DefenceDes = "";
if (!string.IsNullOrEmpty(model.GHS_DefenceDes_Values))
{
string[] names = model.GHS_DefenceDes_Values.Split(sp, StringSplitOptions.RemoveEmptyEntries);
IList <MSDS_P_Statement> statements = _p_StatementService.GetListByNames(names);
int i = 1;
foreach (MSDS_P_Statement item in statements)
{
tag.DefenceDes += "● " + item.Description + "\r\n";
i++;
if (i > 3)
{
break;
}
}
}
else
{
tag.DefenceDes = model.GHS_DefenceDes_Append;
}
#endregion
#region 事故响应
tag.DealDES = "";
if (!string.IsNullOrEmpty(model.GHS_DealDES_Values))
{
string[] names = model.GHS_DealDES_Values.Split(sp, StringSplitOptions.RemoveEmptyEntries);
IList <MSDS_P_Statement> statements = _p_StatementService.GetListByNames(names);
int i = 1;
foreach (MSDS_P_Statement item in statements)
{
tag.DealDES += "● " + item.Description + "\r\n";
i++;
if (i > 7)
{
break;
}
}
}
else
{
tag.DealDES = model.GHS_DealDES_Append;
}
#endregion
#region 安全储存
//tag.StoreDes = "";
//if (!string.IsNullOrEmpty(model.GHS_StoreDes_Values))
//{
// string[] names = model.GHS_StoreDes_Values.Split(sp, StringSplitOptions.RemoveEmptyEntries);
// IList<MSDS_P_Statement> statements = _p_StatementService.GetListByNames(names);
// int i = 1;
// foreach (MSDS_P_Statement item in statements)
// {
// tag.StoreDes += item.Description + ";";
// i++;
// if (i > 3) break;
// }
//}
//else
//{
// tag.StoreDes = model.GHS_StoreDes_Append;
//}
//if (tag.StoreDes?.Length > 34)
//{
// tag.StoreDes = tag.StoreDes.Substring(0, 34);
//}
tag.StoreDes = "存储化学品必须遵照国家法律、法规和其他有关的规定，化学品应有明显的标志。一般情况下，应避免阳光直射，保持通风良好，储存区域内严禁吸烟和使用明火。";
#endregion
#region 废弃处置
//if (model.Product_WasteHanding?.Length > 95)
//{
// tag.WasteHanding = model.Product_WasteHanding.Substring(0,95);
//}
//else
//{
// tag.WasteHanding = model.Product_WasteHanding;
//}
tag.WasteHanding = "请遵从当地环保法规要求";
#endregion
#region 供应商信息
tag.Supplier_Big = string.Format(@"供应商：{0}
地址：{1}
电话：{2}
应急电话：{3}",
model.Supplier_Name,
model.Supplier_Address,
model.Supplier_Phone,
model.Supplier_UrgencyCall);
tag.Supplier_Small = string.Format(@"供应商：{0}
地址：{1}
电话：{2}",
model.Supplier_Name,
model.Supplier_Address,
model.Supplier_Phone);
#endregion
_importExportService.ExportTagWithTemplate(tag);
}
public void ExportTagWithTemplate(Tag_ExportModel model)
{
FileInfo fileInfo = new FileInfo(model.templateFilePath);
if (!fileInfo.Exists)
{
throw new Exception("未找到名为 " + model.templateFilePath + " 的模板");
}
using (var xlPackage = new ExcelPackage(fileInfo))
{
var worksheet = xlPackage.Workbook.Worksheets[model.SheetName_Big];
if (worksheet == null)
{
throw new Exception("模板中未包含名为 " + model.SheetName_Big + " 的Sheet");
}
#region 大标签
var listTitle = worksheet.Drawings.Where(o => o.Name == "Title").ToList();
foreach (var item in listTitle)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.ProductName;
}
var listHS = worksheet.Drawings.Where(o => o.Name == "HS").ToList();
foreach (var item in listHS)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.HS;
}
var listWarning = worksheet.Drawings.Where(o => o.Name == "txtWarning").ToList();
foreach (var item in listWarning)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.WarningContent;
}
var listHazardouDes = worksheet.Drawings.Where(o => o.Name == "HazardouDes").ToList();
foreach (var item in listHazardouDes)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.HazardousDescription;
}
var listDefenceDes = worksheet.Drawings.Where(o => o.Name == "DefenceDes").ToList();
foreach (var item in listDefenceDes)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.DefenceDes;
}
var listDealDES = worksheet.Drawings.Where(o => o.Name == "DealDES").ToList();
foreach (var item in listDealDES)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.DealDES;
}
var listStoreDes = worksheet.Drawings.Where(o => o.Name == "StoreDes").ToList();
foreach (var item in listStoreDes)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.StoreDes;
}
var listWasteHanding = worksheet.Drawings.Where(o => o.Name == "WasteHanding").ToList();
foreach (var item in listWasteHanding)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.WasteHanding;
}
var listSupplierBig = worksheet.Drawings.Where(o => o.Name == "Supplier").ToList();
foreach (var item in listSupplierBig)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.Supplier_Big;
}
#endregion
var worksheet_small = xlPackage.Workbook.Worksheets[model.SheetName_Small];
if (worksheet_small == null)
{
throw new Exception("模板中未包含名为 " + model.SheetName_Small + " 的Sheet");
}
#region 小标签
var listTitleSmall = worksheet_small.Drawings.Where(o => o.Name == "Title").ToList();
foreach (var item in listTitleSmall)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.ProductName;
}
var listHSSmall = worksheet_small.Drawings.Where(o => o.Name == "HS").ToList();
foreach (var item in listHSSmall)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.HS;
}
var listHazardouDesSmall = worksheet_small.Drawings.Where(o => o.Name == "HazardouDes").ToList();
foreach (var item in listHazardouDesSmall)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.HazardousDescription;
}
var listSupplierSmall = worksheet_small.Drawings.Where(o => o.Name == "Supplier").ToList();
foreach (var item in listSupplierSmall)
{
ExcelShape shape = item as ExcelShape;
shape.RichText.Text = model.Supplier_Small;
}
#endregion
#region 替换警示标识
int cellCount = model.WarningPicPaths.Count;
if (cellCount == 1)
{
#region
string pName = "Picture3";
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[0]);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[0]);
}
for (int i = 1; i < 6; i++)
{
if (i == 3)
{
continue;
}
string _pName = "Picture" + i;
var _listA = worksheet.Drawings.Where(o => o.Name == _pName).ToList();
foreach (var item in _listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
var _listB = worksheet_small.Drawings.Where(o => o.Name == _pName).ToList();
foreach (var item in _listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
}
#endregion
}
else if (cellCount == 2)
{
string pName = "";
#region
for (int i = 0; i < cellCount; i++)
{
if (i == 0)
{
pName = "Picture4";
}
else
{
pName = "Picture2";
}
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
}
for (int i = 1; i < 6; i++)
{
if (i == 2 || i == 4)
{
continue;
}
pName = "Picture" + i;
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
}
#endregion
}
else if (cellCount == 3)
{
#region
string pName = "";
for (int i = 0; i < cellCount; i++)
{
if (i == 0)
{
pName = "Picture4";
}
else if (i == 1)
{
pName = "Picture3";
}
else
{
pName = "Picture2";
}
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
}
for (int i = 1; i < 6; i++)
{
if (i == 2 || i == 4 || i == 3)
{
continue;
}
pName = "Picture" + i;
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
}
#endregion
}
else if (cellCount == 4)
{
#region
string pName = "";
for (int i = 0; i < cellCount; i++)
{
if (i == 0)
{
pName = "Picture5";
}
else if (i == 1)
{
pName = "Picture4";
}
else if (i == 2)
{
pName = "Picture3";
}
else
{
pName = "Picture2";
}
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.WarningPicPaths[i]);
}
}
for (int i = 1; i < 2; i++)
{
if (i == 1)
{
pName = "Picture" + i;
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
}
}
#endregion
}
else if (cellCount == 5)
{
int k = 1;
foreach (string picUrl in model.WarningPicPaths)
{
string pName = "Picture" + k;
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(picUrl);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(picUrl);
}
k++;
}
for (int i = k; i < 6; i++)
{
string pName = "Picture" + i;
var listA = worksheet.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listA)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
var listB = worksheet_small.Drawings.Where(o => o.Name == pName).ToList();
foreach (var item in listB)
{
var img = item as ExcelPicture;
img.Image = Image.FromFile(model.BlankPicPath);
}
}
}
#endregion
xlPackage.Save();
}
}
