How to attach an Xml to .net Mail Attachment using MemoryStream?

Question

I have a cshtml view in mvc filled with xml such as :

@model myproject.net.Models.mymodel
@{
    Layout = null;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", "attachment; " + "filename=" + 
        Model.myusername.ToString()  + "(" + 
        Model.mydate.Date.ToShortDateString() + ").xls");
}

<?xml version="1.0" encoding="utf-8"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <Styles>
        <Style ss:ID="s25">
            <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Interior ss:Color="#FFA500" ss:Pattern="Solid"/>
        </Style>
    </Styles>
    <ss:Worksheet ss:Name="Sheet1">
        <ss:Table>
        @if (Model.someNumerableContent.Count > 0)
        {
            <!-- MyContent -->
            <ss:Row>
                <ss:Cell><ss:Data ss:Type="String">Content Header</ss:Data></ss:Cell>
            </ss:Row>
            <ss:Row>
                <ss:Cell><ss:Data ss:Type="String">SubHeader 1</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">SubHeader 2</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">SubHeader 3</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">SubHeader 4</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">SubHeader 5</ss:Data></ss:Cell>
            </ss:Row>
            foreach (var subContent in Model.someNumerableContent)
            {
            <ss:Row>
                <ss:Cell><ss:Data ss:Type="String">@subContent.mydate.Date.ToShortDateString()</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">@subContent.number</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">@subContent.name</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">@subContent.surname</ss:Data></ss:Cell>
                <ss:Cell><ss:Data ss:Type="String">@subContent.issue</ss:Data></ss:Cell>
            </ss:Row>
            }
            <ss:Row> </ss:Row>
        }
        </ss:Table>
    </ss:Worksheet>
</ss:Workbook>

and i wanna convert it to .xls file and attach to a mail without saving it anywhere. but couldnt figure out how can i achive this. I cant use office.interop for some restrictions i got in the server so its not an option to me. I only want to create an .xls file by Xml and send it using .net mail. so far i've done this:

// Memory stream for the xml file
using (MemoryStream memoryStream = new MemoryStream())
{
    //                                                 Can i use something like this?
    byte[] contentAsBytes = Encoding.Default.GetBytes( View("ExportToExcel").ToString() );
    memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length);
    // Set the position to the beginning of the stream.
    memoryStream.Seek(0, SeekOrigin.Begin);
    // Create attachment
    ContentType contentType = new ContentType();
    contentType.MediaType = MediaTypeNames.Text.Xml;
    contentType.Name = UserName + "(" + FileDate + ").xls";
    // Attach
    mail.Attachment = new Attachment(memoryStream, contentType);
}

so how can i achive this?

Answer 1

I couldnt implement razor view successfull but after working 30 hours on it. i found another method..

First I created an XmlString as below:

public String XmlToImplement(int id)
{
    // Add values from db to model
    MyModel model = new MyModel();
    model.contents = db.contents.Where(c => c.MyData.id == id).ToList();
    // XmlString
    String XmlString = @"<?xml version='1.0' encoding='utf-8'?>
    <ss:Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
                xmlns:o='urn:schemas-microsoft-com:office:office'
                xmlns:x='urn:schemas-microsoft-com:office:excel'
                xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
                xmlns:html='http://www.w3.org/TR/REC-html40'>
        <Styles>
            <Style ss:ID='s25'>
                <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
                <Borders>
                    <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
                </Borders>
                <Interior ss:Color='#FFA500' ss:Pattern='Solid'/>
            </Style>
        </Styles>
        <ss:Worksheet ss:Name='Sheet1'>
            <ss:Table>";
            if (model.contents.Count > 0)
            {
                XmlString = XmlString + @"<!-- MyModelData -->
                <ss:Row>
                    <ss:Cell><ss:Data ss:Type='String'> MyModelData </ss:Data></ss:Cell>
                </ss:Row>
                <ss:Row>
                    <ss:Cell><ss:Data ss:Type='String'>Date</ss:Data></ss:Cell>
                    <ss:Cell><ss:Data ss:Type='String'>Number</ss:Data></ss:Cell>
                    <ss:Cell><ss:Data ss:Type='String'>Name/Surname</ss:Data></ss:Cell>
                </ss:Row>"; 
                foreach (var content in model.contents)
                {
                    XmlString = XmlString + @"<ss:Row>";
                    XmlString = XmlString + @"<ss:Cell><ss:Data ss:Type='String'>" + content.thedate.Date.ToShortDateString() + "</ss:Data></ss:Cell>";
                    XmlString = XmlString + @"<ss:Cell><ss:Data ss:Type='String'>" + content.number + "</ss:Data></ss:Cell>";
                    XmlString = XmlString + @"<ss:Cell><ss:Data ss:Type='String'>" + content.namesurname+ "</ss:Data></ss:Cell>";
                    XmlString = XmlString + @"</ss:Row>";
                }
                // An empty row for next value set
                XmlString = XmlString + "<ss:Row> </ss:Row>"; 
            }
            XmlString = XmlString + @"</ss:Table>
        </ss:Worksheet>
    </ss:Workbook>";
    return XmlString;
}

Then i saved it to MemoryStream with UTF8 Encoding..

// Create attachment
// Add XML to MemoryStream                                          /* MyXmlString */
MemoryStream memoryStream = new MemoryStream(Encoding.UTF8.GetBytes(XmlToImplement(id)));

Then defined ContentType as Excel

// Content Type
ContentType contentType = new ContentType("application/vnd.ms-excel");
contentType.Name = UserName + "(" + FileDate + ").xls";

At last i attached it to .net mail

// Attach
mail.Attachment = new Attachment(memoryStream, contentType);
mail.Attachment.NameEncoding = UTF8Encoding.UTF8;
mail.Attachment.TransferEncoding = TransferEncoding.Base64;
mail.Attachment.ContentDisposition.DispositionType = DispositionTypeNames.Attachment;

and it worked just fine. I may not be able to turn an xml view to mail attachment but easily i could turn an xml string to mail attachment and i hope this helps to the others that goes trough this problem.

Answer 2

depending on what version of excel, or what features you required. Carlos Ag's Excel Writer may suffice. it's a bit dated, but works for simple excel workbooks.

If you need to roll the template yourself check out Razor Generator Templates to render the model as an string. pass the string to a Stream and attach to the email.

update

to use the razor templating first you need a view

@* Generator: Template *@
@inherits The.NameSpace.RazorTemplateBase<dynamic>

@functions  {
    public object Model { get; set; }
}

<your typical markup goes here...>

then you need the base class

public abstract class RazorTemplateBase<dynamic>
{
    public static string RootAddress { get; set; }

    public virtual RazorTemplateBase<dynamic> Layout { get; set; }

    private readonly StringBuilder generatingEnvironment = new StringBuilder();

    public abstract void Execute();

    public void WriteLiteral(string textToAppend)
    {
        if (string.IsNullOrEmpty(textToAppend))
        {
            return;
        }
        generatingEnvironment.Append(textToAppend);
    }

    public void Write(object value)
    {
        if ((value == null))
        {
            return;
        }
        string stringValue;
        var t = value.GetType();
        var method = t.GetMethod("ToString", new [] { typeof(IFormatProvider) });
        if ((method == null))
        {
            stringValue = value.ToString();
        }
        else
        {
            stringValue = ((string)(method.Invoke(value, new object[] { CultureInfo.InvariantCulture })));
        }
        WriteLiteral(stringValue);
    }

    string content;

    public string RenderBody()
    {
        return content;
    }

    public string TransformText()
    {
        Execute();
        if (Layout != null)
        {
            Layout.content = generatingEnvironment.ToString();
            return Layout.TransformText();
        }
        else
        {
            return generatingEnvironment.ToString();
        }
    }
}

following the instructions on the project homepage set the custom tool (view properies windows) RazorGenerator.

then you can do something this

string excelXml = new NameOfMyView { Model = theData }.TransformText();
//write string to memmory stream, attach to MailMessage