C# 将Excel里面的数据填充到DataSet中
/// <summary>
/// 将Excel表里的数据填充到DataSet中
/// </summary>
/// <param name="filenameurl">Excel文件的路径(包含文件名)</param>
/// <param name="table">Excel的文件名</param>
/// <returns></returns>
public static DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + filenameurl + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, table);
return ds;
}
//应用示例
/// <summary>
/// 导入Excel信息
/// </summary>
/// <param name="context"></param>
/// <param name="returnMsg"></param>
/// <returns></returns>
public bool ImportInfo(HttpContext context, ref string returnMsg)
{
bool isError = true;
try
{
string FilePath = HttpContext.Current.Request.Files["file1"].FileName; //获取上传的文件名
if (FilePath != "")
{
string IsXls = System.IO.Path.GetExtension(FilePath).ToString().ToLower();//获取文件的后缀名
if (IsXls == ".xls" || IsXls == ".xlsx") //判断是否是Excel文件
{
string FileRoot = ""; //文件保存路径
string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + IsXls;//更改文件名(用当前的时间命名)
HttpContext.Current.Request.Files["file1"].SaveAs(context.Server.MapPath(FileRoot + FileName));//保存上传的文件
DataSet ds = ExecleDs(HttpContext.Current.Server.MapPath(FileRoot + FileName), FileName);//读取excel文件内容 填充到DataSet
DataRow[] dr = ds.Tables[0].Select();//定一个DataRow数组
int rowsNum = ds.Tables[0].Rows.Count; //行总量
if (rowsNum == 0) //判断excel是否为空
{
returnMsg = "Excel表为空表,无数据!";
return false;
}
DataTable dt = ds.Tables[0];
string code = "";
for (int i = 0; i < dr.Length; i++)
{
//数据处理
//if (dr[i].Table.Columns.Contains("行标题") && dr[i]["行标题"] != null)
//{
// if (dr[i]["行标题"].ToString().Trim() == "")
//{
// returnMsg = "第" + (i + 2) + "行的**不能为空,请修改后重新导入!";
//return false;
//}
//code = dr[i]["行标题"].ToString().Trim();
//}
//else
//{
// returnMsg = "缺少**列,请修改后重新导入!";
//return false;
//}
}
return isError;
}
else
{
returnMsg = "只可以选择Excel文件!";
return false;
}
}
else
{
returnMsg = "请选择要导入的Excel文件!";
return false;
}
}
catch(Exception ex)
{
returnMsg = "系统发生未知错误，请联系系统管理员！";
return false;
}
}