C# OleDb导入excel表数据到listview以及对应修改excel表

来源：互联网发布：仿真优化软件编辑：程序博客网时间：2024/06/05 21:25

在网上面看到很多将OleDb导入导出excel的操作，现在自己总结下：

因为我需要循环将修改的listview值修改对应的excel值所以我将OleDbCommand con设置成全局变量。

        //导入g_loadUIDlistView修改的第5项即为烧入实时状态项写入excel表        private bool LoadDataIntoExcelByIndex(string data, int row)        {            if (con.State.ToString() == "Open")            {                string rowstring = row.ToString();                try                {                    string sqlCreate = "UPDATE [" + uidsheet + "] SET 状态='" + data + "' WHERE ID='" + rowstring + "'";                                      OleDbCommand cmd = new OleDbCommand(sqlCreate, con);                    cmd.ExecuteNonQuery();                    return true;                }                catch (System.Exception ex)                {                    MessageBox.Show(ex.Message, "更新数据失败");                    return false;                }            }            else            {                MessageBox.Show("操作excel文件失败，请先点击清空按键重新导入数据", "错误提示");                return false;            }                       }

        //导入excel数据到listview        private void b_loadIntoList_Click(object sender, EventArgs e)        {            //判断烧入的excel表是否存在            if (System.IO.File.Exists(g_burn_filepath.Text.ToString()))            {                g_loadUIDlistView.Items.Clear();                //string DBString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + g_burn_filepath.Text + ";Extended Properties=Excel 12.0";                string DBString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + g_burn_filepath.Text + ";" + "Extended Properties=Excel 8.0;";                con = new OleDbConnection(DBString);                //如果文件已经打开则将excel关闭                if (con.State.ToString() == "Open")                {                    con.Close();                }                 con.Open();                System.Data.DataTable datatable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                //获取表单，原始的是：Sheet1，Sheet2，Sheet3                foreach (DataRow row in datatable.Rows)                {                    //获取表单的名字                    string sheet = row["TABLE_NAME"].ToString();                    OleDbDataAdapter ole = new OleDbDataAdapter("select * from [" + sheet + "]", con);                    DataSet ds = new DataSet();                    ole.Fill(ds);                    if (sheet.CompareTo("UID$") == 0)                    {                        uidsheet = sheet;                        List<ListViewItem> items = new List<ListViewItem>();                        ListViewItem item = null;                        //输出表格里面的内容                        foreach (DataRow col in ds.Tables[0].Rows)                        {                            item = new ListViewItem();                            item.SubItems[0].Text = col[0].ToString();                            for (int x = 1; x < ds.Tables[0].Columns.Count; x++)                            {                                item.SubItems.Add(col[x].ToString());                            }                            g_loadUIDlistView.Items.Add(item);                        }                        g_loadUIDlistView.Items[0].Selected = true;//默认选择第一行                        g_loadUIDlistView.Items[0].Focused = true;                    }                    else if (sheet.CompareTo("基本参数$") == 0)                    {                        //输出表格里面的内容                        foreach (DataRow col in ds.Tables[0].Rows)                        {                            g_burn_pushserver.Text = col[ds.Tables[0].Columns.Count - 1].ToString();                            g_burn_product.Text = col[ds.Tables[0].Columns.Count - 3].ToString();                            g_burn_companyName.Text = col[ds.Tables[0].Columns.Count - 2].ToString();                        }                    }                }                //con.Close();            }        }

0 0