datagridview复制、剪切、粘贴、清空、增加行、删除行、增加列、删除列,导出Excel/Word
来源:互联网 发布:燕麦片的做法 知乎 编辑:程序博客网 时间:2024/05/21 17:07
1.导入Excel。代码走先。
- public void ExcelToDataGridView(string filePath, DataGridView dataGridView1)
- {
- //根据路径打开一个Excel文件并将数据填充到DataSet中
- string strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filePath + "; Extended Properties = 'Excel 8.0;HDR =YES; IMEX=2'";
- // 导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
- OleDbConnection conn = new OleDbConnection(strConn);
- conn.Open();
- string strExcel = "select * from [sheet1$]";
- OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
- ds = new DataSet();
- myCommand.Fill(ds, "[Sheet1$]");
- dataGridView1.DataMember = "[Sheet1$]";
- dataGridView1.DataSource = ds;
- conn.Close();
- dataGridView1.AllowUserToAddRows = true;
- //禁用自动排序
- for (int k= 0;k < dataGridView1.Columns.Count; k++)
- {
- dataGridView1.Columns[k].SortMode = DataGridViewColumnSortMode.NotSortable;
- }
- int cCount = dataGridView1.ColumnCount;
- int rCount = dataGridView1.RowCount;
- double[,] array = new double[rCount, cCount];
- for (int j = 0; j < rCount - 1; j++) // 逐行遍历
- {
- for (int i = 0; i < cCount - 1; i++) // 逐列遍历
- {
- array[j, i] = Convert.ToDouble(dataGridView1.Rows[j].Cells[i].Value.ToString());
- }
- }
- }
public void ExcelToDataGridView(string filePath, DataGridView dataGridView1) { //根据路径打开一个Excel文件并将数据填充到DataSet中 string strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filePath + "; Extended Properties = 'Excel 8.0;HDR =YES; IMEX=2'"; // 导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = "select * from [sheet1$]"; OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, "[Sheet1$]"); dataGridView1.DataMember = "[Sheet1$]"; dataGridView1.DataSource = ds; conn.Close(); dataGridView1.AllowUserToAddRows = true; //禁用自动排序 for (int k= 0;k < dataGridView1.Columns.Count; k++) { dataGridView1.Columns[k].SortMode = DataGridViewColumnSortMode.NotSortable; } int cCount = dataGridView1.ColumnCount; int rCount = dataGridView1.RowCount; double[,] array = new double[rCount, cCount]; for (int j = 0; j < rCount - 1; j++) // 逐行遍历 { for (int i = 0; i < cCount - 1; i++) // 逐列遍历 { array[j, i] = Convert.ToDouble(dataGridView1.Rows[j].Cells[i].Value.ToString()); } } }
- private void button1_Click(object sender, EventArgs e)
- {
- // 打开一个选择框
- OpenFileDialog ofd = new OpenFileDialog();
- ofd.Title = "Excel文件";
- ofd.FileName = "";
- ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
- ofd.Filter = "Excel文件(*.xls)|*.xls";
- ofd.ValidateNames = true; // 文件有效性验证vValidateNames,验证用户输入的是否是一个有效的windows 文件名
- ofd.CheckFileExists = true; // 验证文件的有效性
- ofd.CheckPathExists = true; // 验证路径的有效性
- strName = string.Empty;
- if (ofd.ShowDialog() == DialogResult.OK)
- {
- strName = ofd.FileName;
- }
- if (strName == "")
- {
- MessageBox.Show("没有选择Excel文件!无法进行数据导入");
- return;
- }
- DgvSet EtG = new DgvSet();
- EtG.ExcelToDataGridView(strName, this.dataGridView1);
- }
private void button1_Click(object sender, EventArgs e) { // 打开一个选择框 OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "Excel文件"; ofd.FileName = ""; ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); ofd.Filter = "Excel文件(*.xls)|*.xls"; ofd.ValidateNames = true; // 文件有效性验证vValidateNames,验证用户输入的是否是一个有效的windows 文件名 ofd.CheckFileExists = true; // 验证文件的有效性 ofd.CheckPathExists = true; // 验证路径的有效性 strName = string.Empty; if (ofd.ShowDialog() == DialogResult.OK) { strName = ofd.FileName; } if (strName == "") { MessageBox.Show("没有选择Excel文件!无法进行数据导入"); return; } DgvSet EtG = new DgvSet(); EtG.ExcelToDataGridView(strName, this.dataGridView1); }
用此方法导入Excel最为方便,注意需要命名空间using System.Data.OleDb;
2.我遇到最大的难题就是插入行,因为此导入Excel方法会绑定数据源,当你用代码插入时总会提示“当控件与数据源绑定时,无法以编程方式向其中添加行”,最后得以高手帮助,搞定。见代码。
- public void AddExcelToDataGridView(DataGridView dataGridView1, int index)
- {
- dataGridView1.DataBindings.Clear();
- DataRow row = row = ds.Tables[0].NewRow();
- ds.Tables[0].Rows.InsertAt(row, index);
- dataGridView1.DataSource = ds;
- dataGridView1.AllowUserToAddRows = true;
- }
public void AddExcelToDataGridView(DataGridView dataGridView1, int index) { dataGridView1.DataBindings.Clear(); DataRow row = row = ds.Tables[0].NewRow(); ds.Tables[0].Rows.InsertAt(row, index); dataGridView1.DataSource = ds; dataGridView1.AllowUserToAddRows = true; }
- //datagridview右键功能
- private void 插入行ToolStripMenuItem_Click(object sender, EventArgs e)
- {
- try
- {
- //MessageBox.Show(dataGridView1.CurrentRow.Index.ToString());
- DgvSet EtG = new DgvSet();
- EtG.AddExcelToDataGridView( this.dataGridView1, dataGridView1.CurrentRow.Index);
- //this.dataGridView1.Rows.Insert(dataGridView1.CurrentRow.Index, 1);
- //this.dataGridView1.Rows.Insert(dataGridView1.CurrentRow.Index, new DataGridViewRow());
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message.ToString());
- }
- }
//datagridview右键功能 private void 插入行ToolStripMenuItem_Click(object sender, EventArgs e) { try { //MessageBox.Show(dataGridView1.CurrentRow.Index.ToString()); DgvSet EtG = new DgvSet(); EtG.AddExcelToDataGridView( this.dataGridView1, dataGridView1.CurrentRow.Index); //this.dataGridView1.Rows.Insert(dataGridView1.CurrentRow.Index, 1); //this.dataGridView1.Rows.Insert(dataGridView1.CurrentRow.Index, new DataGridViewRow()); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
- public void Clear(DataGridView dataGridView1)
- {
- foreach (DataGridViewCell cell in dataGridView1.SelectedCells)
- {
- cell.Value = DBNull.Value;
- }
- }
public void Clear(DataGridView dataGridView1) { foreach (DataGridViewCell cell in dataGridView1.SelectedCells) { cell.Value = DBNull.Value; } }
4.另一个较复杂问题就是粘贴,,粘贴不仅要能够从外面的Excel复制在datagridview粘贴,本身datagridview还要能够复制粘贴,而且,当复制行列数超过datagridview总行列数时,datagridview还要能够自动增加行与列。代码
- #region 粘贴
- public int Paste(DataGridView dgv, string pasteText, int kind, bool b_cut)
- {
- try
- {
- if (kind == 0)
- {
- pasteText = Clipboard.GetText();
- }
- if (string.IsNullOrEmpty(pasteText))
- return -1;
- int rowNum = 0;
- int columnNum = 0;
- //获得当前剪贴板内容的行、列数
- for (int i = 0; i < pasteText.Length; i++)
- {
- if (pasteText.Substring(i, 1) == "\t")
- {
- columnNum++;
- }
- if (pasteText.Substring(i, 1) == "\n")
- {
- rowNum++;
- }
- }
- Object[,] data;
- //粘贴板上的数据来自于EXCEL时,每行末都有\n,在DATAGRIDVIEW内复制时,最后一行末没有\n
- if (pasteText.Substring(pasteText.Length - 1, 1) == "\n")
- {
- rowNum = rowNum - 1;
- }
- columnNum = columnNum / (rowNum + 1);
- data = new object[rowNum + 1, columnNum + 1];
- String rowStr;
- //对数组赋值
- for (int i = 0; i < (rowNum + 1); i++)
- {
- for (int colIndex = 0; colIndex < (columnNum + 1); colIndex++)
- {
- rowStr = null;
- //一行中的最后一列
- if (colIndex == columnNum && pasteText.IndexOf("\r") != -1)
- {
- rowStr = pasteText.Substring(0, pasteText.IndexOf("\r"));
- }
- //最后一行的最后一列
- if (colIndex == columnNum && pasteText.IndexOf("\r") == -1)
- {
- rowStr = pasteText.Substring(0);
- }
- //其他行列
- if (colIndex != columnNum)
- {
- rowStr = pasteText.Substring(0, pasteText.IndexOf("\t"));
- pasteText = pasteText.Substring(pasteText.IndexOf("\t") + 1);
- }
- if (rowStr == string.Empty)
- rowStr = null;
- data[i, colIndex] = rowStr;
- }
- //截取下一行数据
- pasteText = pasteText.Substring(pasteText.IndexOf("\n") + 1);
- }
- /*检测值是否是列头*/
- /*
- //获取当前选中单元格所在的列序号
- int columnindex = dgv.CurrentRow.Cells.IndexOf(dgv.CurrentCell);
- //获取获取当前选中单元格所在的行序号
- int rowindex = dgv.CurrentRow.Index;*/
- int columnindex = -1, rowindex = -1;
- int columnindextmp = -1, rowindextmp = -1;
- if (dgv.SelectedCells.Count != 0)
- {
- columnindextmp = dgv.SelectedCells[0].ColumnIndex;
- rowindextmp = dgv.SelectedCells[0].RowIndex;
- }
- //取到最左上角的 单元格编号
- foreach (DataGridViewCell cell in dgv.SelectedCells)
- {
- //dgv.Rows[cell.RowIndex].Selected = true;
- columnindex = cell.ColumnIndex;
- if (columnindex > columnindextmp)
- {
- //交换
- columnindex = columnindextmp;
- }
- else
- columnindextmp = columnindex;
- rowindex = cell.RowIndex;
- if (rowindex > rowindextmp)
- {
- rowindex = rowindextmp;
- rowindextmp = rowindex;
- }
- else
- rowindextmp = rowindex;
- }
- if (kind == -1)
- {
- columnindex = 0;
- rowindex = 0;
- }
- //如果行数超过当前列表行数
- if (rowindex + rowNum + 1 > dgv.RowCount)
- {
- int mm = rowNum + rowindex + 1 - dgv.RowCount;
- for (int ii = 0; ii < mm+1; ii++)
- {
- dgv.DataBindings.Clear();
- DataRow row = row = ds.Tables[0].NewRow();
- ds.Tables[0].Rows.InsertAt(row, ii + rowindex + 1);
- }
- }
- //如果列数超过当前列表列数
- if (columnindex + columnNum + 1 > dgv.ColumnCount)
- {
- int mmm = columnNum + columnindex + 1 - dgv.ColumnCount;
- for (int iii= 0; iii < mmm; iii++)
- {
- dgv.DataBindings.Clear();
- DataGridViewTextBoxColumn colum = new DataGridViewTextBoxColumn();
- dgv.Columns.Insert(columnindex+1, colum);
- }
- }
- //增加超过的行列
- for (int j = 0; j < (rowNum + 1); j++)
- {
- for (int colIndex = 0; colIndex < (columnNum + 1); colIndex++)
- {
- if (colIndex + columnindex < dgv.Columns.Count)
- {
- if (dgv.Columns[colIndex + columnindex].CellType.Name == "DataGridViewTextBoxCell")
- {
- if (dgv.Rows[j + rowindex].Cells[colIndex + columnindex].ReadOnly == false)
- {
- dgv.Rows[j + rowindex].Cells[colIndex + columnindex].Value = data[j, colIndex];
- dgv.Rows[j + rowindex].Cells[colIndex + columnindex].Selected = true;
- }
- }
- }
- }
- }
- //清空剪切板内容
- if (b_cut)
- Clipboard.Clear();
- return 1;
- }
- catch
- {
- return -1;
- }
- }
- #endregion
#region 粘贴 public int Paste(DataGridView dgv, string pasteText, int kind, bool b_cut) { try { if (kind == 0) { pasteText = Clipboard.GetText(); } if (string.IsNullOrEmpty(pasteText)) return -1; int rowNum = 0; int columnNum = 0; //获得当前剪贴板内容的行、列数 for (int i = 0; i < pasteText.Length; i++) { if (pasteText.Substring(i, 1) == "\t") { columnNum++; } if (pasteText.Substring(i, 1) == "\n") { rowNum++; } } Object[,] data; //粘贴板上的数据来自于EXCEL时,每行末都有\n,在DATAGRIDVIEW内复制时,最后一行末没有\n if (pasteText.Substring(pasteText.Length - 1, 1) == "\n") { rowNum = rowNum - 1; } columnNum = columnNum / (rowNum + 1); data = new object[rowNum + 1, columnNum + 1]; String rowStr; //对数组赋值 for (int i = 0; i < (rowNum + 1); i++) { for (int colIndex = 0; colIndex < (columnNum + 1); colIndex++) { rowStr = null; //一行中的最后一列 if (colIndex == columnNum && pasteText.IndexOf("\r") != -1) { rowStr = pasteText.Substring(0, pasteText.IndexOf("\r")); } //最后一行的最后一列 if (colIndex == columnNum && pasteText.IndexOf("\r") == -1) { rowStr = pasteText.Substring(0); } //其他行列 if (colIndex != columnNum) { rowStr = pasteText.Substring(0, pasteText.IndexOf("\t")); pasteText = pasteText.Substring(pasteText.IndexOf("\t") + 1); } if (rowStr == string.Empty) rowStr = null; data[i, colIndex] = rowStr; } //截取下一行数据 pasteText = pasteText.Substring(pasteText.IndexOf("\n") + 1); } /*检测值是否是列头*/ /* //获取当前选中单元格所在的列序号 int columnindex = dgv.CurrentRow.Cells.IndexOf(dgv.CurrentCell); //获取获取当前选中单元格所在的行序号 int rowindex = dgv.CurrentRow.Index;*/ int columnindex = -1, rowindex = -1; int columnindextmp = -1, rowindextmp = -1; if (dgv.SelectedCells.Count != 0) { columnindextmp = dgv.SelectedCells[0].ColumnIndex; rowindextmp = dgv.SelectedCells[0].RowIndex; } //取到最左上角的 单元格编号 foreach (DataGridViewCell cell in dgv.SelectedCells) { //dgv.Rows[cell.RowIndex].Selected = true; columnindex = cell.ColumnIndex; if (columnindex > columnindextmp) { //交换 columnindex = columnindextmp; } else columnindextmp = columnindex; rowindex = cell.RowIndex; if (rowindex > rowindextmp) { rowindex = rowindextmp; rowindextmp = rowindex; } else rowindextmp = rowindex; } if (kind == -1) { columnindex = 0; rowindex = 0; } //如果行数超过当前列表行数 if (rowindex + rowNum + 1 > dgv.RowCount) { int mm = rowNum + rowindex + 1 - dgv.RowCount; for (int ii = 0; ii < mm+1; ii++) { dgv.DataBindings.Clear(); DataRow row = row = ds.Tables[0].NewRow(); ds.Tables[0].Rows.InsertAt(row, ii + rowindex + 1); } } //如果列数超过当前列表列数 if (columnindex + columnNum + 1 > dgv.ColumnCount) { int mmm = columnNum + columnindex + 1 - dgv.ColumnCount; for (int iii= 0; iii < mmm; iii++) { dgv.DataBindings.Clear(); DataGridViewTextBoxColumn colum = new DataGridViewTextBoxColumn(); dgv.Columns.Insert(columnindex+1, colum); } } //增加超过的行列 for (int j = 0; j < (rowNum + 1); j++) { for (int colIndex = 0; colIndex < (columnNum + 1); colIndex++) { if (colIndex + columnindex < dgv.Columns.Count) { if (dgv.Columns[colIndex + columnindex].CellType.Name == "DataGridViewTextBoxCell") { if (dgv.Rows[j + rowindex].Cells[colIndex + columnindex].ReadOnly == false) { dgv.Rows[j + rowindex].Cells[colIndex + columnindex].Value = data[j, colIndex]; dgv.Rows[j + rowindex].Cells[colIndex + columnindex].Selected = true; } } } } } //清空剪切板内容 if (b_cut) Clipboard.Clear(); return 1; } catch { return -1; } } #endregion
5.另外,因为datagridview无法编辑第一列,当插入列时,就无法在datagridview上编辑,只能增加列时就自动传一个列名,我是这样做的:
- private void 插入列ToolStripMenuItem_Click(object sender, EventArgs e)
- {
- try
- {
- DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
- int columnindex = dataGridView1.CurrentRow.Cells.IndexOf(dataGridView1.CurrentCell);
- string newName = "New" + (k++);
- column.HeaderText =newName;
- //column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
- //column.CellTemplate.Style.BackColor = Color.Beige;
- dataGridView1.Columns.Insert(columnindex + 1, column);
- }
- catch(Exception error)
- {
- MessageBox.Show(error.Message);
- }
- }
private void 插入列ToolStripMenuItem_Click(object sender, EventArgs e) { try { DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn(); int columnindex = dataGridView1.CurrentRow.Cells.IndexOf(dataGridView1.CurrentCell); string newName = "New" + (k++); column.HeaderText =newName; //column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells; //column.CellTemplate.Style.BackColor = Color.Beige; dataGridView1.Columns.Insert(columnindex + 1, column); } catch(Exception error) { MessageBox.Show(error.Message); } }
6.其他的代码就不一一上传了,只把主要的问题和代码上传,有需要的邮件给我,我传给你。
第一次发这么长的,求人品。。。。。。。。
(附图,有图有真相,见下):
- datagridview复制、剪切、粘贴、清空、增加行、删除行、增加列、删除列,导出Excel/Word
- 给datagridview增加编辑,删除列
- 增加 删除 tabel 表格 行 列 insertRow()
- 动态增加、删除列
- jquery增加/删除列
- 增加和删除列
- javascript 动态增加行/列,动态删除行/列
- SQL 增加列、修改列、删除列
- SQL 增加列、修改列、删除列
- Oracle 增加 修改 删除 列
- MYSQL 删除列,增加列 alter table
- 数据库增加列或删除列操作
- 修改、删除表内容,增加列、修改列、删除列
- Oracle列操作(增加列,修改列,删除列)
- Oracle列操作(增加列,修改列,删除列)
- mysql 增加列,修改列名、列属性,删除列
- 使用jQuery操纵表格-增加、删除行和列
- R:增加或删除矩阵的行或列
- uva_10051 - Tower of Cubes(LIS)
- 美国加州地区发现长着750条腿的奇异生物
- remote.alert - send trap alert to remote mon
- GridControl的绑定
- error C2248
- datagridview复制、剪切、粘贴、清空、增加行、删除行、增加列、删除列,导出Excel/Word
- snpp.alert - Pure perl SNPP client
- 第十一周上机任务项目5-体验文件操作
- Asp.net上传文件实践
- ubuntu12.04 远程桌面 centos5.5 实例 原创求首页!!
- IIS 7.x Application Request Routing(ARR) 502错误的解决方法
- test.alert
- linux 比较工具使用步骤
- 如何定义和实现一个类的成员函数为回调函数