一、导入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