使用C#+NPOI进行Excel处理，实现多个Excel文件的求和统计

一个简易的控制台程序，使用C#+NPOI进行Excel处理，实现多个Excel文件的求和统计。

前提：

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;}}
}