C# dataGridView OleDB 를 이용하여 엑셀로 내보내기

C# dataGridView 에서 OleDB 를 이용하여 엑셀로 내보내기 구현하면서 엄청 생고생을 많이 했다. 처음부터 그냥 단순하게 시작해서 잘 동작되는지 확인하고 했어야 하는데 검색해서 찾아낸 소스가 좋아보여서 그걸 분석하면서 해본다고 하다가 시간만 엄청 소모했다.

엑셀 내보내기, 가져오기 괜찮은 블로그 : http://blog.naver.com/exila 이 사이트는 DataTable 과 DataSet 을 활용하고 OleDB 를 이용하여 엑셀 파일로 저장하는 로직이다.

내가 아직 DataSet 을 이용하는 걸 완벽하게 이해를 못해서 활용하기에는 부담스러웠다.

그리고 난 그냥 dataGridView 화면 출력상에서 보이는 걸 가지고 내보내기를 해보고 싶었다.

이 코드는 완벽하게 동작한다고 할 수가 없다. 기능 동작이 안되는게 아니라 파일 덮어쓰기를 하면 이미 Sheet1 테이블이 존재한다고 하면서 에러가 난다. 이런 부분 등을 고려하여 더 보완을 해야 한다.

엑셀 2003 이하버전으로 저장을 하면 문제없이 저장이 되는데 엑셀 2007 이상으로 저장하면 파일 인식을 잘 못한다. 이 부분도 개선을 해야 한다.
'Excel 12.0;HDR=No' 은 따옴표가 들어가야 문제가 발생하지 않는다고 언급된 것이 있어서 찾아서 적어줬다.

일일이 값을 찍어주는 걸 하면서 확인 작업을 했다.

string strquery = string.Format("INSERT INTO [Sheet1$] values({0},{1},'{2}',{3},{4})", dgv.Rows[r].Cells[0].Value, dgv.Rows[r].Cells[1].Value, dgv.Rows[r].Cells[2].Value, dgv.Rows[r].Cells[3].Value, dgv.Rows[r].Cells[4].Value);

좀더 테스트를 해보니 이 부분을 넣어줘야만 정상적으로 동작한다.

Sheet 명이 존재하는지 검사해서 처리를 해봐도 안된다. 결국 이 로직을 추가하고 나서야 정상 실행된다.

if (File.Exists(fileName))
{
    File.Delete(fileName);  // 기존 파일에 기록을 하려면 이렇게 삭제하고 다시 생성해야 정상 동작됨
}

이제 남은 것은 엑셀 2007 이상으로 저장할 때 정상 저장이 되도록 하는 부분을 검토해야 한다.

// ---------------------------------- 코드 --------------------------//

private void btnExcelSave_Click(object sender, EventArgs e)
{
    try
    {
        using (SaveFileDialog dlg = new SaveFileDialog())
        {
            dlg.Filter = "Excel Files(97~2003)|*.xls|Excel Files(2007이상)|*.xlsx";
            dlg.InitialDirectory = @"C:\test\";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                dataGridView_ExportToExcelSave(dlg.FileName, dataGridView2);
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}


private void dataGridView_ExportToExcelSave(string fileName, DataGridView dgv)
{

    if (File.Exists(fileName))
    {
        File.Delete(fileName);  // 기존 파일에 기록을 하려면 이렇게 삭제하고 다시 생성해야 정상 동작됨
    }

    string connectionString = string.Empty;
    try
    {
        if (Path.GetExtension(fileName).ToLower() == ".xls")
        {
            connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Mode=ReadWrite;Extended Properties='Excel 8.0;HDR=No';", fileName);
        }
        else if (Path.GetExtension(fileName).ToLower() == ".xlsx")
        {
            connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Mode=ReadWrite;Extended Properties='Excel 12.0;HDR=No';", fileName);
        }

        OleDbConnection oleDBConn = new OleDbConnection(connectionString);
        oleDBConn.Open();

        // Header 라인 생성
        StringBuilder Header_Column = new StringBuilder();
        for (int i = 0; i < dgv.Columns.Count; i++)
        {
            if (Header_Column.Length > 0) Header_Column.Append(",");
            Header_Column.Append("[" + dgv.Columns[i].HeaderText + "] CHAR(255)");
        }
        OleDbCommand Cmd = new OleDbCommand("CREATE TABLE [Sheet1] (" + Header_Column.ToString() + ")", oleDBConn);
        Cmd.ExecuteNonQuery();

        // 본문 내용 출력
        for (int r = 0; r < dgv.Rows.Count; r++)
        {
            StringBuilder Values = new StringBuilder();
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                if (Values.Length > 0) Values.Append(",");
                Values.Append("'" + dgv.Rows[r].Cells[i].Value.ToString() + "'");
            }
            string strquery = "INSERT INTO [Sheet1$] values(" + Values.ToString() + ")";        
            Cmd = new OleDbCommand(strquery, oleDBConn);
            Cmd.ExecuteNonQuery();
        }
        if (oleDBConn.State == ConnectionState.Open)
        {
            oleDBConn.Close();  // 연결 종료
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }           
}