Excel 导入 【含创建文件夹 删除历史文件 重命名文件名 上传服务器 excel简单导入】
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using ExpertsInfo.Data.DAL;
using System.Data.OleDb;
using System.IO;
public partial class T_Bas_Expert_ExpertInser : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 执行导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void drButton_Click(object sender, ImageClickEventArgs e)
{
string filePath = this.FileUpload1.PostedFile.FileName;
if (filePath != "")
{
if (filePath.Contains("xlsx") || filePath.Contains("xls"))//判断文件是否存在
{
InputExcel(filePath);
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script type='text/javascript'> alert(\"导入成功！\"); window.close(); window.opener.searchRefresh(); </script>");
}
else
{
this.RegisterStartupScript("www", "<script language=javascript>alert(\"请上传 xlsx或xls 的文件！\")</script>");
}
}
else
{
this.RegisterStartupScript("www", "<script language=javascript>alert(\"文件名称不能为空！\")</script>");
}
}
/// <summary>
/// 执行execl表导入到数据库
/// </summary>
/// <param name="pPath"></param>
private void InputExcel(string pPath)
{
//文件绝对路径
string FileName = "";
#region 删除今日之前的所有导入的文件夹及文件 并上传至服务器
//创建本日目录 删除上次目录
string filepath = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
string wlpath = this.Server.MapPath("Excel") + "\\" + filepath;
if (Directory.Exists(wlpath))//如果存在 删除之前的文件夹
{
//Directory.Delete(aaaa, false);//如果文件夹中有文件或目录，此处会报错
//Directory.Delete(aaaa, true);//true代表删除文件夹及其里面的子目录和文件
foreach (string d in Directory.GetFileSystemEntries(this.Server.MapPath("Excel")))
{
if (d != wlpath)
{
foreach (string dd in Directory.GetFileSystemEntries(d))
{
if (File.Exists(dd))
File.Delete(dd); //直接删除其中的文件
}
Directory.Delete(d); //直接删除其中的空文件夹
}
}
}
else //如果不存在 创建一个新的文件夹
{
//组装以当前FileUpload控件中文件名临时路径
Directory.CreateDirectory(this.Server.MapPath("Excel") + "\\" + filepath);
}
//获得导入文件的物理路径
string tempPath = this.Server.MapPath("Excel\\") + "\\" + filepath + "\\" + this.FileUpload1.FileName;
//当前上传的文件已存在时即重命名
string path = File.Exists(tempPath) ? this.GetUniqueFileName(tempPath) : tempPath;
this.FileUpload1.PostedFile.SaveAs(path);
FileName = path;
#endregion
#region 服务器上导入到数据库中
//"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
//设置excel连接字符串
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + FileName + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection oleCon = new OleDbConnection(conn);
oleCon.Open();
//ping sql语句
string Sql = "select * from [数据$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[数据$]");
oleCon.Close();
DataTable dt = ds.Tables["[数据$]"];
int count = dt.Rows.Count;
if (count > 0)
{
//循环excel数据 逐条导入
for (int i = 0; i < count; i++)
{
string PKID, WorkProfessional, UName, WorkUnit, Rank, OfficePhone, Phone, email, Postion, Birthday, Sex, Degree, Graduate, StudyProfessional, remark;
PKID = Guid.NewGuid().ToString();
WorkProfessional = dt.Rows[i]["现从事专业"].ToString().Trim();
UName = dt.Rows[i]["姓名"].ToString().Trim();
Sex = dt.Rows[i]["性别"].ToString().Trim();
WorkUnit = dt.Rows[i]["工作单位"].ToString().Trim();
Rank = dt.Rows[i]["职称"].ToString().Trim();
OfficePhone = dt.Rows[i]["办公电话"].ToString().Trim();
Phone = dt.Rows[i]["手机"].ToString().Trim();
email = dt.Rows[i]["Email"].ToString().Trim();
Postion = dt.Rows[i]["职务"].ToString().Trim();
Birthday = dt.Rows[i]["出生年月"].ToString().Trim();
Degree = dt.Rows[i]["学历"].ToString().Trim();
Graduate = dt.Rows[i]["毕业院校"].ToString().Trim();
StudyProfessional = dt.Rows[i]["所学专业"].ToString().Trim();
remark = dt.Rows[i]["备注"].ToString().Trim();
string excelsql = @"INSERT INTO [ExpertsInfo].[dbo].[T_Bas_Expert]
([PKID]
,[WorkProfessional]
,[UName]
,[Sex]
,[WorkUnit]
,[Rank]
,[OfficePhone]
,[Phone]
,[Email]
,[Postion]
,[Birthday]
,[Degree]
,[Graduate]
,[StudyProfessional]
,[Remark])
VALUES
('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')";
//日期格式转换
if (!string.IsNullOrEmpty(Birthday))
{
Birthday = DateTime.Parse(Birthday.ToString()).ToString("yyyy-MM-dd");
}
#region 以现从事单位 姓名 毕业院校为准 判断是否添加
string sqlexcit = "select * from [ExpertsInfo].[dbo].[T_Bas_Expert] where WorkProfessional='" + WorkProfessional + "' and Graduate='" + Graduate + "' and UName='" + UName + "'";
DAT_Bas_Expert dal = new DAT_Bas_Expert();
DataTable dts = dal.DBFactory.GetDataTable(sqlexcit, CommandType.Text);
if (dts.Rows.Count > 0)
continue;
#endregion
excelsql = string.Format(excelsql, PKID, WorkProfessional, UName, Sex, WorkUnit, Rank, OfficePhone, Phone, email, Postion, Birthday, Degree, Graduate, StudyProfessional, remark).ToString();
//导入到数据库
if (UName != "")
dal.Expert_Insert(excelsql);
}
}
#endregion
}
/// <summary>
/// 复制文件时 对复制后的文件进行重命名的方法
/// </summary>
private string GetUniqueFileName(string path)
{
//获取文件名中文件夹路径部分
string dicPath = path.Substring(0, path.LastIndexOf("\\") + 1);
//获取文件名部分
string fileName = path.Substring(path.LastIndexOf("\\") + 1);
//获取文件名前缀部分
string prefix = fileName.Substring(0, fileName.LastIndexOf("."));
//获取文件名后缀部分
string suffix = fileName.Substring(fileName.LastIndexOf("."));
string tempPath = string.Empty;
int i = 1;
while (i > 0)
{
tempPath = string.Format("{0}{1}[{2}]{3}", dicPath, prefix, i, suffix);
if (File.Exists(tempPath))
{
i++;
continue;
}
else
{
break;
}
}
return tempPath;
}
}
using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using ExpertsInfo.Data.DAL; using System.Data.OleDb; using System.IO; public partial class T_Bas_Expert_ExpertInser : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// 执行导入 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void drButton_Click(object sender, ImageClickEventArgs e) { string filePath = this.FileUpload1.PostedFile.FileName; if (filePath != "") { if (filePath.Contains("xlsx") || filePath.Contains("xls"))//判断文件是否存在 { InputExcel(filePath); Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script type='text/javascript'> alert(\"导入成功！\"); window.close(); window.opener.searchRefresh(); </script>"); } else { this.RegisterStartupScript("www", "<script language=javascript>alert(\"请上传 xlsx或xls 的文件！\")</script>"); } } else { this.RegisterStartupScript("www", "<script language=javascript>alert(\"文件名称不能为空！\")</script>"); } } /// <summary> /// 执行execl表导入到数据库 /// </summary> /// <param name="pPath"></param> private void InputExcel(string pPath) { //文件绝对路径 string FileName = ""; #region 删除今日之前的所有导入的文件夹及文件 并上传至服务器 //创建本日目录 删除上次目录 string filepath = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString(); string wlpath = this.Server.MapPath("Excel") + "\\" + filepath; if (Directory.Exists(wlpath))//如果存在 删除之前的文件夹 { //Directory.Delete(aaaa, false);//如果文件夹中有文件或目录，此处会报错 //Directory.Delete(aaaa, true);//true代表删除文件夹及其里面的子目录和文件 foreach (string d in Directory.GetFileSystemEntries(this.Server.MapPath("Excel"))) { if (d != wlpath) { foreach (string dd in Directory.GetFileSystemEntries(d)) { if (File.Exists(dd)) File.Delete(dd); //直接删除其中的文件 } Directory.Delete(d); //直接删除其中的空文件夹 } } } else //如果不存在 创建一个新的文件夹 { //组装以当前FileUpload控件中文件名临时路径 Directory.CreateDirectory(this.Server.MapPath("Excel") + "\\" + filepath); } //获得导入文件的物理路径 string tempPath = this.Server.MapPath("Excel\\") + "\\" + filepath + "\\" + this.FileUpload1.FileName; //当前上传的文件已存在时即重命名 string path = File.Exists(tempPath) ? this.GetUniqueFileName(tempPath) : tempPath; this.FileUpload1.PostedFile.SaveAs(path); FileName = path; #endregion #region 服务器上导入到数据库中 //"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; //设置excel连接字符串 string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + FileName + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; OleDbConnection oleCon = new OleDbConnection(conn); oleCon.Open(); //ping sql语句 string Sql = "select * from [数据$]"; OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon); DataSet ds = new DataSet(); mycommand.Fill(ds, "[数据$]"); oleCon.Close(); DataTable dt = ds.Tables["[数据$]"]; int count = dt.Rows.Count; if (count > 0) { //循环excel数据 逐条导入 for (int i = 0; i < count; i++) { string PKID, WorkProfessional, UName, WorkUnit, Rank, OfficePhone, Phone, email, Postion, Birthday, Sex, Degree, Graduate, StudyProfessional, remark; PKID = Guid.NewGuid().ToString(); WorkProfessional = dt.Rows[i]["现从事专业"].ToString().Trim(); UName = dt.Rows[i]["姓名"].ToString().Trim(); Sex = dt.Rows[i]["性别"].ToString().Trim(); WorkUnit = dt.Rows[i]["工作单位"].ToString().Trim(); Rank = dt.Rows[i]["职称"].ToString().Trim(); OfficePhone = dt.Rows[i]["办公电话"].ToString().Trim(); Phone = dt.Rows[i]["手机"].ToString().Trim(); email = dt.Rows[i]["Email"].ToString().Trim(); Postion = dt.Rows[i]["职务"].ToString().Trim(); Birthday = dt.Rows[i]["出生年月"].ToString().Trim(); Degree = dt.Rows[i]["学历"].ToString().Trim(); Graduate = dt.Rows[i]["毕业院校"].ToString().Trim(); StudyProfessional = dt.Rows[i]["所学专业"].ToString().Trim(); remark = dt.Rows[i]["备注"].ToString().Trim(); string excelsql = @"INSERT INTO [ExpertsInfo].[dbo].[T_Bas_Expert] ([PKID] ,[WorkProfessional] ,[UName] ,[Sex] ,[WorkUnit] ,[Rank] ,[OfficePhone] ,[Phone] ,[Email] ,[Postion] ,[Birthday] ,[Degree] ,[Graduate] ,[StudyProfessional] ,[Remark]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"; //日期格式转换 if (!string.IsNullOrEmpty(Birthday)) { Birthday = DateTime.Parse(Birthday.ToString()).ToString("yyyy-MM-dd"); } #region 以现从事单位 姓名 毕业院校为准 判断是否添加 string sqlexcit = "select * from [ExpertsInfo].[dbo].[T_Bas_Expert] where WorkProfessional='" + WorkProfessional + "' and Graduate='" + Graduate + "' and UName='" + UName + "'"; DAT_Bas_Expert dal = new DAT_Bas_Expert(); DataTable dts = dal.DBFactory.GetDataTable(sqlexcit, CommandType.Text); if (dts.Rows.Count > 0) continue; #endregion excelsql = string.Format(excelsql, PKID, WorkProfessional, UName, Sex, WorkUnit, Rank, OfficePhone, Phone, email, Postion, Birthday, Degree, Graduate, StudyProfessional, remark).ToString(); //导入到数据库 if (UName != "") dal.Expert_Insert(excelsql); } } #endregion } /// <summary> /// 复制文件时 对复制后的文件进行重命名的方法 /// </summary> private string GetUniqueFileName(string path) { //获取文件名中文件夹路径部分 string dicPath = path.Substring(0, path.LastIndexOf("\\") + 1); //获取文件名部分 string fileName = path.Substring(path.LastIndexOf("\\") + 1); //获取文件名前缀部分 string prefix = fileName.Substring(0, fileName.LastIndexOf(".")); //获取文件名后缀部分 string suffix = fileName.Substring(fileName.LastIndexOf(".")); string tempPath = string.Empty; int i = 1; while (i > 0) { tempPath = string.Format("{0}{1}[{2}]{3}", dicPath, prefix, i, suffix); if (File.Exists(tempPath)) { i++; continue; } else { break; } } return tempPath; } }
本人联系qq:873247758 请说明是博客园添加
浙公网安备 33010602011771号