// 上传按钮
protected void btnUp_Click(object sender, EventArgs e)
{
bool b = Upload(fuExcel); // 上传excel文件
if (!b)
{ return; }
string name = fuExcel.FileName;
string filepath = Server.MapPath("~/upload/") + name;
DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
GridView1.DataSource = ds;
GridView1.DataBind();
}
//上传文件方法
private bool Upload(FileUpload myFileUpload)
{
bool flag = false; //是否允许上载
bool fileAllow = false; //设定允许上载的扩展文件名类型
string[] allowExtensions = { ".xls" }; //取得网站根目录路径
string path = HttpContext.Current.Request.MapPath("~/upload/");
if (myFileUpload.HasFile)
{
string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
for (int i = 0; i < allowExtensions.Length; i++)
{
if (fileExtension == allowExtensions[i])
{
fileAllow = true;
}
}
if (fileAllow)
{
try
{
//存储文件到文件夹
myFileUpload.SaveAs(path + myFileUpload.FileName);
lblMes.Text = "文件导入成功"; flag = true;
}
catch (Exception ex)
{
lblMes.Text += ex.Message; flag = false;
}
}
else
{
lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
flag = false;
}
}
else
{
lblMes.Text = "请选择要导入的excel文件!";
flag = false;
}
return flag;
}
//该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名
public DataSet ExcelDataSource(string filepath, string sheetname)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
conn.Close();
return ds;
}
//获得Excel中的所有sheetname。
public ArrayList ExcelSheetName(string filepath)
{
ArrayList al = new ArrayList();
string strConn;
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();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
return al;
}