C# 导入和导出EXCEL

来源：互联网发布：淘宝助理如何删除宝贝编辑：程序博客网时间：2024/06/05 20:57

一、导入Excel(WinFrom)

(1)Excel Model 定义

    public class ImportExpressCustomModel
    {
        /// <summary>
        /// 收件人姓名
        /// </summary>
        [CsvField(Name = "收件人姓名")]
        public string Receivername { get; set; }

        /// <summary>
        /// 收件人公司
        /// </summary>
        [CsvField(Name = "收件人公司")]
        public string ReceiverCompany { get; set; }

        /// <summary>
        /// 收件人电话
        /// </summary>
        [CsvField(Name = "收件人电话")]
        public string ReceiverPhone { get; set; }

        /// <summary>
        /// 收件人Email
        /// </summary>
        [CsvField(Name = "收件人Email")]
        public string ReceiverEmail { get; set; }

        /// <summary>
        /// 收件人地址
        /// </summary>
        [CsvField(Name = "收件人地址")]
        public string ReceiverAddress { get; set; }

        /// <summary>
        /// 发件人公司
        /// </summary>
        [CsvField(Name = "发件人公司")]
        public string FormCompany { get; set; }

        /// <summary>
        /// 快递单号
        /// </summary>
        [CsvField(Name = "快递单号")]
        public string WaybillNumber { get; set; }

}

(2)属性绑定（文件路径）

        private string FilePath
        {
            get { return ctrlFilePath.Text; }
            set { ctrlFilePath.Text = value; }
        }

（3）验证Excel指定列不可为空

        private static bool Verif(ImportExpressCustomModel model)
        {
            return !string.IsNullOrWhiteSpace(model.WaybillNumber)
                   && !string.IsNullOrWhiteSpace(model.ReceiverAddress);
        }

（4）获取导入的数据

        private static IEnumerable<ImportExpressCustomModel> GetExpressCustomCollection(string path)
        {
            if (path == null || !File.Exists(path))
            {
                return null;
            }

            string fileExtension = Path.GetExtension(path).ToUpper();

            IEnumerable<ImportExpressCustomModel> records;
            switch (fileExtension)
            {
                case ".XLSX":
                    records = path.GetRecordsFromXlsx<ImportExpressCustomModel>();
                    break;
                case ".XLS":
                    records = path.GetRecordsFromXls<ImportExpressCustomModel>();
                    break;
                default:
                    return null;
            }

            return records.Where(Verif).ToList();
        }

（5）存入

private void ctrlFilePath_MouseDown(object sender, MouseEventArgs e)
        {
            if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
            LogMessage = "";
            FilePath = "";
            var models = new List<ImportExpressCustomModel>();
            if (openFileDialog1.FileNames.Length > 0)
            {
                foreach (var t in openFileDialog1.FileNames)
                {
                    FilePath += t + ";";

                    models.AddRange(GetExpressCustomCollection(t));
                }
            }
            gvData.DataSource = models;
            gvData.Update();
            LogMessage = $"共{gvData.RowCount}行数据";
        }

二、导出Excel

(1)导出excel的数据用list接收

.//查询导出数据

        private void btnSelect_Click(object sender, EventArgs e)
        {
            list = customerlLogic.GetSummary(dtpDateForm.Value,
                    dtpDateTo.Value, txtCustomerCode.Text);
            dgrdData.DataSource = list;
            dgrdData.Update();
            DataCount = string.Format("共{0}行数据", dgrdData.RowCount);
        }
       （2）导出

        private void btnExport_Click(object sender, EventArgs e)
        {
            this.openFileDialog.DefaultExt = "xlsx";
            this.openFileDialog.FileName = @"客户发货清单";
            this.openFileDialog.Filter = @"Excel 工作簿(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
            if (this.openFileDialog.ShowDialog(this) != DialogResult.OK) return;
            if (dgrdData.RowCount != null && dgrdData.RowCount > 0)
            {
                list.WriteRecordsToXlsx(this.openFileDialog.FileName);
                MessageBox.Show("导出成功");
            }
        }

三、

（1）CsvHelper 关于CSV文件导入和导出以及转化
（2）list.WriteRecordsToXlsx （Cnzilla.Library.dll） Cnzilla.Library.Extensions
（3）path.GetRecordsFromXlsx Cnzilla.Library.Extensions

0 0