Blank record when import rows from excel to dynamic GridPanel

  1. #1

    Blank record when import rows from excel to dynamic GridPanel

    hello everyone,

    I have a problem when load excel file using FileUpload to my dinamyc GridPanel, number of row on the GridPanel is equal with my row on excel file, but there is just blank record to shown.

    Here is my aspx code :
    <ext:Panel ID="pnHeader" runat="server" Layout="AnchorLayout" AnchorHorizontal="100%" AnchorVertical="100%">
        <HeaderConfig TitleAlign="Left" Title="Mortgage Collateral Data Upload" TitlePosition="0"></HeaderConfig>
        <Items>
            <ext:FormPanel ID="fpMortgage" runat="server" Layout="AnchorLayout" AnchorHorizontal="100%" AnchorVertical="20%" RowSpan="20">
                <Items>
                    <ext:Panel ID="pnLeft" runat="server" Layout="AutoLayout" ColumnWidth=".5" Border="false">
                        <Items>
                            <ext:ComboBox ID="cmbDocument" runat="server" FieldLabel="Document " DisplayField="name" QueryMode="Local" Width="400" TypeAhead="true">
                                <Store>
                                    <ext:Store ID="storeDocument" Data="<%# PopulateDocument %>" runat="server" AutoDataBind="true">
                                        <Model>
                                            <ext:Model ID="modelDocument" runat="server">
                                                <Fields>
                                                    <ext:ModelField Name="value" />
                                                    <ext:ModelField Name="name" />
                                                </Fields>
                                            </ext:Model>
                                        </Model>
                                        <Reader>
                                            <ext:ArrayReader />
                                        </Reader>
                                    </ext:Store>
                                </Store>
                            </ext:ComboBox>
                            <ext:FileUploadField ID="upFile" FieldLabel="File Data " runat="server" Icon="Attach" Width="400">
                            </ext:FileUploadField>
                            <ext:Button ID="btnUpload" runat="server" Text="Upload" Icon="ArrowUp" OnDirectClick="btnUploadEvents" />
                        </Items>
                    </ext:Panel>
    
                    <ext:Panel ID="pnRight" runat="server" Layout="AnchorLayout" ColumnWidth=".5" Border="false">
                        <Items>
                            <ext:LinkButton ID="lnkTemplate" runat="server" Text="Download Template"></ext:LinkButton>
                            <ext:GridPanel
                                ID="grdData"
                                runat="server"
                                Title="Upload result should be here"
                                Layout="AnchorLayout"
                                AnchorHorizontal="100%"
                                AnchorVertical="90%"
                                ClientIDMode="Static">
                                <Store>
                                    <ext:Store
                                        ID="Store1"
                                        runat="server"
                                        OnReadData="RefreshDataSet"
                                        PageSize="10"
                                        ClientIDMode="Static">
                                        <Model>
                                            <ext:Model runat="server" ></ext:Model>
                                        </Model>
                                    </ext:Store>
                                </Store>
                                <SelectionModel>
                                    <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" Mode="Single" />
                                </SelectionModel>
                            </ext:GridPanel>
                        </Items>
                    </ext:Panel>
                </Items>
            </ext:FormPanel>
        </Items>
        </ext:Panel>
    and here is my codebehind :
    public partial class M_ParameterUpload : System.Web.UI.Page
        {
            private string fileName;
    
            public string ExcelFile
            {
                get { return fileName; }
                set { fileName = value; }
            }
    
            public DataTable myDt
            {
                get { return (DataTable)Session["myDt"]; }
                set { Session["myDt"] = value; }
            }
    
            public List<DataRow> propList
            {
                get { return (List<DataRow>)Session["maiList"]; }
                set { Session["maiList"] = value; }
            }
    
            protected void Page_Load(object sender, EventArgs e)
            {
                
            }
    
            protected void btnUploadEvents(object sender, DirectEventArgs e)
            {
                try
                {
                    string doc;
                    doc = (string)cmbDocument.Value;
    
                    string fullPath;
                    fullPath = Server.MapPath("~/Upload/");
    
                    if (upFile.HasFile)
                    {
                        if (File.Exists(fullPath + upFile.FileName))
                        {
                            File.Delete(fullPath + upFile.FileName);
                        }
    
                        upFile.PostedFile.SaveAs(fullPath + upFile.FileName);
    
                        if (upFile.PostedFile.FileName.EndsWith(".xlsx"))
                        {
                            ExcelFile = upFile.FileName;
                            GenerateGrid(fullPath + ExcelFile);
                        }
                        else
                        {
                            X.Msg.Show(new MessageBoxConfig
                            {
                                Buttons = MessageBox.Button.OK,
                                Icon = MessageBox.Icon.ERROR,
                                Title = "Failed",
                                Message = "Please select an Excel-File"
                            });
                        }
                    }
                }
                catch (Exception uploadException)
                {
                    X.Msg.Show(new MessageBoxConfig
                    {
                        Buttons = MessageBox.Button.OK,
                        Icon = MessageBox.Icon.ERROR,
                        Title = "Failed",
                        Message = uploadException.Message
                    });
                }
            }
    
            private DataTable ExcelReader(string filePath, Boolean header)
            {
                FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader;
                if (filePath.Contains(".xlsx"))
                {
                    //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    
                }  //...
                else
                {
                    //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                } //...
                //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                //DataSet result = excelReader.AsDataSet();
                //...
                //4. DataSet - Create column names from first row
                if (header)
                {
                    excelReader.IsFirstRowAsColumnNames = false;
                }
                else
                {
                    excelReader.IsFirstRowAsColumnNames = true;
                }
    
                DataSet result = excelReader.AsDataSet();
                DataTable mydt = new DataTable();
                
                //5. Data Reader methods
                //foreach (DataRow item in result.Tables[0].Rows)
                //{
                //    item[""].ToString();
                //}
    
                for (int i = 0; i <= result.Tables[0].Rows.Count - 1; i++)
                {
                    for (int j = 0; j <= result.Tables[0].Columns.Count - 1; j++)
                    {
                        if (result.Tables[0].Rows[i][j] == DBNull.Value)
                        {
                            result.Tables[0].Rows[i][j] = "-";
                            result.Tables[0].AcceptChanges();
                        }
                    }
                }
    
                mydt = result.Tables[0];
                return mydt;
            }
    
            protected void RefreshDataSet(object sender, StoreReadDataEventArgs e)
            {
                this.Store1.DataSource = propList;
                this.Store1.DataBind();
            }
    
            private void AddField(ModelField field)
            {
                if (X.IsAjaxRequest)
                {
                    this.Store1.AddField(field);
                }
                else
                {
                    this.Store1.Model[0].Fields.Add(field);
                }
            }
    
            private void GenerateGrid(string fullPath)
            {
                myDt = this.ExcelReader(fullPath, false);
                propList = myDt.Rows.Cast<DataRow>().ToList();
                myDt = this.ExcelReader(fullPath, true);
                string str;
                //Store store1 = new Store();
                //GridPanel grid = new GridPanel();
                //Ext.Net.Model model = new Model();
    
                if (X.IsAjaxRequest)
                {
                    this.Store1.RemoveFields();
                }
    
                for (int j = 0; j <= myDt.Columns.Count - 1; j++)
                {
                    str = (string)myDt.Rows[0][j];
                    if (str.Replace(" ", "").Replace("(", "").Replace(")", "").Replace("-", "").Replace("?", "").Replace(".", "") == "DateofBirth")
                    {
                        str = str.Replace(" ", "").Replace("(", "").Replace(")", "").Replace("-", "").Replace("?", "").Replace(".", "");
                        this.AddField(new ModelField(str, ModelFieldType.Date));
                    }
                    else
                    {
                        str = str.Replace(" ", "").Replace("(", "").Replace(")", "").Replace("-", "").Replace("?", "").Replace(".", "");
                        this.AddField(new ModelField(str, ModelFieldType.String));
                    }
                }
    
                Column col;
                for (int j = 0; j <= myDt.Columns.Count - 1; j++)
                {
                    str = (string)myDt.Rows[0][j];
                    col = new Column();
                    col.Text = str;
                    col.DataIndex = str.Replace(" ", "").Replace("(", "").Replace(")", "").Replace("-", "").Replace("?", "").Replace(".", "");
                    this.grdData.ColumnModel.Columns.Add(col);
                }
    
                this.Store1.RebuildMeta();
    
                this.Store1.DataSource = propList;
                this.Store1.DataBind();      
                
                //this.grdData.SelectionModel.Add(new RowSelectionModel { Mode = SelectionMode.Single });
    
                this.grdData.SetTitle(ExcelFile);
    
                if (X.IsAjaxRequest)
                {
                    this.grdData.Reconfigure();
                }
            }
        }
  2. #2
    Hello @jhon!

    Would you kindly let us know the version of Ext.NET you are using so we can move your thread to the appropriate version's forums?
    Fabrício Murta
    Developer & Support Expert
  3. #3
    ext.net 2.2.0, .NET framework 4.0

Similar Threads

  1. Import excel to Datagrid
    By xborderland in forum 1.x Help
    Replies: 3
    Last Post: Nov 03, 2016, 6:31 AM
  2. [CLOSED] Import Excel to DataGrid
    By Geovision in forum 4.x Legacy Premium Help
    Replies: 1
    Last Post: Jul 27, 2016, 8:52 PM
  3. Replies: 1
    Last Post: Oct 17, 2015, 7:50 AM
  4. [CLOSED] how to Import excel to Datagrid?
    By tobros in forum 2.x Legacy Premium Help
    Replies: 13
    Last Post: Aug 26, 2013, 12:45 PM
  5. [CLOSED] Import Excel into Grid
    By mcfromero in forum 1.x Legacy Premium Help
    Replies: 7
    Last Post: Mar 13, 2012, 2:22 PM

Tags for this Thread

Posting Permissions