EXCEL数据汇总

最新推荐文章于 2024-07-24 23:05:54 发布

歪人

最新推荐文章于 2024-07-24 23:05:54 发布

阅读量100

点赞数

文章标签： microsoft

本文链接：https://blog.csdn.net/u010905517/article/details/126644936

版权

把同一文件夹中相同格式的表格文件汇总成一张表。未采用逐条读写的方式搬运，使用 worksheet.Range[s2, e2].Value2 整块处理。

using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;


namespace 实验成绩上传
{
    public partial class Form1 : Form
    {

        public DataSet ds;
        public bool Stop = false;

        public Form1()
        {
            InitializeComponent();
        }



        private void button1_Click(object sender, EventArgs e)  //打开文件
        {
            System.Windows.Forms.FolderBrowserDialog dialog = new System.Windows.Forms.FolderBrowserDialog();
            dialog.Description = "请选择解压报表所在文件夹";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (string.IsNullOrEmpty(dialog.SelectedPath))
                {
                    MessageBox.Show(this, "文件夹路径不能为空", "提示");
                    return;
                }
                textBoxFilepath.Text = dialog.SelectedPath;
            }
        }

        private void button5_Click(object sender, EventArgs e)//退出程序
        {
            Stop = true;
            MessageBox.Show("已停止检查，右上角退出！", "", MessageBoxButtons.OK);


        }




        private void buttonPost_Click(object sender, EventArgs e)//上传按钮
        {
            Stop = false;




            int number = 0;
            string[] files = Directory.GetFiles(textBoxFilepath.Text, "*.xls ");
            number = files.Length + 1;
            if (number == 0) { MessageBox.Show("无符合条件的文件，请重新选择文件夹。", "错误", MessageBoxButtons.OK); return; }


            progressBar1.Maximum = number;
            progressBar1.Value = 0;
            buttonPost.Enabled = false;




            if (number == 0) { MessageBox.Show("无符合条件的文件，请重新选择文件夹。", "错误", MessageBoxButtons.OK); return; }

            string textCont = "";
            Microsoft.Office.Interop.Excel.Application xlsAppdddd;
            xlsAppdddd = new Microsoft.Office.Interop.Excel.Application();

            Workbook workbookdddd = xlsAppdddd.Workbooks.Open(@"C:\Users\Administrator\Desktop\新建文件夹\z汇总表.xlsx");
            Worksheet worksheetdddd = workbookdddd.Sheets[1] as Worksheet;



            int start = 5;
            foreach (string file in files)//逐个文件检查
            {
                if (Stop) return;

                Console.WriteLine(file);
                string fn = file.Substring(file.LastIndexOf('\\') + 1);
                fn = fn.Split('_')[0];


                textCont = fn;



                try
                {
                    if (file.IndexOf("xlsx") > -1)
                        continue;
                    Microsoft.Office.Interop.Excel.Application xlsApp;
                    xlsApp = new Microsoft.Office.Interop.Excel.Application();

                    Workbook workbook = xlsApp.Workbooks.Open(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    Worksheet worksheet = workbook.Sheets[1] as Worksheet;

                    int end = worksheet.UsedRange.Rows.Count;

                    //for (int i=5; i< worksheet.UsedRange.Rows.Count; i++)
                    {
                        string s1 = "A5";// + start.ToString();
                        string e1 = "AK" + end.ToString();

                        object[,] v = new object[,] { };


                        string s2 = "A" + start.ToString();
                        string e2 = "AK" + (start + end - 5).ToString();

                        v = worksheet.Range[s1, e1].Value2;

                        worksheetdddd.Range[s2, e2].Value2 = v;
                        v = null;
                        workbookdddd.Save();
                        start += end - 5;

                        Console.WriteLine(start);
                    }



                    workbook.Close();







                }

                catch (Exception f) { MessageBox.Show(f.Message, "错误", MessageBoxButtons.OK); ; }



            }
            workbookdddd.Save();
            workbookdddd.Close();
            xlsAppdddd.Quit();
            MessageBox.Show("ok");


        }


    }

}