How to create an Excel file using Silverlight and C-Sharp?

Sometime we need to export data as Excel report. This can be a plain Console application or a high end desktop and/or web application. You can of course do this using plugins/add-ons but what if you don’t have permission to use any 3rd party assemblies in your application?

Okay, in this post we are going to discuss this with help of a simple Silverlight & C# application but logic will be same in other platforms/technologies too. Keep reading.

Current days, it’s easy to create Excel file as it can read XML file format and view it as Excel. XML based Excel file uses a template to recognize it as Excel format. The tag “<?mso-application profid="Excel.Sheet"?>” does this thing intelligently. The “Workbook” tag tells it to create a Workbook and the “Worksheet” tag tells it to create a Worksheet. In the worksheet, you can create table using the “Table” tag to represent your data into it.

The “Table” tag consists of few “Row” and “Cell” tags to represent the data in tabular format. Now we just have to create rows and cells specifying proper data type which can be string, number etc. Check out the below code snippet where we are populating the data in a tabular format by using the rows and cells in a StringBuilder object and inserting the same in an Excel Template to create the Excel file. As we are using Silverlight, we are reading the excel file template (Data.txt) from the resource and then replacing the predefined $$ROW_DATA$$ tag with the string from the StringBuilder object.

private void OnExportToExcel(object sender, RoutedEventArgs e)
{
    var tableData = new StringBuilder();
    
    // populate the data in each cell of the rows
    for (var i = 0; i < 5; i++)
    {
        tableData.Append("<Row ss:AutoFitHeight="0">");
        tableData.Append("<Cell><Data ss:Type="String">" + Persons[i].Fname + "</Data></Cell>");
        tableData.Append("<Cell><Data ss:Type="String">" + Persons[i].Lname + "</Data></Cell>");
        tableData.Append("<Cell><Data ss:Type="String">" + Persons[i].City + "</Data></Cell>");
        tableData.Append("<Cell><Data ss:Type="Number">" + Persons[i].Age + "</Data></Cell>");
        tableData.Append("</Row>");
    }
    
    // read the excel file template from the resource
    using (var stream = Application.GetResourceStream(new Uri("/Demo;component/Data.txt", 
                                                                UriKind.Relative)).Stream)
    {    
        using (var streamReader = new StreamReader(stream))
        {
            // read the template and replace the table data to the specific portion of it
            var template = streamReader.ReadToEnd();
            template = template.Replace("$$ROW_DATA$$", tableData.ToString());
    
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.Filter = "Excel File (*.xml)|*.xml";
    
            if (dialog.ShowDialog() == true)
            {
                using (var fileWriter = new StreamWriter(dialog.OpenFile()))
                {
                    // write the data and save it as excel file
                    fileWriter.Write(template);
                }
            }
        }
    }
}

Later save the whole string as a File in local system. As it was Silverlight application, we used SaveFileDialog to create the stream. Remember that, though the demonstration was done using Silverlight and C#, but the implementation is same in all other technologies too. If you understand the Excel File template and it’s above implementation to structure the data, you will not face any issues else where.

Here is the simple Excel file template that we used in this demonstration, but we can further study the Excel file structure to add various other options, settings and styles:

Keeping an article simple, always help the readers to understand it easily and thus this post with a simple demonstration. If you further want to learn about adding styles in Excel sheet that we created, stay tuned to my blog to read the next post. Till then, happy coding.

Don’t forget to connect with me on Twitter, Facebook and Google+. Also subscribe to my blog’s RSS feed and email newsletter to get updates of new articles published in my blog.