1. #1
    Sencha User
    Join Date
    Aug 2011
    Posts
    25
    Vote Rating
    0
    geovannefarell is on a distinguished road

      0  

    Default Unanswered: how to create report from grid panel

    Unanswered: how to create report from grid panel


    hello...
    i want to create report from my grid panel,
    but i don't know how to create it,
    can you help me ?

  2. #2
    Sencha User
    Join Date
    Jan 2011
    Location
    Lima, Peru
    Posts
    43
    Answers
    5
    Vote Rating
    5
    poseidonjm is on a distinguished road

      0  

    Default


    If you need to export your data from grid to excel I recommend do it on server side.
    In Java I use Apache POI.
    Code:
    XlsProxy<PagingLoadResult<Parametro>> xlsProxy = new XlsProxy<PagingLoadResult<Parametro>>() {
    
                @Override
                protected void load(final Object loadConfig, final ExcelParameter xparam,
                        final AsyncCallback<PagingLoadResult<Parametro>> callback) {
                    new RPCCall<PagingLoadResult<Parametro>>(true) {
    
    
                        @Override
                        public void onFailure(Throwable caught) {
                            callback.onFailure(caught);
                        }
    
    
                        @Override
                        public void onSuccess(PagingLoadResult<Parametro> result) {
                            callback.onSuccess(result);
                        }
    
    
                        @Override
                        protected void callService(
                                AsyncCallback<PagingLoadResult<Parametro>> cb) {
                            service.paginateParametro((FilterPagingLoadConfig) loadConfig,
                                    xparam, cb);
                            
                        }
                    }.retry(3);
                }            
            };
            
            xfiltroDialog = new FiltrarExcelDialog<PagingLoadResult<Parametro>>(grid, loader, xlsProxy, this);
            xfiltroDialog.setFileName("parametros.xls");
            xfiltroDialog.setSheetName("Hoja1");
    xfiltroDialog.show();
    /***Dialog Filter columns to export***/
    Code:
    public class FiltrarExcelDialog<D> extends MyDialog{
    
    
        private DualListField<ExcelColumn> columnList;
        private ListField<ExcelColumn> origenList;
        private ListField<ExcelColumn> destinoList;
        
        private ListStore<ExcelColumn> store;
        private ListStore<ExcelColumn> store1;
        private ArrayList<ExcelColumn> columns;
        private ColumnModel cm;
        
        private String fileName;
        private String sheetName;
        private Frame frame;    
        
        public FiltrarExcelDialog(final Grid<BeanModel> grid, final PagingLoader<PagingLoadResult<ModelData>> loader, final XlsDataProxy<D> xlsProxy, GridPanel gridPanel) {
            super("Seleccionar Columnas");
            setSize(500, 320);        
    
    
            frame = new Frame();
            frame.setVisible(false);
            gridPanel.add(frame);        
            
            this.cm = grid.getColumnModel();
            
            store = new ListStore<ExcelColumn>();
            
            saveButton.setText(constants.enviar());
            saveButton.setToolTip("");
            saveButton.setIcon(IconHelper.createPath("images/icons/play.png"));
            
            columnList = new DualListField<ExcelColumn>();
            columnList.setMode(Mode.INSERT);
            columnList.setHideLabel(true);
            columnList.setHeight(240);
            
            origenList = columnList.getFromList();
            origenList.setDisplayField("columnHeader");        
            
            origenList.setStore(store);
            origenList.addSelectionChangedListener(new SelectionChangedListener<ExcelColumn>() {
                        @Override
                        public void selectionChanged(SelectionChangedEvent<ExcelColumn> se) {
                            
                        }
                    });
            
            store1 = new ListStore<ExcelColumn>();
    
    
            destinoList = columnList.getToList();
            destinoList.setDisplayField("columnHeader");
            destinoList.setStore(store1);    
        
        
        add(columnList, 100, 0);
        
        addListener(MyDialogEvents.onCancel, new Listener<DialogEvent>() {
            @Override
            public void handleEvent(DialogEvent be) {
                hide();
            }
    
    
        });
        
        addListener(MyDialogEvents.onSave, new Listener<DialogEvent>() {        
            @Override
            public void handleEvent(DialogEvent be) {
                ArrayList<ExcelColumn> columnList = (ArrayList<ExcelColumn>) destinoList.getStore().getModels();
                if(columnList.size() > 0){
                    
                    ExcelParameter xparam = new ExcelParameter();
                    xparam.setExcelColumn(columnList);
                    xparam.setFileName(fileName);
                    xparam.setSheetName(sheetName);
                    
                    final FilterPagingLoadConfig loadConfig = (FilterPagingLoadConfig) grid.getStore().getLoadConfig();
                    loadConfig.setLimit(loader.getTotalCount());
                    
                    saveButton.setEnabled(false);
                    xlsProxy.loads(loadConfig, xparam, new AsyncCallback<D>() {
    
    
                        @Override
                        public void onFailure(Throwable caught) {
                            
                        }
    
    
                        @Override
                        public void onSuccess(Object result) {
                            Random random = new Random();                        
                            frame.setUrl(GWT.getHostPageBaseURL()+"files/"+fileName+"?r="+random.nextInt());                        
                        }
                    });
                    hide();                
                }
            }
    
    
        });
        
        }
        
        @Override
        public void show() {
            store.removeAll();
            store1.removeAll();
            fillStore();    
            
            super.show();        
        }
        
        private void fillStore(){
            columns = new ArrayList<ExcelColumn>();
            ExcelColumn ec;
            for(int i = 0; i < cm.getColumnCount(); i++){
                
                if(cm.getColumnHeader(i) != null){
                    int w = cm.getColumnWidth(i);                
                    
                    ec = new ExcelColumn();
                    ec.setColumnWidth(w);
                    ec.setColumnHeader(cm.getColumnHeader(i));
                    ec.setDataIndex(cm.getDataIndex(i));                    
                    
                    columns.add(ec);
                    
                }
                
            }
            store.add(columns);
        }
        
        public void setFileName(String fileName) {
            this.fileName = fileName;
        }
        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }
    
    }
    /*****Bean to send info about column to server******/
    Code:
    
    public class ExcelColumn extends BaseModel implements Serializable {
    
    
        private static final long serialVersionUID = -6267995925284338304L;
        
        public String getColumnHeader() {
            return get("columnHeader");
        }
        public void setColumnHeader(String columnHeader) {
            set("columnHeader", columnHeader);
        }
        public String getDataIndex() {
            return get("dataIndex");
        }
        public void setDataIndex(String dataIndex) {
            set("dataIndex", dataIndex);
        }
        public Integer getColumnWidth() {
            return get("columnWidth");
        }
        public void setColumnWidth(Integer columnWidth) {
            set("columnWidth", columnWidth);
        }
        
        
    }
    To send data about column
    Code:
    public class ExcelParameter implements Serializable {
    
    
        private static final long serialVersionUID = 7190134320316378109L;
    
    
        private Integer totalWidthInPixels;
        private String fileName;
        private String sheetName;
        
        private ArrayList<ExcelColumn> excelColumn;    
        
        public ExcelParameter(){        
            excelColumn = new ArrayList<ExcelColumn>();        
        }
    
    
        public Integer getTotalWidthInPixels() {
            return totalWidthInPixels;
        }
    
    
        public void setTotalWidthInPixels(Integer totalWidthInPixels) {
            this.totalWidthInPixels = totalWidthInPixels;
        }    
        
        public String getFileName() {
            return fileName;
        }
        public void setFileName(String fileName) {
            this.fileName = fileName;
        }
        
        public ArrayList<ExcelColumn> getExcelColumn() {
            return excelColumn;
        }
        public void setExcelColumn(ArrayList<ExcelColumn> excelColumn) {
            this.excelColumn = excelColumn;
        }
        public String getSheetName() {
            return sheetName;
        }
        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }
        
    }
    /***Util classes***/
    Code:
    public interface XlsDataProxy<D> {
    
         public void loads(Object loadConfig, ExcelParameter xparam, AsyncCallback<D> callback);
    }
    Code:
    public abstract class XlsProxy<D> implements XlsDataProxy<D> {
    
    
        public void loads(final Object loadConfig,    ExcelParameter xparam,
                  final AsyncCallback<D> callback) {
                load(loadConfig, xparam, new AsyncCallback<D>() {
    
    
                  public void onFailure(Throwable caught) {
                    callback.onFailure(caught);
                  }
    
    
                  
                  @SuppressWarnings("unchecked")
                public void onSuccess(Object result) {
                    try {                  
                        D data = (D) result;                   
                        callback.onSuccess(data);
                    } catch (Exception e) {
                      callback.onFailure(e);
                    }
                  }
    
    
                });
              }
        protected abstract void load(Object loadConfig, ExcelParameter xparam, AsyncCallback<D> callback);
    }
    Class on server that use POI

    Code:
    import java.io.FileOutputStream;import java.io.IOException;
    import java.lang.reflect.Field;
    import java.util.Date;
    import java.util.List;
    
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    
    
    
    public class ExcelSheetWriter {
    
    
        @SuppressWarnings("deprecation")
        public static void writeExcel(List<?> data, ExcelParameter xparam, Class<?> type) {
            
            FileOutputStream fileOutputStream = null;
            HSSFWorkbook sampleWorkbook = null;
            HSSFSheet sampleDataSheet = null;
            try
            {
            /**
            * Create a new instance for HSSFWorkBook class and create a
            * sample worksheet using HSSFSheet class to write data.
            */
            sampleWorkbook = new HSSFWorkbook();
            sampleDataSheet = sampleWorkbook.createSheet(xparam.getSheetName());
    
    
            //sampleDataSheet.setColumnWidth(0, 10000);
    
    
            /**
            * Create two rows using HSSFRow class, where headerRow denotes the
            * header and the dataRow1 denotes the cell data.
            */
            HSSFRow headerRow = sampleDataSheet.createRow(0);
            
            
            HSSFCellStyle cellStyle = setHeaderStyle(sampleWorkbook);
            /**
            * Call the setHeaderStyle method and set the styles for the
            * all the three header cells.
            */
    
    
            
            for(int i = 0; i< xparam.getExcelColumn().size(); i++){             
                HSSFCell headerCell = headerRow.createCell(i);
                headerCell.setCellStyle(cellStyle);
                headerCell.setCellValue(xparam.getExcelColumn().get(i).getColumnHeader());
            }
            /**
            * Set the cell value for all the data rows.
            */
    
    
    
    
            HSSFRow dataRow;
            Field privateField;
            HSSFCell rowCell = null;
            for(int i = 0; i < data.size(); i++){
                dataRow = sampleDataSheet.createRow(i+1);
                for(int j = 0; j< xparam.getExcelColumn().size(); j++){
                    privateField = type.getDeclaredField(xparam.getExcelColumn().get(j).getDataIndex());
                    privateField.setAccessible(true);
                                     
                    if(privateField.getType() == String.class){
                        rowCell = dataRow.createCell(j); 
                        rowCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        rowCell.setCellValue((String) privateField.get(data.get(i)));
                    }else if(privateField.getType() == Integer.class){
                        rowCell = dataRow.createCell(j); 
                        rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        rowCell.setCellValue((Integer) privateField.get(data.get(i)));
                    }else if(privateField.getType() == Double.class){
                        rowCell = dataRow.createCell(j); 
                        rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        rowCell.setCellValue((Double) privateField.get(data.get(i)));
                    }else if(privateField.getType() == Date.class){
                        cellStyle = sampleWorkbook.createCellStyle();
                        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/MM/yyyy"));
                           
                        rowCell = dataRow.createCell(j);                     
                        rowCell.setCellValue((Date) privateField.get(data.get(i)));
                        rowCell.setCellStyle(cellStyle);
                    }else if(privateField.getType() == Boolean.class){
                        rowCell = dataRow.createCell(j); 
                        rowCell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                        rowCell.setCellValue((Boolean) privateField.get(data.get(i)));
                    }
                    
                }
            }
    
    
            for(int i = 0; i< xparam.getExcelColumn().size(); i++){
                sampleDataSheet.autoSizeColumn((short)i); 
            }        
                   
            fileOutputStream = new FileOutputStream("files/"+xparam.getFileName());
            sampleWorkbook.write(fileOutputStream);
            }
            catch (Exception ex)
            {
            ex.printStackTrace();
            }
            finally
            {
            /**
            * Close the fileOutputStream.
            */
            try
            {
            if (fileOutputStream != null)
            {
            fileOutputStream.close();
            }
            }
            catch (IOException ex)
            {
            ex.printStackTrace();
            }
            }
            
        }
        
        /**
        * This method is used to set the styles for all the headers
        * of the excel sheet.
        * @param sampleWorkBook - Name of the workbook.
        * @return cellStyle - Styles for the Header data of Excel sheet.
        */
        private static HSSFCellStyle setHeaderStyle(HSSFWorkbook sampleWorkBook)
        {
        HSSFFont font = sampleWorkBook.createFont();
        font.setFontName(HSSFFont.FONT_ARIAL);
    
    
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cellStyle = sampleWorkBook.createCellStyle();
        cellStyle.setFont(font);
        return cellStyle;
        }
    }
    Use it on server
    Code:
    List<Parametro> data = null;
    ExcelParameter xparam = null;
    //get data
    ExcelSheetWriter.writeExcel(data, xparam, Parametro.class);
    Where data is your list of objects

Thread Participants: 1

Tags for this Thread