用npoi处理excel数据

来源：互联网发布：jav番号新域名编辑：程序博客网时间：2024/06/09 22:49

一个学生本来是用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+"\"");            }        }    }}

0 0