发布：2022/9/15 16:44:36作者：管理员 来源：本站 浏览次数：2033
此次导入Excel数据借助了ExcelDataReader插件。（由于我需要的是打开工作簿时能够选择工作表绑定于datagridview控件，通过网上资源的浏览与借鉴，ExcelDataReader能满足我的需求。）一、插件安装
（1）安装ExcelDataReader插件（如图）
（2）查看是否安装成功（如图表明已安装成功）
二、代码编写
（1）为了方便使用，打开文件的过程构造为一个方法
DataTableCollection tableCollection;public void OpenFile()
{
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
if (file.ShowDialog() == DialogResult.OK)
{
txtFileName.Text = file.FileName;//对话框中选择的文件名
//1：打开文件，得到文件流stream
FileStream stream = File.Open(file.FileName, FileMode.Open, FileAccess.Read);//FileStream:用于文件中任何位置的读写。
var sw = new Stopwatch();//定时器
sw.Start();
//2：得到文件reader
var reader = ExcelReaderFactory.CreateReader(stream);
//3：通过reader得到数据
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
//标题行的内容是否显示
UseHeaderRow = true
}
});
var openTiming = sw.ElapsedMilliseconds;
toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
//4：得到ExcelFile文件的表Sheet
tableCollection = result.Tables;
cboSheet.Items.Clear();
foreach (DataTable item in tableCollection)
{
cboSheet.Items.Add(item.TableName);
Console.WriteLine(item.TableName);
}
}
}
（2）效果展示（图上为控件name）
（3）数据绑定方法
public static void BindDataGridView(DataTable dt, DataGridView dgv)
{
/*dgv.ClearSelection();
//不像用户显示添加行
dgv.AllowUserToAddRows = false;
dgv.DataSource = null;*/
//DataTable存储数据
dgv.DataSource = dt;
}
（4）选择工作表，绑定相应数据
private void dgv_CellContentClick()
{
//datagridview组件的颜色
dgvData.RowsDefaultCellStyle.BackColor = Color.White;
Console.WriteLine(cboSheet.SelectedItem.ToString());
var dt = tableCollection[cboSheet.SelectedItem.ToString()];
BindDataGridView(dt, dgvData);
dgvData.DataSource = dt;
// DataGridView取消选中第一行第一列方法(绑定数据源后)
dgvData.Rows[0].Cells[0].Selected = false;
dgvData.Rows[0].Selected = false;
}
（5）在相应控件调用方法即可
private void btnFilenpath_Click(object sender, EventArgs e)
{
OpenFile();
}
private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
dgv_CellContentClick();
}
这就完成将Excel数据导入到Excel的过程啦！
下面是全部源码：
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WinSampling
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/*
private void button1_Click(object sender, EventArgs e)
{
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{ Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog.FileName;//文件路径
}
else
{
label2.Text = "文件框无法打开！";
}
}
}
private DataTable ReadExcelToTable(string path, int x) //excel存放的路径
{
try
{
//string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
string conStr =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;",
path);
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(conStr))
{
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string sheetName = dtSheet.Rows[x]["Table_Name"].ToString();
string sql = string.Format("select * from [{0}]", sheetName);
oda.SelectCommand = new OleDbCommand(sql, conn);
oda.Fill(ds);
dt = ds.Tables[0];
return dt;
}
}
catch (Exception ex)
{
label2.Text = ex.Message;
return null;
}
}
private void button2_Click(object sender, EventArgs e)
{
DataTable dt = ReadExcelToTable(textBox1.Text, 0);
dataTableToListview(listView1, dt);
}
//fill the listview
public void dataTableToListview(ListView lv, DataTable dt)
{
if (dt != null)
{
lv.Items.Clear();
lv.Columns.Clear();
for (int i = 0; i < dt.Columns.Count; i++)
{
lv.Columns.Add(dt.Columns[i].Caption.ToString());
}
foreach (DataRow dr in dt.Rows)
{
ListViewItem lvi = new ListViewItem();
lvi.SubItems[0].Text = dr[0].ToString();
for (int i = 1; i < dt.Columns.Count; i++)
{
lvi.SubItems.Add(dr[i].ToString());
}
lv.Items.Add(lvi);
}
lv.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
}
public void listViewToDataTable(ListView lv, DataTable dt)
{
int i, j;
DataRow dr;
dt.Clear();
dt.Columns.Clear();
//生成DataTable列头
for (i = 0; i < lv.Columns.Count; i++)
{
dt.Columns.Add(lv.Columns[i].Text.Trim(), typeof(String));
}
//每行内容
for (i = 0; i < lv.Items.Count; i++)
{
dr = dt.NewRow();
for (j = 0; j < lv.Columns.Count; j++)
{
dr[j] = lv.Items[i].SubItems[j].Text.Trim();
}
dt.Rows.Add(dr);
}
}
*/
DataTableCollection tableCollection;
public void OpenFile()
{
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
if (file.ShowDialog() == DialogResult.OK)
{
txtFileName.Text = file.FileName;//对话框中选择的文件名
//1：打开文件，得到文件流stream
FileStream stream = File.Open(file.FileName, FileMode.Open, FileAccess.Read);//FileStream:用于文件中任何位置的读写。
var sw = new Stopwatch();//定时器
sw.Start();
//2：得到文件reader
var reader = ExcelReaderFactory.CreateReader(stream);
//3：通过reader得到数据
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
//标题行的内容是否显示
UseHeaderRow = true
}
});
var openTiming = sw.ElapsedMilliseconds;
//toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
//4：得到ExcelFile文件的表Sheet
tableCollection = result.Tables;
cboSheet.Items.Clear();
foreach (DataTable item in tableCollection)
{
cboSheet.Items.Add(item.TableName);
Console.WriteLine(item.TableName);
}
}
}
private void dgv_CellContentClick()
{
//datagridview组件的颜色
dgvData.RowsDefaultCellStyle.BackColor = Color.White;
Console.WriteLine(cboSheet.SelectedItem.ToString());
var dt = tableCollection[cboSheet.SelectedItem.ToString()];
BindDataGridView(dt, dgvData);
dgvData.DataSource = dt;
// DataGridView取消选中第一行第一列方法(绑定数据源后)
dgvData.Rows[0].Cells[0].Selected = false;
dgvData.Rows[0].Selected = false;
}
public static void BindDataGridView(DataTable dt, DataGridView dgv)
{
/*dgv.ClearSelection();
//不像用户显示添加行
dgv.AllowUserToAddRows = false;
dgv.DataSource = null;*/
//DataTable存储数据
dgv.DataSource = dt;
}
private void btnFilenpath_Click(object sender, EventArgs e)
{
OpenFile();
}
private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
dgv_CellContentClick();
}
}
}
