mvc4.0 excel 导入问题

[jinxiao880]

public ActionResult Importexcel()
{
int validCount = 0;
int invalidCount = 0;

int userid = Util.CToInt(Session["UserID"]);
string cstid = Util.CToStr(System.Web.HttpContext.Current.Session["CustomerID"]);
string tmpTable = "ImportPartsTmp_" + userid.ToString();
List<PartsImport_v> partList = null;
if (Request.Files["FileUpload1"] != null && Request.Files["FileUpload1"].ContentLength > 0)
{
int i_cstid = Util.CToInt(cstid);
string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName);
string filename = cstid + "_" + System.IO.Path.GetFileName(Request.Files["FileUpload1"].FileName);
ViewBag.filename = filename;
string uploadpath = Server.MapPath("~/Content/UploadedFolder");
if (!System.IO.Directory.Exists(uploadpath))
{
System.IO.Directory.CreateDirectory(uploadpath);
}
string path1 = string.Format("{0}/{1}", uploadpath, filename);
if (System.IO.File.Exists(path1))
System.IO.File.Delete(path1);

Request.Files["FileUpload1"].SaveAs(path1);
//string sqlConnectionString = @"Data Source=LEEDHAR2-PC\SQLEXPRESS;Database=Leedhar_Import;Trusted_Connection=true;Persist Security Info=True";

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False";
// SQL Server Connection String
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("Select [P/N#],[ChineseName],[Unit],[Description],[Weight],[ECCN#],[COO],[ModelNo],[UnitPrice],[HTS#],[HS],[Netweight] from [Sheet1$] where len(rtrim([P/N#])) > 0 ", connection);
connection.Open();
//Create DbDataReader to Data Worksheet
OleDbDataReader dr = command.ExecuteReader();

string sql = " if object_id('" + tmpTable + "') is not null begin truncate table " + tmpTable +
" drop table " + tmpTable + " end"+
" Select [P/N#]=PN,[ChineseName],[Unit],[Description],[Weight],[ECCN#]=ECCN,[COO],[ModelNo]=Model,[UnitPrice],[HTS#]=HTS,[HS],[Netweight],Info=Convert(varchar(50),'') " +
" into " + tmpTable + " from parts where 1=2";
Util.DBExecuteNonQuery(_db,sql,null);
// Bulk Copy to SQL Server
System.Data.SqlClient.SqlBulkCopy bulkInsert = new System.Data.SqlClient.SqlBulkCopy(_db.Database.Connection.ConnectionString);
bulkInsert.DestinationTableName = tmpTable;

bulkInsert.WriteToServer(dr);
if (dr != null)
dr.Close();
connection.Close();
}
if (Request.QueryString["page"] != null)
{
partList = _db.Database.SqlQuery<PartsImport_v>(" select PN,[ChineseName],[Unit],[Description],[Weight],ECCN,"+
"[COO],Model,[UnitPrice],HTS,[HS],Netweight,Info from tmpImportList").ToList();
}
else
{
string sql = " if object_id('tmpImportList') is not null begin truncate table tmpImportList " +
" drop table tmpImportList end;" +
" exec sp_GetPartsImportList @TmpTable='" + tmpTable + "',@CstID=" + cstid;
((System.Data.Entity.Infrastructure.IObjectContextAdapter)_db).ObjectContext.CommandTimeout = 180;
partList = _db.Database.SqlQuery<PartsImport_v>(sql).ToList();
}
if (partList != null)
{
validCount = partList.Where(c => c.Info == "valid").Count();
invalidCount = partList.Count() - validCount;
}

ViewBag.status = "1";
ViewBag.total = partList.Count;
ViewBag.validCount = validCount;
ViewBag.inValidCount = invalidCount;
return View("Import", partList);
}

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一直报错：至少一个参数没有被指定值。

excel换成和定义的字段一样的也不行

麻烦各位大大 帮忙看下是哪里错了

给个方向 再弄不好 下周就不用来上班了

[程序员-阿勇]

 
        //导入员工信息
        public void SubmitEmployeeExcel()
        {
            string result = "";
            HttpPostedFile postedFile = System.Web.HttpContext.Current.Request.Files["EmployeeExcel"];
            string name = postedFile.FileName;
            string _tp = System.IO.Path.GetExtension(name);
            #region 查询关联数据
            List<string> liststr = new List<string>();
            liststr.Add("1");//工作地点
            liststr.Add("18");//隶属公司
            liststr.Add("19");//业务单元
            liststr.Add("20");//成本中心
            liststr.Add("21");//员工职能
            liststr.Add("22");//员工职位/层级
            liststr.Add("24");//员工类别1
            liststr.Add("25");//员工类别2
            liststr.Add("26");//员工类别3
            liststr.Add("27");//员工雇佣类型
            liststr.Add("28");//员工变动类型
            liststr.Add("29");//员工工作制度
            liststr.Add("30");//特殊工作证件类型
            liststr.Add("31");//高薪技术职位类型
            liststr.Add("32");//员工合同类型
            liststr.Add("33");//员工合同签署方
            liststr.Add("34");//政治面貌类别
            liststr.Add("35");//学历类别
            liststr.Add("36");//员工户口类型
            liststr.Add("37");//员工紧急联系人与本人关系
            liststr.Add("43");//国籍
            liststr.Add("44");//证件类型
            liststr.Add("45");//婚姻状态
            liststr.Add("46");//个税类型
            liststr.Add("47");//雇佣类型
            SearchCommonCategory searchcommencategory = new SearchCommonCategory
            {
                SearchType = SearchTypeEnum.Total,
                CategoryTypeIDs = string.Join(",", liststr)
            };
            IList<CommonCategoryInfo> commonList = icommonCategory.SearchCommonCategory(searchcommencategory);

            
            SearchDepDetail searchdep=new SearchDepDetail ()
            {
                  SearchType = SearchTypeEnum.Total,
            };
            IList<DepDetailInfo> deplist = idepdetail.SearchDepDetail(searchdep);
            #endregion

            if (_tp == ".xlsx")
            {
                string saveName = DateTime.Now.ToString("yyyyMMddHHmmss") + _tp;
                string path = Server.MapPath("../fileupload/EmployeeInfoExcel/") + saveName;
                postedFile.SaveAs(path);
                DataSet ds = ReadExcel(path);
                if (ds.Tables.Count > 0)
                {
                    result += "ds.Tables.Count=" + ds.Tables.Count;
                    result +="Rows.Count="+ ds.Tables[0].Rows.Count;
                    EmployeeInfo info = null;
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        result += "ds.Tables[0].Rows.RemoveAt(0)=" + ds.Tables[0].Rows[0][0];
                        if (ds.Tables[0].Rows[0][1].ToString() == "员工工号")
                            ds.Tables[0].Rows.RemoveAt(0);                        
                    }                    
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        result += "dr[1]=" + dr[1].ToString();
                        if (!string.IsNullOrEmpty(dr[1].ToString()))
                        {
                            
                            info = iemployee.GetEmployee(dr[1].ToString());
                            info = info ?? new EmployeeInfo();

                            #region Info赋值
                            info.Age = dr[52].ToString();
                            //info.ApplicationDate = "";
                            info.ArchivesAddress = dr[83].ToString();
                            info.AreaCode = dr[10].ToString();
                            info.AreaID = commonList.Where(w => w.CategoryTypeID == 1 && w.CategoryName == dr[12].ToString() && w.CLevel == 3).Select(s => s.CategoryID).FirstOrDefault();                           
                          ...
                            info.EmployeeRelationship = commonList.Where(w => w.CategoryTypeID == 47 && w.CategoryName == dr[101].ToString()).Select(s => s.CategoryID).FirstOrDefault();

                            string email = dr[30].ToString();
                            string username = dr[102].ToString();
                            if (!string.IsNullOrEmpty(username) && username != "0" && username != "#N/A")
                            {
                                if (info.UserID > 0)
                                {
                                    UserInfo userinfo = new UserInfo();
                                    userinfo = iuser.GetUser(info.UserID);
                                    //userinfo.UserName = username;
                                    userinfo.Profile.Email = email;
                                    iuser.UpdateUser(userinfo);
                                }
                                else
                                {
                                    //if (iuser.ExsitUsername("branson#" + username))
                                    //{
                                    //    info.UserID = iuser.GetUser("branson#" + username).UserID;
                                    //}
                                    //else
                                    if (!iuser.ExsitUsername("branson#" + username))
                                        info.UserID = iuser.CreateADUser(username, email, info.EmployeeNameCh,info.Tel,info.Phone,info.CensusRegisterAddress,info.EmployeeCode,info.ZipCode,info.Birthday);
        

                                }
                            }
                            if (info.EmployeeID > 0)
                                iemployee.UpdateEmployee(info);
                            else
                                iemployee.CreateEmployee(info);
                            #endregion
                        }
                        else
                            continue;
                    }
                    Response.Write("{'result':" + result + "}");
                }
            }          
            
        }

[bitsbird]

用epplus吧， 比你自己写的容错好

[正怒月神]

双击，你的错误信息，会跳转到代码错误的行

[Justin-Liu]

会debug不？