- A+
所属分类：.NET技术
做下记录，
首先插入一个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 }
2，导出数据（NPOI）
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 }
3，删除空行函数
// 删除空白行调用函数，在不需要用户添加新行或者数据的时候使用，ALLowUsertoAddRows 这个属性需要设置false
//如果ALLowUsertoAddRows 这个属性是true,则，用户可以输入数据，但是row < view.Rows.Count需要 -1不去判断最后一行
// 所以传入的x根据情况更改，想用户可以输入，ALLowUsertoAddRows的值为true, x=0;
//不想用户输入ALLowUsertoAddRows的值为false x=1;
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 }
4，获取excel的列标题，给ComboBox控件
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 }
5，定位，可以在每个列里根据关键字去查找，并且定位到行，可实现下一条功能
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 }
6，textbox可以敲回车直接查找，需要添加textbox的KeyDown事件
private void guanJianZi_box_KeyDown(object sender, KeyEventArgs e) { if (e.KeyValue == 13) { chaZhao_bt_1_Click(sender, e); } }
