Aspose.cells 处理Excel文件

最新推荐文章于 2024-02-24 10:08:39 发布

weixin_30328063

最新推荐文章于 2024-02-24 10:08:39 发布

阅读量127

点赞数

原文链接：http://www.cnblogs.com/buzi521/p/3851520.html

版权

本文介绍了一个使用C#实现的Excel数据导入程序示例。该程序包括浏览并选择Excel文件、读取指定工作表的数据及将数据导入到数据库等功能。通过此示例可以了解到如何在C#中操作Excel文件并进行数据处理。

摘要由CSDN通过智能技术生成

//浏览Excel文件
        private void btnScan_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.DefaultExt = "xlsx";
            ofd.Filter = "Excel file|*.xlsx";
            if (ofd.ShowDialog() == true)
            {
                this.txtFilePath.Text = ofd.FileName;
            }
        }

        //读取Excel
        private DataTable ReadExcel(string strFileName, int i)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[i-1];
            Cells cells = sheet.Cells;

            return cells.ExportDataTableAsString(1, 0, cells.MaxDataRow, cells.MaxDataColumn + 1);
        }

        //导入Excel数据
        private void btnImport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (this.txtFilePath.Text.Trim().Length <= 0)
                {
                    MessageBox.Show("Please select import file!");
                    return;
                }

                int tid = Convert.ToInt32(cbProductType.SelectedValue.ToString());
                int pid = Convert.ToInt32(cbOfferCompany.SelectedValue.ToString());
                DataTable dt = ReadExcel(this.txtFilePath.Text.Trim(), 1);
                int iCount = dt.Rows.Count;
                this.pbRoll.Value = 0;
                this.pbRoll.Visibility = Visibility.Visible;
                
                Action<int> method = x => { this.pbRoll.Value = x; DispatcherHelper.DoEvents(); };
                int index = 0;

                DataClasses1DataContext db = new DataClasses1DataContext();

                for (int i = 0; i < iCount; i++)
                {
                    index++;
                    if (method != null) method(index * 100 / iCount);

                    TBProduct pro = new TBProduct();
                    pro.No = dt.Rows[i][0].ToString();
                    pro.Name = dt.Rows[i][1].ToString();
                    pro.SalePrice = StringToDecimal(dt.Rows[i][2].ToString());
                    pro.SRP = StringToDecimal(dt.Rows[i][3].ToString());
                    pro.TId = tid;
                    pro.PId = pid;
                    pro.IsDelete = 1;
                    pro.State = 1;

                    db.TBProduct.InsertOnSubmit(pro);
                    db.SubmitChanges();
                }

                this.lbResult.Content = "Import Records:" + iCount.ToString();

                MessageBox.Show("Import Successful!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Import Failed!\n\n" + ex.ToString());
            }
            finally 
            {
                this.pbRoll.Value = 100;
                this.pbRoll.Visibility = Visibility.Hidden;
            }
        }