question

NazHim-9882 avatar image
0 Votes"
NazHim-9882 asked ·

Problem Excel to DataGridView

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.

dotnet-csharp
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

gekka avatar image
0 Votes"
gekka answered ·
 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();
 }
· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello
Thanks for your fast response
xlsx and xls both are not working..!
getting error

if use xlsx file
Provider is Not Installed or CPU type(x86,x64)miss match

if use xlx file
System.Data.OleDb.OleDbException: "Sheet1$" is not a valid name

best regards
NazHim

0 Votes 0 ·

Hello
Thanks for your fast response
xlsx and xls both are not working..!
getting error

if use xlsx file
Provider is Not Installed or CPU type(x86,x64)miss match
79825-screenshot-2021-03-20-075735.png

if use xlx file
System.Data.OleDb.OleDbException: "Sheet1$" is not a valid name
79786-screenshot-2021-03-20-083855.png

best regards
NazHim


0 Votes 0 ·

Provider is Not Installed or CPU Type missmatch.

You need install Access Database Engine. (link for version 2016)
If you installed already , try change platform target x64 to x86 or x86 to x64.
There are two types of Access Database Engine, for 32bit and for 64bit.


"Sheet1$" is not valid name

This error occurs when a non-existent file path is specified.


0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

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.

.










·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.