winfrom选择Excel文件并转成DataTable
直接上代码:
//导入
private void btnImport_Click(object sender, EventArgs e)
{
string fileName = string.Empty;
fileName = GetFileName();
if (fileName == string.Empty) return;
DataTable tbTF = InputFromExcel(fileName, "Sheet1");
}
//选择Excel文件
private string GetFileName()
{
string fileName = string.Empty;
{
OpenFileDialog dlgOpen = new OpenFileDialog();
dlgOpen.Filter = "表格文件 (*.xls)|*.xls";
if (dlgOpen.ShowDialog(this) == DialogResult.Cancel)
{
return string.Empty;
}
fileName = dlgOpen.FileName;
dlgOpen = null;
}
return fileName;
}
//读取Excel数据
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.Ace.OleDb.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 12.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;
}
//读取Excle文件
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.Ace.OleDb.12.0;Extended Properties=Excel 12.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;
}
就这些,复制粘贴直接用