If you read my last article on exporting SharePoint data to Excel, you will remember that getting data from SharePoint to Excel can be fairly simple. It may seem reasonable that getting data from Excel to SharePoint is equally simple. After all, you have a 'Import Spreadsheet' thing on the Create page, right? They must be doing something. And if Microsoft can do it, how hard can it be?
Turns out, its not simple at all. The way Microsoft does this is to open Excel itself using a, ActiveX control called SpreadsheetLauncher. This in turn allows you to select a range of cells, named ranges or tables. The selected cells are then returned to SharePoint and a list is created.
Granted, the Import does a very good job at creating the list and guessing what we want. For instance, if you have only single lines of text in a column, SharePoint makes that column into a single line of text field in SharePoint. However, if you have only a single row of multi line text anywhere in the column, SharePoint will make a multi line SharePoint text field. If you format a column as a date field, SharePoint will pick up on that. It will even pick up some formatting of the date, such as having a date-only format as well as date+time format, both of which will produce their respective SharePoint counterparts. Take a look at this file for some Spreadsheet import challenges for SharePoint.
However, for all its glory and magnificence, the importer does have some flaws. First of all, it does require the you have Excel installed on the client. This may not seem like such a big issue, I mean, if you have created a spreadsheet you more than likely also have Excel, right? Well, not so. More and more products, including SharePoint, are able to produce OOXML Spreadsheet files. You may edit or create the files in numerous ways, or obtain the file in numerous ways. It is not even guaranteed that you will be working on a client, you may have a situation in which you get the file from a business partner web service somewhere, sent directly into SharePoint, and no amount of begging will get your partner to install Excel on his server, not to mention script it to click the correct buttons to select the right ranges.
Second quirk, well, as good as it is, automatic field type selection has some flaws. Format the column as time and SharePoint, having no concept of a time-only field, will not have a clue what to do, and default back to single line of text. Any 'special' formatting in Excel will be completely ignored and added a single line of text. Need a special decimal count? Well, good luck, let me know if you find something, SharePoint will leave this at 'Automatic' no matter what you select.
And in any case, what about non-default field types? After all, a great feature of SharePoint is the ability to create your own field types to hold, with the exception of free sex and beer, anything you like. Want to use a custom field type in your import? Sorry, no can do. Simon says, stick with the defaults.
If you don't need custom fields and are happy with the guess work that the automatic process does, then great. Go with the import spreadsheet option. I use it a lot, it works 95% of the time.
So, why this article? Well, I want 100%. Or actually, I usually don't but at times I need to make something that SharePoint simply does not include and I need to make my own import function. I'll get you started as well.
First some gotchas. I will be using ExcelPackage again because I am too lazy to write all this stuff myself. Using ExcelPackage, though, has some major drawbacks when it comes to column types. The way Excel handles data types is by applying styles. ExcelPackage, however, has extremely limited support for these styles, in fact, the only way to set styles is to already have a style defined in the Excel file and then applying that style. These is no support for manipulating the styles or reading any configuration about the styles. One would need to work with the file directly, for instance through the OpenXML SDK. This still is tedious work; in the current version of OpenXML SDK there is still a lot of XML handcrafting to be done to create and manipulate styles.
But hey, who cares, we don't want to mimic the existing stuff, right? We want to make our own. So, let's get started.
First our scope. I want to show you, using as simple an example as possible, how to import a spreadsheet into a SharePoint list, allowing you to select how the list will be generated. This will not be production code, it is meant to illustrate and get your imagination going. If you want production code, hire me.
So, what I'll do is create a simple console application that uses ExcelPackage to read the contents of a sample spreadsheet file and for each column allow you to select, from any existing field type in your site collection, what field will be created. How you would actually implement this in production is up to you, perhaps you want to do this from a SharePoint application page, perhaps you want to create an XML-file to hold the configuration, perhaps you want to guess. Do what thou willt.
Now, start up Visual Studio and create a new .Net Console Application. I'm using C#, you use whatever rocks your boat. Pick up a copy of ExcelPackage from CodePlex, add references to Windows.SharePoint.dll and ExcelPackage.dll just as we did in the last article. Add using statements if you like, it makes the code a lot more readable. It should look something along the lines of:
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4
5 using System.IO;
6 using OfficeOpenXml;
7 using Microsoft.SharePoint;
8
9 namespace ExcelImport
10 { 11 class Program
12 { 13 static void Main(string[] args)
14 {Further, we want to open up the Excel file to see what's inside. Feel free to not hard-code the file path, that is only stupid. Again, illustrative purposes. I'll stop saying that, so every time something seems extremely stupid, assume it is for illustrative purposes.
15 // Open xlsx-file.
16 FileInfo fi = new FileInfo(@"c:\Files\toImport.xlsx");
17 if (! fi.Exists)
18 { 19 throw new Exception("File does not exist"); 20 }
21
22 ExcelPackage excel = new ExcelPackage(fi);
23 ExcelWorksheet ws = excel.Workbook.Worksheets[1];
24
25 int maxColumn = 0; // Keeping track of how many columns exist
With that in place, it is time to get serious.
First we need a SharePoint site and a web to work with, as well as finding the available field types for our columns:
27 using (SPSite site = new SPSite("http://lab-s02:12000/")) 28 { 29 using (SPWeb web = site.OpenWeb())
30 { 31 // Get available field type definitions
32 SPFieldTypeDefinitionCollection fieldtypedefs = web.FieldTypeDefinitionCollection;
33
We might as well make ourselves a few objects while we're at it:
34 // Have someplace to store our selections
35 Dictionary<string, SPFieldTypeDefinition> colTypes = new Dictionary<string,SPFieldTypeDefinition>();
36 Dictionary<int, string> colNames = new Dictionary<int, string>();
37
38 Boolean ColumnEmpty = false; // Just to see if we are at the end of the columns
39 int ColumnCounter = 1; // Keeping track of which column we are processing.
40
Ok, now comes the processing. We want to iterate through the columns of the Excel sheet first. For each column we should present the user with all available SPFieldTypeDefinitions that are available and then store their selection for that column. I have commented the code online so I wont have to write further comments for each of the lines.
41 while (!ColumnEmpty)
42 { 43 ExcelColumn col = ws.Column(ColumnCounter);
44 if (!col.Hidden) // Skip hidden columns
45 { 46 ExcelCell cell = ws.Cell(1, ColumnCounter); // Assume headings in first row
47 string ColumnTitle = cell.Value;
48 if (string.IsNullOrEmpty(ColumnTitle))
49 { 50 ColumnEmpty = true; // Stop processing if we found an empty column header
51 }
52 else
53 { 54 Boolean validSelection = false; // just to get some simple input validation
55
56 while (!validSelection)
57 { 58 int selection; // to store our parsed selection
59 int selectionCounter = 1; // to display a number for users to select
60 string selectionString; // input frmo user
61
62 // Communicate with the user
63 Console.Clear();
64 Console.WriteLine(string.Format("For column {0}, which field type would you like?", ColumnTitle)); 65
66 foreach (SPFieldTypeDefinition fieldtypedef in fieldtypedefs)
67 { 68 Console.WriteLine(string.Format("{0}) : {1}", selectionCounter++, fieldtypedef.TypeDisplayName)); 69 }
70 Console.Write("Enter your selection: "); 71 selectionString = Console.ReadLine();
72
73 // Verify that we have a valid selection
74 if (int.TryParse(selectionString, out selection))
75 { 76 if (selection > 0 && selection < fieldtypedefs.Count)
77 { 78 validSelection = true;
79 colTypes[ColumnTitle] = fieldtypedefs[selection-1]; // Reduce by one since collection is zero-based
80 colNames[ColumnCounter-1] = ColumnTitle;
81 }
82 }
83
84
85 }
86 maxColumn++; // Update highest number of columns...
87 ColumnCounter++; // and advance...
88 }
89 }
90
91 }
That was quite a mouthful, but don't worry, the basic idea should be simple, we really just need some way of finding out what field type the user wants for each Excel column.
Now to the SharePoint stuff. We may want to delete our list if it already exists. After all, we are importing a totally new list.
93 // Delete the list if it already exists...
94 SPListCollection listColl = web.Lists;
95 if (listColl["MyTitle"] != null)
96 { 97 listColl["MyTitle"].Delete();
98 }
99
MyTitle, of course, should be changed. By, why do I keep repeating the 'for illustrative purposes' still...
Next, let's create our list an populate it with fields. Again I have commented inline:
100 // Make a new list
101 Guid listId = listColl.Add("MyTitle", "MyDescription " + new Random().Next(10000).ToString(), SPListTemplateType.GenericList); 102 SPList list = web.Lists[listId];
103
104 // Add new fields to the list
105 foreach (string column in colTypes.Keys)
106 { 107 SPField newField = new SPField(list.Fields, colTypes[column].TypeName, column);
108 // this is where you would customize your fields, ie set number of lines in multiline, set number of decimals, etc.
109 list.Fields.Add(newField);
110 }
111
Simple enough, eh? Well, note that I have skipped the field customization. You will want to put code there to make sure your fields work as you expect. However, I'll leave that, and quite a few other things, as an exercise.
Finally we are at the last leg, we now just need to import our data. This is messy code, but you should get the drift. What we do is iterate through each row, creating a new item in our list, and for each column (remember that colNames dictionary we create earlier?) we add the value to the item:
112 // Now for importing the data
113 int rowCounter = 2; // 2 to skip first line with titles/column names
114
115 // Check if first value row is empty. Do something better than this in production, please...
116 Boolean rowEmpty = string.IsNullOrEmpty(ws.Cell(rowCounter, 1).Value);
117
118 while (!rowEmpty)
119 { 120 SPListItem item = list.Items.Add(); // New item
121 for (int colCounter = 1; colCounter <= maxColumn; colCounter++) // colCounter for use in Excel cell references
122 { 123 int fieldCounter = colCounter - 1; // Keeps track of our column names in colNames dictionary
124 Type t = item.Fields[colNames[fieldCounter]].GetType();
125 string typestring = t.ToString();
126 string fieldstring = item.Fields[colNames[fieldCounter]].StaticName;
127
128 if (t == typeof(SPFieldText) t == typeof(SPFieldMultiLineText))
129 { 130 // This only works for strings and simple values, folks!
131 item[colNames[fieldCounter]] = ws.Cell(rowCounter, colCounter).Value;
132 }
133 else if (t == typeof(SPFieldDateTime))
134 { 135 // Handle Dates
136 }
137 else if (t == typeof(SPFieldChoice))
138 { 139 // Handle Choice
140 }
141 else if (t == typeof(MyField))
142 { 143 // Handle custom field
144 }
145 else
146 { 147 throw new Exception(string.Format("{0} is unsupported type", t.ToString())); 148 }
149
150 }
151
152 // Make sure item is updated
153 item.Update();
154 // And advance
155 rowCounter++;
156
157 // Check next row, first column to see if we should continue...
158 rowEmpty = string.IsNullOrEmpty(ws.Cell(rowCounter, 1).Value);
159
160 }
161
Note that for each of the different field types, except SPFieldText and SPFieldMultiLineText, I have not added the actual adding of the values.
The reason for this is, well, one thing is that I am still lazy, however, the more important thing is that what we get from ExcelPackage is still only text strings. You can just add that value to a Date field, you first need to parse it, which in itself is a challenge, but you also need to input the value in some manner. For most default field types, this should be easy, and you should be able to Google a ton of resources. It may or may not be just that simple for custom types. That, however, is another show.
Well, if you just close up your brackets, you should be able to compile and run. Which, and I promise that this will be the last time I mention this in this article, is not the point, since THIS IS JUST FOR ILLUSTRATIVE PURPOSES!
Untill next time, my name is Bjørn Furuknap, and I am a SharePoint architect.
