将Excel文件导入到C＃或VB.Net中的Datagridview

问题描述：

您好我有以下字段名称，mobileNo，TotalCoupen.i excel文件想要在datagridview中导入这些字段与唯一的序列号如果一个人总共5 coupen它会显示5 coupen串行像（10001,10002,10003,10004,10005）我还附上图片将Excel文件导入到C＃或VB.Net中的Datagridview

这里是我的代码这段代码加载Excel文件successfuly但不产生coupen没有只excel导入文件

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Data; 
using System.Linq; 
using System.Text; 
using System.Windows.Forms; 
using System.IO; 
using System.Data.SqlClient; 
using System.Configuration; 
using System.Data.OleDb; 
using Excel = Microsoft.Office.Interop.Excel; 

namespace ReadExcelFileApp 
{ 
public partial class Form1 : Form 
{ 
    public Form1() 
    { 
     InitializeComponent(); 
    } 

    private void Form1_Load(object sender, EventArgs e) 
    { 
     dataGridView1.Visible = false; 
    } 

    private void btnChoose_Click(object sender, EventArgs e) 
    { 
     string filePath = string.Empty; 
     string fileExt = string.Empty; 
     OpenFileDialog file = new OpenFileDialog();//open dialog to choose file 
     if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK)//if there is a file choosen by the user 
     { 
      filePath = file.FileName;//get the path of the file 
      fileExt = Path.GetExtension(filePath);//get the file extension 
      if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) 
      { 
       try 
       { 
        DataTable dtExcel = new DataTable(); 
        dtExcel = ReadExcel(filePath, fileExt);//read excel file 
        dataGridView1.Visible = true; 
        dataGridView1.DataSource = dtExcel; 
       } 
       catch (Exception ex) 
       { 
        MessageBox.Show(ex.Message.ToString()); 
       } 
      } 
      else 
      { 
       MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);//custom messageBox to show error 
      } 
     } 
    } 

    private void btnClose_Click(object sender, EventArgs e) 
    { 
     this.Close();//to close the window(Form1) 
    } 

    public DataTable ReadExcel(string fileName, string fileExt) 
    { 
     string conn = string.Empty; 
     DataTable dtexcel = new DataTable(); 
     if (fileExt.CompareTo(".xls") == 0)//compare the extension of the file 
      conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";//for below excel 2007 
     else 
      conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";//for above excel 2007 
     using (OleDbConnection con = new OleDbConnection(conn)) 
     { 
      try 
      { 
       OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);//here we read data from sheet1 
       oleAdpt.Fill(dtexcel);//fill excel data into dataTable 
      } 
      catch(Exception ex) 
      { 
       MessageBox.Show(ex.Message.ToString()); 
      } 
     } 
     return dtexcel; 
    } 
}

}

你可能需要出示你试图吸引一个答案看看[如何创建一个最小的，完整的，并且可验证的示例]（http://stackoverflow.com/help/mcve）也见[我如何问一个好问题？]（http://stackoverflow.com/help/how-to-ask） – OSKM

在你的网格中匹配excel字段的列名？ – ADyson

好的，但我如何在每一行中添加唯一的coupen数字像上面的图像 – user757321

答

我不知道为什么你想要添加这些恕我直拨重复行。一个简单的解决方案是创建一个新的DataTable与额外的行。循环遍历Excel数据表中的所有行，然后为每个新行循环total coupen次，然后更新coupen no，如图所示。我不知道为什么你会这样做，但这是一种方法。下面的代码从DataTable开始新的DataTable从ReadExcel方法返回。根据需求，AddDuplicates方法添加行。

dtExcel = ReadExcel(filePath, fileExt);//read excel file 
DataTable dgvTable = AddDuplicates(dtExcel); 
dataGridView1.Visible = true; 
//dataGridView1.DataSource = dtExcel; 
dataGridView1.DataSource = dgvTable; 

private DataTable AddDuplicates(DataTable dt) { 
    DataTable dtcopy = dt.Clone(); 
    int curCount = 100000; 
    double coupenCount = 0; 
    foreach(DataRow dr in dt.Rows) { 
    coupenCount = (double)dr.ItemArray[2]; 
    for (int i = 0; i < coupenCount; i++) { 
     dtcopy.Rows.Add(dr.ItemArray[0], dr.ItemArray[1], ++curCount); 
    } 
    } 
    return dtcopy; 
}

awsome工作jhon克非常感谢你 – user757321

答

试试这样做。

using System; 
using System.Data; 
using System.Windows.Forms; 
using System.Data.SqlClient; 

namespace WindowsFormsApplication1 
{ 
    public partial class Form1 : Form 
    { 
     public Form1() 
     { 
      InitializeComponent(); 
     } 

     private void button1_Click(object sender, EventArgs e) 
     { 
      System.Data.OleDb.OleDbConnection MyConnection; 
      System.Data.DataSet DtSet; 
      System.Data.OleDb.OleDbDataAdapter MyCommand; 
      MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); 
      MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); 
      MyCommand.TableMappings.Add("Table", "Net-informations.com"); 
      DtSet = new System.Data.DataSet(); 
      MyCommand.Fill(DtSet); 
      dataGridView1.DataSource = DtSet.Tables[0]; 
      MyConnection.Close(); 
     } 
    } 
}

将Excel文件导入到C＃或VB.Net中的Datagridview

相关推荐