第17章-企业级应用最佳实践

第十七章：企业级应用最佳实践

17.1 架构设计

17.1.1 服务层封装

/// <summary>
/// Excel服务接口
/// </summary>
public interface IExcelService
{
    byte[] Export<T>(IEnumerable<T> data, ExportOptions options = null) where T : class;
    List<T> Import<T>(Stream fileStream, ImportOptions options = null) where T : class, new();
    DataTable ImportToDataTable(Stream fileStream, int sheetIndex = 0, bool hasHeader = true);
}

/// <summary>
/// 导出选项
/// </summary>
public class ExportOptions
{
    public string SheetName { get; set; } = "Sheet1";
    public bool IncludeHeader { get; set; } = true;
    public Dictionary<string, string> ColumnMappings { get; set; }
    public Dictionary<string, string> ColumnFormats { get; set; }
}

/// <summary>
/// 导入选项
/// </summary>
public class ImportOptions
{
    public int SheetIndex { get; set; } = 0;
    public bool HasHeader { get; set; } = true;
    public int StartRow { get; set; } = 0;
    public Dictionary<string, string> ColumnMappings { get; set; }
    public List<string> RequiredColumns { get; set; }
}

/// <summary>
/// Excel服务实现
/// </summary>
public class ExcelService : IExcelService
{
    public byte[] Export<T>(IEnumerable<T> data, ExportOptions options = null) where T : class
    {
        options ??= new ExportOptions();
        
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet(options.SheetName);
        
        var properties = typeof(T).GetProperties();
        
        // 创建表头
        if (options.IncludeHeader)
        {
            IRow headerRow = sheet.CreateRow(0);
            ICellStyle headerStyle = CreateHeaderStyle(workbook);
            
            for (int i = 0; i < properties.Length; i++)
            {
                string columnName = GetColumnName(properties[i], options.ColumnMappings);
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(columnName);
                cell.CellStyle = headerStyle;
            }
        }
        
        // 创建数据行
        int rowIndex = options.IncludeHeader ? 1 : 0;
        var styleCache = new Dictionary<string, ICellStyle>();
        
        foreach (var item in data)
        {
            IRow row = sheet.CreateRow(rowIndex++);
            for (int i = 0; i < properties.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                object value = properties[i].GetValue(item);
                SetCellValue(cell, value, workbook, options, properties[i].Name, styleCache);
            }
        }
        
        // 自动列宽
        for (int i = 0; i < properties.Length; i++)
        {
            sheet.AutoSizeColumn(i);
        }
        
        using MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
        return ms.ToArray();
    }
    
    public List<T> Import<T>(Stream fileStream, ImportOptions options = null) where T : class, new()
    {
        options ??= new ImportOptions();
        
        IWorkbook workbook = WorkbookFactory.Create(fileStream);
        ISheet sheet = workbook.GetSheetAt(options.SheetIndex);
        
        var result = new List<T>();
        var properties = typeof(T).GetProperties();
        
        // 建立列映射
        var columnMap = new Dictionary<int, PropertyInfo>();
        IRow headerRow = sheet.GetRow(options.StartRow);
        
        if (options.HasHeader && headerRow != null)
        {
            for (int i = 0; i < headerRow.LastCellNum; i++)
            {
                string columnName = headerRow.GetCell(i)?.ToString();
                if (string.IsNullOrEmpty(columnName)) continue;
                
                var prop = FindProperty(properties, columnName, options.ColumnMappings);
                if (prop != null)
                {
                    columnMap[i] = prop;
                }
            }
        }
        
        // 读取数据
        int startRow = options.HasHeader ? options.StartRow + 1 : options.StartRow;
        for (int i = startRow; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            if (row == null) continue;
            
            T item = new T();
            bool hasData = false;
            
            foreach (var kvp in columnMap)
            {
                ICell cell = row.GetCell(kvp.Key);
                if (cell == null) continue;
                
                object value = ConvertCellValue(cell, kvp.Value.PropertyType);
                if (value != null)
                {
                    kvp.Value.SetValue(item, value);
                    hasData = true;
                }
            }
            
            if (hasData) result.Add(item);
        }
        
        return result;
    }
    
    public DataTable ImportToDataTable(Stream fileStream, int sheetIndex = 0, bool hasHeader = true)
    {
        IWorkbook workbook = WorkbookFactory.Create(fileStream);
        ISheet sheet = workbook.GetSheetAt(sheetIndex);
        
        DataTable dt = new DataTable(sheet.SheetName);
        IRow firstRow = sheet.GetRow(0);
        
        if (firstRow == null) return dt;
        
        int columnCount = firstRow.LastCellNum;
        
        // 创建列
        for (int i = 0; i < columnCount; i++)
        {
            string columnName = hasHeader 
                ? firstRow.GetCell(i)?.ToString() ?? $"Column{i}"
                : $"Column{i}";
            dt.Columns.Add(columnName);
        }
        
        // 读取数据
        int startRow = hasHeader ? 1 : 0;
        for (int i = startRow; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            if (row == null) continue;
            
            DataRow dr = dt.NewRow();
            for (int j = 0; j < columnCount; j++)
            {
                dr[j] = row.GetCell(j)?.ToString() ?? "";
            }
            dt.Rows.Add(dr);
        }
        
        return dt;
    }
    
    // 辅助方法...
    private ICellStyle CreateHeaderStyle(IWorkbook workbook)
    {
        ICellStyle style = workbook.CreateCellStyle();
        IFont font = workbook.CreateFont();
        font.IsBold = true;
        style.SetFont(font);
        style.FillForegroundColor = IndexedColors.Grey25Percent.Index;
        style.FillPattern = FillPattern.SolidForeground;
        style.Alignment = HorizontalAlignment.Center;
        return style;
    }
    
    private string GetColumnName(PropertyInfo prop, Dictionary<string, string> mappings)
    {
        if (mappings != null && mappings.TryGetValue(prop.Name, out string name))
            return name;
            
        var displayAttr = prop.GetCustomAttribute<DisplayNameAttribute>();
        return displayAttr?.DisplayName ?? prop.Name;
    }
    
    private void SetCellValue(ICell cell, object value, IWorkbook workbook,
        ExportOptions options, string propName, Dictionary<string, ICellStyle> styleCache)
    {
        if (value == null) return;
        
        switch (value)
        {
            case string s:
                cell.SetCellValue(s);
                break;
            case DateTime dt:
                cell.SetCellValue(dt);
                cell.CellStyle = GetOrCreateDateStyle(workbook, styleCache);
                break;
            case double d:
                cell.SetCellValue(d);
                break;
            case decimal dec:
                cell.SetCellValue((double)dec);
                break;
            case int i:
                cell.SetCellValue(i);
                break;
            case bool b:
                cell.SetCellValue(b);
                break;
            default:
                cell.SetCellValue(value.ToString());
                break;
        }
    }
    
    private ICellStyle GetOrCreateDateStyle(IWorkbook workbook, Dictionary<string, ICellStyle> cache)
    {
        if (!cache.TryGetValue("date", out ICellStyle style))
        {
            style = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            style.DataFormat = format.GetFormat("yyyy-MM-dd");
            cache["date"] = style;
        }
        return style;
    }
    
    private PropertyInfo FindProperty(PropertyInfo[] properties, string columnName,
        Dictionary<string, string> mappings)
    {
        // 先检查映射
        if (mappings != null)
        {
            foreach (var kvp in mappings)
            {
                if (kvp.Value.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                {
                    return properties.FirstOrDefault(p => p.Name == kvp.Key);
                }
            }
        }
        
        // 按名称匹配
        return properties.FirstOrDefault(p => 
            p.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
    }
    
    private object ConvertCellValue(ICell cell, Type targetType)
    {
        if (cell == null) return null;
        
        Type underlyingType = Nullable.GetUnderlyingType(targetType) ?? targetType;
        
        try
        {
            if (underlyingType == typeof(string))
                return cell.ToString();
            
            if (underlyingType == typeof(DateTime))
            {
                if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                    return cell.DateCellValue;
                if (DateTime.TryParse(cell.ToString(), out DateTime dt))
                    return dt;
                return null;
            }
            
            if (cell.CellType == CellType.Numeric)
                return Convert.ChangeType(cell.NumericCellValue, underlyingType);
            
            if (double.TryParse(cell.ToString(), out double d))
                return Convert.ChangeType(d, underlyingType);
            
            return null;
        }
        catch
        {
            return null;
        }
    }
}

17.1.2 依赖注入配置

// ASP.NET Core 依赖注入配置
public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddExcelServices(this IServiceCollection services)
    {
        services.AddScoped<IExcelService, ExcelService>();
        services.AddScoped<IWordService, WordService>();
        return services;
    }
}

// 使用示例
public class MyController : ControllerBase
{
    private readonly IExcelService _excelService;
    
    public MyController(IExcelService excelService)
    {
        _excelService = excelService;
    }
    
    [HttpGet("export")]
    public IActionResult Export()
    {
        var data = GetData();
        byte[] fileBytes = _excelService.Export(data);
        return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx");
    }
}

17.2 异常处理

17.2.1 自定义异常

/// <summary>
/// Excel操作异常基类
/// </summary>
public class ExcelException : Exception
{
    public ExcelException(string message) : base(message) { }
    public ExcelException(string message, Exception innerException) : base(message, innerException) { }
}

/// <summary>
/// 文件格式异常
/// </summary>
public class InvalidExcelFormatException : ExcelException
{
    public string FileName { get; }
    
    public InvalidExcelFormatException(string fileName, string message) 
        : base($"文件 '{fileName}' 格式无效: {message}")
    {
        FileName = fileName;
    }
}

/// <summary>
/// 数据验证异常
/// </summary>
public class ExcelDataValidationException : ExcelException
{
    public int RowIndex { get; }
    public string ColumnName { get; }
    public List<string> Errors { get; }
    
    public ExcelDataValidationException(int rowIndex, string columnName, string error)
        : base($"第 {rowIndex} 行, {columnName} 列数据验证失败: {error}")
    {
        RowIndex = rowIndex;
        ColumnName = columnName;
        Errors = new List<string> { error };
    }
    
    public ExcelDataValidationException(List<string> errors)
        : base($"数据验证失败: {string.Join("; ", errors)}")
    {
        Errors = errors;
    }
}

17.2.2 异常处理中间件

/// <summary>
/// Excel异常处理
/// </summary>
public static class ExcelExceptionHandler
{
    public static T SafeExecute<T>(Func<T> action, string operationName)
    {
        try
        {
            return action();
        }
        catch (IOException ex)
        {
            throw new ExcelException($"文件操作失败: {operationName}", ex);
        }
        catch (InvalidOperationException ex)
        {
            throw new ExcelException($"操作无效: {operationName}", ex);
        }
        catch (OutOfMemoryException ex)
        {
            throw new ExcelException($"内存不足，文件可能过大: {operationName}", ex);
        }
        catch (Exception ex)
        {
            throw new ExcelException($"{operationName} 失败", ex);
        }
    }
    
    public static void SafeExecute(Action action, string operationName)
    {
        SafeExecute(() => { action(); return true; }, operationName);
    }
}

17.3 日志记录

17.3.1 操作日志

/// <summary>
/// Excel操作日志服务
/// </summary>
public class ExcelOperationLogger
{
    private readonly ILogger<ExcelOperationLogger> _logger;
    
    public ExcelOperationLogger(ILogger<ExcelOperationLogger> logger)
    {
        _logger = logger;
    }
    
    public void LogExport(string fileName, int rowCount, TimeSpan duration)
    {
        _logger.LogInformation(
            "Excel导出完成 - 文件: {FileName}, 行数: {RowCount}, 耗时: {Duration}ms",
            fileName, rowCount, duration.TotalMilliseconds);
    }
    
    public void LogImport(string fileName, int rowCount, int errorCount, TimeSpan duration)
    {
        if (errorCount > 0)
        {
            _logger.LogWarning(
                "Excel导入完成(有错误) - 文件: {FileName}, 总行数: {RowCount}, 错误数: {ErrorCount}, 耗时: {Duration}ms",
                fileName, rowCount, errorCount, duration.TotalMilliseconds);
        }
        else
        {
            _logger.LogInformation(
                "Excel导入完成 - 文件: {FileName}, 行数: {RowCount}, 耗时: {Duration}ms",
                fileName, rowCount, duration.TotalMilliseconds);
        }
    }
    
    public void LogError(string operation, Exception ex)
    {
        _logger.LogError(ex, "Excel操作失败 - 操作: {Operation}", operation);
    }
}

17.4 数据验证

17.4.1 导入数据验证

/// <summary>
/// 导入数据验证器
/// </summary>
public class ImportDataValidator<T> where T : class
{
    private readonly List<Func<T, int, ValidationResult>> _rules = new();
    
    public ImportDataValidator<T> AddRule(Func<T, int, ValidationResult> rule)
    {
        _rules.Add(rule);
        return this;
    }
    
    public ImportDataValidator<T> Required(Expression<Func<T, object>> property, string displayName = null)
    {
        var prop = GetPropertyInfo(property);
        displayName ??= prop.Name;
        
        _rules.Add((item, row) => {
            var value = prop.GetValue(item);
            if (value == null || (value is string s && string.IsNullOrWhiteSpace(s)))
            {
                return ValidationResult.Error(row, displayName, "不能为空");
            }
            return ValidationResult.Success();
        });
        
        return this;
    }
    
    public ImportDataValidator<T> Range(Expression<Func<T, double?>> property, 
        double min, double max, string displayName = null)
    {
        var prop = GetPropertyInfo(property);
        displayName ??= prop.Name;
        
        _rules.Add((item, row) => {
            var value = (double?)prop.GetValue(item);
            if (value.HasValue && (value < min || value > max))
            {
                return ValidationResult.Error(row, displayName, $"必须在 {min} 到 {max} 之间");
            }
            return ValidationResult.Success();
        });
        
        return this;
    }
    
    public List<ValidationResult> Validate(IEnumerable<T> items)
    {
        var errors = new List<ValidationResult>();
        int rowIndex = 1;
        
        foreach (var item in items)
        {
            foreach (var rule in _rules)
            {
                var result = rule(item, rowIndex);
                if (!result.IsValid)
                {
                    errors.Add(result);
                }
            }
            rowIndex++;
        }
        
        return errors;
    }
    
    private PropertyInfo GetPropertyInfo<TProperty>(Expression<Func<T, TProperty>> expression)
    {
        if (expression.Body is MemberExpression member)
        {
            return (PropertyInfo)member.Member;
        }
        if (expression.Body is UnaryExpression unary && unary.Operand is MemberExpression m)
        {
            return (PropertyInfo)m.Member;
        }
        throw new ArgumentException("Invalid expression");
    }
}

public class ValidationResult
{
    public bool IsValid { get; set; }
    public int RowIndex { get; set; }
    public string ColumnName { get; set; }
    public string Message { get; set; }
    
    public static ValidationResult Success() => new() { IsValid = true };
    public static ValidationResult Error(int row, string column, string message) 
        => new() { IsValid = false, RowIndex = row, ColumnName = column, Message = message };
}

17.5 模板引擎

17.5.1 基于模板的文档生成

/// <summary>
/// Excel模板引擎
/// </summary>
public class ExcelTemplateEngine
{
    /// <summary>
    /// 基于模板生成Excel
    /// </summary>
    public byte[] GenerateFromTemplate(string templatePath, Dictionary<string, object> data)
    {
        using FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = new XSSFWorkbook(fs);
        
        foreach (ISheet sheet in workbook)
        {
            ProcessSheet(sheet, data);
        }
        
        using MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
        return ms.ToArray();
    }
    
    private void ProcessSheet(ISheet sheet, Dictionary<string, object> data)
    {
        for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            if (row == null) continue;
            
            for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell == null || cell.CellType != CellType.String) continue;
                
                string cellValue = cell.StringCellValue;
                if (cellValue.Contains("{{") && cellValue.Contains("}}"))
                {
                    string newValue = ReplacePlaceholders(cellValue, data);
                    cell.SetCellValue(newValue);
                }
            }
        }
    }
    
    private string ReplacePlaceholders(string template, Dictionary<string, object> data)
    {
        var regex = new Regex(@"\{\{(\w+)\}\}");
        return regex.Replace(template, match => {
            string key = match.Groups[1].Value;
            if (data.TryGetValue(key, out object value))
            {
                return value?.ToString() ?? "";
            }
            return match.Value;
        });
    }
}

17.6 单元测试

17.6.1 服务测试

[TestClass]
public class ExcelServiceTests
{
    private IExcelService _excelService;
    
    [TestInitialize]
    public void Setup()
    {
        _excelService = new ExcelService();
    }
    
    [TestMethod]
    public void Export_WithValidData_ReturnsExcelFile()
    {
        // Arrange
        var data = new List<TestModel>
        {
            new() { Id = 1, Name = "Test1", Value = 100 },
            new() { Id = 2, Name = "Test2", Value = 200 }
        };
        
        // Act
        byte[] result = _excelService.Export(data);
        
        // Assert
        Assert.IsNotNull(result);
        Assert.IsTrue(result.Length > 0);
        
        // 验证内容
        using var ms = new MemoryStream(result);
        var workbook = new XSSFWorkbook(ms);
        var sheet = workbook.GetSheetAt(0);
        
        Assert.AreEqual(3, sheet.LastRowNum + 1);  // 1 header + 2 data rows
    }
    
    [TestMethod]
    public void Import_WithValidFile_ReturnsData()
    {
        // Arrange
        var exportData = new List<TestModel>
        {
            new() { Id = 1, Name = "Test", Value = 100 }
        };
        byte[] fileBytes = _excelService.Export(exportData);
        
        // Act
        using var ms = new MemoryStream(fileBytes);
        var result = _excelService.Import<TestModel>(ms);
        
        // Assert
        Assert.AreEqual(1, result.Count);
        Assert.AreEqual("Test", result[0].Name);
    }
    
    private class TestModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public double Value { get; set; }
    }
}

17.7 本章小结

本章介绍了NPOI在企业级应用中的最佳实践。通过本章学习，你应该掌握：

• 服务层架构设计和依赖注入
• 自定义异常和异常处理
• 日志记录和操作追踪
• 数据验证机制
• 模板引擎的实现
• 单元测试方法

这些最佳实践可以帮助你构建健壮、可维护的企业级应用。

---

下一章预告：第十八章将提供实战案例与综合应用。