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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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;
}