private void button2_Click(object sender, EventArgs e) { ISheet sheet = Workbook2.GetSheetAt(1); int count = GetValidRows(); MessageBox.Show(Convert.ToString(count), "测试", MessageBoxButtons.OK, MessageBoxIcon.Information); }
/* * 下拉框选择改变事件 */ private void comboBox3_SelectedIndexChanged(object sender, EventArgs e) { int index = comboBox3.SelectedIndex; ISheet sheet = Workbook2.GetSheetAt(index); IRow row = sheet.GetRow(0); int ColunmCount = row.LastCellNum; ICell cell; for (int i = 0; i < ColunmCount; i++) { cell = row.GetCell(i); comboBox2.Items.Add(cell.StringCellValue); comboBox4.Items.Add(cell.StringCellValue); } }
/*获取有效填充行数*/ public int GetValidRows() { ISheet sheet = Workbook2.GetSheetAt(comboBox3.SelectedIndex); IRow row = sheet.GetRow(1); int CelIndex = comboBox2.SelectedIndex; ICell cell; int sum = 0; for (int i = 1; i <= sheet.LastRowNum; i++) { cell = row.GetCell(CelIndex); if (cell != null && !cell.StringCellValue.Equals("")) { sum++; } } return(sum); }
/* * 点击事件 */ private void button1_Click(object sender, EventArgs e) { int conditon1 = comboBox2.SelectedIndex; int condition2 = comboBox4.SelectedIndex; int select = comboBox1.SelectedIndex; string p1 = comboBox2.Text; string p2 = comboBox4.Text; if (conditon1 < 0 || condition2 < 0 || select < 0) { MessageBox.Show("请选择匹配条件或匹配源!", "未选择条件列或匹配源", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (p1.Equals(p2)) { MessageBox.Show("根据条件列与需要填充的列不能是同一个,请重新选择", "列选择错误", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //if(!CheckFileType()) int c1 = GetPachIndex(p1); int c2 = GetPachIndex(p2); if (c1 < 0 || c2 < 0) { MessageBox.Show("错误,在匹配源中找不到选择的列,请检查匹配源的sheet或选择匹配的条件列", "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } List <CellObject> CellList = GetPachExcelObject(c1, c2); //获取符合条件的Cell对象 ISheet sheet = Workbook2.GetSheetAt(comboBox3.SelectedIndex); //操作此Sheet IRow row; ICell cell1, cell2; int OpColumn = comboBox4.SelectedIndex; //填充此列 int finished = 0; progressBar1.Maximum = sheet.LastRowNum; progressBar1.Value = 0; progressBar1.Step = 1; for (int i = 1; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); //行移动 cell1 = row.GetCell(conditon1); //操作的文件的匹配条件 cell2 = row.GetCell(OpColumn); //本行需要填充的列 cell1.SetCellType(CellType.String); if (cell2 == null) //如果此单元格为空则创建 { cell2 = row.CreateCell(OpColumn); } cell2.SetCellType(CellType.String); //强制设置为字符串类型 foreach (CellObject co in CellList) //遍历list { if (cell1.StringCellValue.Equals(co.Param1)) //如果根据条件的单元格和list中的字段1相同 { cell2.SetCellValue(co.Param2); //把list中的字段2写入这个单元格 CellList.Remove(co); //删除已经匹配中的对象 break; //提前结束循环,提高运行效率 } } finished++; if (progressBar1.Value > progressBar1.Maximum) //防止进度条越界 { progressBar1.Value -= 1; } progressBar1.Value += progressBar1.Step; label7.Text = "已完成:" + Convert.ToString(finished) + "/" + Convert.ToString(progressBar1.Maximum).ToString(); label7.Refresh(); System.Threading.Thread.Sleep(10); //线程休眠 } FileStream fs = File.Create(destination); Workbook2.Write(fs); //保存修改 Workbook1.Close(); Workbook2.Close(); fs.Close(); MessageBox.Show("执行完成,已成功匹配所有记录", "操作成功", MessageBoxButtons.OK, MessageBoxIcon.Information); }