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                 else
22                 {
23                     if (!lt.Hidden)
24                     {
25                         lbxLists.Items.Add(lt.Title);
26                     }
27                 }
28             }
29             //MessageBox.Show(@"list加载完毕");
30         }
加载Lists

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 = 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                     else
35                     {
36                         if (!field.Hidden)
37                         {
38                             cklbFields.Items.Add(fdInfo);
39                         }
40                     }
41                 }
42                 // MessageBox.Show(@"Field加载完毕");
43             }
44             else
45             {
46                 MessageBox.Show(@"为选择列表名称");
47             }
48 
49         }
加载Fields

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 = 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                             #endregion
106                         }
107                         else if (fieldType == "URL")
108                         {
109                             #region URL
110                             var urlValue = item[fieldName] as FieldUrlValue;
111                             if (urlValue != null) dr[fieldC] = urlValue.Url + "@" + urlValue.Description; 
112                             #endregion
113                         }
114                         else
115                         {
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 = new ArrayList();
 54                     var listId = new ArrayList();
 55 
 56                     var userInField = new ArrayList();
 57                     var userValue = 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 = 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 MultiChoice
103                                 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                                 #endregion
124                             }
125                             else if (fieldType == "URL")
126                             {
127                                 #region URL
128                                 var fv = new FieldUrlValue();
129                                 fv.Url = value.Split('@')[0];
130                                 fv.Description = value.Split('@')[1];
131                                 newItem[inName] = fv;
132                                 #endregion
133                             }
134                             else
135                             {
136                                 newItem[inName] = value;
137                             }
138 
139                         }
140                         #endregion
141                     }
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                     #endregion
175 
176 
177                 }
178                 MessageBox.Show(@"导入完毕！");
179             }
180             else
181             {
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         }
读取GridView数据至List

导入思路：

　　1.检查GridView字段数及字段名称与所选List字段显示名称是否一致；

　　2.循环GridView行数据，循环Row数据中的Column给所选List字段相应字段赋值；

　　3.赋值过程是首先通过新增的方式给非查域项及人员类型的字段赋值，接下来通过ID获取到刚才新增的列表项，以更新该列表项给查域项及人员类型赋值(可更改创建人修改者值)；

遇到的问题：

　　1.直接以新增的方式给查域项及人员类型赋值，其他字段的值都会变成null,只有查域项或人员类型存在值；

　　2.当人员类型为允许多选时，赋值报错。赋值方式为：item[user]="20;#张三;21;#李四;";

　　3.导入数据效率不太高，尤其在包含查阅项或人员类型的情况下，如果数据量特别大可以考虑多线程解决.

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liuzhiye/p/3930536.html