Problem Excel to DataGridView

Question

Hi All,

when Excel to DataGridView getting error..!
after opening the Excel file.
Load data from Excel file to DataGridiView
Get Not any Error
also working perfectly
how to solve this error..?

best regards
Nazhim

my code snippet and Images at below
Error:

Excel File

opening file

final

my Code Snippet

private void button1_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data
Source='f:\Medicine-Names\ExceBookTest.xlsx';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "excel-tab");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();
}

thanks.

Answer 1

private void button1_Click(object sender, EventArgs e)
{
    string path = @"f:\Medicine-Names\ExceBookTest.xlsx";

    const string PROVIDER_XLS = "Microsoft.Jet.OLEDB.4.0"; // This can  open 'XLS' 
    const string PROVIDER_XLSX = "Microsoft.ACE.OLEDB.12.0"; // This can open 'XLS' and 'XLSX'
    string connectionString;
    string provider;
    if (string.Equals(System.IO.Path.GetExtension(path), ".xls", StringComparison.OrdinalIgnoreCase))
    {
        provider = PROVIDER_XLS;
    }
    else
    {
        provider = PROVIDER_XLSX;
    }

    var installedProviderNames = new System.Data.OleDb.OleDbEnumerator().GetElements().Rows.OfType<DataRow>().Select(row => (string)row["SOURCES_NAME"]).ToArray();
    if (!installedProviderNames.Any(p => string.Equals(p, provider, StringComparison.OrdinalIgnoreCase)))
    {
        MessageBox.Show("Provider is Not Installed or CPU type(x86,x64) miss match", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }


    connectionString = $@"Provider={ provider };Data Source='{ path }';Extended Properties=Excel 8.0;";

    System.Data.OleDb.OleDbConnection MyConnection;
    System.Data.DataSet DtSet;
    System.Data.OleDb.OleDbDataAdapter MyCommand;
    MyConnection = new System.Data.OleDb.OleDbConnection(connectionString);

    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
    MyCommand.TableMappings.Add("Table", "excel-tab");
    DtSet = new System.Data.DataSet();
    MyCommand.Fill(DtSet);
    dataGridView1.DataSource = DtSet.Tables[0];
    MyConnection.Close();
}

Answer 2

Take a look at the following method which uses a smart connection for Excel. In the code, I have a sheet named Customers, you need to replace it with your sheet name.

SmartConnection class which is in a class by itself and allow to set IMEX which may times needs to be adjusted dependent on the data in a sheet.

using System.Data.OleDb;

namespace ExcelOperations.OleDbWork
{
    public enum ExcelHeader
    {
        Yes,
        No
    }

    public class SmartConnection
    {
        public string ConnectionString(string pFileName, int pImex = 1, ExcelHeader pHeader = ExcelHeader.No)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
            if (System.IO.Path.GetExtension(pFileName)?.ToUpper() == ".XLS")
            {
                builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={pImex};HDR={pHeader.ToString()};");
            }
            else
            {
                builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={pImex};HDR={pHeader.ToString()};");
            }

            builder.DataSource = pFileName;

            return builder.ConnectionString;
        }

        public string ConnectionStringExporter(string pFileName, int pImex = 1, ExcelHeader pHeader = ExcelHeader.No)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
            builder.Provider = "Microsoft.ACE.OLEDB.12.0";
            builder.Add("Extended Properties", $"Excel 12.0 Xml;IMEX={pImex};HDR={pHeader.ToString()};");

            builder.DataSource = pFileName;

            return builder.ConnectionString;
        }

    }
}

Usage

public DataTable ReaDataTable(string pFileName)
{
    var dt = new DataTable();
    var selectStatement = "SELECT * FROM [Customers$]";
    var con = new SmartConnection();

    try
    {
        using (var cn = new OleDbConnection {ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.Yes)})
        {
            using (var cmd = new OleDbCommand {Connection = cn, CommandText = selectStatement})
            {
                cn.Open();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException());
            }
        }
    }
    catch (OleDbException oe)
    {
        // isolate specific exception and record to log
    }
    catch (Exception e)
    {
        // record exception to log of some sort
    }

    return dt;
}

Answer 3

Working with Excel can be problematic on various levels which is why I recommend using an Excel library. In this case SpreadSheetLight (home page has a downloadable help file) which is free and installed via NuGet (first link is .NET Framework version, second link .NET Core version).

I wrote a code sample for you, found here on GitHub.

Here I did just a few rows and demonstrate a simple update.

Form code

Note I tend to use a decent amount of assertion which can go away if you decide to which means less code but I recommend keeping it.

using System;  
using System.Data;  
using System.Windows.Forms;  
  
namespace SpreadSheetLight2  
{  
    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
            WriteButton.Enabled = false;  
  
            if (!ExcelOperations.FileExist)  
            {  
                ReadButton.Enabled = false;  
            }  
        }  
  
        private void ReadButton_Click(object sender, EventArgs e)  
        {  
            var dt = ExcelOperations.Read();  
            if (ExcelOperations.Exception == null)  
            {  
                dataGridView1.DataSource = dt;  
                WriteButton.Enabled = true;  
            }  
            else  
            {  
                MessageBox.Show(ExcelOperations.Exception.Message);  
            }  
  
        }  
  
        private void WriteButton_Click(object sender, EventArgs e)  
        {  
            var dt = (DataTable) dataGridView1.DataSource;  
  
            MessageBox.Show(ExcelOperations.Write(dt) ?   
                "Updated" :   
                ExcelOperations.Exception.Message);  
        }  
    }  
}  
  

Excel class

using System;  
using System.Data;  
using System.IO;  
using SpreadsheetLight;  
  
namespace SpreadSheetLight2  
{  
    public class ExcelOperations  
    {  
        private static string _excelFileName =>   
            Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExcelBookTest.xlsx");  
          
        private static string _sheetName = "Sheet1";  
  
        public static bool FileExist => File.Exists(_excelFileName);  
  
        public static Exception Exception { get; set; }  
          
        public static DataTable Read()  
        {  
  
            Exception = null;  
              
            var dt = new DataTable();  
              
            dt.Columns.Add("Student", typeof(string));  
            dt.Columns.Add("rollno", typeof(int));  
            dt.Columns.Add("Course", typeof(string));  
  
            try  
            {  
                using (var doc = new SLDocument(_excelFileName, _sheetName))  
                {  
  
                    var stats = doc.GetWorksheetStatistics();  
  
  
                    for (int index = 1; index < stats.EndRowIndex + 1; index++)  
                    {  
  
                        var col1Value = doc.GetCellValueAsString(index, 1);  
                        var col2Value = doc.GetCellValueAsString(index, 2);  
                        var col3Value = doc.GetCellValueAsString(index, 3);  
  
                        if (int.TryParse(col2Value, out var rollNumber))  
                        {  
                            dt.Rows.Add(col1Value, rollNumber, col3Value);  
                        }  
  
                    }  
  
                      
                }  
            }  
            catch (Exception ex)  
            {  
                Exception = ex;  
            }  
  
            return dt;  
        }  
  
        public static bool Write(DataTable pDataTable, bool pColumnHeaders = true)  
        {  
  
            Exception = null;  
            /*  
             * Copy the original DataTable so we can insert a row between  
             * first row column names and actual data to match up with  
             * the question asked.  
             */  
            var dt = pDataTable.Copy();  
            var dr = dt.NewRow();  
            dt.Rows.InsertAt(dr,0);  
  
            try  
            {  
                using (var doc = new SLDocument(_excelFileName))  
                {  
  
                    doc.SelectWorksheet(_sheetName);  
                    doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), dt, pColumnHeaders);  
  
                    var stats = doc.GetWorksheetStatistics();  
  
                    doc.AutoFitColumn(1, stats.EndColumnIndex);  
  
                    doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, _sheetName);  
  
                    doc.SaveAs(_excelFileName);  
  
                    return true;  
  
                }  
            }  
            catch (Exception ex)  
            {  
                Exception = ex;  
                return false;  
            }  
        }  
  
    }  
}  

Notes

• SpreadSheetLight has a fair amount of styling options.
• I've used this library but for work use GemBox Spreadsheet library which is not worth spending money on for small Excel operations.

.