2011년 7월 14일 목요일

DataGrideView를 Excel로 저장



using System;
using System.Data;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace RedQueen
{
    public class Export2Excel
    {
        public Export2Excel()
        {

        }

        private string get_string(object o)
        {
            try
            {
                if (o == null) return "";
                return o.ToString();
            }
            catch
            {
                return "";
            }
        }

        private string get_excel_column_char(int no)
        {
            no++;
            if (no > 26)
            {
                return ((char)(Math.Floor(((double)no - 1) / 26) + 64)).ToString()
                       + ((char)(((no - 1) % 26) + 65)).ToString();
            }
            return ((char)(no + 64)).ToString();
        }

        public void ToExcel(DataGridView datagridview)
        {
            object missingType = Type.Missing;

            Excel.Application objExcelApp = null;
            Excel._Workbook objWorkBook = null;
            Excel.Workbooks objBooks = null;
            Excel.Sheets objWorkSheets = null;
            Excel._Worksheet objSheet = null;
            Excel.Range range = null;

            string[] headers = new string[datagridview.ColumnCount];
            string[] columns = new string[datagridview.ColumnCount];

            for (int c = 0; c < datagridview.ColumnCount; c++)
            {
                headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                columns[c] = get_excel_column_char(c);
            }

            int i = 0;
            int j = 0;
            try
            {
                objExcelApp = new Excel.Application();
                objExcelApp.Visible = false;

                objBooks = objExcelApp.Workbooks;
                objWorkBook = objBooks.Add(Missing.Value);
                objWorkSheets = objWorkBook.Worksheets;
                objSheet = (Excel._Worksheet)objWorkSheets.get_Item(1);


                for (int c = 0; c < datagridview.ColumnCount; c++)
                {
                    range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                    range.set_Value(Missing.Value, get_string(headers[c]));
                }

                string tmp = "";

                for (i = 0; i < datagridview.RowCount ; i++)
                {
                    for (j = 0; j < datagridview.ColumnCount; j++)
                    {
                        tmp = datagridview.Rows[i].Cells[j].ValueType.ToString();
                        range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2), Missing.Value);

                        if (tmp == "String")
                        {
                            range.set_Value(Missing.Value, "'" + get_string(datagridview.Rows[i].Cells[j].Value));
                        }
                        else
                        {
                            range.set_Value(Missing.Value, get_string(datagridview.Rows[i].Cells[j].Value));
                        }                        
                        
                    }
                }

                Cursor.Current = Cursors.Default;
                objExcelApp.Visible = true;
                objExcelApp.UserControl = true;
                MessageBox.Show("완료");
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                errorMessage = String.Concat(errorMessage, " Row: " + i.ToString());
                errorMessage = String.Concat(errorMessage, " Column: " + j.ToString());

                MessageBox.Show(errorMessage, "Error");
            }

        }
    }
}





댓글 없음:

댓글 쓰기