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>
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();
}
/// 读取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
