11:59 am
January 28
Export data from ASP .NET application and insert table to Word document
( 0 Votes )
Written by: Blogging Team Views : 3251
Attachments with this blog:
Source code 2 
Source code 1 
In this post I’ll explain how to insert data into word document using asp .net application. I was searching on net for exporting data into word but the most of them (almost all of them) where using Response.Wirte and setting the content header type to word which will save the content to word document but we will not have full control on the exported content, i.e. it will be very difficult to control the styling of exported text.

So here is a simple solution for exporting the data from an asp .net application to a word document using Microsoft Word Interop Assemblies, by using Interop assemblies we will have full control on the content which we export into word document.

index

To make the solution simpler to understand, I took a class called ‘User’ with two properties ‘Name’ and ‘Email’ and on button click on a web page I am populating two dummy records at runtime and exporting code as you the code snippets for the class syntax and C# statement for dummy data generation.

User class:

public class User
{
    public string Name { get; set; }
    public string Email { get; set; }
}

Sample data to export:

List<User> users = new List<User>()
{
    new User() {Name = "Kranthi", Email = "kranthi@email.com"},
    new User() {Name = "Gullapalli", Email = "gullapalli@mail.com"}
};

Now we have our model and data ready for export now let’s look into exporting data into Word document.

Exporting data to Word in four simple steps:

  • Add Microsoft.Office.Interop.Word reference to our project, by right click on project and add reference.
  • Crete instance of Word Application and Document using below snippet which creates an instance of document and activates it.
    Application oWordApp = new Application();
    Document document = oWordApp.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
    document.Activate();
  • Add content to the word document using Interop Assemblies. Below is the code snippet which will loop through the sample data i.e. users and enter the Name and Email into our word document instance created above.
    foreach (var user in users)
    {
        //Just to display current user index in list of users
        oWordApp.Selection.TypeText("User " + (users.IndexOf(user) + 1));
        oWordApp.Selection.TypeParagraph();
    
        oWordApp.Selection.TypeText("Name: " + user.Name);
        oWordApp.Selection.TypeParagraph();
    
        oWordApp.Selection.TypeText("Email: " + user.Email);
        oWordApp.Selection.TypeParagraph();
    }
  • Save the Word document and Quit the application using below code snippet.
    document.SaveAs(@"C:\SampleWordGenerated.docx");
    oWordApp.Application.Quit(ref oMissing, ref oMissing, ref oMissing);

That’s it, now building and executing the above code should generate a sample word document containing the sample data which we defined above.

Here is the code of the button click where is doing all the process:

protected void btnExport_OnClick(object sender, EventArgs e)
{
    List<User> users = new List<User>()
    {
        new User() {Name = "Kranthi", Email = "kranthi@email.com"},
        new User() {Name = "Gullapalli", Email = "gullapalli@mail.com"}
    };
    try
    {
        object oMissing = System.Reflection.Missing.Value;
        object header1 = Word.WdBuiltinStyle.wdStyleHeading1;
        object header2 = Word.WdBuiltinStyle.wdStyleHeading2;
        object title = Word.WdBuiltinStyle.wdStyleTitle;
        Application oWordApp = new Application();
        Document document = oWordApp.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
        document.Activate();

        foreach (var user in users)
        {
            oWordApp.Selection.TypeText("User " + (users.IndexOf(user) + 1));
            oWordApp.Selection.set_Style(ref title);
            oWordApp.Selection.TypeParagraph();

            oWordApp.Selection.TypeText("Name: " + user.Name);
            oWordApp.Selection.set_Style(ref header1);
            oWordApp.Selection.TypeParagraph();

            oWordApp.Selection.TypeText("Email: " + user.Email);
            oWordApp.Selection.set_Style(ref header2);
            oWordApp.Selection.TypeParagraph();
            oWordApp.Selection.TypeParagraph();
        }
        document.SaveAs(@"C:\SampleWordGenerated.docx");
        oWordApp.Application.Quit(ref oMissing, ref oMissing, ref oMissing);
        lblMessage.Text = "Document sucessfully exported to the above specified path.";
    }
    catch (Exception ex)
    {
        lblMessage.Text = "Error while generating document. Error: " + ex.Message;
    }
}

If we want to add styles to the content exported, we can specify them while adding content to the word document. For Example if we want to the default header1 styling in word document to the UserName, below is the code snippet to do that:

//defining the Header Styling
object header1 = Word.WdBuiltinStyle.wdStyleHeading1;

//Adding the header styling to the UserName
oWordApp.Selection.TypeText("Name: " + user.Name);
oWordApp.Selection.set_Style(ref header1);
oWordApp.Selection.TypeParagraph();

In another hand, maybe what you want to do it insert a table into this word document instead of just the data. So, let’s consider we have List (list of users) in our application and we need to export this data as a table into the word document, below is the procedure to do that.

In this example I took the class ‘User’ with more properties than in the previous example, with the properties FirstName, LastName, Email, Phone, Address and a Pincode, what it is more complete and it will show a table with more columns, as it is showed below:

public class User
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    public int Pincode { get; set; }
}

We need users list to export, I’ve written a dummy method to return a list of users:

private static List<User> GetDummyUserData()
{
    return new List<User>()
    {
        new User() { FirstName = "Kranthi", LastName = "Kiran", Email="kranthi_kiran@live.com", Address="305 North Street", Phone = "9090909090", Pincode= 54321},
        new User() { FirstName = "John", LastName = "Doe", Email="kranthi_kiran@live.com", Address="305 North Street", Phone = "9090909090", Pincode= 54321},
        new User() { FirstName = "Gunda", LastName = "Phani", Email="kranthi_kiran@live.com", Address="305 North Street", Phone = "9090909090", Pincode= 54321},
    };
}

Now we are ready with data. Let’s start inserting table with user data into word document, which can be done in four simple steps:

  • Creating the instance of table. Below you can see the code snippet which creates a table instance using interop dlls.
    int noOfRows = users.Count + 1;
    int noOfColumns = 5;
    Word.Table table = oWordDoc.Tables.Add(oWordDoc.Range(ref start, ref end), noOfRows, noOfColumns, ref oMissing, ref oMissing);
  • Adding column headers. I am defining the column headers in the first row, below you can see it in the code snippet:
    private static void AddColumnHeaders(ref Word.Table table)
    {
        table.Rows[1].Cells[1].Range.Text = "First Name";
        table.Rows[1].Cells[2].Range.Text = "Last Name";
        table.Rows[1].Cells[3].Range.Text = "Email";
        table.Rows[1].Cells[4].Range.Text = "Address";
        table.Rows[1].Cells[5].Range.Text = "Pincode";
    }
  • Setting the style for the Table. Setting the style is different for different versions of Microsoft.Interop.Word dll versions. So, for the version 8.5.0.0 we can use the built in Enumeration provided as shown below:
    object style1 = Word.WdBuiltinStyle.wdStyleTableLightGridAccent1;
    table.set_Style(ref style1);
  • And for the version 11.0.0.0 I did not find a way to get the Table Style Enumeration code, so, my workaround was, in word document when ever you insert table manually, in the design tab all the different table styles are displayed. When I hover mouse on table style, style name is displayed as a tooltip. Using the tooltip text we can set the table style as below.
    table.set_Style("Colorful List");
    I know this is not the best way but I couldn’t find any other way.

Here is all the code of the button click event that is making all the process:

protected void btnExport_OnClick(object sender, EventArgs e)
{
    List<User> users = GetDummyUserData();
    object oMissing = System.Reflection.Missing.Value;
    object header1 = Word.WdBuiltinStyle.wdStyleHeading1;
    object header2 = Word.WdBuiltinStyle.wdStyleHeading2;
    object title = Word.WdBuiltinStyle.wdStyleTitle;

    try
    {
        Application oWordApp = new Application();
        Document oWordDoc = oWordApp.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
        oWordDoc.Activate();
        object start = 0;
        object end = 0;

        int noOfRows = users.Count + 1;
        int noOfColumns = 5;
        Word.Table table = oWordDoc.Tables.Add(oWordDoc.Range(ref start, ref end), users.Count + 1, 5, ref oMissing, ref oMissing);
            AddColumnHeaders(ref table);
        for (int i = 1; i <= users.Count; i++)
            {
            table.Rows[i + 1].Cells[1].Range.Text = users[i-1].FirstName;
            table.Rows[i + 1].Cells[2].Range.Text = users[i - 1].LastName;
            table.Rows[i + 1].Cells[3].Range.Text = users[i - 1].Email;
            table.Rows[i + 1].Cells[4].Range.Text = users[i - 1].Address;
            table.Rows[i + 1].Cells[5].Range.Text = users[i - 1].Pincode.ToString();
        }
        table.set_Style("Colorful List");
        oWordDoc.SaveAs(@"C:\SampleWordGenerated.docx");
        oWordApp.Application.Quit(ref oMissing, ref oMissing, ref oMissing);
        lblMessage.Text = "Document sucessfully exported to the above specified path.";
    }
    catch (Exception ex)
    {
        lblMessage.Text = "Error while generating document. Error: " + ex.Message;
    }
}

That’s it, now you should be able to export the list of users into a word document.

For a better understanding, please download the source code attached to this post.



( 0 Votes )