1、要安装或引用DLL;“Microsoft.Office.Interop.Excel.dll”,复制到bin/debug目录下;
2 、在项目解决方案上引用;
网上搜到的函数 ,我建 了个通用类Excel ;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
public class Excel { public static DataTable ReadFromExcel(string excelpath) { string sExt = System.IO.Path.GetExtension(excelpath); string sConn = null; if (sExt == ".xlsx") { sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + "Extended Properties='Excel 12.0;HDR=YES'"; } else if (sExt == ".xls") { sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + "Extended Properties=Excel 8.0"; } else { throw new Exception("文件格式有误"); } OleDbConnection oledbConn = new OleDbConnection(sConn); oledbConn.Open(); /// //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等 DataTable dtSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //包含excel中表名的字符串数组 string[] strTableNames = new string[dtSheetName.Rows.Count]; for (int k = 0; k < dtSheetName.Rows.Count; k++) { strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); } OleDbDataAdapter command = new OleDbDataAdapter("SELECT * FROM [" + strTableNames[0] + "]", oledbConn); DataSet ds = new DataSet(); command.Fill(ds); oledbConn.Close(); return ds.Tables[0]; } }
调用方法,引用 Excel.ReadFromExcel(excelpath);:
OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { string excelpath = ofd.FileName; dataGridView1.DataSource = Excel.ReadFromExcel(excelpath); }
参考1:解决 |Sheet1 名称出错问题
(53条消息) 'Sheet1$' 不是一个有效名称的解决方法_心如止水-CSDN博客_不是一个有效名称
参考2:
(53条消息) C#读取Excel到DataGridView(两种方式)_CSDNwhyu的博客-CSDN博客_c# datagridview excel
我试了参考2的两种方法 ,第一种方法速度太慢