<div>안녕하세요 C# winform에 엑셀 로드하고 저장하는 기능을 구현중인데 도저히 속도 개선이 나오질 않아 질문 드립니다.</div> <div>현재 interop.excel을 사용해서 로드중인데 속도를 개선하는 방법이 없을까요??<br>oledb 방식으로 하게되면 사용자 컴퓨터에서도 드라이버를 설치해야되는 번거로움과 드라이버를 설치해도 "Microsoft.ACE.OELDB.12.0 공급자는 로컬 컴퓨터에 등록할 수 없습니다" 라는 메시지를 반환하고 에러가 납니다.</div> <div>interop방식으로 속도개선을 할수 있는 방법이나.. oledb방식으로 했을때 에러 해소 방안을 물어보고 싶어요 ㅠ</div> <div> </div> <div>1. form 디자인</div> <div style="text-align:left;"><img width="800" height="425" class="chimg_photo" style="border:;" alt="excel_Capture.PNG" src="http://thimg.todayhumor.co.kr/upfile/201807/153179012998f5017effed4771b09fdadcb2b0e990__mn496571__w1196__h636__f39656__Ym201807.png" filesize="39656"></div> <div style="text-align:left;"> </div> <div style="text-align:left;">2. C# 코드 - 엑셀로드 부분만</div> <div style="text-align:left;">using System;<br>using System.Collections.Generic;<br>using System.ComponentModel;<br>using System.Data;<br>using System.Drawing;<br>using System.Linq;<br>using System.Text;<br>using System.Threading.Tasks;<br>using System.Windows.Forms;<br>using System.IO;<br>using System.Data.OleDb;<br>using System.Reflection;<br>using Excel = Microsoft.Office.Interop.Excel;<br>using System.Runtime.InteropServices;<br></div> <div style="text-align:left;">//엑셀 로드부분</div> <div style="text-align:left;">private void btn_Open_Click(object sender, EventArgs e)<br> {<br> //openFileDialog1.ShowDialog();</div> <div style="text-align:left;"> Excel.Application xlApp = null;<br> Excel.Workbook xlWorkbook = null;<br> Excel.Worksheet xlWorksheet = null;<br> Excel.Worksheet xlWorksheet2 = null;</div> <div style="text-align:left;"> OpenFileDialog ofd = new OpenFileDialog();<br> ofd.Filter = "Excel File (*.xlsx)|*.xlsx|Excel File 97~2003 (*.xls)|*.xls|All Files (*.*)|*.*";</div> <div style="text-align:left;"> if (ofd.ShowDialog() == DialogResult.OK)<br> {<br> try<br> {<br> excelDataView.Columns.Clear();</div> <div style="text-align:left;"> DataTable dt = new DataTable();</div> <div style="text-align:left;"> xlApp = new Excel.Application();<br> xlWorkbook = xlApp.Workbooks.Open(ofd.FileName);<br> gFileName = ofd.FileName;<br> xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);</div> <div style="text-align:left;"> string[] sheet_Name = new string[xlWorkbook.Worksheets.Count];</div> <div style="text-align:left;"> cmbSheetName.Items.Clear();<br> for (int i = 1; i <= xlWorkbook.Worksheets.Count; i ++)<br> {<br> xlWorksheet2 = xlWorkbook.Worksheets[i] as Excel.Worksheet;<br> cmbSheetName.Items.Add(i.ToString() + "-" + xlWorksheet2.Name);<br> }</div> <div style="text-align:left;"> Excel.Range range = xlWorksheet.UsedRange;</div> <div style="text-align:left;"> object[,] data = range.Value;</div> <div style="text-align:left;"> for (int i = 1; i <= range.Columns.Count; i++)<br> {<br> dt.Columns.Add(i.ToString(), typeof(string));<br> }</div> <div style="text-align:left;"> for (int r = 1; r < range.Rows.Count; r++)<br> {<br> DataRow dr = dt.Rows.Add();</div> <div style="text-align:left;"> for (int c = 1; c < range.Columns.Count; c++)<br> {<br> dr[c - 1] = data[r, c];<br> }<br> }</div> <div style="text-align:left;"> xlWorkbook.Close(true);<br> xlApp.Quit();</div> <div style="text-align:left;"> excelDataView.DataSource = dt;</div> <div style="text-align:left;"> }<br> catch (Exception ex)<br> {<br> MessageBox.Show(ex.Message);<br> }<br> finally<br> {<br> ReleaseExcelObject(xlWorksheet);<br> ReleaseExcelObject(xlWorkbook);<br> ReleaseExcelObject(xlApp);<br> ReleaseExcelObject(xlWorksheet2);<br> }<br> }<br> }</div> <div><br> </div> <div>3. 전체 C# 코드</div> <div>using System;<br>using System.Collections.Generic;<br>using System.ComponentModel;<br>using System.Data;<br>using System.Drawing;<br>using System.Linq;<br>using System.Text;<br>using System.Threading.Tasks;<br>using System.Windows.Forms;<br>using System.IO;<br>using System.Data.OleDb;<br>using System.Reflection;<br>using Excel = Microsoft.Office.Interop.Excel;<br>using System.Runtime.InteropServices;</div> <div>namespace WindowsFormsApp2<br>{<br> public partial class Form1 : Form<br> {<br> private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'";<br> private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'";<br> private string gfileExtension = "";<br> private string gFileName = "";<br> private int Search_Count = 0;</div> <div> public Form1()<br> {<br> InitializeComponent();<br> UserDefine_Control();<br> }</div> <div> private void UserDefine_Control()<br> {<br> this.btn_Open.Click += new System.EventHandler(this.btn_Open_Click);<br> this.btn_Save.Click += new System.EventHandler(this.btn_Save_Click);<br> this.btn_col.Click += new System.EventHandler(this.btn_col_Click);<br> this.btn_Search.Click += new System.EventHandler(this.btn_Search_Click);<br> this.txtSearch.KeyDown += new KeyEventHandler(this.termKey_KeyDown);<br> this.cmbSheetName.SelectedIndexChanged += new System.EventHandler(this.cmbSheetName_SelectedIndexChanged);<br> this.excelDataView.MouseClick += new MouseEventHandler(this.excelDataView_MouseClick);<br> excelDataView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;<br> excelDataView.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSteelBlue;<br> //excelDataView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;</div> <div> this.button1.Click += new System.EventHandler(this.btn_test);<br> }</div> <div> private void excelDataView_MouseClick(object sender, MouseEventArgs e)<br> {<br> if (e.Button == MouseButtons.Right)<br> {<br> EventHandler eh = new EventHandler(MenuClick);<br> MenuItem[] ami =<br> {<br> new MenuItem("숨김",eh)<br> };<br> ContextMenu = new System.Windows.Forms.ContextMenu(ami);<br> }<br> }<br> <br> private void MenuClick(object sender, EventArgs e)<br> {<br> MenuItem menu_item = (MenuItem)sender;<br> String str = menu_item.Text;</div> <div> if (str == "숨김")<br> {<br> int rowIndex = 0;<br> rowIndex = excelDataView.CurrentCell.RowIndex;</div> <div> CurrencyManager curr = (CurrencyManager)BindingContext[excelDataView.DataSource];<br> curr.SuspendBinding();<br> excelDataView.Rows[rowIndex].Visible = false;<br> curr.ResumeBinding();<br> Search_Count = Search_Count - 1;<br> toolStripStatusLabel1.Text = "검색수 : " + Search_Count.ToString();<br> }<br> }</div> <div><br> //특정행으로 이동<br> private void btn_test(object sender, EventArgs e)<br> {<br> this.excelDataView.CurrentCell = this.excelDataView[1, 0];<br> }</div> <div> private void btn_Open_Click(object sender, EventArgs e)<br> {<br> //openFileDialog1.ShowDialog();</div> <div> Excel.Application xlApp = null;<br> Excel.Workbook xlWorkbook = null;<br> Excel.Worksheet xlWorksheet = null;<br> Excel.Worksheet xlWorksheet2 = null;</div> <div> OpenFileDialog ofd = new OpenFileDialog();<br> ofd.Filter = "Excel File (*.xlsx)|*.xlsx|Excel File 97~2003 (*.xls)|*.xls|All Files (*.*)|*.*";</div> <div> if (ofd.ShowDialog() == DialogResult.OK)<br> {<br> try<br> {<br> excelDataView.Columns.Clear();</div> <div> DataTable dt = new DataTable();</div> <div> xlApp = new Excel.Application();<br> xlWorkbook = xlApp.Workbooks.Open(ofd.FileName);<br> gFileName = ofd.FileName;<br> xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);</div> <div> string[] sheet_Name = new string[xlWorkbook.Worksheets.Count];</div> <div> cmbSheetName.Items.Clear();<br> for (int i = 1; i <= xlWorkbook.Worksheets.Count; i ++)<br> {<br> xlWorksheet2 = xlWorkbook.Worksheets[i] as Excel.Worksheet;<br> cmbSheetName.Items.Add(i.ToString() + "-" + xlWorksheet2.Name);<br> }</div> <div> Excel.Range range = xlWorksheet.UsedRange;</div> <div> object[,] data = range.Value;</div> <div> for (int i = 1; i <= range.Columns.Count; i++)<br> {<br> dt.Columns.Add(i.ToString(), typeof(string));<br> }</div> <div> for (int r = 1; r < range.Rows.Count; r++)<br> {<br> DataRow dr = dt.Rows.Add();</div> <div> for (int c = 1; c < range.Columns.Count; c++)<br> {<br> dr[c - 1] = data[r, c];<br> }<br> }</div> <div> xlWorkbook.Close(true);<br> xlApp.Quit();</div> <div> excelDataView.DataSource = dt;</div> <div> }<br> catch (Exception ex)<br> {<br> MessageBox.Show(ex.Message);<br> }<br> finally<br> {<br> ReleaseExcelObject(xlWorksheet);<br> ReleaseExcelObject(xlWorkbook);<br> ReleaseExcelObject(xlApp);<br> ReleaseExcelObject(xlWorksheet2);<br> }<br> }<br> }</div> <div> private void termKey_KeyDown(object sender, KeyEventArgs e)<br> {<br> if(e.KeyCode == Keys.Enter)<br> {<br> btn_Search_Click(sender, e);<br> }<br> }</div> <div> private void btn_col_Click(object sender, EventArgs e)<br> {<br> excelDataView.Columns.Add("Column", txtColName.Text);<br> }</div> <div> private void cmbSheetName_SelectedIndexChanged(object sender, EventArgs e)<br> {<br> lblSheetName.Text = cmbSheetName.Text;<br> int Sheet_Num = 0;</div> <div> Excel.Application xlApp = null;<br> Excel.Workbook xlWorkbook = null;<br> Excel.Worksheet xlWorksheet = null;<br> Excel.Worksheet xlWorksheet2 = null;</div> <div> try<br> {<br> excelDataView.Columns.Clear();</div> <div> DataTable dt = new DataTable();</div> <div> xlApp = new Excel.Application();<br> xlWorkbook = xlApp.Workbooks.Open(gFileName);</div> <div> Sheet_Num = Convert.ToInt16(cmbSheetName.Text.Split('-')[0]);<br> xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(Sheet_Num);</div> <div> Excel.Range range = xlWorksheet.UsedRange;</div> <div> object[,] data = range.Value;</div> <div> for (int i = 1; i <= range.Columns.Count; i++)<br> {<br> dt.Columns.Add(i.ToString(), typeof(string));<br> }</div> <div> for (int r = 1; r < range.Rows.Count; r++)<br> {<br> DataRow dr = dt.Rows.Add();</div> <div> for (int c = 1; c < range.Columns.Count; c++)<br> {<br> dr[c - 1] = data[r, c];<br> }<br> }</div> <div> xlWorkbook.Close(true);<br> xlApp.Quit();</div> <div> excelDataView.DataSource = dt;<br> }<br> catch (Exception ex)<br> {<br> MessageBox.Show(ex.Message);<br> }<br> finally<br> {<br> ReleaseExcelObject(xlWorksheet);<br> ReleaseExcelObject(xlWorkbook);<br> ReleaseExcelObject(xlApp);<br> ReleaseExcelObject(xlWorksheet2);<br> }<br> }</div> <div> private void ReleaseExcelObject(object obj)<br> {<br> try<br> {<br> if (obj != null)<br> {<br> Marshal.ReleaseComObject(obj);<br> obj = null;<br> }<br> }<br> catch (Exception ex)<br> {<br> obj = null;<br> throw ex;<br> }<br> finally<br> {<br> GC.Collect();<br> }<br> }</div> <div> private void btn_Save_Click(object sender, EventArgs e)<br> {<br> ExportExcel(true);<br> }</div> <div> public static String changeIndex(int index)<br> {<br> int quotient = (index) / 26;</div> <div> if (quotient > 0)<br> {<br> return changeIndex(quotient - 1) + (char)(index % 36 + 65);<br> }<br> else<br> {<br> return "" + (char)(index % 36 + 65);<br> }<br> }</div> <div> private void ExportExcel(bool caption)<br> {<br> this.saveFileDialog1.FileName = "TempName";<br> this.saveFileDialog1.DefaultExt = gfileExtension;<br> //this.saveFileDialog1.Filter = "Excel files (*." + gfileExtension + ")|*." + gfileExtension;<br> this.saveFileDialog1.Filter = "Excel files (*.xls)|*.xls";<br> this.saveFileDialog1.InitialDirectory = "c:\\";</div> <div> DialogResult result = saveFileDialog1.ShowDialog();</div> <div> if (result == DialogResult.OK)<br> {<br> object missingType = Type.Missing;</div> <div> Excel.Application objApp;<br> Excel._Workbook objBook;<br> Excel.Workbooks objBooks;<br> Excel.Sheets objSheets;<br> Excel._Worksheet objSheet;<br> Excel.Range range;</div> <div> string[] headers = new string[excelDataView.ColumnCount];<br> string[] columns = new string[excelDataView.ColumnCount];</div> <div> /*for (int c = 0; c < excelDataView.ColumnCount; c++)<br> {<br> headers[c] = excelDataView.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();<br> num += c + 65;<br> columns[c] = Convert.ToString((char)num);<br> }*/</div> <div> for (int c = 0; c < excelDataView.ColumnCount; c++)<br> {<br> headers[c] = excelDataView.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();</div> <div> int quotient = (c) / 26;<br> if (quotient > 0)<br> {<br> columns[c] = Convert.ToString(changeIndex(quotient - 1) + (char)((c % 26) + 65));<br> }<br> else<br> {<br> columns[c] = Convert.ToString((char)((c % 26) + 65));<br> }<br> }</div> <div> try<br> {<br> objApp = new Excel.Application();<br> objBooks = objApp.Workbooks;<br> objBook = objBooks.Add(Missing.Value);<br> objSheets = objBook.Worksheets;<br> objSheet = (Excel._Worksheet)objSheets.get_Item(1);</div> <div> if (caption)<br> {<br> for (int i = 0; i < excelDataView.ColumnCount; i ++)<br> {<br> range = objSheet.get_Range(columns[i] + "1", Missing.Value);<br> range.set_Value(Missing.Value, headers[i]);<br> }<br> }</div> <div> for (int i = 0; i < excelDataView.RowCount - 1; i++)<br> {<br> for (int j = 0; j < excelDataView.ColumnCount; j++)<br> {<br> range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),<br> Missing.Value);<br> range.set_Value(Missing.Value,<br> excelDataView.Rows[i].Cells[j].Value.ToString());<br> }<br> }</div> <div> objApp.Visible = false;<br> objApp.UserControl = false;</div> <div> objBook.SaveAs(@saveFileDialog1.FileName,<br> Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,<br> missingType, missingType, missingType, missingType,<br> Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,<br> missingType, missingType, missingType, missingType, missingType);<br> objBook.Close(false, missingType, missingType);</div> <div> Cursor.Current = Cursors.Default;</div> <div> MessageBox.Show("Save Success!!!");</div> <div> }<br> catch(Exception e)<br> {<br> String ErrMsg;<br> ErrMsg = "Error: ";<br> ErrMsg = String.Concat(ErrMsg, e.Message);<br> ErrMsg = String.Concat(ErrMsg, " Line: ");<br> ErrMsg = String.Concat(ErrMsg, e.Source);</div> <div> MessageBox.Show(ErrMsg, "Error");<br> }<br> }<br> }</div> <div> private void btn_Search_Click(object sender, EventArgs e)<br> {<br> DataTable dt = new DataTable();<br> BindingSource bs = new BindingSource();<br> string[] colName = new string[excelDataView.Columns.Count];<br> int count = 0;</div> <div> bs.DataSource = excelDataView.DataSource;<br> excelDataView.DataSource = bs;<br> <br> for (int i = 0; i < excelDataView.Columns.Count; i++)<br> {<br> colName[i] = excelDataView.Columns[i].HeaderText;<br> }</div> <div> for (int i = 0; i < excelDataView.Columns.Count; i++)<br> {<br> if (bs.Filter == null)<br> {<br> bs.Filter = string.Format("Convert(["+ colName[i].ToString().Trim() + "], 'System.String') LIKE '%" + txtSearch.Text + "%'");<br> }<br> else<br> {<br> bs.Filter += string.Format(" OR Convert(["+ colName[i].ToString().Trim() + "], 'System.String') LIKE '%" + txtSearch.Text + "%'");<br> }<br> <br> }<br> Search_Count = excelDataView.Rows.Count-1;<br> toolStripStatusLabel1.Text = "검색수 : "+ Search_Count.ToString();<br> }</div> <div> private void txtCell_Type(object sender, EventArgs e)<br> {<br> string[,] valueType = new string[6, 3];<br> string[,] valueGetType = new string[6, 3];</div> <div> if (typeof(Int32) == excelDataView.SelectedCells[0].Value.GetType())<br> {<br> MessageBox.Show("숫자");<br> }<br> else if (typeof(string) == excelDataView.SelectedCells[0].Value.GetType())<br> {<br> MessageBox.Show("문자");<br> }<br> else<br> {<br> MessageBox.Show(excelDataView.SelectedCells[0].Value.GetType().ToString());<br> }<br> }</div> <div> private void txtSearch_TextChanged(object sender, EventArgs e)<br> {<br> //cell의 데이터 값<br> //string test = "";<br> //test = excelDataView.Rows[1].Cells[2].Value.ToString();</div> <div> //검색한 셀 위치<br> /*string search_Value = txtSearch.Text;<br> int rowIndex = 0;<br> excelDataView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;</div> <div> try<br> {<br> if (excelDataView.Rows.Count > 0)<br> {<br> foreach (DataGridViewRow row in excelDataView.Rows)<br> {<br> if (row.Cells[rowIndex].Value.ToString().Equals(search_Value))<br> {<br> row.Selected = true;<br> rowIndex++;<br> break;<br> }<br> }<br> }<br> }<br> catch(Exception exe)<br> {<br> MessageBox.Show(exe.Message);<br> }*/</div> <div> DataTable dt = new DataTable();<br> BindingSource bs = new BindingSource();<br> string[] colName = new string[excelDataView.Columns.Count];</div> <div> bs.DataSource = excelDataView.DataSource;<br> excelDataView.DataSource = bs;</div> <div> for (int i = 0; i < excelDataView.Columns.Count; i++)<br> {<br> colName[i] = excelDataView.Columns[i].HeaderText;<br> }</div> <div> for (int i = 0; i < excelDataView.Columns.Count; i++)<br> {<br> if (bs.Filter == null)<br> {<br> bs.Filter = string.Format(colName[i].ToString() + " LIKE '%{0}%'", txtSearch.Text);<br> }<br> else<br> {<br> bs.Filter += string.Format(" OR " + colName[i].ToString() + " LIKE '%{0}%'", txtSearch.Text);<br> }</div> <div> }<br> }<br> }<br>}<br></div>
