C# Read data in Excel

C# Read data in Excel

   #region 读取Excel中的数据
        /// <summary> 
        /// 读取Excel中的数据
        /// </summary> 
        /// <param name="excelFile">Excel文件名及路径,EG:C:\Users\JK\Desktop\导入测试.xls</param> 
        /// <returns>Excel中的数据</returns> 
        private DataTable GetTable(string fileName)
        { 
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            string connString = string.Empty;
            OleDbDataAdapter da = new OleDbDataAdapter();
            //获取Excel工作薄中Sheet页(工作表)名集合
            String[] ss = this.GetExcelSheetNames(fileName);
            DataTable dataTable = new DataTable();   
            try
            {               
                string FileType = fileName.Substring(fileName.LastIndexOf("."));
                if (FileType == ".xls")
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
                else//.xlsx
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                // 创建连接对象 
                objConn = new OleDbConnection(connString);
                // 打开数据库连接 
                objConn.Open();
               
                string sql_F = "Select * FROM [{0}]";
                for (int i = 0; i < ss.Length;i++ )
                {
                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, ss[i].ToString() + "$"), objConn);
                    da.Fill(dataTable);
                    MessageBox.Show("第"+i+"次表中数据量="+dataTable.Rows.Count.ToString());
                }
                dataTable = DeleteBlank(dataTable,9);
                MessageBox.Show("删除空行后,表中数据量=" + dataTable.Rows.Count.ToString());
                return dataTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return null;
            }
            finally
            {
                // 清理 
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }           
        }

        #endregion

 #region  删除指定表中的空白行
        /// <summary>
        ///删除指定表中的空白行 
        /// </summary>
        /// <param name="dt">表名</param>
        /// <param name="ColNum">Excel中的列数</param>
        /// <returns>删除空白行后的DataTable</returns>
        private DataTable DeleteBlank(DataTable dt,int ColNum)
        {
            if (dt == null || dt.Rows.Count==0)
            {
                return dt;
            }
            //删除其中的空行(注意for循环的形式)
            for (int i = dt.Rows.Count - 1; i >= 0; i--)
            {
                DataRow row = dt.Rows[i];
                bool flag = true;
                //当某行的ColNum列,均为空时,改行为空
                for (int j = 0; j < ColNum; j++)
                {
                    object o = row[j];
                    if (o != DBNull.Value && Convert.ToString(o).Trim().Length > 0)
                    {
                        flag = false;
                        break;
                    }
                }
                if (flag)
                {
                    dt.Rows[i].Delete();                 
                }
            }
            dt.AcceptChanges();
            //把行中DBNull列替换成空字符串
            for (int k = dt.Rows.Count - 1; k >= 0; k--)
            {
                DataRow row = dt.Rows[k];
                for (int z = 0; z < ColNum; z++)
                {
                    object o = row[z];
                    if (o == DBNull.Value)
                    {
                        if (dt.Columns[z].DataType == typeof(string))
                        {
                            row[z] = "";
                        }
                    }
                }
            }
            dt.AcceptChanges();
            return dt;
        }
        #endregion

Small note:

When reading Excel, the table header will be automatically processed.

The above is the content of C# reading data in Excel. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!