C#之Excel操作

標籤：tables   void   type   final   apt   tostring   bre   dap   new   

下面的這幾個方法是我在項目中經常用到的，歡迎大家批評指正

讀取Excel表中的資料

第一種：功能豐富，速度慢

 　　　　/// <summary>        /// 從Excel讀取資料        /// </summary>        /// <param name="path">Excel文檔的全路徑</param>        /// <returns>將一行作為一個對象，所以返回的是很多行資料的對象</returns>        public Model[] ReadDataFromExcel(string path)        {            List<Model> list = new List<Model>();            Application excelApp = null;            try            {                excelApp = new Application() { Visible = false, DefaultFilePath = "", DisplayAlerts = true, SheetsInNewWorkbook = 1 };                Workbooks books = excelApp.Workbooks;                if (File.Exists(path))                {                    Workbook book = books.Open(path);                    Worksheet sheet = book.Sheets.get_Item(1);                    if (sheet != null)                    {                        int rowNum = sheet.UsedRange.Rows.Count;                        DateTime timeLimit;                        timeLimit = GetTime(((Range)sheet.Cells[2, "B"]).Text.ToString()).AddSeconds(sectionNum*timeSpan*60);                                                //從第2行開始讀資料                        for (int i = 2; i < rowNum; i++)                        {                            if (((Range)sheet.Cells[i, "A"]).Text != "" && ((Range)sheet.Cells[i, "B"]).Text != "")                            {                                if (timeLimit.CompareTo(GetTime(((Range)sheet.Cells[i, "B"]).Text.ToString())) < 0)                                {                                    break;                                }                                else                                {                                    Model m = new Model()                                    {                                        SN = ((Range)sheet.Cells[i, "A"]).Text.ToString(),                                        time = GetTime(((Range)sheet.Cells[i, "B"]).Text.ToString()),                                        ch1 = ((Range)sheet.Cells[i, "C"]).Text.ToString(),                                        ch2 = ((Range)sheet.Cells[i, "D"]).Text.ToString(),                                        ch3 = ((Range)sheet.Cells[i, "E"]).Text.ToString(),                                        ch4 = ((Range)sheet.Cells[i, "F"]).Text.ToString()                                    };                                    list.Add(m);                                }                                                            }                        }                    }                }            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                //執行完讀取Excel資料後，將關閉該進程                KillExcel(excelApp);            }            return list.ToArray();        }

第二種：速度快

  public DataTable ExcelToDS(string filePath)        {            string connStr = "";            string fileType = System.IO.Path.GetExtension(Path.GetFileName(filePath));            if (string.IsNullOrEmpty(fileType))            {                return null;            }            if (fileType == ".xls")                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";            else                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";            OleDbConnection conn = new OleDbConnection(connStr);            conn.Open();            string strExcel = "";            OleDbDataAdapter myCommand = null;            DataSet ds = null;            strExcel = "select * from [sheet1$]";            myCommand = new OleDbDataAdapter(strExcel, connStr);            ds = new DataSet();            myCommand.Fill(ds, "table1");            return ds.Tables[0];        }

 

 

關閉Excel進程：

  [DllImport("user32.dll", SetLastError = true)]        static extern int GetWindowThreadProcessId(IntPtr hwnd, out int processId);        public void KillExcel(Application excelApp)        {            try            {                if(excelApp!=null)                {                    int processId;                    GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd), out processId);                    Process p = Process.GetProcessById(processId);                    p.Kill();                }            }            catch (Exception ex)            {                                throw ex;            }        }

讀取Excel儲存格資料：

  /// <summary>        /// 擷取儲存格資料        /// </summary>        /// <param name="sheet"></param>        /// <param name="row"></param>        /// <param name="col"></param>        /// <returns></returns>        public string GetValue(Worksheet sheet, int row, int col)        {            // 取得儲存格.            var cell = (Range)sheet.Cells[row, col];            if ((bool)cell.MergeCells == true)            {                // 本儲存格是 “合併儲存格”                if (cell.MergeArea.Row == row                && cell.MergeArea.Column == col)                {                    // 目前的儲存格 就是 合併儲存格的 左上方 內容.                    return cell.Text.ToString();                }                else                {                    // 返回 合併儲存格的 左上方 內容.                    return ((Range)(sheet.Cells[cell.MergeArea.Row, cell.MergeArea.Column])).Text.ToString();                }            }            else            {                // 本儲存格是 “普通儲存格”                // 擷取文本資訊.                return cell.Text.ToString();            }        }

 根據文本擷取該文本所在行號和所佔的行數，一般主要用來讀取合併儲存格

/// <summary>      /// 根據文本擷取行號和所佔行數      /// </summary>      /// <param name="sheet">查詢的表單</param>      /// <param name="text">查詢的文本</param>      /// <param name="rowNumber">行號</param>      /// <param name="rowCounts">所佔行數</param>        public void GetRowNumber(Worksheet sheet, string text,out int rowNumber,out int rowCounts)        {            rowNumber = -1;            rowCounts = -1;            foreach(Range range in sheet.UsedRange)            {                if (range.Value == null)                    continue;                if (range.Value.ToString() == text)                {                    rowNumber = range.Row;                    if ((bool)range.MergeCells == true)                        rowCounts = range.MergeArea.Rows.Count;                    else                        rowCounts = 1;                    break;                }            }        }

 

C#之Excel操作