Uploading data to Sharepoint lists

When developing SharePoint solutions, there is often a task, in addition to automatically expanding the site structure (fields, content types, lists), and also to fill it with basic content. These can be mainly reference lists. But, in addition to this, there may be a need to fill them with some kind of test data in order to test the solution, including the load one.

There is a way to fill lists with data, specifying them in an XML file to ListInstance. But this approach is less convenient than that considered in the article. ListDefinitions are not always practical to use for creating lists, besides there may be a need to fill lists that already exist on the site, rather than being expanded from a solution.

So, the idea is in the following logic:

creating tables with data in Excel,
export data from Excel to XML,
adding XML files to a Visual Studio project,
parsing XML file contents and adding data to the lists in the receiver features for activation.
Using Excel is quite convenient. First, the data there is easy to fill; You can create copies of different lines by simply stretching them with the mouse; You can write different formulas to give randomness values ​​for testing purposes, etc. And most importantly, it is in this form that the reference information is usually sent by the customer, that is, it will be much easier for him to prepare information on your request in Excel.
')
Sequencing:

1. Create in Excel-book on a separate sheet a table with data.

2. Create an .xsd file that will represent the data schema in the following format:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Currencies">
<xs:complexType>
<xs:sequence>
<xs:element ref="Currency" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Currency">
<xs:complexType>
<xs:sequence>
<xs:element name="Title" type="xs:string"/>
<xs:element name="Currency_IsNational" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

For convenience, call the root element as your list is called on the site (or as the main part of its address). I called this element Currencies. When parsing XML you can immediately use this name to access the desired list.

Specify the name of the fields in accordance with the InternalName of the fields in the desired list (in the example above, this is Title and Currency_IsNational).

Also do not be embarrassed that the value type is “xs: string”. When parsing XML, you will still receive string values, which will be converted to the correct type when writing to the SPListItem object.

If you need to use more complex types, like User, then specify in the Excel table, for example, the login name, and in the code perform additional conversion in SPFieldUserValue. This will be further demonstrated.

3. Activate the tab "Developer" in Excel.

4. On this tab, click the "Source" button:

5. In the panel that appears, click the "XML Maps" button:

6. Click the Add button and add the .xsd file you created earlier:

7. After adding the scheme, select the table header in Excel and click in the panel on the right in the data tree “Match Element”:

This way you map your data schema to your table. Now it can be exported to XML.

8. Click “Export” and save the .xml file:

The exported file will look something like this:

9. Follow the steps above for all the tables that you are going to upload to the site.

10. Create a Module in your Visual Studio project and add all .xml files with data created this way to it. Call it for example InitialData.

Do not forget to include the module in the feature.

11. When a feature is activated, a folder with the name InitialData will be created on the site, where the .xml files will be added.

12. Next, in the receiver of feature activation, add logic for parsing these xml files and creating items in the necessary lists.

By the time these actions are completed, the lists should already exist on the site with all the required fields. In this case, you can fill with data lists that already contain other data.

The method below goes through all the xml files in the InitialData folder and calls the ImportToSPList method for each.

public void Import()
{
// _siteUrl -
using (SPSite site = new SPSite(_siteUrl))
{
SPWeb web = site.RootWeb;
// _initialDataFolderName - (, )
SPFolder initialDataFolder = web.RootFolder.SubFolders[_initialDataFolderName];

foreach (SPFile file in initialDataFolder.Files)
{
if (file.Name.EndsWith(".xml", StringComparison.CurrentCultureIgnoreCase))
{
using (MemoryStream inStream = new MemoryStream(file.OpenBinary()))
{
using (XmlTextReader reader = new XmlTextReader(inStream))
{
XmlDocument xd = new XmlDocument();
xd.Load(reader);

ImportToSPList(web, xd);
}
}
}
}
}
}

ImportToSPList xml, . , , , SPListItem, SPListItem'.

private void ImportToSPList(SPWeb web, XmlDocument xd)
{
XmlElement root = xd.DocumentElement;
// .
string rootName = root.LocalName;
string listUrl = String.Format("Lists/{0}/AllItems.aspx", rootName);
SPList list = web.GetListFromUrl(listUrl);

foreach (XmlNode item in root.ChildNodes)
{
SPListItem spListItem = list.AddItem();
//
foreach (XmlNode field in item.ChildNodes)
{
//
DataImportSpecialRule specialRule =
_diSpecialRules.FirstOrDefault(x => x.ListUrl == listUrl && x.FieldName == field.LocalName);
if (specialRule == null)
{
// ,
spListItem[field.LocalName] = field.InnerText;
}
else
{
// -
spListItem[field.LocalName] = specialRule.Converter(web, field.InnerText);
}
}
//
spListItem.Update();
}
}

Also note the auxiliary class DataImportSpecialRule, which is needed to convert string values ​​from xml to a more complex type, if required.

Here is the DataImportSpecialRule class itself, which stores the address of the list, the name of the field, and the function to convert:

public delegate object GetTypedValue(SPWeb web, string value);

class DataImportSpecialRule
{
public string ListUrl { get; set; }
public string FieldName { get; set; }
public GetTypedValue Converter { get; set; }
}
The function has the signature described in the GetTypedValue delegate. It accepts SPWeb and a string value as input, and returns an object to be recorded in the SPListItem field.

Initially, you can describe all the rules that should be applied to all your directories, as shown in the example:

_diSpecialRules = new List()
{
new DataImportSpecialRule()
{
ListUrl = "Lists/BusinessBlocks/AllItems.aspx",
FieldName = "BusinessBlock_Group",
Converter = (SPWeb web, string value) =>
{
SPGroup group = web.SiteGroups[value];
return new SPFieldUserValue(web, group.ID, group.Name);
}
}
//
};

, , xml , , SPListItem .

. . , - .

private SPFieldMultiChoiceValue MultiChoiceConverter(SPWeb web, string value)
{
SPFieldMultiChoiceValue fValue = new SPFieldMultiChoiceValue();
foreach (string choice in value.Split(';')) fValue.Add(choice.Trim());
return fValue;
}

, . , .

:

, -, . , , xml , , .
.

SharePoint Channel _diSpecialRules = new List()
{
new DataImportSpecialRule()
{
ListUrl = "Lists/BusinessBlocks/AllItems.aspx",
FieldName = "BusinessBlock_Group",
Converter = (SPWeb web, string value) =>
{
SPGroup group = web.SiteGroups[value];
return new SPFieldUserValue(web, group.ID, group.Name);
}
}
//
};

, , xml , , SPListItem .

. . , - .

private SPFieldMultiChoiceValue MultiChoiceConverter(SPWeb web, string value)
{
SPFieldMultiChoiceValue fValue = new SPFieldMultiChoiceValue();
foreach (string choice in value.Split(';')) fValue.Add(choice.Trim());
return fValue;
}

, . , .

:

, -, . , , xml , , .
.

SharePoint Channel