datagridview复制、剪切、粘贴、清空、增加行、删除行、增加列、删除列，导出Excel/Word

最新推荐文章于 2023-03-18 22:00:59 发布

ibmfashion

最新推荐文章于 2023-03-18 22:00:59 发布

阅读量2.7k

点赞数 2

分类专栏： c#

c# 专栏收录该内容

77 篇文章 0 订阅

订阅专栏

1.导入Excel。代码走先。

[csharp] view plain copy print ?

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());
                }
            }
        }

[csharp] view plain copy print ?

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方法会绑定数据源，当你用代码插入时总会提示“当控件与数据源绑定时，无法以编程方式向其中添加行”，最后得以高手帮助，搞定。见代码。

[csharp] view plain copy print ?

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;
        }

[csharp] view plain copy print ?

//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());
            }
        }

3.就是datagridview单元格为空问题，而datagridview单元格为空不能使用NULL,应使用DBNull.Value;如下：

[csharp] view plain copy print ?

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还要能够自动增加行与列。代码

[csharp] view plain copy print ?

#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上编辑，只能增加列时就自动传一个列名，我是这样做的：

[csharp] view plain copy print ?

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.其他的代码就不一一上传了，只把主要的问题和代码上传，有需要的邮件给我，我传给你。

第一次发这么长的，求人品。。。。。。。。

（附图，有图有真相，见下）：

ibmfashion

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
7
评论
datagridview复制、剪切、粘贴、清空、增加行、删除行、增加列、删除列，导出Excel/Word

1.导入Excel。代码走先。[csharp] view plaincopyprint?public void ExcelToDataGridView(string filePath, DataGridView dataGridView1) { //根据路径打开一个Excel文件并将数据填充到DataSet中
复制链接

扫一扫