使用C#+NPOI进行Excel处理，实现多个Excel文件的求和统计（Using C #+NPOI for Excel processing to achieve summation statis）

一个简易的控制台程序，使用C#+NPOI进行Excel处理，实现多个Excel文件的求和统计。
A simple console program that uses C #+NPOI for Excel processing, achieving the sum and statistics of multiple Excel files.

前提：

1. 待统计的Excel格式相同
2. 统计结果表与待统计的表格格式一致

引入如下四个动态库：
1. NPOI.dll
2. NPOI.OOXML.dll
3. NPOI.OpenXml4Net.dll
4. NPOI.OpenXmlFormats.dll

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;

namespace excelMergeCal
{
    class Program
    {
        static String BaseDIir = "d:\\docs";
        static String ResultFile = "d:\\result.xlsx";
        static int startRow = 5;
        static int endRow = 72;
        static int startColumn = 3;
        static int endColumn = 67;
        static List<CellWithPos> AllCells = new List<CellWithPos>();
        static void Main(string[] args)
        {
            Console.WriteLine("********************************************************************************");
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine("* 1.将待处理的Excel放在D盘的docs文件夹下****************************************");
            Console.WriteLine("* 2.将待处理的一个文件的全部数据单元格清空后放在D盘根目录下，命名为result.xlsx *");
            Console.WriteLine("* 3.关闭打开的excel文件 ********************************************************");
            Console.WriteLine("* 4.按下回车键(Enter)开始*******************************************************");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine("********************************************************************************");
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("等待按下回车键(Enter)开始...");
            Console.ForegroundColor = ConsoleColor.White;
            Console.ReadLine();
            ReadAllExcel();
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("处理完成，按回车键(Enter)关闭.");
            Console.ReadLine();
        }
        static void ReadAllExcel()
        {
            string[] files = Directory.GetFiles(BaseDIir, "*.*", SearchOption.AllDirectories);
            foreach (string file in files)
            {
                Console.WriteLine("正在处理文件：" + file);
                if (!file.EndsWith("xlsx") && !file.EndsWith("xls"))
                {
                    Console.WriteLine("文件 " + file + " 不是xlsx/xls后缀，已忽略.....");
                    continue;
                }
                ReadExcel(file);
                // 处理每个文件
            }
            WriteResult();
        }
        static CellWithPos Get(int row, int col)
        {
            for (int i = 0; i < AllCells.Count; i++)
            {
                if (AllCells[i].row == row && AllCells[i].column == col)
                    return AllCells[i];
            }
            return null;
        }
        public static void WriteResult()
        {
            String path = ResultFile;
            string extension = System.IO.Path.GetExtension(path);
            // 第一步：读取文件流
            NPOI.SS.UserModel.IWorkbook workbook;
            using (FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                if (extension.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }

                #region 读取第一个sheet页面
                ISheet sheet = workbook.GetSheetAt(0);//第一个sheet页（列表）
                int rowCount = sheet.LastRowNum;
                IRow row = sheet.GetRow(0);  //读取当前行数据
                for (int i = startRow; i <= sheet.LastRowNum & i <= endRow; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row == null) continue;
                    //Console.WriteLine(row.GetCell(1).ToString());
                    for (int j = startColumn; j < row.Cells.Count & j <= endColumn; j++)
                    {
                        ICell cell = row.GetCell(j);
                        CellWithPos cp = Get(i, j);
                        if (cp != null)
                        {
                            cell.SetCellValue(cp.value);
                        }
                    }
                }
                #endregion
            }
            // 第三步：写入文件流
            using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
                workbook.Close();
            }
        }
        public static void ReadExcel(string path)
        {
            try
            {
                IWorkbook wk = null;
                string extension = System.IO.Path.GetExtension(path);
                FileStream fs = File.OpenRead(path);
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }
                fs.Close();

                int sheetCount = wk.NumberOfSheets;//获取sheet的数量
                ISheet sheet = wk.GetSheetAt(0);//第一个sheet页（列表）
                int rowCount = sheet.LastRowNum;
                IRow row = sheet.GetRow(0);  //读取当前行数据

                #region 读取第一个sheet页面
                for (int i = startRow; i <= sheet.LastRowNum & i <= endRow; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row == null) continue;
                    //Console.WriteLine(row.GetCell(1).ToString());
                    for (int j = startColumn; j < row.Cells.Count & j <= endColumn; j++)
                    {
                        ICell cell = row.GetCell(j);
                        double val = 0;
                        if (cell.CellType == CellType.Formula)
                        {
                            val = cell.NumericCellValue;
                        }
                        else if (cell.CellType == CellType.Numeric)
                        {
                            val = cell.NumericCellValue;
                        }
                        else if (cell.CellType == CellType.String || cell.CellType == CellType.Blank)
                        {
                            Double.TryParse(cell.StringCellValue, out val);
                        }
                        else
                        {
                            Console.WriteLine("單元格格式錯誤：" + i + "," + j);
                            Console.WriteLine("單元格的值：" + i + "," + j + " : " + val);
                        }
                        CellWithPos cp = Get(i, j);
                        if (cp != null)
                        {
                            cp.value += val;
                        }
                        else
                        {
                            AllCells.Add(new CellWithPos() { row = i, column = j, value = val });
                        }
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }
        class CellWithPos
        {
            public int row;
            public int column;
            public double value = 0;
        }
    }
}