马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?立即注册
x
做下记录,
首先插入一个dataGridView控件,两个button按钮(导入数据,导出数据),一个ComboBox(获取列标题使用),一个textbox(输入关键字),一个定位按钮(定位使用)
1,导入数据(NPOI)- 1
- 2 private void daoRuShuJu_cmd_Click(object sender, EventArgs e)
- 3 {
- 4 DataTable daNpoi = new DataTable();
- 5 string fileName = Application.StartupPath;
- 6 fileName += "\\4G模组表.xls";
- 7 string sheetName = "4G模组情况表";
- 8 bool isColumnName = true;
- 9 IWorkbook workbook;
- 10 string fileExt = Path.GetExtension(fileName).ToString();
- 11 using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
- 12 {
- 13 if (fileExt == ".xlsx")
- 14 {
- 15 workbook = new XSSFWorkbook(fs);
- 16 }
- 17 else if (fileExt == ".xls")
- 18 {
- 19 workbook = new HSSFWorkbook(fs);
- 20 }
- 21 else
- 22 {
- 23
- 24 workbook = null;
- 25 }
- 26
- 27 ISheet sheet = null;
- 28 if (sheetName != null && sheetName != "")
- 29 {
- 30 sheet = workbook.GetSheet(sheetName);
- 31 if (sheet == null)
- 32 {
- 33 sheet = workbook.GetSheetAt(0);
- 34 }
- 35
- 36 }
- 37 else
- 38 {
- 39 sheet = workbook.GetSheetAt(0);
- 40 }
- 41
- 42 IRow header = sheet.GetRow(sheet.FirstRowNum);
- 43 int startRow = 0;
- 44 if (isColumnName)
- 45 {
- 46 startRow = sheet.FirstRowNum + 1;
- 47 for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
- 48 {
- 49 ICell cell = header.GetCell(i);
- 50 if (cell != null)
- 51 {
- 52 string cellValue = cell.ToString();
- 53 if (cellValue != null)
- 54 {
- 55 DataColumn col = new DataColumn(cellValue);
- 56 daNpoi.Columns.Add(col);
- 57 }
- 58 else
- 59 {
- 60 DataColumn col = new DataColumn();
- 61 daNpoi.Columns.Add(col);
- 62 }
- 63 }
- 64 }
- 65 }
- 66
- 67 for (int i = startRow; i <= sheet.LastRowNum; i++)
- 68 {
- 69 IRow row = sheet.GetRow(i);
- 70 if (row == null)
- 71 {
- 72 continue;
- 73 }
- 74 DataRow dr = daNpoi.NewRow();
- 75 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
- 76 {
- 77
- 78 if (row.GetCell(j) != null)
- 79 {
- 80 dr[j] = row.GetCell(j).ToString();
- 81 }
- 82 }
- 83 daNpoi.Rows.Add(dr);
- 84 }
- 85 }
- 86
- 87 dataGridView1.DataSource = daNpoi;
- 88 }
复制代码 3,删除空行函数
// 删除空白行调用函数,在不需要用户添加新行或者数据的时候使用,ALLowUsertoAddRows 这个属性需要设置false
//如果ALLowUsertoAddRows 这个属性是true,则,用户可以输入数据,但是row < view.Rows.Count需要 -1不去判断最后一行
// 所以传入的x根据情况更改,想用户可以输入,ALLowUsertoAddRows的值为true, x=0;
//不想用户输入ALLowUsertoAddRows的值为false x=1;- 1 private void baoCunShuJu_cmd_Click(object sender, EventArgs e)
- 2 {
- 3 DataTable dtTable = dataGridView1.DataSource as DataTable;
- 4 string sheetName = "4G模组情况表"; //sheet名字
- 5 IWorkbook wb = new HSSFWorkbook();
- 6 ISheet sheet = string.IsNullOrEmpty(sheetName) ? wb.CreateSheet("sheet1") : wb.CreateSheet(sheetName);
- 7 int rowIndex = 0;
- 8 if (dtTable.Columns.Count > 0)
- 9 {
- 10 IRow header = sheet.CreateRow(rowIndex);
- 11 for (int i = 0; i < dtTable.Columns.Count; i++)
- 12 {
- 13 ICell cell = header.CreateCell(i);
- 14 cell.SetCellValue(dtTable.Columns[i].ColumnName);
- 15 }
- 16 }
- 17 if (dtTable.Rows.Count > 0)
- 18 {
- 19 for (int i = 0; i < dtTable.Rows.Count; i++)
- 20 {
- 21 rowIndex++;
- 22 IRow row = sheet.CreateRow(rowIndex);
- 23 for (int j = 0; j < dtTable.Columns.Count; j++)
- 24 {
- 25 ICell cell = row.CreateCell(j);
- 26 cell.SetCellValue(dtTable.Rows[i][j].ToString());
- 27 }
- 28 }
- 29 }
- 30
- 31 for (int i = 0; i < dtTable.Columns.Count; i++)
- 32 {
- 33 sheet.AutoSizeColumn(i);
- 34 }
- 35
- 36 string fileName = Application.StartupPath; // debug 目录
- 37 fileName += "\\4G模组表.xls"; //excel 名字
- 38 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
- 39 {
- 40 wb.Write(fs);
- 41 }
- 42 MessageBox.Show("保存成功");
- 43 }
复制代码 4,获取excel的列标题,给ComboBox控件- 1 private void clearGrid(DataGridView view, int x)
- 2 {
- 3 for (int row = 0; row < (view.Rows.Count - x); ++row)
- 4 {
- 5 bool isEmpty = true;
- 6 for (int col = 0; col < view.Columns.Count; ++col)
- 7 {
- 8 object value = view.Rows[row].Cells[col].Value;
- 9 if (value != null && value.ToString().Length > 0)
- 10 {
- 11 isEmpty = false;
- 12 break;
- 13 }
- 14 }
- 15 if (isEmpty)
- 16 {
- 17 view.Rows.RemoveAt(row--);
- 18 }
- 19 }
- 20 }
复制代码
5,定位,可以在每个列里根据关键字去查找,并且定位到行,可实现下一条功能- 1 public void huoQu_Column() //把excel表各列标题弄到combobox上
- 2 {
- 3 // int Rowcount = dataGridView1.RowCount;//获取datagridview的行数
- 4 int Columncount = dataGridView1.ColumnCount;//获取datagridview的列数
- 5 // dataGridView2.ColumnCount = Columncount; //新增列
- 6 // dataGridView2.ColumnHeadersVisible = true; //新增的列显示出来
- 7 for (int i = 0; i < Columncount; i++)
- 8 {
- 9 string var = this.dataGridView1.Columns[i].HeaderText;
- 10 // this.dataGridView2.Columns[i].HeaderText = this.dataGridView1.Columns[i].HeaderText; //2的列名和1的一样
- 11 // dataGridView2.Columns[i].MinimumWidth = dataGridView1.Columns[i].MinimumWidth; //2的列宽和1 一样
- 12 // = dataGridView1.Rows[0].Cells[i].Value.ToString();
- 13 lie_ming_cb.Items.Add(var);
- 14 // lie_ming_cb_1.Items.Add(var);
- 15 // if (var == "IMEI")
- 16 // {
- 17 // lie_ming_cb_1.SelectedIndex = i;
- 18 // }
- 19 }
- 20 lie_ming_cb.SelectedIndex = 0;
- 21 }
复制代码 6,textbox可以敲回车直接查找,需要添加textbox的KeyDown事件- 1 int xiayitiao_int = 0; //下一条标记
- 2
- 3 private void chaZhao_bt_1_Click(object sender, EventArgs e) //定位按钮
- 4 {
- 5 DataTable rentTable = (DataTable)dataGridView1.DataSource;//获取数据源
- 6 int r = 0;
- 7 bool dingwei_f; //定位标记,
- 8
- 9
- 10 if ((guanJianZi_box.Text != "") && (guanJianZi_box.Text != "/请输入关键字/"))
- 11 {
- 12 for (int i = xiayitiao_int; i < rentTable.Rows.Count; i++)
- 13 {
- 14 dingwei_f = rentTable.Rows[i][lie_ming_cb.Text].ToString().Contains(guanJianZi_box.Text); //对比字符串,
- 15
- 16 if (dingwei_f)
- 17 {
- 18 //指定行
- 19 dataGridView1.ClearSelection();
- 20 dataGridView1.Rows[i].Selected = true;
- 21 //让指定行处于选中状态(状态)
- 22 dataGridView1.CurrentCell = dataGridView1.Rows[i].Cells[1];
- 23 dataGridView1.CurrentRow.Selected = true;
- 24 dingwei_f = false;
- 25 xiayitiao_int = i+1; //下一条的标记
- 26 r++;
- 27 return;
- 28 }
- 29 }
- 30 if (r > 0)
- 31 {
- 32
- 33 }
- 34 else
- 35 {
- 36 MessageBox.Show("没有匹配项或已经是最后一条,将从第一条继续查找", "提示");
- 37 xiayitiao_int = 0; //重新查找
- 38 }
- 39 }
- 40 else
- 41 {
- 42 MessageBox.Show("请输入正确的关键字", "提示");
- 43 }
- 44 }
复制代码
来源:https://www.cnblogs.com/qi-jiayou/archive/2022/06/17/16385092.html
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |