A grid in WPF binds DataTable data.

1. First quote DocumentFormat.OpenXml.dll

2. Then create a new Openexcelhelper class to convert Excel to a DataTable.

<summary>
Organize a DataTable according to the specified Excel stream
</summary>
<param name= "SheetName" > sheet</param> to be read
<param name= "Stream" >excel file stream </param>
<returns>DataTable</returns>
public static DataTable Getexceldata (string sheetname, String stream)
{

using (spreadsheetdocument document = Spreadsheetdocument.open (stream, false))
{
Open Excel
ienumerable<sheet> sheets = document. Workbookpart.workbook.descendants<sheet> (). Where (s = = S.name = = SheetName);
if (sheets. Count () = = 0)
{
return null;
}
Worksheetpart Worksheetpart = (worksheetpart) document. Workbookpart.getpartbyid (sheets. First (). ID);
Get shared data in Excel
Sharedstringtable SST = document. WorkbookPart.SharedStringTablePart.SharedStringTable;
Get Data rows in Excel
ienumerable<row> rows = worksheetpart.worksheet.descendants<row> ();
DataTable dt = new DataTable ();
Because the data needs to be imported into the DataTable, we assume that the first line of Excel is the column name, and the row data starts from the second row
foreach (row row in rows)
{
if (row. RowIndex = = 1)
{
Excel first behavior column name
Getdatacolumn (row, SST, ref DT);
}
The second line of Excel is the first row of data for the DataTable
Getdatarow (row, SST, ref DT);
}
return DT;
}
}
private static void Getdatacolumn (row row, sharedstringtable SST, ref DataTable DT)
{
DataColumn dc = new DataColumn ();
foreach (cell cell in row)
{
String cellvalue = Getcellvalue (cell, SST);
DC = new DataColumn (cellvalue);
Dt. Columns.Add (DC);
}
}
<summary>
To build a column for a DataTable
</summary>
<param name= Row object defined by "row" >openxml </param>
<param name= "SST" ></param>
<param name= "DT" > The DataTable object to be returned </param>
private static void Getdatarow (row row, sharedstringtable SST, ref DataTable DT)
{
DataRow dr = dt. NewRow ();
int i = 0;
int nullrowcount = i;
foreach (cell cell in row)
{
String cellval = Getcellvalue (cell, SST);
if (Cellval = = string. Empty)
{
nullrowcount++;
}
Dr[i] = Cellval;
i++;
}
if (nullrowcount! = i)
{
Dt. Rows.Add (DR);
}

}
<summary>
Get the value of a cell
</summary>
<param name= "Cell" ></param>
<param name= "SST" ></param>
<returns></returns>
private static string Getcellvalue (cell cell, sharedstringtable SST)
{
Because Excel data is stored in sharedstringtable, you need to get the index of the data in sharedstringtable
String value = String. Empty;
Try
{
if (cell. Childelements.count = = 0)
{
return value;

}
Value = Double. Parse (cell. Cellvalue.innertext). ToString ();
if (cell. DataType = null) && (cell. DataType = = cellvalues.sharedstring))
{
Value = SST. Childelements[int32.parse (value)]. InnerText;
}
}
catch (Exception)
{

Value = "N/a";
}
return value;

}

3. The code that corresponds to the page.

4 page (AutoGenerateColumns must be set to true.) AutoGenerateColumns is the automatic generation of column meaning
GridView and other controls, if set Autogeneratecolumns=true, will be able to automatically generate the GridView table column according to the actual situation of the data source, if set to Autogeneratecolumns=flase, Then you have to manually write the columns collection of the GridView control, otherwise even if the data is correctly gridvew, nothing is displayed because the column is not defined, and the column is not automatically generated by himself.

A grid in WPF binds DataTable data.