Exportxian() {
var url = "/api/Ema_bilingBill/ExportXianDeclaration";
const params = {};
var callback = res => {
this.common.showMsg(res);
if (res.Result) {
window.open(this.common.domain + res.Data);
}
};
this.common.ajax(url, params, callback, true);
}
#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[0];
//模板路径
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[1]; //创建sheet，如果多个，就改变这个数字，把这个写入到循环里面
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
currentWorksheet.Cells[1, 1].Value = Convert.ToString("分运单号");
currentWorksheet.Cells[1, 2].Value = Convert.ToString("申报类型");
currentWorksheet.Cells[1, 3].Value = Convert.ToString("物品名称");
currentWorksheet.Cells[1, 4].Value = Convert.ToString("英文物品名称");
currentWorksheet.Cells[1, 5].Value = Convert.ToString("商品编码");
currentWorksheet.Cells[1, 6].Value = Convert.ToString("净重(KG)");
currentWorksheet.Cells[1, 7].Value = Convert.ToString("毛重(KG)");
currentWorksheet.Cells[1, 8].Value = Convert.ToString("规格/型号");
currentWorksheet.Cells[1, 9].Value = Convert.ToString("产销城市");
currentWorksheet.Cells[1, 10].Value = Convert.ToString("币制");
currentWorksheet.Cells[1, 11].Value = Convert.ToString("申报数量");
currentWorksheet.Cells[1, 12].Value = Convert.ToString("申报总价");
currentWorksheet.Cells[1, 13].Value = Convert.ToString("申报计量单位");
currentWorksheet.Cells[1, 14].Value = Convert.ToString("收件人");
currentWorksheet.Cells[1, 15].Value = Convert.ToString("收件人城市");
currentWorksheet.Cells[1, 16].Value = Convert.ToString("收件人地址");
currentWorksheet.Cells[1, 17].Value = Convert.ToString("收件人电话)");
currentWorksheet.Cells[1, 18].Value = Convert.ToString("收发件人证件号");
currentWorksheet.Cells[1, 19].Value = Convert.ToString("包装种类");
currentWorksheet.Cells[1, 20].Value = Convert.ToString("是否含木质包装");
currentWorksheet.Cells[1, 21].Value = Convert.ToString("是否为旧物品");
currentWorksheet.Cells[1, 22].Value = Convert.ToString("是否未低温运输");
currentWorksheet.Cells[1, 23].Value = Convert.ToString("生产国别");
currentWorksheet.Cells[1, 24].Value = Convert.ToString("贸易国别");
int i = 2;
foreach (DataRow datalist in WayBillList.Rows)
{
currentWorksheet.Cells[i, 1].Value = Convert.ToString(datalist["ExpressCode"].GetString());//盘点ID
currentWorksheet.Cells[i, 2].Value = Convert.ToString(datalist["DeclareType"].GetString());//盘点明细ID
currentWorksheet.Cells[i, 3].Value = Convert.ToString(datalist["CIQNameCn"].GetString());
currentWorksheet.Cells[i, 4].Value = Convert.ToString(datalist["CIQName"].GetString());
currentWorksheet.Cells[i, 5].Value = Convert.ToString(datalist["ProductCode"].GetString());
currentWorksheet.Cells[i, 6].Value = Convert.ToString(datalist["Weight"].GetString());
currentWorksheet.Cells[i, 7].Value = Convert.ToString(datalist["GrossWeight"].GetString());
currentWorksheet.Cells[i, 8].Value = Convert.ToString(datalist["ProductSpec"].GetString());
currentWorksheet.Cells[i, 9].Value = Convert.ToString(datalist["SaleCity"].GetString());
currentWorksheet.Cells[i, 10].Value = Convert.ToString(datalist["Currency"].GetString());
currentWorksheet.Cells[i, 11].Value = Convert.ToString(datalist["QuantityOrder"].GetString());
currentWorksheet.Cells[i, 12].Value = Convert.ToString(datalist["QuantityTotalprice"].GetString());
currentWorksheet.Cells[i, 13].Value = Convert.ToString(datalist["DeclareUnit"].GetString());
currentWorksheet.Cells[i, 14].Value = Convert.ToString(datalist["ConsigneeName"].GetString());
currentWorksheet.Cells[i, 15].Value = Convert.ToString(datalist["CityName"].GetString());
currentWorksheet.Cells[i, 16].Value = Convert.ToString(datalist["ConsigneeAddress"].GetString());
currentWorksheet.Cells[i, 17].Value = Convert.ToString(datalist["ConsigneeMobile"].GetString());
currentWorksheet.Cells[i, 18].Value = Convert.ToString(datalist["ConsigneeIdcard"].GetString());
currentWorksheet.Cells[i, 19].Value = Convert.ToString(datalist["PackagingType"].GetString());
currentWorksheet.Cells[i, 20].Value = Convert.ToString(datalist["IsWoodPackaging"].GetString());
currentWorksheet.Cells[i, 21].Value = Convert.ToString(datalist["IsOldArticles"].GetString());
currentWorksheet.Cells[i, 22].Value = Convert.ToString(datalist["IsColdchain"].GetString());
currentWorksheet.Cells[i, 23].Value = Convert.ToString(datalist["ProductionCountry"].GetString());
currentWorksheet.Cells[i, 24].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
浙公网安备 33010602011771号