May 20, 2018

엑셀 시트명이 가변일 때 엑셀 첫번째 시트 가져오기 C#

string oleDBCon = "";
           
oleDBCon += @"Provider=Microsoft.ACE.OLEDB.12.0;";
oleDBCon += @"Data Source=";
oleDBCon += FilePath.Text + ";";
oleDBCon += @"Extended Properties=""Excel 12.0;";
oleDBCon += @"HDR=YES;"";";

OleDbConnection excelConnection = new OleDbConnection(oleDBCon);
excelConnection.Open();

var dtSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
OleDbCommand dbCommand = new OleDbCommand(String.Format("SELECT * FROM [{0}]", sheet1), excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
dataAdapter.Fill(dTable);
ExceldataGridView.DataSource = dTable;

// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
excelConnection.Close();
excelConnection.Dispose();

No comments:

Post a Comment