Aspose.cells 處理Excel檔案

標籤：style   blog   color   os   檔案   資料   

//瀏覽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;            }        }