c#dataGridView与access数据库及其增删查改导出报表等
假如我们要写⼀个实验室设备管理的⼀个简单程序，⽤access数据库可以说是最简单便捷的选择了，Access 是Office⾥⾯的⼀个
组件是office⾃带的数据库。
⽐如说要做成这样的形式，有数据的查询，编辑，添加，删除，⽣成报表等功能。 先⽤winform创建⼀个界⾯，设计好表格的列属性，
其中⼩组和使⽤状态是⽤的下拉列表，表格是dataGridView。
主要代码及注释：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
//using Microsoft.Office.Interop.Word;
/
/using Microsoft.Office.Interop.Excel;
using System.Threading;
namespace ComputerManage
{
public partial class Form1 : Form
{
OleDbConnection conn = new OleDbConnection("Data Source=" + System.Windows.Forms.Application.StartupPath + "\\config\\cinfor.mdb;Provider=Microso Form2 dataEdit = new Form2();
// int rowIndex = -1;
// bool serchFlag = false;
public Form1()
{
InitializeComponent();
//初始化
InitCombo();
InitGridView();
}
//初始化下拉列表
private void InitCombo()
{
thisboGroup.Items.Add("全部");
thisboGroup.Items.Add("502");
thisboGroup.Items.Add("508");
thisboGroup.Items.Add("510");
thisboGroup.Items.Add("506");
thisboGroup.Items.Add("617");
thisboGroup.Items.Add("503");
thisboGroup.Items.Add("530");
thisboGroup.Items.Add("614");
thisboState.Items.Add("全部");
thisboState.Items.Add("使⽤中");
thisboState.Items.Add("停⽤");
}
/
/初始化gridview1
private void InitGridView()
{
//不显⽰最后⼀⾏的空⽩⾏
dataGridView1.AllowUserToAddRows = false;
DataTable dt = new DataTable();
string allInformation = "select * from detailInfor order by ID";
dt = DataTableExcute(allInformation);
dataGridView1.DataSource = dt;
}
//得到所选⾏索引
public int getSelectedIndex()
{
int count = this.dataGridView1.Rows.Count;
for (int i = 0; i < count - 1; i++)
{
if (dataGridView1.Rows[i].Selected == true)
return i;
}
return -1;
}
//查询得到datatable
public DataTable DataTableExcute(string cmdstr)
{
OleDbCommand cmd = new OleDbCommand(cmdstr, conn);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd); DataTable datatable = new DataTable();
DataSet dataset = new DataSet();
try
{
conn.Open();
dataAdapter.Fill(dataset);
if (dataset.Tables[0].Rows.Count > 0)
{
datatable = dataset.Tables[0];
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
conn.Close();
cmd.Dispose();
dataAdapter.Dispose();
}
return datatable;
}
//对数据表的操作
public bool DataSheetOperate(string cmdstr)
{
OleDbCommand cmd = new OleDbCommand(cmdstr, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch
{
return false;
}
finally
{
conn.Close();
cmd.Dispose();
}
return true;
}
//刷新⼀⾏
public void GridViewRowRefresh(string newName,string newDeclare,string newUser,string newGroup,string newGettime,string newUpdate,string newHisuse {
int rowIndex = getSelectedIndex();
dataGridView1.Rows[rowIndex].Cells[1].Value = newName;
dataGridView1.Rows[rowIndex].Cells[2].Value = newDeclare;
dataGridView1.Rows[rowIndex].Cells[3].Value = newUser;
dataGridView1.Rows[rowIndex].Cells[4].Value = newGroup;
dataGridView1.Rows[rowIndex].Cells[5].Value = newGettime;
dataGridView1.Rows[rowIndex].Cells[6].Value = newUpdate;
dataGridView1.Rows[rowIndex].Cells[7].Value = newHisuser;
dataGridView1.Rows[rowIndex].Cells[8].Value = newState;
}
//下拉列表动态绑定
//public void initCombobox()
//{
// string cmdstr = "select ID,c_name from baseInfor";
// DataTable dt = new DataTable();
// dt = DataTableExcute(cmdstr);
// if (dt.Rows.Count > 0)
/
/ {
// // MessageBox.Show(dt.Rows.Count.ToString());
// thisboGroup.DisplayMember = "c_name";
// thisboGroup.ValueMember = "ID";
// thisboGroup.DataSource = dt;
// }
// else
// {
// MessageBox.Show("Combobox get data error!");
// }
//}
/
/查询
private void button1_Click(object sender, EventArgs e)
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string cmdstr = "";
string group = thisboGroup.Text;
string state = thisboState.Text;
string stateValue = null;
if (state == "使⽤中")
stateValue = "1";
else
stateValue = "0";
string user = Box1.Text.ToString();
try
{
if ((group == "" || group == "全部") && (state == "" || state == "全部") && user == "")
{
cmdstr = "select * from detailInfor order by ID";
}
else if ((group == "" || group == "全部") && (state == "" || state == "全部"))
{
cmdstr = "select * from detailInfor where d_user='" + user + "'";
}
else if ((state == "" || state == "全部") && user == "")
{
cmdstr = "select * from detailInfor where d_group='" + group + "'";
}
else if ((group == "" || group == "全部") && user == "")
{
cmdstr = "select * from detailInfor where d_state=" + stateValue + "";
}
else if(group == "" || group == "全部")
{
cmdstr = "select * from detailInfor where d_user='" + user + "' and d_state=" + stateValue + "";
}
else if(state == "" || state == "全部")
{
cmdstr = "select * from detailInfor where d_user='" + user + "' and d_group='" + group + "'";
}
else if (user == "")
{
cmdstr = "select * from detailInfor where d_group='" + group + "' and d_state=" + stateValue + "";
}
else
{
cmdstr = "select * from detailInfor where d_group='" + group + "' and d_state=" + stateValue + " and d_user='" + user+"'"; }
dt = DataTableExcute(cmdstr);
if (dt.Rows.Count == 0)
{
MessageBox.Show("没有相关信息！，请重新选择查询条件");
}
else
{
this.dataGridView1.DataSource = dt;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
dt.Dispose();
}
}
}
//⽣成报表
private void button3_Click(object sender, EventArgs e)
{
Thread generateMyWord = new Thread(new ThreadStart(OutputAsExcelFile));
//设置为后台线程
generateMyWord.IsBackground = true;
gridview不显示//开启线程
generateMyWord.Start();
}
private void OutputAsExcelFile()
{
//将datagridView中的数据导出到⼀张表中
DataTable tempTable = poreDataToTable(this.dataGridView1);
//导出信息到Excel表
Microsoft.Office.Interop.Excel.ApplicationClass myExcel;
Microsoft.Office.Interop.Excel.Workbooks myWorkBooks;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet;
char myColumns;
Microsoft.Office.Interop.Excel.Range myRange;
object[,] myData = new object[500, 35];
int i, j;//j代表⾏,i代表列
myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//显⽰EXCEL
myExcel.Visible = true;
if (myExcel == null)
{
MessageBox.Show("本地Excel程序⽆法启动!请检查您的Microsoft Office正确安装并能正常使⽤", "提⽰");
return;
}
myWorkBooks = myExcel.Workbooks;
myWorkBook = myWorkBooks.Add(System.Reflection.Missing.Value);
myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets[1];
myColumns = (char)(tempTable.Columns.Count + 64);//设置列
myRange = _Range("A4", myColumns.ToString() + "5");//设置列宽
int count = 0;
//设置列名
foreach (DataColumn myNewColumn in tempTable.Columns)
{
myData[0, count] = myNewColumn.ColumnName;
count = count + 1;
}
//输出datagridview中的数据记录并放在⼀个⼆维数组中
j = 1;
foreach (DataRow myRow in tempTable.Rows)//循环⾏
{
for (i = 0; i < tempTable.Columns.Count; i++)//循环列
{
myData[j, i] = myRow[i].ToString();
}
j++;
}
//将⼆维数组中的数据写到Excel中
myRange = _Resize(tempTable.Rows.Count + 1, tempTable.Columns.Count);//创建列和⾏
myRange.Value2 = myData;
myRange.EntireColumn.AutoFit();
myRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//删除前⽅空⽩三⾏
Microsoft.Office.Interop.Excel.Range deleteRng = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing]; deleteRng.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
Microsoft.Office.Interop.Excel.Range deleteRng1 = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing]; deleteRng1.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论