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('@');
26 dt.Columns.Add(item.ToString().Split('@'));
27 // string fieldType = item.ToString().Split('@');
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('@');
51 string fieldType = fd.ToString().Split('@');
52 string fieldC = fd.ToString().Split('@');
53 if (fieldType.Contains("Look"))
54 {
55 #region LookUp
56 var lkFieldLookup = item as FieldLookupValue;
57 if (lkFieldLookup != null) dr = lkFieldLookup.LookupValue;
58 #endregion
59 }
60 else if (fieldType == "User")
61 {
62 #region User
63 var userValue = item as FieldUserValue;
64 if (userValue != null) dr = userValue.LookupId + ";#" + userValue.LookupValue + ";";
65 #endregion
66 }
67 else if (fieldType == "UserMulti")
68 {
69 #region UserMulti
70 if (item.FieldValues != null)
71 {
72 string usersStr = "";
73 var uv = item.FieldValues 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 = usersStr;
81 }
82 #endregion
83 }
84 else if (fieldType == "Choice")
85 {
86 #region Choice
87 if (item.FieldValues != null) dr = item.FieldValues.ToString();
88 #endregion
89 }
90 else if (fieldType == "MultiChoice")
91 {
92 #region MultiChoice
93 if (item.FieldValues != null)
94 {
95 string choice = "";
96
97 var mcStrings = item.FieldValues as String[];
98 if (mcStrings != null)
99 foreach (var s in mcStrings)
100 {
101 choice += s + "@";
102 }
103 dr = choice;
104 }
105 #endregion
106 }
107 else if (fieldType == "URL")
108 {
109 #region URL
110 var urlValue = item as FieldUrlValue;
111 if (urlValue != null) dr = urlValue.Url + "@" + urlValue.Description;
112 #endregion
113 }
114 else
115 {
116 if (item != null) dr = item.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;
11 string[] fieldName = new string;
12 if (dgv1.ColumnCount == cklbFields.CheckedItems.Count)
13 {
14 for (int i = 0; i < dgv1.ColumnCount; i++)
15 {
16 string t1 = dgv1.Columns.Name;
17 //string t2 = t1.Split('@').Trim();
18 column = t1;
19 }
20 for (int j = 0; j < cklbFields.CheckedItems.Count; j++)
21 {
22 fieldName = cklbFields.CheckedItems.ToString().Split('@').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('@');//内部名称
62 string disName = s.ToString().Split('@');//显示名称
63 string fieldType = s.ToString().Split('@');//字段类型
64 var dataGridViewColumn = dgv1.Columns;
65 int index = dataGridViewColumn.Index;
66 string value = dgv1.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('@');
74 string fd = s.ToString().Split('@');//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 = value;
99 }
100 else if (fieldType == "MultiChoice")
101 {
102 #region MultiChoice
103 var t = new string;
104 int tc = 0;
105 for (int k = 0; k < value.Split('@').Count(); k++)
106 {
107 if (!string.IsNullOrEmpty(value.Split('@')))
108 {
109 tc += 1;
110 t = value.Split('@');
111 }
112 }
113 var tcc = new string;
114 for (int j = 0; j < t.Length - 1; j++)
115 {
116 if (t != null)
117 {
118 tcc = t;
119 }
120 }
121 newItem = 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('@');
130 fv.Description = value.Split('@');
131 newItem = fv;
132 #endregion
133 }
134 else
135 {
136 newItem = 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.ToString();
155 string c = lookupField.ToString();
156 string dv = disvalue.ToString();
157 string ttId = listId.ToString();
158 string v = Getlookup(spContext, ttId, c, dv);
159 updateItem = v;
160 }
161 }
162 if (userInField.Count > 0)
163 {
164 for (int j = 0; j < userInField.Count; j++)
165 {
166 string inName = userInField.ToString();
167 string uv = userValue.ToString();
168 updateItem = 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;
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="20;#张三;21;#李四;";
3.导入数据效率不太高，尤其在包含查阅项或人员类型的情况下，如果数据量特别大可以考虑多线程解决.
页:
[1]