C #: Use OLE DB to read information from an Excel table to a DataTable

Reading data into the DataTable data type from an Excel table, I did this by using OLE DB

(OLE DB is an abbreviation for object linking and embedding database)

There is a workbook file with an extension of xlsx, "throttle list. xlsx", with 24 solar terms in the sheet Sheet1

The example program in this article (the code will be given later) reads the data table after the effect such as:

As you can see, after OLE DB reads data from an Excel worksheet (Sheet), the first row of the worksheet becomes the title, and the second line is a row of data rows into a DataTable.

Description of the sample program control:

Program code:

using system;using system.collections.generic;using system.componentmodel;using  system.data;using system.data.oledb;using system.drawing;using system.linq;using  system.text;using system.threading.tasks;using system.windows.forms;namespace excelreader{     public partial class formmain : form    {         public formmain ()          {            initializecomponent ();         }        private  Void formmain_load (object sender, eventargs e)          {            txtxlsxpath.text = @ " Throttle meter. xlsx ";             txtsheetname.text  = @ "Sheet1";         }        /// <summary>         ///  Buttons: Reading information from an Excel workbook           </summary>        /// <param name= "Sender" ></param>        /// <param name= "E" ></ Param>        private void btnread_click (object  Sender, eventargs e)         {             dgvtable.datasource = readfromexcel (TxtXlsxPath.Text,  txtsheetname.text);        }         /// <summary>        ///  Read information from an Excel workbook to a DataTable (requires System.Data.OleDb)         /// </ Summary>        /// <param name= "SXlsxPath" > Excel workbook file Address </param>        /// <param name= " Ssheetname "> Sheet name </param>        private DataTable  Readfromexcel (String sxlsxpath, string ssheetname)          {            string sExt =  System.IO.Path.GetExtension (Sxlsxpath);             string sConn =  "";             if  (sext ==  ". xlsx")  //excel2007            {                 sConn =                       "provider=microsoft.ace.oledb.12.0;"  +                        "data source="  + sXlsxPath +  ";"  +                        "Extended properties= ' excel 12.0; Hdr=yes ' ";            }             else if  (sext ==  ". xls")  //excel2003             {                sconn =                      " provider=microsoft.jet.oledb.4.0; "  +                      "data source="  + sXlsxPath +  ";"  +                      "extended properties=excel 8.0";             }            else             {                 throw new exception ("Unknown file type");             }             Oledbconnection oledbconn = new oledbconnection (sconn);             oledbconn.open ();             oledbdataadapter command = new oledbdataadapter (                  "select * from ["  + sSheetName +  "$]",  oledbconn);             dataset ds = new dataset ();             command. Fill (Ds, ssheetname);             Oledbconn.close ();                       return ds. tables[ssheetname];        }    }}

This program was written with two exceptions, and the workaround is as follows:

1) Exception "External table is not the expected format":

When writing a connection expression (sconn in the code above), discuss the extension. xlsx and. xls classifications

2) Exception: "Could not find installable ISAM":

When reading an Excel table in. xlsx format, the Extended Properties section of the join expression, the character after the equal sign is enclosed in single quotation marks, and if a single quotation mark is omitted, it is better to fill it up.

END

C #: Use OLE DB to read information from an Excel table to a DataTable