// 엑셀 가져오기 함수
private void importExcel()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "엑셀 파일 선택하세요";
openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
DialogResult result = openFileDialog.ShowDialog();
if (result == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;
//엑셀 앱
Excel.Application app = new Excel.Application();
Excel.Workbooks workbooks = app.Workbooks;
//엑셀 워크북(파일경로읽어서)
Excel.Workbook workbook = workbooks.Open(openFileDialog.FileName);
// 엑셀 워크싯 객체
Excel.Sheets sheets = workbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
// 엑셀파일 이름이 나온다
string excelFileName = workbook.Name;
//excelFileName = excelFileName.Substring(book.Name.Length-3);
string[] str = excelFileName.Split('.');
// 워크시트 첫번째 이름
string workSheetName = worksheet.Name;
try
{
if (str[1].Equals("xls"))
{
// 연결 string
string constr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='"
+ openFileDialog.FileName + "';Extended Properties=Excel 8.0;";
//경로까지 다 포함해서 .xls라고 뜨네;
//MessageBox.Show(openFileDialog.FileName);
// excel conn
OleDbConnection conn = new OleDbConnection(constr);
// excel cmd
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + workSheetName + "$]", conn);
conn.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
this.Cursor = Cursors.WaitCursor;
dataGridView1.DataSource = dt;
this.Cursor = Cursors.Default;
conn.Close();
}
else if (str[1].Equals("xlsx"))
{
// 연결 string
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
openFileDialog.FileName +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
//경로까지 다 포함해서 .xls라고 뜨네;
//MessageBox.Show(openFileDialog.FileName);
// excel conn
OleDbConnection conn = new OleDbConnection(constr);
// excel cmd
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + workSheetName + "$]", conn);
conn.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
this.Cursor = Cursors.Default;
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
//메모리
workbook.Close(Type.Missing, openFileDialog.FileName, Type.Missing);
app.Quit();
if(worksheet!=null) releaseObject(worksheet);
if (sheets != null) releaseObject(sheets);
if (workbook != null) releaseObject(workbook);
if (workbooks != null) releaseObject(workbooks);
if (app != null) releaseObject(app);
}
}
}
#region 메모리해제
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception e)
{
obj = null;
throw e;
}
//finally
//{
// GC.Collect();
//}
}
#endregion
