winform Excel导入进度条2
1.主界面
引用
using ExcelDataReader; using System; using System.ComponentModel; using System.Data; using System.IO; using System.Threading; using System.Windows.Forms; namespace ExcelToolsWinform { public partial class Form1 : Form { FileStream fs = null; IExcelDataReader excelReader = null; private DataTable dt = new DataTable(); public Form1() { DevExpress.Data.CurrencyDataController.DisableThreadingProblemsDetection = true;//devexpress夸线程操作数据的解决方案 InitializeComponent(); } /// <summary> /// Excelr操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnImport_Click(object sender, EventArgs e) { OpenFileDialog fileDialog = new OpenFileDialog(); fileDialog.Title = "请选择文件"; fileDialog.Filter = "所有文件(*xls*)|*.xls*"; //设置要选择的文件的类型 if (fileDialog.ShowDialog() == DialogResult.OK) { textEdit1.Text = fileDialog.FileName; dt = new DataTable(); gridControl1.DataSource = null; //防止重复执行异步操作引发错误 if (backgroundWorker1.IsBusy) return; this.backgroundWorker1.RunWorkerAsync(); //运行backgroundWorker组件 backgroundWorker1.WorkerReportsProgress = true; //设置能报告进度更新 backgroundWorker1.WorkerSupportsCancellation = true; //设置支持异步取消 ProgressForm form = new ProgressForm(this.backgroundWorker1); //显示进度条窗体 form.ShowDialog(this); form.Close(); } } //在另一个线程上开始运行(处理进度条) private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { BackgroundWorker worker = sender as BackgroundWorker; string[] columnName = { "col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8" }; try { fs = new FileStream(textEdit1.Text, FileMode.Open, FileAccess.Read); excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs); //DataSet ds = excelReader.AsDataSet(); int rowCount = excelReader.RowCount;//所有的行 int readIndex = 0; //生成列头 for (int i = 0; i <= columnName.Length - 1; i++) { var name = columnName[i]; while (dt.Columns.Contains(name)) { name = name + "_1";//重复行名称会报错。 } dt.Columns.Add(new DataColumn(name, typeof(string))); } int rowIdx = 0;//第一行为标题 实际数据从第二行开始 while (excelReader.Read()) { if (readIndex <= rowIdx) { ++readIndex; continue; } DataRow dr = dt.NewRow(); int colNum = excelReader.FieldCount; for (int iCol = 0; iCol <= columnName.Length - 1; iCol++) { if (iCol >= colNum) { continue; } dr[iCol] = excelReader.GetValue(iCol) == null ? "" : excelReader.GetValue(iCol).ToString(); } dt.Rows.Add(dr); Thread.Sleep(0); //不推迟取消按钮点不了，推迟的话数据量大后进度会缓慢 worker.ReportProgress(readIndex * 100 / rowCount);//加载进度条 if (worker.CancellationPending) // 如果用户取消则跳出处理数据代码 { e.Cancel = true; break; } ++readIndex; } fs.Close(); fs.Dispose(); excelReader.Close(); excelReader.Dispose(); } catch (Exception ex) { if (worker.CancellationPending) // 如果用户取消则跳出处理数据代码 { e.Cancel = true; } if (fs != null) { fs.Close(); fs.Dispose(); } if (excelReader != null) { excelReader.Close(); excelReader.Dispose(); } throw new Exception("" + ex.Message); } } private void backgroundWorker1_DoWork1(object sender, DoWorkEventArgs e) { BackgroundWorker worker = sender as BackgroundWorker; for (int i = 0; i < 100; i++) { System.Threading.Thread.Sleep(100); worker.ReportProgress(i); if (worker.CancellationPending) //获取程序是否已请求取消后台操作 { e.Cancel = true; break; } } } private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { if (e.Error != null) { MessageBox.Show(e.Error.Message); } else if (e.Cancelled) { dt = new DataTable(); //如果取消重新定义datatable this.labelControl4.Text = "取消" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); MessageBox.Show("取消"); } else { gridControl1.DataSource = dt; this.labelControl4.Text = "成功"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); MessageBox.Show("完成"); } } private void simpleButton1_Click(object sender, EventArgs e) { dt = new DataTable(); //防止重复执行异步操作引发错误 if (backgroundWorker1.IsBusy) return; this.backgroundWorker1.RunWorkerAsync(); //运行backgroundWorker组件 backgroundWorker1.WorkerReportsProgress = true; //设置能报告进度更新 backgroundWorker1.WorkerSupportsCancellation = true; //设置支持异步取消 ProgressForm form = new ProgressForm(this.backgroundWorker1); //显示进度条窗体 form.ShowDialog(this); form.Close(); } private void btnSave_Click(object sender, EventArgs e) { gridView1.CloseEditor(); gridView1.UpdateCurrentRow(); var data = gridView1.DataSource; } } }
2.进度界面
using DevExpress.XtraEditors; 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 ExcelToolsWinform { public partial class ProgressForm : DevExpress.XtraEditors.XtraForm { private BackgroundWorker backgroundWorker1; //ProgressForm窗体事件(进度条窗体) public ProgressForm(BackgroundWorker bgWork) { InitializeComponent(); // add my code this.backgroundWorker1 = bgWork; //绑定进度条改变事件 this.backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged); //绑定后台操作完成，取消，异常时的事件 this.backgroundWorker1.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker1_RunWorkerCompleted); } void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e) { this.progressBar1.Value = e.ProgressPercentage; //获取异步任务的进度百分比 } void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { this.Close(); //执行完之后，直接关闭页面 } //取消 private void btnCancel_Click(object sender, EventArgs e) { this.backgroundWorker1.CancelAsync(); //请求取消挂起的后台操作 this.btnCancel.Enabled = false; this.Close(); } } }
using ExcelDataReader;using System;using System.ComponentModel;using System.Data;using System.IO;using System.Threading;using System.Windows.Forms;
namespace ExcelToolsWinform{ public partial class Form1 : Form { FileStream fs = null; IExcelDataReader excelReader = null; private DataTable dt = new DataTable(); public Form1() { DevExpress.Data.CurrencyDataController.DisableThreadingProblemsDetection = true;//devexpress夸线程操作数据的解决方案 InitializeComponent(); } /// <summary> /// Excelr操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnImport_Click(object sender, EventArgs e) { OpenFileDialog fileDialog = new OpenFileDialog(); fileDialog.Title = "请选择文件"; fileDialog.Filter = "所有文件(*xls*)|*.xls*"; //设置要选择的文件的类型 if (fileDialog.ShowDialog() == DialogResult.OK) { textEdit1.Text = fileDialog.FileName; dt = new DataTable(); gridControl1.DataSource = null; //防止重复执行异步操作引发错误 if (backgroundWorker1.IsBusy) return; this.backgroundWorker1.RunWorkerAsync(); //运行backgroundWorker组件 backgroundWorker1.WorkerReportsProgress = true; //设置能报告进度更新 backgroundWorker1.WorkerSupportsCancellation = true; //设置支持异步取消 ProgressForm form = new ProgressForm(this.backgroundWorker1); //显示进度条窗体 form.ShowDialog(this); form.Close(); } }
//在另一个线程上开始运行(处理进度条) private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { BackgroundWorker worker = sender as BackgroundWorker; string[] columnName = { "col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8" }; try { fs = new FileStream(textEdit1.Text, FileMode.Open, FileAccess.Read); excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs); //DataSet ds = excelReader.AsDataSet(); int rowCount = excelReader.RowCount;//所有的行 int readIndex = 0; //生成列头 for (int i = 0; i <= columnName.Length - 1; i++) { var name = columnName[i]; while (dt.Columns.Contains(name)) { name = name + "_1";//重复行名称会报错。 } dt.Columns.Add(new DataColumn(name, typeof(string))); }
int rowIdx = 0;//第一行为标题 实际数据从第二行开始
while (excelReader.Read()) { if (readIndex <= rowIdx) { ++readIndex; continue; } DataRow dr = dt.NewRow(); int colNum = excelReader.FieldCount; for (int iCol = 0; iCol <= columnName.Length - 1; iCol++) { if (iCol >= colNum) { continue; } dr[iCol] = excelReader.GetValue(iCol) == null ? "" : excelReader.GetValue(iCol).ToString(); } dt.Rows.Add(dr); Thread.Sleep(0); //不推迟取消按钮点不了，推迟的话数据量大后进度会缓慢 worker.ReportProgress(readIndex * 100 / rowCount);//加载进度条 if (worker.CancellationPending) // 如果用户取消则跳出处理数据代码 { e.Cancel = true; break; } ++readIndex; }
fs.Close(); fs.Dispose(); excelReader.Close(); excelReader.Dispose(); } catch (Exception ex) { if (worker.CancellationPending) // 如果用户取消则跳出处理数据代码 { e.Cancel = true; } if (fs != null) { fs.Close(); fs.Dispose(); } if (excelReader != null) { excelReader.Close(); excelReader.Dispose(); } throw new Exception("" + ex.Message); } } private void backgroundWorker1_DoWork1(object sender, DoWorkEventArgs e) { BackgroundWorker worker = sender as BackgroundWorker; for (int i = 0; i < 100; i++) { System.Threading.Thread.Sleep(100); worker.ReportProgress(i); if (worker.CancellationPending) //获取程序是否已请求取消后台操作 { e.Cancel = true; break; } } }
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { if (e.Error != null) { MessageBox.Show(e.Error.Message); } else if (e.Cancelled) { dt = new DataTable(); //如果取消重新定义datatable this.labelControl4.Text = "取消" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); MessageBox.Show("取消"); } else { gridControl1.DataSource = dt; this.labelControl4.Text = "成功"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); MessageBox.Show("完成");
} }
private void simpleButton1_Click(object sender, EventArgs e) { dt = new DataTable(); //防止重复执行异步操作引发错误 if (backgroundWorker1.IsBusy) return; this.backgroundWorker1.RunWorkerAsync(); //运行backgroundWorker组件 backgroundWorker1.WorkerReportsProgress = true; //设置能报告进度更新 backgroundWorker1.WorkerSupportsCancellation = true; //设置支持异步取消 ProgressForm form = new ProgressForm(this.backgroundWorker1); //显示进度条窗体 form.ShowDialog(this); form.Close(); }
private void btnSave_Click(object sender, EventArgs e) { gridView1.CloseEditor(); gridView1.UpdateCurrentRow(); var data = gridView1.DataSource; } }}
