Export data in microsoft excel using C# windowsforms application

Question

Hi,

i got the exception when i am trying to export data to excel.... the exception is

COMException: Exception from HRESULT: 0x800A03EC.

How shall i solve this error? How shall i get solution? Tell me the solution of this problem...

Thanks in advance...

My Code is:

         {
        oxl = new Excel.Application();
        oxl.Visible = true;
        oxl.DisplayAlerts = false;


        wbook = oxl.Workbooks.Add(Missing.Value);

        wsheet = (Excel.Worksheet)wbook.ActiveSheet;
        wsheet.Name = "Customers"; 



        DataTable dt = InstituteTypeDetail();

        int rowCount = 1;
        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                // Add the header the first time through
                if (rowCount == 2)
                {
                    wsheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                }
                wsheet.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }

        range = wsheet.get_Range(wsheet.Cells[1, 1],
                      wsheet.Cells[rowCount, dt.Columns.Count]);//In this place i got the exception
        range.EntireColumn.AutoFit();


        wsheet = null;
        range = null;

}

what i did wrong? what is the way to solve this exception.... Anyone plz tell me...

Answer 1

Is your rowCount actually correct at the point you get the error? You're starting it from 1, but then incrementing it immediately - so when you're examining row 1 in your foreach loop, rowCount is actually 2.

I think your rowCount should be initialised to 0 (zero.) Then your header row check should look for if (rowCount == 1).

Answer 2

get_Range expects the name of a cell as string, i.e. something like "A1", "B25" etc. You could try to replace the line with the following code:

range = wsheet.Cells(1, 1);
range = range.Resize(rowCount, dt.Columns.Count);

Answer 3

The following sample code works fine for me. Can you give it a try. Have modified your sample. Also it is always a good practice to release references to all com objects at the end.

Application oxl = null;
        try
        {
            oxl = new Application( );
            oxl.Visible = true;
            oxl.DisplayAlerts = false;

            string fileName = @"D:\one.xls";
            object missing = Missing.Value;
            Workbook wbook = oxl.Workbooks.Open( fileName, missing, missing, missing, missing, missing,missing,missing,missing,missing,missing,missing,missing,missing,missing );

            Worksheet wsheet = ( Worksheet )wbook.ActiveSheet;
            wsheet.Name = "Customers";


            System.Data.DataTable dt = new System.Data.DataTable( "test" );
            dt.Columns.Add( "col1" );
            dt.Columns.Add( "col2" );
            dt.Columns.Add( "col3" );

            dt.Rows.Add( new object[ ] { "one", "one", "one" } );
            dt.Rows.Add( new object[ ] { "two", "two", "two" } );
            dt.Rows.Add( new object[ ] { "three", "three", "three" } );


            for ( int i = 1 ; i <=  dt.Columns.Count ; i++ )
            {
                wsheet.Cells[ 1, i ] = dt.Columns[ i - 1 ].ColumnName;
            }

            for ( int j = 1 ; j <= dt.Rows.Count ; j++ )
            {
                for ( int k = 0 ; k <  dt.Columns.Count ; k++ )
                {
                    DataRow dr = dt.Rows[ k ];
                    wsheet.Cells[ j +1, k+1 ] = dr[ k ].ToString( );
                }
            }

            Range range = wsheet.get_Range( wsheet.Cells[ 1, 1 ],
                          wsheet.Cells[ dt.Rows.Count + 1, dt.Columns.Count ] );//In this place i got the exception
            range.EntireColumn.AutoFit( );

            wbook.Save( );

            wsheet = null;
            range = null;


        }
        finally
        {
            oxl.Quit( );
            System.Runtime.InteropServices.Marshal.ReleaseComObject( oxl );
            oxl = null;
        }

Answer 4

SpreadsheetGear for .NET will let you work with Excel workbooks from .NET without the problems associated with Excel COM Interop. SpreadsheetGear is also faster than COM Interop - especially when looping through a number of cells as your code seems to do.

Here is some code which does the same thing as your code using the SpreadsheetGear API:

using System;
using SpreadsheetGear;

namespace Program
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = @"D:\one.xls";
            IWorkbook wbook = SpreadsheetGear.Factory.GetWorkbook(fileName);
            IWorksheet wsheet = wbook.ActiveWorksheet;
            wsheet.Name = "Customers";
            System.Data.DataTable dt = new System.Data.DataTable("test");
            dt.Columns.Add("col1");
            dt.Columns.Add("col2");
            dt.Columns.Add("col3");
            dt.Rows.Add(new object[] { "one", "one", "one" });
            dt.Rows.Add(new object[] { "two", "two", "two" });
            dt.Rows.Add(new object[] { "three", "three", "three" });
            wsheet.Cells[0, 0, dt.Rows.Count - 1, dt.Columns.Count - 1].CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);
            wsheet.UsedRange.EntireColumn.AutoFit();
            wbook.Save();
        }
    }
}

You can see live samples here and download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC