导入excel数据到数据库
2011-04-06 15:41 suzh 阅读(618) 评论(0) 编辑 收藏 举报 /*方法一 */
/// <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);
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
//System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
//string strCom = "SELECT * FROM [Sheet1$]";
//Conn.Open();
//System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
//DataSet dss = new DataSet();
//myCommand.Fill(dss, "[Sheet1$]");
//Conn.Close();
//return ds;
}
catch (Exception ex)
{
throw ex;
}
}
//上传Excel文件
private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string path = "";
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++)
{
//SaveData(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;
}
public bool SaveData()
{
return true;
}
/*=============================================================================================================================*/
/*方法二 */
//1将需要导入的excel文件上传到服务器
public string upLoadExcelFile(System.Web.UI.HtmlControls.HtmlInputFile fileUp)
{
string filePath = "",fileExtName="";
string myPath = "";
string FullName = "";//保存文件的完整路径
if(fileUp.PostedFile.FileName!="")
{
//取得文件路径
filePath = fileUp.PostedFile.FileName;
//取得文件扩展名
fileExtName = filePath.Substring(filePath.LastIndexOf('.')+1);
//判断是否excel文件
if (fileExtName.ToLower() == "xls")
{
try
{
//取得与web服务器上指定的虚拟路径相对应的物理路径
myPath = HttpContext.Current.Server.MapPath("upFiles/");
//取得当前时间，已时分秒来命名，以免重复
string strDateName = DateTime.Now.ToString("hhmmss");
//保存上传文件到指定目录
FullName = myPath + strDateName + "." + fileExtName;
fileUp.PostedFile.SaveAs(FullName);
}
catch (Exception ex)
{
}
}
else
{
return "文件格式不正确";
}
}
return FullName;
}
//读取Excel到DataSet
public void readExcelToDataSet(string FullName)
{
string strConn = "provider=Micorsoft.Jet.OLEDB.4.0;Data Source=" + FullName+";Extended Properties=Excel8.0;" ;
OleDbConnection connxls = new OleDbConnection(strConn);
if (connxls.State.ToString() == "Closed")
{
connxls.Open();
}
string sqlExcel = "select * from [Sheet1$]";
OleDbDataAdapter myDa = new OleDbDataAdapter(sqlExcel,connxls);
DataSet myDS = new DataSet();
myDa.Fill(myDS);
if (myDS.Tables[0].Rows.Count > 0)
{
//写入数据库
InsertExcelData(myDS);
}
else
{
//没有数据
}
}
//将数据导入到数据库
public void InsertExcelData(DataSet ds)
{
string strSql = "";
string sqlConn = "Server=(local);DataBase=myDB;user id=sa pwd=sa";
SqlConnection conn = new SqlConnection(sqlConn);
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand myCmd = new SqlCommand();
//将数据逐行写入数据库
for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
{
strSql = "";
myCmd.Connection = conn;
myCmd.CommandText = strSql;
try
{
myCmd.ExecuteNonQuery();
}
catch(Exception exce)
{
}
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
