学步园

一个学生本来是用vba来处理两个sheet（每个sheet里面的数据差不多2完条）中的数据，要求本来不复杂，但是用excel的vba来处理数据处理了差不多一天也没有处理完，差不多崩溃了，我也尝试了用vba来处理，不过觉得用vba来处理有些不爽，里面处理大数据量有些慢，虽然有优化的方法，但是我不想在vba花费时间了，我尝试用npoi来对excel处理。处理的时间差不多5分钟就完事了。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using System.IO;
using System.Windows.Forms;
namespace excel问题
{
    class model
    {
        public string id { get; set; }
        public string date { get; set; }
        public string value { get; set; }
    }
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
           
            string appDirPath = System.AppDomain.CurrentDomain.BaseDirectory;
            OpenFileDialog of = new OpenFileDialog();
            of.Filter = "excel|*.xlsx;*.xls";
            if (of.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            
            using (FileStream fs = File.OpenRead(of.FileName))
            {
                Console.WriteLine("开始准备读取文件");
                IWorkbook wb = WorkbookFactory.Create(fs);
                ISheet sh0 = wb.GetSheetAt(0);
                ISheet sh1 = wb.GetSheetAt(1);
                List<model> list = new List<model>();
                Console.WriteLine("开始处理sheet2表");
                for (int i = 1; i <=sh1.LastRowNum; i++)
                {
                    Console.WriteLine("sheet2表第{0}行", i);
                    model md = new model();
                    IRow row = sh1.GetRow(i);
                    if (row.GetCell(0) != null)
                    {
                        md.id = row.GetCell(0).StringCellValue;
                    }
                    if (row.GetCell(1) != null)
                    {
                        md.date = row.GetCell(1).StringCellValue;
                    }
                    if (row.GetCell(2) != null)
                    {
                        md.value = row.GetCell(2).StringCellValue;
                    }
                    list.Add(md);
                }
                Console.WriteLine("处理sheet2表结束");
                Console.WriteLine("----------------------------");
                Console.WriteLine("开始处理sheet1表");
                for (int i = 1; i <=sh0.LastRowNum; i++)
                {
                    Console.WriteLine("sheet1表第{0}行", i);
                    IRow row = sh0.GetRow(i);
                    string value1 = row.GetCell(0).StringCellValue;
                    string value2 = row.GetCell(1).StringCellValue;
                    var result = list.Where(m => m.id == value1 && string.Compare(m.date, value2) >= 0).OrderBy(m=>m.date).Take(12);
                   int count=0;
                    foreach (var item in result)
                    {
                        row.CreateCell(5 + count++).SetCellValue(item.value);
                    }

                }
                Console.WriteLine("正在写入处理结果，请稍候");
                string newPath=appDirPath+"result.xlsx";
                FileStream sw = File.Create(newPath);
                wb.Write(sw);
                sw.Close();
                System.Diagnostics.Process.Start("explorer.exe","/select,\""+newPath+"\"");
            }
        }
    }
}