AP.NET 读取Excel文件并绑定至GridView控件(过滤空白行)
创建数据存储类
internal class WarehouseEntryNumber
{
public string NO { get; set; }
public string ORDERS { get; set; }
public string CTNS { get; set; }
public string CBM { get; set; }
public string ZT { get; set; }
}
GridView前端代码及对应字段绑定,绑定字段为数据存储类对应的字段
<asp:GridView ID="GridView2" runat="server" Width="100%" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="序号">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text="<%# Container.DataItemIndex + 1 %>"></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:BoundField DataField="NO" HeaderText="进仓编号" />
<asp:BoundField DataField="ORDERS" HeaderText="PO号" />
<asp:BoundField DataField="CTNS" HeaderText="箱数">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="CBM" HeaderText="体积">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="ZT" HeaderText="检测状态" NullDisplayText="未检测">
<FooterStyle Font-Bold="True" />
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#E1EDFF" ForeColor="White" HorizontalAlign="Center" />
<PagerTemplate>
<div style="text-align: center; color: blue; background-color: #E1EDFF">
<asp:LinkButton ID="cmdFirstPage" runat="server" CommandName="Page" CommandArgument="First" Enabled="<%# ((GridView)Container.Parent.Parent).PageIndex!=0 %>">首页</asp:LinkButton>
<asp:LinkButton ID="cmdPreview" runat="server" CommandArgument="Prev" CommandName="Page" Enabled="<%# ((GridView)Container.Parent.Parent).PageIndex!=0 %>">上一页</asp:LinkButton>
第<asp:Label ID="lblcurPage" ForeColor="Blue" runat="server" Text='<%# ((GridView)Container.Parent.Parent).PageIndex+1 %>'></asp:Label>页/共
<asp:Label ID="lblPageCount" ForeColor="blue" runat="server" Text='<%# ((GridView)Container.Parent.Parent).PageCount %>'></asp:Label>页
<asp:LinkButton ID="cmdNext" runat="server" CommandName="Page" CommandArgument="Next" Enabled="<%# ((GridView)Container.Parent.Parent).PageIndex!=((GridView)Container.Parent.Parent).PageCount-1 %>">下一页</asp:LinkButton>
<asp:LinkButton ID="cmdLastPage" runat="server" CommandArgument="Last" CommandName="Page" Enabled="<%# ((GridView)Container.Parent.Parent).PageIndex!=((GridView)Container.Parent.Parent).PageCount-1 %>">尾页</asp:LinkButton>
</PagerTemplate>
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
后台读取Excel文件数据及数据绑定
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;
}
string filename = FileUpload1.FileName;
string savePath = Server.MapPath(("~/Uploadfiles//NO//") + filename);
DataTable ExcelTable = new DataTable();
FileUpload1.SaveAs(savePath);
DataSet ds = new DataSet();
OleDbConnection objConn = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + savePath +
";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);
objConn.Close();
ExcelTable = ds.Tables[tableName];
int iColums = ExcelTable.Columns.Count;
int iRows = ExcelTable.Rows.Count;
string[,] storedata = new string[iRows, iColums];
System.Collections.ArrayList list = new System.Collections.ArrayList();
for (int i = 0; i < ExcelTable.Rows.Count; i++)
{
WarehouseEntryNumber upload = new WarehouseEntryNumber();
for (int j = 0; j < ExcelTable.Columns.Count; j++)
{
storedata[i, j] = ExcelTable.Rows[i][j].ToString();
if (j == 0)
{
upload.NO = ExcelTable.Rows[i][j].ToString();
}
else if (j == 1)
{
upload.ORDERS = ExcelTable.Rows[i][j].ToString();
}
else if (j == 2)
{
upload.CTNS = ExcelTable.Rows[i][j].ToString();
}
else if (j == 3)
{
upload.CBM = ExcelTable.Rows[i][j].ToString();
}
}
if (upload.NO.Trim() == " " || upload.NO.ToString().Trim() == null|| upload.NO.ToString().Trim() == "")
{
continue;
}
else
{
list.Add(upload);
}
}
GridView2.DataSource = list;
GridView2.DataBind();
}