mvc使用Chsword.Excel2Object导出和导入数据

Chsword.Excel2Object导出原理：

将数据写入类似UserOut的类中，然后使用Chsword.Excel2Object中提供的方法直接导出数据。

导出数据控制器程序：

using Chsword.Excel2Object;

public ActionResult export(int role=10)
        {
            List<tb_user> ulist = .Where(c => c.user_role == role).ToList();

            List<UserOut> rtnlist = new List<UserOut>();
            foreach (var item in ulist)
            {
                rtnlist.Add(new UserOut { addtime = item.addtime, id = item.id, username = item.username, truename=item.truename, telephone=item.telephone, qq=item.qq, email=item.email,
                    user_money=item.user_money,blocked_money=item.blocked_money
                });
            }
//导出数据
            var exporter = new ExcelExporter();
            string fileName = "UserOut" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string filePath = Server.MapPath("~/upload/temp/") + fileName;
          
            return File(new FileStream(filePath, FileMode.Open), "application/octet-stream", Server.UrlEncode(fileName));
        }

tb_user实体类程序：

public partial class tb_user
    {

        /// <summary>
        /// 主键
        /// </summary>
        public int id { get; set; }
        
        /// <summary>
        /// 登陆账号 
        /// </summary>
        public string username { get; set; }

        /// <summary>
        /// 登陆密码
        /// </summary>
        public string password { get; set; }

        /// <summary>
        /// 盐值
        /// </summary>
        public string salt { get; set; }

        /// <summary>
        /// 真实姓名
        /// </summary>
        public string truename { get; set; }

        /// <summary>
        /// 联系方式
        /// </summary>
        public string telephone { get; set; }

        /// <summary>
        /// qq号
        /// </summary>
        public string qq { get; set; }

        /// <summary>
        /// 邮箱
        /// </summary>
        public string email { get; set; }

        /// <summary>
        /// 注册时间
        /// </summary>
        public DateTime addtime { get; set; }

        /// <summary>
        /// 会员可用余额
        /// </summary>
        public decimal user_money { get; set; }

        /// <summary>
        /// 会员 头像
        /// </summary>
        public string smallimage { get; set; }

        /// <summary>
        /// 会员状态
        /// 1 正常
        /// 0 待审核 
        /// 2 禁用
        /// </summary>
        public int status { get; set; }

        /// <summary>   
        /// 会员冻结金额
        /// </summary>
        public decimal blocked_money { get; set; }

        /// <summary>
        /// 会员角色 
        /// 10 普通会员 20 经济商会员
        /// </summary>
        public int user_role { get; set; }

        /// <summary>
        /// 提成率 0.003
        /// </summary>
        public decimal Percentage { get; set; }

        /// <summary>
        /// 洲
        /// </summary>
        public string areaP { get; set; }
        /// <summary>
        /// 国家或地区
        /// </summary>
        public string areaC { get; set; }

    }

UserOut导出类：

using Chsword.Excel2Object;
using System;

namespace Web
{
    public class UserOut
    {
        public int id { get; set; }

        /// <summary>
        /// 登陆账号 
        /// </summary>
        [ExcelTitle("用户名称", Order = 1)]
        public string username { get; set; }

        /// <summary>
        /// 真实姓名
        /// </summary>
        [ExcelTitle("真实姓名", Order = 2)]
        public string truename { get; set; }

        /// <summary>
        /// 联系方式
        /// </summary>
        [ExcelTitle("联系方式", Order = 3)]
        public string telephone { get; set; }

        /// <summary>
        /// qq号
        /// </summary>
        [ExcelTitle("证件号", Order =4)]
        public string qq { get; set; }


        /// <summary>
        /// 会员余额
        /// </summary>
        [ExcelTitle("会员余额", Order = 5)]
        public decimal user_money { get; set; }

        /// <summary>   
        /// 冻结金额
        /// </summary>
        [ExcelTitle("冻结金额", Order = 6)]
        public decimal blocked_money { get; set; }

        /// <summary>
        /// 邮箱
        /// </summary>
        [ExcelTitle("邮箱", Order = 7)]
        public string email { get; set; }

        /// <summary>
        /// 注册时间
        /// </summary>
        [ExcelTitle("注册时间", Order = 8)]
        public DateTime addtime { get; set; }

    }
}

 

二、导出数据：

原理和导出数据相反。

 var importer = new ExcelImporter();
                    IEnumerable<UserImpViewModel> result = importer.ExcelToObject<UserImpViewModel>("excel路径");
                    var count = result.Count();

UserImpViewModel类：

 public class UserImpViewModel
    {
        [ExcelTitle("邀请人")]
        public string commandName { get; set; }

        //[ExcelTitle("登录名")]
        //public string username { get; set; }
        /// <summary>
        /// 真实姓名
        /// </summary>
        [ExcelTitle("真实姓名")]
        public string truename { get; set; }

        [ExcelTitle("电话")]
        public string telePhone { get; set; }

        [ExcelTitle("身份证")]
        public string personCard { get; set; }

        [ExcelTitle("邮箱")]
        public string email { get; set; }

    }

注意在使用Chsword.Excel2Object的时候 需要引用其他的dll才能正常使用。