ASP.NET中将规则的Excel导入数据库程序

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + serverFilename + "';Extended Properties='Excel 12.0;HDR=YES'";

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + serverFilename + "';Extended Properties='Excel 8.0;HDR=YES;'";

protected void btnImport_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write(" ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
if(IsXls!=".xlsx")
{
Response.Write("");
return;//当选择的不是Excel文件时,返回
}
}
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("upfiles") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExcelSqlConnection(savePath, filename,IsXls); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write(""); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i {
//前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面
string title = dr[i]["标题"].ToString();
string linkurl = dr[i]["链接地址"].ToString();
string categoryname = dr[i]["分类"].ToString();

//Response.Write("");
}
Response.Write("");
}

}

#region 连接Excel 读取Excel数据 并返回DataSet数据集合
///

string filename = string.Empty;
try
{
filename = UpdateXls(this.FileExcel);//上传XLS文件
ImportXlsToData(filename);//将XLS文件内容导入
//删除文件
if (filename != string.Empty && File.Exists(filename))
{
File.Delete(filename);//删除上传的文件
}
}
catch(Exception ex)
{
this.WriteErrorXML(ex);//出错处理
this.lblMessage.Text = ex.Message;//错误显示
}
finally
{
//重新加载页面
LoadData();
}

/**////

oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

if (m_tableName != null && m_tableName.Rows.Count > 0)
{

m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();

}
string sqlMaster;
sqlMaster=" SELECT * FROM ["+m_tableName.TableName+"]";
oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
oleAdMaster.Fill(ds,"m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();

//测试是否提取数据
//this.Datagrid1.DataSource = ds.Tables["m_tableName"];
//this.Datagrid1.DataBind();
//将Dataset中数据导入SQL
AddDatasetToSQL(ds);

}
catch(Exception ex)
{
throw ex;
}
}

//上传Excel文件
private string UpdateXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "" ;//文件扩展名
int fileSize = 0;//文件大小
try
{
if(inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if(fileSize == 0 )
{
throw new Exception("找不到该文件！");
}
//得到扩展名
fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
if(fileExtend.ToLower() != "xls")
{
throw new Exception("请确认您所导入的文件是否EXCEL文件！！");
}
//路径
uploadfilepath = System.Web.HttpContext.Current.Server.MapPath(".")+path;
//新文件名
modifyfilename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString()
+ DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString()
+ DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString()
+ DateTime.Now.Millisecond.ToString();
modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath+modifyfilename;
//如果存在,删除文件
if(File.Exists(orifilename))
{
File.Delete(orifilename);
}
// 上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("没有选择Excel文件!");
}
}
catch(Exception ex)
{
throw ex;
}
return orifilename;
}

//将Dataset的内容导入SQL
private bool AddDatasetToSQL(DataSet pds)
{
int ic,ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns.Count {
throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{
for (int i = 0;i {
Save(pds.Tables[0].Rows[i][0].ToString(),pds.Tables[0].Rows[i][1].ToString(),
pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(),
pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(),
pds.Tables[0].Rows[i][6].ToString());
}
}
else
{
throw new Exception("导入数据为空！");
}
return true;
}