前台
代码
<div class="left">
<ul>
<li>
<asp:FileUpload ID="fuload" runat="server" />
</li>
<asp:Label ID="lbmsg" runat="server"></asp:Label>
<li>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="导入信息" />
</li>
<li>
</li>
</ul>
</div>
</div>
</form>
后台代码
代码
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
namespace gene
{
public partial class drexcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
private DataTable xsldata()
{
if (fuload.FileName == "")
{
lbmsg.Text = "请选择文件";
return null;
}
string fileExtenSion;
fileExtenSion = Path.GetExtension(fuload.FileName);
if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx")
{
lbmsg.Text = "上传的文件格式不正确";
return null;
}
try
{
string FileName = "App_Data/" + Path.GetFileName(fuload.FileName);
if (File.Exists(Server.MapPath(FileName)))
{
File.Delete(Server.MapPath(FileName));
}
fuload.SaveAs(Server.MapPath(FileName));
//HDR=Yes,这代表第一行是标题,不做为数据使用 ,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES
string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
OleDbConnection conn;
if (fileExtenSion.ToLower() == ".xls")
{
conn = new OleDbConnection(connstr2003);
}
else
{
conn = new OleDbConnection(connstr2007);
}
conn.Open();
string sql = "select * from [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
DataTable dt = new DataTable();
OleDbDataReader sdr = cmd.ExecuteReader();
dt.Load(sdr);
sdr.Close();
conn.Close();
//删除服务器里上传的文件
if (File.Exists(Server.MapPath(FileName)))
{
File.Delete(Server.MapPath(FileName));
}
return dt;
}
catch (Exception e)
{
return null;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
DataTable dt = xsldata();
int insertcount = 0;//记录插入成功条数
for (int i = 0; i < dt.Columns.Count; i++) //列数
{
string infoid = dt.Rows[0][i].ToString();//获取编号
for (int j = 1; j < 16; j++)
{
int baifen = Convert.ToInt32(dt.Rows[j][i].ToString());//获取每个编号下的百分比
string sql = "update chkitem set baifen=" + baifen + " where infoid='" + infoid + "' and itemid=" + j;
System.Data.DataTable dw = DB.getDataTable(sql);
insertcount++;
}
}
Response.Write(insertcount + "条数据导入成功!");
//Response.Write("<script languge='javascript'>alert('信息导入成功');window.location.href='checkzi.aspx'</script>");
//Response.End();
//Response.Write("<script>alert('导入成功!')</script>");
}
catch (Exception ex)
{
}
}
}
}