DataTable with Images to Excel File using Open XML SDK

• Download DataTableToExcel.zip - 24.7 KB

Introduction

This tip will be useful for programmers who work with open XML to create Excel report from a datatable. I am explaining about how to export the ADO.NET DataTable into an Excel file using open XML SDK. You might came across a lot of blogs with this type of exporting DataTable to Excel sheet. I didn't find any blog or anyone explaining about exporting the DataTable along with images, that's the tricky part here.

Background

Before you begin, you must possess some basic knowledge about the internal representation of Excel sheet (*.xlsx). In simple terms, an Excel file (*.xlsx) is a package of few XML files which is combined as a single archive file. If you extract an Excel file, you are able to see the set of XML files in an organized way as shown in the below figure 1.

Short summary about the important XML parts of an Excel file:

• Core.xml and App.xml holds the common set of file properties such as creator name, creation date, title, keywords, etc.
• Workbook.xml (or workbook part) contains the workbook data and references to all of its worksheets. An Excel file shall contain exactly one workbook part.
• Sheet1.xml (or worksheet part) contains all the data, formulas, sheet properties, etc.
• Drawing1.xml (or drawing part) contains the presentation and layout information for one or more drawing elements such as images, autoshapes, etc. that are present on the worksheet. A worksheet shall have exactly one Drawing part as its child or relationship. Adding more than one Drawing part for a worksheet will corrupt the Excel file.
• SharedString.xml contains one occurrence of each unique string that occurs on all worksheets in a workbook. An Excel file shall contain exactly one shared string table part.
• Styles.xml contains all the characteristics for all the cells in the workbook. Such information includes numeric and text formatting, alignment, font, color and border.

Using the Code

Let’s see how to export a DataTable along with images and setting the column width & row height to fit the contents. From high level – We need a data table instance, then create an Excel file with a single worksheet and then fill the datatable contents into the worksheet. On looking into the creation of Excel file more closely, it should contain workbook part (holds the information about the sheet collection), worksheet part to hold the data; as we are going to fill the worksheet with string values and images, this worksheet needs to have a shared string part and a drawing part respectively.

Step 1

Create a simple DataTable instance & fill it with some contents as shown below:

public static class Data
{
    /// <summary>
    /// Get the DataTable instance
    /// </summary>
    /// <returns>Returns the DataTable instance</returns>
    public static DataTable GetTable()
    {
        //creating a DataTable
        DataTable table = new DataTable();
        //Adding four columns by specifying its type.
        table.Columns.Add("First Name", typeof(string));
        table.Columns.Add("Last Name", typeof(string));
        table.Columns.Add("Mail", typeof(string));
        table.Columns.Add("Avatar", typeof(Image));
        //Add some contents into the DataTable created
        table.Rows.Add("John", "Smith", 
        "john.smith@gmail.com", GetImageFromFile("default-user1.jpg"));
        table.Rows.Add("Alex", "Daniel", 
        "alex.daniel@gmail.com", GetImageFromFile("default-user2.jpg"));
        table.Rows.Add("Sierra", "Burch", 
        "Sierra.burch@gmail.com", GetImageFromFile("default-user3.jpg"));
        //return the datatable instance
        return table;
    }
    /// <summary>
    /// Returns the image from the file specified by the path argument
    /// </summary>
    /// <param name="path">represents the file path</param>
    /// <returns>Returns the Image instance</returns>
    private static Image GetImageFromFile(string fileName)
    {
        string path = @"..\..\Images\" + fileName;
        //check the existence of the file in disc
        if (File.Exists(path))
        {
            Image image = Image.FromFile(path);
            return image;
        }
        else
            return null;
    }
}

Step 2

Using Open XML SDK class, create a Spreadsheet document object using the open XML class named SpreadsheetDocument.

SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)

Step 3

Create a WorkbookPart and add it into the SpreadsheetDocument instance:

WorkbookPart workbookPart = package.AddWorkbookPart();

Step 4

Create a worksheet reference and add it into sheet collection of workbook part:

private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
 {
     Workbook workbook1 = new Workbook() 
     { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
     workbook1.AddNamespaceDeclaration("r", 
     "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
     workbook1.AddNamespaceDeclaration("mc", 
     "http://schemas.openxmlformats.org/markup-compatibility/2006");
     workbook1.AddNamespaceDeclaration("x15", 
     "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
 
     Sheets sheetCollection = new Sheets();
     Sheet sheet1 = new Sheet() 
     { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
     sheetCollection.Append(sheet1);
 
     workbook1.Append(sheetCollection);
     workbookPart1.Workbook = workbook1;
 } 

Step 5

Create a WorksheetPart and add it as child to the workbook part:

WorksheetPart worksheetPart1 = workbookPart.AddNewPart
<WorksheetPart>(GetNextRelationShipId());

Step 6

Create column information and create sheet data to fill the contents of datatable.

• Column collection information is needed to be added and this element is used to set the column width.
• As the contents of the DataTable contain string data & image, the resultant Excel file should contain shared string part to hold the unique strings and worksheet part should contain a drawing part as its child to hold the characteristics, bounds and reference to image part of the image data. Also, drawing part should add all the raw images as its child.
• Column width is calculated for the cell which contains string data by considering the default font “Calibri” of size 11.
• Row height & column width are adjusted to fit the images too.

// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
    Worksheet worksheet1 = new Worksheet() 
    { MCAttributes = new MarkupCompatibilityAttributes() 
    { Ignorable = "x14ac" } };
    worksheet1.AddNamespaceDeclaration("r", 
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    worksheet1.AddNamespaceDeclaration("mc", 
    "http://schemas.openxmlformats.org/markup-compatibility/2006");
    worksheet1.AddNamespaceDeclaration("x14ac", 
    "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    
    // Creates an Columns instance and adds its children.
    Columns columns1 = new Columns();
    for(int i = 0; i < m_table.Columns.Count; i++)
    {
        columns1.Append(new Column() 
        { Max = (UInt32Value)10U, BestFit = true, CustomWidth = true });
    }
    worksheet1.Append(columns1);
 
    SheetData sheetData1 = new SheetData();
    string drawingrID = GetNextRelationShipId();
    AppendSheetData(sheetData1, worksheetPart1, drawingrID, columns1);
    worksheet1.Append(sheetData1);
 
    if (worksheetPart1.DrawingsPart != null 
    && worksheetPart1.DrawingsPart.WorksheetDrawing != null)
    {
        Drawing drawing1 = new Drawing() { Id = drawingrID };
        worksheet1.Append(drawing1);
    }
    worksheetPart1.Worksheet = worksheet1;
}
/// <summary>
/// Fills the contents from DataTable to SheetData instance of worksheet part
/// </summary>
/// <param name="sheetData1">Instance of SheetData</param>
/// <param name="worksheetPart">Instance of WorksheetPart</param>
/// <param name="drawingrID">relationship id of drawing part</param>
/// <param name="columns">Instance of Column collection</param>
private void AppendSheetData(SheetData sheetData1, 
WorksheetPart worksheetPart, string drawingrID, Columns columns)
{
    Row headerRow = new Row();
    for (int i = 0; i < m_table.Columns.Count; i++)
    {
        Cell cell = new Cell();
        CellValue cellValue = new CellValue();
        AddTextToCell(cell, m_table.Columns[i].ColumnName, cellValue, i, columns);
        headerRow.Append(cell);
    }
    sheetData1.Append(headerRow);
    for (int rowIndex = 1; rowIndex < m_table.Rows.Count; rowIndex++)
    {
        Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + 1U) };
        DataRow tableRow = m_table.Rows[rowIndex];
        for (int colIndex = 0; colIndex < tableRow.ItemArray.Length; colIndex++)
        {
            Cell cell = new Cell();
            CellValue cellValue = new CellValue();
            object data = tableRow.ItemArray[colIndex];
 
            if (data is int || data is float || data is double)
            {
                //if the data is int or float or double, 
                //then the data can be serialized along within the cell itself
                cellValue.Text = data.ToString();
                cell.Append(cellValue);
            }
            else if (data is string)
            {
                AddTextToCell(cell, data, cellValue, colIndex, columns);
            }
            else if (data is Image)
            {
                //Calculate & sets the column width & Row height based on the image size
                Size imageSize = (data as Image).Size;
                row.Height = imageSize.Height;
                row.CustomHeight = true;
                Column column = (columns.ChildElements[colIndex] as Column);
                DoubleValue currentImageWidth = GetExcelCellWidth(imageSize.Width);
                if (column.Width != null)
                    column.Width = column.Width > 
                    currentImageWidth ? column.Width : currentImageWidth;
                else
                    column.Width = currentImageWidth; 
                column.Min = UInt32Value.FromUInt32((uint)colIndex + 1);
                column.Max = UInt32Value.FromUInt32((uint)colIndex + 2);
 
                //if the data is Image, we need to serailize 
                //its characteristics information in the drawing part
                //and then raw image need to be added as Image part within file or package
                DrawingsPart drawingsPart = null;
                Xdr.WorksheetDrawing worksheetDrawing = null;
 
                if (worksheetPart.DrawingsPart == null)
                {
                    drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(drawingrID);
                    worksheetDrawing = new Xdr.WorksheetDrawing();
                    worksheetDrawing.AddNamespaceDeclaration("xdr", 
                    "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                    worksheetDrawing.AddNamespaceDeclaration("a", 
                    "http://schemas.openxmlformats.org/drawingml/2006/main");
                    drawingsPart.WorksheetDrawing = worksheetDrawing;
                }
                else if (worksheetPart.DrawingsPart != null 
                && worksheetPart.DrawingsPart.WorksheetDrawing != null)
                {
                    drawingsPart = worksheetPart.DrawingsPart;
                    worksheetDrawing = worksheetPart.DrawingsPart.WorksheetDrawing;
                }
                string imagerId = GetNextRelationShipId();
                Xdr.TwoCellAnchor cellAnchor = AddTwoCellAnchor
                (rowIndex, colIndex, rowIndex + 1, colIndex + 1, imagerId);
                worksheetDrawing.Append(cellAnchor);
                ImagePart imagePart = 
                drawingsPart.AddNewPart<ImagePart>("image/png", imagerId);
                GenerateImagePartContent(imagePart, data as Image);
            }
            row.Append(cell);
        }
        sheetData1.Append(row);
    }
}
 
private void AddTextToCell
(Cell cell, object data, CellValue cellValue, int colIndex, Columns columns)
{
    cell.DataType = CellValues.SharedString;
    string text = data.ToString();
    if (!sharedStrings.Contains(text))
        sharedStrings.Add(text);
    cellValue.Text = sharedStrings.IndexOf(text).ToString();
 
    //Measure the text with default font and calculate the current cell width
    double width = graphics.MeasureString(text, font).Width;
    Column column = (columns.ChildElements[colIndex] as Column);
    DoubleValue currentWidth = 
    GetExcelCellWidth(width + 5); //5 constant represents the padding
                                  //sets the column width if the current cell width is maximum
    if (column.Width != null)
        column.Width = column.Width > currentWidth ? column.Width : currentWidth;
    else
        column.Width = currentWidth;
    column.Min = UInt32Value.FromUInt32((uint)colIndex + 1);
    column.Max = UInt32Value.FromUInt32((uint)colIndex + 2);
 
    cell.Append(cellValue);
}
 
/// <summary>
/// Calculate the cell width in excel units by taking the actual width in pixel 
/// </summary>
/// <param name="widthInPixel">Actual GDI based width in pixel</param>
/// <returns></returns>
private DoubleValue GetExcelCellWidth(double widthInPixel)
{
    DoubleValue result = 0;
    if (widthInPixel > 12)
    {
        result = 1;
        result += (widthInPixel - 12) / 7;
    }
    else
        result = 1;
 
    return result;
}
/// <summary>
/// Generates the image part
/// </summary>
/// <param name="imagePart">Instance of the image part</param>
/// <param name="image">Instance of the 
/// image which need to be added into the package
/// </param>
private void GenerateImagePartContent(ImagePart imagePart, Image image)
{
    MemoryStream memStream = new MemoryStream();
    image.Save(memStream, ImageFormat.Png);
    memStream.Position = 0;
    imagePart.FeedData(memStream);
    memStream.Close();
}
/// <summary>
/// Represents the bounds of the image, 
/// reference to image part and other characteristics using TwoCellAnchor class
/// </summary>
/// <param name="startRow">Starting row of the image</param>
/// <param name="startColumn">starting column of the image</param>
/// <param name="endRow">Ending row of the image</param>
/// <param name="endColumn">ending column of the image</param>
/// <param name="imagerId">Image's relationship id</param>
/// <returns></returns>
private Xdr.TwoCellAnchor AddTwoCellAnchor
(int startRow, int startColumn, int endRow, int endColumn, string imagerId)
{
    Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
 
    Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
    Xdr.ColumnId columnId1 = new Xdr.ColumnId();
    columnId1.Text = startColumn.ToString();
    Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
    columnOffset1.Text = "0";
    Xdr.RowId rowId1 = new Xdr.RowId();
    rowId1.Text = startRow.ToString();
    Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
    rowOffset1.Text = "0";
 
    fromMarker1.Append(columnId1);
    fromMarker1.Append(columnOffset1);
    fromMarker1.Append(rowId1);
    fromMarker1.Append(rowOffset1);
 
    Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
    Xdr.ColumnId columnId2 = new Xdr.ColumnId();
    columnId2.Text = endColumn.ToString();
    Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
    columnOffset2.Text = "0";// "152381";
    Xdr.RowId rowId2 = new Xdr.RowId();
    rowId2.Text = endRow.ToString();
    Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
    rowOffset2.Text = "0";//"152381";
 
    toMarker1.Append(columnId2);
    toMarker1.Append(columnOffset2);
    toMarker1.Append(rowId2);
    toMarker1.Append(rowOffset2);
 
    Xdr.Picture picture1 = new Xdr.Picture();
 
    Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
    Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = 
    	new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };
 
    Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = 
    	new Xdr.NonVisualPictureDrawingProperties();
    A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
 
    nonVisualPictureDrawingProperties1.Append(pictureLocks1);
 
    nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
    nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
 
    Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
 
    A.Blip blip1 = new A.Blip() { Embed = imagerId };
    blip1.AddNamespaceDeclaration("r", 
    	"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
 
    A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
 
    A.BlipExtension blipExtension1 = new A.BlipExtension() 
    	{ Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
 
    A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
    useLocalDpi1.AddNamespaceDeclaration("a14", 
    	"http://schemas.microsoft.com/office/drawing/2010/main");
 
    blipExtension1.Append(useLocalDpi1);
 
    blipExtensionList1.Append(blipExtension1);
 
    blip1.Append(blipExtensionList1);
 
    A.Stretch stretch1 = new A.Stretch();
    A.FillRectangle fillRectangle1 = new A.FillRectangle();
 
    stretch1.Append(fillRectangle1);
 
    blipFill1.Append(blip1);
    blipFill1.Append(stretch1);
 
    Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
 
    A.Transform2D transform2D1 = new A.Transform2D();
    A.Offset offset1 = new A.Offset() { X = 0L, Y = 0L };
    A.Extents extents1 = new A.Extents() { Cx = 152381L, Cy = 152381L };
 
    transform2D1.Append(offset1);
    transform2D1.Append(extents1);
 
    A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
    A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
 
    presetGeometry1.Append(adjustValueList1);
 
    shapeProperties1.Append(transform2D1);
    shapeProperties1.Append(presetGeometry1);
 
    picture1.Append(nonVisualPictureProperties1);
    picture1.Append(blipFill1);
    picture1.Append(shapeProperties1);
    Xdr.ClientData clientData1 = new Xdr.ClientData();
 
    twoCellAnchor1.Append(fromMarker1);
    twoCellAnchor1.Append(toMarker1);
    twoCellAnchor1.Append(picture1);
    twoCellAnchor1.Append(clientData1);
 
    return twoCellAnchor1;
}
/// <summary>
/// Generates the SharedString xml part using the string collection in SharedStrings (List<string>)
/// </summary>
/// <param name="part"></param>
private void GenerateSharedStringTablePart1Content(SharedStringTablePart part)
{
    SharedStringTable sharedStringTable1 = new SharedStringTable();
    sharedStringTable1.Count = new UInt32Value((uint)sharedStrings.Count);
    sharedStringTable1.UniqueCount = new UInt32Value((uint)sharedStrings.Count);
 
    foreach (string item in sharedStrings)
    {
        SharedStringItem sharedStringItem = new SharedStringItem();
        Text text = new Text();
        text.Text = item;
 
        sharedStringItem.Append(text);
        sharedStringTable1.Append(sharedStringItem);
    }
    part.SharedStringTable = sharedStringTable1;
}
/// <summary>
/// Gets the next relationship id
/// </summary>
/// <returns></returns>
private string GetNextRelationShipId()
{
    s_rId++;
    return "rId" + s_rId.ToString();
}

History

• 21^st February, 2014: Initial post
• 12^th July, 2016: Added the Column name of DataTable as header (first) row in the Excel file

In future, I might upgrade this solution to support basic formattings.