27.3、列表插件，引入Excel-3，导入界面代码

业务场景：仿标准产品，引入EXCEL功能；

                    导入销售出库单，运单号，物流公司；

续

27.2

1、打开VS，新建一个类库，具体可以参考21.1 ，1-4步

2、

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS;
using Kingdee.BOS.Core.Bill.PlugIn;
using System.ComponentModel;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm;
using System.Data;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel;
using Kingdee.BOS.Resource;
using Kingdee.BOS.Util;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.JSON;
using Kingdee.BOS.Core.List.PlugIn;
using Kingdee.BOS.Core.List;

namespace Kingdee.Bos.OnLoad.List
{
    [Description("导入Excel文件")]
    
    [Kingdee.BOS.Util.HotUpdate]
    
    
    public class OnLoadField:AbstractDynamicFormPlugIn
    {
        DataTable dtexcel;
        string sql;
        private string _filePath;
        
        
        //点击导入Excel按钮
        public override void ButtonClick(ButtonClickEventArgs e)
        {
            base.ButtonClick(e);
            string a;
            //当点击事件不为空时
            //不管大小写,都转换成大写
            if((a = e.Key.ToUpperInvariant()) !=null)
            {
                //当点击的按钮名字为FImportData
                if(a =="FImportData")
                {
                    //判断是否有Excel表格
                    if(this.IsNotNull())
                    {
                        //数据的操作
                        Impoort();
                        this.View.ShowMessage("导入完成");
                        return;
                    }
                }
            }              
        }
        
        
        
        //自定义方法,判断是否上传Excel表格
        private bool IsNotNull()
        {
            ExcelOperation excelOperation = new ExcelOperation();
            DataSet dataSet = null;
            dataSet = excelOperation.ReadFromFile(this._filePath, 0, 0);
            dtexcel = dataSet.Tables["Sheet1"];
            if(dtexcel == null)
            {
                this.View.ShowMessage("没有找到模板请重新选择");
                return false;
                
            }
            return true;
        }
        
        
        //自定义方法,引入的数据
        private void Impoort()
        {
            ExcelOperation excelOperation = new ExcelOperation();
            DataSet dataSet = null;
            dataSet = excelOperation.ReadFromFile(this._filePath, 0, 0);
            //在dataSet中获取一个Table的表中的数据
            dtexcel = dataSet.Tables["Sheet1"];
            for(int i =1;i<dtexcel.Rows.Count;i++)
            {
                if(dtexcel.Rows[i][0].ToString()!="")
                {
                    //更新销售出库单T_SAL_OUTSTOCK, 运单号F_YDH = 导入的运单号
                    sql = "/*dialect*/update T_SAL_OUTSTOCK set F_YDH = '" + dtexcel.Rows[i]["运单号"].ToString() + "',F_WLGS ='" + dtexcel.Rows[i]["物流公司"].ToString() + "' where FBillNo ='" + dtexcel.Rows[i]["单据编号"].ToString() + "'";
                    DBUtils.Execute(this.Context, sql);
                }
            }
            this.View.ShowMessage("上传完成");
        }
        
        
        
        //自定义方法,文件上传完毕触发的事件
        public override void CustomEvents(CustomEventsArgs e)
        {
            if(e.Key.EqualsIgnoreCase("FFileUpdate"))
            {
                if(e.EventName.EqualsIgnoreCase("FILECHANGED"))
                {
                    JSONObject jSONObject = KDObjectConverter.DeserializeObject<JSONObject>(e.EventArgs);
                    if(jSONObject !=null)
                    {
                        JSONArray jSONArray = new JSONArray(jSONObject["NewValue"].ToString());
                        if(jSONArray.Count >0)
                        {
                            string text = (jSONArray[0] as Dictionary<string, object>)["ServerFileName"].ToString();
                            if(this.CheckFile(text))
                            {
                                this._filePath = this.GetFilePath(text);
                                //上传按钮变成可用状态
                                this.EnableButton("FImportData", true);
                            }
                        }
                        else
                        {
                            this.EnableButton("FImportData", false);
                        }
                    }
                }
                else
                {
                    if(e.EventName.EqualsIgnoreCase("STATECHANGED"))
                    {
                        JSONObject jSONObject2 = KDObjectConverter.DeserializeObject<JSONObject>(e.EventArgs);
                        if(jSONObject2["State"].ToString() !="2")
                        {
                            this.EnableButton("FImportData", false);
                        }
                    }
                }
            }
            base.CustomEvents(e);
        }
        
        
        
        //自定义方法,判断是否是上传的是Excel文件
        private bool CheckFile(string fileName)
        {
            bool flag = false;
            string[] array = fileName.Split(new char[] 
            {
                '.'
            });
            //通过后缀名,判断是否是Excel
             if(array.Length ==2 && (array[1].EqualsIgnoreCase("xls")|| array[1].EqualsIgnoreCase("xlsx")))
             {
                 flag = true;
             }
            if(!flag)
            {
                this.View.ShowWarnningMessage("请选择正确的文件进行引入");
            }
            return flag;
        }
        
        
        
        //自定义方法,没有上传完成Excel,那么上传按钮是灰色的
        private void EnableButton(string key, bool bEnable)
        {
            this.View.GetControl<Button>(key).Enabled = bEnable;
        }
        
        
        
        //自定义方法,获取上传路径
        private string GetFilePath(string serverFileName)
        {
            string directory = "FileUpLoadServices\\UploadFiles";
            return PathUtils.GetPhysicalPath(directory, serverFileName);
        }
    }
}

3、重新生成dll

4、重新打开销售出库单列表，点按钮，先导出Excel，

4.1、修改表名为Sheet1，填写运单号、物流公司信息

5、点按钮，导入

这节比较复杂，很奇怪，提示   没有找到模板请重新选择，dtexcel总是null空值，待续......

        //判断是否上传Excel表格
        private bool IsNotNull()
        {
            ExcelOperation excelOperation = new ExcelOperation();
            DataSet dataSet = null;
            dataSet = excelOperation.ReadFromFile(this._filePath, 0, 0);
            dtexcel = dataSet.Tables["Sheet1"];
            if (dtexcel == null)
            {
                this.View.ShowMessage("没有找到模板请重新选择！");
                return false;
            }
            return true;
        }

如果提示上面报错，添加3行数据再试下；

不能只填写一行数据；

视频链接

https://www.bilibili.com/video/BV1ti4y1M7vU/

总目录链接

https://vip.kingdee.com/article/64993872014591232