读取Excel数据到Gridview

 

#region 读取Excel数据到Gridview

    public void ReadExcel(string sExcelFile, GridView dgBom)
    {

　　　　DataTable ExcelTable;
        DataSet ds = new DataSet();

        //Excel的连接
        OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";" + "Extended Properties=Excel 8.0;");
        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];

        ArrayList list = new ArrayList();

        for (int i = 0; i < ExcelTable.Rows.Count; i++)
        {
            SupermarketVO vo = new SupermarketVO();

            for (int j = 1; j < ExcelTable.Columns.Count; j++)
            {
                //将Excel表中的数据存储到数组
                storedata[i, j] = ExcelTable.Rows[i][j].ToString();

                if (j == 1)
                {
                    vo.Sup_nm = ExcelTable.Rows[i][j].ToString();
                }
                else if (j == 2)
                {
                    vo.Sup_linker = ExcelTable.Rows[i][j].ToString();
                }
                else if (j == 3)
                {
                    vo.Phone = ExcelTable.Rows[i][j].ToString();
                }
            }

            //如果名称、联系人、电话都为空，则忽略该记录
            if (vo.Sup_nm.Trim() == "" && vo.Sup_linker.Trim() == "" && vo.Phone.Trim() == "")
            {
                continue;
            }
            else
            {
                list.Add(vo);
            }
        }

        //判断记录数，以便于处理空记录时的显示
        if (list.Count < 1)
        {
            SupermarketVO vo = new SupermarketVO();
            list.Add(vo);

            //设置空记录时的显示（包含表头显示）和绑定记录
            GridviewControl.GridViewDataBind(dgBom, list);
        }
        else
        {
            dgBom.DataSource = list;
            dgBom.DataBind();
        }

        LblErrorInfo.Text = "导入操作已经完成。";
    }

    #endregion