C#多线程中,我们可以使用async和await来异步处理耗时任务。
现在我们打开一个Excel表格,将Excel表格的每一行数据进行处理,并存储到数据库中
新建Windows应用程序DataImportDemo,.net framework 4.6.1
将默认的Form1重命名为FormDataImport,
窗体FormDataImport设计如图:
窗体FormDataImport设计器代码如下:
FormDataImport.Designer.cs文件
namespace DataImportDemo
{partial class FormDataImport{/// <summary>/// 必需的设计器变量。/// </summary>private System.ComponentModel.IContainer components = null;/// <summary>/// 清理所有正在使用的资源。/// </summary>/// <param name="disposing">如果应释放托管资源,为 true;否则为 false。</param>protected override void Dispose(bool disposing){if (disposing && (components != null)){components.Dispose();}base.Dispose(disposing);}#region Windows 窗体设计器生成的代码/// <summary>/// 设计器支持所需的方法 - 不要修改/// 使用代码编辑器修改此方法的内容。/// </summary>private void InitializeComponent(){this.dgvData = new System.Windows.Forms.DataGridView();this.btnSave = new System.Windows.Forms.Button();this.rtxtMessage = new System.Windows.Forms.RichTextBox();this.btnExport = new System.Windows.Forms.Button();this.dgvcCoreID = new System.Windows.Forms.DataGridViewTextBoxColumn();this.dgvcModuleBarcode = new System.Windows.Forms.DataGridViewTextBoxColumn();this.dgvcPoleCode = new System.Windows.Forms.DataGridViewTextBoxColumn();this.dgvcModuleCategory = new System.Windows.Forms.DataGridViewTextBoxColumn();this.progressBarImport = new System.Windows.Forms.ProgressBar();((System.ComponentModel.ISupportInitialize)(this.dgvData)).BeginInit();this.SuspendLayout();// // dgvData// this.dgvData.AllowUserToAddRows = false;this.dgvData.AllowUserToDeleteRows = false;this.dgvData.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;this.dgvData.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {this.dgvcCoreID,this.dgvcModuleBarcode,this.dgvcPoleCode,this.dgvcModuleCategory});this.dgvData.Location = new System.Drawing.Point(12, 61);this.dgvData.Name = "dgvData";this.dgvData.ReadOnly = true;this.dgvData.RowTemplate.Height = 23;this.dgvData.Size = new System.Drawing.Size(627, 628);this.dgvData.TabIndex = 0;// // btnSave// this.btnSave.Font = new System.Drawing.Font("宋体", 16F);this.btnSave.Location = new System.Drawing.Point(67, 9);this.btnSave.Name = "btnSave";this.btnSave.Size = new System.Drawing.Size(233, 43);this.btnSave.TabIndex = 1;this.btnSave.Text = "打开Excel并导入数据";this.btnSave.UseVisualStyleBackColor = true;this.btnSave.Click += new System.EventHandler(this.btnSave_Click);// // rtxtMessage// this.rtxtMessage.Location = new System.Drawing.Point(645, 61);this.rtxtMessage.Name = "rtxtMessage";this.rtxtMessage.Size = new System.Drawing.Size(621, 628);this.rtxtMessage.TabIndex = 2;this.rtxtMessage.Text = "";// // btnExport// this.btnExport.Font = new System.Drawing.Font("宋体", 16F);this.btnExport.Location = new System.Drawing.Point(336, 9);this.btnExport.Name = "btnExport";this.btnExport.Size = new System.Drawing.Size(122, 43);this.btnExport.TabIndex = 3;this.btnExport.Text = "导出模板";this.btnExport.UseVisualStyleBackColor = true;this.btnExport.Click += new System.EventHandler(this.btnExport_Click);// // dgvcCoreID// this.dgvcCoreID.HeaderText = "序号";this.dgvcCoreID.Name = "dgvcCoreID";this.dgvcCoreID.ReadOnly = true;// // dgvcModuleBarcode// this.dgvcModuleBarcode.HeaderText = "模组条码";this.dgvcModuleBarcode.Name = "dgvcModuleBarcode";this.dgvcModuleBarcode.ReadOnly = true;this.dgvcModuleBarcode.Width = 180;// // dgvcPoleCode// this.dgvcPoleCode.HeaderText = "极性编码";this.dgvcPoleCode.Name = "dgvcPoleCode";this.dgvcPoleCode.ReadOnly = true;// // dgvcModuleCategory// this.dgvcModuleCategory.HeaderText = "模组类型";this.dgvcModuleCategory.Name = "dgvcModuleCategory";this.dgvcModuleCategory.ReadOnly = true;this.dgvcModuleCategory.Width = 140;// // progressBarImport// this.progressBarImport.Location = new System.Drawing.Point(529, 15);this.progressBarImport.Name = "progressBarImport";this.progressBarImport.Size = new System.Drawing.Size(737, 23);this.progressBarImport.TabIndex = 4;// // FormDataImport// this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;this.ClientSize = new System.Drawing.Size(1305, 710);this.Controls.Add(this.progressBarImport);this.Controls.Add(this.btnExport);this.Controls.Add(this.rtxtMessage);this.Controls.Add(this.btnSave);this.Controls.Add(this.dgvData);this.Name = "FormDataImport";this.Text = "数据导入使用async,await异步处理耗时任务";((System.ComponentModel.ISupportInitialize)(this.dgvData)).EndInit();this.ResumeLayout(false);}#endregionprivate System.Windows.Forms.DataGridView dgvData;private System.Windows.Forms.Button btnSave;private System.Windows.Forms.RichTextBox rtxtMessage;private System.Windows.Forms.Button btnExport;private System.Windows.Forms.DataGridViewTextBoxColumn dgvcCoreID;private System.Windows.Forms.DataGridViewTextBoxColumn dgvcModuleBarcode;private System.Windows.Forms.DataGridViewTextBoxColumn dgvcPoleCode;private System.Windows.Forms.DataGridViewTextBoxColumn dgvcModuleCategory;private System.Windows.Forms.ProgressBar progressBarImport;}
}
添加对NPOI框架的引用
添加对【NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormats.dll】类库的引用.
新建类NpoiExcelOperateUtil,用于【Excel表格与DataTable内存数据表的相互转换操作】
NpoiExcelOperateUtil.cs源程序:
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace DataImportDemo
{/// <summary>/// Excel表格与DataTable内存数据表的相互转换操作类/// 斯内科 2023-08-08/// </summary>public static class NpoiExcelOperateUtil{/// <summary>/// Excel的第一个工作簿(Sheet)转化成DataTable/// 使用EXCEL的第一个工作簿,默认为Sheet1/// </summary>/// <param name="file"></param>/// <returns></returns>public static DataTable ExcelToTable(string file){DataTable dt = new DataTable();IWorkbook workbook;string fileExt = Path.GetExtension(file).ToLower();using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)){//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式if (fileExt == ".xlsx"){workbook = new XSSFWorkbook(fs);}else if (fileExt == ".xls"){workbook = new HSSFWorkbook(fs);}else{return null;}//第一个工作簿ISheet sheet = workbook.GetSheetAt(0);if (sheet == null){return null;}return ExcelToTable(file, sheet.SheetName);}}/// <summary>/// Excel的指定Sheet转化成内存表/// </summary>/// <param name="file">路径</param>/// <param name="sheetName">sheet名称</param>/// <returns></returns>public static DataTable ExcelToTable(string file, string sheetName){DataTable[] dataTables = ExcelToTable(file, new List<string>() { sheetName });if (dataTables != null && dataTables.Length > 0){return dataTables[0];}return null;}/// <summary>/// 一个excel文件的多个Sheet转化成内存表数组,/// 每个Sheet都对应一个数据表/// </summary>/// <param name="file">路径</param>/// <param name="list_SheetName">sheet名称集合</param>/// <returns></returns>public static DataTable[] ExcelToTable(string file, List<string> list_SheetName){int count = list_SheetName.Count;DataTable[] dtS = new DataTable[count];//===============================//IWorkbook workbook;string fileExt = Path.GetExtension(file).ToLower();using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)){//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式if (fileExt == ".xlsx"){workbook = new XSSFWorkbook(fs);}else if (fileExt == ".xls"){workbook = new HSSFWorkbook(fs);}else{return null;}ISheet[] sheetS = new ISheet[count];for (int k = 0; k < count; k++){dtS[k] = new DataTable(list_SheetName[k]);sheetS[k] = workbook.GetSheet(list_SheetName[k]);ISheet sheet = sheetS[k];if (sheet == null){continue;}DataTable dt = new DataTable(list_SheetName[k]);//表头 IRow header = sheet.GetRow(sheet.FirstRowNum);List<int> columns = new List<int>();for (int i = 0; i < header.LastCellNum; i++){object obj = GetValueType(header.GetCell(i));if (obj == null || obj.ToString() == string.Empty){dt.Columns.Add(new DataColumn("Columns" + i.ToString()));}elsedt.Columns.Add(new DataColumn(obj.ToString()));columns.Add(i);}//数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++){DataRow dr = dt.NewRow();bool hasValue = false;foreach (int j in columns){dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));if (dr[j] != null && dr[j].ToString() != string.Empty){hasValue = true;}}if (hasValue){dt.Rows.Add(dr);}}dtS[k] = dt;}}return dtS;}/// <summary>/// Datable导出成Excel/// </summary>/// <param name="dt"></param>/// <param name="file"></param>public static void TableToExcel(DataTable dt, string file){IWorkbook workbook;string fileExt = Path.GetExtension(file).ToLower();if (fileExt == ".xlsx"){//workbook = new XSSFWorkbook();workbook = new HSSFWorkbook();}else if (fileExt == ".xls"){workbook = new HSSFWorkbook();}else{workbook = null;}if (workbook == null){return;}ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);//表头 IRow row = sheet.CreateRow(0);for (int i = 0; i < dt.Columns.Count; i++){ICell cell = row.CreateCell(i);cell.SetCellValue(dt.Columns[i].ColumnName);}//数据 for (int i = 0; i < dt.Rows.Count; i++){IRow row1 = sheet.CreateRow(i + 1);for (int j = 0; j < dt.Columns.Count; j++){ICell cell = row1.CreateCell(j);cell.SetCellValue(dt.Rows[i][j].ToString());}}//转为字节数组 MemoryStream stream = new MemoryStream();workbook.Write(stream);var buf = stream.ToArray();//保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)){fs.Write(buf, 0, buf.Length);fs.Flush();}}/// <summary>/// 获取单元格类型/// </summary>/// <param name="cell"></param>/// <returns></returns>private static object GetValueType(ICell cell){if (cell == null)return null;switch (cell.CellType){case CellType.Blank: //BLANK: return null;case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue;case CellType.Numeric: //NUMERIC: return cell.NumericCellValue;case CellType.String: //STRING: return cell.StringCellValue;case CellType.Error: //ERROR: return cell.ErrorCellValue;case CellType.Formula: //FORMULA: default:return "=" + cell.CellFormula;}}}
}
窗体FormDataImport的异步耗时任务代码如下:
FormDataImport.cs文件
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;namespace DataImportDemo
{public partial class FormDataImport : Form{public FormDataImport(){InitializeComponent();rtxtMessage.ReadOnly = true;progressBarImport.Value = 0;dgvData.AutoGenerateColumns = false;dgvcCoreID.DataPropertyName = "序号";dgvcModuleBarcode.DataPropertyName = "模组条码";dgvcPoleCode.DataPropertyName = "极性编码";dgvcModuleCategory.DataPropertyName = "模组类型";}/// <summary>/// 显示推送消息/// </summary>/// <param name="msg"></param>private void DisplayMessage(string msg){this.BeginInvoke(new Action(() =>{if (rtxtMessage.TextLength > 20480){rtxtMessage.Clear();}rtxtMessage.AppendText($"{DateTime.Now.ToString("HH:mm:ss.fff")}->{msg}\n");rtxtMessage.ScrollToCaret();}));}/// <summary>/// 一行一行的更新数据库【耗时任务】/// </summary>/// <param name="dtImport"></param>private Task<int> UpdateDatabase(DataTable dtImport){int rowCount = dtImport.Rows.Count;progressBarImport.Maximum = rowCount;progressBarImport.Value = 0;//进度条清零DisplayMessage($"开始导入模组极柱数据,准备导入数据条数【{rowCount}】......");Task<int> task = Task.Run<int>(() =>{int cnt = 0;for (int i = 0; i < rowCount; i++){//更新进度条this.BeginInvoke(new Action(() =>{if (this.IsHandleCreated){progressBarImport.Value++;}}));string ModuleBarcode = Convert.ToString(dtImport.Rows[i]["模组条码"]);string PoleCode = Convert.ToString(dtImport.Rows[i]["极性编码"]);string ModuleCategory = Convert.ToString(dtImport.Rows[i]["模组类型"]);if (string.IsNullOrWhiteSpace(ModuleBarcode)){continue;}try{List<string> sqlCollection = new List<string>();List<Dictionary<string, object>> dictCollection = new List<Dictionary<string, object>>();sqlCollection.Add("delete from module_pole where ModuleBarcode=@ModuleBarcode");sqlCollection.Add("insert into module_pole (ModuleBarcode,ModuleCategory,PoleCode,ProcessEndTime) values (@ModuleBarcode,@ModuleCategory,@PoleCode,@ProcessEndTime)");dictCollection.Add(new Dictionary<string, object>() { { "ModuleBarcode", ModuleBarcode } });dictCollection.Add(new Dictionary<string, object>() { { "ModuleBarcode", ModuleBarcode },{ "ModuleCategory", ModuleCategory }, { "PoleCode", PoleCode }, { "ProcessEndTime", DateTime.Now } });ExecuteTransaction(sqlCollection, dictCollection);DisplayMessage($"导入模组极柱数据成功,模组条码【{ModuleBarcode}】");cnt++;}catch (Exception ex){DisplayMessage($"导入模组极柱数据出错【{ex.Message}】,模组条码【{ModuleBarcode}】");}}return cnt;});return task;}/// <summary>/// 这里执行事务/// </summary>/// <param name="sqlCollection"></param>/// <param name="dictCollection"></param>private void ExecuteTransaction(List<string> sqlCollection, List<Dictionary<string, object>> dictCollection) {Random random = new Random((int)DateTime.Now.Ticks);int millisecondsTimeout = random.Next(100, 300);System.Threading.Thread.Sleep(millisecondsTimeout);DisplayMessage($"执行数据库事务,耗时【{millisecondsTimeout}】ms");}private async void btnSave_Click(object sender, EventArgs e){OpenFileDialog openFileDialog = new OpenFileDialog();openFileDialog.Filter = "Excel文件|*.xls;*.xlsx";DialogResult dialog = openFileDialog.ShowDialog();if (dialog != DialogResult.OK){return;}string fileName = openFileDialog.FileName;try{DataTable dtImport = NpoiExcelOperateUtil.ExcelToTable(fileName);if (!dtImport.Columns.Contains("序号")){MessageBox.Show($"必须包含列【序号】,请检查excel格式.路径\n【{fileName}】", "提示");return;}if (!dtImport.Columns.Contains("模组条码")){MessageBox.Show($"必须包含列【模组条码】,请检查excel格式.路径\n【{fileName}】", "提示");return;}if (!dtImport.Columns.Contains("极性编码")){MessageBox.Show($"必须包含列【极性编码】,请检查excel格式.路径\n【{fileName}】", "提示");return;}if (!dtImport.Columns.Contains("模组类型")){MessageBox.Show($"必须包含列【模组类型】,请检查excel格式.路径\n【{fileName}】", "提示");return;}//绑定网格数据dgvData.DataSource = dtImport;int rowCount = dtImport.Rows.Count;if (rowCount < 1){MessageBox.Show($"Excel不存在有效的上传数据配置行.路径\n【{fileName}】", "提示");return;}Task<int> task = UpdateDatabase(dtImport);await task;DisplayMessage($"导入模组极柱数据成功,实际导入数据条数【{task.Result}】......");}catch (Exception ex){MessageBox.Show($"读取excel出现异常,请检查是否是标准的Excel文件.【{ex.Message}】路径\n【{fileName}】", "出错");return;}}private void btnExport_Click(object sender, EventArgs e){SaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd-HHmmss") + ".xls";saveFileDialog.Filter = "Excel文件|*.xls;*.xlsx";DialogResult dialog = saveFileDialog.ShowDialog();if (dialog != DialogResult.OK){return;}string fileName = saveFileDialog.FileName;DataTable dt = new DataTable("TableToExcel");dt.Columns.Add("序号", typeof(int));dt.Columns.Add("模组条码", typeof(string));dt.Columns.Add("极性编码", typeof(string));dt.Columns.Add("模组类型", typeof(string));//最多保留3行for (int i = 0; i < Math.Min(dgvData.Rows.Count, 3); i++){dt.Rows.Add(dgvData[0, i].Value, dgvData[1, i].Value, dgvData[2, i].Value, dgvData[3, i].Value);}try{NpoiExcelOperateUtil.TableToExcel(dt, fileName);MessageBox.Show($"保存示例模板成功,保存数据条数【{dt.Rows.Count}】.\n路径【{fileName}】", "提示");}catch (Exception ex){MessageBox.Show($"保存示例模板出现异常,请检查是否是标准的Excel文件.【{ex.Message}】路径\n【{fileName}】", "出错");return;}}}
}
程序运行如图: