将Excel文件导入C＃或VB.Net中的Datagridview [英] Import Excel file to Datagridview in C# or VB.Net

问题描述

你好，我有一个具有以下字段名称，mobileNo，TotalCoupen的excel文件。我想将这些字段导入具有唯一序列号的datagridview中。如果一个人的总数为5 coupen，它将显示5 coupen序列，例如（10001,10002,10003 ，10004,10005）我还附加了图像

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文件，但没有生成coupen否，仅导入excel文件

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

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

}

推荐答案

我不确定为什么要添加这些恕我直言的重复行。一个简单的解决方案是用额外的行创建一个新的 DataTable 。循环浏览excel数据表中的所有行，然后循环每个新行总优惠券次，然后将 coupen no 更新为这个图片显示着。同样，我不确定您为什么要这样做，但这是一种方法。下面的代码从 ReadExcel DataTable 中创建了一个新的 DataTable c>方法。 AddDuplicates 方法根据需要添加行。

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.

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

这篇关于将Excel文件导入C＃或VB.Net中的Datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！