WPF Excel Export Microsoft.Office.Interop 성능향상(열 기준으로 복사)

Microsoft.Office.Interop을 사용하여 셀별로 복사하면 너무오래걸려 Row 일괄 복사하도록하여 성능향상

 

DataGrid -> DataView -> DataTable 로 변환하고 foreach 에서 DataRow 별로 일괄 붙이기.

DataView Dview = (DataView)result_grid2.ItemsSource as DataView;
DataTable Ex_table = Dview.Table;

string excelRange1 = "A" + cellRowIndex;// 복사 시작범위
string excelRange2 = "BL" + cellRowIndex;// 복사 종료범위
worksheet.Range[excelRange1, excelRange2].Value2 = dr.ItemArray; // DataRow 일괄 붙이기.

-----------------------------------------------

 

private void Excel_Export()
{
DataView Dview = (DataView)result_grid2.ItemsSource as DataView;
DataTable Ex_table = Dview.Table;

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

try
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;//workbook.ActiveSheet; // workbook.ActiveSheet; //(Excel.Range)

worksheet.Name = "Data";

int cellRowIndex = 2; // 일괄 붙이기 시작열

 

// ProgressBar 시작

float values = 0.0f;

pbStatus.Value = 10;
int value_count = result_grid2.Items.Count;

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

// header
for (int i = 0; i < Ex_table.Columns.Count; i++)
{

worksheet.Cells[1, 1 + i] = result_grid2.Columns[i].Header;

} // for

foreach (DataRow dr in Ex_table.Rows)
{

if (cellRowIndex == 2)
{
values = ((1.0f) / (float)value_count) * 100.0f;
}
else
{
values = Convert.ToInt16((cellRowIndex / (float)value_count) * 100.0f);
}

if ( cellRowIndex == Ex_table.Rows.Count+1)
{
values = 100.0f;
}

Application.Current.Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.ApplicationIdle, (Action)(() =>
{
this.pbStatus.Value = (int)values;
sbStrip1.Content = values.ToString() + "%";

if (values == 100.0f)
{
sbStrip1.Content = "저장준비중입니다. 잠시만 더 기다려 주세요";
}
}));

// ProgressBar 종료

string excelRange1 = "A" + cellRowIndex;// String.Format("A3:{0}{1}", "C", "3");
string excelRange2 = "BL" + cellRowIndex;// String.Format("A3:{0}{1}", "C", "3");
worksheet.Range[excelRange1, excelRange2].Value2 = dr.ItemArray;

cellRowIndex++;

} // foreach

}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog();

saveDialog.Filter = "Excel 2007 files (*.xls)|*.xls| Excel 2010 files (*.xlsx)|*.xlsx"; //All files (*.*)|*.*";
saveDialog.FilterIndex = 2; // FilterIndex = 2;

if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
sbStrip1.Content = "저장완료";

MessageBox.Show("Export Successful");
}
excel.Quit();
workbook = null;
excel = null;
}

}