첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170
/// <summary>
/// EXCEL 관리자
/// </summary>
public class EXCELManager
{
    //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
    ////////////////////////////////////////////////////////////////////////////////////////// Public

    #region 생성자 - EXCELManager()

    /// <summary>
    /// 생성자
    /// </summary>
    public EXCELManager()
    {
    }

    #endregion

    //////////////////////////////////////////////////////////////////////////////////////////////////// Method
    ////////////////////////////////////////////////////////////////////////////////////////// Public

    #region 내보내기 - Export(dataGridView)

    /// <summary>
    /// 내보내기
    /// </summary>
    /// <param name="dataGridView">DataGridView</param>
    public void Export(DataGridView dataGridView)
    {
        object                                     missingType      = Type.Missing;
        Microsoft.Office.Interop.Excel.Application excelApplication = null;
        Microsoft.Office.Interop.Excel._Workbook   excelWorkbook    = null;
        Microsoft.Office.Interop.Excel.Workbooks   excelWorkbooks   = null;
        Microsoft.Office.Interop.Excel.Sheets      excelWorksheets  = null;
        Microsoft.Office.Interop.Excel._Worksheet  excelWorksheet   = null;
        Microsoft.Office.Interop.Excel.Range       excelRange       = null;

        string[] headerArray = new string[dataGridView.ColumnCount];
        string[] columnArray = new string[dataGridView.ColumnCount];

        for(int i = 0; i < dataGridView.ColumnCount; i++)
        {
            headerArray[i] = dataGridView.Rows[0].Cells[i].OwningColumn.HeaderText.ToString();
            columnArray[i] = GetColumnName(i);
        }

        int y = 0;
        int x = 0;

        try
        {
            excelApplication = new Microsoft.Office.Interop.Excel.Application();

            excelApplication.Visible = false;

            excelWorkbooks = excelApplication.Workbooks;

            excelWorkbook = excelWorkbooks.Add(Missing.Value);

            excelWorksheets = excelWorkbook.Worksheets;

            excelWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)excelWorksheets.get_Item(1);

            for(int i = 0; i < dataGridView.ColumnCount; i++)
            {
                excelRange = excelWorksheet.get_Range(columnArray[i] + "1", Missing.Value);

                excelRange.set_Value(Missing.Value, GetString(headerArray[i]));
            }

            string valueType = string.Empty;

            for(y = 0; y < dataGridView.RowCount ; y++)
            {
                for(x = 0; x < dataGridView.ColumnCount; x++)
                {
                    valueType = dataGridView.Rows[y].Cells[x].ValueType.ToString();

                    excelRange = excelWorksheet.get_Range(columnArray[x] + Convert.ToString(y + 2), Missing.Value);

                    if(valueType == "String")
                    {
                        excelRange.set_Value(Missing.Value, "'" + GetString(dataGridView.Rows[y].Cells[x].Value));
                    }
                    else
                    {
                        excelRange.set_Value(Missing.Value, GetString(dataGridView.Rows[y].Cells[x].Value));
                    }
                }
            }

            Cursor.Current = Cursors.Default;

            excelApplication.Visible = true;

            excelApplication.UserControl = true;

            MessageBox.Show("완료");
        }
        catch(Exception exception)
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendFormat("메시지 : {0}", exception.Message);
            stringBuilder.AppendFormat("소스   : {0}", exception.Source);
            stringBuilder.AppendFormat("행     : {0}", y);
            stringBuilder.AppendFormat("열     : {0}", x);

            MessageBox.Show(stringBuilder.ToString(), "에러");
        }
    }

    #endregion

    ////////////////////////////////////////////////////////////////////////////////////////// Private

    #region 문자열 구하기 - GetString(sourceObject)

    /// <summary>
    /// 문자열 구하기
    /// </summary>
    /// <param name="sourceObject">소스 객체</param>
    /// <returns>문자열</returns>
    private string GetString(object sourceObject)
    {
        try
        {
            if(sourceObject == null)
            {
                return string.Empty;
            }

            return sourceObject.ToString();
        }
        catch
        {
            return string.Empty;
        }
    }

    #endregion

    #region 컬럼명 구하기 - GetColumnName(column)

    /// <summary>
    /// 컬럼명 구하기
    /// </summary>
    /// <param name="column">컬럼</param>
    /// <returns>컬럼명</returns>
    private string GetColumnName(int column)
    {
        column++;

        if(column > 26)
        {
            return ((char)(Math.Floor(((double)column - 1) / 26) + 64)).ToString() + ((char)(((column - 1) % 26) + 65)).ToString();
        }

        return ((char)(column + 64)).ToString();
    }

    #endregion
}

※ Microsoft Excel 14.0 Object Library를 참조해야 한다.

728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요