태그 보관물: C# excel

C# DataGridView To Excel

윈도우 폼에서 Excel로 데이터 받아 내기 위한 소스 입니다.

Vs 2008 SP1 ,Net 3.5 p1 , Ms Office 2010  , Windows 7 Pro x86 환경 입니다

1. 솔루션탐색기->참조추가->COM->Microsoft Excel 14.0 Object Libary 선택 하여 참조 추가 K-20131204-585051

폼에서의 Data 확인 이름 : dataGridView1

K-20131204-597648

폼에서 button1 을 누르면 해당 로직을 수행하게

 
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
 
 
private void Button1_Click(object sender, EventArgs e)
        {
 
            this.saveFileDialog2.FileName = "저장할파일이름";
            this.saveFileDialog2.DefaultExt = "xls";
            this.saveFileDialog2.Filter = "Excel files (*.xls)|*.xls";
            this.saveFileDialog2.InitialDirectory = "c:\\";
 
            DialogResult result = saveFileDialog2.ShowDialog();
 
            if (result == DialogResult.OK)
             {
                int num = 0;
                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[dataGridView1.ColumnCount];   
                string[] columns = new string[dataGridView1.ColumnCount];   
 
                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    headers[c]=dataGridView1.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                    num = c + 65;
                    columns[c] = Convert.ToString((char)num);
                }
 
                try
                {
                    objApp = new Excel.Application();
                    objBooks = objApp.Workbooks;
                    objBook = objBooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
                    objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 
                        for (int c = 0; c < dataGridView1.ColumnCount; c++)
                        {
                            range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                            range.set_Value(Missing.Value, headers[c]);
                        }
 
 
                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.ColumnCount; j++)
                        {
                            range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),
                                                                    Missing.Value);
                            range.set_Value(Missing.Value,
                                                   dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }
                    }
 
                    objApp.Visible = false;
                    objApp.UserControl = false;
 
                    objBook.SaveAs(@saveFileDialog2.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 theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);
 
                    MessageBox.Show(errorMessage, "Error");
                }
            }
        }