Reading Excel and get error

Question

1.00/5 (1 vote)

See more:

I wrote a short code to read an excel file. Here is my code.

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel_ref = Microsoft.Office.Interop.Excel;

namespace Excel_Sheet
{
    public partial class Excel : Form
    {
        public Excel()
        {
            InitializeComponent();
        }


        Excel_ref.Application _Excel_App = new Excel_ref.Application();

        public static Excel_ref.Workbook _curr_WorkBook;
        public static Excel_ref.Worksheet _curr_WorkSheet;
        public static Excel_ref.Range _curr_range;
        string _chosen_file = "";

        private void btnGetExcel_Click(object sender, EventArgs e)
        {

            openFileDialog1.Filter = "Excel Files (*.xls)|*.xls|Excel 2007(*xlsx)|*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                _chosen_file = openFileDialog1.FileName;
            }
            if (_chosen_file == string.Empty)
            {
                return;
            }
            txtWorkBook.Text = _chosen_file;
            getExcelData(txtWorkBook.Text);
        }

        private void getExcelData(string get_File)
        {
            FileStream F_stream = File.Open(get_File, FileMode.Open, FileAccess.Read);

            _Excel_App = new Excel_ref.Application();
            _curr_WorkBook = _Excel_App.Workbooks.Open(openFileDialog1.FileName, 
                0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                "\t", false, false, 0, true, 1, 0);
            //_curr_WorkBook = _Excel_App.Workbooks.Open(openFileDialog1.FileName,
            //               0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            //               "\t", false, false, 0, true, 1, 0);
            _curr_WorkSheet = (Excel_ref.Worksheet)_curr_WorkBook.Worksheets.get_Item(1);
            _curr_range = _curr_WorkSheet.UsedRange;

            string str;
            int rCnt = 0, cCnt = 0;
            for (rCnt = 1; rCnt <= _curr_range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= _curr_range.Columns.Count; cCnt++)
                {
                    str = (string)(_curr_range.Cells[rCnt, cCnt] as Excel_ref.Range).Value2;
                    MessageBox.Show(str);
                }
            }
            MessageBox.Show("Read all cell!");
            //Excel_ref.Sheets sheets = _curr_WorkBook.Worksheets;

            //if (get_File.EndsWith(".xlsx"))
            //{

            //}
            
        }
    }
}

When I ran these code, I got this error.

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Excel_Sheet.exe

Additional information: Exception from HRESULT: 0x800A03EC

Please help me to fix this error.

thanks,

Posted 6-Oct-11 6:38am

chaupha

Updated 6-Oct-11 6:48am

Alan N

v2

Add a Solution

2 solutions

Solution 2

You can use follwing piece of code for reading Excel file directly to Dataset.
Use ExcelDataReader Library (link provided).

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

// Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

// DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();

// Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Here is the reference for ExcelDataReader.
http://exceldatareader.codeplex.com/

Posted 6-Oct-11 7:32am

Srikanth Anandateertha

Comments

chaupha 7-Oct-11 11:24am

I use this already, but it can't handle xlsx.

Srikanth Anandateertha 7-Oct-11 11:39am

You can handle .xlsx also using this
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

Le@rner 29-Jun-13 6:19am

how to use it in vc++

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)

Alan N · Accepted Answer · 2011-10-06T07:05:00

It would help to know where the exception is thrown but a section of the code doesn't look right.

C#

private void getExcelData(string get_File)
{
    FileStream F_stream = File.Open(get_File, FileMode.Open, FileAccess.Read);

    _Excel_App = new Excel_ref.Application();
    _curr_WorkBook = _Excel_App.Workbooks.Open(openFileDialog1.FileName,
        0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
        "\t", false, false, 0, true, 1, 0);

1) Remove the File.Open because you want Excel to open the file.
2) Replace openFileDialog1.FileName with get_File.

Alan.