首页 > 代码库 > Sharepoint列表数据导入导出工具(支持查阅项及用户类型)
Sharepoint列表数据导入导出工具(支持查阅项及用户类型)
基于.NET客户端对象模型。主要思路：获取选择的列表，读取列表字段，选择需要导出或导入的字段，导出(循环选择的字段动态产生CAML查询语句查出数据存放到GridView中，然后读取数据到Excel),导入数据(读取Excel数据到GridView,循环GridView数据给选择的需要导入的字段赋值) 
主要代码：
1.加载Lists
1 private void btnLoadLists_Click(object sender, EventArgs e) 2 { 3 string spUrl = this.tbxUrl.Text.Trim(); 4 string domain = tbxDomain.Text.Trim(); 5 string uname = tbxUserName.Text.Trim(); 6 string pwd = tbxPasswod.Text.Trim(); 7 8 lbxLists.Items.Clear(); 9 var spContext = new ClientContext(spUrl);10 var w = spContext.Web;11 var cc = new NetworkCredential(uname, pwd, domain);12 spContext.Credentials = cc;13 var lts = spContext.LoadQuery(w.Lists);14 spContext.ExecuteQuery();15 foreach (var lt in lts)16 {17 if (cbxHidden.Checked)18 {19 lbxLists.Items.Add(lt.Title);20 }21 else22 {23 if (!lt.Hidden)24 {25 lbxLists.Items.Add(lt.Title);26 }27 }28 }29 //MessageBox.Show(@"list加载完毕");30 }
2.加载选择的lists字段
1 private void btnLoadField_Click(object sender, EventArgs e) 2 { 3 string spUrl = this.tbxUrl.Text.Trim(); 4 string domain = tbxDomain.Text.Trim(); 5 string uname = tbxUserName.Text.Trim(); 6 string pwd = tbxPasswod.Text.Trim(); 7 8 string listTitle = lbxLists.SelectedItem.ToString(); 9 if (!string.IsNullOrEmpty(listTitle))10 {11 cklbFields.Items.Clear();12 var spContext = new ClientContext(spUrl);13 Web w = spContext.Web;14 var cc = new NetworkCredential(uname, pwd, domain);15 spContext.Credentials = cc;16 var listFields = spContext.LoadQuery(w.Lists.GetByTitle(listTitle).Fields);17 spContext.ExecuteQuery();18 foreach (var field in listFields)19 {20 string fdInfo = field.Title + "@" + field.InternalName + "@" + field.TypeAsString;//Field:显示名称@内部名称@字段类型21 if (field.TypeAsString.Contains("ook"))22 {23 FieldLookup lvValue = http://www.mamicode.com/field as FieldLookup;24 if (lvValue != null)25 {26 string listId = lvValue.LookupList;27 fdInfo += "@" + listId + "@" + lvValue.LookupField;//Field:显示名称@内部名称@字段类型@查阅项源list GUID@查阅项源字段内部名称28 }29 }30 if (cbxHidden.Checked)31 {32 cklbFields.Items.Add(fdInfo);33 }34 else35 {36 if (!field.Hidden)37 {38 cklbFields.Items.Add(fdInfo);39 }40 }41 }42 // MessageBox.Show(@"Field加载完毕");43 }44 else45 {46 MessageBox.Show(@"为选择列表名称");47 }48 49 }
3.读取所选字段list数据至GridView
1 private void btnGetData_Click(object sender, EventArgs e) 2 { 3 string spUrl = this.tbxUrl.Text.Trim(); 4 string domain = tbxDomain.Text.Trim(); 5 string uname = tbxUserName.Text.Trim(); 6 string pwd = tbxPasswod.Text.Trim(); 7 8 var spContext = new ClientContext(spUrl); 9 Web w = spContext.Web; 10 var cc = new NetworkCredential(uname, pwd, domain); 11 spContext.Credentials = cc; 12 DataTable dt = Caml(spContext); 13 dgv1.DataSource = dt; 14 } 15 16 private DataTable Caml(ClientContext spContext) 17 { 18 var dt = new DataTable(); 19 string caml = ""; 20 if (cklbFields.CheckedItems.Count > 0) 21 { 22 foreach (var item in cklbFields.CheckedItems) 23 { 24 25 string fieldName = item.ToString().Split(‘@‘)[1]; 26 dt.Columns.Add(item.ToString().Split(‘@‘)[0]); 27 // string fieldType = item.ToString().Split(‘@‘)[2]; 28 caml += "<FieldRef Name=‘" + fieldName + "‘/>"; 29 } 30 caml = @"<View><ViewFields>" + caml + "</ViewFields></View>"; 31 32 } 33 34 var spList = spContext.Web.Lists.GetByTitle(lbxLists.SelectedItem.ToString()); 35 spContext.Load(spList); 36 spContext.ExecuteQuery(); 37 if (spList != null && spList.ItemCount > 0) 38 { 39 var camlQuery = new CamlQuery(); 40 camlQuery.ViewXml = caml; 41 ListItemCollection listItems = spList.GetItems(camlQuery); 42 spContext.Load(listItems); 43 spContext.ExecuteQuery(); 44 45 foreach (var item in listItems) 46 { 47 DataRow dr = dt.NewRow(); 48 foreach (var fd in cklbFields.CheckedItems) 49 { 50 string fieldName = fd.ToString().Split(‘@‘)[1]; 51 string fieldType = fd.ToString().Split(‘@‘)[2]; 52 string fieldC = fd.ToString().Split(‘@‘)[0]; 53 if (fieldType.Contains("Look")) 54 { 55 #region LookUp 56 var lkFieldLookup = item[fieldName] as FieldLookupValue; 57 if (lkFieldLookup != null) dr[fieldC] = lkFieldLookup.LookupValue; 58 #endregion 59 } 60 else if (fieldType == "User") 61 { 62 #region User 63 var userValue = http://www.mamicode.com/item[fieldName] as FieldUserValue; 64 if (userValue != null) dr[fieldC] = userValue.LookupId + ";#" + userValue.LookupValue + ";"; 65 #endregion 66 } 67 else if (fieldType == "UserMulti") 68 { 69 #region UserMulti 70 if (item.FieldValues[fieldName] != null) 71 { 72 string usersStr = ""; 73 var uv = item.FieldValues[fieldName] as FieldUserValue[]; 74 if (uv != null) 75 foreach (var userValue in uv) 76 { 77 usersStr = userValue.LookupId + ";#" + userValue.LookupValue + ";"; 78 //usersStr += userValue.LookupValue + "@"; 79 } 80 dr[fieldC] = usersStr; 81 } 82 #endregion 83 } 84 else if (fieldType == "Choice") 85 { 86 #region Choice 87 if (item.FieldValues[fieldName] != null) dr[fieldC] = item.FieldValues[fieldName].ToString(); 88 #endregion 89 } 90 else if (fieldType == "MultiChoice") 91 { 92 #region MultiChoice 93 if (item.FieldValues[fieldName] != null) 94 { 95 string choice = ""; 96 97 var mcStrings = item.FieldValues[fieldName] as String[]; 98 if (mcStrings != null) 99 foreach (var s in mcStrings)100 {101 choice += s + "@";102 }103 dr[fieldC] = choice;104 } 105 #endregion106 }107 else if (fieldType == "URL")108 {109 #region URL110 var urlValue = http://www.mamicode.com/item[fieldName] as FieldUrlValue;111 if (urlValue != null) dr[fieldC] = urlValue.Url + "@" + urlValue.Description; 112 #endregion113 }114 else115 {116 if (item[fieldName] != null) dr[fieldC] = item[fieldName].ToString();117 }118 }119 dt.Rows.Add(dr);120 }121 }122 123 return dt;124 }
4.读取GridView数据导入到list
1 private void btnImportExcel_Click(object sender, EventArgs e) 2 { 3 string spUrl = this.tbxUrl.Text.Trim(); 4 string domain = tbxDomain.Text.Trim(); 5 string uname = tbxUserName.Text.Trim(); 6 string pwd = tbxPasswod.Text.Trim(); 7 string listName = lbxLists.SelectedItem.ToString(); 8 bool checking = true; 9 #region 检查数据是否一致 10 string[] column = new string[dgv1.ColumnCount]; 11 string[] fieldName = new string[dgv1.ColumnCount]; 12 if (dgv1.ColumnCount == cklbFields.CheckedItems.Count) 13 { 14 for (int i = 0; i < dgv1.ColumnCount; i++) 15 { 16 string t1 = dgv1.Columns[i].Name; 17 //string t2 = t1.Split(‘@‘)[0].Trim(); 18 column[i] = t1; 19 } 20 for (int j = 0; j < cklbFields.CheckedItems.Count; j++) 21 { 22 fieldName[j] = cklbFields.CheckedItems[j].ToString().Split(‘@‘)[0].Trim(); 23 } 24 foreach (var s1 in column) 25 { 26 checking = fieldName.Contains(s1); 27 } 28 } 29 else 30 { 31 checking = false; 32 33 } 34 #endregion 35 36 if (checking) 37 { 38 var spContext = new ClientContext(spUrl); 39 Web w = spContext.Web; 40 var cc = new NetworkCredential(uname, pwd, domain); 41 spContext.Credentials = cc; 42 var spList = spContext.Web.Lists.GetByTitle(listName); 43 spContext.Load(spList); 44 spContext.ExecuteQuery(); 45 46 var itemCreateInfo = new ListItemCreationInformation(); 47 for (int i = 0; i < dgv1.Rows.Count - 1; i++) 48 { 49 ListItem newItem = spList.AddItem(itemCreateInfo); 50 51 var lookupField = new ArrayList(); 52 var lpField = new ArrayList();//字段名 53 var disvalue = http://www.mamicode.com/new ArrayList(); 54 var listId = new ArrayList(); 55 56 var userInField = new ArrayList(); 57 var userValue = http://www.mamicode.com/new ArrayList(); 58 59 foreach (var s in cklbFields.CheckedItems) 60 { 61 string inName = s.ToString().Split(‘@‘)[1];//内部名称 62 string disName = s.ToString().Split(‘@‘)[0];//显示名称 63 string fieldType = s.ToString().Split(‘@‘)[2];//字段类型 64 var dataGridViewColumn = dgv1.Columns[disName]; 65 int index = dataGridViewColumn.Index; 66 string value =http://www.mamicode.com/ dgv1[index, i].Value.ToString(); 67 #region MyRegion 68 if (!string.IsNullOrEmpty(value)) 69 { 70 if (fieldType.Contains("Look")) 71 { 72 #region Lookup 73 string tid = s.ToString().Split(‘@‘)[3]; 74 string fd = s.ToString().Split(‘@‘)[4];//outlookfield 75 lpField.Add(inName); 76 lookupField.Add(fd); 77 disvalue.Add(value); 78 listId.Add(tid); 79 #endregion 80 } 81 else if (fieldType == "User") 82 { 83 #region User 84 userInField.Add(inName); 85 userValue.Add(value); 86 //http://stackoverflow.com/questions/9406018/add-users-to-usermulti-field-type-using-client-object-model 87 #endregion 88 } 89 else if (fieldType == "UserMulti") 90 { 91 #region UserMulti 92 userInField.Add(inName); 93 userValue.Add(value); 94 #endregion 95 } 96 else if (fieldType == "Choice") 97 { 98 newItem[inName] = value; 99 }100 else if (fieldType == "MultiChoice")101 {102 #region MultiChoice103 var t = new string[value.Split(‘@‘).Count()];104 int tc = 0;105 for (int k = 0; k < value.Split(‘@‘).Count(); k++)106 {107 if (!string.IsNullOrEmpty(value.Split(‘@‘)[k]))108 {109 tc += 1;110 t[k] = value.Split(‘@‘)[k];111 }112 }113 var tcc = new string[tc];114 for (int j = 0; j < t.Length - 1; j++)115 {116 if (t[j] != null)117 {118 tcc[j] = t[j];119 }120 }121 newItem[inName] = tcc.Count() > 0 ? tcc : null;122 //http://www.learningsharepoint.com/2010/11/21/get-values-from-multichoice-column-client-object-model-sharepoint-2010/123 #endregion124 }125 else if (fieldType == "URL")126 {127 #region URL128 var fv = new FieldUrlValue();129 fv.Url = value.Split(‘@‘)[0];130 fv.Description = value.Split(‘@‘)[1];131 newItem[inName] = fv;132 #endregion133 }134 else135 {136 newItem[inName] = value;137 }138 139 }140 #endregion141 }142 newItem.Update();143 spContext.ExecuteQuery();144 145 #region Update 查阅项与用户字段146 if (lpField.Count > 0 || userInField.Count > 0)147 {148 int ltId = newItem.Id;149 ListItem updateItem = spList.GetItemById(ltId);150 if (lpField.Count > 0)151 {152 for (int j = 0; j < lookupField.Count; j++)153 {154 string lpf = lpField[j].ToString();155 string c = lookupField[j].ToString();156 string dv = disvalue[j].ToString();157 string ttId = listId[j].ToString();158 string v = Getlookup(spContext, ttId, c, dv);159 updateItem[lpf] = v;160 }161 }162 if (userInField.Count > 0)163 {164 for (int j = 0; j < userInField.Count; j++)165 {166 string inName = userInField[j].ToString();167 string uv = userValue[j].ToString();168 updateItem[inName] = uv;169 }170 }171 updateItem.Update();172 spContext.ExecuteQuery();173 } 174 #endregion175 176 177 }178 MessageBox.Show(@"导入完毕！");179 }180 else181 {182 MessageBox.Show(@"Excel数据列数与选择的字段数不一致");183 }184 185 }186 187 /// <summary>188 /// 取得查域项值189 /// </summary>190 /// <param name="ctxClientContext">客户端上下文对象</param>191 /// <param name="listId">列表ID</param>192 /// <param name="fd">查域项字段</param>193 /// <param name="lookupValue">查域项Value</param>194 /// <returns></returns>195 private static string Getlookup(ClientContext ctxClientContext, string listId, string fd, string lookupValue)196 {197 string lookup = "";198 List toList = ctxClientContext.Web.Lists.GetById(new Guid(listId));199 ctxClientContext.Load(toList);200 ctxClientContext.ExecuteQuery();201 CamlQuery cqQuery = new CamlQuery();202 cqQuery.ViewXml = @"<View> 203 <Query> 204 <Where><Eq><FieldRef Name=‘" + fd + @"‘ /><Value Type=‘Text‘>" + lookupValue + @"</Value></Eq></Where> 205 </Query> 206 </View>";207 ListItemCollection ltcCollection = toList.GetItems(cqQuery);208 ctxClientContext.Load(ltcCollection);209 ctxClientContext.ExecuteQuery();210 if (ltcCollection.Count > 0)211 {212 ListItem im = ltcCollection[0];213 lookup = im.Id.ToString(CultureInfo.InvariantCulture);214 }215 return lookup + ";#" + lookupValue+";";216 }
导入思路：
1.检查GridView字段数及字段名称与所选List字段显示名称是否一致；
2.循环GridView行数据，循环Row数据中的Column给所选List字段相应字段赋值；
3.赋值过程是首先通过新增的方式给非查域项及人员类型的字段赋值，接下来通过ID获取到刚才新增的列表项，以更新该列表项给查域项及人员类型赋值(可更改创建人修改者值)；
遇到的问题：
1.直接以新增的方式给查域项及人员类型赋值，其他字段的值都会变成null,只有查域项或人员类型存在值；
2.当人员类型为允许多选时，赋值报错。赋值方式为：item[user]="20;#张三;21;#李四;";
3.导入数据效率不太高，尤其在包含查阅项或人员类型的情况下，如果数据量特别大可以考虑多线程解决.
Sharepoint列表数据导入导出工具(支持查阅项及用户类型)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。