1、装完读取插件才可以对EXCEL读取

Excel 2010 读取数据插件

https://www.microsoft.com/zh-CN/download/details.aspx?id=13255

 

 

2、ExcelHelper，需要引用Excel COM组件

using System;

using System.IO;

using System.Data;

using System.Collections;

using System.Data.OleDb;

namespace ViewBoard

{

/// <summary>

/// Excel操作类

/// </summary>

/// Microsoft Excel 11.0 Object Library

public class ExcelHelper

{

public static DataTable InputFromExcel(string ExcelFilePath, string TableName)

{

if (!File.Exists(ExcelFilePath))

{

throw new Exception("Excel文件不存在！");

}

//如果数据表名不存在，则数据表名为Excel文件的第一个数据表

ArrayList TableList = new ArrayList();

TableList = GetExcelTables(ExcelFilePath);

if (TableName.IndexOf(TableName) < 0)

{

TableName = TableList[0].ToString().Trim();

}

DataTable table = new DataTable();

OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");

OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);

OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

try

{

if (dbcon.State == ConnectionState.Closed)

{

dbcon.Open();

}

adapter.Fill(table);

}

catch (Exception exp)

{

throw exp;

}

finally

{

if (dbcon.State == ConnectionState.Open)

{

dbcon.Close();

}

}

return table;

}

public static ArrayList GetExcelTables(string ExcelFileName)

{

DataTable dt = new DataTable();

ArrayList TablesList = new ArrayList();

if (File.Exists(ExcelFileName))

{

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))

{

try

{

conn.Open();

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

}

catch (Exception exp)

{

throw exp;

}

//获取数据表个数

int tablecount = dt.Rows.Count;

for (int i = 0; i < tablecount; i++)

{

string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');

if (TablesList.IndexOf(tablename) < 0)

{

TablesList.Add(tablename);

}

}

}

}

return TablesList;

}

public static DataSet ExcelToDataSet(string ExcelFileName)

{

DataSet ds = new DataSet();

DataTable tables = null;

DataTable dt = null;

ArrayList TablesList = new ArrayList();

if (File.Exists(ExcelFileName))

{

using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" + ExcelFileName))

{

try

{

conn.Open();

tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

for (int i = 0; i < tables.Rows.Count; i++)

{

string sql = "select * from [" + tables.Rows[i][2] + "];";

OleDbCommand cmd = new OleDbCommand(sql, conn);

OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

dt = new DataTable();

dt.TableName = tables.Rows[i][2].ToString().Replace("$", "");

adapter.Fill(dt);

ds.Tables.Add(dt);

}

}

catch (Exception exp)

{

throw exp;

}

finally {

if (conn.State == ConnectionState.Open)

{

conn.Close();

}

}

}

}

return ds;

}

}

}