Excel数据导入到GridView中

Excel数据导入到GridView中

                                                           <图一 未导入Excel数据前的GridView>

   

                                        <图二 导入的Excel数据文件>

                                                     <图三 导入后的GridView>

第一步 前台主要代码

< asp:GridView ID = " GridView1 " runat = " server " AutoGenerateColumns = " False " CellPadding = " 4 "
ForeColor = " #333333 " Font - Size = " 14px " AllowPaging = " True " PageSize = " 10 " OnDataBound = " NewPage "
DataKeyNames = " studentId " OnRowDeleting = " GridView1_RowDeleting " >
< FooterStyle BackColor = " #507CD1 " Font - Bold = " True " ForeColor = " White " />
< RowStyle BackColor = " #EFF3FB " />
< Columns >
< asp:TemplateField HeaderText = " 学生ID " >
< ItemTemplate >
< asp:Label ID = " Label1 " runat = " server " Text = ' <%# Eval("studentId") %> ' Width = " 80px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = " 姓名 " >
< ItemTemplate >
< asp:Label ID = " Label2 " runat = " server " Text = ' <%# Eval("name") %> ' Width = " 60px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = " 专业 " >
< ItemTemplate >
< asp:Label ID = " Label3 " runat = " server " Text = ' <%# Eval("subject") %> ' Width = " 60px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = " 学院 " >
< ItemTemplate >
< asp:Label ID = " Label4 " runat = " server " Text = ' <%# Eval("college") %> ' Width = " 75px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = " 手机号 " >
< ItemTemplate >
< asp:Label ID = " Label5 " runat = " server " Text = ' <%# Eval("cellphone") %> ' Width = " 80px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = " 邮箱 " >
< ItemTemplate >
< asp:Label ID = " a " runat = " server " Text = ' <%# Eval("email") %> ' Width = " 120px " ></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField ShowHeader = " False " >
< ItemTemplate >
< asp:Button ID = " Button1 " CssClass = " btn " runat = " server " CommandName = " delete " Text = " 删除 " />
</ ItemTemplate >
< ControlStyle BackColor = " #FFC0C0 " />
< ItemStyle HorizontalAlign = " Center " />
</ asp:TemplateField >
</ Columns >
< PagerTemplate >
< asp:Table ID = " Table1 " Width = " 100% " runat = " server " >
< asp:TableRow >
< asp:TableCell Width = " 200px " >
< asp:Label ID = " lblMessage " ForeColor = " Blue " Text = " 请选择页码: " runat = " server " CssClass = " bottom " />
< asp:DropDownList ID = " myDropDownList " AutoPostBack = " true " OnSelectedIndexChanged = " ChangePage "
runat = " server " />
< asp:LinkButton ID = " btnPrev " Style = " text-decoration: none " OnClick = " ChangePage " runat = " server "
Text = " 上一页 " >
</ asp:LinkButton >
< asp:LinkButton ID = " btnNext " Style = " text-decoration: none " OnClick = " ChangePage " runat = " server "
Text = " 下一页 " >
</ asp:LinkButton >
</ asp:TableCell >
< asp:TableCell Width = " 200px " HorizontalAlign = " right " >
< asp:Label ID = " lblPageLabel " ForeColor = " Blue " runat = " server " Width = " 200px " />
</ asp:TableCell >
</ asp:TableRow >
</ asp:Table >
</ PagerTemplate >
< PagerStyle BackColor = " #2461BF " ForeColor = " White " HorizontalAlign = " Center " />
< SelectedRowStyle BackColor = " #D1DDF1 " Font - Bold = " True " ForeColor = " #333333 " />
< HeaderStyle BackColor = " #507CD1 " Font - Bold = " True " ForeColor = " White " />
< EditRowStyle BackColor = " #2461BF " />
< AlternatingRowStyle BackColor = " White " />
</ asp:GridView >

< div >< span style = " font-weight:bold " > 批量导入 </ span ></ div >
< div >< span style = " width:100px " > 选择Excel文件 </ span >< asp:FileUpload ID = " fudExcel "
runat = " server " />
< asp:Button
ID = " Button2 " runat = " server " Text = " 上传 " onclick = " Button2_Click " />
< asp:HyperLink ID = " HyperLink1 " runat = " server "
NavigateUrl = " ~/upload/学生信息导入模板.xls " Font - Size = " XX-Small " > 下载导入模板 </ asp:HyperLink >
</ div >
</ div >

第二步 后台主要代码

/// <summary>
/// 读取Excel数据
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
public DataTable ExcelDataSource( string filepath, ref bool existsSheetname)
{
DataTable dt = null ;
string sheetname = " Sheet1$ " ;
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + " ;Extended Properties=Excel 8.0; " ;
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object [] { null , null , null , " TABLE " });
conn.Close();

// 获取第0个sheet
if (sheetNames.Rows.Count > 0 )
{
foreach (DataRow row in sheetNames.Rows)
{
if (row[ 2 ].ToString() == sheetname)
{
existsSheetname = true ;
break ;
}
}
}
if (existsSheetname)
{
OleDbDataAdapter oada = new OleDbDataAdapter( " select * from [ " + sheetname + " ] " , strConn);
dt = new DataTable();
dt.Columns.Add( new DataColumn( " studentId " ));
dt.Columns.Add( new DataColumn( " name " ));
dt.Columns.Add( new DataColumn( " subject " ));
dt.Columns.Add( new DataColumn( " college " ));
dt.Columns.Add( new DataColumn( " cellphone " ));
dt.Columns.Add( new DataColumn( " email " ));
oada.Fill(dt);
}
return dt;
}

protected void Button2_Click( object sender, EventArgs e)
{
#region 验证文件
if ( string .IsNullOrEmpty(fudExcel.FileName))
{
ScriptManager.RegisterStartupScript( this , this .GetType(), " updateScript " , " alert(\"请选择上传文件 \"); " , true );
return ;
}
string extension = fudExcel.FileName.Substring(fudExcel.FileName.LastIndexOf( ' . ' ));

if (extension == " .xlsx " )
{
ScriptManager.RegisterStartupScript( this , this .GetType(), " updateScript " , " alert(\"目前模板只支持Excel2003版文件，请转换后再导入！\"); " , true );

return ;
}
if (extension != " .xls " )
{
ScriptManager.RegisterStartupScript( this , this .GetType(), " updateScript " , " alert(\"上传文件扩展必须是(xls/xlsx)文件！\"); " , true );
return ;
}
#endregion
string filepath = string .Empty;
// 上传到服务器临时目录下
string tempdir = Server.MapPath( " ../upload/ " );
string filename = Guid.NewGuid() + extension;
filepath = tempdir + filename;
// 保存
fudExcel.SaveAs(filepath);
bool existsSheetname = false ;
// 读取到DataTable
var data = ExcelDataSource(filepath, ref existsSheetname);
if ( ! existsSheetname)
{
ScriptManager.RegisterStartupScript( this , this .GetType(), " updateScript " , " alert(\"没有找到《模板工作表》工作表!\"); " , true );
return ;
}

// 删除临时文件
System.IO.File.Delete(filepath);
if (data == null )
{
ScriptManager.RegisterStartupScript( this , this .GetType(), " updateScript " , " 解析Excel失败，请检查Excel是否符合模板要求！\"); " , true );
return ;
}

foreach (DataRow row in data.Rows)
{
string id = row[ " studentId " ].ToString();
var arrRow = DataSource.Select( " studentId=' " + id + " ' " );
if (arrRow != null && arrRow.Length > 0 )
{
}
else
{
DataRow newrow = DataSource.NewRow();
newrow[ " studentId " ] = row[ " studentId " ];
newrow[ " name " ] = row[ " name " ];
newrow[ " subject " ] = row[ " subject " ];
newrow[ " college " ] = row[ " college " ];

StudentsManage sm = new StudentsManage();
if (sm.SelectByValue(newrow[ " studentId " ].ToString()).Rows.Count == 0 )
{
students n = new students();
n.StudentId = newrow[ " studentId " ].ToString();
n.Name = newrow[ " name " ].ToString();
n.Subject = newrow[ " subject " ].ToString();
n.College = newrow[ " college " ].ToString();
n.Cellphone = "" ;
n.Creater = Session[ " adminId " ].ToString();
n.Pwd = newrow[ " studentId " ].ToString();
n.Email = "" ;
n.Sex = "" ;
sm.Insert(n);
}
StuCourseManage scm = new StuCourseManage();
stuCourse m = new stuCourse();
m.ClassId = Convert.ToInt32(Request.QueryString[ " classId " ]);
m.StudentId = newrow[ " studentId " ].ToString();
m.Creater = Session[ " adminId " ].ToString();
scm.InsertStu(m);
}

}
gridviewBind();

}

更加详细深入的分析请参考http://www.cnblogs.com/yizhuqing/archive/2011/01/24/1943407.html

转载于:https://www.cnblogs.com/xiaowu/archive/2011/06/28/2092391.html