将Excel内容导入数据库(ASP.NET/C#)
思路：先上传XLS文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将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(); }其中ImportXlsToData是使用OleDb链接Excel文件并将其数据导入Dataset中；
UpdateXls是上传Xls文件（其实应该写成UpLoadXls......-_-!）;
AddDatasetToSQL是将Dataset导入数据库，其中Save函数是插入一行记录（这里没有给出该函数）。
/// <summary> /// 从Excel提取数据--》Dataset /// </summary> /// <param name="filename">Excel文件路径名</param> private void ImportXlsToData(string fileName) { try { if (fileName == string.Empty) { throw new ArgumentNullException("上传文件失败！"); } // string oleDBConnString = String.Empty; oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"; oleDBConnString += "Data Source="; oleDBConnString += fileName; oleDBConnString += ";Extended Properties=Excel 8.0;"; // OleDbConnection oleDBConn = null; OleDbDataAdapter oleAdMaster = null; DataTable m_tableName=new DataTable(); DataSet ds=new DataSet(); 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 < 7) { 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 < pds.Tables[0].Rows.Count;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; }
