Exporting data to excel file C#.NET

Question

I want to export the data from a access database to excel file. But i got Exception from HRESULT: 0x800A03EC error.

Here is a bit of code that i have written,

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

private void showBtn_Click(object sender, EventArgs e)
    {
        int cnt = -1;
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AyumiDB1.mdb");
        thisConnection.Open();
        OleDbCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.CommandText = "SELECT CodeNumber, Particular, LF, DebitCredit, Amount FROM JournalVoucher";
        OleDbDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
        {
            cnt++;
            MessageBox.Show(thisReader["CodeNumber"].GetType().ToString());
            xlWorkSheet.Cells[cnt, 1] = thisReader["CodeNumber"].ToString();
            xlWorkSheet.Cells[cnt, 2] = thisReader["Particular"].ToString();
            xlWorkSheet.Cells[cnt, 3] = thisReader["LF"].ToString();
            xlWorkSheet.Cells[cnt, 4] = thisReader["DebitCredit"].ToString();
            xlWorkSheet.Cells[cnt, 5] = thisReader["Amount"].ToString();
        }
        thisReader.Close();
        thisConnection.Close();
        xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

My problem is the

MessageBox.Show(thisReader["CodeNumber"].GetType().ToString());

runs perfectly, but when i tried to insert the same value to the cell of excel with

xlWorkSheet.Cells[cnt, 1] = thisReader["CodeNumber"].ToString();

then the exception is thrown.

Am i doing something wrong??

Thanks

Barry Kaye · Accepted Answer · 2012-03-29 12:56:15Z

1

xlWorkSheet.Cells[cnt, 1] = thisReader["CodeNumber"].ToString();

In this line avoid the exception you change

int cnt = 0;

edited Mar 29, 2012 at 12:56

Barry Kaye

7,7696 gold badges46 silver badges65 bronze badges

answered Mar 29, 2012 at 8:29

amit

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Ash · Accepted Answer · 2011-06-17 13:41:51Z

0

Instead of

xlWorkSheet.Cells[cnt, 1] = thisReader["CodeNumber"].ToString();

try this

((Excel.Range)xlWorkSheet.Cells[cnt, 1]).Value2= thisReader["CodeNumber"].ToString();

Might work

answered Jun 17, 2011 at 13:41

Ash

2,6012 gold badges30 silver badges39 bronze badges

Collectives™ on Stack Overflow

Exporting data to excel file C#.NET

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related