[C#][WinForm] 讀取 Excel

  • 3788
  • 0
  • 2019-09-02

目前知識淺薄,只知道讀寫Excel 相關的有三種方式:
一、使用Oledb的方式
二、使用 Microsoft Excel XX.X Object Library 參考
三、NPOI (寫Web時使用)

 

關於第一種方式,我沒嘗試過。
這篇主要筆記第二種方式。

===== じゃあ、始めよう ======

不意外的,從一顆按鈕開始....

        private void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult ans = MessageBox.Show("確定匯入資料?", "訊息視窗",
                    MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (ans == DialogResult.OK)
                {
                    ImportFile(); 
                }
                else
                {
                    return;
                }

            }
            catch (Exception ex)
            {
                string error = ex.ToString;
                MessageBox.Show(error, "執行錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

        }

再來是抓取檔案/匯入

       protected void ImportFile()
        {       
            OpenFileDialog dialog = new OpenFileDialog(); //建立檔案選擇視窗
            dialog.Title = "Please your files";           
            dialog.InitialDirectory = ".\\";              
            dialog.Filter = "xls Files(*.xls; *.xlsx;)| *.xls; *.xlsx;"; 
                           //"xls Files(*.xls; *.xlsx;)| *.xls; *.xlsx; | All files(*.*) | *.*"
            string msg = "";
            string xlsfile = "";

            if (dialog.ShowDialog() == DialogResult.OK)
            {
                xlsfile = dialog.FileName;
                msg =  "Are you sure import " + dialog.FileName + " ?";
                DialogResult ans = MessageBox.Show(msg, "Check Message", 
                                   MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                if (ans == DialogResult.OK)
                {
                    //1.DataTable
                    dt_cap1 = null;
                    //2.抓取Excel內容
                    DataTable dtXls = ExcelProcess(xlsfile); 
                    dt_cap1 = ExcelProcess(xlsfile); //Excel內容抓取至datatable
                    if (dt_cap1.Rows.Count > 0)
                    {
                        //3.抓取後存至畫面元件
                        dataGridView1.DataSource = dt_cap1;
                    }
                    MessageBox.Show("Import Compelete!");
                }
            }
        }

        public DataTable ExcelProcess(string path)
        {
            DataTable dtExcel = new DataTable();
            dtExcel.Columns.Add("item_number"); 
            dtExcel.Columns.Add("item_value"); 
            dtExcel.Columns.Add("special_value"); 
            dtExcel.Columns.Add("order_value"); 
            dtExcel.Columns.Add("change_value");


            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int row = 12;
            int col1 = 1; //A 欄位起始值
            int col2 = 7; //G

            progressBar1.Visible = true;
            progressBar1.Minimum = 0;


            //part1 
            for (int i = 1; i == col1; i++)
            {
                for (int j = 12; j >= row; j++)
                {
                    if (xlRange.Cells[j, i].Value2 != null)  //xlRange.Cells[j, i].Value != null!= null
                    {
                        DataRow dRow = dtExcel.NewRow();
                        dRow["item_number"] = (xlRange.Cells[j, i] as Excel.Range).Text;
                        dRow["item_value"] = "0";
                        dRow["special_value"] = "0";
                        dRow["order_value"] = "0";
                        dtExcel.Rows.Add(dRow);
                    }
                    else
                    {
                        break;
                    }
                }
            }
            progressBar1.Maximum = dtExcel.Rows.Count;
            
            //part2
            for (int i = 7; i == col2; i++)
            {
                for (int j = 12; j >= row; j++)
                {
                    if (xlRange.Cells[j, i].Value != null)
                    {
                        DataRow dRow = dtExcel.Rows[j - 12];
                        dRow["change_value"] = xlRange.Cells[j, i].Value;
                        if (progressBar1.Value < progressBar1.Maximum)
                        { progressBar1.Value += 1; }
                    }
                    else { break; }
                }
            }



            /*--------------------*/

            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close(true);
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
            Marshal.FinalReleaseComObject(xlApp);

            return dtExcel;
        }

未盡事宜,想到再補充。