C# WinFrom 导入Excel文件，显示进度条

C# WinFrom 导入Excel文件，显示进度条

因为WINForm程序是在64位上运行如果使用另外一种快速的读取Excel的方法会报“未在本地计算机上注册“Microsoft.Jet.OLEDB.12.0”提供程序”

所以我就换了现在这种读取有点慢的方式

PS 采用上一种方式要更改成32位，由于我的系统有其他需求只有64位支持，所以不得不放弃，而且也需要客户端注册这个

Form1

控件 一个显示路径的TextBox: txt_ExcelPath

两个按钮 Button:btn_selectpath,btn_savedata

一个backgroundWorker1组件

 

 /// <summary>
        /// 选择Excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_selectpath_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            openFile.Multiselect = false;
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                txt_ExcelPath.Text = openFile.FileName;
            }
        }

 /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_datainto_Click(object sender, EventArgs e)
        {
            dt = new System.Data.DataTable();
            this.backgroundWorker1.RunWorkerAsync(); // 运行 backgroundWorker 组件 
            backgroundWorker1.WorkerReportsProgress = true;
            backgroundWorker1.WorkerSupportsCancellation = true;
            RadProcessBar form = new RadProcessBar(this.backgroundWorker1);// 显示进度条窗体 
            form.ShowDialog(this);
            form.Close();


           
        }

此处的RadProcessBar 是显示进度条的窗体（进度条期间禁止其他操作）

以下是backgroundWorker1组件的一些事件

        /// <summary>
        /// 完成进程工作之后的处理动作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                MessageBox.Show(e.Error.Message);
            }
            else if (e.Cancelled)
            {
                dt = new System.Data.DataTable(); //如果取消重新定义datatable
            }
            else
            {
                dataGridView1.DataSource = dt;
            }
        }

         private  System.Data.DataTable dt = new System.Data.DataTable();

        //你可以在这个方法内，实现你的调用，方法等。
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            //System.Data.DataTable dt = GetDataFromExcelByCom();
            //dataGridView1.DataSource = dt;
            BackgroundWorker worker = sender as BackgroundWorker;           
            string[] columnName = { "1", "2", "3", "4", "5", "6", "7", "8" };
            string excelFilePath = txt_ExcelPath.Text.Trim();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            object oMissiong = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
           

            try
            {
                if (app == null) return ;
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                sheets = workbook.Worksheets;

                //将数据读入到DataTable中
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(2);//读取第二张表 次数我的测试Excel中数据在2表中  
                if (worksheet == null) return ;

                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = 8;// worksheet.UsedRange.Columns.Count;//个人根据需求选择数据源的列数
                //生成列头
                for (int i = 0; i < iColCount; i++)
                {
                    var name = columnName[i]; //"column" + i;
                    while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                    dt.Columns.Add(new DataColumn(name, typeof(string)));
                }
                //生成行数据
                Microsoft.Office.Interop.Excel.Range range;
                int rowIdx = 2;//第一行为标题 实际数据从第二行开始
                for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                        dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
                    }
                    dt.Rows.Add(dr);
                    Thread.Sleep(0);
                    worker.ReportProgress(iRow*100/iRowCount);//加载进度条
                    if (worker.CancellationPending)  // 如果用户取消则跳出处理数据代码 
                    {
                        e.Cancel = true;
                        break;
                    }
                    
                }

            }
            catch { return; }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
        }

进度条Form RadProcessBar

一个progressBar1 一个按钮

public partial class RadProcessBar : Form
    {
        
        private BackgroundWorker backgroundWorker1; //ProcessForm 窗体事件(进度条窗体) 

        public RadProcessBar(BackgroundWorker backgroundWorker1)
        {
            InitializeComponent();

            this.backgroundWorker1 = backgroundWorker1;
            this.backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged);
            this.backgroundWorker1.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker1_RunWorkerCompleted);
        }

        void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.Close();//执行完之后，直接关闭页面
        }

        void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            this.progressBar1.Value = e.ProgressPercentage;
        }

        private void btn_cancel_Click(object sender, EventArgs e)
        {
            this.backgroundWorker1.CancelAsync();
            this.btn_cancel.Enabled = false;
            this.Close();
        }
    }

 

posted on 2015-07-08 17:36  Jruik.shen 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Jruik/p/Excel.html