非常规Excel模板导入导出

标签：

excel模板预览

图片

下载代码

前台

HTML 全选
<excel-export v-permission="8" method="post" actions="/***/ExportExcel">导出Excel</excel-export>
用技术成就未来
YES开发框架网 www.yesdotnet.com

后台

C# 全选
/// <summary>
/// 导出订单到excel
/// </summary>
/// <param name="input"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
/// <exception cref="RequestErrorException"></exception>
public async Task<(string fielName, byte[] bytes)> ExportOrderAsync(req_params_id input, CancellationToken cancellationToken)
{
    var data = await _businessContext.Query<tce_data_purchase_order>().Where(w => w.row_id == input.id).FirstOrDefaultAsync(cancellationToken);

    var detail = await (from a in _businessContext.Query<tce_data_purchase_order_details>().Where(w => w.doc_id == input.id)
                        join material in _businessContext.Query<tce_data_material>() on a.material_code equals material.material_code
                        select new
                        {
                            a.organization_id,
                            a.material_code,
                            a.purchase_unit_code,
                            a.quantity,
                            a.gross_price,
                            a.gross_amount,
                            a.remark,
                            material_name = material.material_name
                        }).ToListAsync(cancellationToken);

    var orgData = await _businessDataCacheTCE.Value.GetOrganizationDataAsync(_operator.BusinessCode);
    var dictOrg = orgData.ToDictionary(x => x.OrganizationId, x => x);

    var dictUnit = _businessContext.Query<tce_data_unit>().ToDictionary(x => x.unit_code, x => x.unit_name);

    var itemDocType = _businessContext.Query<data_CommonDict>().Where(w => w.DictType == JOCAPI.Models.Enums.EnumCommonDicData.PurchaseOrderType && w.DictCode == data.doc_type).FirstOrDefault();
    var xmdData = dictOrg[data.organization_id];
    var dataSupplier = _businessContext.Query<tce_data_businessPartner>().Where(w => w.business_partner_code == data.supplier_code).FirstOrDefault();

    XLSDataMain xlsData = new XLSDataMain()
    {
        DocNo = data.doc_no,
        DocType = itemDocType == null ? data.doc_type : itemDocType.DictDescription,
        DocDate = data.doc_date,
        DeliveryDate = data.delivery_date,
        Organization03Code = xmdData.OrganizationCode,
        Organization03Name = xmdData.OrganizationName,
        Organization04Code = "",
        Organization04Name = "",
        SupplierCode = data.supplier_code,
        Suppliername = dataSupplier == null ? "" : dataSupplier.business_partner_name,
        PurchaseGroup = data.purchase_group,
        Amount = data.gross_amount,
        Remark = data.remark,
        Items = new List<XLSDataDetail>()
    };

    if (!String.IsNullOrEmpty(data.organization_id_04))
    {
        var dkData = dictOrg[data.organization_id_04];
        xlsData.Organization04Code = dkData.OrganizationCode;
        xlsData.Organization04Name = dkData.OrganizationName;
    }

    foreach (var _d in detail)
    {
        var orgItem = dictOrg[_d.organization_id];

        XLSDataDetail d = new XLSDataDetail()
        {
            Organization04Code = orgItem.OrganizationCode,
            Organization04Name = orgItem.OrganizationName,
            MaterialCode = _d.material_code,
            MaterialName = _d.material_name,
            Unit = dictUnit[_d.purchase_unit_code],
            Quantity = _d.quantity,
            Price = _d.gross_price,
            Amount = _d.gross_amount,
            Remark = _d.remark
        };

        xlsData.Items.Add(d);
    }

    var mappings = new List<(string propertyName, string caption)>()
    {
        (nameof(XLSDataDetail.Organization04Code ),"档口代码"),
        (nameof(XLSDataDetail.Organization04Name ),"档口名称"),
        (nameof(XLSDataDetail.MaterialCode),"物料代码"),
        (nameof(XLSDataDetail.MaterialName),"物料名称"),
        (nameof(XLSDataDetail.Unit),"单位"),
        (nameof(XLSDataDetail.Quantity),"订单数量"),
        (nameof(XLSDataDetail.Price),"单价"),
        (nameof(XLSDataDetail.Amount),"金额"),
        (nameof(XLSDataDetail.Remark),"备注"),
    };



    var bytes = NOPIHelper.saveExcel(xlsData.Items, mappings, true, NOPIHelper.WorkbookType.xlsx, beforeData: (sheet) =>
    {

        var cellStyleTitle = sheet.Workbook.GetCellStyleAt(1);
        var cellStyleNomal = sheet.Workbook.GetCellStyleAt(2);
        var row1 = sheet.CreateRow(sheet.PhysicalNumberOfRows);
        NOPIHelper.WriteCell(row1, "采购单号", 0).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row1, xlsData.DocNo, 1).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row1, "单据类型", 2).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row1, xlsData.DocType, 3).CellStyle = cellStyleNomal;
        //NOPIHelper.WriteCell(row1, "单据日期", 4).CellStyle = cellStyleTitle;
        //if (xlsData.DocDate.HasValue) NOPIHelper.WriteCell(row1, xlsData.DocDate.Value, 5).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row1, "交货日期", 6).CellStyle = cellStyleTitle;
        if (xlsData.DeliveryDate.HasValue) NOPIHelper.WriteCell(row1, xlsData.DeliveryDate.Value, 7).CellStyle = cellStyleNomal;

        var row2 = sheet.CreateRow(sheet.PhysicalNumberOfRows);
        NOPIHelper.WriteCell(row2, "项目点代码", 0).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row2, xlsData.Organization03Code, 1).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row2, "项目点名称", 2).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row2, xlsData.Organization03Name, 3).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row2, "档口代码", 4).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row2, xlsData.Organization04Code, 5).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row2, "档口名称", 6).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row2, xlsData.Organization04Name, 7).CellStyle = cellStyleNomal;

        var row3 = sheet.CreateRow(sheet.PhysicalNumberOfRows);

        NOPIHelper.WriteCell(row3, "供应商代码", 0).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row3, xlsData.SupplierCode, 1).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row3, "供应商名称", 2).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row3, xlsData.Suppliername, 3).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row3, "采购组", 4).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row3, xlsData.PurchaseGroup, 5).CellStyle = cellStyleNomal;
        NOPIHelper.WriteCell(row3, "总金额", 6).CellStyle = cellStyleTitle;
        if (xlsData.Amount.HasValue) NOPIHelper.WriteCell(row3, (double)xlsData.Amount.Value, 7).CellStyle = cellStyleNomal;

        var row4 = sheet.CreateRow(sheet.PhysicalNumberOfRows);
        NOPIHelper.WriteCell(row4, "备注", 0).CellStyle = cellStyleTitle;
        NOPIHelper.WriteCell(row4, xlsData.Remark ?? "", 1, 7);
        var cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(row4.RowNum, row4.RowNum, 1, 7);
        RegionUtil.SetBorderLeft(1, cellRangeAddress, sheet);
        RegionUtil.SetBorderRight(1, cellRangeAddress, sheet);
        RegionUtil.SetBorderTop(1, cellRangeAddress, sheet);
        RegionUtil.SetBorderBottom(1, cellRangeAddress, sheet);

        // 添加一个空行
        sheet.CreateRow(sheet.PhysicalNumberOfRows);
    });

    string fielName = $"采购订单-{data.doc_no}-团餐易物料.xlsx";
    return (fielName, bytes);
}
用技术成就未来
YES开发框架网 www.yesdotnet.com

导入数据

前台

HTML 全选
<excel-choose ref="excelChooseRef" :showFilePath="false" :handleWorkBook="handleWorkBook">Excel单据导入</excel-choose>
用技术成就未来
YES开发框架网 www.yesdotnet.com
JavaScript 全选
/**
 * Excel单据导入
 */
const handleWorkBook = (workbook: XLSX.WorkBook) => {

	const worksheetName = workbook.SheetNames[0];
	const worksheet = workbook.Sheets[worksheetName];

	var jsonData: any[] = XLSX.utils.sheet_to_json(worksheet, { header: 'A' });
	
	const data: XLSDataMain = {
		docNo: jsonData[0]['B'],
		docType: jsonData[0]['D'],
		deliveryDate: jsonData[0]['F'],
		organization03Code: jsonData[1]['B'],
		organization03Name: jsonData[1]['D'],
		paymentMode: jsonData[1]['F'],

		customerCode: jsonData[2]['B'],
		customerName: jsonData[2]['D'],
		amount: jsonData[2]['F'],
		remark: jsonData[3]['B'],

		items: jsonData.slice(5).map(item => {
			return {
				materialCode: item['B'],
				materialName: item['C'],
				unit: item['D'],
				quantity: item['E'],
				price: item['F'],
				amount: item['G'],
				remark: item['H']
			}
		})
	}
	debugger

	request.importExcelConvert(data).then(res => {
		formEditRef.value?.showCreateFromExcel(res.data)
	})
}
用技术成就未来
YES开发框架网 www.yesdotnet.com

后台

C# 全选
 public async Task<FormData> ImportExcelConvert(XLSDataMain input)
 {
     if (String.IsNullOrEmpty(input.SupplierCode))
     {
         throw new RequestErrorException($"供应商代码不能为空");
     }
     if (String.IsNullOrEmpty(input.Organization03Code))
     {
         throw new RequestErrorException($"项目点代码不能为空");
     }

     _businessContext.SetDataFilterDisable();
     var orgData = await _businessDataCacheTCE.Value.GetOrganizationDataAsync(_operator.BusinessCode);

     // 项目点
     Models.MenuCategory.CacheDataOrganization? itemOrg03 = orgData.Where(w => w.OrganizationCode == input.Organization03Code && w.OrganizationCategory == JOCAPI.Models.Enums.OrganizationCategory.C03).FirstOrDefault();
     if (itemOrg03 == null)
     {
         throw new RequestErrorException($"系统中没有找到代码为{input.Organization03Code}的项目点");
     }

     // 档口
     Models.MenuCategory.CacheDataOrganization? itemOrg04 = null;
     if (!String.IsNullOrEmpty(input.Organization04Code))
     {
         itemOrg04 = orgData.Where(w => w.OrganizationCode == input.Organization04Code && w.OrganizationCategory == JOCAPI.Models.Enums.OrganizationCategory.C04).FirstOrDefault();
     }

     // 供应商
     var itemSupplierData = _businessContext.Query<tce_data_businessPartner>().Where(w => w.business_partner_code == input.SupplierCode).FirstOrDefault();
     if (itemSupplierData != null)
     {
         if ((itemSupplierData.role_category + ",").Contains(JOCAPI.Models.Enums.BusinessPartnerRoleCategory.Supplier + ",") == false)
             itemSupplierData = null;
     }
     if (itemSupplierData == null)
     {
         throw new RequestErrorException($"系统中没有找到代码为{input.SupplierCode}的供应商");
     }

     // 单据类型
     var itemDocType = _businessContext.Query<data_CommonDict>().Where(w => w.DictType == JOCAPI.Models.Enums.EnumCommonDicData.PurchaseOrderType && w.DictDescription == input.DocType).FirstOrDefault();

     // 采购组
     var itemGroup = _businessContext.Query<data_CommonDict>().Where(w => w.DictType == JOCAPI.Models.Enums.EnumCommonDicData.PurchaseGroup && w.DictDescription == input.PurchaseGroup).FirstOrDefault();



     FormData data = new FormData()
     {
         DocNo = "",
         PurchaseGroup = itemGroup?.DictCode ?? "",
         Currency = "CNY",
         DeliveryDate = input.DeliveryDate,
         FreightAmount = null,
         DocDate = DateTime.Today,
         DocType = itemDocType?.DictCode ?? "",
         DataBySource = JOCAPI.Models.Enums.EnumDataBySource.XLSImport,
         GrossAmount = input.Amount,
         IsDelete = false,
         NetAmount = null,
         PurchaserCode = null,
         Remark = input.Remark,
         SupplierCode = itemSupplierData?.business_partner_code ?? "",
         TaxAmount = null,
         TaxRate = null,
         OrganizationId = itemOrg03?.OrganizationId ?? "",
         OrganizationId04 = itemOrg04?.OrganizationId ?? "",
         PurchaserName = "",
         IsClosed = false,
         SourceDocId = "",
         SourceDocNo = "",
         SourceDocType = "",
         WarehouseId = "",
         DetailDetail = new List<DetailDetailItem>()
         //DetailDetail = input.DetailDetail.Select(s =>).ToList()
     };


     // 明细表
     var materialCodes = input.Items.Select(s => s.MaterialCode).Distinct().ToList();
     var materialData = (from b in _businessContext.Query<tce_data_material>()
                         join c in _businessContext.Query<tce_data_material_data>() on b.row_id equals c.material_id
                         where c.organization_id == itemOrg03!.OrganizationId && materialCodes.Contains(b.material_code)
                         select new
                         {
                             material_code = b.material_code,
                             material_name = b.material_name,
                             status = b.material_status,
                             orgStatus = c.material_status
                         }).ToList();


     // 校验物料是否配置
     var errMaterialCodes = materialCodes.Except(materialData.Select(s => s.material_code).Distinct().ToList()).ToList();
     if (errMaterialCodes.Count > 0)
     {
         throw new RequestErrorException($"物料不存在或项目点没有配置物料：{String.Join(";", errMaterialCodes)}");
     }
     // 校验物料状态
     var errStatus = materialData.Where(w => w.status != JOCAPI.Models.Enums.MaterialStatus.V01 || w.orgStatus != JOCAPI.Models.Enums.MaterialStatus.V01).ToList();
     if (errStatus.Count > 0)
     {
         throw new RequestErrorException($"物料状态异常：{String.Join(";", errStatus.Select(s => s.material_code))}");
     }

     var dictUnit = _businessContext.Query<tce_data_unit>().ToList().ToDictionary(x => x.unit_name, x => x.unit_code);

     int rowNumber = 0;
     foreach (var d in input.Items)
     {
         var itemMaterial = materialData.Where(w => w.material_code == d.MaterialCode).FirstOrDefault();
         if (itemMaterial == null) continue;
         rowNumber = rowNumber + 1;
         string unitCode = "";
         if (!dictUnit.TryGetValue(d.Unit, out unitCode)) unitCode = "";

         var itemDOrg04 = orgData.Where(w => w.OrganizationCode == d.Organization04Code && w.OrganizationCategory == JOCAPI.Models.Enums.OrganizationCategory.C04).FirstOrDefault();

         var ditem = new DetailDetailItem()
         {
             RowId = "",
             DocId = "",
             RowNumber = "",
             Sort = rowNumber,
             MaterialCode = itemMaterial.material_code,
             MaterialName = itemMaterial.material_name,
             PurchaseUnitCode = unitCode,
             WarehouseUnitCode = "",
             BaseUnitConvert = 1,
             DocUnitConvert = 1,
             GrossAmount = d.Amount,
             GrossPriceGroup = d.Price,
             GrossPriceGroupQuantity = 1,
             GrossPrice = d.Price,
             IsDelete = false,
             NetAmount = null,
             NetPrice = null,
             Quantity = d.Quantity ?? 0,
             Remark = d.Remark,
             TaxAmount = null,
             TaxPoint = null,
             WarehouseQuantity = d.Quantity ?? 0,
             WarehouseId = "",
             OrganizationId = itemDOrg04?.OrganizationId ?? ""
         };

         data.DetailDetail.Add(ditem);
     }

     return data;
 }
用技术成就未来
YES开发框架网 www.yesdotnet.com

 

 

 

 

版权声明：本文为YES开发框架网发布内容,转载请附上原文出处连接
YESWEB 张国生
评论列表
发表评论
评论内容
昵称：
关联文章
非常规Excel模板导入导出
Excel导入导出下拉选项实现
Excel文件数据导入和导出功能实现
Excel数据导出导入 V3
C# NPOI导出excel绑定列的下拉数据源
NPOI生成Excel文件时设置一整列为文本类型
YESWin Winform开发框架 业务模块项目模板制作
企业项目实战.Net Core +FastReport教程一|制作报表模板
TT模板断点调试
TT模板断点调试
模板修改无法保存
EF EntityFramework.MappingAPI批量导入 模型属性顺序和数据库表不一致时导入错位
DTCMS：html模板中格式化时间
Excel自定义格式千分符
excel2007直接打开文件空白，需要菜单打开
Python读取excel xlrd读取xlsx报错：Excel xlsx file; not supported
YES-CMS内容管理系统 页面模板介绍
Razor模板编辑高亮CodeMirror和格式化JS-Beautify
.NET Core 运行时T4模板使用,T4生成代码
第三方导入用户API接口