Import data from Excel & Export to Excel, PDF or XPS

If you’ve used our Excel or Document APIs, then you may already know where is article is headed. There have been numerous posts on how our Excel and Document APIs can be used to import and export data when working with our controls. In this post, we will focus on the newly released WebDataGrid control. Currently, it doesn’t have built-in support for exporting contents to Excel, PDF or XPS, ofcourse something that will be included in future. Here you can learn how to use the Excel API's load method to grab data from an excel sheet and display it within the WebDataGrid. Once the data is connected to the grid, you can then let the user do any grid operations like filter, sort, edit..etc. Once the user decides to export the displaying data, you can use the same API Engines to export the modified data and view back to Excel, PDF or XPS formats.
If you are only interested in learning how to export your data, you can skip the first section below.




Importing Data
Let’s start off by assigning some local variables that are going to help us accomplish the task.

view plaincopy to clipboardprint

1. //Create the temporary table to store data DataTable myDataTable = new DataTable();   
2. DataColumn myDataColumn;  
3. DataRow myDataRow;   
4.   
5. //MIN/MAX Values used to frame the working size of the Excel data to be imported.   
6. int minCellRow = Int32.MaxValue;   
7. int maxCellRow = Int32.MinValue;   
8. int minCellColumn = Int32.MaxValue;   
9. int maxCellColumn = Int32.MinValue;  

Now, using an Excel sheet that has the NorthWind customer data, call the load method off of the Excel API to read the data and then construct a dataset.

view plaincopy to clipboardprint

1. Workbook internalWorkBook = Workbook.Load(Request.PhysicalApplicationPath + "Northwind.xls");  

The load method will read the excel contents and return us an Excel workbook object. Once we have the workbook, we can search for the work sheet that contains the data and start constructing our data object that we will later bind to our WebDataGrid. In this example we are using a DataTable.
First, need to determine the bounds of our data, the column structure so that we can create the skeleton our data object accordingly. Using the workbook object, we can iterate through the rows and cells to determine the max and min of cells and rows that are contained within our worksheet that we want to load in our WebDataGrid.

view plaincopy to clipboardprint

1. foreach (Infragistics.Excel.WorksheetRow row in internalWorkBook.Worksheets["Customers"].Rows)   
2. {  
3.     foreach (Infragistics.Excel.WorksheetCell cell in row.Cells)   
4.     {   
5.         if (cell.Value != null)  
6.         {   
7.            //Logic For Determining the Range of Rows/Columns in the Excel File.  
8.            minCellRow = Math.Min(minCellRow, cell.RowIndex);  
9.            maxCellRow = Math.Max(maxCellRow, cell.RowIndex);  
10.            minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex);  
11.            maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex);  
12.         }  
13.     }   
14. }  

After we’ve collected the structural infromation of our worksheet, we can create columns for our datatable, and at the same time construct the columns of our WebDataGrid too. Here is where you can modify or remove colunms that you don’t want to import or setup properties on the WebDataGrid columns that you are creating.

view plaincopy to clipboardprint

1. for (int i = minCellColumn; i <= maxCellColumn; i++)   
2. {   
3.    //Get the column name   
4.    string columnName = internalWorkBook.Worksheets["Customers"].Rows[minCellRow].Cells[ i].Value.ToString();  
5.   
6.    //The export that was demonstrated earlier utilizes the first row  
7.    //for the column header. We can now use that to give column names.  
8.    myDataColumn = new DataColumn(columnName);  
9.   
10.    //Add the columns to the datatable.  
11.    myDataTable.Columns.Add(myDataColumn);  
12.   
13.    //Create WebDataGrid Columns and enable settings   
14.    BoundDataField bdf = new BoundDataField(true);   
15.    bdf.DataFieldName = columnName;   
16.    bdf.Key = columnName;   
17.    bdf.Header.Text = columnName;   
18.    bdf.Width = Unit.Pixel(100);   
19.    importGrid.Columns.Add(bdf); }  

Finally, now it’s time to push the data from our worksheet into our DataTable.

view plaincopy to clipboardprint

1. for (int rowIndex = minCellRow + 1; rowIndex <= maxCellRow; rowIndex++)   
2. {   
3.    //Create a new DataRow myDataRow = myDataTable.NewRow();   
4.    //Loop through the columns and associate the value to each cell   
5.    for (int columnIndex = minCellColumn; columnIndex <= maxCellColumn; columnIndex++)   
6.    {   
7.       myDataRow[columnIndex] = internalWorkBook.Worksheets["Customers"].Rows[rowIndex].Cells[columnIndex].Value;   
8.    }   
9.      
10.    //Add The Row to a DataTable   
11.    myDataTable.Rows.Add(myDataRow);   
12. }  

Ok, so now we have all we need in our DataTable, our WebDataGrid columns are setup as we want, let's go ahead and databind our WebDataGrid.

view plaincopy to clipboardprint

1. //Set the primary key so that the WebDataGrid can perform Auto Crud   
2. myDataTable.PrimaryKey = new DataColumn[] { myDataTable.Columns["CustomerID"] };  
3.   
4. //ImportGrid below is the grid that we have on our page   
5. importGrid.DataSource = myDataTable; importGrid.DataBind();  

To download the sample that includes the source code, use the link at the end of this post.
Note: The code assumes that you have the XLS file on the server, so that you can feed it in the Excel engine and can import data within the WebDataGrid. If you want to extend the behavior such that the client should be able to import an Excel file from his/her machine, then you will have to add the ability for the client to be able to upload that file onto the server first. Once, it is on the server, you can then simply use the import method to extract data from it.

Exporting Data
The idea behind exporting data to any of the formats using the APIs is the same. Basically, you iterate through the WebDataGrid columns first to create the column structure of the exported document and then iterate through the rows of the WebDataGrid to create rows for the data you want to export. Since the code is mostly the same when exporting to any of the formats, we will only going to run through  exporting to excel in the post, you can download the sample from link below to get code for all formats.
First we need to create the workbook and worksheet object of excel that we are going to export our data into and some helper variables that will help us accomplish the task.

view plaincopy to clipboardprint

1. //Create workbook and worksheet object for Excel Workbook   
2. theWorkbook = new Workbook();   
3. Worksheet theWorkSheet = theWorkbook.Worksheets.Add("WorkSheet1");   
4. int iRow = 1; int iCell = 1;  

Now, iterate through the WebDataGrid columns to create the excel sheet columns and go through your rows to fill up the cells in those columns.

view plaincopy to clipboardprint

1. //Iterate through the columns of the WebDataGrid and create  
2.  // columns within the worksheet that will be exported.   
3. foreach(GridField gridField in this.WebDataGrid1.Columns)   
4. {   
5.    iRow = 1;   
6.    theWorkSheet.Rows[iRow].Cells[iCell].Value = gridField.Header.Text;   
7.    theWorkSheet.Columns[iCell].Width = 5000;   
8.    iRow += 1;   
9.   
10.    //Now iterate through the grid rows to add rows to the worksheet   
11.    foreach(GridRecord gridRecord in this.WebDataGrid1.Rows)   
12.    {   
13.       theWorkSheet.Rows[iRow].Cells[iCell].Value = gridRecord.Items[iCell-1].Text;   
14.       iRow += 1;   
15.    }   
16.    iCell += 1;   
17. }  

In the sample attached you will find some extra code for formatting your cells while they are being exported. Once you’ve create your worksheet and exported the data, you can now go ahead and write the excel sheet to your stream, so that the client can view it.

view plaincopy to clipboardprint

1. //Create the Stream class   
2. System.IO.MemoryStream theStream = new System.IO.MemoryStream();   
3.   
4. //Write the in memory Workbook object to the Stream   
5. theWorkbook.Save(theStream);   
6.   
7. //Create a Byte Array to contain the stream and send the exported sheet to the client   
8. byte[] byteArr = (byte[])Array.CreateInstance(typeof(byte), theStream.Length);   
9. theStream.Position = 0;   
10. theStream.Read(byteArr, 0, (int)theStream.Length);   
11. theStream.Close();   
12. Response.Clear();   
13. Response.AddHeader("content-disposition", "attachment; filename=ExportedTo.xls");   
14. Response.BinaryWrite(byteArr);   
15. Response.End();  

That’s it! Using the code samples above you should be able to import your data from Excel and export it to Excel, PDF or XPS using the APIs that we ship along with our product. Download the source code of this tutorial from the link below.
Download Source Code: ExportWebDataGridContents.zip