View feature attrbutes

SamirGambhir · ‎06-27-2012

Hi,
I am building a query using multiple features and multiple outfields. I would like to open the results of this query in an Excel spreadsheet, but I am not sure how to operationalize this. I am trying to look at the results of the query in an alert box so I can decide how to slice the data to prepare it for Excel. Can somebody point me to an example where I can view the data prior to putting it in excel. Also, if there is an example how to open and paste this data programatically on to Excel. I have been struggling this for sometime now, so I request your help asap. I'll really appreciate your help.
Thanks
Samir

JanJeske · ‎06-29-2012

Hey one Sollution would be to create a .csv file. Just open a new Window with an Output like:

id;data;other
1;something;ycxvas
2;something other;asdfas

and so on.

Contenttype should be "application/x-csv"

I hope it help a little bit

SamirGambhir · ‎07-03-2012

Thanks Jan,
Would you be able to share some code for this? I cannot figure out how to operationalize it.
Samir

JeffPace · ‎07-03-2012

WE do this serverside. Basically we take the query results in json and pass it to a Java servlet. The servlet then parse the data up using

org.apache.poi.hssf.usermodel

to create the workbook, worksheet, and then loop over the data and cell by cell write it for export to excel.

SamirGambhir · ‎07-03-2012

Hi Jeff,
Can you please provide me with an example of this? I'll really appreciate it.
Thanks
Samir

JeffPace · ‎07-03-2012

Okay apologies in advance for how bad my JAVA code is. This written way back in the 1.6 api days and before I was really comfortable. However since it works I have not updated it

So we have a widget that has a form

<form id="miexcelform" style="display:none" action="exporttoexcel" method="POST">
                    <input id="midata" name="data"></input>
                    <input id="mifilename" name="filename"></input>
                    <input id="mitype" name="type"></input>
                </form>

We also have a results table elsewhere in the application from a query. It data in it is json, and looks like

{"result0":{"Address":"Address"},"resultcolumns0":{"column0":"Address"},"result1":{"Title Field":"5188570309","PARCEL ID:":"5188570309","PRIMARY ADDR:":"5609 52ND AVE W SCT","UNIT:":"null","OWNER:":"BATTEL,LUC","SECOND OWNER:":"null","COMMISSIONER:":"John Chappie","SUBDIVISION:":"GLENN LAKES PH 6 PB35/176","LOT BLOCK:":"46 M","ACRES:":".207","LUC:":"0100","LUC DESCR:":"Single Family Residential (1554)","ZONING:":"PD-R","FUTURE LANDUSE:":"RES-9","SECTION INDEX:":"S8 T35 R17","FLOOD ZONE:":"X","FLOODWAY:":"N","FLOOD MAP:":"328_B","IMPACT FEE DIST:":"A - SW","HISTORIC:":"URBAN-A","OVERLAYS:":"NONE","WATERSHED:":"NONE","FIRE DISTRICT:":"Cedar Hammock","EVAC ZONE:":"D","SPECIAL_AREAS:":"NONE","SCHOOL SV AREA:":"SSA-4","FRONTAGE:":"75","OWN ADDR:":"VANMALDEGHEMSTRAAT","OWN ADDR2:":"KOKSIJDE","OWN CITY:":"null","OWN ST:":"null","OWN ZIP:":"null","OWN CNTRY:":"BELGIUM","OWN CNTR ZIP:":"40A","PARENT PIN:":"5188570309"},"resultcolumns1":{"column0":"Title Field","column1":"PARCEL ID:","column2":"PRIMARY ADDR:","column3":"UNIT:","column4":"OWNER:","column5":"SECOND OWNER:","column6":"COMMISSIONER:","column7":"SUBDIVISION:","column8":"LOT BLOCK:","column9":"ACRES:","column10":"LUC:","column11":"LUC DESCR:","column12":"ZONING:","column13":"FUTURE LANDUSE:","column14":"SECTION INDEX:","column15":"FLOOD ZONE:","column16":"FLOODWAY:","column17":"FLOOD MAP:","column18":"IMPACT FEE DIST:","column19":"HISTORIC:","column20":"OVERLAYS:","column21":"WATERSHED:","column22":"FIRE DISTRICT:","column23":"EVAC ZONE:","column24":"SPECIAL_AREAS:","column25":"SCHOOL SV AREA:","column26":"FRONTAGE:","column27":"OWN ADDR:","column28":"OWN ADDR2:","column29":"OWN CITY:","column30":"OWN ST:","column31":"OWN ZIP:","column32":"OWN CNTRY:","column33":"OWN CNTR ZIP:","column34":"PARENT PIN:"},"result2":{"Title Field":"5188570259","PARCEL ID:":"5188570259","PRIMARY ADDR:":"5605 52ND AVE W SCT","UNIT:":"null","OWNER:":"PROCHASKA,DARRELL DEAN","SECOND OWNER:":"PROCHASKA,DENA ANN","COMMISSIONER:":"John Chappie","SUBDIVISION:":"GLENN LAKES PH 6 PB35/176","LOT BLOCK:":"45 M","ACRES:":".207","LUC:":"0100","LUC DESCR:":"Single Family Residential (1554)","ZONING:":"PD-R","FUTURE LANDUSE:":"RES-9","SECTION INDEX:":"S8 T35 R17","FLOOD ZONE:":"X","FLOODWAY:":"N","FLOOD MAP:":"328_B","IMPACT FEE DIST:":"A - SW","HISTORIC:":"URBAN-A","OVERLAYS:":"NONE","WATERSHED:":"NONE","FIRE DISTRICT:":"Cedar Hammock","EVAC ZONE:":"D","SPECIAL_AREAS:":"NONE","SCHOOL SV AREA:":"SSA-4","FRONTAGE:":"75","OWN ADDR:":"5605 52ND AVE W","OWN ADDR2:":"null","OWN CITY:":"BRADENTON","OWN ST:":"FL","OWN ZIP:":"34210","OWN CNTRY:":"null","OWN CNTR ZIP:":"null","PARENT PIN:":"5188570259"},"resultcolumns2":{"column0":"Title Field","column1":"PARCEL ID:","column2":"PRIMARY ADDR:","column3":"UNIT:","column4":"OWNER:","column5":"SECOND OWNER:","column6":"COMMISSIONER:","column7":"SUBDIVISION:","column8":"LOT BLOCK:","column9":"ACRES:","column10":"LUC:","column11":"LUC DESCR:","column12":"ZONING:","column13":"FUTURE LANDUSE:","column14":"SECTION INDEX:","column15":"FLOOD ZONE:","column16":"FLOODWAY:","column17":"FLOOD MAP:","column18":"IMPACT FEE DIST:","column19":"HISTORIC:","column20":"OVERLAYS:","column21":"WATERSHED:","column22":"FIRE DISTRICT:","column23":"EVAC ZONE:","column24":"SPECIAL_AREAS:","column25":"SCHOOL SV AREA:","column26":"FRONTAGE:","column27":"OWN ADDR:","column28":"OWN ADDR2:","column29":"OWN CITY:","column30":"OWN ST:","column31":"OWN ZIP:","column32":"OWN CNTRY:","column33":"OWN CNTR ZIP:","column34":"PARENT PIN:"},"result3":{"Subdivision Number":"Subdivision Number"},"resultcolumns3":{"column0":"Subdivision Number"},"result4":{"Title Field":"5188539","SUBDIV NAME:":"GLENN LAKES PHASE 6","SUBDIV NUMBER:":"5188539","BOOK:":"0035","PAGE:":"0176","RECORD DATE:":"04-27-00","TOWNSHIP RANGE:":"3517","SECTION:":"8","HEADER_NUMBER:":"null"},"resultcolumns4":{"column0":"Title Field","column1":"SUBDIV NAME:","column2":"SUBDIV NUMBER:","column3":"BOOK:","column4":"PAGE:","column5":"RECORD DATE:","column6":"TOWNSHIP RANGE:","column7":"SECTION:","column8":"HEADER_NUMBER:"}}

and the export to excel function in the javascript application is

onExcelExport:function(evt){
                        var json = this.widgets.results.getChildren();
                        var value = this.widgets.miinputfilename.getValue();
                        var excel = org.mymanatee.common.util.exportExcel(json, value);
                        var form = dojo.byId("miexcelform");
                        console.log(excel.data);
                        dojo.byId("midata").value=excel.data;
                        dojo.byId("mifilename").value=excel.filename;
                        //remove last comma
                        if(this.searchType.substring(this.searchType.length-1, this.searchType.length)==","){
                        dojo.byId("mitype").value=this.searchType.substring(0, this.searchType.length-1);
                        }else{
                          dojo.byId("mitype").value=this.searchType
                        }
                        form.submit();

The form is submitted to a JAVA servlet

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.mymanatee.gis.excel;

import java.io.File;
import java.io.OutputStream;
import java.net.URI;
import java.sql.Array;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.mymanatee.gis.json.*;
import org.apache.poi.ss.usermodel.RichTextString;

/**
 *
 * @author sansbro
 */
public class ResultListtoExcelServlet extends HttpServlet {

    @Override
    public void service(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, java.io.IOException {
            System.out.println("req: "+req);
//
            try {

            String rawdata = req.getParameter("data");
            String filename = req.getParameter("filename");
            String type = req.getParameter("type");
      //      System.out.println("type: "+type);
            if(type.length()<1){
            type="Title Field,";
            }
            String types[] =type.split(",");
   //         System.out.println("types: "+types[0].toString());
   //         System.out.println(filename);
            resp.addHeader("Content-Disposition", "attachment; filename=" + filename);
            OutputStream out = resp.getOutputStream();
            JSONObject data = new JSONObject(rawdata);
            
//            System.out.println(data.names().toString());
//
//
            try {
//
//            ServletConfig config = getServletConfig();
//            ServletContext context = config.getServletContext();
//            String path = context.getRealPath("/DamageAssessmentTemplate.xls");
//            File file = new File(path);
//
                ResultListtoExcel.doSpreadsheets(out, data, filename, types);
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }



        } catch (JSONException ex) {
            Logger.getLogger(ResultListtoExcelServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


}

JeffPace · ‎07-03-2012

which takes the data from the form and sends it to a second class to parse it via the doSpreadsheets method

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


package org.mymanatee.gis.excel;



import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.mymanatee.gis.json.*;

/**
 *
 * @author jpace
 */
public class ResultListtoExcel {
 public static void main(String[] args)
 {
  try
  {
                    String dir = "C:\\Documents and Settings\\jpace\\Desktop\\Results\\";
                    String filename = "export.xls";
                    String types[] = {"Parcels"};
                    JSONObject data = new JSONObject("{result0:{'Parcel Id:':\"4015111505\", 'Primary Address:':\"4903 20TH AVE W SCT\", 'Owner:':\"ARRIAGA, FELIX\", 'Secondary Owner:':\"ARRIAGA, KRISTINE L\", 'Commissioner:':\"John Chappie\", 'Subdivision:':\"WESTWOODS SUBDIVISION PB16/24\", 'Subdivision Lot Block:':\"7 C\", 'Acres:':\"0.2342\", 'Luc:':\"0100\", 'Luc Description:':\"SINGLE FAMILY RESIDENTIAL\", 'Zoning:':\"RSF-4.5\", 'Future Land Use:':\"RES-6\", 'Section Index:':\"S33 T34 R17\", 'Flood Zone:':\"X\", 'Flood Way:':\"N\", 'Flood Map:':\"326_B\", 'Impact Fee Dist:':\"A - SW\", 'Historic:':\"URBAN-A\", 'Overlays:':\"NONE\", 'Watershed:':\"NONE\", 'Fire District:':\"West Manatee\", 'Evacuation Zone:':\"E\", 'Special Areas:':\"NONE\", 'School Svc Area:':\"SSA-4\", 'Frontage:':\"100\"}, resultcolumns0:{column0:\"Parcel Id:\", column1:\"Primary Address:\", column2:\"Owner:\", column3:\"Secondary Owner:\", column4:\"Commissioner:\", column5:\"Subdivision:\", column6:\"Subdivision Lot Block:\", column7:\"Acres:\", column8:\"Luc:\", column9:\"Luc Description:\", column10:\"Zoning:\", column11:\"Future Land Use:\", column12:\"Section Index:\", column13:\"Flood Zone:\", column14:\"Flood Way:\", column15:\"Flood Map:\", column16:\"Impact Fee Dist:\", column17:\"Historic:\", column18:\"Overlays:\", column19:\"Watershed:\", column20:\"Fire District:\", column21:\"Evacuation Zone:\", column22:\"Special Areas:\", column23:\"School Svc Area:\", column24:\"Frontage:\"}, result1:{'Parcel Id:':\"4193610054\", 'Primary Address:':\"2702 19TH AVE W BR\", 'Owner:':\"ARRIAGA, MATTHEW F\", 'Secondary Owner:':\"ARRIAGA, BROOKE\", 'Commissioner:':\"John Chappie\", 'Subdivision:':\"GREENWOOD HEIGHTS PB2/106\", 'Subdivision Lot Block:':\"11 B\", 'Acres:':\"0.3302\", 'Luc:':\"0100\", 'Luc Description:':\"SINGLE FAMILY RESIDENTIAL\", 'Zoning:':\"CITY\", 'Future Land Use:':\"CITY\", 'Section Index:':\"S34 T34 R17\", 'Flood Zone:':\"X\", 'Flood Way:':\"N\", 'Flood Map:':\"327_C\", 'Impact Fee Dist:':\"A - SW\", 'Historic:':\"URBAN-A\", 'Overlays:':\"NONE\", 'Watershed:':\"NONE\", 'Fire District:':\"Bradenton\", 'Evacuation Zone:':\"NONE\", 'Special Areas:':\"NONE\", 'School Svc Area:':\"SSA-4\", 'Frontage:':\"102.2\"}, resultcolumns1:{column0:\"Parcel Id:\", column1:\"Primary Address:\", column2:\"Owner:\", column3:\"Secondary Owner:\", column4:\"Commissioner:\", column5:\"Subdivision:\", column6:\"Subdivision Lot Block:\", column7:\"Acres:\", column8:\"Luc:\", column9:\"Luc Description:\", column10:\"Zoning:\", column11:\"Future Land Use:\", column12:\"Section Index:\", column13:\"Flood Zone:\", column14:\"Flood Way:\", column15:\"Flood Map:\", column16:\"Impact Fee Dist:\", column17:\"Historic:\", column18:\"Overlays:\", column19:\"Watershed:\", column20:\"Fire District:\", column21:\"Evacuation Zone:\", column22:\"Special Areas:\", column23:\"School Svc Area:\", column24:\"Frontage:\"}");
 
                    FileOutputStream fileOut = new FileOutputStream("C:\\Documents and Settings\\jpace\\Desktop\\Results\\export.xls");
                    doSpreadsheets(fileOut, data, filename, types );


fileOut.close();

   }

catch(Exception e)
{
System.out.println("Exception occured in main" +e);
e.printStackTrace();
    }
        }










        public static void doSpreadsheets (OutputStream out, JSONObject data, String filename, String[] type) throws FileNotFoundException, IOException
 {
       //     System.out.println("in here");
  //          FileInputStream inputStream = new FileInputStream(file);
 HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("GIS Results List");
            HSSFRow row;
            int k=0;
      //      JSONObject rawJson = data;
            
            JSONObject dataJson = data;
   //         System.out.println(dataJson.names().toString());
//        try {
//            System.out.println("title: "+dataJson.getString("title"));
//        } catch (JSONException ex) {
//            Logger.getLogger(ResultListtoExcel.class.getName()).log(Level.SEVERE, null, ex);
//        }
 //           String filename = rawJson.getString("filename");
 //           System.out.println(filename);
            List<String> columnHeaders = new ArrayList<String>();
            List<String> columnHeadersTemp = new ArrayList<String>();
            List<String> dataTemp = new ArrayList<String>();
            int rowCounter=0;
            int titleCounter=0;
            if(dataJson != null) {


       
          for (int i =0; i<dataJson.names().length()/2;i++){
              JSONObject resultItemData = dataJson.optJSONObject("result"+i);
              JSONObject resultItemHeaders = dataJson.optJSONObject("resultcolumns"+i);
//              JSONObject resultItemData = dataJson.optJSONObject(results.getString(i));
//              System.out.println(resultItemHeaders.toString());
              if(resultItemData!=null){
              for (int j = 0; j<resultItemHeaders.length(); j++){
                    try {
                        if (j == 0) {
                            //reset headers
                            columnHeadersTemp = new ArrayList<String>();
                            dataTemp = new ArrayList<String>();
                        }
                        columnHeadersTemp.add(resultItemHeaders.getString("column" + j));
                        dataTemp.add(resultItemData.getString(resultItemHeaders.getString("column" + j)));
//                System.out.println(columnHeadersTemp.toString());
                    } catch (JSONException ex) {
                        Logger.getLogger(ResultListtoExcel.class.getName()).log(Level.SEVERE, null, ex);
                    }
//                System.out.println(columnHeadersTemp.toString());
                }
                if (columnHeaders.equals(columnHeadersTemp)==false||i==0){
                    if(i!=0){
                    rowCounter++;
                    }
                         row = sheet.createRow(rowCounter);
                         rowCounter++;
                        columnHeaders=columnHeadersTemp;
                        for (int l=0; l<columnHeaders.size();l++){
                        HSSFCell cell = row.createCell(l);
//                        System.out.println("type in excel: "+type.toString());
//                        System.out.println("length: "+type.length);
//                        System.out.println("titleCounter: "+titleCounter);
//                        System.out.println("check2: "+(type.length-1>=titleCounter));
                        if(("Title Field").equals(columnHeaders.get(l))&&(type.length-1>=titleCounter)){
                          cell.setCellValue(new HSSFRichTextString(type[titleCounter].replace("_"," ")+":"));
                          titleCounter++;
                           }else{
                          cell.setCellValue(new HSSFRichTextString(columnHeaders.get(l).toString()));
                          }
                        }
                        if(columnHeaders.size()>1){
                        row = sheet.createRow(rowCounter);
                        rowCounter++;
                            for (int l=0; l<columnHeaders.size();l++){
                            HSSFCell cell = row.createCell(l);
                          if(("null").equals(dataTemp.get(l).toString())){
                          cell.setCellValue(new HSSFRichTextString(""));
                          }else{
                          cell.setCellValue(new HSSFRichTextString(dataTemp.get(l).toString()));
                          }
                        }}
                }else{
                   row = sheet.createRow(rowCounter);
                   rowCounter++;
                    for (int l=0; l<columnHeaders.size();l++){
                        HSSFCell cell = row.createCell(l);
                         if(("null").equals(dataTemp.get(l).toString())){
                          cell.setCellValue(new HSSFRichTextString(""));
                          }else{
                          cell.setCellValue(new HSSFRichTextString(dataTemp.get(l).toString()));
                          }
                        }
                   
                }
              for (int l=0; l<columnHeaders.size();l++){
                       sheet.autoSizeColumn(l);
                        }

          }}

  }
        try {
            wb.write(out);
        } catch (IOException ex) {
            Logger.getLogger(ResultListtoExcel.class.getName()).log(Level.SEVERE, null, ex);
        }




   }
}

The resultant excel spreadsheet looks like the attached forum.xls spreadsheet.

This query was a result of an identify of 2 layers.

JeffPace · ‎07-03-2012

The key was making the results data really obnoxious, i.e. adding the column headings. This was because

1. JSON is not sorted, so could not rely on order
2. Lots of nulls in the data, wanted to make sure right data was written to the right column.

The formatting utility is my worst code of all (since i have no idea how to do a regular expression) but it is still really fast. It takes data that is in table format and writes it out for the servlet.

org.mymanatee.common.util.exportExcel=function(json, value){
    var excel = {};
    var j=0;
    var resultsItems = {};
            //                             alert(json.toSource());
            dojo.forEach(json, function(f){
                   //             alert(f.params.toSource());

                if (f.params.graphic){
                    var dataArray = [];
                    var dataJson={};

                    var columnJson={};
                    var temp = f.params.graphic.attributes.content;
                    if(!temp){
                        temp = f.params.content;
                    }
                     temp = temp.replace(/<table><\/table>/g,"");
                            temp = temp.replace(/<table><tr><td>/g,"");
                            temp = temp.replace(/<tr style="display:none;"><td>/g,"");
                           temp = temp.replace(/<\/td><td>/g,",,,");
                           temp = temp.replace(/<\/td><\/tr><tr><td>/g,",,,");
                           temp = temp.replace(/<\/td><td>/g,",,,");
                           temp = temp.replace(/<\/td><\/tr><\/table>/g,"");
                           temp = temp.replace(/<\/td><\/tr>/g,",,,");
                           temp = temp.replace(/<table>/g,"");
                    //                 temp = temp.replace(/'/g,"\"");
                    dataArray=temp.split(",,,");
                    dataArray.unshift("Title Field",f.params.title );
                    for (var i = 0; i<dataArray.length; i=i+2){

                        dataJson[dataArray]= dataArray[i+1];
                        columnJson["column"+i/2]=dataArray;
                    }

                    resultsItems["result"+j]=dataJson;
                    resultsItems["resultcolumns"+j]=columnJson;

                    j++;
                }else{
                    var title = f.params.title;
                    var item = {};
                    item[title]=title;
                    resultsItems["result"+j]=item;
                    resultsItems["resultcolumns"+j]={
                        "column0":title
                    };
                    j++;
                }
            });

            excel.data=JSON.stringify(resultsItems);
            if (value.length>0){
                excel.filename=value+".xls";
            }
            else{
                excel.filename="export.xls";
            }
      return excel;
};

SamirGambhir · ‎07-04-2012

Hi Jeff,
Wow! This is along piece of code. Let me go through it and understand it. I'll also try to see if I can make it work for my situation. I really appreciate you sharing this code.

I have two similar issues with my application which might require server-side coding. I need to print my map as a layout and also, I would like to export the map display as an image. I am using ArcGIS Server 10.0 which does not has a Print widget, so I am not sure how to make this work. Any suggestions will be helpful.
Thanks
Samir

SamirGambhir · ‎07-04-2012

Hi Jeff,
My query result is an array of numbers that need to be sliced based on the number of features and number of attributes for which I run the query. Not sure how to convert the array into a JSON format. Can I send you my code through a private message so you can look at it and suggest what I need to do to make it work? I'll really appreciate your help.
Thanks
Samir