#region 西安报关口岸导出
/// <summary>
/// 西安报关口岸导出
/// </summary>
/// <param name="reqInfo"></param>
/// <returns></returns>
[HttpPost]
public ResultInfo<string> ExportXianDeclaration(BaseModel reqInfo)
{
var info = new ResultInfo<string>();
try
{
string where = string.Empty;
string url = string.Empty;
string sql = string.Empty;
//数据源
sql = @"SELECT W.ExpressCode,'B' AS DeclareType ,P.CIQNameCn,P.CIQName,L.ProductCode,L.Weight,L.GrossWeight,P.ProductSpec,'Null' AS SaleCity,'NULL' AS Currency,L.QuantityOrder,(L.QuantityOrder * P.DeclarePrice) AS QuantityTotalprice, --AS'zongjia',
P.DeclareUnit,W.ConsigneeName,W.CityName,W.ConsigneeAddress,W.ConsigneeMobile,W.ConsigneeIdcard,'NULL' AS PackagingType,'' AS IsWoodPackaging,'' AS IsOldArticles,'' AS IsColdchain,'' AS ProductionCountry,'' AS TradeCountry
FROM TMS_WayBill AS W INNER JOIN
TMS_WayBillList AS L ON W.WayBill_Id = L.WayBill_Id LEFT OUTER JOIN
TMS_ProductInfo_Port AS P ON L.Product_Id = P.Product_Id
WHERE W.PortName = '西安口岸' AND w.Port_Id = 7";
DataTable WayBillList = TMS_WayBillRepository.Instance.DataContext.ExecuteDataSet(CommandType.Text, sql).Tables[];
//模板路径
var path = HttpContext.Current.Server.MapPath("/Upload/ImportTemplate/报关数据(西安口岸模板).xlsx");
//指定文档
FileInfo newFile = new FileInfo(path);
//开启
using (ExcelPackage pck = new ExcelPackage(newFile))
{
//设定ExcelWorkBook
ExcelWorkbook workBook = pck.Workbook;
ExcelWorksheet currentWorksheet = pck.Workbook.Worksheets[];
if (workBook != null)
{
if (workBook.Worksheets.Count > )
{
currentWorksheet.Cells[, ].Value = Convert.ToString("分运单号");
currentWorksheet.Cells[, ].Value = Convert.ToString("申报类型");
currentWorksheet.Cells[, ].Value = Convert.ToString("物品名称");
currentWorksheet.Cells[, ].Value = Convert.ToString("英文物品名称");
currentWorksheet.Cells[, ].Value = Convert.ToString("商品编码");
currentWorksheet.Cells[, ].Value = Convert.ToString("净重(KG)");
currentWorksheet.Cells[, ].Value = Convert.ToString("毛重(KG)");
currentWorksheet.Cells[, ].Value = Convert.ToString("规格/型号");
currentWorksheet.Cells[, ].Value = Convert.ToString("产销城市");
currentWorksheet.Cells[, ].Value = Convert.ToString("币制");
currentWorksheet.Cells[, ].Value = Convert.ToString("申报数量");
currentWorksheet.Cells[, ].Value = Convert.ToString("申报总价");
currentWorksheet.Cells[, ].Value = Convert.ToString("申报计量单位");
currentWorksheet.Cells[, ].Value = Convert.ToString("收件人");
currentWorksheet.Cells[, ].Value = Convert.ToString("收件人城市");
currentWorksheet.Cells[, ].Value = Convert.ToString("收件人地址");
currentWorksheet.Cells[, ].Value = Convert.ToString("收件人电话)");
currentWorksheet.Cells[, ].Value = Convert.ToString("收发件人证件号");
currentWorksheet.Cells[, ].Value = Convert.ToString("包装种类");
currentWorksheet.Cells[, ].Value = Convert.ToString("是否含木质包装");
currentWorksheet.Cells[, ].Value = Convert.ToString("是否为旧物品");
currentWorksheet.Cells[, ].Value = Convert.ToString("是否未低温运输");
currentWorksheet.Cells[, ].Value = Convert.ToString("生产国别");
currentWorksheet.Cells[, ].Value = Convert.ToString("贸易国别");
int i = ;
foreach (DataRow datalist in WayBillList.Rows)
{
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ExpressCode"].GetString());//盘点ID
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["DeclareType"].GetString());//盘点明细ID
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["CIQNameCn"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["CIQName"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ProductCode"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["Weight"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["GrossWeight"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ProductSpec"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["SaleCity"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["Currency"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["QuantityOrder"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["QuantityTotalprice"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["DeclareUnit"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ConsigneeName"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["CityName"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ConsigneeAddress"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ConsigneeMobile"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ConsigneeIdcard"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["PackagingType"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["IsWoodPackaging"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["IsOldArticles"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["IsColdchain"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["ProductionCountry"].GetString());
currentWorksheet.Cells[i, ].Value = Convert.ToString(datalist["TradeCountry"].GetString());
i++;
}
}
}
//下载到指定位置路径文件.xlsx
var folder = HttpContext.Current.Server.MapPath("/Upload/download/");
Directory.CreateDirectory(folder);
url = "/Upload/download/订单数据" +DateTime.Now.ToString("yyyyMMddHHmmss")+".xlsx";
string pathInfoList = HttpContext.Current.Server.MapPath(url);
pck.SaveAs(new FileInfo(pathInfoList));
}
info.Result = true;
info.Data = url;
info.Msg = "导出成功！";
}
catch (Exception ex)
{
info.Result = false;
info.Msg = "错误信息：" + ex.Message;
}
return info;
}
#endregion
