C#读取Excel数据并重新写入Excel

最新推荐文章于 2024-05-02 01:13:13 发布

蓝色封印

最新推荐文章于 2024-05-02 01:13:13 发布

阅读量825

点赞数

CC 4.0 BY-SA版权

分类专栏： C#开发文章标签： excel c# button object dataset textbox

本文链接：https://blog.csdn.net/wenyan07/article/details/6458372

C#开发专栏收录该内容

13 篇文章

订阅专栏

本文介绍如何使用 C# 从两个 Excel 工作表中读取数据并进行整合，包括数据加载、比较与合并，最后将结果导出到新的 Excel 文件。

public partial class Form1 : Form
    {
        private System.Data.DataSet myDataSet;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            GetConnect();
            dataGridView1.DataMember = "[Sheet1$]";
            dataGridView1.DataSource = myDataSet;
            dataGridView2.DataMember = "[Sheet2$]";
            dataGridView2.DataSource = myDataSet;
        }

        private void GetConnect()
        {
            string strCon = textBox1.Text.Trim();
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [Sheet1$] ";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            myDataSet = new DataSet();
            myCommand.Fill(myDataSet, "[Sheet1$]");
            myConn.Close();

            strCom = " SELECT * FROM [Sheet2$] ";
            myConn.Open();
            myCommand = new OleDbDataAdapter(strCom, myConn);
            myCommand.Fill(myDataSet, "[Sheet2$]");
            myConn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView2.Rows.Count; j++)
                {
                    if (dataGridView1.Rows[i].Cells[0].Value.ToString() == dataGridView2.Rows[j].Cells[0].Value.ToString())
                    {
                        dataGridView1.Rows[i].Cells[1].Value = Decimal.Parse(dataGridView1.Rows[i].Cells[1].Value.ToString()) + Decimal.Parse(dataGridView2.Rows[j].Cells[1].Value.ToString());
                    }
                }
            }

            for (int i = 0; i < dataGridView2.Rows.Count; i++)
            {
                int j = 0;
                for (j = 0; j < dataGridView1.Rows.Count; j++)
                {
                    if (dataGridView2.Rows[i].Cells[0].Value.ToString() == dataGridView1.Rows[j].Cells[0].Value.ToString())
                    {
                        break;
                    }
                }
                if (j == dataGridView1.Rows.Count)
                {
                    DataRow dr = myDataSet.Tables["[Sheet1$]"].NewRow();
                    dr[0] = dataGridView2.Rows[i].Cells[0].Value.ToString();
                    dr[1] = dataGridView2.Rows[i].Cells[1].Value.ToString();
                    myDataSet.Tables["[Sheet1$]"].Rows.Add(dr);
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            for (int i = 0; i < dataGridView1.Rows.Count;i++ )
            {
                excel.Cells[i + 1, 1] = dataGridView1.Rows[i].Cells[0].Value.ToString();
                excel.Cells[i + 1, 2] = dataGridView1.Rows[i].Cells[1].Value.ToString();
            }
            //excel.Cells[1, 1] = "第一行第一列";
            //excel.Cells[1, 2] = "第一行第二列";
            //excel.Cells[2, 1] = "第二行第一列";
            //excel.Cells[2, 2] = "第二行第二列";
            //excel.Cells[3, 1] = "第三行第一列";
            //excel.Cells[3, 2] = "第三行第二列";

excel.Visible = true;

}

注意细节：

1、在visual Studio 命令提示工具中，定位到Excel安装目录，运行“tlbimp Excel.EXE Excel.dll”命令，把Excel.exe编译为 Excel.dll

2、为项目添加 EXCEL.DLL的引用方法如下: 项目 -> 添加引用 -> 浏览 -> 定位到EXCEL.DLL