c#读取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

巴特西

c#读取Excel数据到Gridview

最新文章

热门文章