c#获取excel文件里的内容有几行

发布网友发布时间：2022-05-01 21:44

共4个回答

热心网友时间：2022-06-23 23:08

/// <summary>
        /// 将 Excel 文件转成 DataTable
        /// </summary>
        /// <param name="serverMapPathExcel">Excel文件及其路径</param>
        /// <param name="strSheetName">工作表名,如:Sheet1$</param>
        /// <param name="isTitleOrDataOfFirstRow">True 第一行是标题,False 第一行是数据</param>
        /// <returns>DataTable</returns>
        public DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow, int index)
        {
            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            //源的定义
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + serverMapPathExcel + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";
            //Sql语句
            //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
            //string strExcel = "select * from [" + strSheetName + "]";
            ////定义存放的数据表
            //DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            //连接数据源
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    DataTable dtSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    conn.Close();

                    //MessageBox.Show(dtSchema.Rows.Count.ToString() + "列:" + dtSchema.Columns.Count);
                    //for (int i = 0; i < dtSchema.Rows.Count; i++)
                    //{
                    //    richTextBox1.Text = richTextBox1.Text + (i + ":" + dtSchema.Rows[i][2].ToString()) + "\n";
                    //}
                    string tableName = dtSchema.Rows[index][2].ToString().Trim();
                    MessageBox.Show(tableName);
                    OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM [" + tableName + "A1:AK2000]", conn);//excel的AG列
                    //OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM [" + tableName + "]", conn);
                    OleDbDataAdapter oa = new OleDbDataAdapter(OleDbCommand);
                    oa.FillSchema(dt, SchemaType.Source);
                    oa.Fill(dt);
                    //oa.FillSchema(dt2, SchemaType.Source);
                    //oa.Fill(dt2);
                    //RemoveEndEmptyLines(ref dt2, dt2.Rows.Count - 1);
                    //dt2.Rows.RemoveAt(0);

                    //dataGridView1.DataSource = dt2;

                    //适配到数据源
                    //OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    //adapter.Fill(ds, strSheetName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error");
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return dt;//ds.Tables[strSheetName];
        }
        //调用时
        OpenFileDialog ofd = new OpenFileDialog();
            ofd.Multiselect = false;
            ofd.Title = "请选择入职员工所在的Excel文件[请确保该Excel已关闭]";
            ofd.Filter = "excel file|*.xls|所有文件|*.*";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                int hireIndex = int.Parse(numericUpDown1.Value.ToString("N0"));//入职表格所在的所在的索引
                //richTextBox1.Text = richTextBox1.Text + hireIndex + "\n";
                try
                {
                    DataTable dt = ExcelToDataTable(ofd.FileName, "Sheet1$", true, hireIndex);
                    richTextBox1.Text = richTextBox1.Text + "行数:" + dt.Rows.Count + "\n";
                    Application.DoEvents();
                    }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error,请检查excel是否符合规则");
                    richTextBox1.Text = richTextBox1.Text + "导入出错";
                    Application.DoEvents();
                    return;
                }
                richTextBox1.Text = richTextBox1.Text + "操作结束";
                Application.DoEvents();
            }
//返回的DataTable 的行数就是你要的结果

热心网友时间：2022-06-23 23:08

将excel数据读取到dataset中，取到Tables(0).Rows.Count 即可。

热心网友时间：2022-06-23 23:09

问题没表述清楚

热心网友时间：2022-06-23 23:09

int row_ = xlsheet.UsedRange.Rows.Count;