C # Read Excel Data to the Gridview

# Region read Excel Data to the Gridview

Public void ReadExcel (string sExcelFile, GridView dgBom)
{

DataTable ExcelTable;
DataSet ds = new DataSet ();

// Excel connection
OleDbConnection objConn = new OleDbConnection ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + sExcelFile + ";" + "Extended Properties = Excel 8.0 ;");
ObjConn. Open ();
DataTable schemaTable = objConn. GetOleDbSchemaTable (System. Data. OleDb. OleDbSchemaGuid. Tables, null );

String tableName = schemaTable. Rows [0] [2]. ToString (). Trim (); // obtain the table name in Excel. The default value is sheet1.
String strSql = "select * from [" + tableName + "]";

OleDbCommand objCmd = new OleDbCommand (strSql, objConn );
OleDbDataAdapter myData = new OleDbDataAdapter (strSql, objConn );

// Fill in data
MyData. Fill (ds, tableName );

ObjConn. Close ();

ExcelTable = ds. Tables [tableName];
Int iColums = ExcelTable. Columns. Count; // Number of Columns
Int iRows = ExcelTable. Rows. Count; // number of Rows

// Define a two-dimensional array to store data read from an Excel table
String [,] storedata = new string [iRows, iColums];

ArrayList list = new ArrayList ();

For (int I = 0; I <ExcelTable. Rows. Count; I ++)
{
SupermarketVO vo = new SupermarketVO ();

For (int j = 1; j <ExcelTable. Columns. Count; j ++)
{
// Store data in an Excel table to an array
Storedata [I, j] = ExcelTable. Rows [I] [j]. ToString ();

If (j = 1)
{
Vo. Sup_nm = ExcelTable. Rows [I] [j]. ToString ();
}
Else if (j = 2)
{
Vo. Sup_linker = ExcelTable. Rows [I] [j]. ToString ();
}
Else if (j = 3)
{
Vo. Phone = ExcelTable. Rows [I] [j]. ToString ();
}
}

// Ignore this record if the name, contact, and phone number are empty
If (vo. Sup_nm.Trim () = "" & vo. Sup_linker.Trim () = "" & vo. Phone. Trim () = "")
{
Continue;
}
Else
{
List. Add (vo );
}
}

// Determine the number of records to facilitate the display of empty records
If (list. Count <1)
{
SupermarketVO vo = new SupermarketVO ();
List. Add (vo );

// Set the display (including the display of the header) and binding record when an empty record is set
GridviewControl. GridViewDataBind (dgBom, list );
}
Else
{
DgBom. DataSource = list;
DgBom. DataBind ();
}

LblErrorInfo. Text = "the import operation has been completed. ";
}

# Endregion