将数据导出到 Excel 文件 C#.NET

Question

我想将access数据库中的数据导出到excel文件。 但我收到 HRESULT 异常：0x800A03EC 错误。

这是我编写的一些代码，

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

我的问题是

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

运行完美，但是当我尝试将相同的值插入 excel 单元格

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

时，会引发异常。

我做错了什么吗？

谢谢

Answer 1

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

在这一行中避免您更改的异常

int cnt = 0;

Answer 2

而不是

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

尝试这个

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

可能会起作用