using System.IO;
//判断是否上传文件，若上传，上传的文件是否存在、格式是否正确。fileupload1为FileUpload 控件ID
protected bool IsFileupload(FileUpload fileupload1, out string message, out string filepath)
{
if (fileupload1.HasFile) //判断该控件是否包含文件
{
string fullfilename = fileupload1.PostedFile.FileName;
string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
string type = filename.Substring(filename.LastIndexOf('.') + 1); //截取文件名后缀
if (type.ToLower() == "xls")
{
string basepath = this.Server.MapPath("~\\Temp\\教师信息"); //保存到文件夹
string subpath = basepath + "\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + filename; //修改上传文件名字，避免重复名称
if (!File.Exists(subpath))
{
if (!Directory.Exists(Server.MapPath("~\\Temp")))
Directory.CreateDirectory(basepath);
if (!Directory.Exists(basepath))
Directory.CreateDirectory(basepath);
filepath = subpath;
message = "";
return true;
}
else
{
message = "您上传的文件已存在，请重命名后再上传！";
filepath = "";
return false;
}
}
else
{
message = "您上传的文件格式不正确！";
filepath = "";
return false;
}
}
else
{
message = "您未上传文件或上传的文件为空！";
filepath = "";
return false;
}
}
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections;
//将Excel表中数据转换为DataSet 类型
public static DataSet ExcelToDatatable(string filepath)
{
HSSFWorkbook hssfworkbook = null;
using (FileStream file
= new FileStream
(filepath, FileMode
.Open, FileAccess
.Read)) {
hssfworkbook
= new HSSFWorkbook
(file
); }
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataSet ds
= new DataSet
(); DataTable dt
= new DataTable
(); int rowIndex = 1;
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
DataRow dr = null;
if (rowIndex == 1)
{
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
string value;
if (cell == null)
value = string.Empty;
else
value = cell.ToString();
dt.Columns.Add(value);
}
if (dr != null)
dt.Rows.Add(dr);
}
else
{
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
string value;
if (cell == null)
value = string.Empty;
else
value = cell.ToString();
if(dr == null)
dr = dt.NewRow();
dr[i] = value;
}
if (dr != null)
dt.Rows.Add(dr);
}
rowIndex++;
}
ds.Tables.Add(dt);
return ds;
}
//导入按钮事件
protected void btn_users_Click(object sender, EventArgs e)
{
string message = "";
string filepath = "";
if (IsFileupload(FU_users, out message, out filepath))
{
FU_users.PostedFile.SaveAs(filepath);
DataSet ds = NPOITool.ExcelToDatatable(filepath);
if (ds != null && ds.Tables.Count > 0)
{
try
{
// 数据有效性判断
string error = "";//姓名是否为空\是否重复
List
<string> namelist
= new List
<string>(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
string name = dr[0].ToString();
if (string.IsNullOrEmpty(name))
{
error += (i + 2).ToString() + ",";
}
if (namelist.Contains(name))
{
error += (i + 2).ToString() + ",";
}
else
namelist.Add(name);
}
if (error == "")
{
//把数据添加到数据库
}
else
{
error = "以下行数姓名为空或重复：" + error.TrimEnd(',') + "\\n";
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + error + "')</script>");
File.Delete(filepath);
}
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('请按模板要求填写信息。')</script>");
File.Delete(filepath);
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('文件异常。')</script>");
File.Delete(filepath);
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + message + "')</script>");
}
}