C#读取excel数据并呈现
本文仅为自身学习记录
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ApExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data.OleDb;
namespace Input
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void 导入ToolStripMenuItem_Click(object sender, EventArgs e)
{
string strConn = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件(.xls)|*.xls|Excel文件(.xlsx)|*.xlsx|CSV UTF-8(逗号分隔)(.csv)|*.csv|CSV(逗号分隔)(.csv)|*.csv";
if(ofd.ShowDialog()==DialogResult.OK)
{
string path = ofd.FileName;
string ext = Path.GetExtension(path);
if(ext==".xls")
{
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
//IMEX = 0 表示Excel只能用作写入,1 只能作读取,2 读写都可;
//HDR = YES 第一行是标题, NO 第一行是数据。
}
else
{
strConn = @"Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}
DataTable dtData = new DataTable();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable dtNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string shName = dtNames.Rows[0][2].ToString();
string sql = "select * from ["+shName+"]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
da.Fill(dtData);
}
dataGridView1.DataSource = dtData;
}
}
}
}