编写 read_excel.cs 如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.OleDb;namespace ReadExcel
{public partial class Program{static void Main(string[] args){if (args.Length <1){Console.WriteLine(" usage: read_excel your_file.xlsx ");return ;}if (! File.Exists(args[0])){Console.WriteLine("Error: {0} not exists.", args[0]);return ;}if (Path.GetExtension(args[0]) != ".xlsx"){Console.WriteLine("Tip: can only read file.xlsx");}string filePath = args[0]; // your_excel_file_pathstring connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", filePath);using (OleDbConnection conn = new OleDbConnection(connStr)){conn.Open();string sheet1 = "Sheet1";string query = string.Format("SELECT * FROM [{0}$]", sheet1);using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn)){DataTable dataTable = new DataTable();adapter.Fill(dataTable);int rows, cols;// 处理获取到的数据foreach (DataRow row in dataTable.Rows){rows = row.Table.Rows.IndexOf(row) +1;foreach (DataColumn column in dataTable.Columns){string value = row[column].ToString() ?? string.Empty; cols = column.Ordinal +1;Console.WriteLine("Cell({0:d},{1:d}): {2}", rows,cols,value);}}}} Console.ReadKey();}}
}
SET PATH=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;%PATH%
编译:csc.exe /t:exe read_excel.cs
环境:win10 64位系统 运行 \your\path\read_excel.exe test1.xlsx
错误信息:未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。
搜索 Microsoft Access Database Engine 2016
我先下载了 accessdatabaseengine.exe 安装好后,还是运行出错。
卸载了32位版本,又下载了 AccessDatabaseEngine_X64.exe 安装好后,能运行了。
参考:C#读取Excel表格数据到DataGridView中和导出DataGridView中的数据到Excel
C#读取Excel表格数据到DataGridView中,代码如下
private void btnShow_Click(object sender, EventArgs e){ //首先根据打开文件对话框,选择excel表格OpenFileDialog fd = new OpenFileDialog();fd.Filter = "xlsx表格|*.xlsx"; //打开文件对话框筛选器string strPath;//文件完整的路径名if (fd.ShowDialog() == DialogResult.OK){try{strPath = fd.FileName;string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";OleDbConnection Con = new OleDbConnection(strCon);//建立连接string strSql = "select * from [Sheet1$]";//表名的写法也应注意不同,对应的excel表为sheet1,在这里要在其后加美元符号$,并用中括号OleDbCommand Cmd = new OleDbCommand(strSql, Con);//建立要执行的命令OleDbDataAdapter da = new OleDbDataAdapter(Cmd);//建立数据适配器DataSet ds = new DataSet();//新建数据集da.Fill(ds, "sheet1");//把数据适配器中的数据读到数据集中的一个表中(此处表名为sheet1,可以任取表名)//指定datagridview1的数据源为数据集ds的第一张表(也就是sheet1表),也可以写ds.Table["sheet1"]dataGridView1.DataSource = ds.Tables[0];}catch (Exception ex){MessageBox.Show(ex.Message);//捕捉异常}}}