将Excel文件导入c#或VB.Net中的Datagridview

hi i have excel file with following fields name,mobileNo,TotalCoupen.i want to import these field in datagridview with unique serial no.if a person have total 5 coupen it will show 5 coupen serial like (10001,10002,10003,10004,10005) i also attach image

你好，我有excel文件，有以下字段名，mobileNo,TotalCoupen。我想将这些字段导入datagridview中，并使用唯一的序列号。如果一个人总共有5个coupen，它会显示5个coupen系列，比如(10001,10002,10003,10004,10005)我也会附上图片

here is my code this code load excel file successfuly but not generate coupen no it only import excel file

这是我的代码这段代码成功地加载了excel文件但没有生成coupen no它只导入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;
    }
}

}

}

2 个解决方案

#1

I am not sure why you would want to add these IMHO duplicate rows. A simple solution is to create a new DataTable with the extra rows. Loop through all the rows in the excel data table then loop total coupen times for each new row then update coupen no as the picture shows. Again I am not sure why you would do this but here is one way. The code below makes a new DataTable from the DataTable returned from ReadExcel method. The AddDuplicates methods add the rows as per the requirement.

我不确定为什么要添加这些IMHO重复行。一个简单的解决方案是使用额外的行创建一个新的DataTable。循环遍历excel数据表中的所有行，然后循环每个新行的总coupen时间，然后更新coupen no，如图所示。我不知道你们为什么要这么做，但有一个方法。下面的代码从ReadExcel方法返回的DataTable生成一个新的DataTable。addduplicate方法按照需求添加行。

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;
}

#2

Try doing it this way.

试着这样做。

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();
        }
    }
}