Wednesday, June 9, 2010

Exporting to Excel in Multiple Windows

In this article I'm going to talk about how to export to excel using Java, JavaScript and XML to multiple windows. I'll also show how this can be done from Adobe Flex pages. At the end I'll provide some code examples of my own of how to do the same thing using mainly Java

My company needed a way to export tables of varying sizes (and complexity) to excel. I did some research and found that the best way to do this is to group the XML value in HSSF rows and then write it out to an excel file. The added problem came up that even if you do this, sometimes users like to edit excel files they see, and without closing them they then go and open a new one on a different page. Obviously we didn't want their information to be lost, so we had to come up with a way to open multiple pages, or the same page again and again in multiple windows. Additionally, our clients wanted to be warned prior to waiting forever, if the download was too large and to have the option of only seeing the first 2000 entries or so.

So I came up with the following solution in JavaScript and the following solution: Example of Excel Export in JavaScript

And in the following jsp/java part, prior to invoking it we create a random number and append it to the file name, thus creating a unique window. Another option if you prefer is using the time stamp or a counter of some sort. Random number generation and Function Call Example

Lastly, for your use, this is how to export to excel in java: Java Example of "Table to Excel"

or with the Java on a JSP file:  JSP Example of "Table to Excel"

and if you have only wish to display the certain items from the xml, you can package an array and send it like this: Javascript Prep for Excel

Lastly, like I promised, I'll show you how to accomplish this from Adobe Flex:
 Firstly, the call looks something like this in the mxml: click="Utils.loadDGInExcel(orderBatches,'Order Batch',0,8);"

Firstly in our Flex you need to prepare the information into an HTML table format
private static var urlExcelExport:String = "openExcelTable";

public static function loadDGInExcel(dg:DataGrid,documentName:String,startPos:Number,colNum:Number):void {
          var arrayToExport:Array = convertDGToHTMLTable(dg,startPos,colNum);

How to convert a Datagrid's Data to HTML format

It might look complicated but it is a good example of how to get information from ItemRenderers inside of a DataGrid with normal cells and Item Renderer cells. you will obviously have to adapt it to your own needs as your DataGrid will be shaped different. We had to do one other one where we also had to convert specialized components but I'll omit that here unless someone asks for it. I found that the key is to do this differently:

else if(dg.dataProvider.getItemAt(j) is OrderBatchTO && dg.dataProvider.getItemAt(j).status is OrderBatchStatusTO) {
                            str2[m] = dg.dataProvider.getItemAt(j).status.longName;
you need to use the "is" function which is similar to the "as" function in other languages.

No comments:

Post a Comment