Problem Excel to DataGridView

NazHim 201 Reputation points
2021-03-19T18:50:27.773+00:00

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:
79801-screenshot-2021-03-19-235328.png
Excel File
79802-screenshot-2021-03-19-235726.png
opening file
79773-screenshot-2021-03-19-235522.png
final
79774-screenshot-2021-03-19-235937.png

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.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
7,035 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. gekka 1,031 Reputation points
    2021-03-20T00:55:22.93+00:00
    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();
    }
    

  2. Karen Payne MVP 29,236 Reputation points MVP
    2021-03-20T02:41:05.59+00:00

    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;
    }
    
    No comments

  3. Karen Payne MVP 29,236 Reputation points MVP
    2021-03-20T12:07:00.757+00:00

    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.

    79738-spreadsheetlight1.png

    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.

    .

    No comments