C# MySQL 엑셀로 내보내기

http://link2me.tistory.com/822  에 설명된 자료를 보면 되는데 이 코드는 MySQL DB 에 있는 데이터를 내보내기 하는 코드이다.

속도가 느려서 추천하고 싶은 코드는 아니지만 필요할 때가 있을 때 인터넷 검색하고 코드가 맞는지 테스트하고 싶지 않아서 적어둔다.

using Excel = Microsoft.Office.Interop.Excel;

private void ExportToExcelFromMySQL(string fileName, DataGridView dgv, bool elapsedtime)
{
    Excel.Application excelApp = new Excel.Application();
    if (excelApp == null)
    {
        MessageBox.Show("엑셀이 설치되지 않았습니다");
        return;
    }
    Excel.Workbook wb = excelApp.Workbooks.Add(true);
    Excel._Worksheet workSheet = wb.Worksheets.get_Item(1) as Excel._Worksheet;

    try
    {
        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();  //동작시간 체크
        if (elapsedtime == true)
        {
            sw.Start();
        }

        using (var myConn = new MySqlConnection(GetConnectionString()))
        {
            myConn.Open();
            string strqry = "SELECT uid,eng,direct,is_direct,kor,(select classname from category where uid = data.category1) as cate1,(select classname from category where uid = data.category2) as cate2 ";
            strqry += "FROM data ";
            strqry += "where hidden=1 ";
            strqry += "ORDER BY uid DESC";
            using (var cmd = new MySqlCommand(strqry, myConn))
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        int r = 0;
                        while (reader.Read())
                        {
                             파일로 저장할 정보 기록
                            csvExport.Write(reader.GetString(0)); csvExport.Write(delimiter);
                            workSheet.Cells[r + 2, 1] = reader.GetString(0);
                            workSheet.Cells[r + 2, 2] = reader.GetString(1);
                            workSheet.Cells[r + 2, 3] = reader.GetString(2);
                            workSheet.Cells[r + 2, 4] = reader.GetString(3);
                            workSheet.Cells[r + 2, 5] = reader.GetString(4);
                            workSheet.Cells[r + 2, 6] = reader.GetString(5);
                            workSheet.Cells[r + 2, 7] = reader.GetString(6);
                            r++;
                        }
                    }
                    else
                    {
                        MessageBox.Show("데이터가 없습니다");
                    }
                }
            }
            myConn.Close();
        }

         // 엑셀 2003 으로만 저장이 됨
        wb.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        wb.Close(Type.Missing, Type.Missing, Type.Missing);
        excelApp.Quit();
        releaseObject(excelApp);
        releaseObject(workSheet);
        releaseObject(wb);

        if (elapsedtime == true)
        {
            sw.Stop();
            MessageBox.Show("소요시간 : " + sw.Elapsed.ToString() + " 이고 엑셀파일 저장완료");
        }
        else
        {
            MessageBox.Show("CSV 파일이 저장되었습니다");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        throw;   비정상적인 상황(예외)이 발생한 경우 이를 알리는 데 사용
    }
}

#region 메모리해제
private static void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        throw ex;
    }
    finally
    {
        GC.Collect();
    }
}

#endregion