Excel 导入导出 .Net
一.excel的导出功能
public partial class OutExcel : System.Web.UI.Page
{
UsersOperate useroperate = new UsersOperate();
string strWhere = " and UserStatus=1";
int iCurrentPage = 1;
protected void Page_Load(object sender, EventArgs e)
{
StreamWriter sw = null;
try
{
string filePath = Server.MapPath("/csv");
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
string name = filePath + "/"+DateTime.Now.ToString("yyyy-MM-dd")+".csv";
if (File.Exists(name))
{
File.Delete(name);
}
sw = new StreamWriter(name, true, System.Text.Encoding.UTF8, 200);
StringBuilder sbTitle = new StringBuilder();
sbTitle.Append("用户名称,密码,角色名");
sw.WriteLine(sbTitle.ToString());
IList<UsersInfo> userslist = useroperate.GetList(10000, iCurrentPage, strWhere, "UserId asc", "UserId desc"); //获取数据信息
RolesInfo rinfo = new RolesInfo();
RolesOperate rop = new RolesOperate();
foreach (UsersInfo UInfo in userslist)
{
StringBuilder sbContent = new StringBuilder();
string str = string.Empty;
rinfo = rop.GetObject(UInfo.RoleId);
if (rinfo.Id == Convert.ToInt32(UInfo.RoleId))
{
str = rinfo.RoleName;
}
sbContent.Append(UInfo.UserLogin + "," + UInfo.UserPassword + "," + str);
sw.WriteLine(sbContent.ToString());
}
sw.Close();
if (System.IO.File.Exists(name))
{
string strFileName = System.IO.Path.GetFileName(name);
System.IO.FileInfo fileInfo = new System.IO.FileInfo(name);
long FileSize = fileInfo.Length;
Page.Response.ContentType = "APPLICATION/OCTET-STREAM";
Page.Response.AddHeader("Content-length", FileSize.ToString());
Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
Page.Response.WriteFile(name);
Response.Flush();
Response.End();
}
else
{
Response.Write("文件不存在!");
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
sw.Close();
}
}
}
2.excel导入数据库中
/// <summary>
/// 导入事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Btn_OK_Click(object sender, EventArgs e)
{
#region
if (FileUpload1.HasFile)
{
if (FileUpload1.PostedFile.ContentLength < 1000000000)
{
string isxls = "";
string fistName = "";
string savePath = "";
string strdt = "";
DataTable dt;
try
{
isxls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
fistName = System.IO.Path.GetFileName(FileUpload1.FileName).ToString();
if (isxls != ".csv") { Label1.Text = "出现异常:请选择正确的CSV文件!"; return; } //检查文件是不是Excel
//文件名组装
strdt = DateTime.Now.ToString("yyyyMMDDHHmmss") + ".csv";
//获取路径
savePath = Server.MapPath("/Upload/" + strdt);
//文件移动到指定位置
FileUpload1.PostedFile.SaveAs(savePath);
}
catch
{
Label1.Text = "出现异常:文件上传失败!";
}
try
{
dt=ReadCsvFileToTable(true, ',', savePath);
DataRow[] dr =dt.Select();
int rownum = dt.Rows.Count;
if (rownum == 0)
{
Label1.Text = "出现异常:CSV表为空,无数据!";
}
else
{ //数据导入
for (int i = 0; i < dr.Length; i++)
{
if (GetUsers(dt))
{
Users_ExcelIn(dr[i]);///调用用户导入方法
}
else
{
Label1.Text = "出现异常:请选择正确的用户资料模板导入";
return;
}
}
}
}
catch (Exception ex) { Label1.Text = ex.Message; }
}
else
{
Label1.Text = "出现异常:文件过大!";
}
}
else
{
Label1.Text = "出现异常:没有选择文件!";
}
#endregion
}
#region 操作数据
/// <summary>
/// 导入数据信息过滤
/// </summary>
/// <param name="dr"></param>
public void Users_ExcelIn(DataRow dr)
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()) && !string.IsNullOrEmpty(dr[2].ToString()))
{
UsersOperate uo = new UsersOperate();
UsersInfo usinfo = new UsersInfo();
RolesOperate rOperate = new RolesOperate();
IList<RolesInfo> rList = rOperate.GetListCheckNull(1, 1, " and rolename='" + dr[2] + "' and RoleType<>0", "roleid desc", "roleid asc");
if (rList.Count > 0)
{
usinfo.UserLogin = System.Convert.ToString(dr[0]);
UsersInfo usinfois = new UsersInfo();
usinfois = uo.GetisName(usinfo.UserLogin);
if (usinfois == null)
{
if (dr[1].ToString().Length >= 6)
{
UsersInfo usinfos = new UsersInfo();
usinfos.UserLogin = System.Convert.ToString(dr[0]);
usinfos.UserPassword = System.Convert.ToString(dr[1]);
usinfos.UserEmail = "";
usinfos.UserStatus = 1;
usinfos.RoleId = rList[0].Id;
usinfos.UserAdder = GetUserSession().Id;
usinfos.UserAdded = DateTime.Now;
usinfos.UserIsLogin = 0;
if (uo.Insert(usinfos) > 0)
{
MessageInfo = "用户导入成功";
Label1.Text = "" + MessageInfo + "";
}
else
{
MessageInfo = "出现异常:用户导入失败";
Label1.Text = "" + MessageInfo + "";
}
}
else
{
Label1.Text = "出现异常:用户密码长度少于六位!";
}
}
else
{
Label1.Text = "出现异常:存在重复用户,导入重复的一条数据";
}
}
else
{
Label1.Text = "出现异常:角色名没有数据!";
}
}
else
{
Label1.Text = "出现异常:数据不能为空!";
}
}
/// <summary>
/// 判断模版格式
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public bool GetUsers(DataTable dt)
{
if (dt.Columns[0].ColumnName == "用户名称" &&
dt.Columns[1].ColumnName == "密码" &&
dt.Columns[2].ColumnName == "角色名")
{
return true;
}
else
{
return false;
}
}
#endregion
/// <summary>
/// 取消
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Btn_cl_Click(object sender, EventArgs e)
{
Response.Redirect("../Manager/UserMain.aspx");
}
public DataTable ReadCsvFileToTable(bool HeadYes, char span, string filename)
{
//文件路径和文件名
string files = filename;
DataTable dt = new DataTable();
StreamReader fileReader = new StreamReader(files, Encoding.Default);
try
{
//是否为第一行(如果HeadYes为TRUE,则第一行为标题行)
int lsi = 0;
//列之间的分隔符
char cv = span;
while (fileReader.EndOfStream == false)
{
string line = fileReader.ReadLine();
string[] y = line.Split(cv);
//第一行为标题行
if (HeadYes == true)
{
//第一行
if (lsi == 0)
{
for (int i = 0; i < y.Length; i++)
{
dt.Columns.Add(y[i].Trim().ToString());
}
lsi++;
}
//从第二列开始为数据列
else
{
DataRow dr = dt.NewRow();
for (int i = 0; i < y.Length; i++)
{
dr[i] = y[i].Trim();
}
dt.Rows.Add(dr);
}
}
//第一行不为标题行
else
{
if (lsi == 0)
{
for (int i = 0; i < y.Length; i++)
{
dt.Columns.Add("Col" + i.ToString());
}
lsi++;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < y.Length; i++)
{
dr[i] = y[i].Trim();
}
dt.Rows.Add(dr);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
fileReader.Close();
fileReader.Dispose();
}
return dt;
}