项目中经常用到导入导出的例子,前面做了导出的例子,现在把导入Excel的数据的例子也把它晾出来,不足之处,请大家多多指教。
1.导入前的视图Excel和页面视图
2.导入后的视图
3.前台代码
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="请选择上传文件(.xls)"></asp:Label>
<asp:FileUpload ID="fUExcel" runat="server" />
<asp:Button ID="btnExcel" runat="server" Text="确定" OnClick="btnExcel_Click" />
<asp:GridView ID="gvExcelData" runat="server" BackColor="LightGoldenrodYellow"
BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black"
GridLines="None">
<FooterStyle BackColor="Tan" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
</div>
</form>
</body>
4.后台代码
protected void btnExcel_Click(object sender, EventArgs e)
{
HttpPostedFile p = fUExcel.PostedFile;
string filename = DateTime.Now.ToString("yyyyMMddHHmm") + Path.GetFileName(p.FileName);
if (!Directory.Exists(Server.MapPath(@"~/upload" + "//")))
Directory.CreateDirectory(Server.MapPath(@"~/upload" + "//"));
string filePath = Server.MapPath(@"~/upload" + "//" + filename);
fUExcel.SaveAs(filePath);
if (filename != "")
{
if (filename.Contains("xls"))
{
OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
oleCon.Open();
string Sql = "SELECT * FROM [Sheet1$] ";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
oleCon.Close();
int count = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string ItemId = ds.Tables["[Sheet1$]"].Rows[i]["项目Id"].ToString().Trim();
string Fixnumber = ds.Tables["[Sheet1$]"].Rows[i]["用户号码"].ToString().Trim();
string LinkMan = ds.Tables["[Sheet1$]"].Rows[i]["联系人姓名"].ToString().Trim();
//这里可以写插入数据库的代码
}
gvExcelData.DataSource = ds;
gvExcelData.DataBind();
Response.Write("<script>window.alert('导入数据成功!');</script>");
}
else
{
Response.Write("<script>window.alert('请检查您选择的文件是否为Excel文件!');</script>");
return;
}
}
else
{
Response.Write("<script>window.alert('请先选择导入文件后,再执行导入!');</script>");
return;
}
}
5.注意得引入命名空间
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Sql;
6.最后一点就是你要导入的Excel表的表头是
项目Id、用户号码、联系人姓名