Apr 03, 2017, 10:04 AM
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 :
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();
}
}
}