Converting DataGridView to Excel throwing exception in windows forms application

Question

0.00/5 (No votes)

See more:

Hi,

I am trying to export the gridview to excel using Microsoft interop dll. Below is the code that I got from some source. This code is creating excel file in my documents folder with a default name. But when I try to click the convert to excel button again, then it is showing a default prompt whether to overwrite the existing or not. If I say No, then it's throwing the below exception. Moreover, it's not prompting me to mention my own file name and select the path to save.
Please help me...
This is the exception I am getting. The saveas method throwing this exception.

Exception from HRESULT: 0x800A03EC
System.Runtime.InteropServices.COMException was unhandled
  Message=Exception from HRESULT: 0x800A03EC
  Source=AssetManagementSystem
  ErrorCode=-2146827284

Here is the code:

C#

private void btnExportExcel_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            
            object misValue = System.Reflection.Missing.Value;
            
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            String st = System.IO.Directory.GetCurrentDirectory() + "\\A.xlsx";
            // Change properties of the Workbook 
            ExcelApp.Columns.ColumnWidth = 20;

            // Storing header part in Excel
            for (int i = 1; i < gvContractDetails.Columns.Count + 1; i++)
            {
                ExcelApp.Cells[1, i] = gvContractDetails.Columns[i - 1].HeaderText;
            }

            // Storing Each row and column value to excel sheet
            for (int i = 0; i < gvContractDetails.Rows.Count - 1; i++)
            {
                for (int j = 0; j < gvContractDetails.Columns.Count; j++)
                {
                    ExcelApp.Cells[i + 2, j + 1] = gvContractDetails.Rows[i].Cells[j].Value.ToString();
                }
            }
            ExcelApp.Visible = true;
            ExcelApp.ActiveWorkbook.SaveAs(Type.Missing,Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        }

Posted 6-Jul-13 6:20am

Mr Apps

Updated 7-Jul-13 2:30am

v5

Add a Solution

Comments

Sergey Alexandrovich Kryukov 6-Jul-13 15:42pm

In what line of code?

I added "pre" tags to format code samples. You should always do it in your post; if you want to see how to properly format it, hit "Improve question" and take a look.

—SA

[no name] 6-Jul-13 15:44pm

Check and make sure that you are passing the correct parameters to the SaveAs function.

2 solutions

Solution 1

I see that you created a file name

C#

String st = System.IO.Directory.GetCurrentDirectory() + "\\A.xlsx";

However I couldn't find where have you used this variable?

Please make sure you are passing correct arguments while saving your excel.

Posted 7-Jul-13 4:03am

Ankush Bansal

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mr Apps · Accepted Answer · 2013-07-07T08:44:00

I got the solution now. Please let me know if there are any issues with this code:

public static void ExportGridViewToExcel(DataGridView gridview)
        {
            try
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                //Microsoft.Office.Interop.Excel.Application excel = null;
                Microsoft.Office.Interop.Excel.Workbook wb = null;
                Microsoft.Office.Interop.Excel._Worksheet wSheet = null;
                object misValue = System.Reflection.Missing.Value;
                
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                wb = ExcelApp.Workbooks.Add(Missing.Value);
                wSheet = wb.Sheets.Add(Type.Missing);
                //wSheet = wb.Sheets["sheet1"];
                ExcelApp.Columns.ColumnWidth = 20;
                ExcelApp.Visible = false;
                // Storing header part in Excel
                for (int i = 1; i < gridview.Columns.Count + 1; i++)
                {
                    wSheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
                }
                // Storing Each row and column value to excel sheet
                for (int i = 0; i < gridview.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < gridview.Columns.Count; j++)
                    {
                        wSheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Value.ToString();
                    }
                }
                ExcelApp.Visible = true;
                string fileName = SaveAs();
                wSheet.SaveAs(fileName);
                wb.Saved = true;
                wb.Close();
                ExcelApp.Quit();
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                ExcelDocViewer(fileName);
            }
            catch (Exception ex)
            {
            }
        }

public static void ExcelDocViewer(string fileName)
{
    try
    {
        System.Diagnostics.Process.Start(fileName);
    }
    catch { }
}

public static string SaveAs()
{
    string FileName = null;
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.InitialDirectory = Environment.GetFolderPath
    (Environment.SpecialFolder.Personal);
    saveFileDialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        FileName = saveFileDialog.FileName;
    }
    return FileName;
}

Converting DataGridView to Excel throwing exception in windows forms application

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Converting DataGridView to Excel throwing exception in windows forms application

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us