AP.NET 读取Excel文件并绑定至GridView控件(过滤空白行)

AP.NET 读取Excel文件并绑定至GridView控件(过滤空白行)

创建数据存储类

// Excel表格数据列对应的字段
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前端代码及对应字段绑定，绑定字段为数据存储类对应的字段

// 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文件数据及数据绑定

// 后台读取Excel文件数据及数据绑定
protected void Button1_Click(object sender, EventArgs e)
        {          
            if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string filename = FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
            string savePath = Server.MapPath(("~/Uploadfiles//NO//") + filename);//Server.MapPath 获得虚拟服务器相对路径
            DataTable ExcelTable = new DataTable();
            FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在
            DataSet ds = new DataSet();
            //Excel的连接
            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();//获取 Excel 的表名，默认值是sheet1
            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;//行数

            //定义二维数组存储 Excel 表中读取的数据
            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++)
                {                   
                        //将Excel表中的数据存储到数组
                        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();
                    }                                     
                }
                //如果NO为空，则忽略该记录
                if (upload.NO.Trim() == "&nbsp;" || upload.NO.ToString().Trim() == null||  upload.NO.ToString().Trim() == "")
                {
                    continue;
                }
                else
                {
                    list.Add(upload);
                }
            }
            GridView2.DataSource = list;
            GridView2.DataBind();
        }