Import/Export using Infragistics Excel in MVC3 Applications

Jordan Tsankov / Friday, March 9, 2012

Using the Infragistics Document library , you have access to a great set of tools to manipulate Microsoft Excel files. Infragistics Excel gives you an API to modify and save server-side data into an Excel document , as well as load up and process a file that a client specifies. In this blog post we’ll cover a basic scenario for both of these cases – showing you how to export data that has been fed to an Infragistics Grid , as well as using a local Excel file and populate an Infragistics jQuery Grid with its contents.

Let’s get on with it !

Initial Preparation

Before you can use the Infragistics Excel library , you need to add a few references to your project. First off, open the folder where you installed your Infragistics products, then navigate to jQuery/mvc/MVC3/bin/ and reference the Infragistics.Web.Mvc.dll. After that, you go to jQuery/mvc/Common/bin/ and reference both Infragistics.Web.Mvc.Documents.Core.dll and Infragistics.Web.Mvc.Documents.Excel.dll.

Exporting

To showcase how exporting works , we will set up a pretty simple MVC3 application. We have a controller that feeds data to an Infragistics jQuery Grid. The same controller is also responsible for loading up a .xls file and then populating a collection with objects which have been ‘parsed’ from the Excel file. That collection is finally used to supply a new Infragistics jQuery Grid with data. Here’s the code for the controller:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Security;

using ExcelExport.Models;

using Infragistics.Web.Mvc;

using Infragistics.Documents.Excel;

  

namespace ExcelExport.Controllers

{

    public class HomeController : Controller

    {

        //

        // GET: /Home/

  

        public ActionResult Index()

        {

            return View(SimpleItem.GetItemsList().AsQueryable());

        }

  

        [HttpPost]

        public void Export()

        {

            List<SimpleItem> items = SimpleItem.GetItemsList();

            Workbook workbook = new Workbook(WorkbookFormat.Excel97To2003);

            Worksheet sheet = workbook.Worksheets.Add("Sheet 1");

            int i = 1;

            foreach (SimpleItem item in items)

            {

                sheet.Rows[i].Cells[0].Value = item.Text1;

                sheet.Rows[i].Cells[1].Value = item.Text2;

                i++;

            }

            Save(workbook);

        }

  

        private void Save(Workbook document)

        {

            string fileName = "Document1.xls";

            Response.Clear();

            Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);

            Response.ContentType = "application/octet-stream";

            document.SetCurrentFormat(WorkbookFormat.Excel97To2003);

            document.Save(Response.OutputStream);

            Response.End();

        }

  

        public ActionResult Import() {

            Workbook wb = Workbook.Load(Server.MapPath("~/Content/Document1.xls"));

            Worksheet sheet = wb.Worksheets[0];

            List<SimpleItem> data = new List<SimpleItem>();

  

            foreach (WorksheetRow row in sheet.Rows)

            {

                if (row.Cells[0].Value != null && row.Cells[1].Value != null)

                {

                    SimpleItem item = new SimpleItem 

                    {

                        Text1 = row.Cells[0].Value.ToString(),

                        Text2 = row.Cells[1].Value.ToString()

                    };

  

                    data.Add(item);

                }

  

            }

            return View(data.AsQueryable());

        }

    }

}

The Index ActionResult is the one responsible for the main view – the one we have our initial grid on. This is why we send the collection of data as the view’s model – so that the grid has a data model to bind to.

In Export we handle incoming POST requests, which are in this case used only to signal that the user wants to export the model data collection. We create a new Workbook object, which is technically a code representation of an Excel file. Then we add a worksheet to this workbook and simply write data in each row , in the first two cells.

Attention then gets shifted towards the Save method, which attaches the resulting Excel file to the response header and returns the response.

And here you have the model class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

  

namespace ExcelExport.Models {

    public class SimpleItem {

        public String Text1 { get; set; }

        public String Text2 { get; set; }

  

        public static List<SimpleItem> GetItemsList() {

            List<SimpleItem> items = new List<SimpleItem>();

            for (int i = 0; i < 100; i++) {

                items.Add(new SimpleItem { Text1 = "Item " + i, Text2 = "Text " + i });

            }

            return items;

        }

    }

}

Nothing staggering here – a function that yields a list with some items added inside. We use that method in the controller in order to set the view’s model.

And finally here is the Index view , which holds the initial grid:

@using Infragistics.Web.Mvc;

@model IQueryable<ExcelExport.Models.SimpleItem>

  

@{

    ViewBag.Title = "Index";

}

  

@(

Html.Infragistics()

.Grid(Model)

.ID("grid1")

.Width("300px")

.Height("500px")

.AutoGenerateColumns(true)

.DataBind()

.Render()

)

  

<form action="Home/Export" method="post">

<button type="submit" value="Export">Export</button>

</form>

<form action="Home/Import">

<button type="submit" value="Import">Import</button>

</form>



Importing

As stated above , our goal is to have an external Microsoft Excel file used as a data source for an Infragistics jQuery Grid. We have one such file prepared in the /Contents folder of our sample project – here’s the one:

Document Location Excel

In this Excel file , we have the first two columns populated with text in the following pattern:

Import 0	Item 0
Import 1	Item 1
Import 2	Item 2
Import 3	Item 3
Import 4	Item 4

This should effectively translate to a grid with two columns , populated with the same entries. The function that takes care of this is in the HomeController , and it’s called Import.

public ActionResult Import() {

            Workbook wb = Workbook.Load(Server.MapPath("~/Content/Document1.xls"));

            Worksheet sheet = wb.Worksheets[0];

            List<SimpleItem> data = new List<SimpleItem>();

  

            foreach (WorksheetRow row in sheet.Rows)

            {

                if (row.Cells[0].Value != null && row.Cells[1].Value != null)

                {

                    SimpleItem item = new SimpleItem 

                    {

                        Text1 = row.Cells[0].Value.ToString(),

                        Text2 = row.Cells[1].Value.ToString()

                    };

  

                    data.Add(item);

                }

  

            }

            return View(data.AsQueryable());

        }

What we do is we load a workbook from a physical file and then open up the first worksheet. We then iterate through all the available rows and create a new SimpleItem instance whenever both of the row’s first and second cells have any value in them. The resulting SimpleItem object we add to a list.

Finally, we return the view together with the list , so it can be used as a data model by our new Infragistics jQuery Grid. Here’s the resulting view:

@using Infragistics.Web.Mvc;

@model IQueryable<ExcelExport.Models.SimpleItem>

           

@{

    ViewBag.Title = "Import";

    Layout = "~/Views/Shared/_Layout.cshtml";

}

  

<h2>Imported from a XML file</h2>

  

@(

Html.Infragistics()

.Grid(Model)

.ID("grid1")

.Width("300px")

.Height("500px")

.AutoGenerateColumns(true)

.DataBind()

.Render()

)



And that’s pretty much all there is to it ! You will see the same data from the Excel file loaded into your jQuery grid.

Conclusion

In a case where your application is grid-heavy , what better way to let users preserve data than enable them to export it locally to a spreadsheet? And if you need two-way interaction – then why not let the same users upload the spreadsheet back to you so you update your server data? With Infragistics Excel , this is all entirely possible and as you’ve seen in the examples above – it is not that hard to achieve as well.

You may acquire further information about exporting your grid data by checking out the official sample.

Or you may wish to download the sample project for this blog post by clicking here.

ExcelExport.zip