SharePoint 2010 out-of-the-box export in Excel does not export the attachments, which can be uploaded for a particular list item.
If you define a custom list and hit “Export to Excel” button, your export file will look like this after saving it.
I was able to overcome this “restriction” by defining custom Ribbon button, which uses Open XML v2.0 and exports into Excel 2010 format all list items along with their attachments.
The final solution should provide the same result (in terms of files accessibility) as if you open a spread sheet and insert objects manually.
Of course the excel file should contain the data from the SPList along with their items’ embeddings.
My solution contains for few parts which I will briefly explain about:
a) Custom ribbon button and posting request, which returns the streamed file
b) Utilizing Open XML 2.0 API for generating the excel file
c) Wrapping Ole32.dll into managed code assembly to be able runtime to convert files into BIN files, which are embedded as application/vnd.openxmlformats-officedocument.oleObject
Custom Action
I decided to place the custom ribbon action button in the Actions group of List Items context buttons.
In terms of SharePoint code, all you need for displaying the button is SharePoint “Empty Element” item, feature and 2 images, which are referred into the custom action’s definition.
Below is the XML which defines the “Export To XLS” element:
<CustomActionId="ListItemsXLSCustomRibbonButton"RegistrationId="100"RegistrationType="List"Location="CommandUI.Ribbon"Sequence="5"Title="Export To XLS"><CommandUIExtension><CommandUIDefinitions><CommandUIDefinition Location="Ribbon.ListItem.Actions.Controls._children"><ButtonId="Ribbon.Items.XLS.Export"Alt="Export To XLS"Sequence="5"Command="Items_To_XLS"Image32by32="/_layouts/images/ListItemsExporter/xlsx32.png"Image16by16="/_layouts/images/ListItemsExporter/xlsx16.png"LabelText="Export to XLS"TemplateAlias="o1" /></CommandUIDefinition></CommandUIDefinitions><CommandUIHandlers><CommandUIHandlerCommand="Items_To_XLS"CommandAction="javascript:function exportToXls(){var site='{SiteUrl}';var url = site + '/_layouts/ListItemsExporter/ExportListItems.ashx?List={ListId}';var pageUrl = window.location;var submitUrl = site + '/_layouts/ListItemsExporter/ExportListItems.ashx?List={ListId}&listUrl=' + pageUrl;var form = $('<form method="POST" action="' + submitUrl + '"></form>').appendTo(document.body);form[0].submit();form.remove();}exportToXls();"/></CommandUIHandlers></CommandUIExtension></CustomAction>
On clicking the button we need a way to post a request and receive a file to download.
Unfortunately, this cannot be handled using Ajax, since Ajax can only receive responses in the form of text. That’s why I am submitting a dynamically created form, which does the trick.
I am also passing the url, which I will use on the server side to get the SPView, which is used to render the list items. Based on the SPView definition, I will export the same columns in the excel result file.
Here ends the client side of the story. On the server side I have a generic handler, which takes the request, fetches the data from the list along with its attachments and generates the result xlsx file which is streamed in the response.
Open XML 2.0 API
The most useful tool, when it comes to Open XML 2.0 is unquestionably “Open XML SDK 2.0 Productivity Tool”. http://www.microsoft.com/download/en/details.aspx?id=5124
You can create your office documents manually with the correspondent MS Office program, and after that you can reflect the code into C#. That should be your base line. You customizations and programming should start from here.
In my case a created few methods mostly related to the embedding files.
Every embedding file is represented by class with next properties:
public class XlsOleEmbedding
{
private const string prefix = "rId5";
private string propertisID;
/// <summary>
/// EmbeddedObjectProperties ID
/// </summary>
public String PropertiesID
{
get
{
return propertisID;
}
set
{
propertisID = prefix + value;
}
}
public String RowIDText { get; set; }
public String ColumnIDText { get; set; }
/// <summary>
/// OleObject ID represents Relation ID
/// </summary>
public String OleEmbeddingID { get; set; }
/// <summary>
/// If no office format file, generate bin on your own
/// </summary>
public Boolean IsOfficeFormat { get; set; }
/// <summary>
///Name displayed on the embedding Icon in xlsx
/// </summary>
public String FileName { get; set; }
/// <summary>
/// SharePoint Item ID
/// </summary>
public Int32 ID { get; set; }
/// <summary>
/// path to the attachment
/// </summary>
public String FilePath { get; set; }
/// <summary>
/// Binary content of the file used for creating ole embedding
/// </summary>
public byte[] FileContent { get; set; }
/// <summary>
/// unique identifier used for openxml relationships: ID + idx of attachment
/// </summary>
public string UniqueIDx { get; set; }
}
In generally, you should be very careful with relationIDs in Open XML structure, or you may end up with a weird error messages when opening your generated result file.
The code sample below iterates through the SPListItem attachments and transform them into custom objects, which are later used for the OLE embeddings.
SPListItemCollection items = elevatedList.GetItems(view);
DataTable source = items.GetDataTable();
int startingRowNumberForAttachmentContent = 0;
//Prepare attachments
List<XlsOleEmbedding> listOfAttachments = new List<XlsOleEmbedding>();
foreach (SPListItem item in items)
{
if (item.FileSystemObjectType == SPFileSystemObjectType.Folder) continue;
startingRowNumberForAttachmentContent = startingRowNumberForAttachmentContent + 2;
int startingCellNumberForAttachmentContent = source.Columns.Count;
int attIdx = 0;
foreach (string attachment in item.Attachments)
{
SPFile file = item.ParentList.ParentWeb.GetFile(item.Attachments.UrlPrefix + attachment);
string fileExtension = file.Name.Substring(file.Name.LastIndexOf('.') + 1);
startingCellNumberForAttachmentContent = startingCellNumberForAttachmentContent + 2;
byte[] fileContent = file.OpenBinary();
string attachmentFileSystemPath = exportsPath + file.Name;
System.IO.File.WriteAllBytes(attachmentFileSystemPath, fileContent);
XlsOleEmbedding embedding = new XlsOleEmbedding();
embedding.FileContent = fileContent;
embedding.FilePath = attachmentFileSystemPath; // item.Attachments.UrlPrefix + attachment; Ole32 bin generator needs to have a hdd path
embedding.FileName = file.Name;
embedding.ID = item.ID;
embedding.UniqueIDx = item.ID + attIdx.ToString();
embedding.PropertiesID = item.ID.ToString() + attIdx.ToString();
embedding.IsOfficeFormat = ExportHelpers.IsOfficeSupported(file.Name);
embedding.ColumnIDText = startingCellNumberForAttachmentContent.ToString(); //change it to dynamically render the embeddings on different cell
embedding.RowIDText = startingRowNumberForAttachmentContent.ToString(); //change it to dynamically render the embeddings on different column
listOfAttachments.Add(embedding);
attIdx++;
}
}
At the end of the handler body, I generate the BIN files which are used of embedding ole objects and after that I generate the xlsx file.
ExportHelpers.GenerateBinFilesForEmbedding(ref listOfAttachments, exportsPath);
// Adds child parts and generates content of the specified part.
private void GenerateDocumentContentParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
GenerateWorkbookPartContent(workbookPart, listName);
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
if (oleembeddings.Count > 0)
{
VmlDrawingPart vmlDrawingPart1 = worksheetPart.AddNewPart<VmlDrawingPart>("rId3");
GenerateVmlDrawingPart1Content(vmlDrawingPart1);
DrawingsPart drawingsPart1 = worksheetPart.AddNewPart<DrawingsPart>("rId2");
GenerateDrawingsPart1Content(drawingsPart1, oleembeddings.Count);
//
string xemfID = String.Empty;
for (int i = 0; i < oleembeddings.Count; i++)
{
//ImagePart
if (i == 0)
{
xemfID = "1";
}
else
{
xemfID = oleembeddings[i].UniqueIDx;
}
ImagePart imagePart = vmlDrawingPart1.AddNewPart<ImagePart>("image/x-emf", "rId" + xemfID);
GenerateImagePartContentFromResource(imagePart, oleembeddings[i].FileName);
worksheetPart.AddPart(imagePart, oleembeddings[i].PropertiesID);
//embedded object
EmbeddedObjectPart embeddedObjectPart = worksheetPart.AddEmbeddedObjectPart("application/vnd.openxmlformats-officedocument.oleObject");
oleembeddings[i].OleEmbeddingID = worksheetPart.GetIdOfPart(embeddedObjectPart);
if (oleembeddings[i].IsOfficeFormat)
GenerateEmbeddedObjectPartContent(embeddedObjectPart, oleembeddings[i].FileContent);
else
GenerateEmbeddedObjectPartContent(embeddedObjectPart, oleembeddings[i].FilePath); //use this if FileContent doesnt work; in case it is not Office supported format and bin file is generated
}
}
//generate content part with the rows here
GenerateWorksheetPart1Content(worksheetPart, oleembeddings, dataSource);
}
Ole32.dll
Ideally, all of your SPListItem attachments should be read and embedded by executing this small piece of code.
private static void GenerateEmbeddedObjectPartContent(EmbeddedObjectPart part, byte[] embeddedDocumentBytes)
{
using (Stream s = new MemoryStream(embeddedDocumentBytes))
{
s.Flush();
part.FeedData(s);
s.Close();
}
}
Indeed, life turned to be unfair.
I researched a lot on this “small” problem, because my generated files didn’t display properly. And on few places I found next comments:
“Yes, if you need to embed "other format files" which are not office documents, images, audios..., you need to wrap them in .bin file by yourself, and then embed the .bin file in the document. “
Well, my tests show me that I was able to embed only docx file without generating .bin object via my API.
For generating BIN files I had to expose and use Ole32.dll functions as managed code, which my C# can refer and execute.
public class Ole32Package
{
public static void Pacakge(string inputFile, string outputFile)
{
Ole32.IStorage storage = null;
FORMATETC fetc;
STGMEDIUM stgm;
object t,t1;
var result = Ole32.StgCreateStorageEx(
outputFile,
(uint)(Ole32.STGM.STGM_READWRITE | Ole32.STGM.STGM_SHARE_EXCLUSIVE |
Ole32.STGM.STGM_CREATE | Ole32.STGM.STGM_TRANSACTED),
(uint)Ole32.STGFMT.STGFMT_DOCFILE, 0, IntPtr.Zero, IntPtr.Zero, ref Ole32.IID_IStorage, out t);
storage = (Ole32.IStorage)t;
if (result != 0) return;
var CLSID_NULL = Guid.Empty;
fetc = new FORMATETC();
Ole32.IOleObject pOle = null;
result = Ole32.OleCreateFromFile(
ref CLSID_NULL,
inputFile,
ref Ole32.IID_IOleObject,
(uint)Ole32.OLERENDER.OLERENDER_NONE,
ref fetc,
null,
storage,
out t1
);
pOle = (Ole32.IOleObject)t1;
if (result != 0 || pOle == null) return;
result = Ole32.OleRun(pOle);
IntPtr unknownFromOle = Marshal.GetIUnknownForObject(pOle);
IntPtr unknownForDataObj;
Marshal.QueryInterface(unknownFromOle, ref Ole32.IID_IDataObject, out unknownForDataObj);
var pdo = Marshal.GetObjectForIUnknown(unknownForDataObj) as IDataObject;
fetc.cfFormat = (short)Ole32.CLIPFORMAT.CF_ENHMETAFILE;
fetc.dwAspect = DVASPECT.DVASPECT_CONTENT;
fetc.lindex = -1;
fetc.ptd = IntPtr.Zero;
fetc.tymed = TYMED.TYMED_ENHMF;
stgm = new STGMEDIUM();
stgm.unionmember = IntPtr.Zero;
stgm.tymed = TYMED.TYMED_ENHMF;
pdo.GetData(ref fetc, out stgm);
var hemf = GDI32.CopyEnhMetaFile(stgm.unionmember, outputFile);
storage.Commit((int)Ole32.STGC.STGC_DEFAULT);
pOle.Close(0);
GDI32.DeleteEnhMetaFile(stgm.unionmember);
GDI32.DeleteEnhMetaFile(hemf);
Marshal.ReleaseComObject(pdo);
Marshal.Release(unknownForDataObj);
Marshal.Release(unknownFromOle);
var storagePointer = Marshal.GetIUnknownForObject(storage);
int refCount;
do
{
refCount = Marshal.Release(storagePointer);
} while (refCount > 0);
}
}
And the result of all this code is what I was looking for - exporting all SPListItem depending on selected view along with their attachments embedded into the excel spreadsheet.
Let’s review the case in which i have custom SharePoint list with 2 items, and each of them has attachments.
Item1:
Item2:
When i hit the custom ribbon button, the save as dialog pops up and the generated Excel file has the name of the custom list against which the action is executed.
When i save the file, i am able to open the embedded attachments.
On the screeshot below you can see that the sample.pdf file has been successfully opened.
I tested the solution and it works good with Excel 2010 format. The Excel 2007 format though seems to be different when it comes to the embedded objects, so i guess your solution should be separately adopted to Excel 2007 if you are going to use it as client application for you excel files.
Read full article!
