C# 엑셀 핸들링 (import 후 DataGridView에 출력,export)

C#에서 엑셀 임포트 후 datagridview에 출력하는 방법, 엑셀 export 방법.

 

1) 참조 추가 : COM > Microsoft Excel 16.0 Object Library, Microsoft Office 16.0 Object Library 추가

 

2) using 문 추가

using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;

3-1) 임포트 기능

    public partial class UserControl1 : UserControl
    {
        DataSet ds;
        BindingList<ViewModel> viewModel;
        
        public UserControl1()
        {
            InitializeComponent();
        }
        
        #region 엑셀 Import
        private void ImportBtn_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() == false)
            {
                MessageBox.Show("엑셀 Import가 취소되었습니다.");
                return;
            }
            string fileName = dialog.FileName;
            /*--------------------------------------------------------------*/
            ds = ExcelImport.Parse(fileName);
            if (ds == null) return;
            dataGrid.ItemsSource = ds.Tables[0].DefaultView;

            int rowIndex = 0;

            try
            {
                MessageBox.Show("조회시작"); //messagebox가 없으면 조회가 안 된다;
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow r in ds.Tables[0].Rows)
                    {
                        //bindingList 사용 시
                        var data = new ViewModel 
                        {
                            field1 = Convert.ToInt32(r["field1"].ToString()), //int인 경우
                            field2 = r["field2"].ToString(),
                            field3 = r["field3"].ToString(),
                            field4 = r["field4"].ToString(),
                            field5 = r["field5"].ToString(),
                        };
                        viewModel.Add(data);

                        tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[0], data.Pad_empno);
                        tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[1], data.Vbas_name);
                        tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[2], data.Pad_amt);
                        tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[3], data.Pad_sdate);
                        tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[4], data.Pad_edate);
                        
                        rowIndex++;
                        
                        /*
                        //array 사용 시
                        string[] data = new string [5]
                        {
                            r["a"].ToString(),
                            r["b"].ToString(),
                            r["c"].ToString(),
                            r["d"].ToString(),
                            r["e"].ToString(),
                        };
                        if (dataGrid.SelectedItem != null)
                        {
                            for (int i = 0; i <= dataGrid.Columns.Count - 1; i++)
                            {
                                tableView.Grid.SetCellValue(rowIndex, dataGrid.Columns[i], data[i]);
                            }
                            rowIndex++;
                        }
                        */
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
            /*--------------------------------------------------------------*/
            MessageBox.Show("자료가 정상적으로 임포트 되었습니다.");
        }
        #endregion
}

3-2) 익스포트 기능

private void ExcelCreate_Click(object sender, RoutedEventArgs e)
{
	Excel.Application application;
    Excel.Workbook workbook;
    Excel.Worksheet worksheet;
    SaveFileDialog saveFileDialog1 = new SaveFileDialog();

	//바탕화면에 hello.xlsx로 저장됨.
    saveFileDialog1.Filter = "엑셀파일|*.xlsx";
    saveFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
    string savefilename = saveFileDialog1.InitialDirectory + "\\hello"; 

    application = new Excel.Application();
    workbook = application.Workbooks.Add(true);
    worksheet = (Excel.Worksheet)workbook.Sheets[1]; // Excel Sheet 배열은 1부터 시작한다.
    string[,] sheetData = new string[1, 5];

    try
    {
    	sheetData[0, 0] = "employee number";
    	sheetData[0, 1] = "name";
    	sheetData[0, 2] = "price";
    	sheetData[0, 3] = "start date";
    	sheetData[0, 4] = "end date";

    	worksheet.get_Range("A1:E1").Value2 = sheetData;
    	/*
        // 동일한 파일이 있는 경우
    	if (System.IO.File.Exists((string)savefilename)) 
        	{
             int i = 1;
             while (!System.IO.File.Exists((string)savefilename)) {
             	workbook.SaveAs(savefilename + "("+i+")");
                i++;
             }     
        }
        else
        { 
        	workbook.SaveAs(savefilename);
        }
        */
        workbook.SaveAs(savefilename);
     }
     catch (Exception ex)
     {
     	MessageBox.Show(ex.Message);
        return;
      }
      finally
      {
      	// Excel Application 종료
        workbook.Close(false, Type.Missing, Type.Missing);
        application.Quit();
        MessageBox.Show("바탕화면에 저장 완료.");
       }
}
#endregion

 

어렵다TT