从Excel中检索列和行 [英] From Excel Retrieve the Column and rows

问题描述

按如下方式浏览按钮代码

  private   void  Browse_Click（ object  sender，EventArgs e）
 {
 尝试 
 {
 DialogResult MsgDr = MessageBox.Show（  Excel文件格式应该是学生姓名，家长姓名，手机号码，标记。\ n您确定要继续吗？， 确认，MessageBoxButtons.YesNo，MessageBoxIcon.Question）; 
  if （MsgDr == DialogResult.No）
  return ; 
 
 LblSheetName.Text =  ; 
 OpenFileDialog fldg =  new  OpenFileDialog（）; 
 fldg.Title =  选择文件; 
 fldg.FileName = txtFileName.Text; 
 fldg.Filter =   Excel Sheet（*。xls）| * .xls | Excel-2007 Sheet（ * .xlsx）| * .xlsx |所有文件（*。*）| *。*; 
 fldg.FilterIndex =  1 ; 
 fldg.RestoreDirectory =  true ;  //  已添加 
 如果 （fldg.ShowDialog（）== DialogResult.OK）
 {
 txtFileName.Text = fldg.FileName; 
 FileInfo file =  new  FileInfo（txtFileName.Text）; 
  if （IsFileLocked（file）==  true ） //  已添加 
 {
 MessageBox.Show（ 所选文件已经打开/未正确关闭/由其他人使用。请正确关闭并再试一次，  文件已经打开，MessageBoxButtons.OK）; 
 txtFileName.Text =  string  .Empty; 
  return ; 
}  //  已添加 
 Import（）; 
 System.Windows.Forms.Application.DoEvents（）; 
} 
} 
  catch （例外ex1）
 {
 progressBar1.Visible = 假; 
 MessageBox.Show（ 打开时出错 + ex1.ToString（）， 错误，MessageBoxButtons.OK）; 
  return ; 
} 
} 
 
 私有  void 导入（ ）
 {
  string  message =  string  .Empty; 
 尝试 
 {
  int  i =  0 ; 
 
 progressBar1.Visible =  true ; 
 progressBar1.Value =  700 ; 
  String  Pathname = txtFileName.Text; 
 FileStream stream = File.Open（Pathname，FileMode.Open，FileAccess.Read）; 
 IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader（stream）
 excelReader.IsFirstRowAsColumnNames =  true ; 
 DataSet result = excelReader.AsDataSet（）; 
 excelReader.Close（）; 
 System.Data.DataTable table = result.Tables [ 0 ]; 
  for （ int  irow =  1 ; i <  table.Rows.Count; irow ++）
 {
  for （ int  icol =  3 ; icol <  table.Columns.Count; icol ++）
 {
 message + = table.Rows [irow] [icol] .ToString（）; 
} 
} 
 label2.Text = message.ToString（）; 
 dataGridView1.DataSource = table; 
 progressBar1.Visible =  false ; 
} 
  catch （Exception ex）
 {
 MessageBox.Show（message.ToString（））; 
 progressBar1.Visible =  false ; 
 MessageBox.Show（ 导入错误 + ex.ToString（）， 错误，MessageBoxButtons.OK）; 
  return ; 
} 
} 

Excel表格如下

 REO MC CL PH 
 
 50 60 70 80 
 10 40 80 60 

运行模式如下



考试标记文件textbox1浏览（按钮）



当我点击浏览（按钮）时，所选的Excel文件将显示在Datagridview中。



在DataGridView中如下;

 REO MC CL PH 
 
 50 60 70 80 
 10 40 80 60 

我正在做的是我在该标签上有一个标签我显示所有标记如下（来自Excek文件）

标记（标签）5060708010408060 
 

当我点击浏览按钮时，标签显示在Label2中，如下所示

标记（标签）5060708010408060 
 

但是我希望标记为与主题名称一起显示如下

标记（标签）REO50MC60CL70PH80REO10MC40CL80PH60 
 

来自我上面的代码我犯了什么错误。



问候，

Narasiman P. div class =h2_lin>解决方案

分别提取标题并存储它。您也可以打印标题和分数。

Browse Button code as follows

private void Browse_Click(object sender, EventArgs e)
{
   try
   {
      DialogResult MsgDr = MessageBox.Show("Excel file format should be in Student Name,Parent Name,Mobile No,Marks. \nAre you sure want to continue?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
      if (MsgDr == DialogResult.No)
         return;

      LblSheetName.Text = "";
      OpenFileDialog fldg = new OpenFileDialog();
      fldg.Title = "Select File";
      fldg.FileName = txtFileName.Text;
      fldg.Filter = "Excel Sheet(*.xls)|*.xls|Excel-2007 Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
      fldg.FilterIndex = 1;
      fldg.RestoreDirectory = true; //added
      if (fldg.ShowDialog() == DialogResult.OK)
      {
         txtFileName.Text = fldg.FileName;
         FileInfo file = new FileInfo(txtFileName.Text);
         if(IsFileLocked(file) == true) //added
         {
            MessageBox.Show("Selected file is opened already/not properly closed/using by some other person. Close it properly and try again", "File already opened", MessageBoxButtons.OK);
            txtFileName.Text = string.Empty;
            return;
         }     //added
         Import();
         System.Windows.Forms.Application.DoEvents();
      }
   }
   catch (Exception ex1)
   {
      progressBar1.Visible = false;
      MessageBox.Show("Error in open" + ex1.ToString(), "Error", MessageBoxButtons.OK);
      return;
   }
}

private void Import()
{
   string message = string.Empty;
   try
   {
      int i = 0;                

      progressBar1.Visible = true;
      progressBar1.Value = 700;
      String Pathname = txtFileName.Text;
      FileStream stream = File.Open(Pathname, FileMode.Open, FileAccess.Read);
      IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream)
      excelReader.IsFirstRowAsColumnNames = true;
      DataSet result = excelReader.AsDataSet();
      excelReader.Close();
      System.Data.DataTable table = result.Tables[0];
      for (int irow = 1; i < table.Rows.Count; irow++)
      {
         for(int icol = 3; icol < table.Columns.Count; icol++)
         {
            message += table.Rows[irow][icol].ToString();
         }
      }
      label2.Text = message.ToString();
      dataGridView1.DataSource = table;
      progressBar1.Visible = false;
   }
   catch (Exception ex)
   {
      MessageBox.Show(message.ToString());
      progressBar1.Visible = false;
      MessageBox.Show("Error in Import" + ex.ToString(), "Error", MessageBoxButtons.OK);
      return;
   }
}

Excel sheet as follows

REO   MC  CL PH

 50   60  70 80
 10   40  80 60

Run mode as follows

Exam Marks File textbox1 Browse(Button)

When i click the Browse (Button) Selected Excel File will be displayed in the Datagridview.

In DataGridView As follows;

REO   MC  CL   PH

 50    60  70  80
 10    40  80  60

What i am doing is i have one Label in that label i am displaying all the marks as follows(From the Excek File)

Marks (Label)    5060708010408060

When i click the Browse button marks are displaying in the Label2 as follows

Marks (Label)    5060708010408060

But i want the marks to be displayed along with the subject name as follows

Marks (Label)    REO50MC60CL70PH80REO10MC40CL80PH60

for that from my above code what is the mistake i made.

Regards,
Narasiman P.

解决方案

Extract the headers separately and store it. You can alternatively print the headers and scores.

这篇关于从Excel中检索列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！