WZExport/Utilitys/Excel/ExcelBase.cs


using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using Utilty.Unit.File;
using Utilty.Unit.Enumerable;

namespace Utilty.Module.File.Source.Base
{
    /// <summary>
    /// Excel模块基类
    /// </summary>
    internal class ExcelBase
    {
        private readonly string Format1 = ".xlsx";
        private readonly string Format2 = ".xls";
        /// <summary>
        /// 自定义需求
        /// </summary>
        protected Action<ISheet> CustomDemandAction { get; set; }
        /// <summary>
        /// 将数据写入到Excel并保存到指定目录
        /// </summary>
        /// <param name="excelPath">excel路劲</param>
        /// <param name="excelData">写入EXCEL的数据字典，key为sheet名称，value为sheet数据</param>
        public void CreateExcel(string excelPath, Dictionary<string, List<List<string>>> excelData)
        {
            IWorkbook workbook = CreateWorkbook();
            foreach (var sheet in excelData)
            {
                WriteWorkSheet(workbook.CreateSheet(sheet.Key), sheet.Value);
            }
            SaveExcel();

            IWorkbook CreateWorkbook()
            {
                string format = Path.GetExtension(excelPath);
                if (format == Format1)
                    return new XSSFWorkbook();
                else if (format == Format2)
                    return new HSSFWorkbook();
                throw new Exception($"不支持{format}格式");
            }
            void SaveExcel()
            {
                using FileStream fs = FileUnit.CheckCreate(excelPath);
                workbook.Write(fs);
                workbook.Close();
            }
        }
        /// <summary>
        /// 将数据写入到Sheet
        /// </summary>
        /// <param name="worksheet">表</param>
        /// <param name="sheetData">表数据</param>
        private void WriteWorkSheet(ISheet worksheet, List<List<string>> sheetData)
        {         
            sheetData.ForEach((rowData, rowIndex) =>
            {
                IRow row = worksheet.CreateRow(rowIndex);
                rowData.ForEach((cellData, cellIndex) =>
                {
                    ICell cell = row.CreateCell(cellIndex);
                    cell.SetCellValue(cellData);
                });
            });

            worksheet.DefaultColumnWidth = 20;
            CustomDemandAction?.Invoke(worksheet);
        }
        /// <summary>
        /// 读取指定excel文件流的数据
        /// </summary>
        /// <param name="file">excel文件流</param>
        /// <param name="fileExtension">excel文件格式(.xls、.xlsx)</param>
        /// <param name="excelCover">excel转换数据的映射信息</param>
        /// <returns></returns>
        public Dictionary<string, List<List<string>>> ReadExcel(Stream file,string fileExtension, Dictionary<string,(List<string>,int)> excelCover)
        {
            Dictionary<string, List<List<string>>> excelData = new Dictionary<string, List<List<string>>>();
            IWorkbook workbook = ReadWorkbook();
            foreach (var sheetCover in excelCover)
            {
                var (header, headerIndex) = sheetCover.Value;
                ISheet worksheet = workbook.GetSheet(sheetCover.Key);
                excelData.Add(sheetCover.Key,ReadWorkSheet(worksheet, header, headerIndex));
            }
            return excelData;

            IWorkbook ReadWorkbook()
            {
                if (fileExtension == Format1)
                    return new XSSFWorkbook(file);
                else if(fileExtension == Format2)
                    return new HSSFWorkbook(file);
                throw new Exception($"不支持{fileExtension}格式");
            }           
        }      
        /// <summary>
        /// 读取sheet里的数据
        /// </summary>
        /// <param name="worksheet">表</param>
        /// <param name="header">数据头部</param>
        /// <param name="headerIndex">头部所在索引</param>
        /// <returns></returns>
        private List<List<string>> ReadWorkSheet(ISheet worksheet, List<string> header, int headerIndex)
        {            
            var sheetData = new List<List<string>>();
            var (keys, indexs) = GetHeaderMap();
            sheetData.Add(keys);
            IEnumerator rows = worksheet.GetRowEnumerator();
            while (rows.MoveNext())
            {
                var rowData = new List<string>();
                IRow row = rows.Current as IRow;
                foreach (var index in indexs)
                {
                    rowData.Add(row.GetCell(index).StringCellValue);
                }
                sheetData.Add(rowData);
            }
            return sheetData;

            (List<string>, List<int>) GetHeaderMap()
            {
                var keys = new List<string>();
                var indexs = new List<int>();
                IRow headerRow = worksheet.GetRow(headerIndex);
                headerRow.Cells.ForEach(cell =>
                {
                    if (header.Contains(cell.StringCellValue))
                    {
                        keys.Add(cell.StringCellValue);
                        indexs.Add(cell.ColumnIndex);
                    }                      
                });
                worksheet.RemoveRow(headerRow);
                return (keys,indexs);
            }
        }
    }
}