Using OleDB to read excel file in c#?

Question

I am building a program to read excel file into dataGridView.

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.Data;
using System.Data.OleDb;

namespace pro1._0
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sConnecStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\\Copy_of_Acute_HCV_2008.xls" + ";" + "Extended Properties=Excel 8.0;"; 
            OleDbConnection conObj = new OleDbConnection(sConnecStr);
            conObj.Open();
            OleDbCommand sqlCommand  = new OleDbCommand("SELECT * FROM [Sheet1$]",conObj);
            OleDbDataAdapter adaObj = new OleDbDataAdapter();
            adaObj.SelectCommand = sqlCommand;
            DataSet setObj = new DataSet();
            adaObj.Fill(setObj);
            conObj.Close();
            dataGridView1.DataSource = setObj.Tables[0];

            dataGridView1.Refresh();

        }
    }
}

The program runs fine when i use a small excel file but when i use a big excel file it gives me this error

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: 'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

thanks

edit: i always use .xls files not .xlsx

use Microsoft.ACE.OLEDB.12.0 instead of Microsoft.Jet.OLEDB.4.0 — Romil Kumar Jain
– Romil Kumar Jain, Commented May 1, 2012 at 11:48

Bo Persson · Accepted Answer · 2013-04-06 09:09:04Z

1

protected void btnUpload_Click(object sender, EventArgs e)
{
    try
    {
        if ((txtFilePath.HasFile))
        {
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            string query = null;
            string connString = "";
            string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
            string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
            }

            string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed) conn.Open();
            string SpreadSheetName = "";
            DataTable ExcelSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
            query = "SELECT * FROM [" + SpreadSheetName + "]";
            cmd = new OleDbCommand(query, conn);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "tab1");
         }
      }
 }

edited Apr 6, 2013 at 9:09

Bo Persson

92.9k31 gold badges153 silver badges211 bronze badges

answered Apr 6, 2013 at 8:46

Geeta

211 bronze badge

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

2 Comments

Karl-Johan Sjögren Over a year ago

This is poorly formatted and doesn't give any sort of explanation, just code (which also doesn't seem to be based on the code in the question but rather some other project). I see that you are a new user, so try to make your coming answers more descriptive and try to really help the person asking instead of just giving large chunks of code.

joce Over a year ago

@Geeta Please flesh out your answer. Give out some explanations as to why this code helps the OP.

Collectives™ on Stack Overflow

Using OleDB to read excel file in c#?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related