完整ASP.Net Excel导入程序，支持2007
//把EXCEL文件上传到服务器并返回文件路径
private String typename(FileUpload fileloads)
{
string fullfilename = fileloads.PostedFile.FileName;
string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
string murl = "";
if (type == "xls" || type == "xlsx")
{
fileloads.PostedFile.SaveAs(Server.MapPath(filename));
//fileloads.PostedFile.SaveAs(Server.MapPath(Tools.GetAppSettings("ExcelPath")) + "\\" + filename);
murl = Server.MapPath(filename);
}
else
{
Tools.ShowMsg("导入文件格式不对！");
//Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>");
}
return murl;
}
public DataTable ImportToDataSet(string path)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
DataTable dt = new DataTable();
if (conn.State != ConnectionState.Open)
conn.Open();
string strExcel = "select * from [Sheet1$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
adapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
DataTable dt = ImportToDataSet(typename(selectfile));
int j = 0;
bool sec = true;
for (int i = 0; i < dt.Rows.Count; i++)
{
ShippingCharge sc = new ShippingCharge();
string RegionID = dt.Rows[i][1].ToString();
if (!RegionID.IsNullOrEmpty() && Service.GetShippingRegion(dt.Rows[i][1].ToString()) != null)
{
sc.RegionID = Service.GetShippingRegion(dt.Rows[i][1].ToString()).RegionID;
}
sc.Status = Convert.ToByte(dt.Rows[i][2].ToString()=="否"?0:1);
sec = Service.AddShippingCharge(sc);
if (sec == false)
{
j++;
}
}
if(j > 0)
{
Tools.ShowMsg("导入文件失败,请确认文件格式再重试一次！");
}
else
{
//关闭,删除 文件
string filepath = Server.MapPath(selectfile.PostedFile.FileName);
if (File.Exists(filepath))
File.Delete(filepath);
Tools.ShowMsg("导入文件成功！", "ShippingCharges_List.aspx");
}
}
catch (Exception ex)
{
Tools.ShowMsg("导入文件失败,请确认文件格式再重试一次！");
}
}