C # import an Excel file and display it with repreater

Background

 

// Import the employee to be notified
Protected void btnload_click (Object sender, eventargs E)
{
If (flup. hasfile)
{
Datatable dt = transferdata (flup. postedfile. filename, "sheet1 ");
If (DT! = NULL)
{
If (Dt. Columns. Contains ("name") & DT. Columns. Contains ("mobile phone number") & DT. Columns. Contains ("organization abbreviation "))
{
Rpt_employeelist.datasource = DT;
Rpt_employeelist.databind ();
}
}
}
Else
{
Pagehelp. Alert (this, "", "select the file to import! ");
}
}

/// <Summary>
/// Read Excel Data to Dataset
/// </Summary>
/// <Param name = "excelfile"> absolute Excel path </param>
/// <Param name = "sheetname"> Excel name ---- table name </param>
/// <Returns> </returns>
Public datatable transferdata (string excelfile, string sheetname)
{
Dataset DS = new dataset ();
Datatable dt = new datatable ();
Try
{
Exceloperate = new exceloperate ();
Excel. Application xapp = new excel. applicationclass ();

Excel. Workbook xbook = xapp. workbooks. _ open (excelfile,
Missing. Value, missing. Value
, Missing. Value
, Missing. Value, missing. value );

Excel. worksheet xsheet = (Excel. worksheet) xbook. Sheets [1];
String table = xsheet. Name;

Exceloperate. Dispose (xsheet, xbook, xapp );
// Obtain all data
String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + excelfile + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Conn. open ();
String strexcel = "";
Oledbdataadapter mycommand = NULL;
Strexcel = string. Format ("select * from [{0} $]", table );
Mycommand = new oledbdataadapter (strexcel, strconn );
Mycommand. Fill (DS, sheetname );
Conn. Close ();
Dt = Ds. Tables [0];
Return DT;
}
Catch (exception ex)
{
Pagehelp. Alert (ex. tostring ());
Return NULL;
}
}

/// <Summary>
/// Determine whether the Excel column value has a null value
/// </Summary>
/// <Param name = "DT"> </param>
/// <Returns> </returns>
Public datatable validatedatatable (datatable DT)
{
Foreach (datarow row in DT. Rows)
{
For (INT I = 0; I <DT. Columns. Count; I ++)
{
If (string. isnullorempty (row [I]. tostring ()))
{
Row [I] = dbnull. value;
}
}
If (string. isnullorempty (row ["name"]. tostring () & string. isnullorempty (row ["ID card"]. tostring ()))
{
Row. Delete ();
}
}
Return DT;
}

 

 

Front-end

 

<Table cellpadding = "0" cellspacing = "0" class = "table">
<Tbody style = "margin-left: 40px">
<Tr>
<TH style = "width: 5%">
Name
</Th>
<TH style = "width: 7%">
Mobile phone number
</Th>
<TH style = "width: 7%">
Organization abbreviation
</Th>
</Tr>
<Asp: repeater id = "rpt_employeelist" runat = "server">
<Itemtemplate>
<Tr>
<TD>
<% # Eval ("name") %>
</TD>
<TD>
<% # Eval ("mobile phone number") %>
</TD>
<TD>
<% # Eval ("organization abbreviation") %>
</TD>
</Tr>
</Itemtemplate>
</ASP: repeater>
</Tbody>
</Table>

 

 

Imported Excel File Format

 

Name	Mobile phone number	Organization abbreviation
Power Generation	45345	Electric Fan Method
On time	3244234	Local perception