<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>
{"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:"}}
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();
/* * 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); } } }
/* * 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); } } }
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; };