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